package auth

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

	"github.com/TaruLab/keluargamart-backend/internal/db"
	"github.com/TaruLab/keluargamart-backend/pkg/logger"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgtype"
	"github.com/jackc/pgx/v5/pgxpool"
)

// ErrNotFound is returned when a row does not exist.
var ErrNotFound = errors.New("not found")

// Repository persists users and personal_access_tokens in PostgreSQL (sqlc).
type Repository struct {
	pool *pgxpool.Pool
	q    *db.Queries
}

func NewRepository(pool *pgxpool.Pool) *Repository {
	return &Repository{pool: pool, q: db.New(pool)}
}

// ecommerceAdminFlag resolves customers.is_ecommerce_admin for the user.
// A missing customer row (internal ERP users) is not an error — flag = false.
func (r *Repository) ecommerceAdminFlag(ctx context.Context, userID int64) bool {
	ok, err := r.q.CustomerIsEcomAdminByUserID(ctx, pgtype.Int8{Int64: userID, Valid: true})
	return err == nil && ok
}

// SalesUserIDByEmployeeCode resolves a referral code (employees.employee_number)
// to the linked sales user id. No row (unknown code, non-sales dept,
// inactive employee/user) surfaces as ErrNotFound; the service maps it to
// ErrInvalidReferral.
func (r *Repository) SalesUserIDByEmployeeCode(ctx context.Context, code string) (int64, error) {
	id, err := r.q.GetSalesUserIDByEmployeeCode(ctx, code)
	if err != nil {
		return 0, mapUserErr(err)
	}
	return id, nil
}

// storedAppAccess reads users.application_access (migration 000037). Only
// the operator-assigned 'pos' value matters here: 'pos' cannot be expressed
// by DeriveApplicationAccess, which maps every internal role to 'erp'.
// LIVE-AUTHZ AUDIT 2026-08-26: ignoring this column locked every seeded POS
// cashier out of the POS shell (frontend isPOS never saw 'pos').
func (r *Repository) storedAppAccess(ctx context.Context, userID int64) (string, bool) {
	v, err := r.q.GetApplicationAccess(ctx, userID)
	if err != nil {
		return "", false
	}
	return v, v == "pos"
}

// applyAccessAxis finalizes the application axis for a loaded user:
// role+customer-flag derivation first, then the operator-assigned 'pos'
// override from users.application_access, and primary warehouse affiliation.
func applyAccessAxis(ctx context.Context, r *Repository, u *User) {
	u.ApplicationAccess = DeriveApplicationAccess(u.Role, r.ecommerceAdminFlag(ctx, u.ID))
	if v, ok := r.storedAppAccess(ctx, u.ID); ok && v == "pos" {
		u.ApplicationAccess = AppPOS
	}
	if stores, err := r.q.StoreDetailsForUser(ctx, u.ID); err == nil && len(stores) > 0 && stores[0].WarehouseID > 0 {
		wid := stores[0].WarehouseID
		u.WarehouseID = &wid
	}
}

// UserByEmail fetches the full user row (including password hash).
func (r *Repository) UserByEmail(ctx context.Context, email string) (*User, error) {
	row, err := r.q.GetUserByEmail(ctx, email)
	if err != nil {
		return nil, mapUserErr(err)
	}
	u := userFromRow(userRowFields(row))
	applyAccessAxis(ctx, r, u)
	return u, nil
}

// UserByID fetches a user by PK (no password).
func (r *Repository) UserByID(ctx context.Context, id int64) (*User, error) {
	row, err := r.q.GetUserByID(ctx, id)
	if err != nil {
		return nil, mapUserErr(err)
	}
	u := userFromRow(userRowFields(row))
	applyAccessAxis(ctx, r, u)
	return u, nil
}

// TokenByHash loads a token + linked user; returns ErrNotFound when absent.
func (r *Repository) TokenByHash(ctx context.Context, hash string) (*Token, *User, error) {
	tr, err := r.q.GetTokenByHash(ctx, hash)
	if err != nil {
		return nil, nil, mapUserErr(err)
	}
	u, err := r.UserByID(ctx, tr.TokenableID)
	if err != nil {
		return nil, nil, err
	}
	return tokenFromRow(&tr), u, nil
}

// CreateEcommerceAccount inserts the buyer user and its linked customer inside
// one transaction. The password in `in` is already a bcrypt hash.
func (r *Repository) CreateEcommerceAccount(ctx context.Context, in RegisterInput) (*User, error) {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)
	qtx := r.q.WithTx(tx)

	if _, err := qtx.GetUserByEmail(ctx, in.Email); err == nil {
		return nil, ErrEmailTaken
	} else if !errors.Is(err, pgx.ErrNoRows) {
		return nil, err
	}

	urow, err := qtx.CreateEcommerceUser(ctx, db.CreateEcommerceUserParams{
		Name:     in.Name,
		Email:    in.Email,
		Password: in.Password,
	})
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23505" {
			return nil, ErrEmailTaken
		}
		return nil, err
	}

	customerType := "retail"
	customerName := in.Name
	if in.AccountType == "business" {
		customerType = "wholesale"
		customerName = in.CompanyName
	}
	optText := func(s string) pgtype.Text { return pgtype.Text{String: s, Valid: s != ""} }
	custID, err := qtx.CreateEcommerceCustomer(ctx, db.CreateEcommerceCustomerParams{
		Code:          fmt.Sprintf("CUS-U%d", urow.ID),
		Name:          customerName,
		ContactPerson: optText(in.Name),
		Phone:         optText(in.Phone),
		Email:         optText(in.Email),
		TaxNumber:     optText(in.TaxNumber),
		CustomerType:  customerType,
		UserID:        pgtype.Int8{Int64: urow.ID, Valid: true},
	})
	if err != nil {
		return nil, err
	}

	if in.AccountType == "business" {
		// Business accounts receive default credit limit and 30-day TOP
		if _, err := tx.Exec(ctx,
			`UPDATE customers SET credit_limit = 50000000, payment_terms = 30 WHERE id = $1`,
			custID); err != nil {
			return nil, err
		}
		// Referral attribution: link the customer to the referring sales user.
		// in.ReferralSalesID is resolved + validated by the service (0 = self).
		if in.ReferralSalesID > 0 {
			if _, err := tx.Exec(ctx,
				`UPDATE customers SET assigned_sales_id = $1 WHERE id = $2`,
				in.ReferralSalesID, custID); err != nil {
				return nil, err
			}
		}
	}

	// Automatically enroll buyers as Bronze members (both individual and business start from bronze)
	mbrNumber := fmt.Sprintf("MBR-%s-%06d", time.Now().Format("200601"), urow.ID)
	var mbrID int64
	if err := tx.QueryRow(ctx,
		`INSERT INTO memberships (member_number, customer_id, tier, joined_date, is_active, notes, created_at, updated_at)
		 VALUES ($1, $2, 'bronze', CURRENT_DATE, true, 'Auto-enrolled buyer membership', now(), now())
		 ON CONFLICT (customer_id) DO NOTHING
		 RETURNING id`,
		mbrNumber, custID).Scan(&mbrID); err == nil && mbrID > 0 {
		if _, herr := tx.Exec(ctx,
			`INSERT INTO membership_tier_history (membership_id, to_tier, reason, created_at)
			 VALUES ($1, 'bronze', 'membership created', now())`,
			mbrID); herr != nil {
			logger.FromContext(ctx).Warn("membership tier history insert failed",
				"membership_id", mbrID, "customer_id", custID, "err", herr)
		}
	}

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

	u := userFromRow(userRowFields(urow))
	u.ApplicationAccess = AppECommerce
	return u, nil
}

// UpsertEmailVerificationCode stores (or replaces) the bcrypt hash of a
// 6-digit email-verification code (000152). The upsert also resets the
// attempt counter — a resend is a fresh chance, the old code is void.
func (r *Repository) UpsertEmailVerificationCode(ctx context.Context, email, purpose, codeHash string) error {
	return r.q.UpsertEmailVerificationCode(ctx, db.UpsertEmailVerificationCodeParams{
		Email:    email,
		Purpose:  purpose,
		CodeHash: codeHash,
	})
}

// GetEmailVerificationCode reads one OTP row. pgx.ErrNoRows maps to a
// sentinel the service already treats as "no code issued".
func (r *Repository) GetEmailVerificationCode(ctx context.Context, email, purpose string) (*EmailVerificationCode, error) {
	row, err := r.q.GetEmailVerificationCode(ctx, db.GetEmailVerificationCodeParams{
		Email:   email,
		Purpose: purpose,
	})
	if err != nil {
		return nil, mapUserErr(err)
	}
	return &EmailVerificationCode{
		Email:     row.Email,
		Purpose:   row.Purpose,
		CodeHash:  row.CodeHash,
		Attempts:  int(row.Attempts),
		ExpiresAt: row.ExpiresAt.Time,
	}, nil
}

// BumpEmailVerificationAttempts consumes one wrong-code attempt.
func (r *Repository) BumpEmailVerificationAttempts(ctx context.Context, email, purpose string) error {
	return r.q.BumpEmailVerificationAttempts(ctx, db.BumpEmailVerificationAttemptsParams{
		Email:   email,
		Purpose: purpose,
	})
}

// DeleteEmailVerificationCode removes the OTP row after a successful verify.
func (r *Repository) DeleteEmailVerificationCode(ctx context.Context, email, purpose string) error {
	return r.q.DeleteEmailVerificationCode(ctx, db.DeleteEmailVerificationCodeParams{
		Email:   email,
		Purpose: purpose,
	})
}

// MarkEmailVerified stamps email_verified_at on the account.
func (r *Repository) MarkEmailVerified(ctx context.Context, email string) error {
	return r.q.MarkEmailVerified(ctx, email)
}

// InsertToken stores a token record and returns its id.
func (r *Repository) InsertToken(ctx context.Context, t *Token) (int64, error) {
	id, err := r.q.InsertToken(ctx, db.InsertTokenParams{
		TokenableType: t.TokenableType,
		TokenableID:   t.TokenableID,
		Name:          t.Name,
		Token:         t.TokenHash,
		Abilities:     pgtype.Text{String: t.Abilities, Valid: t.Abilities != ""},
		ExpiresAt:     toTS(t.ExpiresAt),
	})
	if err != nil {
		return 0, err
	}
	t.ID = id
	return id, nil
}

// TouchLastUsed updates last_used_at of a token (throttled by caller).
func (r *Repository) TouchLastUsed(ctx context.Context, tokenID int64) error {
	return r.q.TouchLastUsed(ctx, db.TouchLastUsedParams{
		LastUsedAt: pgtype.Timestamptz{Time: time.Now(), Valid: true},
		ID:         tokenID,
	})
}

// DeleteToken removes a token (logout / revocation).
//
// OBSERVABILITY 2026-09-27 (A17): the single-token sibling of
// DeleteTokensByUser was annotated :exec, so the row count never reached Go and
// this method could only return the driver error — "revoke this token" could
// no-op in silence. The annotation is now :execrows and the count is propagated.
//
// A zero is surfaced at WARN rather than raised, exactly as for
// DeleteTokensByUser above: logout is not single-shot. Two tabs logging out, or a
// client retrying after a dropped response, both DELETE the same already-gone
// token, so zero is a legitimately reachable state and raising would turn a
// benign repeat logout into a 500 at internal/auth/handler.go:181-183. The
// security outcome is already correct when the count is zero (the row is gone, so
// the bearer token can no longer authenticate); what zero breaks is visibility,
// and a WARN restores it.
func (r *Repository) DeleteToken(ctx context.Context, id int64) error {
	deleted, err := r.q.DeleteToken(ctx, id)
	return observeTokenDeletion(ctx, id, deleted, err)
}

// observeTokenDeletion surfaces a zero-row single-token revocation and returns
// err unchanged, the single-token sibling of observeTokenRevocation. Ids and the
// count only — never a token value, hash, or credential.
func observeTokenDeletion(ctx context.Context, tokenID, deleted int64, err error) error {
	if err == nil && deleted == 0 {
		logger.FromContext(ctx).Warn("token deletion matched zero rows; the token was already gone",
			"token_id", tokenID, "deleted_tokens", deleted)
	}
	return err
}

// DeleteTokensByUser revokes every personal access token of a user
// (used on password change so old sessions cannot continue).
//
// OBSERVABILITY 2026-09-27: the sqlc :execrows row count used to be discarded
// with `_, err :=`, which is how a zero-row DELETE stayed invisible. The
// predicate once carried a doubled backslash ('App\\Models\\User') that can
// never match, so password change/reset revoked nothing and the doc comment
// above still claimed sessions were revoked. A zero is not proven wrong — a
// user with no other sessions legitimately has nothing to revoke — so it is
// logged at WARN, not raised as an error. Behaviour on the happy path and on
// the error path is unchanged: err is returned exactly as sqlc returned it.
func (r *Repository) DeleteTokensByUser(ctx context.Context, userID int64) error {
	deleted, err := r.q.DeleteTokensByUser(ctx, userID)
	return observeTokenRevocation(ctx, userID, deleted, err)
}

// observeTokenRevocation surfaces a zero-row token revocation and returns err
// unchanged. Split out of DeleteTokensByUser so the branch is unit-testable
// without a live database; it deliberately takes no credentials, only ids.
func observeTokenRevocation(ctx context.Context, userID, deleted int64, err error) error {
	if err == nil && deleted == 0 {
		logger.FromContext(ctx).Warn("token revocation matched zero rows; old sessions may still be live",
			"user_id", userID, "deleted_tokens", deleted)
	}
	return err
}

// UpdateLastLogin throttles last_login_at update to at most once per hour.
// Mirrors the Laravel ≤1×/hr rule (business-rules §7.1). Always succeeds (best
// effort); successive calls within the hour are no-ops.
func (r *Repository) UpdateLastLogin(ctx context.Context, userID int64, loc *time.Location) error {
	return r.q.UpdateLastLogin(ctx, db.UpdateLastLoginParams{
		ID:          userID,
		LastLoginAt: pgtype.Timestamptz{Time: time.Now().In(loc), Valid: true},
	})
}

// UpdateProfile updates name/email and optionally resets the password hash.
// The service reads the user first: zero rows means a concurrent delete.
func (r *Repository) UpdateProfile(ctx context.Context, id int64, name, email, passwordHash *string) error {
	n, err := r.q.UpdateProfile(ctx, db.UpdateProfileParams{
		ID:       id,
		Name:     toText(name),
		Email:    toText(email),
		Password: toText(passwordHash),
	})
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) UpsertPasswordResetToken(ctx context.Context, email, token string) error {
	return r.q.UpsertPasswordResetToken(ctx, db.UpsertPasswordResetTokenParams{Email: email, Token: token})
}

func (r *Repository) UserByResetToken(ctx context.Context, token string) (*User, error) {
	row, err := r.q.UserByResetToken(ctx, token)
	if err != nil {
		return nil, mapUserErr(err)
	}
	return &User{
		ID: row.ID, Name: row.Name, Email: row.Email, Password: row.Password,
		Role: Role(row.Role), IsActive: row.IsActive,
	}, nil
}

func (r *Repository) DeletePasswordResetToken(ctx context.Context, email string) error {
	return r.q.DeletePasswordResetToken(ctx, email)
}

func mapUserErr(err error) error {
	if errors.Is(err, pgx.ErrNoRows) {
		return ErrNotFound
	}
	return err
}

func orEmpty(s *string) string {
	if s == nil {
		return ""
	}
	return *s
}

func toText(s *string) pgtype.Text {
	if s == nil {
		return pgtype.Text{}
	}
	return pgtype.Text{String: *s, Valid: true}
}

func toTS(t *time.Time) pgtype.Timestamptz {
	if t == nil {
		return pgtype.Timestamptz{}
	}
	return pgtype.Timestamptz{Time: *t, Valid: true}
}

// userRowFields mirrors the identical sqlc GetUser*Row structs so either can
// be converted (Go allows conversion between structurally identical types).
type userRowFields struct {
	ID              int64
	Name            string
	Email           string
	EmailVerifiedAt pgtype.Timestamptz
	Password        string
	Role            string
	Permissions     []byte
	IsActive        bool
	LastLoginAt     pgtype.Timestamptz
	Avatar          pgtype.Text
	CreatedAt       pgtype.Timestamptz
	UpdatedAt       pgtype.Timestamptz
}

func userFromRow(r userRowFields) *User {
	u := &User{
		ID:                r.ID,
		Name:              r.Name,
		Email:             r.Email,
		Password:          r.Password,
		Role:              Role(r.Role),
		ApplicationAccess: DeriveApplicationAccess(Role(r.Role), false),
		IsActive:          r.IsActive,
		CreatedAt:         r.CreatedAt.Time,
		UpdatedAt:         r.UpdatedAt.Time,
		EmailVerifiedAt:   tsPtr(r.EmailVerifiedAt),
		LastLoginAt:       tsPtr(r.LastLoginAt),
		Avatar:            textPtr(r.Avatar),
	}
	if len(r.Permissions) > 0 && string(r.Permissions) != "null" {
		var pm map[string]bool
		if err := json.Unmarshal(r.Permissions, &pm); err == nil {
			m := Permissions{}
			for k, v := range pm {
				m[k] = v
			}
			u.Permissions = m
		}
	}
	return u
}

func tsPtr(t pgtype.Timestamptz) *time.Time {
	if !t.Valid {
		return nil
	}
	v := t.Time
	return &v
}

func textPtr(t pgtype.Text) *string {
	if !t.Valid {
		return nil
	}
	v := t.String
	return &v
}

func tokenFromRow(r *db.GetTokenByHashRow) *Token {
	t := &Token{
		ID:            r.ID,
		TokenableType: r.TokenableType,
		TokenableID:   r.TokenableID,
		Name:          r.Name,
		TokenHash:     r.Token,
		Abilities:     r.Abilities.String,
		LastUsedAt:    tsPtr(r.LastUsedAt),
		ExpiresAt:     tsPtr(r.ExpiresAt),
		CreatedAt:     r.CreatedAt.Time,
	}
	return t
}