import { pgQuery } from './client';
import { pgSaveJournalEntry } from './data-access';
import type {
  ExpenseDocument,
  ExpenseDocumentHistoryAction,
  ExpenseDocumentHistoryEntry,
  ExpenseDocumentStatus,
  ExpensePayment,
  ExpensePaymentMethod,
  JournalEntry,
  RecurringExpenseTemplate,
} from '@/lib/types';

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

type TemplateFilters = PgPagination & {
  status?: RecurringExpenseTemplate['status'];
};

type DocumentFilters = PgPagination & {
  status?: ExpenseDocumentStatus;
  periodYear?: number;
  periodMonth?: number;
  templateId?: string;
};

type CreateTemplateInput = Omit<
  RecurringExpenseTemplate,
  'id' | 'createdAt' | 'updatedAt' | 'nextRunDate'
> & {
  nextRunDate?: string;
};

type CreatePaymentInput = {
  paymentDate: string;
  amount: number;
  paymentMethod: ExpensePaymentMethod;
  accountCode: string;
  referenceNumber?: string;
  notes?: string;
  createdBy: string;
};

type DocumentHistoryMeta = {
  action: ExpenseDocumentHistoryAction;
  actorId?: string;
  actorName?: string;
  note?: string;
  details?: Record<string, unknown>;
};

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

function toDateOnly(value: string | Date): string {
  const date = value instanceof Date ? value : new Date(value);
  return date.toISOString().slice(0, 10);
}

function toNumber(value: unknown, fallback = 0): number {
  const num = Number(value);
  return Number.isFinite(num) ? num : fallback;
}

function createId(prefix: string): string {
  return `${prefix}-${Date.now()}-${Math.random().toString(36).slice(2, 8)}`;
}

function daysInMonth(year: number, month1to12: number): number {
  return new Date(Date.UTC(year, month1to12, 0)).getUTCDate();
}

function computeInitialNextRunDate(template: CreateTemplateInput): string {
  if (template.nextRunDate) return toDateOnly(template.nextRunDate);
  const start = new Date(template.startDate);
  if (Number.isNaN(start.getTime())) return toDateOnly(new Date());

  const year = start.getUTCFullYear();
  const month = start.getUTCMonth() + 1;
  const monthOfYear = template.monthOfYear || month;
  const day = template.dayOfMonth || start.getUTCDate();

  if (template.frequency === 'yearly') {
    const safeDay = Math.min(day, daysInMonth(year, monthOfYear));
    return toDateOnly(new Date(Date.UTC(year, monthOfYear - 1, safeDay)));
  }

  const safeDay = Math.min(day, daysInMonth(year, month));
  return toDateOnly(new Date(Date.UTC(year, month - 1, safeDay)));
}

function computeNextRunDate(template: RecurringExpenseTemplate, fromDate: string): string {
  const from = new Date(fromDate);
  const interval = Math.max(1, Number(template.intervalCount || 1));

  if (template.frequency === 'yearly') {
    const baseYear = from.getUTCFullYear() + interval;
    const month = Number(template.monthOfYear || (from.getUTCMonth() + 1));
    const day = Number(template.dayOfMonth || from.getUTCDate());
    const safeDay = Math.min(day, daysInMonth(baseYear, month));
    return toDateOnly(new Date(Date.UTC(baseYear, month - 1, safeDay)));
  }

  const baseYear = from.getUTCFullYear();
  const baseMonthIndex = from.getUTCMonth();
  const target = new Date(Date.UTC(baseYear, baseMonthIndex + interval, 1));
  const month = target.getUTCMonth() + 1;
  const year = target.getUTCFullYear();
  const day = Number(template.dayOfMonth || from.getUTCDate());
  const safeDay = Math.min(day, daysInMonth(year, month));

  return toDateOnly(new Date(Date.UTC(year, month - 1, safeDay)));
}

function mapTemplateRow(row: {
  id: string;
  raw: Record<string, unknown>;
  created_at: unknown;
  updated_at: unknown;
  next_run_date: unknown;
}): RecurringExpenseTemplate {
  const raw = row.raw || {};
  return {
    ...(raw as RecurringExpenseTemplate),
    id: row.id,
    nextRunDate: String((raw as any).nextRunDate || toDateOnly(String(row.next_run_date || new Date()))),
    createdAt: String((raw as any).createdAt || toIso(row.created_at)),
    updatedAt: String((raw as any).updatedAt || toIso(row.updated_at)),
  } as RecurringExpenseTemplate;
}

function mapDocumentRow(row: {
  id: string;
  raw: Record<string, unknown>;
  created_at: unknown;
  updated_at: unknown;
}): ExpenseDocument {
  const raw = row.raw || {};
  return {
    ...(raw as ExpenseDocument),
    id: row.id,
    amount: toNumber((raw as any).amount),
    paidAmount: toNumber((raw as any).paidAmount),
    createdAt: String((raw as any).createdAt || toIso(row.created_at)),
    updatedAt: String((raw as any).updatedAt || toIso(row.updated_at)),
  } as ExpenseDocument;
}

function mapPaymentRow(row: {
  id: string;
  raw: Record<string, unknown>;
  created_at: unknown;
}): ExpensePayment {
  const raw = row.raw || {};
  return {
    ...(raw as ExpensePayment),
    id: row.id,
    amount: toNumber((raw as any).amount),
    createdAt: String((raw as any).createdAt || toIso(row.created_at)),
  } as ExpensePayment;
}

function mapHistoryRow(row: {
  id: string;
  document_id: string;
  from_status: unknown;
  to_status: unknown;
  action: unknown;
  actor_id: unknown;
  actor_name: unknown;
  note: unknown;
  details: unknown;
  created_at: unknown;
}): ExpenseDocumentHistoryEntry {
  return {
    id: row.id,
    documentId: row.document_id,
    fromStatus: (row.from_status as ExpenseDocumentStatus | null) || null,
    toStatus: String(row.to_status) as ExpenseDocumentStatus,
    action: String(row.action) as ExpenseDocumentHistoryAction,
    actorId: row.actor_id ? String(row.actor_id) : undefined,
    actorName: row.actor_name ? String(row.actor_name) : undefined,
    note: row.note ? String(row.note) : undefined,
    details: (row.details as Record<string, unknown>) || {},
    createdAt: toIso(row.created_at),
  };
}

async function appendExpenseDocumentHistory(
  documentId: string,
  fromStatus: ExpenseDocumentStatus | null | undefined,
  toStatus: ExpenseDocumentStatus,
  meta: DocumentHistoryMeta
): Promise<void> {
  await pgQuery(
    `
    INSERT INTO expense_document_history (
      id, document_id, from_status, to_status, action, actor_id, actor_name, note, details, created_at
    ) VALUES (
      $1, $2, $3, $4, $5, $6, $7, $8, $9::jsonb, NOW()
    )
    `,
    [
      createId('exp-his'),
      documentId,
      fromStatus || null,
      toStatus,
      meta.action,
      meta.actorId || null,
      meta.actorName || null,
      meta.note || null,
      JSON.stringify(meta.details || {}),
    ]
  );
}

export async function pgCreateRecurringExpenseTemplate(input: CreateTemplateInput): Promise<RecurringExpenseTemplate> {
  const now = new Date().toISOString();
  const id = createId('rexp-tpl');
  const nextRunDate = computeInitialNextRunDate(input);

  const template: RecurringExpenseTemplate = {
    id,
    name: input.name,
    expenseItemId: input.expenseItemId,
    expenseAccountCode: input.expenseAccountCode,
    payableAccountCode: input.payableAccountCode || '2130',
    defaultAmount: Math.max(0, Number(input.defaultAmount || 0)),
    currencyCode: input.currencyCode,
    frequency: input.frequency,
    intervalCount: Math.max(1, Number(input.intervalCount || 1)),
    startDate: toDateOnly(input.startDate),
    nextRunDate,
    dayOfMonth: input.dayOfMonth,
    monthOfYear: input.monthOfYear,
    endDate: input.endDate ? toDateOnly(input.endDate) : undefined,
    approvalPolicy: input.approvalPolicy || 'single_step',
    autoSubmitForApproval: Boolean(input.autoSubmitForApproval),
    status: input.status || 'active',
    notes: input.notes,
    createdBy: input.createdBy,
    createdAt: now,
    updatedAt: now,
  };

  await pgQuery(
    `
    INSERT INTO recurring_expense_templates (
      id, name, expense_item_id, expense_account_code, payable_account_code,
      default_amount, currency_code, frequency, interval_count,
      start_date, next_run_date, day_of_month, month_of_year, end_date,
      approval_policy, auto_submit_for_approval, status, notes,
      raw, created_by, updated_at
    ) VALUES (
      $1, $2, $3, $4, $5,
      $6, $7, $8, $9,
      $10, $11, $12, $13, $14,
      $15, $16, $17, $18,
      $19::jsonb, $20, NOW()
    )
    `,
    [
      template.id,
      template.name,
      template.expenseItemId || null,
      template.expenseAccountCode,
      template.payableAccountCode,
      template.defaultAmount,
      template.currencyCode || null,
      template.frequency,
      template.intervalCount,
      template.startDate,
      template.nextRunDate,
      template.dayOfMonth || null,
      template.monthOfYear || null,
      template.endDate || null,
      template.approvalPolicy,
      template.autoSubmitForApproval,
      template.status,
      template.notes || null,
      JSON.stringify(template),
      template.createdBy,
    ]
  );

  return template;
}

export async function pgGetRecurringExpenseTemplateById(id: string): Promise<RecurringExpenseTemplate | null> {
  const result = await pgQuery<{
    id: string;
    raw: Record<string, unknown>;
    next_run_date: unknown;
    created_at: unknown;
    updated_at: unknown;
  }>(
    'SELECT id, raw, next_run_date, created_at, updated_at FROM recurring_expense_templates WHERE id = $1',
    [id]
  );

  if (!result.rows.length) return null;
  return mapTemplateRow(result.rows[0]);
}

export async function pgListRecurringExpenseTemplates(
  filters: TemplateFilters = {}
): Promise<{ items: RecurringExpenseTemplate[]; total: number; page: number; pageSize: number }> {
  const page = Math.max(1, Number(filters.page || 1));
  const pageSize = Math.max(1, Math.min(500, Number(filters.pageSize || 50)));
  const offset = (page - 1) * pageSize;

  const where: string[] = [];
  const values: unknown[] = [];

  if (filters.status) {
    values.push(filters.status);
    where.push(`status = $${values.length}`);
  }

  const whereSql = where.length ? `WHERE ${where.join(' AND ')}` : '';

  const rows = await pgQuery<{
    id: string;
    raw: Record<string, unknown>;
    next_run_date: unknown;
    created_at: unknown;
    updated_at: unknown;
  }>(
    `
    SELECT id, raw, next_run_date, created_at, updated_at
    FROM recurring_expense_templates
    ${whereSql}
    ORDER BY next_run_date, created_at DESC
    LIMIT $${values.length + 1} OFFSET $${values.length + 2}
    `,
    [...values, pageSize, offset]
  );

  const countResult = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text as total FROM recurring_expense_templates ${whereSql}`,
    values
  );

  return {
    items: rows.rows.map(mapTemplateRow),
    total: Number(countResult.rows[0]?.total || 0),
    page,
    pageSize,
  };
}

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

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

  await pgQuery(
    `
    UPDATE recurring_expense_templates
    SET name = $2,
        expense_item_id = $3,
        expense_account_code = $4,
        payable_account_code = $5,
        default_amount = $6,
        currency_code = $7,
        frequency = $8,
        interval_count = $9,
        start_date = $10,
        next_run_date = $11,
        day_of_month = $12,
        month_of_year = $13,
        end_date = $14,
        approval_policy = $15,
        auto_submit_for_approval = $16,
        status = $17,
        notes = $18,
        raw = $19::jsonb,
        updated_at = NOW()
    WHERE id = $1
    `,
    [
      id,
      merged.name,
      merged.expenseItemId || null,
      merged.expenseAccountCode,
      merged.payableAccountCode,
      merged.defaultAmount,
      merged.currencyCode || null,
      merged.frequency,
      merged.intervalCount,
      merged.startDate,
      merged.nextRunDate,
      merged.dayOfMonth || null,
      merged.monthOfYear || null,
      merged.endDate || null,
      merged.approvalPolicy,
      merged.autoSubmitForApproval,
      merged.status,
      merged.notes || null,
      JSON.stringify(merged),
    ]
  );

  return merged;
}

export async function pgDeleteRecurringExpenseTemplate(id: string): Promise<void> {
  await pgQuery('DELETE FROM recurring_expense_templates WHERE id = $1', [id]);
}

async function pgCreateExpenseDocument(data: Omit<ExpenseDocument, 'id' | 'createdAt' | 'updatedAt'>): Promise<ExpenseDocument> {
  const now = new Date().toISOString();
  const id = createId('exp-doc');

  const doc: ExpenseDocument = {
    ...data,
    id,
    createdAt: now,
    updatedAt: now,
  };

  await pgQuery(
    `
    INSERT INTO expense_documents (
      id, template_id, expense_item_id, expense_account_code, payable_account_code,
      period_year, period_month, due_date, description, amount,
      currency_code, payment_method, status, paid_amount,
      submitted_by, submitted_at, approved_by, approved_at,
      rejected_by, rejected_at, rejected_reason,
      posted_by, posted_at, journal_entry_id,
      raw, created_by, updated_at
    ) VALUES (
      $1, $2, $3, $4, $5,
      $6, $7, $8, $9, $10,
      $11, $12, $13, $14,
      $15, $16, $17, $18,
      $19, $20, $21,
      $22, $23, $24,
      $25::jsonb, $26, NOW()
    )
    ON CONFLICT (template_id, period_year, period_month) DO NOTHING
    `,
    [
      doc.id,
      doc.templateId || null,
      doc.expenseItemId || null,
      doc.expenseAccountCode,
      doc.payableAccountCode,
      doc.periodYear,
      doc.periodMonth,
      doc.dueDate,
      doc.description,
      doc.amount,
      doc.currencyCode || null,
      doc.paymentMethod,
      doc.status,
      doc.paidAmount,
      doc.submittedBy || null,
      doc.submittedAt || null,
      doc.approvedBy || null,
      doc.approvedAt || null,
      doc.rejectedBy || null,
      doc.rejectedAt || null,
      doc.rejectedReason || null,
      doc.postedBy || null,
      doc.postedAt || null,
      doc.journalEntryId || null,
      JSON.stringify(doc),
      doc.createdBy,
    ]
  );

  const created = await pgGetExpenseDocumentById(doc.id);
  if (!created) throw new Error('Failed to create expense document.');

  await appendExpenseDocumentHistory(created.id, null, created.status, {
    action: 'created',
    actorId: doc.createdBy,
    actorName: doc.createdBy,
    note: doc.templateId ? 'Generated from recurring template' : 'Created manually',
    details: {
      templateId: doc.templateId || null,
      periodYear: doc.periodYear,
      periodMonth: doc.periodMonth,
    },
  });

  return created;
}

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

  if (!result.rows.length) return null;
  return mapDocumentRow(result.rows[0]);
}

export async function pgListExpenseDocuments(
  filters: DocumentFilters = {}
): Promise<{ items: ExpenseDocument[]; total: number; page: number; pageSize: number }> {
  const page = Math.max(1, Number(filters.page || 1));
  const pageSize = Math.max(1, Math.min(500, Number(filters.pageSize || 50)));
  const offset = (page - 1) * pageSize;

  const where: string[] = [];
  const values: unknown[] = [];

  if (filters.status) {
    values.push(filters.status);
    where.push(`status = $${values.length}`);
  }

  if (typeof filters.periodYear === 'number') {
    values.push(filters.periodYear);
    where.push(`period_year = $${values.length}`);
  }

  if (typeof filters.periodMonth === 'number') {
    values.push(filters.periodMonth);
    where.push(`period_month = $${values.length}`);
  }

  if (filters.templateId) {
    values.push(filters.templateId);
    where.push(`template_id = $${values.length}`);
  }

  const whereSql = where.length ? `WHERE ${where.join(' AND ')}` : '';

  const rows = await pgQuery<{
    id: string;
    raw: Record<string, unknown>;
    created_at: unknown;
    updated_at: unknown;
  }>(
    `
    SELECT id, raw, created_at, updated_at
    FROM expense_documents
    ${whereSql}
    ORDER BY due_date DESC, created_at DESC
    LIMIT $${values.length + 1} OFFSET $${values.length + 2}
    `,
    [...values, pageSize, offset]
  );

  const countResult = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text as total FROM expense_documents ${whereSql}`,
    values
  );

  return {
    items: rows.rows.map(mapDocumentRow),
    total: Number(countResult.rows[0]?.total || 0),
    page,
    pageSize,
  };
}

export async function pgGenerateExpenseDocumentsFromTemplates(input: {
  asOfDate?: string;
  actor: string;
}): Promise<{ generated: ExpenseDocument[]; skippedTemplateIds: string[] }> {
  const asOfDate = toDateOnly(input.asOfDate || new Date());
  const dueTemplates = await pgQuery<{
    id: string;
    raw: Record<string, unknown>;
    next_run_date: unknown;
    created_at: unknown;
    updated_at: unknown;
  }>(
    `
    SELECT id, raw, next_run_date, created_at, updated_at
    FROM recurring_expense_templates
    WHERE status = 'active'
      AND next_run_date <= $1
      AND (end_date IS NULL OR end_date >= next_run_date)
    ORDER BY next_run_date
    `,
    [asOfDate]
  );

  const generated: ExpenseDocument[] = [];
  const skippedTemplateIds: string[] = [];

  for (const row of dueTemplates.rows) {
    const template = mapTemplateRow(row);
    const dueDate = toDateOnly(template.nextRunDate);
    const due = new Date(dueDate);
    const periodYear = due.getUTCFullYear();
    const periodMonth = due.getUTCMonth() + 1;

    const status: ExpenseDocumentStatus = template.autoSubmitForApproval ? 'pending_approval' : 'draft';
    const description = `${template.name} - ${periodYear}-${String(periodMonth).padStart(2, '0')}`;
    const nowIso = new Date().toISOString();

    const docInput: Omit<ExpenseDocument, 'id' | 'createdAt' | 'updatedAt'> = {
      templateId: template.id,
      expenseItemId: template.expenseItemId,
      expenseAccountCode: template.expenseAccountCode,
      payableAccountCode: template.payableAccountCode,
      periodYear,
      periodMonth,
      dueDate,
      description,
      amount: template.defaultAmount,
      currencyCode: template.currencyCode,
      paymentMethod: 'bank',
      status,
      paidAmount: 0,
      submittedBy: status === 'pending_approval' ? input.actor : undefined,
      submittedAt: status === 'pending_approval' ? nowIso : undefined,
      approvedBy: undefined,
      approvedAt: undefined,
      rejectedBy: undefined,
      rejectedAt: undefined,
      rejectedReason: undefined,
      postedBy: undefined,
      postedAt: undefined,
      journalEntryId: undefined,
      createdBy: input.actor,
    };

    const createdBefore = await pgQuery<{ id: string }>(
      `
      SELECT id
      FROM expense_documents
      WHERE template_id = $1 AND period_year = $2 AND period_month = $3
      LIMIT 1
      `,
      [template.id, periodYear, periodMonth]
    );

    if (createdBefore.rows.length > 0) {
      skippedTemplateIds.push(template.id);
    } else {
      const doc = await pgCreateExpenseDocument(docInput);
      generated.push(doc);
    }

    const nextRunDate = computeNextRunDate(template, dueDate);
    await pgUpdateRecurringExpenseTemplate(template.id, { nextRunDate });
  }

  return { generated, skippedTemplateIds };
}

async function updateExpenseDocument(
  id: string,
  patch: Partial<ExpenseDocument>,
  historyMeta?: DocumentHistoryMeta
): Promise<ExpenseDocument | null> {
  const existing = await pgGetExpenseDocumentById(id);
  if (!existing) return null;

  const next: ExpenseDocument = {
    ...existing,
    ...patch,
    id,
    updatedAt: new Date().toISOString(),
  };

  await pgQuery(
    `
    UPDATE expense_documents
    SET template_id = $2,
        expense_item_id = $3,
        expense_account_code = $4,
        payable_account_code = $5,
        period_year = $6,
        period_month = $7,
        due_date = $8,
        description = $9,
        amount = $10,
        currency_code = $11,
        payment_method = $12,
        status = $13,
        paid_amount = $14,
        submitted_by = $15,
        submitted_at = $16,
        approved_by = $17,
        approved_at = $18,
        rejected_by = $19,
        rejected_at = $20,
        rejected_reason = $21,
        posted_by = $22,
        posted_at = $23,
        journal_entry_id = $24,
        raw = $25::jsonb,
        updated_at = NOW()
    WHERE id = $1
    `,
    [
      id,
      next.templateId || null,
      next.expenseItemId || null,
      next.expenseAccountCode,
      next.payableAccountCode,
      next.periodYear,
      next.periodMonth,
      next.dueDate,
      next.description,
      next.amount,
      next.currencyCode || null,
      next.paymentMethod,
      next.status,
      next.paidAmount,
      next.submittedBy || null,
      next.submittedAt || null,
      next.approvedBy || null,
      next.approvedAt || null,
      next.rejectedBy || null,
      next.rejectedAt || null,
      next.rejectedReason || null,
      next.postedBy || null,
      next.postedAt || null,
      next.journalEntryId || null,
      JSON.stringify(next),
    ]
  );

  if (historyMeta && existing.status !== next.status) {
    await appendExpenseDocumentHistory(id, existing.status, next.status, historyMeta);
  }

  return next;
}

export async function pgSubmitExpenseDocumentForApproval(
  id: string,
  actor: { id: string; name?: string }
): Promise<ExpenseDocument | null> {
  const existing = await pgGetExpenseDocumentById(id);
  if (!existing) return null;

  if (existing.status !== 'draft' && existing.status !== 'rejected') {
    throw new Error('Only draft or rejected documents can be submitted.');
  }

  return updateExpenseDocument(id, {
    status: 'pending_approval',
    submittedBy: actor.name || actor.id,
    submittedAt: new Date().toISOString(),
    rejectedBy: undefined,
    rejectedAt: undefined,
    rejectedReason: undefined,
  }, {
    action: 'submitted',
    actorId: actor.id,
    actorName: actor.name,
  });
}

export async function pgApproveExpenseDocument(
  id: string,
  actor: { id: string; name?: string }
): Promise<ExpenseDocument | null> {
  const existing = await pgGetExpenseDocumentById(id);
  if (!existing) return null;

  if (existing.status !== 'pending_approval') {
    throw new Error('Only pending documents can be approved.');
  }

  return updateExpenseDocument(id, {
    status: 'approved',
    approvedBy: actor.name || actor.id,
    approvedAt: new Date().toISOString(),
    rejectedBy: undefined,
    rejectedAt: undefined,
    rejectedReason: undefined,
  }, {
    action: 'approved',
    actorId: actor.id,
    actorName: actor.name,
  });
}

export async function pgRejectExpenseDocument(
  id: string,
  actor: { id: string; name?: string },
  reason: string
): Promise<ExpenseDocument | null> {
  const existing = await pgGetExpenseDocumentById(id);
  if (!existing) return null;

  if (existing.status !== 'pending_approval' && existing.status !== 'approved') {
    throw new Error('Only pending or approved documents can be rejected.');
  }

  return updateExpenseDocument(id, {
    status: 'rejected',
    rejectedBy: actor.name || actor.id,
    rejectedAt: new Date().toISOString(),
    rejectedReason: String(reason || '').trim() || 'Rejected',
    approvedBy: undefined,
    approvedAt: undefined,
  }, {
    action: 'rejected',
    actorId: actor.id,
    actorName: actor.name,
    note: String(reason || '').trim() || 'Rejected',
  });
}

export async function pgPostExpenseDocument(
  id: string,
  actor: { id: string; name?: string },
  postingDate?: string
): Promise<ExpenseDocument | null> {
  const existing = await pgGetExpenseDocumentById(id);
  if (!existing) return null;

  if (existing.status !== 'approved') {
    throw new Error('Only approved documents can be posted.');
  }

  const date = toDateOnly(postingDate || existing.dueDate || new Date());
  const description = existing.description || `Expense document ${existing.id}`;

  const entry: JournalEntry = {
    id: createId('je-expdoc'),
    date,
    description,
    status: 'posted',
    referenceType: 'expense',
    referenceId: existing.id,
    lines: [
      {
        accountId: existing.expenseAccountCode,
        accountCode: existing.expenseAccountCode,
        accountName: 'Expense',
        debit: existing.amount,
        credit: 0,
        description,
      },
      {
        accountId: existing.payableAccountCode,
        accountCode: existing.payableAccountCode,
        accountName: 'Accrued Expenses',
        debit: 0,
        credit: existing.amount,
        description,
      },
    ],
    createdBy: actor,
    createdAt: new Date().toISOString(),
  };

  await pgSaveJournalEntry([entry as unknown as Record<string, unknown>]);

  return updateExpenseDocument(id, {
    status: 'posted',
    postedBy: actor.name || actor.id,
    postedAt: new Date().toISOString(),
    journalEntryId: entry.id,
  }, {
    action: 'posted',
    actorId: actor.id,
    actorName: actor.name,
    details: { journalEntryId: entry.id, postingDate: date },
  });
}

export async function pgListExpenseDocumentHistory(documentId: string): Promise<ExpenseDocumentHistoryEntry[]> {
  const rows = await pgQuery<{
    id: string;
    document_id: string;
    from_status: unknown;
    to_status: unknown;
    action: unknown;
    actor_id: unknown;
    actor_name: unknown;
    note: unknown;
    details: unknown;
    created_at: unknown;
  }>(
    `
    SELECT id, document_id, from_status, to_status, action, actor_id, actor_name, note, details, created_at
    FROM expense_document_history
    WHERE document_id = $1
    ORDER BY created_at ASC, id ASC
    `,
    [documentId]
  );

  return rows.rows.map(mapHistoryRow);
}

export async function pgListExpensePayments(documentId: string): Promise<ExpensePayment[]> {
  const rows = await pgQuery<{
    id: string;
    raw: Record<string, unknown>;
    created_at: unknown;
  }>(
    `
    SELECT id, raw, created_at
    FROM expense_payments
    WHERE document_id = $1
    ORDER BY payment_date DESC, created_at DESC
    `,
    [documentId]
  );

  return rows.rows.map(mapPaymentRow);
}

export async function pgCreateExpensePayment(documentId: string, input: CreatePaymentInput): Promise<{
  payment: ExpensePayment;
  document: ExpenseDocument;
}> {
  const document = await pgGetExpenseDocumentById(documentId);
  if (!document) throw new Error('Expense document not found.');

  if (document.status !== 'posted' && document.status !== 'partially_paid' && document.status !== 'paid') {
    throw new Error('Document must be posted before recording payments.');
  }

  const amount = Math.max(0, Number(input.amount || 0));
  if (amount <= 0) {
    throw new Error('Payment amount must be greater than zero.');
  }

  const remaining = Math.max(0, Number(document.amount) - Number(document.paidAmount || 0));
  if (amount > remaining + 0.0001) {
    throw new Error('Payment amount exceeds remaining balance.');
  }

  const now = new Date().toISOString();
  const payment: ExpensePayment = {
    id: createId('exp-pay'),
    documentId,
    paymentDate: toDateOnly(input.paymentDate || new Date()),
    amount,
    paymentMethod: input.paymentMethod,
    accountCode: String(input.accountCode || '').trim(),
    referenceNumber: String(input.referenceNumber || '').trim() || undefined,
    notes: String(input.notes || '').trim() || undefined,
    createdBy: input.createdBy,
    createdAt: now,
    journalEntryId: undefined,
  };

  await pgQuery(
    `
    INSERT INTO expense_payments (
      id, document_id, payment_date, amount, payment_method,
      account_code, reference_number, notes,
      created_by, journal_entry_id, raw
    ) VALUES (
      $1, $2, $3, $4, $5,
      $6, $7, $8,
      $9, $10, $11::jsonb
    )
    `,
    [
      payment.id,
      payment.documentId,
      payment.paymentDate,
      payment.amount,
      payment.paymentMethod,
      payment.accountCode,
      payment.referenceNumber || null,
      payment.notes || null,
      payment.createdBy,
      null,
      JSON.stringify(payment),
    ]
  );

  const journal: JournalEntry = {
    id: createId('je-exppay'),
    date: payment.paymentDate,
    description: `Expense payment ${document.description}`,
    status: 'posted',
    referenceType: 'expense',
    referenceId: document.id,
    lines: [
      {
        accountId: document.payableAccountCode,
        accountCode: document.payableAccountCode,
        accountName: 'Accrued Expenses',
        debit: payment.amount,
        credit: 0,
        description: `Expense payment for ${document.id}`,
      },
      {
        accountId: payment.accountCode,
        accountCode: payment.accountCode,
        accountName: payment.paymentMethod === 'cash' ? 'Cash' : 'Bank',
        debit: 0,
        credit: payment.amount,
        description: `Expense payment for ${document.id}`,
      },
    ],
    createdBy: input.createdBy,
    createdAt: now,
  };

  await pgSaveJournalEntry([journal as unknown as Record<string, unknown>]);

  payment.journalEntryId = journal.id;

  await pgQuery(
    'UPDATE expense_payments SET journal_entry_id = $2, raw = $3::jsonb WHERE id = $1',
    [payment.id, payment.journalEntryId, JSON.stringify(payment)]
  );

  const nextPaidAmount = Math.round((Number(document.paidAmount || 0) + amount) * 10000) / 10000;
  const status: ExpenseDocumentStatus = nextPaidAmount + 0.0001 >= Number(document.amount) ? 'paid' : 'partially_paid';

  const updatedDocument = await updateExpenseDocument(document.id, {
    paidAmount: nextPaidAmount,
    status,
  }, {
    action: 'payment',
    actorId: input.createdBy,
    actorName: input.createdBy,
    details: {
      paymentId: payment.id,
      paymentAmount: amount,
      paymentMethod: input.paymentMethod,
      remainingBefore: remaining,
      remainingAfter: Math.max(0, Number(document.amount) - nextPaidAmount),
    },
  });

  if (!updatedDocument) {
    throw new Error('Failed to update expense document after payment.');
  }

  return { payment, document: updatedDocument };
}
