import fs from "node:fs/promises";
import { env } from "../../config/env.js";
import { pool, query } from "../../db/pool.js";
import { receiptsStorageDir, storageRoot } from "../../lib/storage.js";
import { isSmtpConfigured, sendPendingOutboxEmail } from "../../lib/mailer.js";
import { cancelPendingDeletionForUser, purgeUserImmediately } from "../account/account.service.js";
import { getMaintenanceBacklog } from "../maintenance/maintenance.service.js";

export interface AdminUserSummary {
  id: number;
  email: string;
  name: string;
  role: "admin" | "user";
  status: "active" | "blocked" | "pending_deletion";
  email_verified: boolean;
  access_type: "google" | "password" | "mixed";
  avatar_url: string | null;
  created_at: string;
  product_count: number;
  expiring_count: number;
  expired_count: number;
  last_product_at: string | null;
}

export interface AdminPlatformSummary {
  total_users: number;
  admin_users: number;
  regular_users: number;
  blocked_users: number;
  total_products: number;
  products_expiring_soon: number;
  expired_products: number;
  users_with_products: number;
  recent_users: AdminUserSummary[];
}

export interface AdminUserDetail extends AdminUserSummary {
  active_count: number;
  recent_products: Array<{
    id: number;
    name: string;
    brand: string;
    category: string;
    warranty_end_date: string;
    created_at: string;
  }>;
}

export interface AdminAuditEntry {
  id: number;
  actor_user_id: number | null;
  actor_name: string | null;
  actor_email: string | null;
  target_user_id: number | null;
  target_name: string | null;
  target_email: string | null;
  action: string;
  details: Record<string, unknown>;
  created_at: string;
}

export interface AdminAppLogEntry {
  id: number;
  level: "info" | "warning" | "error";
  source: string;
  message: string;
  details: Record<string, unknown>;
  created_at: string;
}

export interface AdminEmailOutboxEntry {
  id: number;
  user_id: number | null;
  recipient: string;
  subject: string;
  body: string;
  status: "pending" | "sent" | "failed";
  created_at: string;
  sent_at: string | null;
}

export interface AdminPublicReviewEntry {
  id: number;
  user_id: number;
  user_name: string;
  user_email: string;
  incident_number: string;
  title: string;
  store_label: string;
  brand_label: string;
  product_name: string;
  rating: number;
  notes: string;
  share_public_rating: boolean;
  updated_at: string;
}

export interface AdminIncidentEntry {
  id: number;
  incident_number: string;
  title: string;
  status: string;
  incident_type: string;
  user_id: number;
  user_name: string;
  user_email: string;
  product_name: string;
  product_brand: string;
  product_model: string;
  user_rating: number | null;
  share_public_rating: boolean;
  attachment_count: number;
  media_count: number;
  updated_at: string;
}

export interface AdminIncidentDetail extends AdminIncidentEntry {
  product_id: number;
  product_status: string;
  product_days_remaining: number;
  description: string;
  symptom: string;
  accessories: string;
  sat_name: string;
  sat_reference: string;
  diagnosis: string;
  technician_notes: string;
  estimated_cost: string | null;
  resolution: string;
  replaced_with_new: boolean;
  replaced_with_refurbished: boolean;
  closed_at: string | null;
  created_at: string;
  attachments: Array<{
    id: number;
    file_url: string;
    file_name: string;
    mime_type: string;
    caption: string;
    sort_order: number;
    created_at: string;
  }>;
  media: Array<{
    id: number;
    file_url: string;
    file_name: string;
    mime_type: string;
    caption: string;
    sort_order: number;
    created_at: string;
  }>;
}

export interface AdminPublicReviewFilters {
  search?: string;
  sharePublicRating?: boolean;
  page: number;
  pageSize: number;
}

export interface PaginatedResult<T> {
  items: T[];
  total: number;
  page: number;
  pageSize: number;
  totalPages: number;
}

export interface AdminUserFilters {
  search?: string;
  role?: "admin" | "user";
  status?: "active" | "blocked" | "pending_deletion";
  emailVerified?: boolean;
  accessType?: "google" | "password" | "mixed";
  page: number;
  pageSize: number;
}

export interface AdminIncidentFilters {
  search?: string;
  status?: string;
  page: number;
  pageSize: number;
}

export interface AdminAuditFilters {
  search?: string;
  action?: "user.role.update" | "user.status.update" | "user.deletion.cancel" | "user.delete";
  from?: string;
  to?: string;
  page: number;
  pageSize: number;
}

export interface AdminAppLogFilters {
  search?: string;
  level?: "info" | "warning" | "error";
  source?: string;
  from?: string;
  to?: string;
  page: number;
  pageSize: number;
}

export interface AdminEmailOutboxFilters {
  search?: string;
  status?: "pending" | "sent" | "failed";
  page: number;
  pageSize: number;
}

export interface AdminSystemHealth {
  api: {
    status: "ok";
    nodeEnv: string;
    uptimeSeconds: number;
    version: string;
  };
  database: {
    status: "ok" | "error";
    migrations: number;
  };
  storage: {
    status: "ok" | "error";
    root: string;
    receiptsPath: string;
    receiptFiles: number;
  };
  security: {
    jwtSecretConfigured: boolean;
    jwtSecretLooksDefault: boolean;
    googleOAuthConfigured: boolean;
    smtpConfigured: boolean;
    smtpAuthenticated: boolean;
    smtpLastError: string | null;
    smtpLastErrorAt: string | null;
  };
  maintenance: {
    pendingPurgeUsers: number;
    expiredEmailVerificationTokens: number;
    expiredPasswordResetTokens: number;
    expiredAccountDeletionTokens: number;
    expiredAppLogs: number;
    expiredAdminAuditEntries: number;
    warrantyReminderCandidates: number;
    failedEmailOutbox: number;
    pendingEmailOutbox: number;
    pushSubscriptions: number;
    pushDeliveryFailures: number;
    appLogRetentionDays: number;
    adminAuditRetentionDays: number;
    warrantyReminderDays: number[];
    notificationEmailBatchLimit: number;
  };
}

function toNumber(value: string | number | null | undefined): number {
  return Number(value ?? 0);
}

function paginate<T>(items: T[], total: number, page: number, pageSize: number): PaginatedResult<T> {
  return {
    items,
    total,
    page,
    pageSize,
    totalPages: Math.max(1, Math.ceil(total / pageSize)),
  };
}

function mapAdminUser(row: AdminUserSummary): AdminUserSummary {
  return {
    ...row,
    product_count: toNumber(row.product_count),
    expiring_count: toNumber(row.expiring_count),
    expired_count: toNumber(row.expired_count),
  };
}

function accessTypeExpression(alias?: string): string {
  const prefix = alias ? `${alias}.` : "";
  return `
    CASE
      WHEN ${prefix}google_id IS NOT NULL AND ${prefix}password_hash IS NOT NULL THEN 'mixed'
      WHEN ${prefix}google_id IS NOT NULL THEN 'google'
      ELSE 'password'
    END
  `;
}

const USERS_WITH_COUNTS = `
  SELECT
    u.id,
    u.email,
    u.name,
    u.role,
    u.status,
    (u.email_verified_at IS NOT NULL) AS email_verified,
    ${accessTypeExpression("u")} AS access_type,
    u.avatar_url,
    u.created_at,
    COUNT(p.id)::int AS product_count,
    COUNT(p.id) FILTER (
      WHERE p.warranty_end_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '90 days'
    )::int AS expiring_count,
    COUNT(p.id) FILTER (
      WHERE p.warranty_end_date < CURRENT_DATE
    )::int AS expired_count,
    MAX(p.created_at) AS last_product_at
  FROM users u
  LEFT JOIN products p ON p.user_id = u.id
`;

export async function getAdminSummary(): Promise<AdminPlatformSummary> {
  const totals = await query<{
    total_users: string;
    admin_users: string;
    regular_users: string;
    blocked_users: string;
    total_products: string;
    products_expiring_soon: string;
    expired_products: string;
    users_with_products: string;
  }>(`
    SELECT
      (SELECT COUNT(*) FROM users)::int AS total_users,
      (SELECT COUNT(*) FROM users WHERE role = 'admin')::int AS admin_users,
      (SELECT COUNT(*) FROM users WHERE role = 'user')::int AS regular_users,
      (SELECT COUNT(*) FROM users WHERE status = 'blocked')::int AS blocked_users,
      (SELECT COUNT(*) FROM products)::int AS total_products,
      (SELECT COUNT(*) FROM products WHERE warranty_end_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '90 days')::int AS products_expiring_soon,
      (SELECT COUNT(*) FROM products WHERE warranty_end_date < CURRENT_DATE)::int AS expired_products,
      (SELECT COUNT(DISTINCT user_id) FROM products)::int AS users_with_products
  `);

  const recentUsers = await query<AdminUserSummary>(`
    ${USERS_WITH_COUNTS}
    GROUP BY u.id
    ORDER BY u.created_at DESC
    LIMIT 6
  `);

  const t = totals[0];
  return {
    total_users: toNumber(t?.total_users),
    admin_users: toNumber(t?.admin_users),
    regular_users: toNumber(t?.regular_users),
    blocked_users: toNumber(t?.blocked_users),
    total_products: toNumber(t?.total_products),
    products_expiring_soon: toNumber(t?.products_expiring_soon),
    expired_products: toNumber(t?.expired_products),
    users_with_products: toNumber(t?.users_with_products),
    recent_users: recentUsers.map(mapAdminUser),
  };
}

export async function listAdminUsers(filters: AdminUserFilters): Promise<PaginatedResult<AdminUserSummary>> {
  const whereParts: string[] = [];
  const params: unknown[] = [];
  const term = filters.search?.trim();

  if (term) {
    params.push(`%${term}%`);
    whereParts.push(`(u.email ILIKE $${params.length} OR u.name ILIKE $${params.length})`);
  }
  if (filters.role) {
    params.push(filters.role);
    whereParts.push(`u.role = $${params.length}`);
  }
  if (filters.status) {
    params.push(filters.status);
    whereParts.push(`u.status = $${params.length}`);
  }
  if (typeof filters.emailVerified === "boolean") {
    params.push(filters.emailVerified);
    whereParts.push(`(u.email_verified_at IS NOT NULL) = $${params.length}`);
  }
  if (filters.accessType) {
    params.push(filters.accessType);
    whereParts.push(`${accessTypeExpression()} = $${params.length}`);
  }

  const where = whereParts.length ? `WHERE ${whereParts.join(" AND ")}` : "";
  const countRows = await query<{ count: string }>(
    `SELECT COUNT(*)::int AS count FROM users u ${where}`,
    params
  );

  const total = toNumber(countRows[0]?.count);
  const offset = (filters.page - 1) * filters.pageSize;
  const pageParams = [...params, filters.pageSize, offset];

  const rows = await query<AdminUserSummary>(`
    ${USERS_WITH_COUNTS}
    ${where}
    GROUP BY u.id
    ORDER BY u.created_at DESC
    LIMIT $${pageParams.length - 1}
    OFFSET $${pageParams.length}
  `, pageParams);

  return paginate(rows.map(mapAdminUser), total, filters.page, filters.pageSize);
}

export async function getAdminUserDetail(id: number): Promise<AdminUserDetail | null> {
  const users = await query<AdminUserDetail>(`
    ${USERS_WITH_COUNTS}
    WHERE u.id = $1
    GROUP BY u.id
  `, [id]);

  const user = users[0];
  if (!user) return null;

  const activeCounts = await query<{ active_count: string }>(`
    SELECT COUNT(*)::int AS active_count
    FROM products
    WHERE user_id = $1
      AND warranty_end_date >= CURRENT_DATE
  `, [id]);

  const recentProducts = await query<AdminUserDetail["recent_products"][number]>(`
    SELECT id, name, brand, category, warranty_end_date, created_at
    FROM products
    WHERE user_id = $1
    ORDER BY created_at DESC
    LIMIT 8
  `, [id]);

  return {
    ...mapAdminUser(user),
    active_count: toNumber(activeCounts[0]?.active_count),
    recent_products: recentProducts,
  };
}

async function countActiveAdmins(): Promise<number> {
  const rows = await query<{ count: string }>(
    "SELECT COUNT(*)::int AS count FROM users WHERE role = 'admin' AND status = 'active'"
  );
  return toNumber(rows[0]?.count);
}

async function logAdminAction(
  actorUserId: number,
  targetUserId: number,
  action: string,
  details: Record<string, unknown>
): Promise<void> {
  await query(
    "INSERT INTO admin_audit_log (actor_user_id, target_user_id, action, details) VALUES ($1, $2, $3, $4)",
    [actorUserId, targetUserId, action, JSON.stringify(details)]
  );
}

export async function updateAdminUserRole(
  actorUserId: number,
  targetUserId: number,
  role: "admin" | "user"
): Promise<AdminUserSummary | null> {
  const current = await query<{ id: number; role: "admin" | "user"; status: "active" | "blocked" | "pending_deletion" }>(
    "SELECT id, role, status FROM users WHERE id = $1",
    [targetUserId]
  );
  const target = current[0];
  if (!target) return null;

  if (target.role === "admin" && role !== "admin" && target.status === "active") {
    const admins = await countActiveAdmins();
    if (admins <= 1) {
      throw new Error("LAST_ACTIVE_ADMIN");
    }
  }

  const updated = await query<AdminUserSummary>(`
    UPDATE users
    SET role = $1
    WHERE id = $2
    RETURNING
      id, email, name, role, status,
      (email_verified_at IS NOT NULL) AS email_verified,
      ${accessTypeExpression()} AS access_type,
      avatar_url, created_at,
      0::int AS product_count,
      0::int AS expiring_count,
      0::int AS expired_count,
      NULL::timestamptz AS last_product_at
  `, [role, targetUserId]);

  await logAdminAction(actorUserId, targetUserId, "user.role.update", { from: target.role, to: role });
  return getAdminUserDetail(updated[0]!.id);
}

export async function updateAdminUserStatus(
  actorUserId: number,
  targetUserId: number,
  status: "active" | "blocked" | "pending_deletion"
): Promise<AdminUserSummary | null> {
  const current = await query<{ id: number; role: "admin" | "user"; status: "active" | "blocked" | "pending_deletion" }>(
    "SELECT id, role, status FROM users WHERE id = $1",
    [targetUserId]
  );
  const target = current[0];
  if (!target) return null;

  if (target.id === actorUserId && status !== "active") {
    throw new Error("SELF_BLOCK");
  }

  if (target.role === "admin" && target.status === "active" && status !== "active") {
    const admins = await countActiveAdmins();
    if (admins <= 1) {
      throw new Error("LAST_ACTIVE_ADMIN");
    }
  }

  if (status === "active" && target.status === "pending_deletion") {
    await cancelPendingDeletionForUser(targetUserId);
  } else {
    await query(
      "UPDATE users SET status = $1 WHERE id = $2",
      [status, targetUserId]
    );
  }

  const action = target.status === "pending_deletion" && status === "active"
    ? "user.deletion.cancel"
    : "user.status.update";
  await logAdminAction(actorUserId, targetUserId, action, { from: target.status, to: status });
  return getAdminUserDetail(targetUserId);
}

export async function deleteAdminUser(
  actorUserId: number,
  targetUserId: number
): Promise<boolean> {
  const current = await query<{ id: number; role: "admin" | "user"; status: "active" | "blocked" | "pending_deletion" }>(
    "SELECT id, role, status FROM users WHERE id = $1",
    [targetUserId]
  );
  const target = current[0];
  if (!target) return false;

  if (target.id === actorUserId) {
    throw new Error("SELF_DELETE");
  }

  if (target.role === "admin") {
    const admins = await countActiveAdmins();
    if (admins <= 1 && target.status === "active") {
      throw new Error("LAST_ACTIVE_ADMIN");
    }
    if (admins <= 1 && target.status !== "active") {
      throw new Error("LAST_ADMIN");
    }
  }

  await logAdminAction(actorUserId, targetUserId, "user.delete", { status: target.status, role: target.role });

  const deleted = await purgeUserImmediately(targetUserId);
  if (!deleted) return false;

  return true;
}

export async function listAdminPublicReviews(filters: AdminPublicReviewFilters): Promise<PaginatedResult<AdminPublicReviewEntry>> {
  const whereParts: string[] = [];
  const params: unknown[] = [];
  const term = filters.search?.trim();

  if (term) {
    params.push(`%${term}%`);
    whereParts.push(`(
      i.incident_number ILIKE $${params.length}
      OR i.title ILIKE $${params.length}
      OR u.email ILIKE $${params.length}
      OR u.name ILIKE $${params.length}
      OR p.name ILIKE $${params.length}
      OR COALESCE(i.user_rating_notes, '') ILIKE $${params.length}
      OR COALESCE(pd.vendor_canonical, pd.vendor_raw, p.vendor, '') ILIKE $${params.length}
      OR COALESCE(pl.brand_raw, p.brand, '') ILIKE $${params.length}
    )`);
  }
  if (typeof filters.sharePublicRating === "boolean") {
    params.push(filters.sharePublicRating);
    whereParts.push(`i.share_public_rating = $${params.length}`);
  }

  const where = whereParts.length ? `WHERE ${whereParts.join(" AND ")}` : "";
  const countRows = await query<{ count: string }>(`
    SELECT COUNT(*)::int AS count
    FROM repair_incidents i
    INNER JOIN users u ON u.id = i.user_id
    LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
    LEFT JOIN purchase_document_lines pl ON pl.id = i.purchase_document_line_id AND pl.user_id = i.user_id
    LEFT JOIN purchase_documents pd ON pd.id = pl.document_id AND pd.user_id = i.user_id
    ${where}
  `, params);

  const total = toNumber(countRows[0]?.count);
  const offset = (filters.page - 1) * filters.pageSize;
  const pageParams = [...params, filters.pageSize, offset];

  const rows = await query<AdminPublicReviewEntry>(`
    SELECT
      i.id,
      i.user_id,
      u.name AS user_name,
      u.email AS user_email,
      i.incident_number,
      COALESCE(NULLIF(TRIM(i.title), ''), NULLIF(TRIM(p.name), ''), 'Incidencia sin titulo') AS title,
      COALESCE(NULLIF(TRIM(pd.vendor_canonical), ''), NULLIF(TRIM(pd.vendor_raw), ''), NULLIF(TRIM(p.vendor), ''), 'Sin tienda') AS store_label,
      COALESCE(NULLIF(TRIM(pl.brand_raw), ''), NULLIF(TRIM(p.brand), ''), 'Sin marca') AS brand_label,
      COALESCE(NULLIF(TRIM(p.name), ''), 'Producto sin nombre') AS product_name,
      COALESCE(i.user_rating, 0)::int AS rating,
      COALESCE(NULLIF(TRIM(i.user_rating_notes), ''), '') AS notes,
      i.share_public_rating,
      i.updated_at
    FROM repair_incidents i
    INNER JOIN users u ON u.id = i.user_id
    LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
    LEFT JOIN purchase_document_lines pl ON pl.id = i.purchase_document_line_id AND pl.user_id = i.user_id
    LEFT JOIN purchase_documents pd ON pd.id = pl.document_id AND pd.user_id = i.user_id
    ${where}
    ORDER BY i.updated_at DESC, i.id DESC
    LIMIT $${pageParams.length - 1}
    OFFSET $${pageParams.length}
  `, pageParams);

  return paginate(rows.map((row) => ({
    ...row,
    user_id: toNumber(row.user_id),
    rating: toNumber(row.rating),
  })), total, filters.page, filters.pageSize);
}

export async function listAdminIncidents(filters: AdminIncidentFilters): Promise<PaginatedResult<AdminIncidentEntry>> {
  const whereParts: string[] = [];
  const params: unknown[] = [];
  const term = filters.search?.trim();

  if (term) {
    params.push(`%${term}%`);
    whereParts.push(`(
      i.incident_number ILIKE $${params.length}
      OR COALESCE(i.title, '') ILIKE $${params.length}
      OR u.email ILIKE $${params.length}
      OR u.name ILIKE $${params.length}
      OR COALESCE(p.name, '') ILIKE $${params.length}
      OR COALESCE(p.brand, '') ILIKE $${params.length}
      OR COALESCE(p.model, '') ILIKE $${params.length}
    )`);
  }

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

  const where = whereParts.length ? `WHERE ${whereParts.join(" AND ")}` : "";
  const countRows = await query<{ count: string }>(`
    SELECT COUNT(*)::int AS count
    FROM repair_incidents i
    INNER JOIN users u ON u.id = i.user_id
    LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
    ${where}
  `, params);

  const total = toNumber(countRows[0]?.count);
  const offset = (filters.page - 1) * filters.pageSize;
  const pageParams = [...params, filters.pageSize, offset];

  const rows = await query<AdminIncidentEntry>(`
    SELECT
      i.id,
      i.incident_number,
      COALESCE(NULLIF(TRIM(i.title), ''), NULLIF(TRIM(p.name), ''), 'Incidencia sin titulo') AS title,
      i.status,
      i.incident_type,
      i.user_id,
      COALESCE(p.id, 0)::int AS product_id,
      u.name AS user_name,
      u.email AS user_email,
      COALESCE(NULLIF(TRIM(p.name), ''), 'Producto sin nombre') AS product_name,
      COALESCE(NULLIF(TRIM(p.brand), ''), 'Sin marca') AS product_brand,
      COALESCE(NULLIF(TRIM(p.model), ''), 'Sin modelo') AS product_model,
      i.user_rating,
      i.share_public_rating,
      COALESCE(att.attachment_count, 0)::int AS attachment_count,
      COALESCE(media.media_count, 0)::int AS media_count,
      i.updated_at
    FROM repair_incidents i
    INNER JOIN users u ON u.id = i.user_id
    LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
    LEFT JOIN (
      SELECT incident_id, COUNT(*) AS attachment_count
      FROM incident_attachments
      GROUP BY incident_id
    ) att ON att.incident_id = i.id
    LEFT JOIN (
      SELECT product_id, COUNT(*) AS media_count
      FROM product_media
      GROUP BY product_id
    ) media ON media.product_id = i.product_id
    ${where}
    ORDER BY i.updated_at DESC, i.id DESC
    LIMIT $${pageParams.length - 1}
    OFFSET $${pageParams.length}
  `, pageParams);

  return paginate(rows.map((row) => ({
    ...row,
    id: toNumber(row.id),
    user_id: toNumber(row.user_id),
    user_rating: row.user_rating === null ? null : toNumber(row.user_rating),
    attachment_count: toNumber(row.attachment_count),
    media_count: toNumber(row.media_count),
  })), total, filters.page, filters.pageSize);
}

export async function getAdminIncidentDetail(id: number): Promise<AdminIncidentDetail | null> {
  const rows = await query<AdminIncidentDetail>(`
    SELECT
      i.id,
      i.incident_number,
      COALESCE(NULLIF(TRIM(i.title), ''), NULLIF(TRIM(p.name), ''), 'Incidencia sin titulo') AS title,
      i.status,
      i.incident_type,
      i.user_id,
      u.name AS user_name,
      u.email AS user_email,
      COALESCE(NULLIF(TRIM(p.name), ''), 'Producto sin nombre') AS product_name,
      COALESCE(NULLIF(TRIM(p.brand), ''), 'Sin marca') AS product_brand,
      COALESCE(NULLIF(TRIM(p.model), ''), 'Sin modelo') AS product_model,
      COALESCE(
        CASE
          WHEN GREATEST(
            p.warranty_end_date,
            CASE WHEN p.has_extended_warranty THEN p.extended_warranty_end_date ELSE NULL END,
            CASE WHEN p.has_insurance THEN p.insurance_end_date ELSE NULL END
          ) < CURRENT_DATE THEN 'expired'
          WHEN GREATEST(
            p.warranty_end_date,
            CASE WHEN p.has_extended_warranty THEN p.extended_warranty_end_date ELSE NULL END,
            CASE WHEN p.has_insurance THEN p.insurance_end_date ELSE NULL END
          ) <= CURRENT_DATE + INTERVAL '30 days' THEN 'critical'
          WHEN GREATEST(
            p.warranty_end_date,
            CASE WHEN p.has_extended_warranty THEN p.extended_warranty_end_date ELSE NULL END,
            CASE WHEN p.has_insurance THEN p.insurance_end_date ELSE NULL END
          ) <= CURRENT_DATE + INTERVAL '90 days' THEN 'warning'
          ELSE 'active'
        END,
        'active'
      ) AS product_status,
      COALESCE(
        (
          GREATEST(
            p.warranty_end_date,
            CASE WHEN p.has_extended_warranty THEN p.extended_warranty_end_date ELSE NULL END,
            CASE WHEN p.has_insurance THEN p.insurance_end_date ELSE NULL END
          ) - CURRENT_DATE
        )::int,
        0
      ) AS product_days_remaining,
      i.description,
      i.symptom,
      i.accessories,
      i.sat_name,
      i.sat_reference,
      i.diagnosis,
      i.technician_notes,
      i.estimated_cost,
      i.resolution,
      i.replaced_with_new,
      i.replaced_with_refurbished,
      i.user_rating,
      i.share_public_rating,
      i.closed_at,
      i.created_at,
      i.updated_at,
      COALESCE(att.attachments, '[]'::json) AS attachments,
      COALESCE(media.media, '[]'::json) AS media
    FROM repair_incidents i
    INNER JOIN users u ON u.id = i.user_id
    LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
    LEFT JOIN LATERAL (
      SELECT json_agg(json_build_object(
        'id', ia.id,
        'file_url', ia.file_url,
        'file_name', ia.file_name,
        'mime_type', ia.mime_type,
        'caption', ia.caption,
        'sort_order', ia.sort_order,
        'created_at', ia.created_at
      ) ORDER BY ia.sort_order ASC, ia.created_at ASC, ia.id ASC) AS attachments
      FROM incident_attachments ia
      WHERE ia.incident_id = i.id
    ) att ON TRUE
    LEFT JOIN LATERAL (
      SELECT json_agg(json_build_object(
        'id', pm.id,
        'file_url', pm.file_url,
        'file_name', pm.file_name,
        'mime_type', pm.mime_type,
        'caption', pm.caption,
        'sort_order', pm.sort_order,
        'created_at', pm.created_at
      ) ORDER BY pm.sort_order ASC, pm.created_at ASC, pm.id ASC) AS media
      FROM product_media pm
      WHERE pm.product_id = i.product_id
    ) media ON TRUE
    WHERE i.id = $1
    LIMIT 1
  `, [id]);

  const row = rows[0];
  if (!row) return null;

  return {
    ...row,
    id: toNumber(row.id),
    user_id: toNumber(row.user_id),
    product_id: toNumber(row.product_id),
    product_days_remaining: toNumber(row.product_days_remaining),
    user_rating: row.user_rating === null ? null : toNumber(row.user_rating),
    estimated_cost: row.estimated_cost,
    attachments: Array.isArray(row.attachments) ? row.attachments.map((item: any) => ({
      id: toNumber(item.id),
      file_url: String(item.file_url ?? ""),
      file_name: String(item.file_name ?? ""),
      mime_type: String(item.mime_type ?? ""),
      caption: String(item.caption ?? ""),
      sort_order: toNumber(item.sort_order),
      created_at: String(item.created_at ?? ""),
    })) : [],
    media: Array.isArray(row.media) ? row.media.map((item: any) => ({
      id: toNumber(item.id),
      file_url: String(item.file_url ?? ""),
      file_name: String(item.file_name ?? ""),
      mime_type: String(item.mime_type ?? ""),
      caption: String(item.caption ?? ""),
      sort_order: toNumber(item.sort_order),
      created_at: String(item.created_at ?? ""),
    })) : [],
  };
}

export async function setAdminPublicReviewVisibility(
  actorUserId: number,
  reviewId: number,
  sharePublicRating: boolean
): Promise<AdminPublicReviewEntry | null> {
  const rows = await query<AdminPublicReviewEntry>(`
    UPDATE repair_incidents
    SET share_public_rating = $1
    WHERE id = $2
    RETURNING
      id,
      user_id,
      '' AS user_name,
      '' AS user_email,
      incident_number,
      title,
      '' AS store_label,
      '' AS brand_label,
      '' AS product_name,
      COALESCE(user_rating, 0)::int AS rating,
      COALESCE(NULLIF(TRIM(user_rating_notes), ''), '') AS notes,
      share_public_rating,
      updated_at
  `, [sharePublicRating, reviewId]);

  if (!rows[0]) return null;
  await logAdminAction(actorUserId, rows[0].user_id, "review.visibility.update", {
    reviewId,
    sharePublicRating,
  });
  return rows[0];
}

export async function listAdminAuditLog(filters: AdminAuditFilters): Promise<PaginatedResult<AdminAuditEntry>> {
  const whereParts: string[] = [];
  const params: unknown[] = [];
  const term = filters.search?.trim();

  if (term) {
    params.push(`%${term}%`);
    whereParts.push(`(
      l.action ILIKE $${params.length}
      OR actor.email ILIKE $${params.length}
      OR actor.name ILIKE $${params.length}
      OR target.email ILIKE $${params.length}
      OR target.name ILIKE $${params.length}
    )`);
  }

  if (filters.action) {
    params.push(filters.action);
    whereParts.push(`l.action = $${params.length}`);
  }

  if (filters.from) {
    params.push(filters.from);
    whereParts.push(`l.created_at >= $${params.length}::date`);
  }

  if (filters.to) {
    params.push(filters.to);
    whereParts.push(`l.created_at < ($${params.length}::date + INTERVAL '1 day')`);
  }

  const where = whereParts.length ? `WHERE ${whereParts.join(" AND ")}` : "";
  const from = `
    FROM admin_audit_log l
    LEFT JOIN users actor ON actor.id = l.actor_user_id
    LEFT JOIN users target ON target.id = l.target_user_id
    ${where}
  `;

  const countRows = await query<{ count: string }>(`SELECT COUNT(*)::int AS count ${from}`, params);
  const total = toNumber(countRows[0]?.count);
  const offset = (filters.page - 1) * filters.pageSize;
  const pageParams = [...params, filters.pageSize, offset];

  const rows = await query<AdminAuditEntry>(`
    SELECT
      l.id,
      l.actor_user_id,
      actor.name AS actor_name,
      actor.email AS actor_email,
      l.target_user_id,
      target.name AS target_name,
      target.email AS target_email,
      l.action,
      l.details,
      l.created_at
    ${from}
    ORDER BY l.created_at DESC
    LIMIT $${pageParams.length - 1}
    OFFSET $${pageParams.length}
  `, pageParams);

  return paginate(rows, total, filters.page, filters.pageSize);
}

function csvCell(value: unknown): string {
  const text = value === null || value === undefined ? "" : String(value);
  return `"${text.replace(/"/g, '""')}"`;
}

export async function exportAdminUsersCsv(): Promise<string> {
  const users = await query<AdminUserSummary>(`
    ${USERS_WITH_COUNTS}
    GROUP BY u.id
    ORDER BY u.created_at DESC
  `);

  const header = [
    "id",
    "email",
    "name",
    "role",
    "status",
    "created_at",
    "product_count",
    "expiring_count",
    "expired_count",
    "last_product_at",
  ];

  const lines = [
    header.map(csvCell).join(","),
    ...users.map((user) => [
      user.id,
      user.email,
      user.name,
      user.role,
      user.status,
      user.created_at,
      user.product_count,
      user.expiring_count,
      user.expired_count,
      user.last_product_at,
    ].map(csvCell).join(",")),
  ];

  return `${lines.join("\n")}\n`;
}

export async function exportAdminMetricsCsv(): Promise<string> {
  const summary = await getAdminSummary();
  const categoryRows = await query<{ category: string; count: string }>(`
    SELECT category, COUNT(*)::int AS count
    FROM products
    GROUP BY category
    ORDER BY count DESC, category ASC
  `);

  const metricRows: Array<[string, string | number]> = [
    ["total_users", summary.total_users],
    ["admin_users", summary.admin_users],
    ["regular_users", summary.regular_users],
    ["blocked_users", summary.blocked_users],
    ["total_products", summary.total_products],
    ["products_expiring_soon", summary.products_expiring_soon],
    ["expired_products", summary.expired_products],
    ["users_with_products", summary.users_with_products],
    ...categoryRows.map((row): [string, number] => [`products_category_${row.category || "other"}`, toNumber(row.count)]),
  ];

  const lines = [
    ["metric", "value"].map(csvCell).join(","),
    ...metricRows.map((row) => row.map(csvCell).join(",")),
  ];

  return `${lines.join("\n")}\n`;
}

export async function getAdminSystemHealth(): Promise<AdminSystemHealth> {
  let databaseStatus: AdminSystemHealth["database"]["status"] = "ok";
  let migrations = 0;

  try {
    const rows = await query<{ count: string }>("SELECT COUNT(*)::int AS count FROM schema_migrations");
    migrations = toNumber(rows[0]?.count);
    await pool.query("SELECT 1");
  } catch {
    databaseStatus = "error";
  }

  let storageStatus: AdminSystemHealth["storage"]["status"] = "ok";
  let receiptFiles = 0;

  try {
    await fs.mkdir(receiptsStorageDir, { recursive: true });
    const files = await fs.readdir(receiptsStorageDir, { withFileTypes: true });
    receiptFiles = files.filter((file) => file.isFile()).length;
  } catch {
    storageStatus = "error";
  }

  const smtpAuthConfigured = Boolean(env.SMTP_USER && env.SMTP_PASS);
  const smtpEvents = await query<{ level: "info" | "warning" | "error"; message: string; created_at: string }>(`
    SELECT level, message, created_at
    FROM app_event_log
    WHERE source = 'mail.smtp'
    ORDER BY created_at DESC
    LIMIT 1
  `);
  const lastSmtpEvent = smtpEvents[0] ?? null;
  const maintenanceBacklog = await getMaintenanceBacklog();

  return {
    api: {
      status: "ok",
      nodeEnv: env.NODE_ENV,
      uptimeSeconds: Math.round(process.uptime()),
      version: process.env.npm_package_version ?? "0.1.0",
    },
    database: {
      status: databaseStatus,
      migrations,
    },
    storage: {
      status: storageStatus,
      root: storageRoot,
      receiptsPath: receiptsStorageDir,
      receiptFiles,
    },
    security: {
      jwtSecretConfigured: env.JWT_SECRET.length >= 16,
      jwtSecretLooksDefault: env.JWT_SECRET === "change-me-to-a-long-random-secret",
      googleOAuthConfigured: env.GOOGLE_CLIENT_ID.length > 0,
      smtpConfigured: isSmtpConfigured(),
      smtpAuthenticated: smtpAuthConfigured,
      smtpLastError: lastSmtpEvent?.level === "error" ? lastSmtpEvent.message : null,
      smtpLastErrorAt: lastSmtpEvent?.level === "error" ? lastSmtpEvent.created_at : null,
    },
    maintenance: {
      ...maintenanceBacklog,
      appLogRetentionDays: env.APP_LOG_RETENTION_DAYS,
      adminAuditRetentionDays: env.ADMIN_AUDIT_RETENTION_DAYS,
      warrantyReminderDays: env.WARRANTY_REMINDER_DAYS,
      notificationEmailBatchLimit: env.NOTIFICATION_EMAIL_BATCH_LIMIT,
    },
  };
}

export async function retryAdminEmailOutbox(id: number): Promise<boolean | null> {
  return sendPendingOutboxEmail(id);
}

export async function listAdminAppLogs(filters: AdminAppLogFilters): Promise<PaginatedResult<AdminAppLogEntry>> {
  const whereParts: string[] = [];
  const params: unknown[] = [];
  const term = filters.search?.trim();

  if (term) {
    params.push(`%${term}%`);
    whereParts.push(`(message ILIKE $${params.length} OR source ILIKE $${params.length})`);
  }
  if (filters.level) {
    params.push(filters.level);
    whereParts.push(`level = $${params.length}`);
  }
  if (filters.source?.trim()) {
    params.push(filters.source.trim());
    whereParts.push(`source = $${params.length}`);
  }
  if (filters.from) {
    params.push(filters.from);
    whereParts.push(`created_at >= $${params.length}::date`);
  }
  if (filters.to) {
    params.push(filters.to);
    whereParts.push(`created_at < ($${params.length}::date + INTERVAL '1 day')`);
  }

  const where = whereParts.length ? `WHERE ${whereParts.join(" AND ")}` : "";
  const countRows = await query<{ count: string }>(
    `SELECT COUNT(*)::int AS count FROM app_event_log ${where}`,
    params
  );
  const total = toNumber(countRows[0]?.count);
  const offset = (filters.page - 1) * filters.pageSize;
  const pageParams = [...params, filters.pageSize, offset];

  const rows = await query<AdminAppLogEntry>(`
    SELECT id, level, source, message, details, created_at
    FROM app_event_log
    ${where}
    ORDER BY created_at DESC
    LIMIT $${pageParams.length - 1}
    OFFSET $${pageParams.length}
  `, pageParams);

  return paginate(rows, total, filters.page, filters.pageSize);
}

export async function purgeAdminAppLogs(before: string): Promise<number> {
  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
  `, [before]);
  return toNumber(rows[0]?.count);
}

export async function listAdminEmailOutbox(filters: AdminEmailOutboxFilters): Promise<PaginatedResult<AdminEmailOutboxEntry>> {
  const whereParts: string[] = [];
  const params: unknown[] = [];
  const term = filters.search?.trim();

  if (term) {
    params.push(`%${term}%`);
    whereParts.push(`(recipient ILIKE $${params.length} OR subject ILIKE $${params.length})`);
  }
  if (filters.status) {
    params.push(filters.status);
    whereParts.push(`status = $${params.length}`);
  }

  const where = whereParts.length ? `WHERE ${whereParts.join(" AND ")}` : "";
  const countRows = await query<{ count: string }>(
    `SELECT COUNT(*)::int AS count FROM email_outbox ${where}`,
    params
  );
  const total = toNumber(countRows[0]?.count);
  const offset = (filters.page - 1) * filters.pageSize;
  const pageParams = [...params, filters.pageSize, offset];

  const rows = await query<AdminEmailOutboxEntry>(`
    SELECT id, user_id, recipient, subject, body, status, created_at, sent_at
    FROM email_outbox
    ${where}
    ORDER BY created_at DESC
    LIMIT $${pageParams.length - 1}
    OFFSET $${pageParams.length}
  `, pageParams);

  return paginate(rows, total, filters.page, filters.pageSize);
}