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

export interface ProductRow {
  id: number;
  user_id: number;
  name: string;
  brand: string;
  model: string;
  category: string;
  purchase_date: string;
  purchase_price: string | null;
  vendor: string;
  serial_number: string;
  receipt_image: string | null;
  legal_warranty_months: number;
  warranty_end_date: string;
  has_extended_warranty: boolean;
  extended_warranty_end_date: string | null;
  extended_warranty_provider: string;
  extended_warranty_notes: string;
  has_insurance: boolean;
  insurance_end_date: string | null;
  insurance_provider: string;
  insurance_policy_number: string;
  notes: string;
  is_second_hand: boolean;
  created_at: string;
  updated_at: string;
  // computed
  days_remaining: number;
  effective_end_date: string;
  effective_type: string;
  status: "active" | "warning" | "critical" | "expired";
  progress: number;
}

export interface WarrantyReminderCandidate {
  product_id: number;
  user_id: number;
  user_name: string;
  user_email: string;
  warranty_email_enabled: boolean;
  warranty_push_enabled: boolean;
  product_name: string;
  brand: string;
  model: string;
  category: string;
  warranty_end_date: string;
  effective_end_date: string;
}

export const SELECT_WITH_COMPUTED = `
  SELECT
    p.*,
    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
    ) AS effective_end_date,
    CASE
      WHEN p.has_insurance AND p.insurance_end_date >= GREATEST(
        p.warranty_end_date,
        COALESCE(CASE WHEN p.has_extended_warranty THEN p.extended_warranty_end_date END, p.warranty_end_date)
      ) THEN 'insurance'
      WHEN p.has_extended_warranty AND p.extended_warranty_end_date >= p.warranty_end_date THEN 'extended'
      ELSE 'legal'
    END AS effective_type,
    (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,
    LEAST(100, GREATEST(0,
      ROUND(
        (CURRENT_DATE - p.purchase_date)::numeric /
        NULLIF((
          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
          ) - p.purchase_date
        )::numeric, 0) * 100
      )
    ))::int AS progress,
    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
  FROM products p
`;

export async function listProducts(userId: number): Promise<ProductRow[]> {
  return query<ProductRow>(
    `SELECT *
     FROM (
       ${SELECT_WITH_COMPUTED}
       WHERE p.user_id = $1
     ) products
     ORDER BY
      CASE products.status
        WHEN 'expired' THEN 1
        ELSE 0
      END ASC,
      products.effective_end_date ASC,
      products.created_at DESC`,
    [userId]
  );
}

export async function getProduct(userId: number, id: number): Promise<ProductRow | null> {
  const rows = await query<ProductRow>(
    `${SELECT_WITH_COMPUTED} WHERE p.user_id = $1 AND p.id = $2`,
    [userId, id]
  );
  return rows[0] ?? null;
}

export async function getDashboardStats(userId: number) {
  const rows = await query<{
    total: string; active: string; warning: string; critical: string; expired: string;
  }>(`
    SELECT
      COUNT(*) AS total,
      COUNT(*) FILTER (WHERE status = 'active')   AS active,
      COUNT(*) FILTER (WHERE status = 'warning')  AS warning,
      COUNT(*) FILTER (WHERE status = 'critical') AS critical,
      COUNT(*) FILTER (WHERE status = 'expired')  AS expired
    FROM (${SELECT_WITH_COMPUTED} WHERE p.user_id = $1) sub
  `, [userId]);
  const s = rows[0] ?? { total: "0", active: "0", warning: "0", critical: "0", expired: "0" };

  const expiringSoon = await query<ProductRow>(`
    ${SELECT_WITH_COMPUTED}
    WHERE p.user_id = $1
      AND 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
    ) BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '90 days'
    ORDER BY effective_end_date ASC
    LIMIT 10
  `, [userId]);

  const recentlyAdded = await query<ProductRow>(
    `${SELECT_WITH_COMPUTED} WHERE p.user_id = $1 ORDER BY p.created_at DESC LIMIT 6`,
    [userId]
  );

  return {
    total:    parseInt(s.total),
    active:   parseInt(s.active),
    warning:  parseInt(s.warning),
    critical: parseInt(s.critical),
    expired:  parseInt(s.expired),
    expiringSoon,
    recentlyAdded,
  };
}

export interface CreateProductInput {
  userId: number;
  name: string;
  brand: string;
  model: string;
  category: string;
  purchaseDate: string;
  purchasePrice: number | null;
  vendor: string;
  serialNumber: string;
  receiptImage?: string | null;
  legalWarrantyMonths: number;
  warrantyEndDate: string;
  hasExtendedWarranty: boolean;
  extendedWarrantyEndDate?: string | null;
  extendedWarrantyProvider: string;
  extendedWarrantyNotes: string;
  hasInsurance: boolean;
  insuranceEndDate?: string | null;
  insuranceProvider: string;
  insurancePolicyNumber: string;
  notes: string;
  isSecondHand: boolean;
  purchaseDocumentId?: number | null;
  purchaseDocumentLineId?: number | null;
}

export async function createProduct(input: CreateProductInput): Promise<ProductRow> {
  const rows = await query<ProductRow>(
    `INSERT INTO products (
      user_id, name, brand, model, category, purchase_date, purchase_price, vendor, serial_number,
      receipt_image, legal_warranty_months, warranty_end_date,
      has_extended_warranty, extended_warranty_end_date, extended_warranty_provider, extended_warranty_notes,
      has_insurance, insurance_end_date, insurance_provider, insurance_policy_number,
      notes, is_second_hand
    ) VALUES (
      $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22
    ) RETURNING *`,
    [
      input.userId,
      input.name, input.brand, input.model, input.category,
      input.purchaseDate, input.purchasePrice, input.vendor, input.serialNumber,
      input.receiptImage ?? null, input.legalWarrantyMonths, input.warrantyEndDate,
      input.hasExtendedWarranty, input.extendedWarrantyEndDate ?? null,
      input.extendedWarrantyProvider, input.extendedWarrantyNotes,
      input.hasInsurance, input.insuranceEndDate ?? null,
      input.insuranceProvider, input.insurancePolicyNumber,
      input.notes, input.isSecondHand,
    ]
  );
  const product = rows[0]!;
  await linkProductToPurchaseDocumentLine(input, product).catch((err) => {
    console.error("[products] link purchase line error:", err);
  });
  return product;
}

async function linkProductToPurchaseDocumentLine(input: CreateProductInput, product: ProductRow): Promise<void> {
  if (!input.purchaseDocumentId) return;

  const documentRows = await query<{
    id: number;
    user_id: number;
    file_url: string;
    file_name: string;
    document_number: string;
  }>(
    `SELECT id, user_id, file_url, file_name, document_number
     FROM purchase_documents
     WHERE user_id = $1 AND id = $2
     LIMIT 1`,
    [input.userId, input.purchaseDocumentId]
  );
  const document = documentRows[0];
  if (!document) return;

  if (!input.purchaseDocumentLineId) {
    const nextLineRows = await query<{ next_line_number: number }>(
      `SELECT COALESCE(MAX(line_number), 0) + 1 AS next_line_number
       FROM purchase_document_lines
       WHERE user_id = $1 AND document_id = $2`,
      [input.userId, input.purchaseDocumentId]
    );
    const nextLineNumber = nextLineRows[0]?.next_line_number ?? 1;

    await query(
      `INSERT INTO purchase_document_lines (
        document_id, user_id, line_number, raw_text, name_raw, brand_raw, model_raw,
        serial_number, imei, sku, category, quantity, unit_price, total_price,
        linked_product_id, line_status, confidence, metadata
      ) VALUES (
        $1,$2,$3,$4,$5,$6,$7,$8,'','',$9,1,$10,$10,$11,'converted_to_product',1,$12
      )`,
      [
        input.purchaseDocumentId,
        input.userId,
        nextLineNumber,
        [input.name, input.brand, input.model].filter(Boolean).join(" ") || product.name,
        input.name,
        input.brand,
        input.model,
        input.serialNumber,
        input.category,
        input.purchasePrice == null ? null : String(input.purchasePrice),
        product.id,
        JSON.stringify({
          manuallyAddedWithoutLineId: true,
          productLinkedFromManualForm: true,
          sourceDocumentFileName: document.file_name,
          sourceDocumentNumber: document.document_number,
        }),
      ]
    );
    return;
  }

  const sourceRows = await query<{
    id: number;
    line_number: number;
    linked_product_id: number | null;
    metadata: Record<string, unknown> | null;
  }>(
    `SELECT id, line_number, linked_product_id, metadata
     FROM purchase_document_lines
     WHERE user_id = $1 AND document_id = $2 AND id = $3
     LIMIT 1`,
    [input.userId, input.purchaseDocumentId, input.purchaseDocumentLineId]
  );
  const source = sourceRows[0];
  if (!source) return;

  if (!source.linked_product_id) {
    await query(
      `UPDATE purchase_document_lines
       SET
        linked_product_id = $4,
        line_status = 'converted_to_product',
        name_raw = COALESCE(NULLIF($5, ''), name_raw),
        brand_raw = COALESCE(NULLIF($6, ''), brand_raw),
        model_raw = COALESCE(NULLIF($7, ''), model_raw),
        serial_number = COALESCE(NULLIF($8, ''), serial_number),
        total_price = COALESCE($9, total_price),
        raw_text = COALESCE(NULLIF($10, ''), raw_text),
        metadata = COALESCE(metadata, '{}'::jsonb) || $11::jsonb,
        updated_at = NOW()
       WHERE user_id = $1 AND document_id = $2 AND id = $3`,
      [
        input.userId,
        input.purchaseDocumentId,
        input.purchaseDocumentLineId,
        product.id,
        input.name,
        input.brand,
        input.model,
        input.serialNumber,
        input.purchasePrice == null ? null : String(input.purchasePrice),
        [input.name, input.brand, input.model].filter(Boolean).join(" "),
        JSON.stringify({ productLinkedFromManualForm: true }),
      ]
    );
    return;
  }

  const nextLineRows = await query<{ next_line_number: number }>(
    `SELECT COALESCE(MAX(line_number), 0) + 1 AS next_line_number
     FROM purchase_document_lines
     WHERE user_id = $1 AND document_id = $2`,
    [input.userId, input.purchaseDocumentId]
  );
  const nextLineNumber = nextLineRows[0]?.next_line_number ?? source.line_number + 1;

  await query(
    `INSERT INTO purchase_document_lines (
      document_id, user_id, line_number, raw_text, name_raw, brand_raw, model_raw,
      serial_number, imei, sku, category, quantity, unit_price, total_price,
      linked_product_id, line_status, confidence, metadata
    ) VALUES (
      $1,$2,$3,$4,$5,$6,$7,$8,'','',$9,1,$10,$10,$11,'converted_to_product',1,$12
    )`,
    [
      input.purchaseDocumentId,
      input.userId,
      nextLineNumber,
      [input.name, input.brand, input.model].filter(Boolean).join(" "),
      input.name,
      input.brand,
      input.model,
      input.serialNumber,
      input.category,
      input.purchasePrice == null ? null : String(input.purchasePrice),
      product.id,
      JSON.stringify({
        manuallyAddedFromLineId: input.purchaseDocumentLineId,
        productLinkedFromManualForm: true,
      }),
    ]
  );
}

export async function updateProduct(id: number, input: Partial<CreateProductInput>): Promise<ProductRow | null> {
  const fields = Object.entries(input)
    .filter(([, v]) => v !== undefined)
    .map(([k]) => k);

  const userId = input.userId;
  if (!userId) throw new Error("userId is required");

  const colMap: Record<string, string> = {
    name: "name", brand: "brand", model: "model", category: "category",
    purchaseDate: "purchase_date", purchasePrice: "purchase_price",
    vendor: "vendor", serialNumber: "serial_number", receiptImage: "receipt_image",
    legalWarrantyMonths: "legal_warranty_months", warrantyEndDate: "warranty_end_date",
    hasExtendedWarranty: "has_extended_warranty",
    extendedWarrantyEndDate: "extended_warranty_end_date",
    extendedWarrantyProvider: "extended_warranty_provider",
    extendedWarrantyNotes: "extended_warranty_notes",
    hasInsurance: "has_insurance", insuranceEndDate: "insurance_end_date",
    insuranceProvider: "insurance_provider", insurancePolicyNumber: "insurance_policy_number",
    notes: "notes", isSecondHand: "is_second_hand",
  };

  const nonProductFields = new Set(["userId", "purchaseDocumentId", "purchaseDocumentLineId"]);
  const editableFields = fields.filter((f) => !nonProductFields.has(f) && Object.prototype.hasOwnProperty.call(colMap, f));
  if (editableFields.length !== fields.filter((f) => !nonProductFields.has(f)).length) {
    throw new Error("Invalid product fields");
  }
  if (editableFields.length === 0) return getProduct(userId, id);

  const setClauses = editableFields.map((f, i) => `${colMap[f]} = $${i + 3}`).join(", ");
  const values = editableFields.map((f) => (input as Record<string, unknown>)[f]);

  const rows = await query<ProductRow>(
    `UPDATE products SET ${setClauses} WHERE id = $1 AND user_id = $2 RETURNING *`,
    [id, userId, ...values]
  );
  return rows[0] ?? null;
}

export async function deleteProduct(userId: number, id: number): Promise<boolean> {
  const fileRows = await query<{ receipt_image: string | null }>(
    "SELECT receipt_image FROM products WHERE id = $1 AND user_id = $2 LIMIT 1",
    [id, userId]
  );
  const rows = await query<{ id: number }>(
    "DELETE FROM products WHERE id = $1 AND user_id = $2 RETURNING id",
    [id, userId]
  );
  const receiptImage = fileRows[0]?.receipt_image;
  if (rows.length > 0 && receiptImage) {
    const filename = path.basename(receiptImage);
    await fs.unlink(path.join(receiptsStorageDir, filename)).catch(() => {});
  }
  return rows.length > 0;
}

export async function getNotifications(userId: number): Promise<ProductRow[]> {
  return query<ProductRow>(`
    ${SELECT_WITH_COMPUTED}
    WHERE p.user_id = $1
      AND 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'
    ORDER BY effective_end_date ASC
  `, [userId]);
}

export async function listWarrantyReminderCandidates(daysAhead: number, limit: number): Promise<WarrantyReminderCandidate[]> {
  return query<WarrantyReminderCandidate>(`
    SELECT
      p.id AS product_id,
      p.user_id,
      u.name AS user_name,
      u.email AS user_email,
      COALESCE(np.warranty_email_enabled, TRUE) AS warranty_email_enabled,
      COALESCE(np.warranty_push_enabled, FALSE) AS warranty_push_enabled,
      p.name AS product_name,
      p.brand,
      p.model,
      p.category,
      p.warranty_end_date,
      p.effective_end_date
    FROM (
      ${SELECT_WITH_COMPUTED}
    ) p
    INNER JOIN users u ON u.id = p.user_id
    LEFT JOIN notification_preferences np ON np.user_id = u.id
    WHERE p.status IN ('active', 'warning', 'critical')
      AND u.status = 'active'
      AND u.email_verified_at IS NOT NULL
      AND (
        COALESCE(np.warranty_email_enabled, TRUE) = TRUE
        OR COALESCE(np.warranty_push_enabled, FALSE) = TRUE
      )
      AND p.effective_end_date = CURRENT_DATE + $1::int
      AND NOT EXISTS (
        SELECT 1
        FROM warranty_notification_deliveries wd
        WHERE wd.user_id = p.user_id
          AND wd.product_id = p.id
          AND wd.reminder_days = $1
          AND wd.reminder_date = p.effective_end_date
      )
    ORDER BY p.effective_end_date ASC, u.email ASC, p.id ASC
    LIMIT $2
  `, [daysAhead, limit]);
}

export async function countWarrantyReminderCandidates(daysAhead: number): Promise<number> {
  const rows = await query<{ count: string }>(`
    SELECT COUNT(*)::int AS count
    FROM (
      ${SELECT_WITH_COMPUTED}
    ) p
    INNER JOIN users u ON u.id = p.user_id
    LEFT JOIN notification_preferences np ON np.user_id = u.id
    WHERE p.status IN ('active', 'warning', 'critical')
      AND u.status = 'active'
      AND u.email_verified_at IS NOT NULL
      AND (
        COALESCE(np.warranty_email_enabled, TRUE) = TRUE
        OR COALESCE(np.warranty_push_enabled, FALSE) = TRUE
      )
      AND p.effective_end_date = CURRENT_DATE + $1::int
      AND NOT EXISTS (
        SELECT 1
        FROM warranty_notification_deliveries wd
        WHERE wd.user_id = p.user_id
          AND wd.product_id = p.id
          AND wd.reminder_days = $1
          AND wd.reminder_date = p.effective_end_date
      )
  `, [daysAhead]);
  return Number(rows[0]?.count ?? 0);
}