package appsec

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

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

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

const (
	ControlManagedSecretStorage    = "managed_secret_storage"
	ControlKeyRotation             = "key_rotation"
	ControlEdgeWAFTLS              = "edge_waf_tls"
	ControlDAST                    = "dast"
	ControlPenetrationTest         = "penetration_test"
	ControlSecureLogging           = "secure_logging"
	ControlCSRF                    = "csrf"
	ControlVulnerabilityDisclosure = "vulnerability_disclosure"

	ControlStatusDraft       = "draft"
	ControlStatusImplemented = "implemented"
	ControlStatusApproved    = "approved"
	ControlStatusFailed      = "failed"
	ControlStatusRetired     = "retired"

	RotationStatusScheduled  = "scheduled"
	RotationStatusInProgress = "in_progress"
	RotationStatusCompleted  = "completed"
	RotationStatusFailed     = "failed"
	RotationStatusCanceled   = "canceled"
)

type Repository struct {
	db *pgxpool.Pool
}

type Control struct {
	ID                   string          `json:"id"`
	ControlType          string          `json:"control_type"`
	Status               string          `json:"status"`
	Environment          string          `json:"environment"`
	Owner                string          `json:"owner"`
	Provider             string          `json:"provider"`
	PolicyReference      string          `json:"policy_reference"`
	EvidenceReference    string          `json:"evidence_reference"`
	LastVerifiedAt       *time.Time      `json:"last_verified_at,omitempty"`
	NextReviewAt         *time.Time      `json:"next_review_at,omitempty"`
	Metadata             json.RawMessage `json:"metadata"`
	CreatedByAdminUserID string          `json:"created_by_admin_user_id,omitempty"`
	CreatedAt            time.Time       `json:"created_at"`
	UpdatedAt            time.Time       `json:"updated_at"`
}

type KeyRotationRun struct {
	ID                    string     `json:"id"`
	SecretName            string     `json:"secret_name"`
	SecretCategory        string     `json:"secret_category"`
	Status                string     `json:"status"`
	OldKeyReference       string     `json:"old_key_reference,omitempty"`
	NewKeyReference       string     `json:"new_key_reference"`
	RotationReason        string     `json:"rotation_reason"`
	ScheduledFor          time.Time  `json:"scheduled_for"`
	CompletedAt           *time.Time `json:"completed_at,omitempty"`
	EvidenceReference     string     `json:"evidence_reference"`
	CreatedByAdminUserID  string     `json:"created_by_admin_user_id,omitempty"`
	ApprovedByAdminUserID string     `json:"approved_by_admin_user_id,omitempty"`
	CreatedAt             time.Time  `json:"created_at"`
	UpdatedAt             time.Time  `json:"updated_at"`
}

type Metrics struct {
	ControlsTotal         int64 `json:"controls_total"`
	ControlsApproved      int64 `json:"controls_approved"`
	ControlsImplemented   int64 `json:"controls_implemented"`
	ControlsMissingProof  int64 `json:"controls_missing_proof"`
	ControlsOverdueReview int64 `json:"controls_overdue_review"`
	RotationsOpen         int64 `json:"rotations_open"`
	RotationsOverdue      int64 `json:"rotations_overdue"`
	RotationsCompleted30d int64 `json:"rotations_completed_30d"`
}

type Dashboard struct {
	Metrics          Metrics          `json:"metrics"`
	Controls         []Control        `json:"controls"`
	KeyRotationRuns  []KeyRotationRun `json:"key_rotation_runs"`
	GeneratedAt      time.Time        `json:"generated_at"`
	ProductionGates  []Control        `json:"production_gates"`
	OperationalRisks []Control        `json:"operational_risks"`
}

type ControlParams struct {
	ControlType       string
	Status            string
	Environment       string
	Owner             string
	Provider          string
	PolicyReference   string
	EvidenceReference string
	LastVerifiedAt    *time.Time
	NextReviewAt      *time.Time
	Metadata          json.RawMessage
	AdminUserID       string
}

type RotationParams struct {
	SecretName        string
	SecretCategory    string
	Status            string
	OldKeyReference   string
	NewKeyReference   string
	RotationReason    string
	ScheduledFor      time.Time
	CompletedAt       *time.Time
	EvidenceReference string
	AdminUserID       string
}

type RotationUpdateParams struct {
	ID                string
	Status            string
	NewKeyReference   string
	CompletedAt       *time.Time
	EvidenceReference string
	AdminUserID       string
}

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
	}
	controls, err := r.ListControls(ctx, "", "", "", limit)
	if err != nil {
		return Dashboard{}, err
	}
	rotations, err := r.ListKeyRotationRuns(ctx, "open", limit)
	if err != nil {
		return Dashboard{}, err
	}
	gates := make([]Control, 0, len(controls))
	risks := make([]Control, 0, len(controls))
	for _, control := range controls {
		if isProductionGate(control.ControlType) {
			gates = append(gates, control)
		}
		if control.Status == ControlStatusDraft || control.Status == ControlStatusFailed || control.EvidenceReference == "" {
			risks = append(risks, control)
		}
	}
	return Dashboard{
		Metrics:          metrics,
		Controls:         controls,
		KeyRotationRuns:  rotations,
		GeneratedAt:      time.Now().UTC(),
		ProductionGates:  gates,
		OperationalRisks: risks,
	}, nil
}

func (r *Repository) Metrics(ctx context.Context) (Metrics, error) {
	var metrics Metrics
	err := r.db.QueryRow(ctx, `
		SELECT
			(SELECT COUNT(*) FROM application_security_controls)::bigint,
			(SELECT COUNT(*) FROM application_security_controls WHERE status = 'approved')::bigint,
			(SELECT COUNT(*) FROM application_security_controls WHERE status = 'implemented')::bigint,
			(SELECT COUNT(*) FROM application_security_controls WHERE status IN ('implemented', 'approved') AND evidence_reference = '')::bigint,
			(SELECT COUNT(*) FROM application_security_controls WHERE next_review_at IS NOT NULL AND next_review_at < now() AND status <> 'retired')::bigint,
			(SELECT COUNT(*) FROM key_rotation_runs WHERE status IN ('scheduled', 'in_progress'))::bigint,
			(SELECT COUNT(*) FROM key_rotation_runs WHERE status IN ('scheduled', 'in_progress') AND scheduled_for < now())::bigint,
			(SELECT COUNT(*) FROM key_rotation_runs WHERE status = 'completed' AND completed_at >= now() - interval '30 days')::bigint
	`).Scan(
		&metrics.ControlsTotal,
		&metrics.ControlsApproved,
		&metrics.ControlsImplemented,
		&metrics.ControlsMissingProof,
		&metrics.ControlsOverdueReview,
		&metrics.RotationsOpen,
		&metrics.RotationsOverdue,
		&metrics.RotationsCompleted30d,
	)
	return metrics, err
}

func (r *Repository) ListControls(ctx context.Context, controlType, status, environment string, limit int) ([]Control, error) {
	limit = normalizeLimit(limit)
	controlType = strings.ToLower(strings.TrimSpace(controlType))
	status = strings.ToLower(strings.TrimSpace(status))
	environment = strings.ToLower(strings.TrimSpace(environment))
	if controlType != "" && controlType != "all" {
		if err := validateControlType(controlType); err != nil {
			return nil, err
		}
	}
	if status != "" && status != "all" {
		if err := validateControlStatus(status); err != nil {
			return nil, err
		}
	}
	if environment != "" && environment != "all" {
		if err := validateEnvironment(environment); err != nil {
			return nil, err
		}
	}

	rows, err := r.db.Query(ctx, `
		SELECT id::text, control_type, status, environment, owner, provider, policy_reference,
			evidence_reference, last_verified_at, next_review_at, metadata,
			COALESCE(created_by_admin_user_id::text, ''), created_at, updated_at
		FROM application_security_controls
		WHERE ($1 = '' OR $1 = 'all' OR control_type = $1)
			AND ($2 = '' OR $2 = 'all' OR status = $2)
			AND ($3 = '' OR $3 = 'all' OR environment = $3)
		ORDER BY
			CASE status WHEN 'failed' THEN 1 WHEN 'draft' THEN 2 WHEN 'implemented' THEN 3 WHEN 'approved' THEN 4 ELSE 5 END,
			CASE WHEN evidence_reference = '' THEN 0 ELSE 1 END,
			updated_at DESC
		LIMIT $4
	`, controlType, status, environment, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	controls := []Control{}
	for rows.Next() {
		control, err := scanControl(rows)
		if err != nil {
			return nil, err
		}
		controls = append(controls, control)
	}
	return controls, rows.Err()
}

func (r *Repository) UpsertControl(ctx context.Context, params ControlParams) (Control, error) {
	if err := normalizeControlParams(&params); err != nil {
		return Control{}, err
	}
	row := r.db.QueryRow(ctx, `
		INSERT INTO application_security_controls (
			control_type, status, environment, owner, provider, policy_reference,
			evidence_reference, last_verified_at, next_review_at, metadata, created_by_admin_user_id
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, NULLIF($11, '')::uuid)
		ON CONFLICT (control_type, environment) DO UPDATE
		SET status = EXCLUDED.status,
			owner = EXCLUDED.owner,
			provider = EXCLUDED.provider,
			policy_reference = EXCLUDED.policy_reference,
			evidence_reference = EXCLUDED.evidence_reference,
			last_verified_at = EXCLUDED.last_verified_at,
			next_review_at = EXCLUDED.next_review_at,
			metadata = EXCLUDED.metadata,
			created_by_admin_user_id = COALESCE(application_security_controls.created_by_admin_user_id, EXCLUDED.created_by_admin_user_id)
		RETURNING id::text, control_type, status, environment, owner, provider, policy_reference,
			evidence_reference, last_verified_at, next_review_at, metadata,
			COALESCE(created_by_admin_user_id::text, ''), created_at, updated_at
	`, params.ControlType, params.Status, params.Environment, params.Owner, params.Provider, params.PolicyReference,
		params.EvidenceReference, params.LastVerifiedAt, params.NextReviewAt, params.Metadata, params.AdminUserID)

	control, err := scanControl(row)
	if err != nil && isForeignKeyViolation(err) {
		return Control{}, fmt.Errorf("%w: admin user does not exist", domain.ErrValidation)
	}
	return control, err
}

func (r *Repository) ListKeyRotationRuns(ctx context.Context, status string, limit int) ([]KeyRotationRun, error) {
	limit = normalizeLimit(limit)
	status = strings.ToLower(strings.TrimSpace(status))
	if status == "" {
		status = "open"
	}
	if status != "all" && status != "open" {
		if err := validateRotationStatus(status); err != nil {
			return nil, err
		}
	}

	rows, err := r.db.Query(ctx, `
		SELECT id::text, secret_name, secret_category, status, old_key_reference, new_key_reference,
			rotation_reason, scheduled_for, completed_at, evidence_reference,
			COALESCE(created_by_admin_user_id::text, ''), COALESCE(approved_by_admin_user_id::text, ''),
			created_at, updated_at
		FROM key_rotation_runs
		WHERE ($1 = 'all'
			OR ($1 = 'open' AND status IN ('scheduled', 'in_progress'))
			OR status = $1)
		ORDER BY
			CASE status WHEN 'failed' THEN 1 WHEN 'in_progress' THEN 2 WHEN 'scheduled' THEN 3 WHEN 'completed' THEN 4 ELSE 5 END,
			scheduled_for ASC,
			updated_at DESC
		LIMIT $2
	`, status, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	runs := []KeyRotationRun{}
	for rows.Next() {
		run, err := scanKeyRotationRun(rows)
		if err != nil {
			return nil, err
		}
		runs = append(runs, run)
	}
	return runs, rows.Err()
}

func (r *Repository) CreateKeyRotationRun(ctx context.Context, params RotationParams) (KeyRotationRun, error) {
	if err := normalizeRotationParams(&params); err != nil {
		return KeyRotationRun{}, err
	}
	row := r.db.QueryRow(ctx, `
		INSERT INTO key_rotation_runs (
			secret_name, secret_category, status, old_key_reference, new_key_reference,
			rotation_reason, scheduled_for, completed_at, evidence_reference, created_by_admin_user_id
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NULLIF($10, '')::uuid)
		RETURNING id::text, secret_name, secret_category, status, old_key_reference, new_key_reference,
			rotation_reason, scheduled_for, completed_at, evidence_reference,
			COALESCE(created_by_admin_user_id::text, ''), COALESCE(approved_by_admin_user_id::text, ''),
			created_at, updated_at
	`, params.SecretName, params.SecretCategory, params.Status, params.OldKeyReference, params.NewKeyReference,
		params.RotationReason, params.ScheduledFor, params.CompletedAt, params.EvidenceReference, params.AdminUserID)

	run, err := scanKeyRotationRun(row)
	if err != nil && isForeignKeyViolation(err) {
		return KeyRotationRun{}, fmt.Errorf("%w: admin user does not exist", domain.ErrValidation)
	}
	return run, err
}

func (r *Repository) UpdateKeyRotationRun(ctx context.Context, params RotationUpdateParams) (KeyRotationRun, error) {
	if err := normalizeRotationUpdateParams(&params); err != nil {
		return KeyRotationRun{}, err
	}
	row := r.db.QueryRow(ctx, `
		UPDATE key_rotation_runs
		SET status = $2,
			new_key_reference = COALESCE(NULLIF($3, ''), new_key_reference),
			completed_at = $4,
			evidence_reference = $5,
			approved_by_admin_user_id = CASE
				WHEN $2 = 'completed' THEN NULLIF($6, '')::uuid
				ELSE approved_by_admin_user_id
			END
		WHERE id = $1
		RETURNING id::text, secret_name, secret_category, status, old_key_reference, new_key_reference,
			rotation_reason, scheduled_for, completed_at, evidence_reference,
			COALESCE(created_by_admin_user_id::text, ''), COALESCE(approved_by_admin_user_id::text, ''),
			created_at, updated_at
	`, params.ID, params.Status, params.NewKeyReference, params.CompletedAt, params.EvidenceReference, params.AdminUserID)

	run, err := scanKeyRotationRun(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return KeyRotationRun{}, domain.ErrNotFound
	}
	if err != nil && isForeignKeyViolation(err) {
		return KeyRotationRun{}, fmt.Errorf("%w: admin user does not exist", domain.ErrValidation)
	}
	return run, err
}

func normalizeControlParams(params *ControlParams) error {
	params.ControlType = strings.ToLower(strings.TrimSpace(params.ControlType))
	params.Status = strings.ToLower(strings.TrimSpace(params.Status))
	params.Environment = strings.ToLower(strings.TrimSpace(params.Environment))
	params.Owner = strings.TrimSpace(params.Owner)
	params.Provider = strings.TrimSpace(params.Provider)
	params.PolicyReference = strings.TrimSpace(params.PolicyReference)
	params.EvidenceReference = strings.TrimSpace(params.EvidenceReference)
	if params.Status == "" {
		params.Status = ControlStatusDraft
	}
	if params.Environment == "" {
		params.Environment = "staging"
	}
	if err := validateControlType(params.ControlType); err != nil {
		return err
	}
	if err := validateControlStatus(params.Status); err != nil {
		return err
	}
	if err := validateEnvironment(params.Environment); err != nil {
		return err
	}
	if params.Owner == "" {
		return fmt.Errorf("%w: owner is required", domain.ErrValidation)
	}
	if params.PolicyReference == "" {
		return fmt.Errorf("%w: policy_reference is required", domain.ErrValidation)
	}
	if params.Status == ControlStatusApproved && params.EvidenceReference == "" {
		return fmt.Errorf("%w: approved controls require evidence_reference", domain.ErrValidation)
	}
	if len(params.Owner) > 120 || len(params.Provider) > 160 || len(params.PolicyReference) > 300 || len(params.EvidenceReference) > 300 {
		return fmt.Errorf("%w: application security text fields are too long", 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 normalizeRotationParams(params *RotationParams) error {
	params.SecretName = strings.TrimSpace(params.SecretName)
	params.SecretCategory = strings.ToLower(strings.TrimSpace(params.SecretCategory))
	params.Status = strings.ToLower(strings.TrimSpace(params.Status))
	params.OldKeyReference = strings.TrimSpace(params.OldKeyReference)
	params.NewKeyReference = strings.TrimSpace(params.NewKeyReference)
	params.RotationReason = strings.TrimSpace(params.RotationReason)
	params.EvidenceReference = strings.TrimSpace(params.EvidenceReference)
	if params.Status == "" {
		params.Status = RotationStatusScheduled
	}
	if err := validateRotationStatus(params.Status); err != nil {
		return err
	}
	if err := validateSecretCategory(params.SecretCategory); err != nil {
		return err
	}
	if params.SecretName == "" {
		return fmt.Errorf("%w: secret_name is required", domain.ErrValidation)
	}
	if params.NewKeyReference == "" {
		return fmt.Errorf("%w: new_key_reference is required", domain.ErrValidation)
	}
	if params.RotationReason == "" {
		return fmt.Errorf("%w: rotation_reason is required", domain.ErrValidation)
	}
	if params.ScheduledFor.IsZero() {
		params.ScheduledFor = time.Now().UTC()
	}
	if params.Status == RotationStatusCompleted {
		if params.CompletedAt == nil {
			completedAt := time.Now().UTC()
			params.CompletedAt = &completedAt
		}
		if params.EvidenceReference == "" {
			return fmt.Errorf("%w: completed rotations require evidence_reference", domain.ErrValidation)
		}
	}
	if params.Status != RotationStatusCompleted {
		params.CompletedAt = nil
	}
	if len(params.SecretName) > 160 || len(params.OldKeyReference) > 300 || len(params.NewKeyReference) > 300 ||
		len(params.RotationReason) > 500 || len(params.EvidenceReference) > 300 {
		return fmt.Errorf("%w: key rotation text fields are too long", domain.ErrValidation)
	}
	return nil
}

func normalizeRotationUpdateParams(params *RotationUpdateParams) error {
	params.ID = strings.TrimSpace(params.ID)
	params.Status = strings.ToLower(strings.TrimSpace(params.Status))
	params.NewKeyReference = strings.TrimSpace(params.NewKeyReference)
	params.EvidenceReference = strings.TrimSpace(params.EvidenceReference)
	if err := domain.ValidateUUID("id", params.ID); err != nil {
		return err
	}
	if err := validateRotationStatus(params.Status); err != nil {
		return err
	}
	if params.Status == RotationStatusCompleted {
		if params.CompletedAt == nil {
			completedAt := time.Now().UTC()
			params.CompletedAt = &completedAt
		}
		if params.EvidenceReference == "" {
			return fmt.Errorf("%w: completed rotations require evidence_reference", domain.ErrValidation)
		}
	}
	if params.Status != RotationStatusCompleted {
		params.CompletedAt = nil
	}
	if len(params.NewKeyReference) > 300 || len(params.EvidenceReference) > 300 {
		return fmt.Errorf("%w: key rotation text fields are too long", domain.ErrValidation)
	}
	return nil
}

func validateControlType(controlType string) error {
	switch controlType {
	case ControlManagedSecretStorage, ControlKeyRotation, ControlEdgeWAFTLS, ControlDAST,
		ControlPenetrationTest, ControlSecureLogging, ControlCSRF, ControlVulnerabilityDisclosure:
		return nil
	default:
		return fmt.Errorf("%w: invalid application security control_type", domain.ErrValidation)
	}
}

func validateControlStatus(status string) error {
	switch status {
	case ControlStatusDraft, ControlStatusImplemented, ControlStatusApproved, ControlStatusFailed, ControlStatusRetired:
		return nil
	default:
		return fmt.Errorf("%w: invalid application security status", domain.ErrValidation)
	}
}

func validateEnvironment(environment string) error {
	switch environment {
	case "development", "test", "ci", "staging", "production":
		return nil
	default:
		return fmt.Errorf("%w: invalid environment", domain.ErrValidation)
	}
}

func validateRotationStatus(status string) error {
	switch status {
	case RotationStatusScheduled, RotationStatusInProgress, RotationStatusCompleted, RotationStatusFailed, RotationStatusCanceled:
		return nil
	default:
		return fmt.Errorf("%w: invalid key rotation status", domain.ErrValidation)
	}
}

func validateSecretCategory(category string) error {
	switch category {
	case "jwt", "mfa", "card", "webhook", "provider_credential", "database", "other":
		return nil
	default:
		return fmt.Errorf("%w: invalid secret_category", domain.ErrValidation)
	}
}

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

func isProductionGate(controlType string) bool {
	switch controlType {
	case ControlManagedSecretStorage, ControlEdgeWAFTLS, ControlDAST, ControlPenetrationTest:
		return true
	default:
		return false
	}
}

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

func scanControl(row scanner) (Control, error) {
	var control Control
	var lastVerifiedAt sql.NullTime
	var nextReviewAt sql.NullTime
	err := row.Scan(
		&control.ID,
		&control.ControlType,
		&control.Status,
		&control.Environment,
		&control.Owner,
		&control.Provider,
		&control.PolicyReference,
		&control.EvidenceReference,
		&lastVerifiedAt,
		&nextReviewAt,
		&control.Metadata,
		&control.CreatedByAdminUserID,
		&control.CreatedAt,
		&control.UpdatedAt,
	)
	if lastVerifiedAt.Valid {
		control.LastVerifiedAt = &lastVerifiedAt.Time
	}
	if nextReviewAt.Valid {
		control.NextReviewAt = &nextReviewAt.Time
	}
	if len(control.Metadata) == 0 {
		control.Metadata = json.RawMessage(`{}`)
	}
	return control, err
}

func scanKeyRotationRun(row scanner) (KeyRotationRun, error) {
	var run KeyRotationRun
	var completedAt sql.NullTime
	err := row.Scan(
		&run.ID,
		&run.SecretName,
		&run.SecretCategory,
		&run.Status,
		&run.OldKeyReference,
		&run.NewKeyReference,
		&run.RotationReason,
		&run.ScheduledFor,
		&completedAt,
		&run.EvidenceReference,
		&run.CreatedByAdminUserID,
		&run.ApprovedByAdminUserID,
		&run.CreatedAt,
		&run.UpdatedAt,
	)
	if completedAt.Valid {
		run.CompletedAt = &completedAt.Time
	}
	return run, err
}

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