import fs from "node:fs/promises";
import path from "node:path";
import { query } from "../../db/pool.js";
import { env } from "../../config/env.js";
import { recordAppEvent } from "../../lib/app-log.js";
import { incidentAttachmentsStorageDir, productMediaStorageDir, receiptsStorageDir } from "../../lib/storage.js";
import { purgeDueAccounts } from "../account/account.service.js";
import { runWarrantyReminderCycle } from "../notifications/warranty-reminders.service.js";
import { processPendingPurchaseDocuments } from "../purchases/purchases.service.js";
import { countWarrantyReminderCandidates } from "../products/products.service.js";

export interface MaintenanceBacklog {
  pendingPurgeUsers: number;
  expiredEmailVerificationTokens: number;
  expiredPasswordResetTokens: number;
  expiredAccountDeletionTokens: number;
  expiredAppLogs: number;
  expiredAdminAuditEntries: number;
  warrantyReminderCandidates: number;
  failedEmailOutbox: number;
  pendingEmailOutbox: number;
  pushSubscriptions: number;
  pushDeliveryFailures: number;
}

export interface MaintenanceRunSummary extends MaintenanceBacklog {
  purgedUsers: number;
  cleanedEmailVerificationTokens: number;
  cleanedPasswordResetTokens: number;
  cleanedAccountDeletionTokens: number;
  cleanedAppLogs: number;
  cleanedAdminAuditEntries: number;
  warrantyReminderSent: number;
  warrantyReminderCreatedOutbox: number;
  warrantyReminderPushSent: number;
  warrantyReminderPushFailed: number;
  warrantyReminderPushSkipped: number;
  warrantyReminderPushExpired: number;
  cleanedReceiptFiles: number;
  cleanedProductMediaFiles: number;
  cleanedIncidentAttachmentFiles: number;
}

export async function getMaintenanceBacklog(): Promise<MaintenanceBacklog> {
  const appLogRetentionCutoff = new Date(Date.now() - env.APP_LOG_RETENTION_DAYS * 24 * 60 * 60 * 1000).toISOString();
  const adminAuditRetentionCutoff = new Date(Date.now() - env.ADMIN_AUDIT_RETENTION_DAYS * 24 * 60 * 60 * 1000).toISOString();

  const [pendingPurgeUsers] = await query<{ count: string }>(`
    SELECT COUNT(*)::int AS count
    FROM users
    WHERE status = 'pending_deletion'
      AND deleted_at IS NULL
      AND deletion_scheduled_for IS NOT NULL
      AND deletion_scheduled_for <= NOW()
  `);

  const [expiredEmailVerificationTokens] = await query<{ count: string }>(`
    SELECT COUNT(*)::int AS count
    FROM email_verification_tokens
    WHERE consumed_at IS NULL
      AND expires_at <= NOW()
  `);

  const [expiredPasswordResetTokens] = await query<{ count: string }>(`
    SELECT COUNT(*)::int AS count
    FROM password_reset_tokens
    WHERE consumed_at IS NULL
      AND expires_at <= NOW()
  `);

  const [expiredAccountDeletionTokens] = await query<{ count: string }>(`
    SELECT COUNT(*)::int AS count
    FROM account_deletion_tokens
    WHERE consumed_at IS NULL
      AND expires_at <= NOW()
  `);

  const [expiredAppLogs] = await query<{ count: string }>(`
    SELECT COUNT(*)::int AS count
    FROM app_event_log
    WHERE created_at < $1::timestamptz
  `, [appLogRetentionCutoff]);

  const [expiredAdminAuditEntries] = await query<{ count: string }>(`
    SELECT COUNT(*)::int AS count
    FROM admin_audit_log
    WHERE created_at < $1::timestamptz
  `, [adminAuditRetentionCutoff]);

  const warrantyReminderCandidatesByDay = await Promise.all(
    env.WARRANTY_REMINDER_DAYS.map((daysAhead) => countWarrantyReminderCandidates(daysAhead))
  );

  const [failedEmailOutbox] = await query<{ count: string }>(`
    SELECT COUNT(*)::int AS count
    FROM email_outbox
    WHERE status = 'failed'
  `);

  const [pendingEmailOutbox] = await query<{ count: string }>(`
    SELECT COUNT(*)::int AS count
    FROM email_outbox
    WHERE status = 'pending'
  `);

  const [pushSubscriptions] = await query<{ count: string }>(`
    SELECT COUNT(*)::int AS count
    FROM push_subscriptions
  `);

  const [pushDeliveryFailures] = await query<{ count: string }>(`
    SELECT COUNT(*)::int AS count
    FROM app_event_log
    WHERE source = 'push.delivery'
      AND level = 'warning'
  `);

  return {
    pendingPurgeUsers: Number(pendingPurgeUsers?.count ?? 0),
    expiredEmailVerificationTokens: Number(expiredEmailVerificationTokens?.count ?? 0),
    expiredPasswordResetTokens: Number(expiredPasswordResetTokens?.count ?? 0),
    expiredAccountDeletionTokens: Number(expiredAccountDeletionTokens?.count ?? 0),
    expiredAppLogs: Number(expiredAppLogs?.count ?? 0),
    expiredAdminAuditEntries: Number(expiredAdminAuditEntries?.count ?? 0),
    warrantyReminderCandidates: warrantyReminderCandidatesByDay.reduce((total, count) => total + count, 0),
    failedEmailOutbox: Number(failedEmailOutbox?.count ?? 0),
    pendingEmailOutbox: Number(pendingEmailOutbox?.count ?? 0),
    pushSubscriptions: Number(pushSubscriptions?.count ?? 0),
    pushDeliveryFailures: Number(pushDeliveryFailures?.count ?? 0),
  };
}

async function cleanupExpiredVerificationTokens(): Promise<number> {
  const rows = await query<{ count: string }>(`
    WITH deleted AS (
      DELETE FROM email_verification_tokens
      WHERE consumed_at IS NOT NULL
         OR expires_at <= NOW()
      RETURNING 1
    )
    SELECT COUNT(*)::int AS count FROM deleted
  `);
  return Number(rows[0]?.count ?? 0);
}

async function cleanupExpiredPasswordResetTokens(): Promise<number> {
  const rows = await query<{ count: string }>(`
    WITH deleted AS (
      DELETE FROM password_reset_tokens
      WHERE consumed_at IS NOT NULL
         OR expires_at <= NOW()
      RETURNING 1
    )
    SELECT COUNT(*)::int AS count FROM deleted
  `);
  return Number(rows[0]?.count ?? 0);
}

async function cleanupExpiredDeletionTokens(): Promise<number> {
  const rows = await query<{ count: string }>(`
    WITH deleted AS (
      DELETE FROM account_deletion_tokens
      WHERE consumed_at IS NOT NULL
         OR expires_at <= NOW()
      RETURNING 1
    )
    SELECT COUNT(*)::int AS count FROM deleted
  `);
  return Number(rows[0]?.count ?? 0);
}

async function cleanupExpiredAppLogs(): Promise<number> {
  const cutoff = new Date(Date.now() - env.APP_LOG_RETENTION_DAYS * 24 * 60 * 60 * 1000).toISOString();
  const rows = await query<{ count: string }>(`
    WITH deleted AS (
      DELETE FROM app_event_log
      WHERE created_at < $1::timestamptz
      RETURNING 1
    )
    SELECT COUNT(*)::int AS count FROM deleted
  `, [cutoff]);
  return Number(rows[0]?.count ?? 0);
}

async function cleanupExpiredAdminAuditEntries(): Promise<number> {
  const cutoff = new Date(Date.now() - env.ADMIN_AUDIT_RETENTION_DAYS * 24 * 60 * 60 * 1000).toISOString();
  const rows = await query<{ count: string }>(`
    WITH deleted AS (
      DELETE FROM admin_audit_log
      WHERE created_at < $1::timestamptz
      RETURNING 1
    )
    SELECT COUNT(*)::int AS count FROM deleted
  `, [cutoff]);
  return Number(rows[0]?.count ?? 0);
}

async function cleanupOrphanedFiles(): Promise<{
  cleanedReceiptFiles: number;
  cleanedProductMediaFiles: number;
  cleanedIncidentAttachmentFiles: number;
}> {
  const [receiptRefs, productRefs, attachmentRefs] = await Promise.all([
    query<{ receipt_image: string | null }>("SELECT receipt_image FROM products WHERE receipt_image IS NOT NULL"),
    query<{ file_url: string }>("SELECT file_url FROM product_media"),
    query<{ file_url: string }>("SELECT file_url FROM incident_attachments"),
  ]);

  const keep = new Set<string>([
    ...receiptRefs.map((row) => row.receipt_image!).filter(Boolean),
    ...productRefs.map((row) => row.file_url),
    ...attachmentRefs.map((row) => row.file_url),
  ]);

  async function cleanDir(dir: string, prefix: string): Promise<number> {
    let deleted = 0;
    try {
      const files = await fs.readdir(dir, { withFileTypes: true });
      for (const file of files) {
        if (!file.isFile()) continue;
        const url = `${prefix}/${file.name}`;
        if (keep.has(url)) continue;
        try {
          await fs.unlink(path.join(dir, file.name));
          deleted += 1;
        } catch (err) {
          console.warn("[maintenance] orphan cleanup failed:", url, err);
        }
      }
    } catch (err) {
      console.warn("[maintenance] orphan cleanup directory failed:", dir, err);
    }
    return deleted;
  }

  return {
    cleanedReceiptFiles: await cleanDir(receiptsStorageDir, "/receipts"),
    cleanedProductMediaFiles: await cleanDir(productMediaStorageDir, "/files/product-media"),
    cleanedIncidentAttachmentFiles: await cleanDir(incidentAttachmentsStorageDir, "/files/incident-attachments"),
  };
}

export async function runMaintenanceCycle(): Promise<MaintenanceRunSummary> {
  const backlogBefore = await getMaintenanceBacklog();
  const [purgedUsers, cleanedEmailVerificationTokens, cleanedPasswordResetTokens, cleanedAccountDeletionTokens] = await Promise.all([
    purgeDueAccounts(100),
    cleanupExpiredVerificationTokens(),
    cleanupExpiredPasswordResetTokens(),
    cleanupExpiredDeletionTokens(),
  ]);
  const [cleanedAppLogs, cleanedAdminAuditEntries] = await Promise.all([
    cleanupExpiredAppLogs(),
    cleanupExpiredAdminAuditEntries(),
  ]);
  const cleanedFiles = await cleanupOrphanedFiles();
  const warrantyReminderCycle = await runWarrantyReminderCycle();
  const processedPurchaseDocuments = await processPendingPurchaseDocuments(20);

  const summary: MaintenanceRunSummary = {
    ...backlogBefore,
    purgedUsers,
    cleanedEmailVerificationTokens,
    cleanedPasswordResetTokens,
    cleanedAccountDeletionTokens,
    cleanedAppLogs,
    cleanedAdminAuditEntries,
    warrantyReminderSent: warrantyReminderCycle.sent,
    warrantyReminderCreatedOutbox: warrantyReminderCycle.createdOutbox,
    warrantyReminderPushSent: warrantyReminderCycle.pushSent,
    warrantyReminderPushFailed: warrantyReminderCycle.pushFailed,
    warrantyReminderPushSkipped: warrantyReminderCycle.pushSkipped,
    warrantyReminderPushExpired: warrantyReminderCycle.pushExpired,
    ...cleanedFiles,
  };

  await recordAppEvent({
    level: "info",
    source: "maintenance.daily",
    message: "Maintenance cycle executed",
    details: { ...summary, processedPurchaseDocuments },
  });

  return summary;
}