package membership

import (
	"context"
	"errors"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgtype"
	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/shopspring/decimal"

	"github.com/TaruLab/keluargamart-backend/internal/db"
)

// Repository implements Store on a pgx pool (sqlc-backed).
type Repository struct {
	pool *pgxpool.Pool
	q    *db.Queries
}

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

func (r *Repository) Begin(ctx context.Context) (Tx, error) {
	return r.pool.Begin(ctx)
}

// queries binds the sqlc queries to the caller's transaction, falling back to
// the pool only when the caller is not inside one. Reading through the pool
// while holding a transaction deadlocks once concurrent callers exhaust it.
func (r *Repository) queries(q db.DBTX) *db.Queries {
	if q == nil {
		return r.q
	}
	return db.New(q)
}

func (r *Repository) MembershipByID(ctx context.Context, q db.DBTX, id int64) (db.GetMembershipByIDRow, error) {
	row, err := r.queries(q).GetMembershipByID(ctx, id)
	if err != nil {
		return db.GetMembershipByIDRow{}, mapErr(err)
	}
	return row, nil
}

func (r *Repository) MembershipByCustomer(ctx context.Context, q db.DBTX, customerID int64) (db.GetMembershipByIDRow, error) {
	row, err := r.queries(q).GetMembershipByCustomer(ctx, customerID)
	if err != nil {
		return db.GetMembershipByIDRow{}, mapErr(err)
	}
	return db.GetMembershipByIDRow(row), nil
}

func (r *Repository) MembershipByUser(ctx context.Context, q db.DBTX, userID int64) (db.GetMembershipByIDRow, error) {
	row, err := r.queries(q).GetMembershipByUser(ctx, pgtype.Int8{Int64: userID, Valid: true})
	if err != nil {
		return db.GetMembershipByIDRow{}, mapErr(err)
	}
	return db.GetMembershipByIDRow(row), nil
}

func (r *Repository) MembershipByNumber(ctx context.Context, q db.DBTX, number string) (db.GetMembershipByIDRow, error) {
	row, err := r.queries(q).GetMembershipByNumber(ctx, number)
	if err != nil {
		return db.GetMembershipByIDRow{}, mapErr(err)
	}
	return db.GetMembershipByIDRow(row), nil
}

func (r *Repository) List(ctx context.Context, f Filter) ([]db.GetMembershipByIDRow, int, error) {
	var search pgtype.Text
	if f.Search != "" {
		search = pgtype.Text{String: f.Search, Valid: true}
	}
	var tier pgtype.Text
	if f.Tier != "" {
		tier = pgtype.Text{String: f.Tier, Valid: true}
	}
	var isActive pgtype.Bool
	if f.IsActive != nil {
		isActive = pgtype.Bool{Bool: *f.IsActive, Valid: true}
	}

	total, err := r.q.CountMemberships(ctx, db.CountMembershipsParams{
		Search: search, Tier: tier, IsActive: isActive,
	})
	if err != nil {
		return nil, 0, err
	}
	rows, err := r.q.ListMemberships(ctx, db.ListMembershipsParams{
		Limit:  int32(f.PerPage),
		Offset: int32((f.Page - 1) * f.PerPage),
		Search: search, Tier: tier, IsActive: isActive,
	})
	if err != nil {
		return nil, 0, err
	}
	out := make([]db.GetMembershipByIDRow, 0, len(rows))
	for _, row := range rows {
		out = append(out, db.GetMembershipByIDRow(row))
	}
	return out, int(total), nil
}

func (r *Repository) CustomerExists(ctx context.Context, q db.DBTX, id int64) (bool, error) {
	n, err := r.queries(q).CountCustomersForMembership(ctx, id)
	return n > 0, err
}

func (r *Repository) CustomerOrderStats(ctx context.Context, q db.DBTX, customerID int64) (decimal.Decimal, int64, error) {
	var (
		total decimal.Decimal
		count int64
	)
	var row pgx.Row
	if q != nil {
		row = q.QueryRow(ctx, `
			SELECT COALESCE(SUM(total_amount), 0), COUNT(*)
			FROM sales_orders
			WHERE customer_id = $1
			  AND status IN ('confirmed', 'invoiced', 'picking', 'shipped', 'delivered', 'paid')
		`, customerID)
	} else {
		row = r.pool.QueryRow(ctx, `
			SELECT COALESCE(SUM(total_amount), 0), COUNT(*)
			FROM sales_orders
			WHERE customer_id = $1
			  AND status IN ('confirmed', 'invoiced', 'picking', 'shipped', 'delivered', 'paid')
		`, customerID)
	}
	if err := row.Scan(&total, &count); err != nil {
		return decimal.Zero, 0, err
	}
	return total, count, nil
}

func (r *Repository) Create(ctx context.Context, q db.DBTX, p db.CreateMembershipParams) (int64, error) {
	id, err := r.queries(q).CreateMembership(ctx, p)
	if err != nil {
		if isUniqueViolation(err) {
			return 0, ErrAlreadyMember
		}
		return 0, err
	}
	return id, nil
}

func (r *Repository) Update(ctx context.Context, q db.DBTX, p db.UpdateMembershipParams) error {
	n, err := r.queries(q).UpdateMembership(ctx, p)
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) RecordTierChange(ctx context.Context, q db.DBTX, p db.CreateMembershipTierHistoryParams) error {
	return r.queries(q).CreateMembershipTierHistory(ctx, p)
}

func (r *Repository) SetMembershipPoints(ctx context.Context, q db.DBTX, p db.SetMembershipPointsParams) error {
	n, err := r.queries(q).SetMembershipPoints(ctx, p)
	if err != nil {
		return err
	}
	// AccruePoints locks the row first: zero means it vanished mid-tx.
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) TierHistory(ctx context.Context, membershipID int64) ([]db.MembershipTierHistory, error) {
	return r.q.ListMembershipTierHistory(ctx, membershipID)
}

func (r *Repository) TierConfigHistory(ctx context.Context, tierID int64) ([]db.MembershipTierConfigHistory, error) {
	return r.q.ListMembershipTierConfigHistory(ctx, tierID)
}

func (r *Repository) ListTiers(ctx context.Context, q db.DBTX) ([]db.MembershipTier, error) {
	return r.queries(q).ListMembershipTiers(ctx)
}

func (r *Repository) GetTier(ctx context.Context, q db.DBTX, id int64) (db.MembershipTier, error) {
	row, err := r.queries(q).GetMembershipTier(ctx, id)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return db.MembershipTier{}, ErrTierNotFound
		}
		return db.MembershipTier{}, err
	}
	return row, nil
}

func (r *Repository) GetTierByCode(ctx context.Context, q db.DBTX, code string) (db.MembershipTier, error) {
	row, err := r.queries(q).GetMembershipTierByCode(ctx, code)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return db.MembershipTier{}, ErrTierNotFound
		}
		return db.MembershipTier{}, err
	}
	return row, nil
}

func (r *Repository) CreateTier(ctx context.Context, q db.DBTX, p db.CreateMembershipTierParams) (int64, error) {
	row, err := r.queries(q).CreateMembershipTier(ctx, p)
	if err != nil {
		return 0, err
	}
	return row.ID, nil
}

func (r *Repository) UpdateTier(ctx context.Context, q db.DBTX, p db.UpdateMembershipTierParams) error {
	if _, err := r.queries(q).UpdateMembershipTier(ctx, p); err != nil {
		return err
	}
	return nil
}

func (r *Repository) RecordTierConfigHistory(ctx context.Context, q db.DBTX, p db.CreateMembershipTierConfigHistoryParams) error {
	return r.queries(q).CreateMembershipTierConfigHistory(ctx, p)
}

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

func isUniqueViolation(err error) bool {
	var pge *pgconn.PgError
	return errors.As(err, &pge) && pge.Code == "23505"
}