package auth

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"strings"
	"time"

	"chat-agent-system/internal/config"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
)

// PgStore PostgreSQL 实现的账户、凭证与会话存储。
type PgStore struct {
	pool *pgxpool.Pool
	cfg  *config.Config
}

// NewPgStore 构造 PG AuthStore；pool 必填且 IAM 表已迁移。
func NewPgStore(pool *pgxpool.Pool, cfg *config.Config) (*PgStore, error) {
	if pool == nil {
		return nil, errors.New("pg auth store: pool required")
	}
	return &PgStore{pool: pool, cfg: cfg}, nil
}

// IAMTablesReady 检查 Phase 1 IAM 表是否可用。
func IAMTablesReady(ctx context.Context, pool *pgxpool.Pool) bool {
	if pool == nil {
		return false
	}
	var ok bool
	err := pool.QueryRow(ctx, `
		SELECT EXISTS (
			SELECT 1 FROM information_schema.tables
			WHERE table_schema = 'public' AND table_name = 'user_credentials'
		)
	`).Scan(&ok)
	return err == nil && ok
}

func (s *PgStore) scanUser(row pgx.Row) (*UserAccount, error) {
	var u UserAccount
	var passwordHash string
	var createdAt, updatedAt time.Time
	var profileUpdatedBy *int64
	var profileUpdatedAt *time.Time
	err := row.Scan(
		&u.ID,
		&u.Username,
		&u.Name,
		&passwordHash,
		&u.Status,
		&u.MustChangePassword,
		&u.RoleID,
		&u.Position,
		&u.Department,
		&u.EmployeeID,
		&u.Email,
		&profileUpdatedBy,
		&profileUpdatedAt,
		&createdAt,
		&updatedAt,
	)
	if err != nil {
		return nil, err
	}
	u.PasswordHash = passwordHash
	if profileUpdatedBy != nil {
		u.ProfileUpdatedBy = *profileUpdatedBy
	}
	if profileUpdatedAt != nil {
		u.ProfileUpdatedAt = profileUpdatedAt.Format("2006-01-02 15:04:05")
	}
	u.CreatedAt = createdAt.Format("2006-01-02 15:04:05")
	u.UpdatedAt = updatedAt.Format("2006-01-02 15:04:05")
	return &u, nil
}

const userSelectSQL = `
	SELECT u.id,
	       COALESCE(NULLIF(u.username, ''), u.account_id) AS username,
	       u.name,
	       COALESCE(uc.password_hash, '') AS password_hash,
	       u.status,
	       u.must_change_password,
	       u.role_id,
	       u.position,
	       u.department,
	       u.employee_id,
	       u.email,
	       u.profile_updated_by,
	       u.profile_updated_at,
	       u.created_at,
	       u.updated_at
	FROM users u
	LEFT JOIN user_credentials uc ON uc.user_id = u.id
`

func (s *PgStore) GetUserByUsername(ctx context.Context, username string) (*UserAccount, error) {
	username = strings.TrimSpace(username)
	row := s.pool.QueryRow(ctx, userSelectSQL+`
		WHERE lower(COALESCE(NULLIF(u.username, ''), u.account_id)) = lower($1)
		LIMIT 1
	`, username)
	u, err := s.scanUser(row)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, fmt.Errorf("user not found: %s", username)
		}
		return nil, err
	}
	return u, nil
}

func (s *PgStore) GetUserByID(ctx context.Context, id int64) (*UserAccount, error) {
	row := s.pool.QueryRow(ctx, userSelectSQL+` WHERE u.id = $1`, id)
	u, err := s.scanUser(row)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, fmt.Errorf("user not found: %d", id)
		}
		return nil, err
	}
	return u, nil
}

func (s *PgStore) CreateUser(ctx context.Context, user *UserAccount) (*UserAccount, error) {
	if user == nil || strings.TrimSpace(user.Username) == "" {
		return nil, errors.New("username required")
	}
	if strings.TrimSpace(user.PasswordHash) == "" {
		return nil, errors.New("password hash required")
	}
	tx, err := s.pool.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	roleLegacy := "engineer"
	switch user.RoleID {
	case "role-director":
		roleLegacy = "director"
	case "role-tech-lead":
		roleLegacy = "regulatory"
	case "role-guest":
		roleLegacy = "sales"
	}

	var id int64
	err = tx.QueryRow(ctx, `
		INSERT INTO users (
			account_id, name, role, username, status, must_change_password,
			role_id, position, department, employee_id, email
		) VALUES ($1, $2, $3, $1, $4, $5, $6, $7, $8, $9, $10)
		RETURNING id
	`, user.Username, user.Name, roleLegacy, user.Status, user.MustChangePassword,
		user.RoleID, user.Position, user.Department, user.EmployeeID, user.Email).Scan(&id)
	if err != nil {
		if isUsernameConflict(err) {
			return nil, ErrUsernameExists
		}
		return nil, fmt.Errorf("insert user: %w", err)
	}

	_, err = tx.Exec(ctx, `
		INSERT INTO user_credentials (user_id, password_hash)
		VALUES ($1, $2)
	`, id, user.PasswordHash)
	if err != nil {
		return nil, fmt.Errorf("insert credentials: %w", err)
	}

	if user.RoleID != "" {
		if _, err = tx.Exec(ctx, `
			INSERT INTO user_role_bindings (user_id, role_id, scope_type, scope_id, assigned_by)
			VALUES ($1, $2, 'system', 0, $1)
			ON CONFLICT DO NOTHING
		`, id, user.RoleID); err != nil {
			return nil, fmt.Errorf("insert role binding: %w", err)
		}
	}

	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return s.GetUserByID(ctx, id)
}

func isUsernameConflict(err error) bool {
	var pgErr *pgconn.PgError
	if !errors.As(err, &pgErr) || pgErr.Code != "23505" {
		return false
	}
	switch pgErr.ConstraintName {
	case "uq_users_username", "users_account_id_key", "users_username_key":
		return true
	default:
		name := strings.ToLower(pgErr.ConstraintName)
		return strings.Contains(name, "username") || strings.Contains(name, "account_id")
	}
}

func (s *PgStore) UpdateUser(ctx context.Context, user *UserAccount, actorID int64) (*UserAccount, error) {
	_, err := s.pool.Exec(ctx, `
		UPDATE users SET
			name = $2,
			department = $3,
			employee_id = $4,
			email = $5,
			profile_updated_by = CASE WHEN $6::bigint = $1 THEN NULL ELSE NULLIF($6, 0) END,
			profile_updated_at = CASE WHEN $6::bigint = $1 OR $6 = 0 THEN NULL ELSE now() END,
			updated_at = now()
		WHERE id = $1
	`, user.ID, user.Name, user.Department, user.EmployeeID, user.Email, actorID)
	if err != nil {
		return nil, err
	}
	return s.GetUserByID(ctx, user.ID)
}

func (s *PgStore) ListActiveUserDirectory(ctx context.Context) ([]UserDirectoryEntry, error) {
	rows, err := s.pool.Query(ctx, `
		SELECT u.id,
		       COALESCE(NULLIF(u.username, ''), u.account_id) AS username,
		       u.name,
		       u.department,
		       u.position
		FROM users u
		WHERE u.status = 'active'
		ORDER BY u.name ASC, u.id ASC
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	out := make([]UserDirectoryEntry, 0)
	for rows.Next() {
		var e UserDirectoryEntry
		if err := rows.Scan(&e.ID, &e.Username, &e.Name, &e.Department, &e.Position); err != nil {
			return nil, err
		}
		out = append(out, e)
	}
	return out, rows.Err()
}

func (s *PgStore) UpdateUserRole(ctx context.Context, id int64, roleID, position string) (*UserAccount, error) {
	tx, err := s.pool.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	_, err = tx.Exec(ctx, `
		UPDATE users SET role_id = $2, position = $3, updated_at = now() WHERE id = $1
	`, id, roleID, position)
	if err != nil {
		return nil, err
	}

	_, err = tx.Exec(ctx, `
		DELETE FROM user_role_bindings
		WHERE user_id = $1 AND scope_type = 'system' AND scope_id = 0 AND role_id <> $2
	`, id, roleID)
	if err != nil {
		return nil, err
	}

	_, err = tx.Exec(ctx, `
		INSERT INTO user_role_bindings (user_id, role_id, scope_type, scope_id, assigned_by)
		VALUES ($1, $2, 'system', 0, $1)
		ON CONFLICT DO NOTHING
	`, id, roleID)
	if err != nil {
		return nil, err
	}

	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return s.GetUserByID(ctx, id)
}

func (s *PgStore) UpdateUserStatus(ctx context.Context, id int64, status AccountStatus) (*UserAccount, error) {
	_, err := s.pool.Exec(ctx, `
		UPDATE users SET status = $2, updated_at = now() WHERE id = $1
	`, id, status)
	if err != nil {
		return nil, err
	}
	if status == StatusDisabled {
		_ = s.RevokeAllForUser(ctx, id)
	}
	return s.GetUserByID(ctx, id)
}

func (s *PgStore) UpdateUserPassword(ctx context.Context, id int64, passwordHash string, mustChangePassword bool) (*UserAccount, error) {
	tx, err := s.pool.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	_, err = tx.Exec(ctx, `
		INSERT INTO user_credentials (user_id, password_hash, password_changed_at, failed_count, locked_until)
		VALUES ($1, $2, now(), 0, NULL)
		ON CONFLICT (user_id) DO UPDATE SET
			password_hash = EXCLUDED.password_hash,
			password_changed_at = now(),
			failed_count = 0,
			locked_until = NULL
	`, id, passwordHash)
	if err != nil {
		return nil, err
	}

	_, err = tx.Exec(ctx, `
		UPDATE users SET must_change_password = $2, updated_at = now() WHERE id = $1
	`, id, mustChangePassword)
	if err != nil {
		return nil, err
	}

	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return s.GetUserByID(ctx, id)
}

func (s *PgStore) RecordPasswordResetAudit(ctx context.Context, targetUserID, actorUserID int64, requestID string) error {
	_, err := s.pool.Exec(ctx, `
		INSERT INTO user_password_reset_audits (target_user_id, actor_user_id, request_id)
		VALUES ($1, $2, NULLIF($3, ''))
	`, targetUserID, actorUserID, strings.TrimSpace(requestID))
	return err
}

func (s *PgStore) ListUsers(ctx context.Context) ([]*UserAccount, error) {
	rows, err := s.pool.Query(ctx, userSelectSQL+` ORDER BY u.id ASC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	list := make([]*UserAccount, 0)
	for rows.Next() {
		u, err := s.scanUser(rows)
		if err != nil {
			return nil, err
		}
		u.PasswordHash = ""
		list = append(list, u)
	}
	return list, rows.Err()
}

func (s *PgStore) GetConfig(ctx context.Context) (SystemAuthConfig, error) {
	var raw []byte
	err := s.pool.QueryRow(ctx, `
		SELECT config_value FROM iam_system_config WHERE config_key = 'auth'
	`).Scan(&raw)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return SystemAuthConfig{AllowRegistration: true, RequireApproval: false}, nil
		}
		return SystemAuthConfig{}, err
	}
	var cfg SystemAuthConfig
	if err := json.Unmarshal(raw, &cfg); err != nil {
		return SystemAuthConfig{}, err
	}
	return cfg, nil
}

func (s *PgStore) UpdateConfig(ctx context.Context, cfg SystemAuthConfig) error {
	raw, err := json.Marshal(cfg)
	if err != nil {
		return err
	}
	_, err = s.pool.Exec(ctx, `
		INSERT INTO iam_system_config (config_key, config_value, updated_at)
		VALUES ('auth', $1, now())
		ON CONFLICT (config_key) DO UPDATE SET config_value = EXCLUDED.config_value, updated_at = now()
	`, raw)
	return err
}

func (s *PgStore) CreateSession(ctx context.Context, userID int64, tokenHash string, meta SessionMeta) error {
	now := time.Now()
	_, err := s.pool.Exec(ctx, `
		INSERT INTO auth_sessions (
			token_hash, user_id, created_at, last_seen_at, idle_expires_at, absolute_expires_at, ip_hash, user_agent
		) VALUES ($1, $2, $3, $3, $4, $5, $6, $7)
	`, tokenHash, userID, now, now.Add(defaultIdleSessionTTL), now.Add(defaultAbsoluteSessionTTL), meta.IPHash, meta.UserAgent)
	return err
}

func (s *PgStore) ResolveUserID(ctx context.Context, tokenHash string) (int64, error) {
	var userID int64
	var idleExp, absExp time.Time
	var revokedAt *time.Time
	var status AccountStatus
	err := s.pool.QueryRow(ctx, `
		SELECT s.user_id, s.idle_expires_at, s.absolute_expires_at, s.revoked_at, u.status
		FROM auth_sessions s
		JOIN users u ON u.id = s.user_id
		WHERE s.token_hash = $1
	`, tokenHash).Scan(&userID, &idleExp, &absExp, &revokedAt, &status)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return 0, errSessionNotFound
		}
		return 0, err
	}
	if revokedAt != nil {
		return 0, errSessionNotFound
	}
	now := time.Now()
	if now.After(idleExp) || now.After(absExp) {
		return 0, errSessionNotFound
	}
	if status == StatusDisabled || status == StatusPendingApproval {
		return 0, errSessionNotFound
	}

	_, _ = s.pool.Exec(ctx, `
		UPDATE auth_sessions SET last_seen_at = now(), idle_expires_at = $2 WHERE token_hash = $1
	`, tokenHash, now.Add(defaultIdleSessionTTL))
	return userID, nil
}

func (s *PgStore) TouchSession(ctx context.Context, tokenHash string) error {
	_, err := s.pool.Exec(ctx, `
		UPDATE auth_sessions SET last_seen_at = now(), idle_expires_at = $2
		WHERE token_hash = $1 AND revoked_at IS NULL
	`, tokenHash, time.Now().Add(defaultIdleSessionTTL))
	return err
}

func (s *PgStore) RevokeByTokenHash(ctx context.Context, tokenHash string) error {
	_, err := s.pool.Exec(ctx, `
		UPDATE auth_sessions SET revoked_at = now() WHERE token_hash = $1 AND revoked_at IS NULL
	`, tokenHash)
	return err
}

func (s *PgStore) RevokeAllForUser(ctx context.Context, userID int64) error {
	_, err := s.pool.Exec(ctx, `
		UPDATE auth_sessions SET revoked_at = now() WHERE user_id = $1 AND revoked_at IS NULL
	`, userID)
	return err
}

func (s *PgStore) ListActiveSessions(ctx context.Context, userID int64, currentTokenHash string) ([]SessionRecord, error) {
	var currentID string
	if currentTokenHash != "" {
		_ = s.pool.QueryRow(ctx, `
			SELECT id::text FROM auth_sessions
			WHERE token_hash = $1 AND user_id = $2 AND revoked_at IS NULL
		`, currentTokenHash, userID).Scan(&currentID)
	}
	rows, err := s.pool.Query(ctx, `
		SELECT id::text, created_at, last_seen_at, user_agent
		FROM auth_sessions
		WHERE user_id = $1
		  AND revoked_at IS NULL
		  AND idle_expires_at > now()
		  AND absolute_expires_at > now()
		ORDER BY last_seen_at DESC
	`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make([]SessionRecord, 0)
	for rows.Next() {
		var rec SessionRecord
		var createdAt, lastSeenAt time.Time
		if err := rows.Scan(&rec.ID, &createdAt, &lastSeenAt, &rec.UserAgent); err != nil {
			return nil, err
		}
		rec.CreatedAt = createdAt.Format("2006-01-02 15:04:05")
		rec.LastSeenAt = lastSeenAt.Format("2006-01-02 15:04:05")
		rec.IsCurrent = currentID != "" && rec.ID == currentID
		out = append(out, rec)
	}
	return out, rows.Err()
}

func (s *PgStore) RevokeSessionByID(ctx context.Context, userID int64, sessionID string) error {
	tag, err := s.pool.Exec(ctx, `
		UPDATE auth_sessions SET revoked_at = now()
		WHERE id = $1::uuid AND user_id = $2 AND revoked_at IS NULL
	`, sessionID, userID)
	if err != nil {
		return err
	}
	if tag.RowsAffected() == 0 {
		return errSessionNotFound
	}
	return nil
}