package golive

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

	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"

	"github.com/niels/banking-app/backend/internal/domain"
)

const (
	PhaseStaging    = "staging_launch"
	PhaseClosedBeta = "closed_beta_test_money"
	PhaseProduction = "real_money_production"

	StatusPending    = "pending"
	StatusInProgress = "in_progress"
	StatusReady      = "ready"
	StatusApproved   = "approved"
	StatusBlocked    = "blocked"
	StatusFailed     = "failed"
	StatusWaived     = "waived"
)

type Repository struct {
	db *pgxpool.Pool
}

type Gate struct {
	ID                    string          `json:"id"`
	GateNumber            int             `json:"gate_number"`
	Phase                 string          `json:"phase"`
	GateKey               string          `json:"gate_key"`
	Title                 string          `json:"title"`
	Status                string          `json:"status"`
	Priority              string          `json:"priority"`
	OwnerTeam             string          `json:"owner_team"`
	DecisionSummary       string          `json:"decision_summary"`
	EvidenceReference     string          `json:"evidence_reference,omitempty"`
	RunbookReference      string          `json:"runbook_reference,omitempty"`
	ApproverName          string          `json:"approver_name,omitempty"`
	DueAt                 *time.Time      `json:"due_at,omitempty"`
	ApprovedByAdminUserID string          `json:"approved_by_admin_user_id,omitempty"`
	ApprovedAt            *time.Time      `json:"approved_at,omitempty"`
	Metadata              json.RawMessage `json:"metadata"`
	CreatedByAdminUserID  string          `json:"created_by_admin_user_id,omitempty"`
	UpdatedByAdminUserID  string          `json:"updated_by_admin_user_id,omitempty"`
	CreatedAt             time.Time       `json:"created_at"`
	UpdatedAt             time.Time       `json:"updated_at"`
}

type GateParams struct {
	GateNumber        int
	Phase             string
	GateKey           string
	Title             string
	Status            string
	Priority          string
	OwnerTeam         string
	DecisionSummary   string
	EvidenceReference string
	RunbookReference  string
	ApproverName      string
	DueAt             *time.Time
	Metadata          json.RawMessage
	AdminUserID       string
}

type Metrics struct {
	GatesTotal     int64 `json:"gates_total"`
	OpenGates      int64 `json:"open_gates"`
	ReadyGates     int64 `json:"ready_gates"`
	ApprovedGates  int64 `json:"approved_gates"`
	BlockedGates   int64 `json:"blocked_gates"`
	StagingOpen    int64 `json:"staging_open"`
	BetaOpen       int64 `json:"beta_open"`
	ProductionOpen int64 `json:"production_open"`
}

type Dashboard struct {
	Metrics     Metrics           `json:"metrics"`
	Gates       []Gate            `json:"gates"`
	OpenGates   []Gate            `json:"open_gates"`
	ByPhase     map[string][]Gate `json:"by_phase"`
	GeneratedAt time.Time         `json:"generated_at"`
}

func NewRepository(db *pgxpool.Pool) *Repository {
	return &Repository{db: db}
}

func (r *Repository) Dashboard(ctx context.Context, limit int) (Dashboard, error) {
	metrics, err := r.Metrics(ctx)
	if err != nil {
		return Dashboard{}, err
	}
	gates, err := r.ListGates(ctx, "", "", limit)
	if err != nil {
		return Dashboard{}, err
	}
	byPhase := map[string][]Gate{}
	openGates := []Gate{}
	for _, gate := range gates {
		byPhase[gate.Phase] = append(byPhase[gate.Phase], gate)
		if isOpenStatus(gate.Status) {
			openGates = append(openGates, gate)
		}
	}
	return Dashboard{
		Metrics:     metrics,
		Gates:       gates,
		OpenGates:   openGates,
		ByPhase:     byPhase,
		GeneratedAt: time.Now().UTC(),
	}, nil
}

func (r *Repository) Metrics(ctx context.Context) (Metrics, error) {
	var metrics Metrics
	err := r.db.QueryRow(ctx, `
		SELECT
			(SELECT COUNT(*) FROM go_live_gate_controls)::bigint,
			(SELECT COUNT(*) FROM go_live_gate_controls WHERE status IN ('pending', 'in_progress', 'blocked', 'failed'))::bigint,
			(SELECT COUNT(*) FROM go_live_gate_controls WHERE status = 'ready')::bigint,
			(SELECT COUNT(*) FROM go_live_gate_controls WHERE status IN ('approved', 'waived'))::bigint,
			(SELECT COUNT(*) FROM go_live_gate_controls WHERE status = 'blocked')::bigint,
			(SELECT COUNT(*) FROM go_live_gate_controls WHERE phase = 'staging_launch' AND status IN ('pending', 'in_progress', 'blocked', 'failed'))::bigint,
			(SELECT COUNT(*) FROM go_live_gate_controls WHERE phase = 'closed_beta_test_money' AND status IN ('pending', 'in_progress', 'blocked', 'failed'))::bigint,
			(SELECT COUNT(*) FROM go_live_gate_controls WHERE phase = 'real_money_production' AND status IN ('pending', 'in_progress', 'blocked', 'failed'))::bigint
	`).Scan(
		&metrics.GatesTotal,
		&metrics.OpenGates,
		&metrics.ReadyGates,
		&metrics.ApprovedGates,
		&metrics.BlockedGates,
		&metrics.StagingOpen,
		&metrics.BetaOpen,
		&metrics.ProductionOpen,
	)
	return metrics, err
}

func (r *Repository) ListGates(ctx context.Context, phase, status string, limit int) ([]Gate, error) {
	limit = normalizeLimit(limit)
	phase = strings.ToLower(strings.TrimSpace(phase))
	status = strings.ToLower(strings.TrimSpace(status))
	if phase != "" && phase != "all" {
		if err := validatePhase(phase); err != nil {
			return nil, err
		}
	}
	if status != "" && status != "all" {
		if err := validateStatus(status); err != nil {
			return nil, err
		}
	}
	rows, err := r.db.Query(ctx, gateSelect+`
		WHERE ($1 = '' OR $1 = 'all' OR phase = $1)
			AND ($2 = '' OR $2 = 'all' OR status = $2)
		ORDER BY gate_number ASC
		LIMIT $3
	`, phase, status, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	gates := []Gate{}
	for rows.Next() {
		gate, err := scanGate(rows)
		if err != nil {
			return nil, err
		}
		gates = append(gates, gate)
	}
	return gates, rows.Err()
}

func (r *Repository) UpsertGate(ctx context.Context, params GateParams) (Gate, error) {
	if err := normalizeGateParams(&params); err != nil {
		return Gate{}, err
	}
	row := r.db.QueryRow(ctx, `
		INSERT INTO go_live_gate_controls (
			gate_number, phase, gate_key, title, status, priority, owner_team,
			decision_summary, evidence_reference, runbook_reference, approver_name,
			due_at, metadata, created_by_admin_user_id, updated_by_admin_user_id,
			approved_by_admin_user_id, approved_at
		)
		VALUES (
			$1, $2, $3, $4, $5, $6, $7,
			$8, $9, $10, $11,
			$12, $13, NULLIF($14, '')::uuid, NULLIF($14, '')::uuid,
			CASE WHEN $5 IN ('approved', 'waived') THEN NULLIF($14, '')::uuid ELSE NULL END,
			CASE WHEN $5 IN ('approved', 'waived') THEN now() ELSE NULL END
		)
		ON CONFLICT (gate_number) DO UPDATE
		SET phase = EXCLUDED.phase,
			gate_key = EXCLUDED.gate_key,
			title = EXCLUDED.title,
			status = EXCLUDED.status,
			priority = EXCLUDED.priority,
			owner_team = EXCLUDED.owner_team,
			decision_summary = EXCLUDED.decision_summary,
			evidence_reference = EXCLUDED.evidence_reference,
			runbook_reference = EXCLUDED.runbook_reference,
			approver_name = EXCLUDED.approver_name,
			due_at = EXCLUDED.due_at,
			metadata = EXCLUDED.metadata,
			updated_by_admin_user_id = EXCLUDED.updated_by_admin_user_id,
			approved_by_admin_user_id = CASE
				WHEN EXCLUDED.status IN ('approved', 'waived') THEN EXCLUDED.approved_by_admin_user_id
				ELSE NULL
			END,
			approved_at = CASE
				WHEN EXCLUDED.status IN ('approved', 'waived') THEN now()
				ELSE NULL
			END
		RETURNING `+gateColumns,
		params.GateNumber,
		params.Phase,
		params.GateKey,
		params.Title,
		params.Status,
		params.Priority,
		params.OwnerTeam,
		params.DecisionSummary,
		params.EvidenceReference,
		params.RunbookReference,
		params.ApproverName,
		params.DueAt,
		params.Metadata,
		params.AdminUserID,
	)
	gate, err := scanGate(row)
	if err != nil && isForeignKeyViolation(err) {
		return Gate{}, fmt.Errorf("%w: admin user does not exist", domain.ErrValidation)
	}
	return gate, err
}

func normalizeGateParams(params *GateParams) error {
	params.Phase = strings.ToLower(strings.TrimSpace(params.Phase))
	params.GateKey = strings.ToLower(strings.TrimSpace(params.GateKey))
	params.Title = strings.TrimSpace(params.Title)
	params.Status = strings.ToLower(strings.TrimSpace(params.Status))
	params.Priority = strings.ToUpper(strings.TrimSpace(params.Priority))
	params.OwnerTeam = strings.ToLower(strings.TrimSpace(params.OwnerTeam))
	params.DecisionSummary = strings.TrimSpace(params.DecisionSummary)
	params.EvidenceReference = strings.TrimSpace(params.EvidenceReference)
	params.RunbookReference = strings.TrimSpace(params.RunbookReference)
	params.ApproverName = strings.TrimSpace(params.ApproverName)
	if params.GateNumber < 133 || params.GateNumber > 151 {
		return fmt.Errorf("%w: gate_number must be between 133 and 151", domain.ErrValidation)
	}
	if err := validatePhase(params.Phase); err != nil {
		return err
	}
	if expected := expectedPhaseForGate(params.GateNumber); params.Phase != expected {
		return fmt.Errorf("%w: gate_number %d belongs to phase %s", domain.ErrValidation, params.GateNumber, expected)
	}
	if params.GateKey == "" {
		params.GateKey = defaultGateKey(params.GateNumber)
	}
	if params.Title == "" {
		return fmt.Errorf("%w: title is required", domain.ErrValidation)
	}
	if params.Status == "" {
		params.Status = StatusPending
	}
	if err := validateStatus(params.Status); err != nil {
		return err
	}
	if params.Priority == "" {
		params.Priority = "P0"
	}
	if params.Priority != "P0" && params.Priority != "P1" {
		return fmt.Errorf("%w: priority must be P0 or P1", domain.ErrValidation)
	}
	if params.OwnerTeam == "" {
		params.OwnerTeam = defaultOwnerTeam(params.GateNumber)
	}
	if !validOwnerTeam(params.OwnerTeam) {
		return fmt.Errorf("%w: invalid owner_team", domain.ErrValidation)
	}
	if params.DecisionSummary == "" {
		return fmt.Errorf("%w: decision_summary is required", domain.ErrValidation)
	}
	if (params.Status == StatusReady || params.Status == StatusApproved || params.Status == StatusWaived) && params.EvidenceReference == "" {
		return fmt.Errorf("%w: ready, approved and waived gates require evidence_reference", domain.ErrValidation)
	}
	if params.Status == StatusApproved && params.ApproverName == "" {
		return fmt.Errorf("%w: approved gates require approver_name", domain.ErrValidation)
	}
	if params.GateNumber == 151 && params.Status == StatusApproved && params.ApproverName == "" {
		return fmt.Errorf("%w: executive go/no-go signoff requires approver_name", domain.ErrValidation)
	}
	if params.Metadata == nil || len(params.Metadata) == 0 {
		params.Metadata = json.RawMessage(`{}`)
	}
	if !json.Valid(params.Metadata) {
		return fmt.Errorf("%w: metadata must be valid JSON", domain.ErrValidation)
	}
	return nil
}

func validatePhase(value string) error {
	switch value {
	case PhaseStaging, PhaseClosedBeta, PhaseProduction:
		return nil
	default:
		return fmt.Errorf("%w: phase must be staging_launch, closed_beta_test_money or real_money_production", domain.ErrValidation)
	}
}

func validateStatus(value string) error {
	switch value {
	case StatusPending, StatusInProgress, StatusReady, StatusApproved, StatusBlocked, StatusFailed, StatusWaived:
		return nil
	default:
		return fmt.Errorf("%w: invalid go-live gate status", domain.ErrValidation)
	}
}

func validOwnerTeam(value string) bool {
	switch value {
	case "engineering", "security", "operations", "compliance", "legal", "risk", "finance", "support", "product", "executive":
		return true
	default:
		return false
	}
}

func defaultGateKey(gateNumber int) string {
	switch gateNumber {
	case 133:
		return "staging-non-default-secrets"
	case 134:
		return "provider-webhook-e2e"
	case 135:
		return "staging-observability-evidence"
	case 136:
		return "seeded-test-users"
	case 137:
		return "gate-a-complete"
	case 138:
		return "cicd-release-rollback-exercise"
	case 139:
		return "security-scanning-active"
	case 140:
		return "beta-legal-wording"
	case 141:
		return "support-incident-channels"
	case 142:
		return "known-risk-register"
	case 143:
		return "legal-licensing-model"
	case 144:
		return "real-providers-approved"
	case 145:
		return "reconciliation-break-management-live"
	case 146:
		return "security-review-pentest-remediation"
	case 147:
		return "kyc-aml-fraud-ops-live"
	case 148:
		return "dora-ict-risk-resilience"
	case 149:
		return "pci-dss-scope-validation"
	case 150:
		return "bcdr-restore-incident-evidence"
	case 151:
		return "executive-go-no-go"
	default:
		return fmt.Sprintf("gate-%d", gateNumber)
	}
}

func expectedPhaseForGate(gateNumber int) string {
	switch {
	case gateNumber >= 133 && gateNumber <= 136:
		return PhaseStaging
	case gateNumber >= 137 && gateNumber <= 142:
		return PhaseClosedBeta
	default:
		return PhaseProduction
	}
}

func defaultOwnerTeam(gateNumber int) string {
	switch gateNumber {
	case 133, 134, 136, 138:
		return "engineering"
	case 135, 139, 146, 148, 150:
		return "security"
	case 140, 143:
		return "legal"
	case 141:
		return "support"
	case 142:
		return "risk"
	case 144, 145:
		return "operations"
	case 147:
		return "compliance"
	case 149:
		return "security"
	case 151:
		return "executive"
	default:
		return "operations"
	}
}

func isOpenStatus(status string) bool {
	switch status {
	case StatusApproved, StatusWaived:
		return false
	default:
		return true
	}
}

const gateColumns = `id::text, gate_number, phase, gate_key, title, status, priority,
	owner_team, decision_summary, evidence_reference, runbook_reference, approver_name,
	due_at, COALESCE(approved_by_admin_user_id::text, ''), approved_at, metadata,
	COALESCE(created_by_admin_user_id::text, ''), COALESCE(updated_by_admin_user_id::text, ''),
	created_at, updated_at`

const gateSelect = `SELECT ` + gateColumns + ` FROM go_live_gate_controls`

type scanner interface {
	Scan(dest ...any) error
}

func scanGate(row scanner) (Gate, error) {
	var gate Gate
	var dueAt, approvedAt sql.NullTime
	err := row.Scan(
		&gate.ID,
		&gate.GateNumber,
		&gate.Phase,
		&gate.GateKey,
		&gate.Title,
		&gate.Status,
		&gate.Priority,
		&gate.OwnerTeam,
		&gate.DecisionSummary,
		&gate.EvidenceReference,
		&gate.RunbookReference,
		&gate.ApproverName,
		&dueAt,
		&gate.ApprovedByAdminUserID,
		&approvedAt,
		&gate.Metadata,
		&gate.CreatedByAdminUserID,
		&gate.UpdatedByAdminUserID,
		&gate.CreatedAt,
		&gate.UpdatedAt,
	)
	if dueAt.Valid {
		gate.DueAt = &dueAt.Time
	}
	if approvedAt.Valid {
		gate.ApprovedAt = &approvedAt.Time
	}
	if len(gate.Metadata) == 0 {
		gate.Metadata = json.RawMessage(`{}`)
	}
	return gate, err
}

func normalizeLimit(limit int) int {
	if limit <= 0 || limit > 100 {
		return 50
	}
	return limit
}

func isForeignKeyViolation(err error) bool {
	var pgErr *pgconn.PgError
	return errors.As(err, &pgErr) && pgErr.Code == "23503"
}