import { query } from "../../db/pool.js";
import fs from "node:fs/promises";
import path from "node:path";
import { incidentAttachmentsStorageDir, productMediaStorageDir } from "../../lib/storage.js";

export type RepairIncidentStatus =
  | "open"
  | "sent"
  | "received"
  | "diagnosing"
  | "awaiting_parts"
  | "repaired"
  | "replaced"
  | "rejected"
  | "closed";

export interface ProductMediaRow {
  id: number;
  user_id: number;
  product_id: number;
  kind: string;
  file_url: string;
  file_name: string;
  mime_type: string;
  caption: string;
  sort_order: number;
  created_at: string;
}

export interface RepairIncidentRow {
  id: number;
  user_id: number;
  product_id: number;
  purchase_document_line_id: number | null;
  incident_number: string;
  title: string;
  incident_type: string;
  status: RepairIncidentStatus;
  detected_at: string;
  delivered_at: string | null;
  delivery_place: string;
  received_by: string;
  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;
  user_rating: number | null;
  user_rating_notes: string;
  closed_at: string | null;
  created_at: string;
  updated_at: string;
}

export interface RepairIncidentListItem extends RepairIncidentRow {
  product_name: string;
  product_brand: string;
  product_model: string;
  product_category: string;
  product_status: string;
  product_days_remaining: number;
  attachment_count: number;
  media_count: number;
}

export interface RepairIncidentStats {
  total: number;
  open: number;
  resolved: number;
  closed: number;
  avg_rating: number | null;
  top_brands: Array<{ label: string; count: number }>;
  top_models: Array<{ label: string; count: number }>;
  top_products: Array<{ label: string; count: number }>;
}

export interface IncidentAttachmentRow {
  id: number;
  user_id: number;
  incident_id: number;
  kind: string;
  file_url: string;
  file_name: string;
  mime_type: string;
  caption: string;
  sort_order: number;
  created_at: string;
}

function buildStatusOrderSql(alias = "i"): string {
  return `CASE ${alias}.status
    WHEN 'open' THEN 0
    WHEN 'sent' THEN 1
    WHEN 'received' THEN 2
    WHEN 'diagnosing' THEN 3
    WHEN 'awaiting_parts' THEN 4
    WHEN 'repaired' THEN 5
    WHEN 'replaced' THEN 6
    WHEN 'rejected' THEN 7
    WHEN 'closed' THEN 8
    ELSE 9
  END`;
}

export async function assertOwnedProduct(userId: number, productId: number): Promise<void> {
  const rows = await query<{ id: number }>(
    "SELECT id FROM products WHERE id = $1 AND user_id = $2 LIMIT 1",
    [productId, userId]
  );
  if (!rows[0]) {
    throw new Error("product_not_found");
  }
}

export async function listProductMedia(userId: number, productId: number): Promise<ProductMediaRow[]> {
  return query<ProductMediaRow>(
    `SELECT *
     FROM product_media
     WHERE user_id = $1 AND product_id = $2
     ORDER BY sort_order ASC, created_at ASC, id ASC`,
    [userId, productId]
  );
}

export async function addProductMedia(
  userId: number,
  productId: number,
  items: Array<{
    fileUrl: string;
    fileName: string;
    mimeType: string;
    kind: string;
    caption?: string;
    sortOrder?: number;
  }>
): Promise<ProductMediaRow[]> {
  if (items.length === 0) return [];

  const values: unknown[] = [];
  const placeholders = items.map((item, index) => {
    const offset = index * 8;
    values.push(
      userId,
      productId,
      item.kind,
      item.fileUrl,
      item.fileName,
      item.mimeType,
      item.caption ?? "",
      item.sortOrder ?? index,
    );
    return `($${offset + 1}, $${offset + 2}, $${offset + 3}, $${offset + 4}, $${offset + 5}, $${offset + 6}, $${offset + 7}, $${offset + 8})`;
  });

  return query<ProductMediaRow>(
    `INSERT INTO product_media (
      user_id, product_id, kind, file_url, file_name, mime_type, caption, sort_order
    ) VALUES ${placeholders.join(", ")}
    RETURNING *`,
    values
  );
}

export async function deleteProductMedia(userId: number, productId: number, mediaId: number): Promise<boolean> {
  const fileRows = await query<{ file_url: string }>(
    "SELECT file_url FROM product_media WHERE id = $1 AND product_id = $2 AND user_id = $3 LIMIT 1",
    [mediaId, productId, userId]
  );
  const rows = await query<{ id: number }>(
    "DELETE FROM product_media WHERE id = $1 AND product_id = $2 AND user_id = $3 RETURNING id",
    [mediaId, productId, userId]
  );
  const fileUrl = fileRows[0]?.file_url;
  if (rows.length > 0 && fileUrl) {
    const filename = path.basename(fileUrl);
    await fs.unlink(path.join(productMediaStorageDir, filename)).catch(() => {});
  }
  return rows.length > 0;
}

export async function listIncidents(userId: number): Promise<RepairIncidentListItem[]> {
  return query<RepairIncidentListItem>(`
    SELECT
      i.*,
      p.name AS product_name,
      p.brand AS product_brand,
      p.model AS product_model,
      p.category AS product_category,
      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 AS product_status,
      (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 AS product_days_remaining,
      COALESCE(att.attachment_count, 0)::int AS attachment_count,
      COALESCE(media.media_count, 0)::int AS media_count
    FROM repair_incidents i
    INNER 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
      WHERE user_id = $1
      GROUP BY incident_id
    ) att ON att.incident_id = i.id
    LEFT JOIN (
      SELECT product_id, COUNT(*) AS media_count
      FROM product_media
      WHERE user_id = $1
      GROUP BY product_id
    ) media ON media.product_id = i.product_id
    WHERE i.user_id = $1
    ORDER BY
      CASE
        WHEN i.status IN ('closed', 'rejected') THEN 1
        ELSE 0
      END ASC,
      product_days_remaining ASC,
      ${buildStatusOrderSql("i")} ASC,
      i.updated_at DESC,
      i.created_at DESC
  `, [userId]);
}

export async function getIncidentStats(userId: number): Promise<RepairIncidentStats> {
  const summaryRows = await query<{
    total: string;
    open: string;
    resolved: string;
    closed: string;
    avg_rating: string | null;
  }>(`
    SELECT
      COUNT(*)::int AS total,
      COUNT(*) FILTER (WHERE status IN ('open', 'sent', 'received', 'diagnosing', 'awaiting_parts'))::int AS open,
      COUNT(*) FILTER (WHERE status IN ('repaired', 'replaced'))::int AS resolved,
      COUNT(*) FILTER (WHERE status IN ('closed', 'rejected'))::int AS closed,
      ROUND(AVG(user_rating)::numeric, 2) AS avg_rating
    FROM repair_incidents
    WHERE user_id = $1
  `, [userId]);

  const topBrands = await query<{ label: string; count: string }>(`
    WITH incident_sources AS (
      SELECT
        COALESCE(
          NULLIF(TRIM(pl.brand_raw), ''),
          NULLIF(TRIM(p.brand), ''),
          'Sin marca'
        ) AS label
      FROM repair_incidents i
      LEFT JOIN purchase_document_lines pl ON pl.id = i.purchase_document_line_id AND pl.user_id = i.user_id
      LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
      WHERE i.user_id = $1
    )
    SELECT label, COUNT(*)::int AS count
    FROM incident_sources
    GROUP BY label
    ORDER BY count DESC, label ASC
    LIMIT 5
  `, [userId]);

  const topModels = await query<{ label: string; count: string }>(`
    WITH incident_sources AS (
      SELECT
        COALESCE(
          NULLIF(TRIM(pl.model_raw), ''),
          NULLIF(TRIM(p.model), ''),
          'Sin modelo'
        ) AS label
      FROM repair_incidents i
      LEFT JOIN purchase_document_lines pl ON pl.id = i.purchase_document_line_id AND pl.user_id = i.user_id
      LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
      WHERE i.user_id = $1
    )
    SELECT label, COUNT(*)::int AS count
    FROM incident_sources
    GROUP BY label
    ORDER BY count DESC, label ASC
    LIMIT 5
  `, [userId]);

  const topProducts = await query<{ label: string; count: string }>(`
    WITH incident_sources AS (
      SELECT
        COALESCE(
          NULLIF(
            TRIM(
              CONCAT_WS(
                ' · ',
                NULLIF(TRIM(p.name), ''),
                NULLIF(TRIM(p.brand), ''),
                NULLIF(TRIM(p.model), '')
              )
            ),
            ''
          ),
          'Producto sin nombre'
        ) AS label
      FROM repair_incidents i
      LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
      WHERE i.user_id = $1
    )
    SELECT label, COUNT(*)::int AS count
    FROM incident_sources
    GROUP BY label
    ORDER BY count DESC, label ASC
    LIMIT 5
  `, [userId]);

  const summary = summaryRows[0] ?? { total: "0", open: "0", resolved: "0", closed: "0", avg_rating: null };

  return {
    total: Number(summary.total),
    open: Number(summary.open),
    resolved: Number(summary.resolved),
    closed: Number(summary.closed),
    avg_rating: summary.avg_rating === null ? null : Number(summary.avg_rating),
    top_brands: topBrands.map((row) => ({ label: row.label, count: Number(row.count) })),
    top_models: topModels.map((row) => ({ label: row.label, count: Number(row.count) })),
    top_products: topProducts.map((row) => ({ label: row.label, count: Number(row.count) })),
  };
}

export async function getIncident(userId: number, id: number): Promise<{
  incident: RepairIncidentRow;
  product: {
    id: number;
    name: string;
    brand: string;
    model: string;
    category: string;
    status: string;
    days_remaining: number;
    effective_end_date: string;
  };
  attachments: IncidentAttachmentRow[];
  media: ProductMediaRow[];
} | null> {
  const incidentRows = await query<RepairIncidentRow>(
    "SELECT * FROM repair_incidents WHERE id = $1 AND user_id = $2 LIMIT 1",
    [id, userId]
  );
  const incident = incidentRows[0];
  if (!incident) return null;

  const productRows = await query<{
    id: number;
    name: string;
    brand: string;
    model: string;
    category: string;
    status: string;
    days_remaining: number;
    effective_end_date: string;
  }>(`
    SELECT
      p.id,
      p.name,
      p.brand,
      p.model,
      p.category,
      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 AS status,
      (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 AS days_remaining,
      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
      )::text AS effective_end_date
    FROM products p
    WHERE p.id = $1 AND p.user_id = $2
    LIMIT 1
  `, [incident.product_id, userId]);
  const product = productRows[0];
  if (!product) return null;

  const [attachments, media] = await Promise.all([
    query<IncidentAttachmentRow>(
      "SELECT * FROM incident_attachments WHERE incident_id = $1 AND user_id = $2 ORDER BY sort_order ASC, created_at ASC, id ASC",
      [id, userId]
    ),
    query<ProductMediaRow>(
      "SELECT * FROM product_media WHERE product_id = $1 AND user_id = $2 ORDER BY sort_order ASC, created_at ASC, id ASC",
      [incident.product_id, userId]
    ),
  ]);

  return { incident, product, attachments, media };
}

export interface CreateIncidentInput {
  userId: number;
  productId: number;
  purchaseDocumentLineId?: number | null;
  title: string;
  incidentType: string;
  detectedAt: string;
  deliveryPlace: string;
  receivedBy: string;
  description: string;
  symptom: string;
  accessories: string;
  satName: string;
  satReference: string;
  diagnosis: string;
  technicianNotes: string;
  estimatedCost: number | null;
  resolution: string;
  replacedWithNew: boolean;
  replacedWithRefurbished: boolean;
  userRating: number | null;
  userRatingNotes: string;
  sharePublicRating: boolean;
}

export async function createIncident(input: CreateIncidentInput): Promise<RepairIncidentRow> {
  const incidentNumber = `INC-${Date.now()}-${Math.random().toString(36).slice(2, 7).toUpperCase()}`;
  const rows = await query<RepairIncidentRow>(
    `INSERT INTO repair_incidents (
      user_id, product_id, purchase_document_line_id, incident_number, title, incident_type, status, detected_at,
      delivery_place, received_by, description, symptom, accessories, sat_name, sat_reference,
      diagnosis, technician_notes, estimated_cost, resolution, replaced_with_new,
      replaced_with_refurbished, user_rating, user_rating_notes, share_public_rating
    ) VALUES (
      $1,$2,$3,$4,$5,$6,'open',$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23
    ) RETURNING *`,
    [
      input.userId,
      input.productId,
      input.purchaseDocumentLineId ?? null,
      incidentNumber,
      input.title,
      input.incidentType,
      input.detectedAt,
      input.deliveryPlace,
      input.receivedBy,
      input.description,
      input.symptom,
      input.accessories,
      input.satName,
      input.satReference,
      input.diagnosis,
      input.technicianNotes,
      input.estimatedCost,
      input.resolution,
      input.replacedWithNew,
      input.replacedWithRefurbished,
      input.userRating,
      input.userRatingNotes,
      input.sharePublicRating,
    ]
  );
  return rows[0]!;
}

export interface UpdateIncidentInput {
  title?: string;
  incidentType?: string;
  status?: RepairIncidentStatus;
  detectedAt?: string;
  deliveredAt?: string | null;
  deliveryPlace?: string;
  receivedBy?: string;
  description?: string;
  symptom?: string;
  accessories?: string;
  satName?: string;
  satReference?: string;
  diagnosis?: string;
  technicianNotes?: string;
  estimatedCost?: number | null;
  resolution?: string;
  replacedWithNew?: boolean;
  replacedWithRefurbished?: boolean;
  userRating?: number | null;
  userRatingNotes?: string;
  sharePublicRating?: boolean;
  closedAt?: string | null;
}

export async function updateIncident(userId: number, id: number, input: UpdateIncidentInput): Promise<RepairIncidentRow | null> {
  const normalizedInput = {
    ...input,
    closedAt:
      input.closedAt === undefined && input.status === "closed"
        ? new Date().toISOString()
        : input.closedAt,
  };
  const fields = Object.entries(normalizedInput).filter(([, value]) => value !== undefined);
  if (fields.length === 0) {
    const current = await getIncident(userId, id);
    return current?.incident ?? null;
  }

  const colMap: Record<string, string> = {
    title: "title",
    incidentType: "incident_type",
    status: "status",
    detectedAt: "detected_at",
    deliveredAt: "delivered_at",
    deliveryPlace: "delivery_place",
    receivedBy: "received_by",
    description: "description",
    symptom: "symptom",
    accessories: "accessories",
    satName: "sat_name",
    satReference: "sat_reference",
    diagnosis: "diagnosis",
    technicianNotes: "technician_notes",
    estimatedCost: "estimated_cost",
    resolution: "resolution",
    replacedWithNew: "replaced_with_new",
    replacedWithRefurbished: "replaced_with_refurbished",
    userRating: "user_rating",
    userRatingNotes: "user_rating_notes",
    sharePublicRating: "share_public_rating",
    closedAt: "closed_at",
  };

  const clauses = fields.map(([key], index) => `${colMap[key]} = $${index + 3}`);
  const values = fields.map(([, value]) => value);
  const rows = await query<RepairIncidentRow>(
    `UPDATE repair_incidents SET ${clauses.join(", ")} WHERE id = $1 AND user_id = $2 RETURNING *`,
    [id, userId, ...values]
  );
  return rows[0] ?? null;
}

export async function addIncidentAttachments(
  userId: number,
  incidentId: number,
  items: Array<{
    fileUrl: string;
    fileName: string;
    mimeType: string;
    kind: string;
    caption?: string;
    sortOrder?: number;
  }>
): Promise<IncidentAttachmentRow[]> {
  if (items.length === 0) return [];

  const values: unknown[] = [];
  const placeholders = items.map((item, index) => {
    const offset = index * 8;
    values.push(
      userId,
      incidentId,
      item.kind,
      item.fileUrl,
      item.fileName,
      item.mimeType,
      item.caption ?? "",
      item.sortOrder ?? index,
    );
    return `($${offset + 1}, $${offset + 2}, $${offset + 3}, $${offset + 4}, $${offset + 5}, $${offset + 6}, $${offset + 7}, $${offset + 8})`;
  });

  return query<IncidentAttachmentRow>(
    `INSERT INTO incident_attachments (
      user_id, incident_id, kind, file_url, file_name, mime_type, caption, sort_order
    ) VALUES ${placeholders.join(", ")}
    RETURNING *`,
    values
  );
}

export async function deleteIncidentAttachment(userId: number, incidentId: number, attachmentId: number): Promise<boolean> {
  const fileRows = await query<{ file_url: string }>(
    "SELECT file_url FROM incident_attachments WHERE id = $1 AND incident_id = $2 AND user_id = $3 LIMIT 1",
    [attachmentId, incidentId, userId]
  );
  const rows = await query<{ id: number }>(
    "DELETE FROM incident_attachments WHERE id = $1 AND incident_id = $2 AND user_id = $3 RETURNING id",
    [attachmentId, incidentId, userId]
  );
  const fileUrl = fileRows[0]?.file_url;
  if (rows.length > 0 && fileUrl) {
    const filename = path.basename(fileUrl);
    await fs.unlink(path.join(incidentAttachmentsStorageDir, filename)).catch(() => {});
  }
  return rows.length > 0;
}