package repositories

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

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"

	"lune/talentscale/internal/modules/billing/dto"
)

type billingPostgresRepo struct {
	db *pgxpool.Pool
}

// NewBillingPostgresRepository creates a new billing repository backed by pgxpool
func NewBillingPostgresRepository(db *pgxpool.Pool) BillingRepository {
	return &billingPostgresRepo{db: db}
}

func (r *billingPostgresRepo) Pool() *pgxpool.Pool {
	return r.db
}

func (r *billingPostgresRepo) BeginTx(ctx context.Context) (pgx.Tx, error) {
	return r.db.Begin(ctx)
}

// ═══════════════════════════════════════════════════════════════════════════════
// PACKAGES
// ═══════════════════════════════════════════════════════════════════════════════

func (r *billingPostgresRepo) GetActivePackages(ctx context.Context) ([]dto.PublicPackageResponse, error) {
	stmt := `SELECT id, name, price, limit_candidate, period_type
	         FROM packages
	         ORDER BY price ASC`

	rows, err := r.db.Query(ctx, stmt)
	if err != nil {
		return nil, fmt.Errorf("query active packages: %w", err)
	}
	defer rows.Close()

	var packages []dto.PublicPackageResponse
	for rows.Next() {
		var p dto.PublicPackageResponse
		if err := rows.Scan(&p.ID, &p.Name, &p.Price, &p.LimitCandidate, &p.PeriodType); err != nil {
			return nil, fmt.Errorf("scan package row: %w", err)
		}
		// Features derived from package name/tier
		p.Features = resolvePackageFeatures(p.Name)
		packages = append(packages, p)
	}
	return packages, nil
}

func (r *billingPostgresRepo) GetPackageByID(ctx context.Context, id uuid.UUID) (*PackageRow, error) {
	stmt := `SELECT id, name, price, limit_candidate, period_type, created_at, updated_at
	         FROM packages
	         WHERE id = $1
	         LIMIT 1`

	var p PackageRow
	err := r.db.QueryRow(ctx, stmt, id).Scan(
		&p.ID, &p.Name, &p.Price, &p.LimitCandidate, &p.PeriodType,
		&p.CreatedAt, &p.UpdatedAt,
	)
	if err != nil {
		return nil, fmt.Errorf("get package by id: %w", err)
	}
	return &p, nil
}

// ═══════════════════════════════════════════════════════════════════════════════
// INVOICES
// ═══════════════════════════════════════════════════════════════════════════════

func (r *billingPostgresRepo) CreateInvoice(ctx context.Context, tx pgx.Tx, inv *InvoiceRow) error {
	stmt := `INSERT INTO invoices (id, invoice_number, subscription_id, company_id, package_id, valid_until, sub_total, tax, total, status, created_at, updated_at)
	         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)`

	_, err := tx.Exec(ctx, stmt,
		inv.ID, inv.InvoiceNumber, inv.SubscriptionID, inv.CompanyID, inv.PackageID,
		inv.ValidUntil, inv.SubTotal, inv.Tax, inv.Total, inv.Status,
		inv.CreatedAt, inv.UpdatedAt,
	)
	if err != nil {
		return fmt.Errorf("create invoice: %w", err)
	}
	return nil
}

func (r *billingPostgresRepo) CreateInvoiceItem(ctx context.Context, tx pgx.Tx, item *InvoiceItemRow) error {
	stmt := `INSERT INTO invoice_items (id, invoice_id, item_name, quantity, price, description, created_at, updated_at)
	         VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`

	_, err := tx.Exec(ctx, stmt,
		item.ID, item.InvoiceID, item.ItemName, item.Quantity, item.Price, item.Description,
		item.CreatedAt, item.UpdatedAt,
	)
	if err != nil {
		return fmt.Errorf("create invoice item: %w", err)
	}
	return nil
}

func (r *billingPostgresRepo) GetInvoiceByID(ctx context.Context, id uuid.UUID, companyID uuid.UUID) (*InvoiceRow, error) {
	stmt := `SELECT id, invoice_number, subscription_id, company_id, package_id, valid_until, sub_total, tax, total, status, created_at, updated_at
	         FROM invoices
	         WHERE id = $1 AND company_id = $2
	         LIMIT 1`

	var inv InvoiceRow
	err := r.db.QueryRow(ctx, stmt, id, companyID).Scan(
		&inv.ID, &inv.InvoiceNumber, &inv.SubscriptionID, &inv.CompanyID, &inv.PackageID,
		&inv.ValidUntil, &inv.SubTotal, &inv.Tax, &inv.Total, &inv.Status,
		&inv.CreatedAt, &inv.UpdatedAt,
	)
	if err != nil {
		return nil, fmt.Errorf("get invoice by id: %w", err)
	}
	return &inv, nil
}

func (r *billingPostgresRepo) GetInvoicesByCompany(ctx context.Context, companyID uuid.UUID, status string, limit, offset int) ([]dto.InvoiceListItem, int64, error) {
	// Count query
	countStmt := `SELECT COUNT(*) FROM invoices WHERE company_id = $1`
	countArgs := []any{companyID}

	if status != "" {
		countStmt += ` AND status = $2`
		countArgs = append(countArgs, status)
	}

	var total int64
	if err := r.db.QueryRow(ctx, countStmt, countArgs...).Scan(&total); err != nil {
		return nil, 0, fmt.Errorf("count invoices: %w", err)
	}

	// Data query with JOIN to get item details
	dataStmt := `SELECT i.id, i.invoice_number, i.status, i.total,
	                    ii.item_name, ii.quantity, ii.price, ii.description, i.created_at
	             FROM invoices i
	             LEFT JOIN invoice_items ii ON ii.invoice_id = i.id
	             WHERE i.company_id = $1`
	dataArgs := []any{companyID}

	if status != "" {
		dataStmt += ` AND i.status = $2`
		dataArgs = append(dataArgs, status)
	}

	dataStmt += ` ORDER BY i.created_at DESC LIMIT $` + fmt.Sprintf("%d", len(dataArgs)+1) + ` OFFSET $` + fmt.Sprintf("%d", len(dataArgs)+2)
	dataArgs = append(dataArgs, limit, offset)

	rows, err := r.db.Query(ctx, dataStmt, dataArgs...)
	if err != nil {
		return nil, 0, fmt.Errorf("query invoices: %w", err)
	}
	defer rows.Close()

	var invoices []dto.InvoiceListItem
	for rows.Next() {
		var item dto.InvoiceListItem
		if err := rows.Scan(
			&item.ID, &item.InvoiceNumber, &item.Status, &item.Total,
			&item.ItemName, &item.Quantity, &item.Price, &item.Description, &item.CreatedAt,
		); err != nil {
			return nil, 0, fmt.Errorf("scan invoice row: %w", err)
		}
		invoices = append(invoices, item)
	}
	return invoices, total, nil
}

func (r *billingPostgresRepo) UpdateInvoiceStatus(ctx context.Context, tx pgx.Tx, id uuid.UUID, status string) error {
	stmt := `UPDATE invoices
	         SET status_invoice = $1, updated_at = NOW()
	         WHERE id = $2`

	_, err := tx.Exec(ctx, stmt, status, id)
	if err != nil {
		return fmt.Errorf("update invoice status: %w", err)
	}
	return nil
}

func (r *billingPostgresRepo) GetInvoiceItemsByInvoiceID(ctx context.Context, invoiceID uuid.UUID) ([]dto.InvoiceItemDTO, error) {
	stmt := `SELECT id, item_name, quantity, price, description
	         FROM invoice_items
	         WHERE invoice_id = $1
	         ORDER BY created_at ASC`

	rows, err := r.db.Query(ctx, stmt, invoiceID)
	if err != nil {
		return nil, fmt.Errorf("query invoice items: %w", err)
	}
	defer rows.Close()

	var items []dto.InvoiceItemDTO
	for rows.Next() {
		var item dto.InvoiceItemDTO
		if err := rows.Scan(&item.ID, &item.ItemName, &item.Quantity, &item.Price, &item.Description); err != nil {
			return nil, fmt.Errorf("scan invoice item: %w", err)
		}
		items = append(items, item)
	}
	return items, nil
}

func (r *billingPostgresRepo) GetNextInvoiceSequence(ctx context.Context, tx pgx.Tx, datePrefix string) (int, error) {
	stmt := `SELECT COUNT(*) + 1
	         FROM invoices
	         WHERE invoice_number LIKE $1 || '%'`

	var seq int
	err := tx.QueryRow(ctx, stmt, datePrefix).Scan(&seq)
	if err != nil {
		return 0, fmt.Errorf("get invoice sequence: %w", err)
	}
	return seq, nil
}

// ═══════════════════════════════════════════════════════════════════════════════
// PAYMENTS
// ═══════════════════════════════════════════════════════════════════════════════

func (r *billingPostgresRepo) CreatePaymentTransaction(ctx context.Context, tx pgx.Tx, pt *PaymentTransactionRow) error {
	stmt := `INSERT INTO payment_transactions (id, company_id, user_id, order_id, external_id, amount, currency, payment_method, status, paid_at, expired_at, payload, created_at)
	         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)`

	_, err := tx.Exec(ctx, stmt,
		pt.ID, pt.CompanyID, pt.UserID, pt.OrderID, pt.ExternalID,
		pt.Amount, pt.Currency, pt.PaymentMethod, pt.Status,
		pt.PaidAt, pt.ExpiredAt, pt.Payload, pt.CreatedAt,
	)
	if err != nil {
		return fmt.Errorf("create payment transaction: %w", err)
	}
	return nil
}

func (r *billingPostgresRepo) GetPaymentByOrderID(ctx context.Context, orderID string) (*PaymentTransactionRow, error) {
	stmt := `SELECT id, company_id, user_id, order_id, external_id, amount, currency, payment_method, status, paid_at, expired_at, payload, created_at
	         FROM payment_transactions
	         WHERE order_id = $1
	         LIMIT 1`

	var pt PaymentTransactionRow
	err := r.db.QueryRow(ctx, stmt, orderID).Scan(
		&pt.ID, &pt.CompanyID, &pt.UserID, &pt.OrderID, &pt.ExternalID,
		&pt.Amount, &pt.Currency, &pt.PaymentMethod, &pt.Status,
		&pt.PaidAt, &pt.ExpiredAt, &pt.Payload, &pt.CreatedAt,
	)
	if err != nil {
		return nil, fmt.Errorf("get payment by order_id: %w", err)
	}
	return &pt, nil
}

func (r *billingPostgresRepo) GetPaymentByInvoiceOrderID(ctx context.Context, invoiceID uuid.UUID) (*dto.PaymentDTO, error) {
	stmt := `SELECT id, order_id, amount, payment_method, status, paid_at
	         FROM payment_transactions
	         WHERE order_id LIKE $1 || '%'
	         ORDER BY created_at DESC
	         LIMIT 1`

	orderPrefix := fmt.Sprintf("INV-%s", invoiceID.String())
	var p dto.PaymentDTO
	err := r.db.QueryRow(ctx, stmt, orderPrefix).Scan(
		&p.ID, &p.OrderID, &p.Amount, &p.PaymentMethod, &p.Status, &p.PaidAt,
	)
	if err != nil {
		if err == pgx.ErrNoRows {
			return nil, nil
		}
		return nil, fmt.Errorf("get payment by invoice: %w", err)
	}
	return &p, nil
}

func (r *billingPostgresRepo) UpdatePaymentStatus(ctx context.Context, tx pgx.Tx, orderID string, status string, paymentMethod string, rawPayload []byte, paidAt *time.Time) error {
	stmt := `UPDATE payment_transactions
	         SET status = $1, payment_method = $2, payload = $3, paid_at = $4
	         WHERE order_id = $5`

	_, err := tx.Exec(ctx, stmt, status, paymentMethod, rawPayload, paidAt, orderID)
	if err != nil {
		return fmt.Errorf("update payment status: %w", err)
	}
	return nil
}

func (r *billingPostgresRepo) LockPaymentForUpdate(ctx context.Context, tx pgx.Tx, orderID string) (*PaymentTransactionRow, error) {
	stmt := `SELECT id, company_id, user_id, order_id, external_id, amount, currency, payment_method, status, paid_at, expired_at, payload, created_at
	         FROM payment_transactions
	         WHERE order_id = $1
	         FOR UPDATE`

	var pt PaymentTransactionRow
	err := tx.QueryRow(ctx, stmt, orderID).Scan(
		&pt.ID, &pt.CompanyID, &pt.UserID, &pt.OrderID, &pt.ExternalID,
		&pt.Amount, &pt.Currency, &pt.PaymentMethod, &pt.Status,
		&pt.PaidAt, &pt.ExpiredAt, &pt.Payload, &pt.CreatedAt,
	)
	if err != nil {
		return nil, fmt.Errorf("lock payment for update: %w", err)
	}
	return &pt, nil
}

// ═══════════════════════════════════════════════════════════════════════════════
// SUBSCRIPTIONS
// ═══════════════════════════════════════════════════════════════════════════════

func (r *billingPostgresRepo) GetActiveSubscription(ctx context.Context, companyID uuid.UUID) (*SubscriptionRow, error) {
	stmt := `SELECT id, company_id, package_id, start_at, end_at, status, created_at, updated_at
	         FROM subscriptions
	         WHERE company_id = $1 AND status = 'ACTIVE' AND end_at > NOW()
	         ORDER BY end_at DESC
	         LIMIT 1`

	var sub SubscriptionRow
	err := r.db.QueryRow(ctx, stmt, companyID).Scan(
		&sub.ID, &sub.CompanyID, &sub.PackageID,
		&sub.StartedAt, &sub.EndAt, &sub.Status,
		&sub.CreatedAt, &sub.UpdatedAt,
	)
	if err != nil {
		if err == pgx.ErrNoRows {
			return nil, nil
		}
		return nil, fmt.Errorf("get active subscription: %w", err)
	}
	return &sub, nil
}

func (r *billingPostgresRepo) CreateSubscription(ctx context.Context, tx pgx.Tx, sub *SubscriptionRow) error {
	stmt := `INSERT INTO subscriptions (id, company_id, package_id, start_at, end_at, status, created_at, updated_at)
	         VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`

	_, err := tx.Exec(ctx, stmt,
		sub.ID, sub.CompanyID, sub.PackageID,
		sub.StartedAt, sub.EndAt, sub.Status,
		sub.CreatedAt, sub.UpdatedAt,
	)
	if err != nil {
		return fmt.Errorf("create subscription: %w", err)
	}
	return nil
}

func (r *billingPostgresRepo) ExtendSubscription(ctx context.Context, tx pgx.Tx, id uuid.UUID, newEndDate time.Time) error {
	stmt := `UPDATE subscriptions
	         SET end_at = $1, updated_at = NOW()
	         WHERE id = $2`

	_, err := tx.Exec(ctx, stmt, newEndDate, id)
	if err != nil {
		return fmt.Errorf("extend subscription: %w", err)
	}
	return nil
}

func (r *billingPostgresRepo) GetSubscriptionWithPackage(ctx context.Context, companyID uuid.UUID) (*dto.ActiveSubscriptionResponse, error) {
	stmt := `SELECT p.name, s.status, s.start_at, s.end_at, p.price, p.limit_candidate,
	                COALESCE((SELECT COUNT(*) FROM candidates WHERE company_id = $1 AND deleted_at IS NULL), 0) as used_quota
	         FROM subscriptions s
	         INNER JOIN packages p ON p.id = s.package_id
	         WHERE s.company_id = $1 AND s.end_at > NOW()
	         ORDER BY s.end_at DESC
	         LIMIT 1`

	var resp dto.ActiveSubscriptionResponse
	err := r.db.QueryRow(ctx, stmt, companyID).Scan(
		&resp.PackageName, &resp.Status, &resp.StartAt, &resp.EndAt, &resp.PricePerMonth, &resp.TotalQuota, &resp.UsedQuota,
	)
	if err != nil {
		if err == pgx.ErrNoRows {
			return nil, nil
		}
		return nil, fmt.Errorf("get subscription with package: %w", err)
	}

	// Resolve features
	resp.PackageFeatures = resolvePackageFeatures(resp.PackageName)
	resp.RemainingQuota = resp.TotalQuota - resp.UsedQuota

	return &resp, nil
}

// ═══════════════════════════════════════════════════════════════════════════════
// USAGE / QUOTA
// ═══════════════════════════════════════════════════════════════════════════════

func (r *billingPostgresRepo) AddQuota(ctx context.Context, tx pgx.Tx, companyID uuid.UUID, quota int) error {
	now := time.Now()
	year := now.Year()
	month := int(now.Month())

	// Try to update existing record first
	stmt := `UPDATE usage_records
	         SET candidates_used = candidates_used - $1, updated_at = NOW()
	         WHERE company_id = $2 AND year = $3 AND month = $4`

	tag, err := tx.Exec(ctx, stmt, quota, companyID, year, month)
	if err != nil {
		return fmt.Errorf("add quota update: %w", err)
	}

	// If no row was updated, insert a new one
	if tag.RowsAffected() == 0 {
		insertStmt := `INSERT INTO usage_records (id, company_id, year, month, candidates_used, created_at, updated_at)
		               VALUES ($1, $2, $3, $4, $5, NOW(), NOW())
		               ON CONFLICT (company_id, year, month) DO UPDATE
		               SET candidates_used = usage_records.candidates_used - $5, updated_at = NOW()`

		_, err = tx.Exec(ctx, insertStmt, uuid.New(), companyID, year, month, quota)
		if err != nil {
			return fmt.Errorf("add quota insert: %w", err)
		}
	}
	return nil
}

func (r *billingPostgresRepo) GetRemainingQuota(ctx context.Context, companyID uuid.UUID) (int, error) {
	now := time.Now()

	// Get package limit from active subscription
	limitStmt := `SELECT COALESCE(p.limit_candidate, 0)
	              FROM subscriptions s
	              INNER JOIN packages p ON p.id = s.package_id
	              WHERE s.company_id = $1 AND s.status = 'ACTIVE' AND s.end_at > NOW()
	              ORDER BY s.end_at DESC
	              LIMIT 1`

	var limit int
	if err := r.db.QueryRow(ctx, limitStmt, companyID).Scan(&limit); err != nil {
		return 0, nil // No active subscription
	}

	// Get used count this month
	usedStmt := `SELECT COALESCE(candidates_used, 0)
	             FROM usage_records
	             WHERE company_id = $1 AND year = $2 AND month = $3`

	var used int
	if err := r.db.QueryRow(ctx, usedStmt, companyID, now.Year(), int(now.Month())).Scan(&used); err != nil {
		return limit, nil // No usage yet
	}

	remaining := limit - used
	if remaining < 0 {
		remaining = 0
	}
	return remaining, nil
}

// ═══════════════════════════════════════════════════════════════════════════════
// WEBHOOK LOGS
// ═══════════════════════════════════════════════════════════════════════════════

func (r *billingPostgresRepo) CreateWebhookLog(ctx context.Context, tx pgx.Tx, source string, eventType string, payload []byte, headers []byte) (int64, error) {
	stmt := `INSERT INTO webhook_logs (source, event_type, payload, headers, is_processed, created_at)
	         VALUES ($1, $2, $3, $4, false, NOW())
	         RETURNING id`

	var id int64
	err := tx.QueryRow(ctx, stmt, source, eventType, payload, headers).Scan(&id)
	if err != nil {
		return 0, fmt.Errorf("create webhook log: %w", err)
	}
	return id, nil
}

func (r *billingPostgresRepo) IsWebhookProcessed(ctx context.Context, orderID string) (bool, error) {
	stmt := `SELECT EXISTS(
	           SELECT 1 FROM payment_transactions
	           WHERE order_id = $1 AND status IN ('success', 'failed', 'expired')
	         )`

	var processed bool
	if err := r.db.QueryRow(ctx, stmt, orderID).Scan(&processed); err != nil {
		return false, fmt.Errorf("check webhook processed: %w", err)
	}
	return processed, nil
}

func (r *billingPostgresRepo) MarkWebhookProcessed(ctx context.Context, tx pgx.Tx, id int64) error {
	stmt := `UPDATE webhook_logs
	         SET is_processed = true, processed_at = NOW()
	         WHERE id = $1`

	_, err := tx.Exec(ctx, stmt, id)
	if err != nil {
		return fmt.Errorf("mark webhook processed: %w", err)
	}
	return nil
}

// ═══════════════════════════════════════════════════════════════════════════════
// EMAIL LOGS
// ═══════════════════════════════════════════════════════════════════════════════

func (r *billingPostgresRepo) CreateEmailLog(ctx context.Context, log *EmailLogRow) error {
	stmt := `INSERT INTO email_logs (id, recipient, email_type, subject, status, error_message, sent_at, created_at)
	         VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`

	_, err := r.db.Exec(ctx, stmt,
		log.ID, log.Recipient, log.EmailType, log.Subject,
		log.Status, log.ErrorMessage, log.SentAt, log.CreatedAt,
	)
	if err != nil {
		return fmt.Errorf("create email log: %w", err)
	}
	return nil
}

// ═══════════════════════════════════════════════════════════════════════════════
// COMPANY
// ═══════════════════════════════════════════════════════════════════════════════

func (r *billingPostgresRepo) GetCompanyByID(ctx context.Context, id uuid.UUID) (*CompanyRow, error) {
	stmt := `SELECT id, name, COALESCE(email, ''), package_id, status
	         FROM companies
	         WHERE id = $1 
	         LIMIT 1`

	var c CompanyRow
	err := r.db.QueryRow(ctx, stmt, id).Scan(&c.ID, &c.Name, &c.Email, &c.PackageID, &c.Status)
	if err != nil {
		return nil, fmt.Errorf("get company by id: %w", err)
	}
	return &c, nil
}

func (r *billingPostgresRepo) GetCompanyEmailRecipients(ctx context.Context, companyID uuid.UUID) ([]string, error) {
	// Get HR and Finance role users for this company
	stmt := `SELECT DISTINCT u.email
	         FROM users u
	         INNER JOIN roles r ON r.id = u.role_id
	         WHERE u.company_id = $1 
	         AND r.name IN ('hr', 'finance', 'super_admin')
	         ORDER BY u.email`

	rows, err := r.db.Query(ctx, stmt, companyID)
	if err != nil {
		return nil, fmt.Errorf("get company email recipients: %w", err)
	}
	defer rows.Close()

	var emails []string
	for rows.Next() {
		var email string
		if err := rows.Scan(&email); err != nil {
			return nil, fmt.Errorf("scan email: %w", err)
		}
		emails = append(emails, email)
	}
	return emails, nil
}

// ═══════════════════════════════════════════════════════════════════════════════
// HELPERS
// ═══════════════════════════════════════════════════════════════════════════════

// resolvePackageFeatures returns feature list based on package name
func resolvePackageFeatures(packageName string) []string {
	featureMap := map[string][]string{
		"Free Trial": {"DISC", "5 Candidates"},
		"Starter":    {"DISC", "MBTI", "CFIT", "PDF Reports", "Dashboard"},
		"Growth":     {"DISC", "MBTI", "CFIT", "PAPI", "MSDT", "PDF Reports", "Dashboard", "Priority Support", "Export Excel"},
		"Business":   {"All Tests", "PDF Reports", "Dashboard", "Priority Support", "API Access", "Custom Branding", "Multi-User", "Export Excel", "AI Insight"},
	}

	if features, ok := featureMap[packageName]; ok {
		return features
	}
	return []string{"DISC"}
}

// MarshalPayload converts an interface to JSON bytes for storage
func MarshalPayload(v any) []byte {
	data, err := json.Marshal(v)
	if err != nil {
		return []byte("{}")
	}
	return data
}