package finance

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

	"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.
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)
}

func (r *Repository) CreateInvoice(ctx context.Context, q db.DBTX, p db.CreateInvoiceParams) (db.CreateInvoiceRow, error) {
	row, err := db.New(q).CreateInvoice(ctx, p)
	var pgErr *pgconn.PgError
	if errors.As(err, &pgErr) && pgErr.Code == "23505" && pgErr.ConstraintName == "invoices_sales_order_unique_idx" {
		return db.CreateInvoiceRow{}, ErrCannotInvoice
	}
	return row, err
}

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

func (r *Repository) InvoiceByID(ctx context.Context, id int64) (db.GetInvoiceByIDRow, error) {
	row, err := r.q.GetInvoiceByID(ctx, id)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetInvoiceByIDRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) LockInvoice(ctx context.Context, q db.DBTX, id int64) (db.GetInvoiceByIDForUpdateRow, error) {
	row, err := db.New(q).GetInvoiceByIDForUpdate(ctx, id)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetInvoiceByIDForUpdateRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) InvoiceItems(ctx context.Context, salesOrderID int64) ([]db.ListInvoiceItemsRow, error) {
	return r.q.ListInvoiceItems(ctx, salesOrderID)
}

func (r *Repository) ListInvoices(ctx context.Context, f InvoiceFilter) ([]db.ListInvoicesRow, int, error) {
	search := textOrNil(f.Search)
	status := textOrNil(f.Status)
	customerID := positiveInt8(f.CustomerID)
	storeID := positiveInt8(f.StoreID)
	dateFrom := dateOrNil(f.DateFrom)
	dateTo := dateOrNil(f.DateTo)
	total, err := r.q.CountInvoices(ctx, db.CountInvoicesParams{
		Search: search, Status: status, CustomerID: customerID, DateFrom: dateFrom, DateTo: dateTo, StoreID: storeID,
	})
	if err != nil {
		return nil, 0, err
	}
	rows, err := r.q.ListInvoices(ctx, db.ListInvoicesParams{
		Limit: int32(f.PerPage), Offset: int32((f.Page - 1) * f.PerPage),
		Search: search, Status: status, CustomerID: customerID, DateFrom: dateFrom, DateTo: dateTo, StoreID: storeID,
		SortBy: textOrNil(f.SortBy), SortDir: textOrNil(f.SortDir),
	})
	if err != nil {
		return nil, 0, err
	}
	return rows, int(total), nil
}

func (r *Repository) LockSO(ctx context.Context, q db.DBTX, id int64) (db.GetSOHeaderByIDForUpdateRow, error) {
	row, err := db.New(q).GetSOHeaderByIDForUpdate(ctx, id)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetSOHeaderByIDForUpdateRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) InvoiceExistsForSO(ctx context.Context, q db.DBTX, soID int64) (bool, error) {
	return db.New(q).InvoiceExistsForSO(ctx, pgtype.Int8{Int64: soID, Valid: true})
}

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

func (r *Repository) InsertSOStatusHistory(ctx context.Context, q db.DBTX, p db.InsertSOStatusHistoryParams) error {
	_, err := db.New(q).InsertSOStatusHistory(ctx, p)
	return err
}

func (r *Repository) InvoiceCreationStatusHistory(ctx context.Context, q db.DBTX, p db.GetInvoiceCreationStatusHistoryForUpdateParams) (db.SalesOrderStatusHistory, error) {
	row, err := db.New(q).GetInvoiceCreationStatusHistoryForUpdate(ctx, p)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.SalesOrderStatusHistory{}, ErrInvoiceCreationHistoryRequired
	}
	return row, err
}

func (r *Repository) PaymentsExistForInvoice(ctx context.Context, q db.DBTX, invoiceID int64) (bool, error) {
	return db.New(q).PaymentsExistForInvoice(ctx, invoiceID)
}

func (r *Repository) DeleteInvoice(ctx context.Context, q db.DBTX, id int64) error {
	n, err := db.New(q).DeleteInvoice(ctx, id)
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) ListReceivables(ctx context.Context, f ReceivableFilter) ([]db.ListReceivablesRow, int, decimal.Decimal, error) {
	customerID := positiveInt8(f.CustomerID)
	storeID := positiveInt8(f.StoreID)
	status := textOrNil(f.Status)
	filter := db.CountReceivablesParams{CustomerID: customerID, StoreID: storeID, Status: status}
	count, err := r.q.CountReceivables(ctx, filter)
	if err != nil {
		return nil, 0, decimal.Zero, err
	}
	total, err := r.q.SumReceivables(ctx, db.SumReceivablesParams{CustomerID: customerID, StoreID: storeID, Status: status})
	if err != nil {
		return nil, 0, decimal.Zero, err
	}
	rows, err := r.q.ListReceivables(ctx, db.ListReceivablesParams{
		Limit: int32(f.PerPage), Offset: int32((f.Page - 1) * f.PerPage),
		CustomerID: customerID, StoreID: storeID, Status: status,
	})
	if err != nil {
		return nil, 0, decimal.Zero, err
	}
	return rows, int(count), total, nil
}

func (r *Repository) CreatePayment(ctx context.Context, q db.DBTX, p db.CreatePaymentParams) (int64, error) {
	return db.New(q).CreatePayment(ctx, p)
}

// GetPaymentByID reads a payment (with its payable-owner linkage) outside a
// transaction — used for the proof-upload ownership check.
func (r *Repository) GetPaymentByID(ctx context.Context, id int64) (db.GetPaymentByIDForUpdateRow, error) {
	row, err := r.q.GetPaymentByIDForUpdate(ctx, id)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetPaymentByIDForUpdateRow{}, ErrNotFound
	}
	return row, err
}

// LockPayment locks a payment row inside tx so concurrent partial payments
// against the same payable serialize.
func (r *Repository) LockPayment(ctx context.Context, q db.DBTX, id int64) (db.GetPaymentByIDForUpdateRow, error) {
	row, err := db.New(q).GetPaymentByIDForUpdate(ctx, id)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetPaymentByIDForUpdateRow{}, ErrNotFound
	}
	return row, err
}

// proofURLPrefix is the prefix uploads.save prepends to a stored key, so a
// payment's proof_url is either the bare key or this prefix plus the key.
const proofURLPrefix = "/uploads/"

// PaymentProofOwnersByKey resolves the owning users for the payment proof
// stored under key. found=true with an empty owners slice means the key IS a
// proof but its payment's customer has no linked user.
//
// The query matches the stored proof_url against both shapes of the key, so it
// is answered by payments_proof_url_idx (000100) rather than a seq scan. The
// pair is built here instead of inside the SQL because comparing the column to
// the concatenated expression is what kept the old OR-shaped predicate
// unindexable; both candidates are plain bind values, which a btree can serve.
func (r *Repository) PaymentProofOwnersByKey(ctx context.Context, key string) ([]int64, bool, error) {
	rows, err := r.q.PaymentProofOwnersByKey(ctx, []string{key, proofURLPrefix + key})
	if err != nil {
		return nil, false, err
	}
	owners := make([]int64, 0, len(rows))
	for _, row := range rows {
		if row.Valid {
			owners = append(owners, row.Int64)
		}
	}
	return owners, len(rows) > 0, nil
}

func (r *Repository) SetPaymentProofURL(ctx context.Context, id int64, url string) (int64, error) {
	n, err := r.q.SetPaymentProofURL(ctx, db.SetPaymentProofURLParams{
		ID: id, ProofUrl: textOrNilStr(&url),
	})
	return n, err
}

func (r *Repository) SetPaymentReview(ctx context.Context, id int64, verdict string) (int64, error) {
	return r.q.SetPaymentReview(ctx, db.SetPaymentReviewParams{ID: id, Verdict: verdict})
}

func (r *Repository) ListPayments(ctx context.Context, f InvoiceFilter) ([]db.ListPaymentsRow, int, error) {
	search := textOrNil(f.Search)
	dateFrom := dateOrNil(f.DateFrom)
	dateTo := dateOrNil(f.DateTo)
	customerID := positiveInt8(f.CustomerID)
	storeID := positiveInt8(f.StoreID)
	payableID := positiveInt8(f.PayableID)
	payableType := textOrNil(f.PayableType)
	total, err := r.q.CountPayments(ctx, db.CountPaymentsParams{
		Search: search, DateFrom: dateFrom, DateTo: dateTo, CustomerID: customerID,
		StoreID: storeID, PayableID: payableID, PayableType: payableType,
	})
	if err != nil {
		return nil, 0, err
	}
	rows, err := r.q.ListPayments(ctx, db.ListPaymentsParams{
		Limit: int32(f.PerPage), Offset: int32((f.Page - 1) * f.PerPage), Search: search,
		DateFrom: dateFrom, DateTo: dateTo, CustomerID: customerID,
		StoreID: storeID, PayableID: payableID, PayableType: payableType,
	})
	if err != nil {
		return nil, 0, err
	}
	return rows, int(total), nil
}

// SumCustomerPayments resolves the buyer payment total + row count in one
// indexed scan (invoices.customer_id → payments). Date strings use the same
// YYYY-MM-DD shape as InvoiceFilter.
func (r *Repository) SumCustomerPayments(ctx context.Context, customerID int64, dateFrom, dateTo string) (db.SumCustomerPaymentsRow, error) {
	var from, to pgtype.Date
	if dateFrom != "" {
		if d, err := time.Parse("2006-01-02", dateFrom); err == nil {
			from = pgtype.Date{Time: d, Valid: true}
		}
	}
	if dateTo != "" {
		if d, err := time.Parse("2006-01-02", dateTo); err == nil {
			to = pgtype.Date{Time: d, Valid: true}
		}
	}
	return r.q.SumCustomerPayments(ctx, db.SumCustomerPaymentsParams{
		CustomerID: customerID, DateFrom: from, DateTo: to,
	})
}

func (r *Repository) CreateCashflow(ctx context.Context, q db.DBTX, p db.CreateCashflowParams) (int64, error) {
	id, err := db.New(q).CreateCashflow(ctx, p)
	return id, mapFinanceError(err)
}

// CashflowByID, LockCashflow and ListCashflows all take the caller's outlet
// scope (migration 000104). The scope is a []int64 where NIL means platform-wide
// and a non-nil slice restricts to those stores.
//
// The nil-vs-empty distinction is load-bearing and is not a sqlc artefact: pgx
// encodes a nil Go slice as SQL NULL, so the `IS NULL` arm of the predicate
// matches everything, while an empty non-nil slice encodes as '{}' and
// `= ANY('{}')` matches nothing. That is the fail-closed behaviour, and it is
// why the parameter is a slice rather than a boolean flag — a flag would have to
// remember the difference and eventually would not.
func (r *Repository) CashflowByID(ctx context.Context, id int64, storeIDs []int64) (db.GetCashflowByIDRow, error) {
	row, err := r.q.GetCashflowByID(ctx, db.GetCashflowByIDParams{ID: id, StoreIds: storeIDs})
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetCashflowByIDRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) LockCashflow(ctx context.Context, q db.DBTX, id int64, storeIDs []int64) (db.GetCashflowByIDForUpdateRow, error) {
	row, err := db.New(q).GetCashflowByIDForUpdate(ctx, db.GetCashflowByIDForUpdateParams{ID: id, StoreIds: storeIDs})
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetCashflowByIDForUpdateRow{}, ErrNotFound
	}
	return row, err
}

// CashflowAlreadyReversed reports whether a reversal row exists for the
// original cashflow id.
func (r *Repository) CashflowAlreadyReversed(ctx context.Context, q db.DBTX, id int64) (bool, error) {
	return db.New(q).CashflowAlreadyReversed(ctx, pgtype.Int8{Int64: id, Valid: true})
}

func (r *Repository) UpdateCashflow(ctx context.Context, q db.DBTX, p db.UpdateCashflowParams) (db.UpdateCashflowRow, error) {
	row, err := db.New(q).UpdateCashflow(ctx, p)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.UpdateCashflowRow{}, ErrNotFound
	}
	return row, mapFinanceError(err)
}

func (r *Repository) DeleteCashflow(ctx context.Context, q db.DBTX, id int64) error {
	n, err := db.New(q).DeleteCashflow(ctx, id)
	if err != nil {
		return mapFinanceError(err)
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

// ListCashflows carries f.StoreIDs into all THREE queries — the row list, the
// count, and the summary aggregate. The count and the summary are the reason
// this is written once here rather than in the service: a service that scoped
// the list and forgot the count would report the company's row count, and one
// that forgot the summary would report the company's totals while showing no
// rows.
func (r *Repository) ListCashflows(ctx context.Context, f CashflowFilter) ([]db.ListCashflowsRow, int, db.SummarizeCashflowsRow, error) {
	typeFilter := textOrNil(f.Type)
	category := textOrNil(f.Category)
	dateFrom := dateOrNil(f.DateFrom)
	dateTo := dateOrNil(f.DateTo)
	filter := db.SummarizeCashflowsParams{
		Type: typeFilter, Category: category, DateFrom: dateFrom, DateTo: dateTo, StoreIds: f.StoreIDs,
	}
	summary, err := r.q.SummarizeCashflows(ctx, filter)
	if err != nil {
		return nil, 0, db.SummarizeCashflowsRow{}, err
	}
	total, err := r.q.CountCashflows(ctx, db.CountCashflowsParams(filter))
	if err != nil {
		return nil, 0, db.SummarizeCashflowsRow{}, err
	}
	rows, err := r.q.ListCashflows(ctx, db.ListCashflowsParams{
		Limit: int32(f.PerPage), Offset: int32((f.Page - 1) * f.PerPage),
		Type: typeFilter, Category: category, DateFrom: dateFrom, DateTo: dateTo, StoreIds: f.StoreIDs,
	})
	if err != nil {
		return nil, 0, db.SummarizeCashflowsRow{}, err
	}
	return rows, int(total), summary, nil
}

func positiveInt8(v int64) pgtype.Int8 {
	return pgtype.Int8{Int64: v, Valid: v > 0}
}

func mapFinanceError(err error) error {
	var pgErr *pgconn.PgError
	if !errors.As(err, &pgErr) {
		return err
	}
	switch pgErr.Message {
	case "accounting_period_locked":
		return ErrAccountingPeriodLocked
	case "generated_cashflow_immutable":
		return ErrGeneratedCashflowImmutable
	default:
		return err
	}
}

func periodTime(v pgtype.Timestamptz) *time.Time {
	if !v.Valid {
		return nil
	}
	t := v.Time
	return &t
}
func nullableInt(v pgtype.Int8) *int64 {
	if !v.Valid {
		return nil
	}
	x := v.Int64
	return &x
}
func nullableText(v pgtype.Text) *string {
	if !v.Valid {
		return nil
	}
	x := v.String
	return &x
}
func parseJSONMap(raw []byte) map[string]any {
	if len(raw) == 0 {
		return nil
	}
	var m map[string]any
	if err := json.Unmarshal(raw, &m); err != nil {
		return nil
	}
	return m
}

func (r *Repository) ListAccountingPeriods(ctx context.Context) ([]AccountingPeriod, error) {
	rows, err := r.pool.Query(ctx, `SELECT id, period_year, period_month, status, locked_at, locked_by, reopened_at, reopened_by, reopen_reason, close_snapshot, created_at, updated_at FROM accounting_periods ORDER BY period_year DESC, period_month DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []AccountingPeriod{}
	for rows.Next() {
		var p AccountingPeriod
		var la, ra pgtype.Timestamptz
		var lb, rb pgtype.Int8
		var rr pgtype.Text
		var snap []byte
		var ca, ua pgtype.Timestamptz
		if err := rows.Scan(&p.ID, &p.PeriodYear, &p.PeriodMonth, &p.Status, &la, &lb, &ra, &rb, &rr, &snap, &ca, &ua); err != nil {
			return nil, err
		}
		p.LockedAt = periodTime(la)
		p.LockedBy = nullableInt(lb)
		p.ReopenedAt = periodTime(ra)
		p.ReopenedBy = nullableInt(rb)
		p.ReopenReason = nullableText(rr)
		p.Snapshot = parseJSONMap(snap)
		p.CreatedAt = ca.Time
		p.UpdatedAt = ua.Time
		p.StartDate = time.Date(int(p.PeriodYear), time.Month(p.PeriodMonth), 1, 0, 0, 0, 0, time.UTC).Format("2006-01-02")
		p.EndDate = time.Date(int(p.PeriodYear), time.Month(p.PeriodMonth)+1, 0, 23, 59, 59, 0, time.UTC).Format("2006-01-02")
		out = append(out, p)
	}
	return out, rows.Err()
}

func (r *Repository) AccountingPeriod(ctx context.Context, y, m int16) (AccountingPeriod, error) {
	var p AccountingPeriod
	var la, ra pgtype.Timestamptz
	var lb, rb pgtype.Int8
	var rr pgtype.Text
	var snap []byte
	var ca, ua pgtype.Timestamptz
	err := r.pool.QueryRow(ctx, `SELECT id, period_year, period_month, status, locked_at, locked_by, reopened_at, reopened_by, reopen_reason, close_snapshot, created_at, updated_at FROM accounting_periods WHERE period_year=$1 AND period_month=$2`, y, m).Scan(
		&p.ID, &p.PeriodYear, &p.PeriodMonth, &p.Status, &la, &lb, &ra, &rb, &rr, &snap, &ca, &ua,
	)
	if errors.Is(err, pgx.ErrNoRows) {
		return p, ErrNotFound
	}
	if err != nil {
		return p, err
	}
	p.LockedAt = periodTime(la)
	p.LockedBy = nullableInt(lb)
	p.ReopenedAt = periodTime(ra)
	p.ReopenedBy = nullableInt(rb)
	p.ReopenReason = nullableText(rr)
	p.Snapshot = parseJSONMap(snap)
	p.CreatedAt = ca.Time
	p.UpdatedAt = ua.Time
	p.StartDate = time.Date(int(p.PeriodYear), time.Month(p.PeriodMonth), 1, 0, 0, 0, 0, time.UTC).Format("2006-01-02")
	p.EndDate = time.Date(int(p.PeriodYear), time.Month(p.PeriodMonth)+1, 0, 23, 59, 59, 0, time.UTC).Format("2006-01-02")

	// Load audit history
	logs, err := r.AccountingPeriodHistory(ctx, y, m)
	if err == nil {
		p.History = logs
	}
	return p, nil
}

func (r *Repository) AccountingPeriodSummary(ctx context.Context, y, m int16) (PeriodSummary, error) {
	return r.accountingPeriodSummary(ctx, r.pool, y, m)
}

func (r *Repository) accountingPeriodSummary(ctx context.Context, q db.DBTX, y, m int16) (PeriodSummary, error) {
	var s PeriodSummary
	start := time.Date(int(y), time.Month(m), 1, 0, 0, 0, 0, time.UTC)
	end := start.AddDate(0, 1, 0)
	if err := q.QueryRow(ctx, `SELECT COALESCE(SUM(amount) FILTER (WHERE type='income'), 0), COALESCE(SUM(amount) FILTER (WHERE type='expense'), 0), COUNT(*) FROM cashflows WHERE transaction_date >= $1 AND transaction_date < $2`, start, end).Scan(&s.CashflowIn, &s.CashflowOut, &s.CashflowCount); err != nil {
		return s, err
	}
	s.NetCashflow = s.CashflowIn.Sub(s.CashflowOut)

	if err := q.QueryRow(ctx, `SELECT COUNT(*), COALESCE(SUM(total_amount), 0) FROM sales_orders WHERE status IN ('confirmed','picking','shipped','delivered','invoiced','paid') AND created_at >= $1 AND created_at < $2`, start, end).Scan(&s.SalesCount, &s.SalesTotal); err != nil {
		return s, err
	}

	var unbalancedJournals int64
	if err := q.QueryRow(ctx, `
		WITH journal_totals AS (
			SELECT e.id, e.total_debit, e.total_credit,
			       COALESCE(SUM(l.debit), 0) AS line_debit,
			       COALESCE(SUM(l.credit), 0) AS line_credit
			FROM journal_entries e
			LEFT JOIN journal_lines l ON l.journal_entry_id = e.id
			WHERE e.status IN ('posted','reversed') AND e.entry_date >= $1 AND e.entry_date < $2
			GROUP BY e.id
		)
		SELECT COUNT(*), COALESCE(SUM(line_debit), 0), COALESCE(SUM(line_credit), 0),
		       COUNT(*) FILTER (WHERE total_debit <> line_debit OR total_credit <> line_credit OR line_debit <> line_credit)
		FROM journal_totals`, start, end).Scan(&s.JournalCount, &s.JournalDebit, &s.JournalCredit, &unbalancedJournals); err != nil {
		return s, err
	}
	if err := q.QueryRow(ctx, `SELECT COUNT(*) FROM journal_entries WHERE status = 'draft' AND entry_date >= $1 AND entry_date < $2`, start, end).Scan(&s.UnpostedJournals); err != nil {
		return s, err
	}
	s.IsBalanced = unbalancedJournals == 0 && s.JournalDebit.Equal(s.JournalCredit)
	return s, nil
}

func (r *Repository) ValidateAccountingPeriod(ctx context.Context, y, m int16) (PeriodValidationResult, error) {
	summary, err := r.AccountingPeriodSummary(ctx, y, m)
	if err != nil {
		return PeriodValidationResult{}, err
	}

	period, err := r.AccountingPeriod(ctx, y, m)
	if err != nil && !errors.Is(err, ErrNotFound) {
		return PeriodValidationResult{}, err
	}

	var issues []string
	if period.Status == "locked" {
		issues = append(issues, "Periode ini sudah dalam status terkunci")
	}
	if summary.UnpostedJournals > 0 {
		issues = append(issues, fmt.Sprintf("Terdapat %d jurnal draft yang belum diposting", summary.UnpostedJournals))
	}
	if !summary.IsBalanced && summary.JournalCount > 0 {
		issues = append(issues, fmt.Sprintf("Jurnal tidak seimbang: Total Debit (%s) tidak sama dengan Kredit (%s)", summary.JournalDebit.String(), summary.JournalCredit.String()))
	}

	return PeriodValidationResult{
		CanClose: len(issues) == 0 && period.Status != "locked",
		Issues:   issues,
		Summary:  summary,
	}, nil
}

func (r *Repository) CloseAccountingPeriod(ctx context.Context, y, m int16, actor int64) (AccountingPeriod, error) {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return AccountingPeriod{}, err
	}
	defer tx.Rollback(ctx)

	var status string
	var existingSnap []byte
	err = tx.QueryRow(ctx, `SELECT status, close_snapshot FROM accounting_periods WHERE period_year=$1 AND period_month=$2 FOR UPDATE`, y, m).Scan(&status, &existingSnap)
	if errors.Is(err, pgx.ErrNoRows) {
		return AccountingPeriod{}, ErrNotFound
	}
	if err != nil {
		return AccountingPeriod{}, err
	}
	if status == "locked" {
		// Idempotent: return existing locked record
		if err := tx.Commit(ctx); err != nil {
			return AccountingPeriod{}, err
		}
		return r.AccountingPeriod(ctx, y, m)
	}

	// Re-run blocking checks under the close transaction. Posting attempts and close serialize on this period row.
	summary, err := r.accountingPeriodSummary(ctx, tx, y, m)
	if err != nil {
		return AccountingPeriod{}, err
	}
	if summary.UnpostedJournals > 0 {
		return AccountingPeriod{}, fmt.Errorf("%w: %d draft journals remain", ErrPeriodCloseBlocked, summary.UnpostedJournals)
	}
	if !summary.IsBalanced {
		return AccountingPeriod{}, ErrPeriodCloseBlocked
	}

	var actorName string
	_ = tx.QueryRow(ctx, `SELECT name FROM users WHERE id=$1`, actor).Scan(&actorName)

	snapshotData := map[string]any{
		"closed_at":         time.Now().UTC().Format(time.RFC3339),
		"closed_by_user_id": actor,
		"closed_by_name":    actorName,
		"period_year":       y,
		"period_month":      m,
		"cashflow_in":       summary.CashflowIn.String(),
		"cashflow_out":      summary.CashflowOut.String(),
		"net_cashflow":      summary.NetCashflow.String(),
		"cashflow_count":    summary.CashflowCount,
		"sales_count":       summary.SalesCount,
		"sales_total":       summary.SalesTotal.String(),
		"journal_count":     summary.JournalCount,
		"journal_debit":     summary.JournalDebit.String(),
		"journal_credit":    summary.JournalCredit.String(),
		"unposted_journals": summary.UnpostedJournals,
		"is_balanced":       summary.IsBalanced,
	}
	rawSnap, err := json.Marshal(snapshotData)
	if err != nil {
		return AccountingPeriod{}, err
	}

	_, err = tx.Exec(ctx, `UPDATE accounting_periods SET status='locked', locked_at=now(), locked_by=$3, close_snapshot=$4, updated_at=now() WHERE period_year=$1 AND period_month=$2`, y, m, actor, rawSnap)
	if err != nil {
		return AccountingPeriod{}, err
	}

	_, err = tx.Exec(ctx, `INSERT INTO accounting_period_logs (period_year, period_month, action, actor_id, snapshot, created_at) VALUES ($1, $2, 'close', $3, $4, now())`, y, m, actor, rawSnap)
	if err != nil {
		return AccountingPeriod{}, err
	}

	if err = tx.Commit(ctx); err != nil {
		return AccountingPeriod{}, err
	}
	return r.AccountingPeriod(ctx, y, m)
}

func (r *Repository) ReopenAccountingPeriod(ctx context.Context, y, m int16, actor int64, reason string) (AccountingPeriod, error) {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return AccountingPeriod{}, err
	}
	defer tx.Rollback(ctx)

	var status string
	err = tx.QueryRow(ctx, `SELECT status FROM accounting_periods WHERE period_year=$1 AND period_month=$2 FOR UPDATE`, y, m).Scan(&status)
	if errors.Is(err, pgx.ErrNoRows) {
		return AccountingPeriod{}, ErrNotFound
	}
	if err != nil {
		return AccountingPeriod{}, err
	}
	if status != "locked" {
		// Idempotent: period is already open or reopened
		if err := tx.Commit(ctx); err != nil {
			return AccountingPeriod{}, err
		}
		return r.AccountingPeriod(ctx, y, m)
	}

	_, err = tx.Exec(ctx, `UPDATE accounting_periods SET status='reopened', reopened_at=now(), reopened_by=$3, reopen_reason=$4, updated_at=now() WHERE period_year=$1 AND period_month=$2`, y, m, actor, reason)
	if err != nil {
		return AccountingPeriod{}, err
	}

	_, err = tx.Exec(ctx, `INSERT INTO accounting_period_logs (period_year, period_month, action, actor_id, reason, created_at) VALUES ($1, $2, 'reopen', $3, $4, now())`, y, m, actor, reason)
	if err != nil {
		return AccountingPeriod{}, err
	}

	if err = tx.Commit(ctx); err != nil {
		return AccountingPeriod{}, err
	}
	return r.AccountingPeriod(ctx, y, m)
}

func (r *Repository) AccountingPeriodHistory(ctx context.Context, y, m int16) ([]AccountingPeriodLog, error) {
	rows, err := r.pool.Query(ctx, `
		SELECT l.id, l.period_year, l.period_month, l.action, l.actor_id, COALESCE(u.name, ''), l.reason, l.snapshot, l.created_at
		FROM accounting_period_logs l
		LEFT JOIN users u ON u.id = l.actor_id
		WHERE l.period_year = $1 AND l.period_month = $2
		ORDER BY l.created_at DESC
	`, y, m)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var logs []AccountingPeriodLog
	for rows.Next() {
		var log AccountingPeriodLog
		var rson pgtype.Text
		var snap []byte
		var ca pgtype.Timestamptz
		if err := rows.Scan(&log.ID, &log.PeriodYear, &log.PeriodMonth, &log.Action, &log.ActorID, &log.ActorName, &rson, &snap, &ca); err != nil {
			return nil, err
		}
		log.Reason = nullableText(rson)
		log.Snapshot = parseJSONMap(snap)
		log.CreatedAt = ca.Time
		logs = append(logs, log)
	}
	return logs, rows.Err()
}