const { pool } = require("../../config/db");

// ─── Helper: auto-generate voucher number ───────────────────────────────────
// Format: JV/2026/0001  |  PV/2026/0001  |  RV/2026/0001  etc.
const generateVoucherNumber = async (type) => {
  try {
    const year = new Date().getFullYear();

    // prefix map
    const prefixMap = {
      Journal : "JV",
      Payment : "PV",
      Receipt : "RV",
      Contra  : "CV",
    };
    const prefix = prefixMap[type] || "JV";

    const result = await pool.query(
      `SELECT voucher_number FROM journal_vouchers
       WHERE voucher_number LIKE $1
       ORDER BY voucher_number DESC LIMIT 1`,
      [`${prefix}/${year}/%`]
    );

    let nextNumber = 1;
    if (result.rows.length > 0) {
      const lastCode = result.rows[0].voucher_number;
      const match = lastCode.match(/\/(\d+)$/);
      if (match) nextNumber = parseInt(match[1]) + 1;
    }

    return `${prefix}/${year}/${nextNumber.toString().padStart(4, "0")}`;
  } catch (error) {
    console.error("Error generating voucher number:", error);
    return `JV/${Date.now()}`;
  }
};

// ─── CREATE JOURNAL VOUCHER ──────────────────────────────────────────────────
const createJournalVoucher = async (req, res) => {
  try {
    console.log("=== CREATE JOURNAL VOUCHER ===");
    console.log("req.body:", req.body);

    const orgId      = req.user?.orgId;
    const created_by = req.user?.userId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    if (!req.body || Object.keys(req.body).length === 0) {
      return res.status(400).json({ success: false, message: "No data received." });
    }

    const {
      voucher_type,
      voucher_date,
      debit_account,
      credit_account,
      amount,
      narration,
      reference_number,
      status,
    } = req.body;

    // Required field validation
    if (!voucher_type || !voucher_date || !debit_account || !credit_account || !amount || !narration) {
      return res.status(400).json({
        success: false,
        message: "Missing required fields: voucher_type, voucher_date, debit_account, credit_account, amount, narration",
      });
    }

    const parsedAmount = parseFloat(amount);
    if (isNaN(parsedAmount) || parsedAmount <= 0) {
      return res.status(400).json({ success: false, message: "Amount must be a positive number" });
    }

    const validTypes = ["Journal", "Payment", "Receipt", "Contra"];
    if (!validTypes.includes(voucher_type)) {
      return res.status(400).json({
        success: false,
        message: `Invalid voucher_type. Must be one of: ${validTypes.join(", ")}`,
      });
    }

    const voucher_number = await generateVoucherNumber(voucher_type);

    const result = await pool.query(
      `INSERT INTO journal_vouchers (
        voucher_number, voucher_type, voucher_date,
        debit_account, credit_account, amount,
        narration, reference_number, status,
        org_id, created_by
      ) VALUES (
        $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11
      ) RETURNING *`,
      [
        voucher_number,
        voucher_type,
        voucher_date,
        debit_account,
        credit_account,
        parsedAmount,
        narration,
        reference_number || null,
        status           || "Pending",
        orgId,
        created_by       || null,
      ]
    );

    return res.status(201).json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("Error in createJournalVoucher:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

// ─── GET ALL (no pagination) — for dropdowns / exports ──────────────────────
const getAllJournalVouchersWithoutPagination = async (req, res) => {
  try {
    const orgId = req.user?.orgId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const result = await pool.query(
      `SELECT id, voucher_number, voucher_type, voucher_date,
              debit_account, credit_account, amount,
              narration, reference_number, status,
              created_at, updated_at, created_by, updated_by
       FROM journal_vouchers
       WHERE is_deleted = false AND org_id = $1
       ORDER BY voucher_date DESC`,
      [orgId]
    );

    return res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("Error in getAllJournalVouchersWithoutPagination:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

// ─── GET ALL (paginated + search) ───────────────────────────────────────────
const getAllJournalVouchers = async (req, res) => {
  try {
    const orgId = req.user?.orgId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const { search = "", page = 1, limit = 10 } = req.query;
    const offset     = (parseInt(page) - 1) * parseInt(limit);
    const searchTerm = `%${search}%`;

    const countResult = await pool.query(
      `SELECT COUNT(*) AS total
       FROM journal_vouchers
       WHERE is_deleted = false AND org_id = $1
         AND (
           voucher_number   ILIKE $2 OR
           voucher_type     ILIKE $2 OR
           narration        ILIKE $2 OR
           debit_account    ILIKE $2 OR
           credit_account   ILIKE $2 OR
           reference_number ILIKE $2
         )`,
      [orgId, searchTerm]
    );

    const total = parseInt(countResult.rows[0].total);

    const result = await pool.query(
      `SELECT id, voucher_number, voucher_type, voucher_date,
              debit_account, credit_account, amount,
              narration, reference_number, status,
              created_at, updated_at, created_by, updated_by
       FROM journal_vouchers
       WHERE is_deleted = false AND org_id = $1
         AND (
           voucher_number   ILIKE $2 OR
           voucher_type     ILIKE $2 OR
           narration        ILIKE $2 OR
           debit_account    ILIKE $2 OR
           credit_account   ILIKE $2 OR
           reference_number ILIKE $2
         )
       ORDER BY created_at DESC
       LIMIT $3 OFFSET $4`,
      [orgId, searchTerm, parseInt(limit), offset]
    );

    return res.json({
      success: true,
      data: result.rows,
      pagination: {
        currentPage : parseInt(page),
        totalPages  : Math.ceil(total / parseInt(limit)),
        totalEntries: total,
        itemsPerPage: parseInt(limit),
      },
    });
  } catch (err) {
    console.error("Error in getAllJournalVouchers:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

// ─── GET BY ID ───────────────────────────────────────────────────────────────
const getJournalVoucherById = async (req, res) => {
  try {
    const { id }  = req.params;
    const orgId   = req.user?.orgId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const result = await pool.query(
      `SELECT id, voucher_number, voucher_type, voucher_date,
              debit_account, credit_account, amount,
              narration, reference_number, status,
              created_at, updated_at, created_by, updated_by
       FROM journal_vouchers
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [parseInt(id), orgId]
    );

    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Journal voucher not found" });
    }

    return res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("Error in getJournalVoucherById:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

// ─── UPDATE ──────────────────────────────────────────────────────────────────
const updateJournalVoucher = async (req, res) => {
  try {
    const { id }      = req.params;
    const orgId       = req.user?.orgId;
    const updated_by  = req.user?.userId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const {
      voucher_type,
      voucher_date,
      debit_account,
      credit_account,
      amount,
      narration,
      reference_number,
      status,
    } = req.body;

    // Required field validation
    if (!voucher_type || !voucher_date || !debit_account || !credit_account || !amount || !narration) {
      return res.status(400).json({
        success: false,
        message: "Missing required fields: voucher_type, voucher_date, debit_account, credit_account, amount, narration",
      });
    }

    const parsedAmount = parseFloat(amount);
    if (isNaN(parsedAmount) || parsedAmount <= 0) {
      return res.status(400).json({ success: false, message: "Amount must be a positive number" });
    }

    // Check voucher exists and is not Posted (Posted = locked)
    const existing = await pool.query(
      `SELECT id, status FROM journal_vouchers
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [parseInt(id), orgId]
    );

    if (existing.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Journal voucher not found" });
    }

    if (existing.rows[0].status === "Posted") {
      return res.status(400).json({
        success: false,
        message: "Cannot edit a Posted voucher. Only Pending or Approved vouchers can be edited.",
      });
    }

    const result = await pool.query(
      `UPDATE journal_vouchers SET
        voucher_type     = $1,
        voucher_date     = $2,
        debit_account    = $3,
        credit_account   = $4,
        amount           = $5,
        narration        = $6,
        reference_number = $7,
        status           = $8,
        updated_by       = $9,
        updated_at       = CURRENT_TIMESTAMP
      WHERE id = $10 AND org_id = $11
      RETURNING *`,
      [
        voucher_type,
        voucher_date,
        debit_account,
        credit_account,
        parsedAmount,
        narration,
        reference_number || null,
        status           || existing.rows[0].status,
        updated_by       || null,
        parseInt(id),
        orgId,
      ]
    );

    return res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("Error in updateJournalVoucher:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

// ─── UPDATE STATUS ONLY ───────────────────────────────────────────────────────
const updateJournalVoucherStatus = async (req, res) => {
  try {
    const { id }     = req.params;
    const { status } = req.body;
    const orgId      = req.user?.orgId;
    const updated_by = req.user?.userId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const validStatuses = ["Pending", "Approved", "Posted"];
    if (!status || !validStatuses.includes(status)) {
      return res.status(400).json({
        success: false,
        message: `Invalid status. Must be one of: ${validStatuses.join(", ")}`,
      });
    }

    const result = await pool.query(
      `UPDATE journal_vouchers
       SET status     = $1,
           updated_by = $2,
           updated_at = CURRENT_TIMESTAMP
       WHERE id = $3 AND org_id = $4 AND is_deleted = false
       RETURNING id, voucher_number, voucher_type, status`,
      [status, updated_by || null, parseInt(id), orgId]
    );

    if (result.rowCount === 0) {
      return res.status(404).json({ success: false, message: "Journal voucher not found" });
    }

    return res.json({ success: true, message: "Status updated successfully", data: result.rows[0] });
  } catch (err) {
    console.error("Error in updateJournalVoucherStatus:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

// ─── SOFT DELETE ─────────────────────────────────────────────────────────────
const deleteJournalVoucher = async (req, res) => {
  try {
    const { id }     = req.params;
    const orgId      = req.user?.orgId;
    const updated_by = req.user?.userId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    // Block deletion of Posted vouchers
    const existing = await pool.query(
      `SELECT status FROM journal_vouchers
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [parseInt(id), orgId]
    );

    if (existing.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Journal voucher not found or already deleted" });
    }

    if (existing.rows[0].status === "Posted") {
      return res.status(400).json({
        success: false,
        message: "Cannot delete a Posted voucher.",
      });
    }

    const result = await pool.query(
      `UPDATE journal_vouchers
       SET is_deleted  = true,
           updated_by  = $1,
           updated_at  = CURRENT_TIMESTAMP
       WHERE id = $2 AND org_id = $3 AND is_deleted = false
       RETURNING id, voucher_number, voucher_type`,
      [updated_by || null, parseInt(id), orgId]
    );

    return res.json({ success: true, message: "Journal voucher deleted successfully", data: result.rows[0] });
  } catch (err) {
    console.error("Error in deleteJournalVoucher:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

// ─── HARD DELETE ─────────────────────────────────────────────────────────────
const hardDeleteJournalVoucher = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId  = req.user?.orgId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const result = await pool.query(
      `DELETE FROM journal_vouchers
       WHERE id = $1 AND org_id = $2
       RETURNING id, voucher_number, voucher_type`,
      [parseInt(id), orgId]
    );

    if (result.rowCount === 0) {
      return res.status(404).json({ success: false, message: "Journal voucher not found" });
    }

    return res.json({ success: true, message: "Journal voucher permanently deleted", data: result.rows[0] });
  } catch (err) {
    console.error("Error in hardDeleteJournalVoucher:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

module.exports = {
  createJournalVoucher,
  getAllJournalVouchers,
  getAllJournalVouchersWithoutPagination,
  getJournalVoucherById,
  updateJournalVoucher,
  updateJournalVoucherStatus,
  deleteJournalVoucher,
  hardDeleteJournalVoucher,
};