// Bank Reconciliation Data Access Functions

import { pgQuery } from './client';
import type {
  BankReconciliationPeriod,
  BankTransaction,
  LedgerTransaction,
  ReconciliationMatch,
  BankReconciliationDifference,
} from '@/lib/types';

type PgPagination = {
  page?: number;
  pageSize?: number;
};

function toIso(value: unknown): string {
  if (value instanceof Date) return value.toISOString();
  if (typeof value === 'string') return value;
  return new Date().toISOString();
}

// ============================================================================
// Bank Reconciliation Periods
// ============================================================================

export async function pgCreateBankReconciliationPeriod(data: Omit<BankReconciliationPeriod, 'id' | 'createdAt' | 'updatedAt'>): Promise<BankReconciliationPeriod> {
  const id = `recon-${new Date().getTime()}-${Math.random().toString(36).substring(7)}`;

  await pgQuery(
    `
    INSERT INTO bank_reconciliation_periods (
      id, bank_account_id, period_start_date, period_end_date,
      statement_start_balance, statement_end_balance,
      status, created_by, raw, updated_at
    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9::jsonb, NOW())
    `,
    [
      id,
      data.bankAccountId,
      data.periodStartDate,
      data.periodEndDate,
      data.statementStartBalance || null,
      data.statementEndBalance,
      data.status || 'draft',
      data.createdBy,
      JSON.stringify(data),
    ]
  );

  return {
    ...data,
    id,
    createdAt: new Date().toISOString(),
    updatedAt: new Date().toISOString(),
  };
}

export async function pgGetBankReconciliationPeriodById(id: string): Promise<BankReconciliationPeriod | null> {
  const result = await pgQuery<{
    id: string;
    bank_account_id: string;
    raw: Record<string, any>;
    created_at: unknown;
    updated_at: unknown;
  }>(
    'SELECT id, bank_account_id, raw, created_at, updated_at FROM bank_reconciliation_periods WHERE id = $1',
    [id]
  );

  if (!result.rows || result.rows.length === 0) return null;
  const row = result.rows[0];
  const raw = row.raw || {};
  return {
    ...raw,
    id: row.id,
    bankAccountId: raw.bankAccountId || row.bank_account_id,
    createdAt: raw.createdAt || toIso(row.created_at),
    updatedAt: raw.updatedAt || toIso(row.updated_at),
  } as BankReconciliationPeriod;
}

export async function pgListBankReconciliationPeriods(
  bankAccountId: string,
  params: PgPagination = {}
): Promise<{ items: BankReconciliationPeriod[]; total: number; page: number; pageSize: number }> {
  const page = Math.max(1, params.page || 1);
  const pageSize = Math.max(1, Math.min(1000, params.pageSize || 50));
  const offset = (page - 1) * pageSize;

  const rows = await pgQuery<{
    id: string;
    bank_account_id: string;
    raw: Record<string, any>;
    created_at: unknown;
    updated_at: unknown;
  }>(
    `
    SELECT id, bank_account_id, raw, created_at, updated_at FROM bank_reconciliation_periods
    WHERE bank_account_id = $1
    ORDER BY period_end_date DESC, created_at DESC
    LIMIT $2 OFFSET $3
    `,
    [bankAccountId, pageSize, offset]
  );

  const countResult = await pgQuery<{ total: string }>(
    'SELECT COUNT(*)::text as total FROM bank_reconciliation_periods WHERE bank_account_id = $1',
    [bankAccountId]
  );

  return {
    items: rows.rows.map((r) => {
      const raw = r.raw || {};
      return {
        ...raw,
        id: r.id,
        bankAccountId: raw.bankAccountId || r.bank_account_id,
        createdAt: raw.createdAt || toIso(r.created_at),
        updatedAt: raw.updatedAt || toIso(r.updated_at),
      } as BankReconciliationPeriod;
    }),
    total: Number(countResult.rows[0]?.total || 0),
    page,
    pageSize,
  };
}

export async function pgUpdateBankReconciliationPeriod(id: string, updates: Partial<BankReconciliationPeriod>): Promise<BankReconciliationPeriod | null> {
  const existing = await pgGetBankReconciliationPeriodById(id);
  if (!existing) return null;

  const updated = { ...existing, ...updates, updatedAt: new Date().toISOString() };

  await pgQuery(
    `
    UPDATE bank_reconciliation_periods
    SET status = $2, locked_at = $3, locked_by = $4, completed_at = $5, completed_by = $6,
        reconciliation_difference = $7, approved_by = $8, approved_at = $9, notes = $10,
        raw = $11::jsonb, updated_at = NOW()
    WHERE id = $1
    `,
    [
      id,
      updated.status,
      updated.lockedAt || null,
      updated.lockedBy || null,
      updated.completedAt || null,
      updated.completedBy || null,
      updated.reconciliationDifference || null,
      updated.approvedBy || null,
      updated.approvedAt || null,
      updated.notes || null,
      JSON.stringify(updated),
    ]
  );

  return updated;
}

// ============================================================================
// Bank Transactions
// ============================================================================

export async function pgAddBankTransaction(data: Omit<BankTransaction, 'id' | 'createdAt' | 'updatedAt'>): Promise<BankTransaction> {
  const id = `btxn-${new Date().getTime()}-${Math.random().toString(36).substring(7)}`;

  await pgQuery(
    `
    INSERT INTO bank_transactions (
      id, reconciliation_period_id, bank_account_id, transaction_date,
      amount, direction, description, bank_reference, type, currency_code, raw
    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11::jsonb)
    `,
    [
      id,
      data.reconciliationPeriodId,
      data.bankAccountId,
      data.transactionDate,
      data.amount,
      data.direction,
      data.description || null,
      data.bankReference || null,
      data.type || null,
      data.currencyCode || null,
      JSON.stringify(data),
    ]
  );

  return {
    ...data,
    id,
    createdAt: new Date().toISOString(),
    updatedAt: new Date().toISOString(),
  };
}

export async function pgGetBankTransactionsForPeriod(reconciliationPeriodId: string): Promise<BankTransaction[]> {
  const result = await pgQuery<{
    id: string;
    reconciliation_period_id: string;
    bank_account_id: string;
    raw: Record<string, any>;
    created_at: unknown;
    updated_at: unknown;
  }>(
    `
    SELECT id, reconciliation_period_id, bank_account_id, raw, created_at, updated_at FROM bank_transactions
    WHERE reconciliation_period_id = $1
    ORDER BY transaction_date, created_at
    `,
    [reconciliationPeriodId]
  );

  return result.rows.map((r) => {
    const raw = r.raw || {};
    return {
      ...raw,
      id: r.id,
      reconciliationPeriodId: raw.reconciliationPeriodId || r.reconciliation_period_id,
      bankAccountId: raw.bankAccountId || r.bank_account_id,
      createdAt: raw.createdAt || toIso(r.created_at),
      updatedAt: raw.updatedAt || toIso(r.updated_at),
    } as BankTransaction;
  });
}

export async function pgBatchAddBankTransactions(transactions: Omit<BankTransaction, 'id' | 'createdAt' | 'updatedAt'>[]): Promise<BankTransaction[]> {
  if (transactions.length === 0) return [];

  const created: BankTransaction[] = [];
  for (const data of transactions) {
    const result = await pgAddBankTransaction(data);
    created.push(result);
  }

  return created;
}

// ============================================================================
// Ledger Transactions
// ============================================================================

export async function pgAddLedgerTransaction(data: Omit<LedgerTransaction, 'id' | 'createdAt' | 'updatedAt'>): Promise<LedgerTransaction> {
  const id = `ltxn-${new Date().getTime()}-${Math.random().toString(36).substring(7)}`;

  await pgQuery(
    `
    INSERT INTO ledger_transactions (
      id, bank_account_id, journal_entry_id, journal_entry_line_idx,
      transaction_date, amount, direction, description, reference_type, reference_id,
      posting_status, reconciliation_status, reconciliation_period_id,
      currency_code, fx_rate, raw
    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16::jsonb)
    `,
    [
      id,
      data.bankAccountId,
      data.journalEntryId,
      data.journalEntryLineIdx,
      data.transactionDate,
      data.amount,
      data.direction,
      data.description || null,
      data.referenceType || null,
      data.referenceId || null,
      data.postingStatus,
      data.reconciliationStatus,
      data.reconciliationPeriodId || null,
      data.currencyCode || null,
      data.fxRate,
      JSON.stringify(data),
    ]
  );

  return {
    ...data,
    id,
    createdAt: new Date().toISOString(),
    updatedAt: new Date().toISOString(),
  };
}

export async function pgGetLedgerTransactionsForBankAccount(bankAccountId: string): Promise<LedgerTransaction[]> {
  const result = await pgQuery<{
    id: string;
    bank_account_id: string;
    raw: Record<string, any>;
    created_at: unknown;
    updated_at: unknown;
  }>(
    `
    SELECT id, bank_account_id, raw, created_at, updated_at FROM ledger_transactions
    WHERE bank_account_id = $1 AND reconciliation_status = 'unreconciled'
    ORDER BY transaction_date, created_at
    `,
    [bankAccountId]
  );

  return result.rows.map((r) => {
    const raw = r.raw || {};
    return {
      ...raw,
      id: r.id,
      bankAccountId: raw.bankAccountId || r.bank_account_id,
      createdAt: raw.createdAt || toIso(r.created_at),
      updatedAt: raw.updatedAt || toIso(r.updated_at),
    } as LedgerTransaction;
  });
}

export async function pgUpdateLedgerTransactionReconciliationStatus(
  id: string,
  status: 'unreconciled' | 'reconciled' | 'partial' | 'disputed',
  periodId?: string
): Promise<boolean> {
  const result = await pgQuery(
    `
    UPDATE ledger_transactions
    SET reconciliation_status = $2, reconciliation_period_id = $3, last_reconciliation_date = NOW(), updated_at = NOW()
    WHERE id = $1
    `,
    [id, status, periodId || null]
  );

  return (result.rowCount || 0) > 0;
}

// ============================================================================
// Reconciliation Matches
// ============================================================================

export async function pgCreateReconciliationMatch(data: Omit<ReconciliationMatch, 'id' | 'createdAt' | 'updatedAt'>): Promise<ReconciliationMatch> {
  const id = `rmatch-${new Date().getTime()}-${Math.random().toString(36).substring(7)}`;

  await pgQuery(
    `
    INSERT INTO reconciliation_matches (
      id, reconciliation_period_id, bank_transaction_id, ledger_transaction_ids,
      match_type, match_confidence, matched_by, notes, status, raw
    ) VALUES ($1, $2, $3, $4::TEXT[], $5, $6, $7, $8, $9, $10::jsonb)
    `,
    [
      id,
      data.reconciliationPeriodId,
      data.bankTransactionId,
      data.ledgerTransactionIds,
      data.matchType,
      data.matchConfidence,
      data.matchedBy,
      data.notes || null,
      data.status,
      JSON.stringify(data),
    ]
  );

  return {
    ...data,
    id,
    createdAt: new Date().toISOString(),
    updatedAt: new Date().toISOString(),
  };
}

export async function pgGetReconciliationMatchesForPeriod(reconciliationPeriodId: string): Promise<ReconciliationMatch[]> {
  const result = await pgQuery<{
    id: string;
    reconciliation_period_id: string;
    bank_transaction_id: string;
    raw: Record<string, any>;
    ledger_transaction_ids: string[];
    created_at: unknown;
    updated_at: unknown;
  }>(
    `
    SELECT id, reconciliation_period_id, bank_transaction_id, raw, ledger_transaction_ids, created_at, updated_at FROM reconciliation_matches
    WHERE reconciliation_period_id = $1
    ORDER BY match_date DESC
    `,
    [reconciliationPeriodId]
  );

  return result.rows.map((r) => {
    const match = r.raw as ReconciliationMatch;
    match.id = r.id;
    match.reconciliationPeriodId = match.reconciliationPeriodId || r.reconciliation_period_id;
    match.bankTransactionId = match.bankTransactionId || r.bank_transaction_id;
    match.createdAt = match.createdAt || toIso(r.created_at);
    match.updatedAt = match.updatedAt || toIso(r.updated_at);
    match.ledgerTransactionIds = r.ledger_transaction_ids || [];
    return match;
  });
}

export async function pgGetReconciliationMatchForBankTransaction(bankTransactionId: string): Promise<ReconciliationMatch | null> {
  const result = await pgQuery<{
    id: string;
    reconciliation_period_id: string;
    bank_transaction_id: string;
    raw: Record<string, any>;
    ledger_transaction_ids: string[];
    created_at: unknown;
    updated_at: unknown;
  }>(
    'SELECT id, reconciliation_period_id, bank_transaction_id, raw, ledger_transaction_ids, created_at, updated_at FROM reconciliation_matches WHERE bank_transaction_id = $1',
    [bankTransactionId]
  );

  if (!result.rows || result.rows.length === 0) return null;
  const row = result.rows[0];
  const raw = row.raw || {};
  return {
    ...raw,
    id: row.id,
    reconciliationPeriodId: raw.reconciliationPeriodId || row.reconciliation_period_id,
    bankTransactionId: raw.bankTransactionId || row.bank_transaction_id,
    ledgerTransactionIds: row.ledger_transaction_ids || raw.ledgerTransactionIds || [],
    createdAt: raw.createdAt || toIso(row.created_at),
    updatedAt: raw.updatedAt || toIso(row.updated_at),
  } as ReconciliationMatch;
}

// ============================================================================
// Bank Reconciliation Differences
// ============================================================================

export async function pgAddBankReconciliationDifference(data: Omit<BankReconciliationDifference, 'id' | 'createdAt' | 'updatedAt'>): Promise<BankReconciliationDifference> {
  const id = `rdiff-${new Date().getTime()}-${Math.random().toString(36).substring(7)}`;

  await pgQuery(
    `
    INSERT INTO bank_reconciliation_differences (
      id, reconciliation_period_id, type, amount, description,
      bank_transaction_id, ledger_transaction_id, resolution_type,
      notes, raw
    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10::jsonb)
    `,
    [
      id,
      data.reconciliationPeriodId,
      data.type,
      data.amount,
      data.description || null,
      data.bankTransactionId || null,
      data.ledgerTransactionId || null,
      data.resolutionType || null,
      data.notes || null,
      JSON.stringify(data),
    ]
  );

  return {
    ...data,
    id,
    createdAt: new Date().toISOString(),
    updatedAt: new Date().toISOString(),
  };
}

export async function pgGetBankReconciliationDifferencesForPeriod(reconciliationPeriodId: string): Promise<BankReconciliationDifference[]> {
  const result = await pgQuery<{
    id: string;
    reconciliation_period_id: string;
    raw: Record<string, any>;
    created_at: unknown;
    updated_at: unknown;
  }>(
    `
    SELECT id, reconciliation_period_id, raw, created_at, updated_at FROM bank_reconciliation_differences
    WHERE reconciliation_period_id = $1
    ORDER BY created_at DESC
    `,
    [reconciliationPeriodId]
  );

  return result.rows.map((r) => {
    const raw = r.raw || {};
    return {
      ...raw,
      id: r.id,
      reconciliationPeriodId: raw.reconciliationPeriodId || r.reconciliation_period_id,
      createdAt: raw.createdAt || toIso(r.created_at),
      updatedAt: raw.updatedAt || toIso(r.updated_at),
    } as BankReconciliationDifference;
  });
}

export async function pgResolveBankReconciliationDifference(
  id: string,
  resolutionType: 'pending' | 'identified' | 'adjusted' | 'write_off' | 'reversed',
  resolvedBy: string,
  adjustmentEntryId?: string
): Promise<boolean> {
  const result = await pgQuery(
    `
    UPDATE bank_reconciliation_differences
    SET resolution_type = $2, resolution_date = NOW(), resolved_by = $3,
        adjustment_entry_id = $4, updated_at = NOW()
    WHERE id = $1
    `,
    [id, resolutionType, resolvedBy, adjustmentEntryId || null]
  );

  return (result.rowCount || 0) > 0;
}

// ============================================================================
// Additional helpers
// ============================================================================

/** Delete a reconciliation match and revert the ledger transaction status to unreconciled */
export async function pgDeleteReconciliationMatch(matchId: string): Promise<boolean> {
  // First get the match to know which ledger txns to revert
  const matchResult = await pgQuery<{
    id: string;
    ledger_transaction_ids: string[];
  }>(
    'SELECT id, ledger_transaction_ids FROM reconciliation_matches WHERE id = $1',
    [matchId]
  );

  if (!matchResult.rows || matchResult.rows.length === 0) return false;

  const ledgerIds: string[] = matchResult.rows[0].ledger_transaction_ids || [];

  // Revert each ledger transaction
  for (const ledgerId of ledgerIds) {
    await pgQuery(
      `UPDATE ledger_transactions
       SET reconciliation_status = 'unreconciled', reconciliation_period_id = NULL,
           last_reconciliation_date = NULL, updated_at = NOW()
       WHERE id = $1`,
      [ledgerId]
    );
  }

  const deleteResult = await pgQuery(
    'DELETE FROM reconciliation_matches WHERE id = $1',
    [matchId]
  );

  return (deleteResult.rowCount || 0) > 0;
}

/** Get ALL ledger transactions for a bank account (both reconciled and unreconciled) */
export async function pgGetAllLedgerTransactionsForBankAccount(bankAccountId: string): Promise<LedgerTransaction[]> {
  const result = await pgQuery<{
    id: string;
    bank_account_id: string;
    raw: Record<string, any>;
    created_at: unknown;
    updated_at: unknown;
  }>(
    `SELECT id, bank_account_id, raw, created_at, updated_at FROM ledger_transactions
     WHERE bank_account_id = $1
     ORDER BY transaction_date, created_at`,
    [bankAccountId]
  );

  return result.rows.map((r) => {
    const raw = r.raw || {};
    return {
      ...raw,
      id: r.id,
      bankAccountId: raw.bankAccountId || r.bank_account_id,
      createdAt: raw.createdAt || toIso(r.created_at),
      updatedAt: raw.updatedAt || toIso(r.updated_at),
    } as LedgerTransaction;
  });
}

/** Update period status (lock / complete / reopen) */
export async function pgSetBankReconciliationPeriodStatus(
  id: string,
  status: 'draft' | 'in-progress' | 'completed' | 'locked',
  actorId: string,
  reconciliationDifference?: number
): Promise<BankReconciliationPeriod | null> {
  const existing = await pgGetBankReconciliationPeriodById(id);
  if (!existing) return null;

  const now = new Date().toISOString();
  const updated: BankReconciliationPeriod = {
    ...existing,
    status,
    updatedAt: now,
    reconciliationDifference: reconciliationDifference ?? existing.reconciliationDifference,
    ...(status === 'locked' ? { lockedAt: now, lockedBy: actorId } : {}),
    ...(status === 'completed' ? { completedAt: now, completedBy: actorId } : {}),
    ...(status === 'draft' || status === 'in-progress' ? { lockedAt: undefined, lockedBy: undefined } : {}),
  };

  await pgQuery(
    `UPDATE bank_reconciliation_periods
     SET status = $2, locked_at = $3, locked_by = $4, completed_at = $5, completed_by = $6,
         reconciliation_difference = $7, raw = $8::jsonb, updated_at = NOW()
     WHERE id = $1`,
    [
      id,
      updated.status,
      updated.lockedAt || null,
      updated.lockedBy || null,
      updated.completedAt || null,
      updated.completedBy || null,
      updated.reconciliationDifference ?? null,
      JSON.stringify(updated),
    ]
  );

  return updated;
}
