const db = require('../models');
const { sendResponse } = require('../utils/response');
const asyncHandler = require('../middlewares/asyncHandler');
const { Op } = require('sequelize');

const Material = db.Material;
const PurchaseOrder = db.PurchaseOrder;
const GoodsReceiptNote = db.GoodsReceiptNote;
const ProductionOrder = db.ProductionOrder;
const PurchaseRequisition = db.PurchaseRequisition;
const GateEntry = db.GateEntry;
const RequestForQuotation = db.RequestForQuotation;
const VendorInvoice = db.VendorInvoice;
const BusinessPartner = db.BusinessPartner;
const Employee = db.Employee;
const Plant = db.Plant;
const User = db.User;
const WorkCenter = db.WorkCenter;
const BOMHeader = db.BOMHeader;
const Routing = db.Routing;

const safeExecute = async (fn, defaultValue = 0) => {
  try {
    return await fn();
  } catch (error) {
    console.error('Error in KPI calculation:', error.message);
    return defaultValue;
  }
};

exports.getKPIs = asyncHandler(async (req, res) => {
  const companyId = req.user?.companyId;
  const isSuperAdmin = req.user?.isSuperAdmin || false;

  // Build where clause - superadmin sees all, others filter by company
  const buildWhere = (additionalConditions = {}) => {
    const baseWhere = { ...additionalConditions };
    if (!isSuperAdmin && companyId) {
      baseWhere.company_id = companyId;
    }
    return baseWhere;
  };

  const kpis = {
    pendingPurchaseOrders: 0,
    grnsToday: 0,
    openProductionOrders: 0,
    totalMaterials: 0,
    totalCustomers: 0,
    totalVendors: 0,
    totalEmployees: 0,
    totalPlants: 0,
    activeUsers: 0,
    totalWorkCenters: 0,
    totalBOMs: 0,
    totalRoutings: 0,
  };

  const today = new Date();
  today.setHours(0, 0, 0, 0);

  // Purchase Orders
  if (PurchaseOrder) {
    kpis.pendingPurchaseOrders = await safeExecute(async () => {
      return await PurchaseOrder.count({
        where: buildWhere({
          status: { [Op.in]: ['PENDING', 'APPROVED', 'PARTIALLY_RECEIVED'] },
        }),
      });
    });
  }

  // GRNs Today
  if (GoodsReceiptNote) {
    kpis.grnsToday = await safeExecute(async () => {
      return await GoodsReceiptNote.count({
        where: buildWhere({
          grn_date: {
            [Op.gte]: today,
            [Op.lt]: new Date(today.getTime() + 24 * 60 * 60 * 1000),
          },
        }),
      });
    });
  }

  // Production Orders
  if (ProductionOrder) {
    kpis.openProductionOrders = await safeExecute(async () => {
      return await ProductionOrder.count({
        where: buildWhere({
          status: { [Op.in]: ['RELEASED', 'IN_PROGRESS', 'PARTIALLY_COMPLETE'] },
        }),
      });
    });
  }

  // Materials
  kpis.totalMaterials = await safeExecute(async () => {
    return await Material.count({
      where: buildWhere({
        status: { [Op.ne]: 'DELETED' },
      }),
    });
  });

  // Business Partners - Customers
  kpis.totalCustomers = await safeExecute(async () => {
    return await BusinessPartner.count({
      where: buildWhere({
        bp_type: { [Op.in]: ['CUSTOMER', 'BOTH'] },
        status: 'ACTIVE',
      }),
    });
  });

  // Business Partners - Vendors
  kpis.totalVendors = await safeExecute(async () => {
    return await BusinessPartner.count({
      where: buildWhere({
        bp_type: { [Op.in]: ['VENDOR', 'BOTH'] },
        status: 'ACTIVE',
      }),
    });
  });

  // Employees
  kpis.totalEmployees = await safeExecute(async () => {
    return await Employee.count({
      where: buildWhere({
        status: 'ACTIVE',
      }),
    });
  });

  // Plants
  kpis.totalPlants = await safeExecute(async () => {
    return await Plant.count({
      where: buildWhere({
        status: 'ACTIVE',
      }),
    });
  });

  // Active Users
  kpis.activeUsers = await safeExecute(async () => {
    const userWhere = buildWhere({
      status: 'ACTIVE',
      is_active: true,
    });
    return await User.count({ where: userWhere });
  });

  // Work Centers
  kpis.totalWorkCenters = await safeExecute(async () => {
    return await WorkCenter.count({
      where: buildWhere({
        status: 'ACTIVE',
      }),
    });
  });

  // BOMs
  kpis.totalBOMs = await safeExecute(async () => {
    return await BOMHeader.count({
      where: buildWhere({
        status: 'ACTIVE',
      }),
    });
  });

  // Routings
  kpis.totalRoutings = await safeExecute(async () => {
    return await Routing.count({
      where: buildWhere({
        status: 'ACTIVE',
      }),
    });
  });

  sendResponse(res, 200, true, 'KPIs retrieved successfully', { kpis });
});

/**
 * Purchase dashboard stats for Purchase Manager / Purchase role landing page
 */
exports.getPurchaseStats = asyncHandler(async (req, res) => {
  const companyId = req.user?.companyId;
  const isSuperAdmin = req.user?.isSuperAdmin || false;
  const plantId = req.query.plantId || req.user?.plantId;

  const buildWhere = (extra = {}) => {
    const w = { ...extra };
    if (!isSuperAdmin && companyId) w.company_id = companyId;
    if (plantId) w.plant_id = plantId;
    return w;
  };

  const today = new Date();
  today.setHours(0, 0, 0, 0);
  const tomorrow = new Date(today.getTime() + 24 * 60 * 60 * 1000);
  const startOfMonth = new Date(today.getFullYear(), today.getMonth(), 1);

  const [
    pendingPO,
    openPR,
    grnToday,
    grnThisMonth,
    pendingGateEntry,
    openRFQ,
    vendorInvoicesThisMonth,
    recentPO,
  ] = await Promise.all([
    safeExecute(() => PurchaseOrder.count({ where: buildWhere({ status: { [Op.in]: ['DRAFT', 'PENDING', 'APPROVED', 'PARTIALLY_RECEIVED'] } }) })),
    safeExecute(() => PurchaseRequisition.count({ where: buildWhere({ status: { [Op.in]: ['DRAFT', 'SUBMITTED', 'APPROVED'] } }) })),
    safeExecute(() => GoodsReceiptNote.count({ where: buildWhere({ grn_date: { [Op.gte]: today, [Op.lt]: tomorrow } }) })),
    safeExecute(() => GoodsReceiptNote.count({ where: buildWhere({ grn_date: { [Op.gte]: startOfMonth } }) })),
    safeExecute(() => GateEntry.count({ where: buildWhere({ status: { [Op.in]: ['DRAFT', 'QC_PENDING', 'GRN_READY'] } }) })),
    safeExecute(() => RequestForQuotation.count({ where: buildWhere({ status: { [Op.in]: ['DRAFT', 'SENT', 'OPEN'] } }) })),
    safeExecute(() => VendorInvoice.count({ where: buildWhere({ invoice_date: { [Op.gte]: startOfMonth } }) })),
    PurchaseOrder.findAll({
      where: buildWhere(),
      limit: 5,
      order: [['created_at', 'DESC']],
      attributes: ['id', 'po_number', 'po_date', 'status', 'total_amount'],
      include: [{ model: db.BusinessPartner, as: 'vendor', attributes: ['id', 'name'], required: false }],
    }).catch(() => []),
  ]);

  sendResponse(res, 200, true, 'Purchase dashboard stats retrieved', {
    stats: {
      pendingPurchaseOrders: pendingPO,
      openPurchaseRequisitions: openPR,
      grnsToday: grnToday,
      grnsThisMonth: grnThisMonth,
      pendingGateEntries: pendingGateEntry,
      openRFQs: openRFQ,
      vendorInvoicesThisMonth: vendorInvoicesThisMonth,
    },
    recentPurchaseOrders: recentPO || [],
  });
});

/**
 * Production dashboard stats for Production Manager / Production role landing page
 */
exports.getProductionStats = asyncHandler(async (req, res) => {
  const companyId = req.user?.companyId;
  const isSuperAdmin = req.user?.isSuperAdmin || false;
  const plantId = req.query.plantId || req.user?.plantId;

  const buildWhere = (extra = {}) => {
    const w = { ...extra };
    if (!isSuperAdmin && companyId) w.company_id = companyId;
    if (plantId) w.plant_id = plantId;
    return w;
  };

  const today = new Date();
  today.setHours(0, 0, 0, 0);
  const tomorrow = new Date(today.getTime() + 24 * 60 * 60 * 1000);
  const startOfMonth = new Date(today.getFullYear(), today.getMonth(), 1);

  const MaterialIssue = db.MaterialIssue;
  const ProductionConfirmation = db.ProductionConfirmation;

  const [
    openOrders,
    releasedToday,
    closedThisMonth,
    pendingMaterialIssues,
    confirmationsToday,
    recentOrders,
  ] = await Promise.all([
    safeExecute(() =>
      ProductionOrder.count({
        where: buildWhere({
          status: { [Op.in]: ['RELEASED', 'IN_PROGRESS', 'PARTIALLY_CONFIRMED'] },
        }),
      })
    ),
    safeExecute(() =>
      ProductionOrder.count({
        where: buildWhere({
          released_at: { [Op.gte]: today, [Op.lt]: tomorrow },
        }),
      })
    ),
    safeExecute(() =>
      ProductionOrder.count({
        where: buildWhere({
          closed_at: { [Op.gte]: startOfMonth },
          status: 'CLOSED',
        }),
      })
    ),
    MaterialIssue
      ? safeExecute(() =>
        MaterialIssue.count({
          where: buildWhere({ status: { [Op.in]: ['DRAFT', 'PENDING'] } }),
        })
      )
      : Promise.resolve(0),
    ProductionConfirmation
      ? safeExecute(() =>
        ProductionConfirmation.count({
          where: buildWhere({
            created_at: { [Op.gte]: today, [Op.lt]: tomorrow },
          }),
        })
      )
      : Promise.resolve(0),
    ProductionOrder.findAll({
      where: buildWhere(),
      limit: 5,
      order: [['created_at', 'DESC']],
      attributes: [
        'id',
        'production_order_number',
        'production_order_date',
        'status',
        'order_quantity',
        'confirmed_quantity',
      ],
      include: [
        {
          model: db.Material,
          as: 'finishedMaterial',
          attributes: ['id', 'material_code', 'material_name'],
          required: false,
        },
        {
          model: db.UOM,
          as: 'uom',
          attributes: ['uom_code'],
          required: false,
        },
      ],
    }).catch(() => []),
  ]);

  sendResponse(res, 200, true, 'Production dashboard stats retrieved', {
    stats: {
      openOrders,
      releasedToday,
      closedThisMonth,
      pendingMaterialIssues,
      confirmationsToday,
    },
    recentOrders: recentOrders || [],
  });
});

/**
 * Store / Inventory dashboard stats
 */
exports.getStoreStats = asyncHandler(async (req, res) => {
  const companyId = req.user?.companyId;
  const isSuperAdmin = req.user?.isSuperAdmin || false;
  const plantId = req.query.plantId || req.user?.plantId;

  const buildWhere = (extra = {}) => {
    const w = { ...extra };
    if (!isSuperAdmin && companyId) w.company_id = companyId;
    if (plantId) w.plant_id = plantId;
    return w;
  };

  const today = new Date();
  today.setHours(0, 0, 0, 0);
  const tomorrow = new Date(today.getTime() + 24 * 60 * 60 * 1000);
  const startOfMonth = new Date(today.getFullYear(), today.getMonth(), 1);

  const InventoryGoodsReceipt = db.InventoryGoodsReceipt;
  const InventoryGoodsIssue = db.InventoryGoodsIssue;
  const InventoryStockTransfer = db.InventoryStockTransfer;
  const MaterialIssue = db.MaterialIssue;

  const [
    goodsReceiptsToday,
    goodsReceiptsThisMonth,
    goodsIssuesToday,
    pendingMaterialIssues,
    stockTransfersThisMonth,
    recentReceipts,
  ] = await Promise.all([
    InventoryGoodsReceipt
      ? safeExecute(() =>
        InventoryGoodsReceipt.count({
          where: buildWhere({
            movement_date: { [Op.gte]: today, [Op.lt]: tomorrow },
          }),
        })
      )
      : Promise.resolve(0),
    InventoryGoodsReceipt
      ? safeExecute(() =>
        InventoryGoodsReceipt.count({
          where: buildWhere({
            movement_date: { [Op.gte]: startOfMonth },
          }),
        })
      )
      : Promise.resolve(0),
    InventoryGoodsIssue
      ? safeExecute(() =>
        InventoryGoodsIssue.count({
          where: buildWhere({
            movement_date: { [Op.gte]: today, [Op.lt]: tomorrow },
          }),
        })
      )
      : Promise.resolve(0),
    MaterialIssue
      ? safeExecute(() =>
        MaterialIssue.count({
          where: buildWhere({
            status: { [Op.in]: ['DRAFT', 'PENDING'] },
          }),
        })
      )
      : Promise.resolve(0),
    InventoryStockTransfer
      ? safeExecute(() =>
        InventoryStockTransfer.count({
          where: buildWhere({
            movement_date: { [Op.gte]: startOfMonth },
          }),
        })
      )
      : Promise.resolve(0),
    InventoryGoodsReceipt
      ? InventoryGoodsReceipt.findAll({
        where: buildWhere(),
        limit: 5,
        order: [['created_at', 'DESC']],
        attributes: [
          'id', 'movement_number', 'movement_date',
          'movement_type', 'reference_type', 'status',
        ],
      }).catch(() => [])
      : Promise.resolve([]),
  ]);

  sendResponse(res, 200, true, 'Store dashboard stats retrieved', {
    stats: {
      goodsReceiptsToday,
      goodsReceiptsThisMonth,
      goodsIssuesToday,
      pendingMaterialIssues,
      stockTransfersThisMonth,
    },
    recentReceipts: recentReceipts || [],
  });
});
