// controllers/procurement/purchaseTermsController.js
const { pool } = require("../../config/db"); // Adjust path as needed

// ==================== DETAILED CONTENT ====================

// Default document types (system)
const DEFAULT_DOC_TYPES = [
  { doc_type: "invoice", doc_name: "Invoice", icon: "🧾", is_system: true },
  { doc_type: "quotation", doc_name: "Quotation", icon: "📋", is_system: true },
  { doc_type: "grn", doc_name: "GRN", icon: "📦", is_system: true },
  { doc_type: "po", doc_name: "Purchase Order", icon: "🛒", is_system: true },
  { doc_type: "dn", doc_name: "Delivery Note", icon: "🚚", is_system: true },
  { doc_type: "so", doc_name: "Sales Order", icon: "📊", is_system: true },
];

// Initialize default document types for an organization
const initializeDefaultDocTypes = async (orgId, userId) => {
  for (const doc of DEFAULT_DOC_TYPES) {
    await pool.query(
      `INSERT INTO purchase_document_types (doc_type, doc_name, icon, is_system, org_id, created_by)
       VALUES ($1, $2, $3, $4, $5, $6)
       ON CONFLICT (doc_type, org_id) DO NOTHING`,
      [doc.doc_type, doc.doc_name, doc.icon, doc.is_system, orgId, userId]
    );

    // Initialize empty detailed content for each doc type
    await pool.query(
      `INSERT INTO purchase_terms_detailed (doc_type, doc_name, content, org_id, created_by)
       VALUES ($1, $2, $3, $4, $5)
       ON CONFLICT (doc_type, org_id) DO NOTHING`,
      [doc.doc_type, doc.doc_name, `<h2>${doc.doc_name} Terms & Conditions</h2><p>Edit this content...</p>`, orgId, userId]
    );
  }
};

// Get detailed content by doc_type
const getDetailedContent = async (req, res) => {
  try {
    const { doc_type } = req.params;
    const orgId = req.user?.orgId;

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

    let result = await pool.query(
      `SELECT id, doc_type, doc_name, content, created_at, updated_at
       FROM purchase_terms_detailed
       WHERE doc_type = $1 AND org_id = $2 AND is_deleted = false`,
      [doc_type, orgId]
    );

    // If not found, create default
    if (result.rows.length === 0) {
      // Check if doc type exists
      const docTypeResult = await pool.query(
        `SELECT doc_name FROM purchase_document_types
         WHERE doc_type = $1 AND org_id = $2 AND is_deleted = false`,
        [doc_type, orgId]
      );

      let doc_name = doc_type;
      if (docTypeResult.rows.length > 0) {
        doc_name = docTypeResult.rows[0].doc_name;
      }

      const insertResult = await pool.query(
        `INSERT INTO purchase_terms_detailed (doc_type, doc_name, content, org_id, created_by)
         VALUES ($1, $2, $3, $4, $5)
         RETURNING id, doc_type, doc_name, content`,
        [doc_type, doc_name, `<h2>${doc_name} Terms & Conditions</h2><p>Edit this content...</p>`, orgId, req.user?.userId]
      );
      result = insertResult;
    }

    res.json({ success: true, data: result.rows[0] });

  } catch (err) {
    console.error("Error in getDetailedContent:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// Get all detailed contents for an org
const getAllDetailedContents = 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, doc_type, doc_name, content, created_at, updated_at
       FROM purchase_terms_detailed
       WHERE org_id = $1 AND is_deleted = false
       ORDER BY doc_name`,
      [orgId]
    );

    res.json({ success: true, data: result.rows });

  } catch (err) {
    console.error("Error in getAllDetailedContents:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// Save detailed content
const saveDetailedContent = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    const userId = req.user?.userId;

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

    const { doc_type, content, doc_name } = req.body;

    if (!doc_type) {
      return res.status(400).json({ success: false, message: "doc_type is required" });
    }

    // Check if exists
    const check = await pool.query(
      `SELECT id FROM purchase_terms_detailed
       WHERE doc_type = $1 AND org_id = $2 AND is_deleted = false`,
      [doc_type, orgId]
    );

    let result;
    if (check.rows.length > 0) {
      // Update existing
      result = await pool.query(
        `UPDATE purchase_terms_detailed
         SET content = $1,
             doc_name = COALESCE($2, doc_name),
             updated_by = $3,
             updated_at = CURRENT_TIMESTAMP
         WHERE doc_type = $4 AND org_id = $5
         RETURNING id, doc_type, doc_name, content, updated_at`,
        [content, doc_name, userId || null, doc_type, orgId]
      );
    } else {
      // Insert new
      const finalDocName = doc_name || doc_type;
      result = await pool.query(
        `INSERT INTO purchase_terms_detailed (doc_type, doc_name, content, org_id, created_by)
         VALUES ($1, $2, $3, $4, $5)
         RETURNING id, doc_type, doc_name, content`,
        [doc_type, finalDocName, content, orgId, userId || null]
      );
    }

    res.json({ success: true, data: result.rows[0], message: "Content saved successfully" });

  } catch (err) {
    console.error("Error in saveDetailedContent:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// Delete detailed content
const deleteDetailedContent = async (req, res) => {
  try {
    const { doc_type } = 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(
      `UPDATE purchase_terms_detailed
       SET is_deleted = true,
           updated_at = CURRENT_TIMESTAMP
       WHERE doc_type = $1 AND org_id = $2
       RETURNING id, doc_type`,
      [doc_type, orgId]
    );

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

    res.json({ success: true, message: "Content deleted successfully" });

  } catch (err) {
    console.error("Error in deleteDetailedContent:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ==================== DOCUMENT TYPES ====================

// Get all document types
const getAllDocumentTypes = 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, doc_type, doc_name, icon, is_system, created_at, updated_at
       FROM purchase_document_types
       WHERE org_id = $1 AND is_deleted = false
       ORDER BY is_system DESC, doc_name`,
      [orgId]
    );

    // If no document types exist, initialize defaults
    if (result.rows.length === 0) {
      await initializeDefaultDocTypes(orgId, req.user?.userId);
      
      // Fetch again
      const newResult = await pool.query(
        `SELECT id, doc_type, doc_name, icon, is_system, created_at, updated_at
         FROM purchase_document_types
         WHERE org_id = $1 AND is_deleted = false
         ORDER BY is_system DESC, doc_name`,
        [orgId]
      );
      return res.json({ success: true, data: newResult.rows });
    }

    res.json({ success: true, data: result.rows });

  } catch (err) {
    console.error("Error in getAllDocumentTypes:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// Get document type by ID
const getDocumentTypeById = 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, doc_type, doc_name, icon, is_system, created_at, updated_at
       FROM purchase_document_types
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId]
    );

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

    res.json({ success: true, data: result.rows[0] });

  } catch (err) {
    console.error("Error in getDocumentTypeById:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// Create document type
const createDocumentType = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    const userId = req.user?.userId;

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

    const { doc_type, doc_name, icon } = req.body;

    if (!doc_type || !doc_name) {
      return res.status(400).json({
        success: false,
        message: "Missing required fields: doc_type and doc_name are required",
      });
    }

    // Check if doc_type already exists
    const check = await pool.query(
      `SELECT id FROM purchase_document_types
       WHERE doc_type = $1 AND org_id = $2 AND is_deleted = false`,
      [doc_type, orgId]
    );

    if (check.rows.length > 0) {
      return res.status(400).json({
        success: false,
        message: `Document type '${doc_type}' already exists`,
      });
    }

    // Create document type
    const result = await pool.query(
      `INSERT INTO purchase_document_types (doc_type, doc_name, icon, is_system, org_id, created_by)
       VALUES ($1, $2, $3, $4, $5, $6)
       RETURNING id, doc_type, doc_name, icon, is_system`,
      [doc_type, doc_name, icon || "📄", false, orgId, userId || null]
    );

    // Also create empty detailed content for this doc type
    await pool.query(
      `INSERT INTO purchase_terms_detailed (doc_type, doc_name, content, org_id, created_by)
       VALUES ($1, $2, $3, $4, $5)
       ON CONFLICT (doc_type, org_id) DO NOTHING`,
      [doc_type, doc_name, `<h2>${doc_name} Terms & Conditions</h2><p>Edit this content...</p>`, orgId, userId || null]
    );

    res.status(201).json({ success: true, data: result.rows[0] });

  } catch (err) {
    console.error("Error in createDocumentType:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// Update document type
const updateDocumentType = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    const userId = req.user?.userId;

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

    const { doc_name, icon } = req.body;

    // Check if exists and not system (or allow update but not doc_type change)
    const check = await pool.query(
      `SELECT id, doc_type, is_system FROM purchase_document_types
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId]
    );

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

    const docType = check.rows[0];

    const result = await pool.query(
      `UPDATE purchase_document_types
       SET doc_name = COALESCE($1, doc_name),
           icon = COALESCE($2, icon),
           updated_by = $3,
           updated_at = CURRENT_TIMESTAMP
       WHERE id = $4 AND org_id = $5
       RETURNING id, doc_type, doc_name, icon, is_system`,
      [doc_name, icon, userId || null, id, orgId]
    );

    // Also update doc_name in detailed content
    if (doc_name) {
      await pool.query(
        `UPDATE purchase_terms_detailed
         SET doc_name = $1, updated_at = CURRENT_TIMESTAMP
         WHERE doc_type = $2 AND org_id = $3`,
        [doc_name, docType.doc_type, orgId]
      );
    }

    res.json({ success: true, data: result.rows[0] });

  } catch (err) {
    console.error("Error in updateDocumentType:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// Delete document type (soft delete)
const deleteDocumentType = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    const userId = req.user?.userId;

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

    // Check if exists and not system
    const check = await pool.query(
      `SELECT id, doc_type, is_system FROM purchase_document_types
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId]
    );

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

    if (check.rows[0].is_system) {
      return res.status(400).json({
        success: false,
        message: "Cannot delete system document types",
      });
    }

    // Soft delete document type
    await pool.query(
      `UPDATE purchase_document_types
       SET is_deleted = true, updated_by = $1, updated_at = CURRENT_TIMESTAMP
       WHERE id = $2 AND org_id = $3`,
      [userId || null, id, orgId]
    );

    // Also soft delete related term rows
    await pool.query(
      `UPDATE purchase_term_rows
       SET is_deleted = true, updated_at = CURRENT_TIMESTAMP
       WHERE doc_type = $1 AND org_id = $2`,
      [check.rows[0].doc_type, orgId]
    );

    // Soft delete detailed content
    await pool.query(
      `UPDATE purchase_terms_detailed
       SET is_deleted = true, updated_at = CURRENT_TIMESTAMP
       WHERE doc_type = $1 AND org_id = $2`,
      [check.rows[0].doc_type, orgId]
    );

    res.json({ success: true, message: "Document type deleted successfully" });

  } catch (err) {
    console.error("Error in deleteDocumentType:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ==================== TERM ROWS ====================

// Get term rows by doc_type
const getTermRowsByDocType = async (req, res) => {
  try {
    const { doc_type } = 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, term, value, description, display_order
       FROM purchase_term_rows
       WHERE doc_type = $1 AND org_id = $2 AND is_deleted = false
       ORDER BY display_order ASC, id ASC`,
      [doc_type, orgId]
    );

    res.json({ success: true, data: result.rows });

  } catch (err) {
    console.error("Error in getTermRowsByDocType:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// Create term row
const createTermRow = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    const userId = req.user?.userId;

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

    const { doc_type, term, value, description, display_order } = req.body;

    if (!doc_type || !term) {
      return res.status(400).json({
        success: false,
        message: "Missing required fields: doc_type and term are required",
      });
    }

    // Verify doc type exists
    const docCheck = await pool.query(
      `SELECT id FROM purchase_document_types
       WHERE doc_type = $1 AND org_id = $2 AND is_deleted = false`,
      [doc_type, orgId]
    );

    if (docCheck.rows.length === 0) {
      return res.status(400).json({
        success: false,
        message: `Document type '${doc_type}' does not exist`,
      });
    }

    const result = await pool.query(
      `INSERT INTO purchase_term_rows (doc_type, term, value, description, display_order, org_id, created_by)
       VALUES ($1, $2, $3, $4, $5, $6, $7)
       RETURNING id, term, value, description, display_order`,
      [doc_type, term, value || null, description || null, display_order || 0, orgId, userId || null]
    );

    res.status(201).json({ success: true, data: result.rows[0] });

  } catch (err) {
    console.error("Error in createTermRow:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// Update term row
const updateTermRow = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    const userId = req.user?.userId;

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

    const { term, value, description, display_order } = req.body;

    // Check if exists
    const check = await pool.query(
      `SELECT id FROM purchase_term_rows
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId]
    );

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

    const result = await pool.query(
      `UPDATE purchase_term_rows
       SET term = COALESCE($1, term),
           value = $2,
           description = $3,
           display_order = COALESCE($4, display_order),
           updated_by = $5,
           updated_at = CURRENT_TIMESTAMP
       WHERE id = $6 AND org_id = $7
       RETURNING id, term, value, description, display_order`,
      [term, value || null, description || null, display_order, userId || null, id, orgId]
    );

    res.json({ success: true, data: result.rows[0] });

  } catch (err) {
    console.error("Error in updateTermRow:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// Delete term row
const deleteTermRow = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    const userId = req.user?.userId;

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

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

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

    res.json({ success: true, message: "Term row deleted successfully" });

  } catch (err) {
    console.error("Error in deleteTermRow:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// Bulk update term rows (for reordering)
const bulkUpdateTermRows = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    const userId = req.user?.userId;

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

    const { rows } = req.body; // Array of { id, display_order }

    if (!rows || !Array.isArray(rows)) {
      return res.status(400).json({
        success: false,
        message: "rows array is required",
      });
    }

    // Update each row
    const client = await pool.connect();
    try {
      await client.query("BEGIN");

      for (const row of rows) {
        await client.query(
          `UPDATE purchase_term_rows
           SET display_order = $1, updated_by = $2, updated_at = CURRENT_TIMESTAMP
           WHERE id = $3 AND org_id = $4 AND is_deleted = false`,
          [row.display_order, userId || null, row.id, orgId]
        );
      }

      await client.query("COMMIT");
    } catch (err) {
      await client.query("ROLLBACK");
      throw err;
    } finally {
      client.release();
    }

    res.json({ success: true, message: "Term rows updated successfully" });

  } catch (err) {
    console.error("Error in bulkUpdateTermRows:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// Get terms by doc type (alias for getTermRowsByDocType)
const getTermsByDocType = async (req, res) => {
  try {
    const { doc_type } = 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, term, value, description, display_order
       FROM purchase_term_rows
       WHERE doc_type = $1 AND org_id = $2 AND is_deleted = false
       ORDER BY display_order ASC, id ASC`,
      [doc_type, orgId]
    );

    res.json({ success: true, data: result.rows });

  } catch (err) {
    console.error("Error in getTermsByDocType:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

module.exports = {
  // Detailed content
  getDetailedContent,
  getAllDetailedContents,
  saveDetailedContent,
  deleteDetailedContent,
  // Document types
  getAllDocumentTypes,
  getDocumentTypeById,
  createDocumentType,
  updateDocumentType,
  deleteDocumentType,
  // Term rows
  getTermRowsByDocType,
  createTermRow,
  updateTermRow,
  deleteTermRow,
  bulkUpdateTermRows,
  getTermsByDocType,
};