package reconciliation

import (
	"context"
	"fmt"
	"strings"
	"time"

	"github.com/jackc/pgx/v5"

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

type EndOfDayBalanceSnapshot struct {
	ID                    string    `json:"id"`
	SnapshotDate          string    `json:"snapshot_date"`
	SubjectType           string    `json:"subject_type"`
	SubjectID             string    `json:"subject_id"`
	OwnerUserID           string    `json:"owner_user_id,omitempty"`
	Provider              string    `json:"provider,omitempty"`
	ExternalAccountID     string    `json:"external_account_id,omitempty"`
	LedgerAccountID       string    `json:"ledger_account_id,omitempty"`
	Currency              string    `json:"currency"`
	BalanceCents          int64     `json:"balance_cents"`
	AvailableBalanceCents int64     `json:"available_balance_cents,omitempty"`
	ReservedBalanceCents  int64     `json:"reserved_balance_cents,omitempty"`
	DebitBalanceCents     int64     `json:"debit_balance_cents"`
	CreditBalanceCents    int64     `json:"credit_balance_cents"`
	Source                string    `json:"source"`
	CreatedByAdminUserID  string    `json:"created_by_admin_user_id,omitempty"`
	CreatedAt             time.Time `json:"created_at"`
}

type EndOfDaySnapshotBatch struct {
	SnapshotDate  string `json:"snapshot_date"`
	SnapshotCount int64  `json:"snapshot_count"`
}

type DailyRunResult struct {
	Run           Run    `json:"run"`
	SnapshotDate  string `json:"snapshot_date"`
	SnapshotCount int64  `json:"snapshot_count"`
}

func (r *Repository) RunDaily(ctx context.Context, adminUserID string, snapshotDate time.Time) (DailyRunResult, error) {
	batch, err := r.CreateEndOfDaySnapshots(ctx, adminUserID, snapshotDate)
	if err != nil {
		return DailyRunResult{}, err
	}
	run, err := r.Run(ctx, adminUserID, "daily")
	if err != nil {
		return DailyRunResult{}, err
	}
	return DailyRunResult{Run: run, SnapshotDate: batch.SnapshotDate, SnapshotCount: batch.SnapshotCount}, nil
}

func (r *Repository) CreateEndOfDaySnapshots(ctx context.Context, adminUserID string, snapshotDate time.Time) (EndOfDaySnapshotBatch, error) {
	if adminUserID != "" {
		if err := domain.ValidateUUID("admin_user_id", adminUserID); err != nil {
			return EndOfDaySnapshotBatch{}, err
		}
	}
	date := normalizeSnapshotDate(snapshotDate)
	tx, err := r.db.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.RepeatableRead})
	if err != nil {
		return EndOfDaySnapshotBatch{}, err
	}
	defer tx.Rollback(ctx)

	total := int64(0)
	for _, statement := range []string{
		upsertAccountSnapshotsSQL,
		upsertWalletSnapshotsSQL,
		upsertProviderSnapshotsSQL,
		upsertLedgerAccountSnapshotsSQL,
	} {
		tag, err := tx.Exec(ctx, statement, date, adminUserID)
		if err != nil {
			return EndOfDaySnapshotBatch{}, err
		}
		total += tag.RowsAffected()
	}
	if err := tx.Commit(ctx); err != nil {
		return EndOfDaySnapshotBatch{}, err
	}
	return EndOfDaySnapshotBatch{SnapshotDate: date.Format("2006-01-02"), SnapshotCount: total}, nil
}

func (r *Repository) ListEndOfDaySnapshots(ctx context.Context, snapshotDate time.Time, subjectType string, limit int) ([]EndOfDayBalanceSnapshot, error) {
	date := normalizeSnapshotDate(snapshotDate)
	subjectType = strings.ToLower(strings.TrimSpace(subjectType))
	if subjectType != "" && subjectType != "account" && subjectType != "wallet" && subjectType != "provider_account" && subjectType != "ledger_account" {
		return nil, fmt.Errorf("%w: invalid subject_type", domain.ErrValidation)
	}
	limit = normalizeLimit(limit)

	rows, err := r.db.Query(ctx, `
		SELECT id::text, snapshot_date::text, subject_type, subject_id,
			COALESCE(owner_user_id::text, ''), COALESCE(provider, ''), COALESCE(external_account_id, ''),
			COALESCE(ledger_account_id::text, ''), TRIM(currency)::text, balance_cents,
			COALESCE(available_balance_cents, 0), COALESCE(reserved_balance_cents, 0),
			debit_balance_cents, credit_balance_cents, source,
			COALESCE(created_by_admin_user_id::text, ''), created_at
		FROM end_of_day_balance_snapshots
		WHERE snapshot_date = $1::date
			AND ($2 = '' OR subject_type = $2)
		ORDER BY subject_type, currency, subject_id
		LIMIT $3
	`, date, subjectType, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	snapshots := []EndOfDayBalanceSnapshot{}
	for rows.Next() {
		item, err := scanEndOfDaySnapshot(rows)
		if err != nil {
			return nil, err
		}
		snapshots = append(snapshots, item)
	}
	return snapshots, rows.Err()
}

func normalizeSnapshotDate(snapshotDate time.Time) time.Time {
	if snapshotDate.IsZero() {
		snapshotDate = time.Now().UTC()
	}
	year, month, day := snapshotDate.UTC().Date()
	return time.Date(year, month, day, 0, 0, 0, 0, time.UTC)
}

func scanEndOfDaySnapshot(row scanner) (EndOfDayBalanceSnapshot, error) {
	var item EndOfDayBalanceSnapshot
	err := row.Scan(
		&item.ID,
		&item.SnapshotDate,
		&item.SubjectType,
		&item.SubjectID,
		&item.OwnerUserID,
		&item.Provider,
		&item.ExternalAccountID,
		&item.LedgerAccountID,
		&item.Currency,
		&item.BalanceCents,
		&item.AvailableBalanceCents,
		&item.ReservedBalanceCents,
		&item.DebitBalanceCents,
		&item.CreditBalanceCents,
		&item.Source,
		&item.CreatedByAdminUserID,
		&item.CreatedAt,
	)
	return item, err
}

const upsertAccountSnapshotsSQL = `
	INSERT INTO end_of_day_balance_snapshots (
		snapshot_date, subject_type, subject_id, owner_user_id, provider, external_account_id,
		currency, balance_cents, available_balance_cents, reserved_balance_cents,
		debit_balance_cents, credit_balance_cents, source, metadata, created_by_admin_user_id
	)
	SELECT $1::date, 'account', a.id::text, a.user_id, a.bank_provider, a.external_account_id,
		a.currency, a.balance_cents, a.balance_cents, 0,
		0, GREATEST(a.balance_cents, 0), 'daily_reconciliation',
		jsonb_build_object('account_number', a.account_number, 'iban', a.iban, 'status', a.status),
		NULLIF($2, '')::uuid
	FROM accounts a
	ON CONFLICT (snapshot_date, subject_type, subject_id, currency) DO UPDATE
	SET owner_user_id = EXCLUDED.owner_user_id,
		provider = EXCLUDED.provider,
		external_account_id = EXCLUDED.external_account_id,
		balance_cents = EXCLUDED.balance_cents,
		available_balance_cents = EXCLUDED.available_balance_cents,
		reserved_balance_cents = EXCLUDED.reserved_balance_cents,
		debit_balance_cents = EXCLUDED.debit_balance_cents,
		credit_balance_cents = EXCLUDED.credit_balance_cents,
		source = EXCLUDED.source,
		metadata = EXCLUDED.metadata,
		created_by_admin_user_id = EXCLUDED.created_by_admin_user_id,
		created_at = now()
`

const upsertWalletSnapshotsSQL = `
	INSERT INTO end_of_day_balance_snapshots (
		snapshot_date, subject_type, subject_id, owner_user_id, currency, balance_cents,
		available_balance_cents, reserved_balance_cents, debit_balance_cents,
		credit_balance_cents, source, metadata, created_by_admin_user_id
	)
	SELECT $1::date, 'wallet', w.id::text, w.user_id, wb.currency,
		(wb.available_balance_cents + wb.reserved_balance_cents)::bigint,
		wb.available_balance_cents, wb.reserved_balance_cents, 0,
		(wb.available_balance_cents + wb.reserved_balance_cents)::bigint,
		'daily_reconciliation',
		jsonb_build_object('wallet_name', w.name, 'wallet_status', w.status),
		NULLIF($2, '')::uuid
	FROM wallet_balances wb
	JOIN wallets w ON w.id = wb.wallet_id
	ON CONFLICT (snapshot_date, subject_type, subject_id, currency) DO UPDATE
	SET owner_user_id = EXCLUDED.owner_user_id,
		balance_cents = EXCLUDED.balance_cents,
		available_balance_cents = EXCLUDED.available_balance_cents,
		reserved_balance_cents = EXCLUDED.reserved_balance_cents,
		debit_balance_cents = EXCLUDED.debit_balance_cents,
		credit_balance_cents = EXCLUDED.credit_balance_cents,
		source = EXCLUDED.source,
		metadata = EXCLUDED.metadata,
		created_by_admin_user_id = EXCLUDED.created_by_admin_user_id,
		created_at = now()
`

const upsertProviderSnapshotsSQL = `
	WITH ranked AS (
		SELECT pbs.*,
			ROW_NUMBER() OVER (
				PARTITION BY pbs.provider, pbs.external_account_id, pbs.currency
				ORDER BY pbs.as_of DESC, pbs.created_at DESC
			) AS rn
		FROM provider_balance_snapshots pbs
		WHERE pbs.as_of < $1::date + interval '1 day'
	)
	INSERT INTO end_of_day_balance_snapshots (
		snapshot_date, subject_type, subject_id, owner_user_id, provider, external_account_id,
		currency, balance_cents, available_balance_cents, reserved_balance_cents,
		debit_balance_cents, credit_balance_cents, source, metadata, created_by_admin_user_id
	)
	SELECT $1::date, 'provider_account',
		r.provider || ':' || r.external_account_id,
		a.user_id, r.provider, r.external_account_id, r.currency, r.balance_cents,
		r.balance_cents, 0, 0, GREATEST(r.balance_cents, 0),
		'daily_reconciliation',
		jsonb_build_object('provider_snapshot_id', r.id, 'as_of', r.as_of, 'snapshot_source', r.source),
		NULLIF($2, '')::uuid
	FROM ranked r
	LEFT JOIN accounts a ON a.bank_provider = r.provider
		AND a.external_account_id = r.external_account_id
		AND TRIM(a.currency)::text = TRIM(r.currency)::text
	WHERE r.rn = 1
	ON CONFLICT (snapshot_date, subject_type, subject_id, currency) DO UPDATE
	SET owner_user_id = EXCLUDED.owner_user_id,
		provider = EXCLUDED.provider,
		external_account_id = EXCLUDED.external_account_id,
		balance_cents = EXCLUDED.balance_cents,
		available_balance_cents = EXCLUDED.available_balance_cents,
		reserved_balance_cents = EXCLUDED.reserved_balance_cents,
		debit_balance_cents = EXCLUDED.debit_balance_cents,
		credit_balance_cents = EXCLUDED.credit_balance_cents,
		source = EXCLUDED.source,
		metadata = EXCLUDED.metadata,
		created_by_admin_user_id = EXCLUDED.created_by_admin_user_id,
		created_at = now()
`

const upsertLedgerAccountSnapshotsSQL = `
	WITH ledger_lines AS (
		SELECT jl.*
		FROM ledger_journal_lines jl
		JOIN ledger_journal_entries je ON je.id = jl.journal_entry_id
		WHERE je.created_at < $1::date + interval '1 day'
	),
	totals AS (
		SELECT la.id,
			COALESCE(SUM(CASE WHEN ll.direction = 'debit' THEN ll.amount_cents ELSE 0 END), 0)::bigint AS debit_cents,
			COALESCE(SUM(CASE WHEN ll.direction = 'credit' THEN ll.amount_cents ELSE 0 END), 0)::bigint AS credit_cents
		FROM ledger_accounts la
		LEFT JOIN ledger_lines ll ON ll.ledger_account_id = la.id
		GROUP BY la.id
	)
	INSERT INTO end_of_day_balance_snapshots (
		snapshot_date, subject_type, subject_id, owner_user_id, ledger_account_id,
		currency, balance_cents, debit_balance_cents, credit_balance_cents,
		source, metadata, created_by_admin_user_id
	)
	SELECT $1::date, 'ledger_account', la.id::text, la.owner_user_id, la.id,
		la.currency,
		CASE
			WHEN la.normal_balance = 'credit' THEN (t.credit_cents - t.debit_cents)
			ELSE (t.debit_cents - t.credit_cents)
		END,
		GREATEST(t.debit_cents - t.credit_cents, 0),
		GREATEST(t.credit_cents - t.debit_cents, 0),
		'daily_reconciliation',
		jsonb_build_object(
			'reference_type', la.reference_type,
			'reference_id', la.reference_id,
			'normal_balance', la.normal_balance,
			'status', la.status
		),
		NULLIF($2, '')::uuid
	FROM ledger_accounts la
	JOIN totals t ON t.id = la.id
	ON CONFLICT (snapshot_date, subject_type, subject_id, currency) DO UPDATE
	SET owner_user_id = EXCLUDED.owner_user_id,
		ledger_account_id = EXCLUDED.ledger_account_id,
		balance_cents = EXCLUDED.balance_cents,
		debit_balance_cents = EXCLUDED.debit_balance_cents,
		credit_balance_cents = EXCLUDED.credit_balance_cents,
		source = EXCLUDED.source,
		metadata = EXCLUDED.metadata,
		created_by_admin_user_id = EXCLUDED.created_by_admin_user_id,
		created_at = now()
`