/**
 * Advanced Recurring Expenses Data Access Layer (Phase 1)
 * Contracts, Schedules, Obligations, Postings, Allocations
 */

import { pgQuery } from './client';
import type {
  RecurringExpenseContract,
  RecurringSchedule,
  ExpenseObligation,
  ExpensePosting,
  ExpenseAllocation,
  PrepaidAmortizationSchedule,
  ExpenseEscalationRule,
  ExpensePaymentSchedule,
  ExpenseAuditLog,
} from '@/lib/types';

// ============================================================================
// UTILITIES
// ============================================================================

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

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

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

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

// ============================================================================
// CONTRACT MANAGEMENT
// ============================================================================

export async function pgCreateRecurringExpenseContract(
  input: Omit<RecurringExpenseContract, 'id' | 'createdAt' | 'updatedAt'>
): Promise<RecurringExpenseContract> {
  const now = new Date().toISOString();
  const id = createId('exp-contract');

  const contract: RecurringExpenseContract = {
    ...input,
    id,
    createdAt: now,
    updatedAt: now,
  };

  await pgQuery(
    `
    INSERT INTO recurring_expense_contracts (
      id, template_id, name, vendor_id,
      contract_date, start_date, end_date,
      renewal_terms, escalation_policy, escalation_value,
      allocation_strategy, status, notes,
      raw, created_by, updated_at
    ) VALUES (
      $1, $2, $3, $4,
      $5, $6, $7,
      $8, $9, $10,
      $11, $12, $13,
      $14::jsonb, $15, NOW()
    )
    `,
    [
      contract.id,
      contract.templateId || null,
      contract.name,
      contract.vendorId || null,
      toDateOnly(contract.contractDate),
      toDateOnly(contract.startDate),
      contract.endDate ? toDateOnly(contract.endDate) : null,
      contract.renewalTerms,
      contract.escalationPolicy,
      contract.escalationValue || null,
      contract.allocationStrategy,
      contract.status,
      contract.notes || null,
      JSON.stringify(contract),
      contract.createdBy,
    ]
  );

  return contract;
}

export async function pgGetRecurringContractById(id: string): Promise<RecurringExpenseContract | 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 recurring_expense_contracts WHERE id = $1',
    [id]
  );

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

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

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

  await pgQuery(
    `
    UPDATE recurring_expense_contracts
    SET name = $2, template_id = $3, vendor_id = $4,
        contract_date = $5, start_date = $6, end_date = $7,
        renewal_terms = $8, escalation_policy = $9, escalation_value = $10,
        allocation_strategy = $11, status = $12, notes = $13,
        raw = $14::jsonb, updated_at = NOW()
    WHERE id = $1
    `,
    [
      id,
      merged.name,
      merged.templateId || null,
      merged.vendorId || null,
      toDateOnly(merged.contractDate),
      toDateOnly(merged.startDate),
      merged.endDate ? toDateOnly(merged.endDate) : null,
      merged.renewalTerms,
      merged.escalationPolicy,
      merged.escalationValue || null,
      merged.allocationStrategy,
      merged.status,
      merged.notes || null,
      JSON.stringify(merged),
    ]
  );

  return merged;
}

export async function pgListRecurringContracts(filters: {
  status?: string;
  templateId?: string;
  page?: number;
  pageSize?: number;
} = {}): Promise<{ items: RecurringExpenseContract[]; 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 (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 recurring_expense_contracts
    ${whereSql}
    ORDER BY 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_contracts ${whereSql}`,
    values
  );

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

// ============================================================================
// SCHEDULE MANAGEMENT
// ============================================================================

export async function pgCreateRecurringSchedule(
  templateId: string,
  input: Omit<RecurringSchedule, 'id' | 'createdAt' | 'updatedAt'>
): Promise<RecurringSchedule> {
  const now = new Date().toISOString();
  const id = createId('exp-schedule');

  const schedule: RecurringSchedule = {
    ...input,
    templateId,
    id,
    createdAt: now,
    updatedAt: now,
  };

  await pgQuery(
    `
    INSERT INTO recurring_schedules (
      id, template_id, contract_id,
      current_period_start, current_period_end,
      next_scheduled_date, last_generated_date,
      missed_generations, last_retry_at, retry_count,
      status, raw, created_at, updated_at
    ) VALUES (
      $1, $2, $3,
      $4, $5,
      $6, $7,
      $8, $9, $10,
      $11, $12::jsonb, NOW(), NOW()
    )
    `,
    [
      schedule.id,
      schedule.templateId,
      schedule.contractId || null,
      schedule.currentPeriodStart ? toDateOnly(schedule.currentPeriodStart) : null,
      schedule.currentPeriodEnd ? toDateOnly(schedule.currentPeriodEnd) : null,
      toDateOnly(schedule.nextScheduledDate),
      schedule.lastGeneratedDate ? toDateOnly(schedule.lastGeneratedDate) : null,
      schedule.missedGenerations || 0,
      schedule.lastRetryAt ? toIso(schedule.lastRetryAt) : null,
      schedule.retryCount || 0,
      schedule.status,
      JSON.stringify(schedule),
    ]
  );

  return schedule;
}

export async function pgGetScheduleByTemplateId(templateId: string): Promise<RecurringSchedule | 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 recurring_schedules WHERE template_id = $1',
    [templateId]
  );

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

export async function pgUpdateRecurringSchedule(
  templateId: string,
  updates: Partial<RecurringSchedule>
): Promise<RecurringSchedule | null> {
  const existing = await pgGetScheduleByTemplateId(templateId);
  if (!existing) return null;

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

  await pgQuery(
    `
    UPDATE recurring_schedules
    SET contract_id = $2,
        current_period_start = $3,
        current_period_end = $4,
        next_scheduled_date = $5,
        last_generated_date = $6,
        missed_generations = $7,
        last_retry_at = $8,
        retry_count = $9,
        status = $10,
        raw = $11::jsonb,
        updated_at = NOW()
    WHERE template_id = $1
    `,
    [
      templateId,
      merged.contractId || null,
      merged.currentPeriodStart ? toDateOnly(merged.currentPeriodStart) : null,
      merged.currentPeriodEnd ? toDateOnly(merged.currentPeriodEnd) : null,
      toDateOnly(merged.nextScheduledDate),
      merged.lastGeneratedDate ? toDateOnly(merged.lastGeneratedDate) : null,
      merged.missedGenerations || 0,
      merged.lastRetryAt ? toIso(merged.lastRetryAt) : null,
      merged.retryCount || 0,
      merged.status,
      JSON.stringify(merged),
    ]
  );

  return merged;
}

// ============================================================================
// OBLIGATION MANAGEMENT (Core Accounting)
// ============================================================================

export async function pgCreateExpenseObligation(
  input: Omit<ExpenseObligation, 'id' | 'createdAt' | 'updatedAt'>
): Promise<ExpenseObligation> {
  const now = new Date().toISOString();
  const id = createId('exp-obl');

  const obligation: ExpenseObligation = {
    ...input,
    id,
    createdAt: now,
    updatedAt: now,
  };

  await pgQuery(
    `
    INSERT INTO expense_obligations (
      id, document_id, contract_id, template_id,
      period_start, period_end, gross_amount, currency_code,
      basis, recognition_date, settlement_date,
      status, reversal_obligation_id,
      raw, created_by, updated_at
    ) VALUES (
      $1, $2, $3, $4,
      $5, $6, $7, $8,
      $9, $10, $11,
      $12, $13,
      $14::jsonb, $15, NOW()
    )
    `,
    [
      obligation.id,
      obligation.documentId,
      obligation.contractId || null,
      obligation.templateId || null,
      toDateOnly(obligation.periodStart),
      toDateOnly(obligation.periodEnd),
      obligation.grossAmount,
      obligation.currencyCode || null,
      obligation.basis,
      obligation.recognitionDate ? toDateOnly(obligation.recognitionDate) : null,
      obligation.settlementDate ? toDateOnly(obligation.settlementDate) : null,
      obligation.status,
      obligation.reversalObligationId || null,
      JSON.stringify(obligation),
      obligation.createdBy,
    ]
  );

  return obligation;
}

export async function pgGetObligationById(id: string): Promise<ExpenseObligation | 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_obligations WHERE id = $1',
    [id]
  );

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

export async function pgListObligationsByDocument(
  documentId: string
): Promise<ExpenseObligation[]> {
  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_obligations WHERE document_id = $1 ORDER BY created_at DESC',
    [documentId]
  );

  return result.rows.map((row) => ({
    ...(row.raw as ExpenseObligation),
    id: row.id,
    createdAt: String(toIso(row.created_at)),
    updatedAt: String(toIso(row.updated_at)),
  }));
}

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

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

  await pgQuery(
    `
    UPDATE expense_obligations
    SET document_id = $2, contract_id = $3, template_id = $4,
        period_start = $5, period_end = $6, gross_amount = $7,
        currency_code = $8, basis = $9,
        recognition_date = $10, settlement_date = $11,
        status = $12, reversal_obligation_id = $13,
        raw = $14::jsonb, updated_at = NOW()
    WHERE id = $1
    `,
    [
      id,
      merged.documentId,
      merged.contractId || null,
      merged.templateId || null,
      toDateOnly(merged.periodStart),
      toDateOnly(merged.periodEnd),
      merged.grossAmount,
      merged.currencyCode || null,
      merged.basis,
      merged.recognitionDate ? toDateOnly(merged.recognitionDate) : null,
      merged.settlementDate ? toDateOnly(merged.settlementDate) : null,
      merged.status,
      merged.reversalObligationId || null,
      JSON.stringify(merged),
    ]
  );

  return merged;
}

// ============================================================================
// POSTING MANAGEMENT (Journal Entry Integration)
// ============================================================================

export async function pgCreateExpensePosting(
  input: Omit<ExpensePosting, 'id' | 'createdAt' | 'updatedAt'>
): Promise<ExpensePosting> {
  const now = new Date().toISOString();
  const id = createId('exp-post');

  const posting: ExpensePosting = {
    ...input,
    id,
    createdAt: now,
    updatedAt: now,
  };

  await pgQuery(
    `
    INSERT INTO expense_postings (
      id, obligation_id, document_id, journal_entry_id,
      debit_account, credit_account, amount,
      posting_date, posting_type, status,
      reversal_posting_id, reversal_reason,
      raw, created_by, updated_at
    ) VALUES (
      $1, $2, $3, $4,
      $5, $6, $7,
      $8, $9, $10,
      $11, $12,
      $13::jsonb, $14, NOW()
    )
    `,
    [
      posting.id,
      posting.obligationId,
      posting.documentId || null,
      posting.journalEntryId || null,
      posting.debitAccount,
      posting.creditAccount,
      posting.amount,
      toDateOnly(posting.postingDate),
      posting.postingType,
      posting.status,
      posting.reversalPostingId || null,
      posting.reversalReason || null,
      JSON.stringify(posting),
      posting.createdBy,
    ]
  );

  return posting;
}

export async function pgListPostingsByObligation(
  obligationId: string
): Promise<ExpensePosting[]> {
  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_postings WHERE obligation_id = $1 ORDER BY posting_date DESC',
    [obligationId]
  );

  return result.rows.map((row) => ({
    ...(row.raw as ExpensePosting),
    id: row.id,
    createdAt: String(toIso(row.created_at)),
    updatedAt: String(toIso(row.updated_at)),
  }));
}

// ============================================================================
// ALLOCATION MANAGEMENT
// ============================================================================

export async function pgCreateExpenseAllocation(
  input: Omit<ExpenseAllocation, 'id' | 'createdAt' | 'updatedAt'>
): Promise<ExpenseAllocation> {
  const now = new Date().toISOString();
  const id = createId('exp-alloc');

  const allocation: ExpenseAllocation = {
    ...input,
    id,
    createdAt: now,
    updatedAt: now,
  };

  await pgQuery(
    `
    INSERT INTO expense_allocations (
      id, obligation_id, allocation_type,
      allocation_target_id, allocation_target_name,
      percentage, amount,
      raw, created_by, updated_at
    ) VALUES (
      $1, $2, $3,
      $4, $5,
      $6, $7,
      $8::jsonb, $9, NOW()
    )
    `,
    [
      allocation.id,
      allocation.obligationId,
      allocation.allocationType,
      allocation.allocationTargetId,
      allocation.allocationTargetName || null,
      allocation.percentage,
      allocation.amount,
      JSON.stringify(allocation),
      allocation.createdBy,
    ]
  );

  return allocation;
}

export async function pgListAllocationsByObligation(
  obligationId: string
): Promise<ExpenseAllocation[]> {
  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_allocations WHERE obligation_id = $1 ORDER BY created_at',
    [obligationId]
  );

  return result.rows.map((row) => ({
    ...(row.raw as ExpenseAllocation),
    id: row.id,
    createdAt: String(toIso(row.created_at)),
    updatedAt: String(toIso(row.updated_at)),
  }));
}

// ============================================================================
// PAYMENT SCHEDULE
// ============================================================================

export async function pgCreatePaymentSchedule(
  input: Omit<ExpensePaymentSchedule, 'id' | 'createdAt' | 'updatedAt'>
): Promise<ExpensePaymentSchedule> {
  const now = new Date().toISOString();
  const id = createId('exp-pay-sched');

  const schedule: ExpensePaymentSchedule = {
    ...input,
    id,
    createdAt: now,
    updatedAt: now,
  };

  await pgQuery(
    `
    INSERT INTO expense_payment_schedules (
      id, obligation_id, due_date, amount,
      payment_method, status, paid_amount,
      raw, created_at, updated_at
    ) VALUES (
      $1, $2, $3, $4,
      $5, $6, $7,
      $8::jsonb, NOW(), NOW()
    )
    `,
    [
      schedule.id,
      schedule.obligationId,
      toDateOnly(schedule.dueDate),
      schedule.amount,
      schedule.paymentMethod || 'bank',
      schedule.status,
      schedule.paidAmount || 0,
      JSON.stringify(schedule),
    ]
  );

  return schedule;
}

export async function pgListPaymentSchedulesByObligation(
  obligationId: string
): Promise<ExpensePaymentSchedule[]> {
  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_payment_schedules WHERE obligation_id = $1 ORDER BY due_date',
    [obligationId]
  );

  return result.rows.map((row) => ({
    ...(row.raw as ExpensePaymentSchedule),
    id: row.id,
    createdAt: String(toIso(row.created_at)),
    updatedAt: String(toIso(row.updated_at)),
  }));
}

// ============================================================================
// AUDIT LOG
// ============================================================================

export async function pgLogExpenseAudit(
  input: Omit<ExpenseAuditLog, 'id' | 'createdAt'>
): Promise<ExpenseAuditLog> {
  const id = createId('audit');
  const now = new Date().toISOString();

  await pgQuery(
    `
    INSERT INTO expense_audit_log (
      id, entity_type, entity_id, action,
      old_values, new_values, actor_id, actor_name, reason, raw
    ) VALUES (
      $1, $2, $3, $4,
      $5::jsonb, $6::jsonb, $7, $8, $9, $10::jsonb
    )
    `,
    [
      id,
      input.entityType,
      input.entityId,
      input.action,
      JSON.stringify(input.oldValues || {}),
      JSON.stringify(input.newValues || {}),
      input.actorId,
      input.actorName || null,
      input.reason || null,
      JSON.stringify(input),
    ]
  );

  return {
    ...input,
    id,
    createdAt: now,
  };
}
