import db from "../../config/database.js";
import { STATUS_TRANSITIONS } from "./departmentWorkflowConfig.js";
import { departmentWorkScope, hasMinimumRole, ACCESS_ROLES } from "../../utils/roleAccess.js";
import { getPolicyReadScope, policyOwnershipFilter } from "../../utils/dataScope.js";

const LABELS = Object.freeze({
  sales: "Sales", "pos-management": "POS Management", operations: "Operations",
  underwriting: "Underwriting", claims: "Claims", "customer-support": "Customer Support",
  renewal: "Renewal", finance: "Finance", accounts: "Accounts",
  "human-resources": "Human Resources (HR)", administration: "Administration",
  "information-technology": "Information Technology (IT)", marketing: "Marketing",
  compliance: "Compliance", legal: "Legal", training: "Training",
  "business-development": "Business Development", audit: "Audit",
  "risk-management": "Risk Management", crm: "CRM (Customer Relationship Management)",
});
const labelFor = (slug) => LABELS[slug] || slug.replaceAll("-", " ").replace(/\b\w/g, (letter) => letter.toUpperCase());
const slugForLabel = (label) => Object.entries(LABELS).find(([, value]) => value === label)?.[0] || "";
const displayDate = (value) => value ? new Date(value).toISOString().slice(0, 10) : "";
const number = (value) => Number(value) || 0;
const businessRow = (row) => {
  const odIncome = number(row.od_income);
  const tpIncome = number(row.tp_income);
  const netIncome = number(row.net_income);
  return {
    ...row,
    pos_count: number(row.pos_count),
    policy_count: number(row.policy_count),
    total_od: number(row.total_od),
    total_tp: number(row.total_tp),
    net_premium: number(row.net_premium),
    gross_premium: number(row.gross_premium),
    od_income: odIncome,
    tp_income: tpIncome,
    net_income: netIncome,
    total_income: odIncome + tpIncome + netIncome,
  };
};
const businessSummary = (rows) => rows.reduce((summary, row) => {
  summary.policy_count += row.policy_count;
  summary.total_od += row.total_od;
  summary.total_tp += row.total_tp;
  summary.net_premium += row.net_premium;
  summary.gross_premium += row.gross_premium;
  summary.od_income += row.od_income;
  summary.tp_income += row.tp_income;
  summary.net_income += row.net_income;
  summary.total_income += row.total_income;
  return summary;
}, {
  policy_count: 0, total_od: 0, total_tp: 0, net_premium: 0,
  gross_premium: 0, od_income: 0, tp_income: 0, net_income: 0, total_income: 0,
});

const getAdministrationBusinessReports = async (user, requestedMonth = "") => {
  const ownership = policyOwnershipFilter(getPolicyReadScope(user), "p.created_by");
  const now = new Date();
  const currentMonth = `${now.getFullYear()}-${String(now.getMonth() + 1).padStart(2, "0")}`;
  const month = String(requestedMonth || currentMonth).trim();
  if (!/^\d{4}-(0[1-9]|1[0-2])$/.test(month)) {
    const error = new Error("Month must use YYYY-MM format");
    error.statusCode = 400;
    throw error;
  }
  const [year, monthNumber] = month.split("-").map(Number);
  const startDate = `${month}-01`;
  const endDateValue = new Date(Date.UTC(year, monthNumber, 1));
  const endDate = `${endDateValue.getUTCFullYear()}-${String(endDateValue.getUTCMonth() + 1).padStart(2, "0")}-01`;
  const periodWhere = `${ownership.sql} AND p.issue_date >= ? AND p.issue_date < ?`;
  const periodParams = [...ownership.params, startDate, endDate];
  const fiscalStartYear = monthNumber >= 4 ? year : year - 1;
  const fiscalStartDate = `${fiscalStartYear}-04-01`;
  const fiscalEndDate = `${fiscalStartYear + 1}-04-01`;
  const fiscalParams = [...ownership.params, fiscalStartDate, fiscalEndDate];
  const fiscalMonths = Array.from({ length: 12 }, (_, index) => {
    const value = new Date(Date.UTC(fiscalStartYear, 3 + index, 1));
    return {
      key: `${value.getUTCFullYear()}-${String(value.getUTCMonth() + 1).padStart(2, "0")}`,
      label: new Intl.DateTimeFormat("en-IN", { month: "long" }).format(value),
    };
  });
  const incomeColumns = `
    COALESCE(SUM(COALESCE(p.total_od, 0) *
      COALESCE(CAST(NULLIF(REPLACE(p.pos_od, '%', ''), '') AS DECIMAL(10,4)), 0) / 100), 0) AS od_income,
    COALESCE(SUM(COALESCE(p.total_tp, 0) *
      COALESCE(CAST(NULLIF(REPLACE(p.pos_tp, '%', ''), '') AS DECIMAL(10,4)), 0) / 100), 0) AS tp_income,
    COALESCE(SUM(COALESCE(p.net_premium, 0) *
      COALESCE(CAST(NULLIF(REPLACE(p.pos_net, '%', ''), '') AS DECIMAL(10,4)), 0) / 100), 0) AS net_income`;
  const [[posRows], [employeeRows], [allPosRows], [posMonthRows]] = await Promise.all([
    db.query(`
      SELECT p.pos_id,
        COALESCE(NULLIF(TRIM(pos.name), ''), 'Unassigned POS') pos_name,
        COALESCE(NULLIF(TRIM(pos.pos_code), ''), CAST(p.pos_id AS CHAR), 'Unassigned') pos_code,
        COALESCE(NULLIF(TRIM(manager.name), ''), 'Unassigned Relationship Manager') relationship_manager,
        COUNT(*) policy_count,
        COALESCE(SUM(p.total_od), 0) total_od,
        COALESCE(SUM(p.total_tp), 0) total_tp,
        COALESCE(SUM(p.net_premium), 0) net_premium,
        COALESCE(SUM(p.total_payable), 0) gross_premium,
        ${incomeColumns}
      FROM policies p
      LEFT JOIN employee_pos pos ON pos.id = p.pos_id
      LEFT JOIN employees manager ON manager.id = pos.relationship_manager
      WHERE ${periodWhere}
      GROUP BY p.pos_id, pos.name, pos.pos_code, manager.name
      ORDER BY net_premium DESC, policy_count DESC, pos_name`, periodParams),
    db.query(`
      SELECT pos.relationship_manager employee_id,
        COALESCE(NULLIF(TRIM(manager.employee_code), ''), 'Unassigned') employee_code,
        COALESCE(NULLIF(TRIM(manager.name), ''), 'Unassigned Relationship Manager') employee_name,
        COUNT(DISTINCT p.pos_id) pos_count,
        COUNT(*) policy_count,
        COALESCE(SUM(p.total_od), 0) total_od,
        COALESCE(SUM(p.total_tp), 0) total_tp,
        COALESCE(SUM(p.net_premium), 0) net_premium,
        COALESCE(SUM(p.total_payable), 0) gross_premium,
        ${incomeColumns}
      FROM policies p
      LEFT JOIN employee_pos pos ON pos.id = p.pos_id
      LEFT JOIN employees manager ON manager.id = pos.relationship_manager
      WHERE ${periodWhere}
      GROUP BY pos.relationship_manager, manager.employee_code, manager.name
      ORDER BY net_premium DESC, policy_count DESC, employee_name`, periodParams),
    db.query(`
      SELECT pos.id pos_id,
        COALESCE(NULLIF(TRIM(pos.name), ''), 'Unnamed POS') pos_name,
        COALESCE(NULLIF(TRIM(pos.pos_code), ''), CAST(pos.id AS CHAR)) pos_code,
        COALESCE(NULLIF(TRIM(pos.mobile), ''), '—') mobile,
        COALESCE(NULLIF(TRIM(pos.email), ''), '—') email,
        COALESCE(NULLIF(TRIM(pos.status), ''), 'Unassigned') pos_status,
        COALESCE(NULLIF(TRIM(manager.name), ''), 'Unassigned Relationship Manager') relationship_manager
      FROM employee_pos pos
      LEFT JOIN employees manager ON manager.id = pos.relationship_manager
      ORDER BY pos.name, pos.pos_code`),
    db.query(`
      SELECT p.pos_id, DATE_FORMAT(p.issue_date, '%Y-%m') month_key,
        COUNT(*) policy_count,
        COALESCE(SUM(p.total_od), 0) total_od,
        COALESCE(SUM(p.total_tp), 0) total_tp,
        COALESCE(SUM(p.net_premium), 0) net_premium,
        COALESCE(SUM(p.total_payable), 0) gross_premium,
        ${incomeColumns}
      FROM policies p
      WHERE ${ownership.sql} AND p.issue_date >= ? AND p.issue_date < ?
        AND p.pos_id IS NOT NULL
      GROUP BY p.pos_id, DATE_FORMAT(p.issue_date, '%Y-%m')
      ORDER BY p.pos_id, month_key`, fiscalParams),
  ]);
  const pos = posRows.map(businessRow);
  const employees = employeeRows.map(businessRow);
  const posMonthMap = new Map(posMonthRows.map((row) => [`${row.pos_id}:${row.month_key}`, businessRow(row)]));
  const allPos = allPosRows.map((row) => {
    const months = Object.fromEntries(
      fiscalMonths.map(({ key }) => [key, posMonthMap.get(`${row.pos_id}:${key}`) || null]),
    );
    const fiscalBusiness = businessSummary(Object.values(months).filter(Boolean));
    return {
      ...row,
      months,
      has_business: fiscalBusiness.policy_count > 0,
      fiscal_policy_count: fiscalBusiness.policy_count,
      fiscal_net_premium: fiscalBusiness.net_premium,
      fiscal_gross_premium: fiscalBusiness.gross_premium,
      fiscal_total_income: fiscalBusiness.total_income,
    };
  }).sort((left, right) =>
    Number(right.has_business) - Number(left.has_business)
    || right.fiscal_net_premium - left.fiscal_net_premium
    || right.fiscal_policy_count - left.fiscal_policy_count
    || left.pos_name.localeCompare(right.pos_name)
  );
  const businessPosCount = allPos.filter((row) => row.has_business).length;
  return {
    period: {
      key: month,
      label: new Intl.DateTimeFormat("en-IN", { month: "long", year: "numeric" }).format(new Date(year, monthNumber - 1, 1)),
      fiscal_label: `FY ${fiscalStartYear}-${String(fiscalStartYear + 1).slice(-2)}`,
      months: fiscalMonths,
    },
    summary: {
      ...businessSummary(pos),
      pos_count: pos.length,
      all_pos_count: allPos.length,
      business_pos_count: businessPosCount,
      no_business_pos_count: allPos.length - businessPosCount,
      employee_count: employees.length,
    },
    pos,
    all_pos: allPos,
    employees,
  };
};

class DepartmentDashboardModel {
  static async getDashboard(department, user, filters = {}) {
    const scope = departmentWorkScope(user, "wi");
    if (department === "human-resources") {
      const employeeWhere = hasMinimumRole(user, ACCESS_ROLES.MANAGER) ? "1 = 1" : "e.id = ?";
      const employeeParams = hasMinimumRole(user, ACCESS_ROLES.MANAGER) ? [] : [Number(user.id)];
      const [[employeeCounts], [caseCounts], [recent]] = await Promise.all([
        db.query(`SELECT COUNT(*) total,
          SUM(e.status = 'Active') active,
          SUM(e.joining_date >= DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')) new_joiners,
          SUM(e.status != 'Active' OR e.relieving_date IS NOT NULL) inactive
          FROM employees e WHERE ${employeeWhere}`, employeeParams),
        db.query(`SELECT SUM(work_type = 'Leave' AND status IN ('Open','Pending')) pending_leave,
          SUM(work_type = 'Exit' AND status NOT IN ('Completed','Rejected')) pending_exit
          FROM department_work_items wi WHERE department_slug = ? AND ${scope.sql}`, [department, ...scope.params]),
        db.query(`SELECT wi.id, wi.title, wi.status, wi.updated_at,
          COALESCE(e.name, 'HR Team') owner
          FROM department_work_items wi LEFT JOIN employees e ON wi.assigned_to = e.id
          WHERE wi.department_slug = ? AND ${scope.sql}
          ORDER BY wi.updated_at DESC LIMIT 8`, [department, ...scope.params]),
      ]);
      return {
        department, label: labelFor(department),
        summary: [
          { label: "Active Employees", value: Number(employeeCounts[0]?.active) || 0, trend: `${Number(employeeCounts[0]?.total) || 0} total` },
          { label: "New Joiners", value: Number(employeeCounts[0]?.new_joiners) || 0, trend: "This Month" },
          { label: "Pending Leave", value: Number(caseCounts[0]?.pending_leave) || 0, trend: "Needs Review" },
          { label: "Exit Cases", value: Number(caseCounts[0]?.pending_exit) || 0, trend: `${Number(employeeCounts[0]?.inactive) || 0} inactive` },
        ],
        recentActivity: recent.map((row) => ({ id: row.id, title: row.title, owner: row.owner, status: row.status })),
      };
    }

    const allDepartments = department === "administration";
    const departmentWhere = allDepartments ? "1 = 1" : "wi.department_slug = ?";
    const where = `${departmentWhere} AND ${scope.sql}`;
    const params = [...(allDepartments ? [] : [department]), ...scope.params];
    const [[counts], [recent]] = await Promise.all([
      db.query(`SELECT COUNT(*) total,
        SUM(status = 'Open') open_count,
        SUM(status IN ('In Progress','Pending')) pending_count,
        SUM(status IN ('Approved','Completed')) completed_count,
        SUM(due_date < CURRENT_DATE() AND status NOT IN ('Completed','Rejected')) overdue_count
        FROM department_work_items wi WHERE ${where}`, params),
      db.query(`SELECT wi.id, wi.title, wi.status, wi.priority, wi.policy_number,
        COALESCE(e.name, 'Department Team') owner
        FROM department_work_items wi LEFT JOIN employees e ON wi.assigned_to = e.id
        WHERE ${where} ORDER BY wi.updated_at DESC LIMIT 8`, params),
    ]);
    const row = counts[0] || {};
    const businessReports = allDepartments ? await getAdministrationBusinessReports(user, filters.month) : null;
    return {
      department, label: labelFor(department),
      summary: [
        { label: "Total Work Items", value: Number(row.total) || 0, trend: "All Records" },
        { label: "Open", value: Number(row.open_count) || 0, trend: "New Work" },
        { label: "In Progress", value: Number(row.pending_count) || 0, trend: "Action Required" },
        { label: "Overdue", value: Number(row.overdue_count) || 0, trend: `${Number(row.completed_count) || 0} completed` },
      ],
      recentActivity: recent,
      ...(businessReports ? { businessReports } : {}),
    };
  }

  static async getReports(department, filters = {}, user) {
    const scope = departmentWorkScope(user, "wi");
    const conditions = [];
    const params = [];
    if (department !== "administration") {
      conditions.push("wi.department_slug = ?");
      params.push(department);
    } else if (filters.department && filters.department !== "All") {
      const selectedSlug = slugForLabel(filters.department);
      if (selectedSlug) {
        conditions.push("wi.department_slug = ?");
        params.push(selectedSlug);
      }
    }
    if (department === "administration" && filters.month) {
      const month = String(filters.month).trim();
      if (!/^\d{4}-(0[1-9]|1[0-2])$/.test(month)) {
        const error = new Error("Month must use YYYY-MM format");
        error.statusCode = 400;
        throw error;
      }
      const [year, monthNumber] = month.split("-").map(Number);
      const endDateValue = new Date(Date.UTC(year, monthNumber, 1));
      conditions.push("wi.created_at >= ? AND wi.created_at < ?");
      params.push(
        `${month}-01`,
        `${endDateValue.getUTCFullYear()}-${String(endDateValue.getUTCMonth() + 1).padStart(2, "0")}-01`,
      );
    }
    if (filters.status && filters.status !== "All") {
      conditions.push("wi.status = ?");
      params.push(filters.status);
    }
    if (filters.product && filters.product !== "All") {
      conditions.push("wi.work_type = ?");
      params.push(filters.product);
    }
    if (filters.priority && filters.priority !== "All") {
      conditions.push("wi.priority = ?");
      params.push(filters.priority);
    }
    if (filters.search) {
      conditions.push("(wi.title LIKE ? OR wi.policy_number LIKE ? OR wi.customer_name LIKE ?)");
      const search = `%${String(filters.search).trim()}%`;
      params.push(search, search, search);
    }
    conditions.push(scope.sql);
    params.push(...scope.params);
    const [rows] = await db.query(
      `SELECT wi.id, wi.department_slug departmentSlug, wi.title name,
        wi.work_type product, wi.status, wi.priority,
        wi.policy_number policyNumber, wi.customer_name subjectName,
        DATE_FORMAT(wi.due_date, '%Y-%m-%d') dueDate,
        DATE_FORMAT(wi.created_at, '%Y-%m-%d') createdAt,
        COALESCE(e.name, 'Unassigned') assignee,
        CASE WHEN wi.due_date < CURRENT_DATE() AND wi.status NOT IN ('Completed','Rejected')
          THEN 'Overdue' ELSE 'On Track' END dueState
       FROM department_work_items wi
       LEFT JOIN employees e ON wi.assigned_to = e.id
       ${conditions.length ? `WHERE ${conditions.join(" AND ")}` : ""}
       ORDER BY wi.updated_at DESC LIMIT 1000`,
      params,
    );
    return rows.map((row) => ({
      ...row,
      department: labelFor(row.departmentSlug),
      allowedTransitions: STATUS_TRANSITIONS[row.status] || [],
    }));
  }

  static async getPolicies(department, user) {
    const scope = departmentWorkScope(user, "wi");
    if (department === "human-resources") {
      const employeeWhere = hasMinimumRole(user, ACCESS_ROLES.MANAGER) ? "" : "WHERE e.id = ?";
      const [rows] = await db.query(
        `SELECT e.id, e.employee_code policyNumber, e.name employeeName,
          COALESCE(dsg.designation_name, 'Unassigned') product,
          COALESCE(dep.department_name, 'Unassigned') premium, e.status
         FROM employees e
         LEFT JOIN departments dep ON e.department = dep.id
         LEFT JOIN designations dsg ON e.designation = dsg.id
         ${employeeWhere}
         ORDER BY e.name LIMIT 2000`,
        hasMinimumRole(user, ACCESS_ROLES.MANAGER) ? [] : [Number(user.id)],
      );
      return rows;
    }
    const [rows] = await db.query(
      `SELECT DISTINCT p.id, p.policy_number policyNumber, p.insured_name insuredName,
        CONCAT_WS(' · ', p.insurance_company, p.policy_type) product,
        p.total_payable premium,
        CASE WHEN p.tp_expiry < CURRENT_DATE() THEN 'Expired' ELSE 'Active' END status
       FROM department_work_items wi
       INNER JOIN policies p ON wi.policy_id = p.id
       WHERE ${department === "administration" ? "1 = 1" : "wi.department_slug = ?"}
         AND ${scope.sql}
       ORDER BY p.created_at DESC LIMIT 2000`,
      [...(department === "administration" ? [] : [department]), ...scope.params],
    );
    return rows;
  }

  static async getRenewals(department, user) {
    const scope = departmentWorkScope(user, "wi");
    if (department === "human-resources") {
      const [rows] = await db.query(
        `SELECT id, CONCAT('HR-', id) policyNumber,
          customer_name employeeName, due_date renewalDate,
          work_type premium, status
         FROM department_work_items wi
         WHERE department_slug = ? AND work_type IN ('Leave','Exit','Joining')
           AND ${scope.sql}
         ORDER BY due_date IS NULL, due_date, updated_at DESC LIMIT 1000`,
        [department, ...scope.params],
      );
      return rows;
    }
    const [rows] = await db.query(
      `SELECT DISTINCT p.id, p.policy_number policyNumber, p.insured_name insuredName,
        LEAST(COALESCE(p.od_expiry, '9999-12-31'), COALESCE(p.tp_expiry, '9999-12-31')) renewalDate,
        p.total_payable premium,
        CASE
          WHEN LEAST(COALESCE(p.od_expiry, '9999-12-31'), COALESCE(p.tp_expiry, '9999-12-31')) < CURRENT_DATE() THEN 'Due'
          ELSE 'Upcoming'
        END status
       FROM department_work_items wi INNER JOIN policies p ON wi.policy_id = p.id
       WHERE ${department === "administration" ? "1 = 1" : "wi.department_slug = ?"}
         AND ${scope.sql}
       ORDER BY renewalDate LIMIT 1000`,
      [...(department === "administration" ? [] : [department]), ...scope.params],
    );
    return rows.map((row) => ({ ...row, renewalDate: displayDate(row.renewalDate) }));
  }

  static async getMasters(department, user) {
    const scope = departmentWorkScope(user, "wi");
    if (department === "human-resources") {
      if (!hasMinimumRole(user, ACCESS_ROLES.MANAGER)) return [];
      const [rows] = await db.query(
        `SELECT dep.id, dep.department_name name, COUNT(e.id) count,
          MAX(e.updated_at) updatedAt
         FROM departments dep LEFT JOIN employees e ON e.department = dep.id
         GROUP BY dep.id, dep.department_name ORDER BY dep.department_name`,
      );
      return rows.map((row) => ({ ...row, updatedAt: displayDate(row.updatedAt) }));
    }
    if (department === "administration") {
      const [rows] = await db.query(
        `SELECT MIN(id) id, department_slug name, COUNT(*) count, MAX(updated_at) updatedAt
         FROM department_work_items wi WHERE ${scope.sql}
         GROUP BY department_slug ORDER BY department_slug`,
        scope.params,
      );
      return rows.map((row) => ({
        ...row,
        name: labelFor(row.name),
        updatedAt: displayDate(row.updatedAt),
      }));
    }
    const [rows] = await db.query(
      `SELECT MIN(id) id, work_type name, COUNT(*) count, MAX(updated_at) updatedAt
       FROM department_work_items wi WHERE department_slug = ? AND ${scope.sql}
       GROUP BY work_type ORDER BY work_type`,
      [department, ...scope.params],
    );
    return rows.map((row) => ({ ...row, updatedAt: displayDate(row.updatedAt) }));
  }
}

export default DepartmentDashboardModel;
