


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

// ─── helpers ──────────────────────────────────────────────────────────────────

const toBoolean = (value) => {
  if (value === null || value === undefined) return false;
  if (typeof value === "boolean") return value;
  if (typeof value === "string")  return value.toLowerCase() === "true" || value === "1";
  if (typeof value === "number")  return value === 1;
  return Boolean(value); // handles PostgreSQL driver booleans
};

const SKIP_COLUMNS = new Set([
  "id", "role_id", "org_id",
  "created_by", "updated_by",
  "is_deleted", "created_at", "updated_at",
]);

const ACTIONS = ["add", "edit", "delete", "download","approve"];

const MAIN_MODULES = [
  "dashboard",
  "production_ops",
  "admin",
  "organization",
  "users",
  "leaves_management",
  "quality_control",
  "inventory",
  "weighbridge",
  "security_ops",
  "accounts",
  "hr_management",
  "procurement",
  "sales",
  "transport",
  "bom_recipes",
  "terms_conditions",
  "security",
];

const SUB_MODULES = {
  // Production
  batch_execution:   "production_ops",
  scheduling:        "production_ops",
  yield_loss:        "production_ops",
  daily_production:  "production_ops",
  daily_consumption: "production_ops",

  // Admin
  departments:      "admin",
  designations:     "admin",
  warehouses:       "admin",
  uom:              "admin",
  shift_time:       "admin",
  permissions:      "admin",
  role_permissions: "admin",

  // Organization
  org_master: "organization",

  // Users
  users_master: "users",
  create_user:  "users",

  // Leaves
  leave_apply:    "leaves_management",
  leave_requests: "leaves_management",

  // Quality Control
  qc_check_entry: "quality_control",
  check_params:   "quality_control",
  certificates:   "quality_control",

  // Inventory
  stock_dashboard:  "inventory",
  stock:            "inventory",
  inward:           "inventory",
  outward:          "inventory",
  returnable_goods: "inventory",

  // Security Ops
  security_dashboard:  "security_ops",
  visitor_management:  "security_ops",
  commercial_vehicles: "security_ops",
  parking_allocation:  "security_ops",
  watchlist:           "security_ops",

  // Accounts
  financial_dashboard: "accounts",
  journal_voucher:     "accounts",
  budget_planning:     "accounts",
  general_ledger:      "accounts",
  accounts_payable:    "accounts",
  accounts_receivable: "accounts",
  profit_loss:         "accounts",

  // HR
  employee_master:       "hr_management",
  employee_onboarding:   "hr_management",
  benefits_compensation: "hr_management",
  shifts:                "hr_management",
  shift_planner:         "hr_management",
  attendance_management: "hr_management",
  leave_management:      "hr_management",
  assets_management:     "hr_management",
  deductions:            "hr_management",
  payroll_processing:    "hr_management",
  documents_letters:     "hr_management",
  termination_relieving: "hr_management",
  doctors:               "hr_management",

  // Procurement
  vendor_master:        "procurement",
  item_master:          "procurement",
  requisitions:         "procurement",
  purchase_orders:      "procurement",
  req_for_quotation:    "procurement",
  quotation_comparison: "procurement",
  goods_receipt:        "procurement",
  approvals:            "procurement",
  proforma_invoice:     "procurement",
  group_master:         "procurement",
  payment_processing:   "procurement",
   approve_purchase_request: "procurement",

  // Sales
  sales_orders:    "sales",
  sales_customer:  "sales",
  sales_quotations:"sales",
  sales_enquiry:   "sales",
  sales_invoice:   "sales",
  sales_invoices:   "sales",

  // Transport
  dispatch_planning: "transport",
  transport_agency:  "transport",
  gatepass:          "transport",

  // BOM
  recipe_list:     "bom_recipes",
  recipe_designer: "bom_recipes",
  cost_simulation: "bom_recipes",
  version_history: "bom_recipes",
};

/**
 * Build a permission entry from a DB row for one module key.
 *
 * "view" = the bare column (e.g. row['admin']).
 * _view column only overrides if it EXISTS AND has a value in the row
 * (many parent modules like admin, accounts have NO _view column).
 */
const buildEntry = (row, moduleKey) => {
  const entry = { module: moduleKey };

  // Start with the bare column as the view/access toggle
  entry.view = toBoolean(row[moduleKey]);

  // Only let _view override if the column actually exists in the row
  const viewCol = `${moduleKey}_view`;
  if (Object.prototype.hasOwnProperty.call(row, viewCol)) {
    // Use _view value; if it's null/undefined keep the bare column value
    const viewVal = row[viewCol];
    if (viewVal !== null && viewVal !== undefined) {
      entry.view = toBoolean(viewVal);
    }
  }

  // Add action columns
  for (const action of ACTIONS) {
    const col = `${moduleKey}_${action}`;
    entry[action] = Object.prototype.hasOwnProperty.call(row, col)
      ? toBoolean(row[col])
      : false;
  }

  return entry;
};

/**
 * getRolePermissions
 * 
 * Fetches permissions for a specific role from the database.
 * Returns a flat array of permission objects.
 * 
 * @param {Object} req - Express request object
 * @param {Object} res - Express response object
 * @returns {Object} JSON response with permissions data
 */
const getRolePermissions = async (req, res) => {
  try {
    const { role_id } = req.params;

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

    const result = await pool.query(
      `SELECT * FROM role_permissions 
       WHERE role_id = $1 AND is_deleted = false 
       LIMIT 1`,
      [role_id]
    );

    if (result.rowCount === 0) {
      return res.status(200).json({
        success: true,
        data: [],
        message: "No permissions configured for this role"
      });
    }

    const row = result.rows[0];
    const flatPermissions = [];
    const processedModules = new Set();

    // Skip these system columns
    const SKIP_COLUMNS = new Set([
      'id', 'role_id', 'org_id', 'created_by', 'updated_by',
      'is_deleted', 'created_at', 'updated_at'
    ]);

    // Columns to exclude completely (don't send to frontend)
    const EXCLUDE_COLUMNS = new Set([
      // Exclude all _view columns - they are duplicates of bare columns
      'dashboard_view', 'production_ops_view', 'admin_view', 'organization_view',
      'users_view', 'leaves_management_view', 'quality_control_view', 'inventory_view',
      'weighbridge_view', 'security_ops_view', 'accounts_view', 'hr_management_view',
      'procurement_view', 'sales_view', 'transport_view', 'bom_recipes_view',
      'terms_conditions_view', 'security_view','general_ledger_view'
    ]);

    const buildEntry = (moduleName) => {
      const entry = { module: moduleName };
      entry.view = toBoolean(row[moduleName]);
      
      const actions = ['add', 'edit', 'delete', 'download',"approve"];
      actions.forEach(action => {
        const actionColumn = `${moduleName}_${action}`;
        entry[action] = Object.prototype.hasOwnProperty.call(row, actionColumn)
          ? toBoolean(row[actionColumn])
          : false;
      });
      
      return entry;
    };

    // Process all boolean columns
    for (const [columnName, value] of Object.entries(row)) {
      // Skip system columns
      if (SKIP_COLUMNS.has(columnName)) continue;
      
      // Skip excluded columns (like _view columns)
      if (EXCLUDE_COLUMNS.has(columnName)) continue;
      
      // Skip any column ending with _view
      if (columnName.endsWith('_view')) continue;
      
      // Skip action columns (they will be handled by their parent module)
      let isActionColumn = false;
      const actionSuffixes = ['_add', '_edit', '_delete', '_download','_approve'];
      for (const suffix of actionSuffixes) {
        if (columnName.endsWith(suffix)) {
          isActionColumn = true;
          break;
        }
      }
      if (isActionColumn) continue;
      
      if (typeof value !== 'boolean') continue;
      
      flatPermissions.push(buildEntry(columnName));
      processedModules.add(columnName);
    }

    console.log(`✅ Retrieved ${flatPermissions.length} permissions for role_id=${role_id}`);
    console.log(`📋 Modules: ${flatPermissions.map(p => p.module).join(', ')}`);

    return res.status(200).json({
      success: true,
      data: flatPermissions,
      total: flatPermissions.length
    });
    
  } catch (error) {
    console.error("getRolePermissions ERROR:", error);
    return res.status(500).json({
      success: false,
      message: error.message
    });
  }
};

const updateRolePermissions = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { role_id, permissions } = req.body;
    const updatedBy = req.session?.user?.id || null;
    const org_id = req.session?.user?.orgId || 1;

    console.log(`📝 Updating permissions for role_id=${role_id}`);
    
    // Remove duplicates - keep only the first occurrence of each key
    const uniquePermissions = {};
    for (const [key, value] of Object.entries(permissions)) {
      if (!uniquePermissions.hasOwnProperty(key)) {
        uniquePermissions[key] = value;
      } else {
        console.log(`⚠️ Skipping duplicate key: ${key}`);
      }
    }
    
    console.log(`📦 Unique permission fields: ${Object.keys(uniquePermissions).length}`);

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

    // Get all actual column names from the database
    const columnsResult = await client.query(`
      SELECT column_name 
      FROM information_schema.columns 
      WHERE table_name = 'role_permissions' 
      AND data_type = 'boolean'
    `);
    
    const existingColumns = new Set(columnsResult.rows.map(r => r.column_name));

    // Filter to only include valid columns
    const validColumns = [];
    const validValues = [];
    
    for (const [column, value] of Object.entries(uniquePermissions)) {
      // Skip _view columns if bare column exists
      if (column.endsWith('_view')) {
        const bareColumn = column.replace('_view', '');
        if (uniquePermissions.hasOwnProperty(bareColumn)) {
          console.log(`⚠️ Skipping ${column} because ${bareColumn} exists`);
          continue;
        }
      }
      
      if (existingColumns.has(column) && typeof value === 'boolean') {
        validColumns.push(column);
        validValues.push(value);
      } else {
        console.log(`⚠️ Skipping non-existent or invalid column: ${column}`);
      }
    }

    if (validColumns.length === 0) {
      return res.status(400).json({
        success: false,
        message: "No valid permissions to save"
      });
    }

    // Check if record exists
    const checkResult = await client.query(
      `SELECT id FROM role_permissions WHERE role_id = $1 AND is_deleted = false`,
      [role_id]
    );

    if (checkResult.rowCount === 0) {
      // INSERT new record
      const columns = validColumns;
      const placeholders = columns.map((_, i) => `$${i + 4}`).join(', ');
      const values = validValues;
      
      const insertQuery = `
        INSERT INTO role_permissions (role_id, org_id, created_by, created_at, ${columns.join(', ')})
        VALUES ($1, $2, $3, NOW(), ${placeholders})
      `;
      
      await client.query(insertQuery, [role_id, org_id, updatedBy, ...values]);
      console.log(`✅ Created new permission record`);
    } else {
      // UPDATE existing record
      const fields = [];
      const values = [];
      let idx = 1;

      for (let i = 0; i < validColumns.length; i++) {
        fields.push(`${validColumns[i]} = $${idx++}`);
        values.push(validValues[i]);
      }

      fields.push(`updated_by = $${idx++}`);
      fields.push(`updated_at = NOW()`);
      values.push(updatedBy);
      values.push(role_id);

      const updateQuery = `
        UPDATE role_permissions
        SET ${fields.join(", ")}
        WHERE role_id = $${idx} AND is_deleted = false
      `;

      await client.query(updateQuery, values);
      console.log(`✅ Updated permission record`);
    }

    return res.status(200).json({
      success: true,
      message: "Permissions saved successfully"
    });
    
  } catch (error) {
    console.error("updateRolePermissions ERROR:", error);
    return res.status(500).json({
      success: false,
      message: error.message
    });
  } finally {
    client.release();
  }
};

// ─── GET /rolePermission/get/all ──────────────────────────────────────────────
const getAllRoles = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const { rows } = await pool.query(
      `SELECT r.*, CONCAT(u.first_name, ' ', u.last_name) AS created_by_name
       FROM roles r
       LEFT JOIN users u ON u.id = r.created_by
       WHERE r.org_id = $1 AND r.is_deleted = false
       ORDER BY r.created_at DESC`,
      [org_id]
    );
    return res.json({ success: true, data: rows, totalRecords: rows.length });
  } catch (err) {
    console.error("getAllRoles ERROR:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

// ─── GET /rolePermission/:id ──────────────────────────────────────────────────
const getRoleById = async (req, res) => {
  try {
    const { id }   = req.params;
    const org_id   = req.session.user.orgId;
    const { rows } = await pool.query(
      `SELECT r.*, CONCAT(u.first_name, ' ', u.last_name) AS created_by_name
       FROM roles r
       LEFT JOIN users u ON u.id = r.created_by
       WHERE r.id = $1 AND r.org_id = $2 AND r.is_deleted = false`,
      [id, org_id]
    );
    if (!rows.length) {
      return res.status(404).json({ success: false, message: "Role not found" });
    }
    return res.json({ success: true, data: rows[0] });
  } catch (err) {
    console.error("getRoleById ERROR:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

module.exports = { getRolePermissions, updateRolePermissions, getAllRoles, getRoleById };