const db = require('../models');
const { sendSuccess, sendError } = require('../utils/response');
const asyncHandler = require('../middlewares/asyncHandler');
const { Op } = require('sequelize');
const { generateNextNumber } = require('../services/numberFormatService');

const ctx = (req) => ({
  companyId: req.user?.companyId || req.user?.company_id || req.query?.companyId || req.query?.company_id || req.body?.company_id,
  plantId: req.user?.plantId || req.user?.plant_id || req.query?.plantId || req.query?.plant_id || req.body?.plant_id
});

const DEFAULT_CHECKLIST_ROWS = [
  'Overall dimensions maintained(±2mm)',
  'Sub Assy items weld in position',
  'Squareness',
  'Deburing without sander marks',
  'Free from damages & surface level',
  'All items are weld for matching holes'
];

const isEmptyDisplayValue = (v) => {
  const t = String(v ?? '').trim();
  return !t || t === '-' || t.toUpperCase() === 'N/A' || t.toUpperCase() === 'NULL';
};

const parseNumberOrNull = (value) => {
  if (value === '' || value === null || value === undefined) return null;
  const n = Number(value);
  return Number.isFinite(n) ? n : null;
};

const parseOperationMaterialDetails = (jsonText) => {
  if (!jsonText) return [];
  try {
    const arr = JSON.parse(jsonText);
    if (!Array.isArray(arr)) return [];
    return arr
      .map((entry) => {
        if (!entry || typeof entry !== 'object') return null;
        const materialId = parseInt(entry.material_id || entry.materialId || entry.id || entry.value, 10);
        if (!Number.isFinite(materialId) || materialId <= 0) return null;
        return {
          material_id: materialId,
          qty: parseNumberOrNull(entry.qty ?? entry.quantity),
          qa_menus: Array.isArray(entry.qa_menus)
            ? Array.from(new Set(entry.qa_menus.map((m) => String(m || '').trim().toUpperCase()).filter(Boolean)))
            : []
        };
      })
      .filter(Boolean);
  } catch (_) {
    return [];
  }
};

// Generate WFIR Number
const generateWFIRNumber = async (companyId, fiscalYearId = null) => {
  try {
    const generatedNumber = await generateNextNumber(companyId, 'WELDING_FINAL_INSPECTION_REPORT', null, fiscalYearId);
    return generatedNumber;
  } catch (error) {
    console.warn('Number format not found, using fallback method:', error.message);
    const today = new Date();
    const year = today.getFullYear();
    const prefix = `WFIR${year}`;
    
    const lastWFIR = await db.WeldingFinalInspectionReport.findOne({
      where: {
        company_id: companyId,
        wfir_no: { [Op.like]: `${prefix}%` }
      },
      order: [['wfir_no', 'DESC']]
    });

    let sequence = 1;
    if (lastWFIR) {
      const lastSequence = parseInt(lastWFIR.wfir_no.slice(-4)) || 0;
      sequence = lastSequence + 1;
    }

    return `${prefix}${sequence.toString().padStart(4, '0')}`;
  }
};

// Get all Welding Final Inspection Reports
exports.getAll = asyncHandler(async (req, res) => {
  const {
    page = 1,
    limit = 25,
    search = '',
    company_id,
    plant_id,
    fiscal_year_id,
    customer_id,
    status,
    inspection_status,
    date_from,
    date_to
  } = req.query;

  const offset = (page - 1) * limit;
  const where = {};

  if (company_id) where.company_id = company_id;
  if (plant_id) where.plant_id = plant_id;
  if (fiscal_year_id) where.fiscal_year_id = fiscal_year_id;
  if (customer_id) where.customer_id = customer_id;
  if (status) where.status = status;
  if (inspection_status) where.inspection_status = inspection_status;

  if (date_from && date_to) {
    where.wfir_date = { [Op.between]: [date_from, date_to] };
  } else if (date_from) {
    where.wfir_date = { [Op.gte]: date_from };
  } else if (date_to) {
    where.wfir_date = { [Op.lte]: date_to };
  }

  if (search) {
    where[Op.or] = [
      { wfir_no: { [Op.like]: `%${search}%` } },
      { model: { [Op.like]: `%${search}%` } },
      { customer_name: { [Op.like]: `%${search}%` } },
      { fabricator_name: { [Op.like]: `%${search}%` } },
      { won: { [Op.like]: `%${search}%` } }
    ];
  }

  const { count, rows } = await db.WeldingFinalInspectionReport.findAndCountAll({
    where,
    include: [
      {
        model: db.Company,
        as: 'company',
        attributes: ['id', 'code', 'name']
      },
      {
        model: db.Plant,
        as: 'plant',
        attributes: ['id', 'code', 'name']
      },
      {
        model: db.FiscalYear,
        as: 'fiscalYear',
        attributes: ['id', 'fiscal_year', 'start_date', 'end_date']
      },
      {
        model: db.BusinessPartner,
        as: 'customer',
        attributes: ['id', 'bp_number', 'name', 'legal_name']
      },
      {
        model: db.User,
        as: 'creator',
        attributes: ['id', 'username', 'email']
      }
    ],
    limit: parseInt(limit),
    offset: parseInt(offset),
    order: [['wfir_date', 'DESC'], ['id', 'DESC']],
    distinct: true
  });

  // Backfill display fields from Work Order for legacy rows where model/customer are empty.
  const wonList = [...new Set(rows.map((r) => String(r.won || '').trim()).filter(Boolean))];
  const woByNumber = new Map();
  if (wonList.length) {
    const woRows = await db.WorkOrder.findAll({
      where: {
        company_id: company_id || ctx(req).companyId,
        ...(plant_id ? { plant_id } : {}),
        work_order_number: { [Op.in]: wonList }
      },
      attributes: ['work_order_number'],
      include: [
        { model: db.Material, as: 'finishedMaterial', attributes: ['material_number', 'description'], required: false },
        { model: db.BusinessPartner, as: 'customer', attributes: ['name', 'legal_name'], required: false }
      ]
    });
    woRows.forEach((wo) => woByNumber.set(String(wo.work_order_number || '').trim(), wo));
  }

  const enrichedRows = rows.map((row) => {
    const plain = row.toJSON();
    const wo = woByNumber.get(String(plain.won || '').trim());
    const itemCode = !isEmptyDisplayValue(plain.model)
      ? plain.model
      : (wo?.finishedMaterial?.material_number || '');
    const itemName = wo?.finishedMaterial?.description || '';
    const customerName = !isEmptyDisplayValue(plain.customer_name)
      ? plain.customer_name
      : (
        plain.customer?.name ||
        wo?.customer?.name ||
        wo?.customer?.legal_name ||
        ''
      );

    return {
      ...plain,
      item_code: itemCode,
      item_name: itemName,
      model: itemCode || plain.model || '',
      customer_name: customerName
    };
  });

  return sendSuccess(
    res,
    'Welding Final Inspection Reports fetched successfully',
    {
      items: enrichedRows,
      pagination: {
        total: count,
        page: parseInt(page),
        limit: parseInt(limit),
        pages: Math.ceil(count / limit)
      }
    }
  );
});

// Work order options for Welding Final Inspection
exports.getWorkOrderOptions = asyncHandler(async (req, res) => {
  const { companyId, plantId } = ctx(req);
  if (!companyId) return sendError(res, 'Company context is required', 400);

  const rows = await db.WorkOrder.findAll({
    where: {
      company_id: companyId,
      ...(plantId ? { plant_id: plantId } : {})
    },
    attributes: ['id', 'work_order_number', 'work_order_quantity', 'order_quantity', 'planned_start_date', 'created_at', 'createdAt'],
    include: [
      { model: db.Material, as: 'finishedMaterial', attributes: ['id', 'material_number', 'description', 'revision_number'], required: false },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'name', 'legal_name'], required: false }
    ],
    order: [['id', 'DESC']]
  });

  const options = rows.map((wo) => ({
    id: wo.id,
    work_order_number: wo.work_order_number,
    item_code: wo.finishedMaterial?.material_number || '',
    item_name: wo.finishedMaterial?.description || '',
    revision_no: wo.finishedMaterial?.revision_number || '',
    work_order_qty: wo.work_order_quantity || wo.order_quantity || 0,
    work_order_date: wo.planned_start_date || wo.created_at || wo.createdAt || null,
    customer_id: wo.customer?.id || null,
    customer_name: wo.customer?.name || wo.customer?.legal_name || '',
    label: `${wo.work_order_number} | ${wo.finishedMaterial?.material_number || '-'} | ${wo.finishedMaterial?.description || '-'}`
  }));

  return sendSuccess(res, 'Work order options fetched', options);
});

// Work order prefill for Welding Final Inspection
exports.getWorkOrderPrefill = asyncHandler(async (req, res) => {
  const { workOrderId } = req.params;
  const { companyId, plantId } = ctx(req);
  if (!companyId) return sendError(res, 'Company context is required', 400);

  const wo = await db.WorkOrder.findOne({
    where: {
      id: workOrderId,
      company_id: companyId,
      ...(plantId ? { plant_id: plantId } : {})
    },
    include: [
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'name', 'legal_name'], required: false },
      { model: db.Material, as: 'finishedMaterial', attributes: ['id', 'material_number', 'description', 'revision_number'], required: false },
      {
        model: db.Routing,
        as: 'routing',
        required: false,
        include: [{ model: db.RoutingOperation, as: 'operations', required: false }]
      }
    ]
  });
  if (!wo) return sendError(res, 'Work order not found', 404);

  const routingOps = Array.isArray(wo.routing?.operations) ? wo.routing.operations : [];
  const selectedRows = [];
  const selectedMaterialIds = new Set();
  routingOps.forEach((op) => {
    parseOperationMaterialDetails(op.operation_materials_json)
      .filter((d) => Array.isArray(d.qa_menus) && d.qa_menus.includes('WELDING_FINAL_INSPECTION'))
      .forEach((d) => {
        selectedRows.push(d);
        selectedMaterialIds.add(d.material_id);
      });
  });
  const materials = selectedMaterialIds.size > 0
    ? await db.Material.findAll({
      where: { id: { [Op.in]: Array.from(selectedMaterialIds) } },
      attributes: ['id', 'material_number', 'description']
    })
    : [];
  const materialById = new Map(materials.map((m) => [m.id, m]));
  const seen = new Set();
  const templateRows = selectedRows
    .map((d) => ({
      description: materialById.get(d.material_id)?.description || materialById.get(d.material_id)?.material_number || '',
      qty: d.qty != null ? String(d.qty) : ''
    }))
    .filter((r) => {
      const key = `${String(r.description || '').trim().toUpperCase()}|${String(r.qty || '').trim()}`;
      if (!r.description || seen.has(key)) return false;
      seen.add(key);
      return true;
    });

  const checklistRows = DEFAULT_CHECKLIST_ROWS.map((description) => ({ description, qty: 'OK' }));
  const detailsTemplate = [...templateRows, ...checklistRows].map((r, idx) => ({
    line_no: idx + 1,
    description: r.description,
    qty: r.qty,
    frame_1: '',
    frame_2: '',
    frame_3: '',
    frame_4: '',
    frame_5: '',
    frame_6: '',
    frame_7: '',
    frame_8: '',
    frame_9: '',
    frame_10: '',
    frame_11: '',
    frame_12: '',
    frame_13: '',
    frame_14: '',
    frame_15: '',
    frame_16: '',
    frame_17: '',
    frame_18: '',
    frame_19: '',
    frame_20: '',
    remarks: ''
  }));

  return sendSuccess(res, 'Work order prefill fetched', {
    won: wo.work_order_number || '',
    item_code: wo.finishedMaterial?.material_number || '',
    item_name: wo.finishedMaterial?.description || '',
    work_order_date: wo.planned_start_date || wo.created_at || wo.createdAt || null,
    model: wo.finishedMaterial?.material_number || wo.finishedMaterial?.description || '',
    rev_no: wo.finishedMaterial?.revision_number || '0',
    customer_id: wo.customer?.id || null,
    customer_name: wo.customer?.name || wo.customer?.legal_name || '',
    total_qty: wo.work_order_quantity || wo.order_quantity || 0,
    sample_qty: wo.work_order_quantity || wo.order_quantity || 0,
    details_template: detailsTemplate
  });
});

// Get by ID
exports.getById = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const report = await db.WeldingFinalInspectionReport.findByPk(id, {
    include: [
      {
        model: db.Company,
        as: 'company',
        attributes: ['id', 'code', 'name']
      },
      {
        model: db.Plant,
        as: 'plant',
        attributes: ['id', 'code', 'name']
      },
      {
        model: db.FiscalYear,
        as: 'fiscalYear',
        attributes: ['id', 'fiscal_year', 'start_date', 'end_date']
      },
      {
        model: db.BusinessPartner,
        as: 'customer',
        attributes: ['id', 'bp_number', 'name', 'legal_name']
      },
      {
        model: db.User,
        as: 'fabricator',
        attributes: ['id', 'username', 'email']
      },
      {
        model: db.User,
        as: 'qcInspector',
        attributes: ['id', 'username', 'email']
      },
      {
        model: db.User,
        as: 'creator',
        attributes: ['id', 'username', 'email']
      },
      {
        model: db.User,
        as: 'updater',
        attributes: ['id', 'username', 'email']
      },
      {
        model: db.WeldingFinalInspectionReportDetail,
        as: 'details',
        order: [['line_no', 'ASC']]
      }
    ]
  });

  if (!report) {
    return sendError(res, 'Welding Final Inspection Report not found', 404);
  }

  return sendSuccess(res, 'Welding Final Inspection Report fetched successfully', report);
});

// Create
exports.create = asyncHandler(async (req, res) => {
  const userId = req.user?.id;
  const transaction = await db.sequelize.transaction();

  try {
    const { details, ...headerData } = req.body;

    // Generate WFIR Number if not provided
    if (!headerData.wfir_no) {
      headerData.wfir_no = await generateWFIRNumber(
        headerData.company_id,
        headerData.fiscal_year_id
      );
    }

    // Set default document number if not provided
    if (!headerData.doc_no) {
      headerData.doc_no = 'RI/QA/F/04';
    }

    // Create header
    const report = await db.WeldingFinalInspectionReport.create({
      ...headerData,
      created_by: userId,
      updated_by: userId
    }, { transaction });

    // Create detail lines if provided
    if (details && Array.isArray(details) && details.length > 0) {
      const detailsWithWFIRId = details.map((detail, index) => ({
        ...detail,
        wfir_id: report.id,
        line_no: detail.line_no || (index + 1)
      }));

      await db.WeldingFinalInspectionReportDetail.bulkCreate(detailsWithWFIRId, { transaction });
    }

    await transaction.commit();

    // Fetch the created report with all associations
    const createdReport = await db.WeldingFinalInspectionReport.findByPk(report.id, {
      include: [
        {
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name']
        },
        {
          model: db.Plant,
          as: 'plant',
          attributes: ['id', 'code', 'name']
        },
        {
          model: db.FiscalYear,
          as: 'fiscalYear',
          attributes: ['id', 'fiscal_year']
        },
        {
          model: db.BusinessPartner,
          as: 'customer',
          attributes: ['id', 'bp_number', 'name']
        },
        {
          model: db.WeldingFinalInspectionReportDetail,
          as: 'details',
          order: [['line_no', 'ASC']]
        }
      ]
    });

    return sendSuccess(res, 'Welding Final Inspection Report created successfully', createdReport, null, 201);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

// Update
exports.update = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.id;
  const transaction = await db.sequelize.transaction();

  try {
    const report = await db.WeldingFinalInspectionReport.findByPk(id);

    if (!report) {
      await transaction.rollback();
      return sendError(res, 'Welding Final Inspection Report not found', 404);
    }

    const { details, ...headerData } = req.body;

    // Update header
    await report.update({
      ...headerData,
      updated_by: userId
    }, { transaction });

    // Update detail lines
    if (details && Array.isArray(details)) {
      // Delete existing details
      await db.WeldingFinalInspectionReportDetail.destroy({
        where: { wfir_id: id },
        transaction
      });

      // Create new details
      if (details.length > 0) {
        const detailsWithWFIRId = details.map((detail, index) => ({
          ...detail,
          wfir_id: report.id,
          line_no: detail.line_no || (index + 1)
        }));

        await db.WeldingFinalInspectionReportDetail.bulkCreate(detailsWithWFIRId, { transaction });
      }
    }

    await transaction.commit();

    // Fetch the updated report with all associations
    const updatedReport = await db.WeldingFinalInspectionReport.findByPk(id, {
      include: [
        {
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name']
        },
        {
          model: db.Plant,
          as: 'plant',
          attributes: ['id', 'code', 'name']
        },
        {
          model: db.FiscalYear,
          as: 'fiscalYear',
          attributes: ['id', 'fiscal_year']
        },
        {
          model: db.BusinessPartner,
          as: 'customer',
          attributes: ['id', 'bp_number', 'name']
        },
        {
          model: db.User,
          as: 'fabricator',
          attributes: ['id', 'username', 'email']
        },
        {
          model: db.User,
          as: 'qcInspector',
          attributes: ['id', 'username', 'email']
        },
        {
          model: db.WeldingFinalInspectionReportDetail,
          as: 'details',
          order: [['line_no', 'ASC']]
        }
      ]
    });

    return sendSuccess(res, 'Welding Final Inspection Report updated successfully', updatedReport);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

// Delete
exports.delete = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const transaction = await db.sequelize.transaction();

  try {
    const report = await db.WeldingFinalInspectionReport.findByPk(id);

    if (!report) {
      await transaction.rollback();
      return sendError(res, 'Welding Final Inspection Report not found', 404);
    }

    // Delete detail lines first
    await db.WeldingFinalInspectionReportDetail.destroy({
      where: { wfir_id: id },
      transaction
    });

    // Delete header
    await report.destroy({ transaction });

    await transaction.commit();

    return sendSuccess(res, 'Welding Final Inspection Report deleted successfully');
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

// Get by Customer
exports.getByCustomer = asyncHandler(async (req, res) => {
  const { customerId } = req.params;

  const reports = await db.WeldingFinalInspectionReport.findAll({
    where: { customer_id: customerId },
    include: [
      {
        model: db.Company,
        as: 'company',
        attributes: ['id', 'code', 'name']
      },
      {
        model: db.Plant,
        as: 'plant',
        attributes: ['id', 'code', 'name']
      },
      {
        model: db.BusinessPartner,
        as: 'customer',
        attributes: ['id', 'bp_number', 'name', 'legal_name']
      }
    ],
    order: [['wfir_date', 'DESC']]
  });

  return sendSuccess(res, 'Welding Final Inspection Reports fetched successfully', reports);
});

// Update status only
exports.updateStatus = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const { companyId, plantId } = ctx(req);
  const status = String(req.body?.status || '').toUpperCase();

  if (!['DRAFT', 'SUBMITTED', 'APPROVED', 'CANCELLED'].includes(status)) {
    return sendError(res, 'Invalid status', 400);
  }

  const row = await db.WeldingFinalInspectionReport.findOne({
    where: {
      id,
      company_id: companyId,
      ...(plantId ? { plant_id: plantId } : {})
    }
  });
  if (!row) return sendError(res, 'Welding Final Inspection Report not found', 404);

  await row.update({
    status,
    updated_by: req.user?.id || req.user?.userId || null
  });
  return sendSuccess(res, `Status updated to ${status}`, row);
});

module.exports = exports;
