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

/**
 * Helper: Calculate derived account balances by integrating with AR/AP
 */
const getDerivedBalances = async (orgId, accounts) => {
  if (!accounts || accounts.length === 0) return accounts;

  // Get AR summary per account (for ASSET type accounts)
  const arSummary = await pool.query(
    `SELECT 
       si.id as invoice_id,
       si.total_amount,
       COALESCE(SUM(ar.amount), 0) as paid_amount
     FROM sales_invoices si
     LEFT JOIN accounts_receivable ar ON si.id = ar.invoice_id AND ar.is_deleted = false
     WHERE si.org_id = $1 AND si.is_deleted = false
     GROUP BY si.id`,
    [orgId]
  );

  // Get AP summary per GRN (for LIABILITY type accounts)
  const apSummary = await pool.query(
    `SELECT 
       g.id as grn_id,
       g.total_accepted_value,
       COALESCE(SUM(pt.amount), 0) as paid_amount
     FROM grn g
     LEFT JOIN grn_items gi ON gi.grn_id = g.id
     LEFT JOIN payment_transactions pt ON pt.grn_id = g.id AND pt.status = 'COMPLETED'
     WHERE g.org_id = $1 AND g.is_deleted = false AND g.grn_status IN ('Approved', 'Partially Approved')
     GROUP BY g.id, g.total_accepted_value`,
    [orgId]
  );

  // For now, return accounts without dynamic balance calculation
  // You can extend this to map accounts to specific AR/AP entries later
  return accounts;
};

/**
 * GET /chart-of-accounts
 * Get all chart of accounts with pagination and filtering
 */
const getChartOfAccounts = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    const page = parseInt(req.query.page) || 1;
    const limit = parseInt(req.query.limit) || 20;
    const offset = (page - 1) * limit;
    const search = req.query.search || "";
    const accountType = req.query.account_type;
    const level = req.query.level;

    let whereClause = `coa.org_id = $1 AND coa.is_deleted = false`;
    const params = [orgId];
    let paramCounter = 1;

    if (search) {
      paramCounter++;
      params.push(`%${search}%`);
      whereClause += ` AND (coa.account_code ILIKE $${paramCounter} OR coa.account_name ILIKE $${paramCounter})`;
    }

    if (accountType && accountType !== 'ALL') {
      paramCounter++;
      params.push(accountType);
      whereClause += ` AND coa.account_type = $${paramCounter}`;
    }

    if (level && level !== 'ALL') {
      paramCounter++;
      params.push(level);
      whereClause += ` AND coa.level = $${paramCounter}`;
    }

    // Get total count
    const countQuery = `SELECT COUNT(*) FROM chart_of_accounts coa WHERE ${whereClause}`;
    const countResult = await pool.query(countQuery, params.slice(0, paramCounter));
    const total = parseInt(countResult.rows[0].count);

    // Get accounts with parent name
    params.push(limit, offset);
    paramCounter += 2;

    const accountsQuery = `
      SELECT 
        coa.id,
        coa.account_code,
        coa.account_name,
        coa.account_type,
        coa.sub_type,
        coa.level,
        coa.parent_account_id,
        pc.account_name as parent_name,
        coa.opening_balance,
        coa.balance_type,
        coa.description,
        coa.is_active,
        coa.created_by,
        coa.created_at,
        coa.updated_by,
        coa.updated_at,
        COALESCE(coa.opening_balance, 0) as current_balance
      FROM chart_of_accounts coa
      LEFT JOIN chart_of_accounts pc ON pc.id = coa.parent_account_id AND pc.is_deleted = false
      WHERE ${whereClause}
      ORDER BY 
        CASE WHEN coa.level = 'GROUP' THEN 1 
             WHEN coa.level = 'SUBGROUP' THEN 2 
             ELSE 3 END,
        coa.account_code ASC
      LIMIT $${paramCounter - 1} OFFSET $${paramCounter}
    `;

    const result = await pool.query(accountsQuery, params);

    // Get summary counts by type
    const summaryQuery = `
      SELECT 
        COUNT(*) as total_accounts,
        COUNT(*) FILTER (WHERE is_active = true) as active_accounts,
        SUM(CASE WHEN account_type = 'ASSET' THEN opening_balance ELSE 0 END) as total_assets,
        COUNT(*) FILTER (WHERE account_type = 'ASSET') as asset_count,
        SUM(CASE WHEN account_type = 'LIABILITY' THEN opening_balance ELSE 0 END) as total_liabilities,
        COUNT(*) FILTER (WHERE account_type = 'LIABILITY') as liability_count,
        SUM(CASE WHEN account_type = 'EQUITY' THEN opening_balance ELSE 0 END) as total_equity,
        COUNT(*) FILTER (WHERE account_type = 'EQUITY') as equity_count,
        SUM(CASE WHEN account_type = 'INCOME' THEN opening_balance ELSE 0 END) as total_income,
        COUNT(*) FILTER (WHERE account_type = 'INCOME') as income_count,
        SUM(CASE WHEN account_type = 'EXPENSE' THEN opening_balance ELSE 0 END) as total_expense,
        COUNT(*) FILTER (WHERE account_type = 'EXPENSE') as expense_count,
        SUM(CASE WHEN account_type = 'BANK' THEN opening_balance ELSE 0 END) as total_bank,
        COUNT(*) FILTER (WHERE account_type = 'BANK') as bank_count
      FROM chart_of_accounts coa
      WHERE coa.org_id = $1 AND coa.is_deleted = false
    `;

    const summaryResult = await pool.query(summaryQuery, [orgId]);

    res.json({
      success: true,
      data: result.rows,
      pagination: {
        total,
        page,
        limit,
        totalPages: Math.ceil(total / limit)
      },
      summary: summaryResult.rows[0] || {}
    });

  } catch (err) {
    console.error("GET CHART OF ACCOUNTS ERROR:", err);
    res.status(500).json({ 
      success: false, 
      message: "Failed to fetch chart of accounts",
      error: err.message 
    });
  }
};

/**
 * GET /chart-of-accounts/summary
 * Get summary for dashboard cards
 */
const getChartOfAccountsSummary = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    const query = `
      SELECT 
        COUNT(*) as total_accounts,
        COUNT(*) FILTER (WHERE is_active = true) as active_accounts,
        COUNT(*) FILTER (WHERE is_active = false) as inactive_accounts,
        SUM(CASE WHEN account_type = 'ASSET' THEN opening_balance ELSE 0 END) as total_assets,
        COUNT(*) FILTER (WHERE account_type = 'ASSET') as asset_count,
        SUM(CASE WHEN account_type = 'LIABILITY' THEN opening_balance ELSE 0 END) as total_liabilities,
        COUNT(*) FILTER (WHERE account_type = 'LIABILITY') as liability_count,
        SUM(CASE WHEN account_type = 'EQUITY' THEN opening_balance ELSE 0 END) as total_equity,
        COUNT(*) FILTER (WHERE account_type = 'EQUITY') as equity_count,
        SUM(CASE WHEN account_type = 'INCOME' THEN opening_balance ELSE 0 END) as total_income,
        COUNT(*) FILTER (WHERE account_type = 'INCOME') as income_count,
        SUM(CASE WHEN account_type = 'EXPENSE' THEN opening_balance ELSE 0 END) as total_expense,
        COUNT(*) FILTER (WHERE account_type = 'EXPENSE') as expense_count,
        SUM(CASE WHEN account_type = 'BANK' THEN opening_balance ELSE 0 END) as total_bank,
        COUNT(*) FILTER (WHERE account_type = 'BANK') as bank_count,
        COUNT(*) FILTER (WHERE level = 'GROUP') as group_count,
        COUNT(*) FILTER (WHERE level = 'SUBGROUP') as subgroup_count,
        COUNT(*) FILTER (WHERE level = 'LEDGER') as ledger_count
      FROM chart_of_accounts coa
      WHERE coa.org_id = $1 AND coa.is_deleted = false
    `;

    const result = await pool.query(query, [orgId]);
    const data = result.rows[0] || {};

    res.json({
      success: true,
      data: {
        total_accounts: parseInt(data.total_accounts) || 0,
        active_accounts: parseInt(data.active_accounts) || 0,
        inactive_accounts: parseInt(data.inactive_accounts) || 0,
        total_assets: parseFloat(data.total_assets) || 0,
        asset_count: parseInt(data.asset_count) || 0,
        total_liabilities: parseFloat(data.total_liabilities) || 0,
        liability_count: parseInt(data.liability_count) || 0,
        total_equity: parseFloat(data.total_equity) || 0,
        equity_count: parseInt(data.equity_count) || 0,
        total_income: parseFloat(data.total_income) || 0,
        income_count: parseInt(data.income_count) || 0,
        total_expense: parseFloat(data.total_expense) || 0,
        expense_count: parseInt(data.expense_count) || 0,
        total_bank: parseFloat(data.total_bank) || 0,
        bank_count: parseInt(data.bank_count) || 0,
        group_count: parseInt(data.group_count) || 0,
        subgroup_count: parseInt(data.subgroup_count) || 0,
        ledger_count: parseInt(data.ledger_count) || 0,
      }
    });

  } catch (err) {
    console.error("GET COA SUMMARY ERROR:", err);
    res.status(500).json({ success: false, message: "Failed to fetch summary" });
  }
};

/**
 * GET /chart-of-accounts/:id
 * Get single account by ID
 */
const getAccountById = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    const { id } = req.params;

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

    const query = `
      SELECT 
        coa.id,
        coa.account_code,
        coa.account_name,
        coa.account_type,
        coa.sub_type,
        coa.level,
        coa.parent_account_id,
        pc.account_name as parent_name,
        pc.account_code as parent_code,
        coa.opening_balance,
        coa.balance_type,
        coa.description,
        coa.is_active,
        coa.created_by,
        coa.created_at,
        coa.updated_by,
        coa.updated_at,
        COALESCE(coa.opening_balance, 0) as current_balance
      FROM chart_of_accounts coa
      LEFT JOIN chart_of_accounts pc ON pc.id = coa.parent_account_id AND pc.is_deleted = false
      WHERE coa.id = $1 AND coa.org_id = $2 AND coa.is_deleted = false
    `;

    const result = await pool.query(query, [id, orgId]);

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

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

  } catch (err) {
    console.error("GET ACCOUNT BY ID ERROR:", err);
    res.status(500).json({ success: false, message: "Failed to fetch account" });
  }
};

/**
 * POST /chart-of-accounts
 * Create new account
 */
const createAccount = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const userId = req.user?.id;
    const orgId = req.user?.orgId;
    const {
      account_code,
      account_name,
      account_type,
      sub_type,
      level,
      parent_account_id,
      opening_balance,
      balance_type,
      description,
      is_active
    } = req.body;

    if (!userId || !orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    if (!account_code || !account_name || !account_type) {
      return res.status(400).json({ 
        success: false, 
        message: "Account code, name and type are required" 
      });
    }

    await client.query("BEGIN");

    // Check if account code already exists for this org
    const existingCheck = await client.query(
      `SELECT id FROM chart_of_accounts 
       WHERE org_id = $1 AND account_code = $2 AND is_deleted = false`,
      [orgId, account_code]
    );

    if (existingCheck.rows.length > 0) {
      await client.query("ROLLBACK");
      return res.status(409).json({ 
        success: false, 
        message: `Account code ${account_code} already exists for this organization` 
      });
    }

    // If parent_account_id is provided, verify it exists
    if (parent_account_id) {
      const parentCheck = await client.query(
        `SELECT id, level FROM chart_of_accounts 
         WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
        [parent_account_id, orgId]
      );
      
      if (parentCheck.rows.length === 0) {
        await client.query("ROLLBACK");
        return res.status(400).json({ 
          success: false, 
          message: "Parent account not found" 
        });
      }
      
      // Validate hierarchy: GROUP can have SUBGROUP/LEDGER, SUBGROUP can have LEDGER
      const parentLevel = parentCheck.rows[0].level;
      if (level === 'GROUP' && parentLevel !== 'GROUP') {
        await client.query("ROLLBACK");
        return res.status(400).json({ 
          success: false, 
          message: "GROUP level accounts can only be top-level or under other GROUP accounts" 
        });
      }
      if (level === 'SUBGROUP' && !['GROUP', 'SUBGROUP'].includes(parentLevel)) {
        await client.query("ROLLBACK");
        return res.status(400).json({ 
          success: false, 
          message: "SUBGROUP accounts can only be under GROUP or SUBGROUP accounts" 
        });
      }
      if (level === 'LEDGER' && !['GROUP', 'SUBGROUP', 'LEDGER'].includes(parentLevel)) {
        await client.query("ROLLBACK");
        return res.status(400).json({ 
          success: false, 
          message: "LEDGER accounts can be under GROUP, SUBGROUP, or LEDGER accounts" 
        });
      }
    }

    const insertResult = await client.query(
      `INSERT INTO chart_of_accounts (
        org_id, account_code, account_name, account_type, sub_type, level,
        parent_account_id, opening_balance, balance_type, description,
        is_active, created_by, created_at
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, NOW())
      RETURNING id, account_code, account_name`,
      [
        orgId, account_code, account_name, account_type, sub_type || null,
        level || 'LEDGER', parent_account_id || null,
        opening_balance || 0, balance_type || 'DEBIT',
        description || null, is_active !== false, userId
      ]
    );

    await client.query("COMMIT");

    res.status(201).json({
      success: true,
      message: "Account created successfully",
      data: insertResult.rows[0]
    });

  } catch (err) {
    await client.query("ROLLBACK");
    console.error("CREATE ACCOUNT ERROR:", err);
    res.status(500).json({ 
      success: false, 
      message: "Failed to create account",
      error: err.message 
    });
  } finally {
    client.release();
  }
};

/**
 * PUT /chart-of-accounts/:id
 * Update existing account
 */
const updateAccount = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const userId = req.user?.id;
    const orgId = req.user?.orgId;
    const { id } = req.params;
    const {
      account_code,
      account_name,
      account_type,
      sub_type,
      level,
      parent_account_id,
      opening_balance,
      balance_type,
      description,
      is_active
    } = req.body;

    if (!userId || !orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    await client.query("BEGIN");

    // Check if account exists
    const existingAccount = await client.query(
      `SELECT id, account_code, level FROM chart_of_accounts 
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId]
    );

    if (existingAccount.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "Account not found" });
    }

    // Check if new account code conflicts (if changed)
    if (account_code && account_code !== existingAccount.rows[0].account_code) {
      const conflictCheck = await client.query(
        `SELECT id FROM chart_of_accounts 
         WHERE org_id = $1 AND account_code = $2 AND id != $3 AND is_deleted = false`,
        [orgId, account_code, id]
      );
      
      if (conflictCheck.rows.length > 0) {
        await client.query("ROLLBACK");
        return res.status(409).json({ 
          success: false, 
          message: `Account code ${account_code} already exists` 
        });
      }
    }

    // Prevent circular parent reference
    if (parent_account_id === parseInt(id)) {
      await client.query("ROLLBACK");
      return res.status(400).json({ 
        success: false, 
        message: "Account cannot be its own parent" 
      });
    }

    // Build update query dynamically
    const updates = [];
    const values = [];
    let pi = 1;

    if (account_code !== undefined) {
      updates.push(`account_code = $${pi++}`);
      values.push(account_code);
    }
    if (account_name !== undefined) {
      updates.push(`account_name = $${pi++}`);
      values.push(account_name);
    }
    if (account_type !== undefined) {
      updates.push(`account_type = $${pi++}`);
      values.push(account_type);
    }
    if (sub_type !== undefined) {
      updates.push(`sub_type = $${pi++}`);
      values.push(sub_type || null);
    }
    if (level !== undefined) {
      updates.push(`level = $${pi++}`);
      values.push(level);
    }
    if (parent_account_id !== undefined) {
      updates.push(`parent_account_id = $${pi++}`);
      values.push(parent_account_id || null);
    }
    if (opening_balance !== undefined) {
      updates.push(`opening_balance = $${pi++}`);
      values.push(opening_balance);
    }
    if (balance_type !== undefined) {
      updates.push(`balance_type = $${pi++}`);
      values.push(balance_type);
    }
    if (description !== undefined) {
      updates.push(`description = $${pi++}`);
      values.push(description || null);
    }
    if (is_active !== undefined) {
      updates.push(`is_active = $${pi++}`);
      values.push(is_active);
    }

    updates.push(`updated_by = $${pi++}`);
    values.push(userId);
    updates.push(`updated_at = NOW()`);
    
    values.push(id, orgId);

    const query = `
      UPDATE chart_of_accounts 
      SET ${updates.join(", ")}
      WHERE id = $${pi++} AND org_id = $${pi++} AND is_deleted = false
      RETURNING id, account_code, account_name
    `;

    const result = await client.query(query, values);

    await client.query("COMMIT");

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

  } catch (err) {
    await client.query("ROLLBACK");
    console.error("UPDATE ACCOUNT ERROR:", err);
    res.status(500).json({ 
      success: false, 
      message: "Failed to update account",
      error: err.message 
    });
  } finally {
    client.release();
  }
};

/**
 * DELETE /chart-of-accounts/:id
 * Soft delete account
 */
const deleteAccount = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const userId = req.user?.id;
    const orgId = req.user?.orgId;
    const { id } = req.params;

    if (!userId || !orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    await client.query("BEGIN");

    // Check if account exists
    const accountCheck = await client.query(
      `SELECT id, account_code, account_name FROM chart_of_accounts 
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId]
    );

    if (accountCheck.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "Account not found" });
    }

    // Check if there are child accounts
    const childCheck = await client.query(
      `SELECT COUNT(*) as child_count FROM chart_of_accounts 
       WHERE parent_account_id = $1 AND is_deleted = false`,
      [id]
    );

    if (parseInt(childCheck.rows[0].child_count) > 0) {
      await client.query("ROLLBACK");
      return res.status(400).json({ 
        success: false, 
        message: "Cannot delete account that has child accounts. Please reassign or delete children first." 
      });
    }

    // Soft delete the account
    await client.query(
      `UPDATE chart_of_accounts 
       SET is_deleted = true, updated_by = $1, updated_at = NOW()
       WHERE id = $2`,
      [userId, id]
    );

    await client.query("COMMIT");

    res.json({
      success: true,
      message: `Account ${accountCheck.rows[0].account_code} deleted successfully`
    });

  } catch (err) {
    await client.query("ROLLBACK");
    console.error("DELETE ACCOUNT ERROR:", err);
    res.status(500).json({ 
      success: false, 
      message: "Failed to delete account",
      error: err.message 
    });
  } finally {
    client.release();
  }
};

/**
 * GET /chart-of-accounts/hierarchy
 * Get hierarchical tree of accounts
 */
const getAccountHierarchy = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    const query = `
      WITH RECURSIVE account_tree AS (
        -- Base: Top-level accounts (no parent)
        SELECT 
          id, account_code, account_name, account_type, sub_type, level,
          parent_account_id, opening_balance, balance_type, is_active,
          0 as depth,
          ARRAY[id] as path,
          account_code::text as sort_path
        FROM chart_of_accounts
        WHERE org_id = $1 AND is_deleted = false AND parent_account_id IS NULL
        
        UNION ALL
        
        -- Recursive: Child accounts
        SELECT 
          c.id, c.account_code, c.account_name, c.account_type, c.sub_type, c.level,
          c.parent_account_id, c.opening_balance, c.balance_type, c.is_active,
          p.depth + 1,
          p.path || c.id,
          p.sort_path || '.' || c.account_code
        FROM chart_of_accounts c
        INNER JOIN account_tree p ON c.parent_account_id = p.id
        WHERE c.org_id = $1 AND c.is_deleted = false
      )
      SELECT 
        id, account_code, account_name, account_type, sub_type, level,
        parent_account_id, opening_balance, balance_type, is_active,
        depth, sort_path
      FROM account_tree
      ORDER BY sort_path
    `;

    const result = await pool.query(query, [orgId]);

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

  } catch (err) {
    console.error("GET ACCOUNT HIERARCHY ERROR:", err);
    res.status(500).json({ 
      success: false, 
      message: "Failed to fetch account hierarchy",
      error: err.message 
    });
  }
};

module.exports = {
  getChartOfAccounts,
  getChartOfAccountsSummary,
  getAccountById,
  createAccount,
  updateAccount,
  deleteAccount,
  getAccountHierarchy
};