/**
 * Data Access Layer for Recurring Expenses Scheduler (Phase 2)
 * Handles background job management, scheduler tracking, and escalation execution
 */

import { pgQuery } from './client';
import type {
  RecurringScheduleJob,
  BackgroundJobLog,
  RecurringGenerationDuplicate,
  EscalationExecution,
  SchedulerStatus,
} from '@/lib/types';

// Utility functions
export function createId(): string {
  return Math.random().toString(36).substring(2, 15);
}

function toDateOnly(date: string | Date): string {
  const d = typeof date === 'string' ? new Date(date) : date;
  return d.toISOString().split('T')[0];
}

function toIso(date: Date | string): string {
  if (typeof date === 'string') return new Date(date).toISOString();
  return date.toISOString();
}

// ============================================
// RECURRING SCHEDULE JOB CRUD
// ============================================

export async function pgCreateRecurringScheduleJob(data: {
  templateId: string;
  contractId?: string;
  nextRunDate: string;
  status?: 'active' | 'paused' | 'completed' | 'cancelled';
  autoCreateObligation?: boolean;
  obligationBasis?: 'cash' | 'accrual' | 'prepaid';
  autoCreatePosting?: boolean;
  skipWeekends?: boolean;
  skipHolidays?: boolean;
  holidayShiftDays?: number;
  maxRetries?: number;
  createdBy: string;
}): Promise<RecurringScheduleJob | null> {
  const id = `job-${createId()}`;
  
  const result = await pgQuery<any>(`
    INSERT INTO recurring_schedule_jobs (
      id, template_id, contract_id, next_run_date, status,
      auto_create_obligation, obligation_basis, auto_create_posting,
      skip_weekends, skip_holidays, holiday_shift_days, max_retries,
      created_by, updated_by
    ) VALUES (
      $1, $2, $3, $4, $5,
      $6, $7, $8,
      $9, $10, $11, $12,
      $13, $13
    )
    RETURNING *
  `, [
    id,
    data.templateId,
    data.contractId || null,
    toIso(data.nextRunDate),
    data.status || 'active',
    data.autoCreateObligation || false,
    data.obligationBasis || 'accrual',
    data.autoCreatePosting || false,
    data.skipWeekends || false,
    data.skipHolidays || false,
    data.holidayShiftDays || 0,
    data.maxRetries || 3,
    data.createdBy,
  ]);

  return result.rows[0] ? mapScheduleJobRow(result.rows[0]) : null;
}

export async function pgGetScheduleJobById(id: string): Promise<RecurringScheduleJob | null> {
  const result = await pgQuery<any>(`
    SELECT * FROM recurring_schedule_jobs WHERE id = $1
  `, [id]);

  return result.rows[0] ? mapScheduleJobRow(result.rows[0]) : null;
}

export async function pgListScheduleJobsByTemplate(templateId: string): Promise<RecurringScheduleJob[]> {
  const result = await pgQuery<any>(`
    SELECT * FROM recurring_schedule_jobs 
    WHERE template_id = $1 
    ORDER BY next_run_date ASC
  `, [templateId]);

  return result.rows.map(mapScheduleJobRow);
}

export async function pgListActiveScheduleJobs(): Promise<RecurringScheduleJob[]> {
  const result = await pgQuery<any>(`
    SELECT * FROM recurring_schedule_jobs 
    WHERE status = 'active' AND next_run_date <= NOW()
    ORDER BY next_run_date ASC
    LIMIT 100
  `);

  return result.rows.map(mapScheduleJobRow);
}

export async function pgListScheduleJobs(filters: {
  status?: 'active' | 'paused' | 'completed' | 'cancelled';
  templateId?: string;
  limit?: number;
} = {}): Promise<RecurringScheduleJob[]> {
  const where: string[] = [];
  const params: unknown[] = [];

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

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

  const limit = Math.max(1, Math.min(500, Number(filters.limit || 100)));
  params.push(limit);
  const whereSql = where.length ? `WHERE ${where.join(' AND ')}` : '';

  const result = await pgQuery<any>(`
    SELECT * FROM recurring_schedule_jobs
    ${whereSql}
    ORDER BY next_run_date ASC
    LIMIT $${params.length}
  `, params);

  return result.rows.map(mapScheduleJobRow);
}

export async function pgUpdateScheduleJob(
  id: string,
  data: Partial<RecurringScheduleJob> & { updatedBy: string }
): Promise<RecurringScheduleJob | null> {
  const updates: string[] = [];
  const params: any[] = [id];
  let paramIndex = 2;

  if (data.nextRunDate !== undefined) {
    updates.push(`next_run_date = $${paramIndex++}`);
    params.push(toIso(data.nextRunDate));
  }
  if (data.status !== undefined) {
    updates.push(`status = $${paramIndex++}`);
    params.push(data.status);
  }
  if (data.retryCount !== undefined) {
    updates.push(`retry_count = $${paramIndex++}`);
    params.push(data.retryCount);
  }
  if (data.lastError !== undefined) {
    updates.push(`last_error = $${paramIndex++}`);
    params.push(data.lastError);
  }
  if (data.lastRunDate !== undefined) {
    updates.push(`last_run_date = $${paramIndex++}`);
    params.push(toIso(data.lastRunDate));
  }
  if (data.totalRuns !== undefined) {
    updates.push(`total_runs = $${paramIndex++}`);
    params.push(data.totalRuns);
  }

  updates.push(`updated_by = $${paramIndex++}`);
  params.push(data.updatedBy);
  updates.push(`updated_at = NOW()`);

  if (updates.length === 2) return pgGetScheduleJobById(id);

  const result = await pgQuery<any>(`
    UPDATE recurring_schedule_jobs
    SET ${updates.join(', ')}
    WHERE id = $1
    RETURNING *
  `, params);

  return result.rows[0] ? mapScheduleJobRow(result.rows[0]) : null;
}

// ============================================
// BACKGROUND JOB LOG CRUD
// ============================================

export async function pgCreateBackgroundJobLog(data: {
  jobId: string;
  jobType: string;
  status?: 'pending' | 'running' | 'success' | 'failed' | 'retry';
  createdBy?: string;
}): Promise<BackgroundJobLog | null> {
  const id = `log-${createId()}`;

  const result = await pgQuery<any>(`
    INSERT INTO background_job_logs (
      id, job_id, job_type, started_at, status, created_by
    ) VALUES ($1, $2, $3, NOW(), $4, $5)
    RETURNING *
  `, [
    id,
    data.jobId,
    data.jobType,
    data.status || 'pending',
    data.createdBy || null,
  ]);

  return result.rows[0] ? mapJobLogRow(result.rows[0]) : null;
}

export async function pgUpdateBackgroundJobLog(
  id: string,
  data: {
    status?: string;
    generatedDocuments?: number;
    failedDocuments?: number;
    errorMessage?: string;
    completedAt?: boolean; // Set to true to mark as completed
  }
): Promise<BackgroundJobLog | null> {
  const updates: string[] = [];
  const params: any[] = [id];
  let paramIndex = 2;

  if (data.status !== undefined) {
    updates.push(`status = $${paramIndex++}`);
    params.push(data.status);
  }
  if (data.generatedDocuments !== undefined) {
    updates.push(`generated_documents = $${paramIndex++}`);
    params.push(data.generatedDocuments);
  }
  if (data.failedDocuments !== undefined) {
    updates.push(`failed_documents = $${paramIndex++}`);
    params.push(data.failedDocuments);
  }
  if (data.errorMessage !== undefined) {
    updates.push(`error_message = $${paramIndex++}`);
    params.push(data.errorMessage);
  }
  if (data.completedAt) {
    updates.push(`completed_at = NOW()`);
  }

  if (updates.length === 0) {
    return pgQuery<any>(`SELECT * FROM background_job_logs WHERE id = $1`, [id])
      .then(r => r.rows[0] ? mapJobLogRow(r.rows[0]) : null);
  }

  const result = await pgQuery<any>(`
    UPDATE background_job_logs
    SET ${updates.join(', ')}
    WHERE id = $1
    RETURNING *
  `, params);

  return result.rows[0] ? mapJobLogRow(result.rows[0]) : null;
}

export async function pgListRecentJobLogs(
  jobId: string,
  limit: number = 10
): Promise<BackgroundJobLog[]> {
  const result = await pgQuery<any>(`
    SELECT * FROM background_job_logs 
    WHERE job_id = $1
    ORDER BY started_at DESC
    LIMIT $2
  `, [jobId, limit]);

  return result.rows.map(mapJobLogRow);
}

export async function pgListBackgroundJobLogs(filters: {
  jobId?: string;
  status?: 'pending' | 'running' | 'success' | 'failed' | 'retry';
  limit?: number;
} = {}): Promise<BackgroundJobLog[]> {
  const where: string[] = [];
  const params: unknown[] = [];

  if (filters.jobId) {
    params.push(filters.jobId);
    where.push(`job_id = $${params.length}`);
  }

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

  const limit = Math.max(1, Math.min(500, Number(filters.limit || 100)));
  params.push(limit);
  const whereSql = where.length ? `WHERE ${where.join(' AND ')}` : '';

  const result = await pgQuery<any>(`
    SELECT * FROM background_job_logs
    ${whereSql}
    ORDER BY started_at DESC
    LIMIT $${params.length}
  `, params);

  return result.rows.map(mapJobLogRow);
}

// ============================================
// GENERATION DUPLICATE PREVENTION
// ============================================

export async function pgCreateGenerationDuplicate(data: {
  jobId: string;
  templateId: string;
  periodStart: string;
  periodEnd: string;
  generatedDocumentId?: string;
}): Promise<RecurringGenerationDuplicate | null> {
  const id = `dup-${createId()}`;

  const result = await pgQuery<any>(`
    INSERT INTO recurring_generation_duplicates (
      id, job_id, template_id, period_start, period_end, generated_document_id
    ) VALUES ($1, $2, $3, $4, $5, $6)
    RETURNING *
  `, [
    id,
    data.jobId,
    data.templateId,
    toDateOnly(data.periodStart),
    toDateOnly(data.periodEnd),
    data.generatedDocumentId || null,
  ]);

  return result.rows[0] ? mapDuplicateRow(result.rows[0]) : null;
}

export async function pgCheckGenerationExists(
  templateId: string,
  periodStart: string,
  periodEnd: string
): Promise<boolean> {
  const result = await pgQuery<any>(`
    SELECT 1 FROM recurring_generation_duplicates 
    WHERE template_id = $1 
      AND period_start = $2 
      AND period_end = $3
    LIMIT 1
  `, [
    templateId,
    toDateOnly(periodStart),
    toDateOnly(periodEnd),
  ]);

  return result.rows.length > 0;
}

// ============================================
// ESCALATION EXECUTION TRACKING
// ============================================

export async function pgCreateEscalationExecution(data: {
  templateId: string;
  contractId?: string;
  escalationType: 'fixed_percent' | 'indexed' | 'tiered';
  escalationRuleId?: string;
  executionDate: string;
  newAmount: number;
  previousAmount?: number;
  increasePercent?: number;
  createdBy: string;
}): Promise<EscalationExecution | null> {
  const id = `esc-${createId()}`;

  const result = await pgQuery<any>(`
    INSERT INTO escalation_executions (
      id, template_id, contract_id, escalation_type, escalation_rule_id,
      execution_date, new_amount, previous_amount, increase_percent,
      status, created_by
    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, 'completed', $10)
    RETURNING *
  `, [
    id,
    data.templateId,
    data.contractId || null,
    data.escalationType,
    data.escalationRuleId || null,
    toDateOnly(data.executionDate),
    data.newAmount,
    data.previousAmount || null,
    data.increasePercent || null,
    data.createdBy,
  ]);

  return result.rows[0] ? mapEscalationRow(result.rows[0]) : null;
}

export async function pgListEscalationsByTemplate(
  templateId: string,
  limit: number = 50
): Promise<EscalationExecution[]> {
  const result = await pgQuery<any>(`
    SELECT * FROM escalation_executions 
    WHERE template_id = $1
    ORDER BY execution_date DESC
    LIMIT $2
  `, [templateId, limit]);

  return result.rows.map(mapEscalationRow);
}

// ============================================
// SCHEDULER STATUS MANAGEMENT
// ============================================

export async function pgGetSchedulerStatus(): Promise<SchedulerStatus | null> {
  const result = await pgQuery<any>(`
    SELECT * FROM scheduler_status WHERE id = 'scheduler-main'
  `);

  return result.rows[0] ? mapSchedulerStatusRow(result.rows[0]) : null;
}

export async function pgUpdateSchedulerStatus(data: Partial<SchedulerStatus>): Promise<SchedulerStatus | null> {
  const updates: string[] = [];
  const params: any[] = [];
  let paramIndex = 1;

  if (data.isRunning !== undefined) {
    updates.push(`is_running = $${paramIndex++}`);
    params.push(data.isRunning);
  }
  if (data.isHealthy !== undefined) {
    updates.push(`is_healthy = $${paramIndex++}`);
    params.push(data.isHealthy);
  }
  if (data.lastRunAt !== undefined) {
    updates.push(`last_run_at = $${paramIndex++}`);
    params.push(toIso(data.lastRunAt));
  }
  if (data.lastCheckAt !== undefined) {
    updates.push(`last_check_at = $${paramIndex++}`);
    params.push(toIso(data.lastCheckAt));
  }
  if (data.nextCheckAt !== undefined) {
    updates.push(`next_check_at = $${paramIndex++}`);
    params.push(toIso(data.nextCheckAt));
  }
  if (data.totalJobs !== undefined) {
    updates.push(`total_jobs = $${paramIndex++}`);
    params.push(data.totalJobs);
  }
  if (data.activeJobs !== undefined) {
    updates.push(`active_jobs = $${paramIndex++}`);
    params.push(data.activeJobs);
  }
  if (data.completedJobs !== undefined) {
    updates.push(`completed_jobs = $${paramIndex++}`);
    params.push(data.completedJobs);
  }
  if (data.failedJobs !== undefined) {
    updates.push(`failed_jobs = $${paramIndex++}`);
    params.push(data.failedJobs);
  }
  if (data.lastErrorMessage !== undefined) {
    updates.push(`last_error_message = $${paramIndex++}`);
    params.push(data.lastErrorMessage);
  }
  if (data.errorCount !== undefined) {
    updates.push(`error_count = $${paramIndex++}`);
    params.push(data.errorCount);
  }

  updates.push(`updated_at = NOW()`);

  if (updates.length === 1) return pgGetSchedulerStatus();

  const result = await pgQuery<any>(`
    UPDATE scheduler_status 
    SET ${updates.join(', ')}
    WHERE id = 'scheduler-main'
    RETURNING *
  `, params);

  return result.rows[0] ? mapSchedulerStatusRow(result.rows[0]) : null;
}

// ============================================
// ROW MAPPING FUNCTIONS
// ============================================

function mapScheduleJobRow(row: any): RecurringScheduleJob {
  return {
    id: row.id,
    templateId: row.template_id,
    contractId: row.contract_id || undefined,
    nextRunDate: row.next_run_date,
    status: row.status,
    retryCount: row.retry_count,
    maxRetries: row.max_retries,
    lastError: row.last_error || undefined,
    lastRunDate: row.last_run_date || undefined,
    totalRuns: row.total_runs,
    skipWeekends: row.skip_weekends,
    skipHolidays: row.skip_holidays,
    holidayShiftDays: row.holiday_shift_days,
    autoCreateObligation: row.auto_create_obligation,
    obligationBasis: row.obligation_basis,
    autoCreatePosting: row.auto_create_posting,
    createdBy: row.created_by,
    createdAt: row.created_at,
    updatedBy: row.updated_by || undefined,
    updatedAt: row.updated_at,
    raw: row.raw || undefined,
  };
}

function mapJobLogRow(row: any): BackgroundJobLog {
  return {
    id: row.id,
    jobId: row.job_id,
    jobType: row.job_type,
    startedAt: row.started_at,
    completedAt: row.completed_at || undefined,
    status: row.status,
    generatedDocuments: row.generated_documents,
    failedDocuments: row.failed_documents,
    errorMessage: row.error_message || undefined,
    errorStack: row.error_stack || undefined,
    createdBy: row.created_by || undefined,
    raw: row.raw || undefined,
  };
}

function mapDuplicateRow(row: any): RecurringGenerationDuplicate {
  return {
    id: row.id,
    jobId: row.job_id,
    templateId: row.template_id,
    periodStart: row.period_start,
    periodEnd: row.period_end,
    generatedAt: row.generated_at,
    attempts: row.attempts,
    generatedDocumentId: row.generated_document_id || undefined,
  };
}

function mapEscalationRow(row: any): EscalationExecution {
  return {
    id: row.id,
    templateId: row.template_id,
    contractId: row.contract_id || undefined,
    escalationType: row.escalation_type,
    escalationRuleId: row.escalation_rule_id || undefined,
    executionDate: row.execution_date,
    newAmount: Number(row.new_amount),
    previousAmount: row.previous_amount ? Number(row.previous_amount) : undefined,
    increasePercent: row.increase_percent ? Number(row.increase_percent) : undefined,
    status: row.status,
    errorMessage: row.error_message || undefined,
    createdBy: row.created_by,
    createdAt: row.created_at,
    raw: row.raw || undefined,
  };
}

function mapSchedulerStatusRow(row: any): SchedulerStatus {
  return {
    id: row.id,
    isRunning: row.is_running,
    isHealthy: row.is_healthy,
    lastRunAt: row.last_run_at || undefined,
    lastCheckAt: row.last_check_at || undefined,
    nextCheckAt: row.next_check_at || undefined,
    totalJobs: row.total_jobs,
    activeJobs: row.active_jobs,
    completedJobs: row.completed_jobs,
    failedJobs: row.failed_jobs,
    averageJobDurationMs: row.average_job_duration_ms || undefined,
    lastErrorMessage: row.last_error_message || undefined,
    errorCount: row.error_count,
    checkIntervalSeconds: row.check_interval_seconds,
    enableAutoGeneration: row.enable_auto_generation,
    enableEscalation: row.enable_escalation,
    enableAmortization: row.enable_amortization,
    updatedAt: row.updated_at,
    raw: row.raw || undefined,
  };
}
