'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    // Get reference data
    const companies = await queryInterface.sequelize.query(
      'SELECT id FROM company LIMIT 1;',
      { type: Sequelize.QueryTypes.SELECT }
    );

    const plants = await queryInterface.sequelize.query(
      'SELECT id FROM plant LIMIT 1;',
      { type: Sequelize.QueryTypes.SELECT }
    );

    const fiscalYears = await queryInterface.sequelize.query(
      'SELECT id FROM fiscal_year ORDER BY id DESC LIMIT 1;',
      { type: Sequelize.QueryTypes.SELECT }
    );

    const customers = await queryInterface.sequelize.query(
      'SELECT id, name FROM business_partner WHERE bp_type = "CUSTOMER" OR bp_type = "BOTH" LIMIT 2;',
      { type: Sequelize.QueryTypes.SELECT }
    );

    const users = await queryInterface.sequelize.query(
      'SELECT id FROM user LIMIT 1;',
      { type: Sequelize.QueryTypes.SELECT }
    );

    if (!companies.length || !plants.length || !fiscalYears.length) {
      console.log('Required master data not found. Skipping in-process checklist seeding.');
      return;
    }

    const companyId = companies[0].id;
    const plantId = plants[0].id;
    const fiscalYearId = fiscalYears[0].id;
    const userId = users.length > 0 ? users[0].id : null;

    // Insert header records
    const checkLists = [
      {
        company_id: companyId,
        plant_id: plantId,
        fiscal_year_id: fiscalYearId,
        ipc_no: 'IPC2026001',
        ipc_date: '2026-02-01',
        doc_no: 'RI/QA/F/09',
        rev_no: '0 / 05.10.2018',
        work_order_no: 'WO-2026-001',
        customer_id: customers.length > 0 ? customers[0].id : null,
        customer_name: customers.length > 0 ? customers[0].name : 'ABC Manufacturing',
        model: 'HYD-CYL-500',
        total_qty: 50,
        status: 'APPROVED',
        created_by: userId,
        updated_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      },
      {
        company_id: companyId,
        plant_id: plantId,
        fiscal_year_id: fiscalYearId,
        ipc_no: 'IPC2026002',
        ipc_date: '2026-02-05',
        doc_no: 'RI/QA/F/09',
        rev_no: '0 / 05.10.2018',
        work_order_no: 'WO-2026-002',
        customer_id: customers.length > 1 ? customers[1].id : null,
        customer_name: customers.length > 1 ? customers[1].name : 'XYZ Industries',
        model: 'FRAME-ASM-200',
        total_qty: 100,
        status: 'SUBMITTED',
        created_by: userId,
        updated_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      }
    ];

    await queryInterface.bulkInsert('in_process_checklist', checkLists, {});

    // Get inserted IDs
    const insertedCheckLists = await queryInterface.sequelize.query(
      'SELECT id, ipc_no FROM in_process_checklist WHERE ipc_no IN ("IPC2026001", "IPC2026002") ORDER BY ipc_no;',
      { type: Sequelize.QueryTypes.SELECT }
    );

    if (insertedCheckLists.length > 0) {
      const details = [];

      // Details for IPC2026001
      const ipc1Id = insertedCheckLists.find(c => c.ipc_no === 'IPC2026001')?.id;
      if (ipc1Id) {
        details.push(
          {
            ipc_id: ipc1Id,
            line_no: 1,
            item_description: 'Cylinder Barrel - Machining Check',
            drawing_no: 'DWG-CB-001',
            model: 'HYD-CYL-500',
            qty_per_set: 1,
            total_qty: 50,
            qc_status: 'OK',
            remarks: 'All dimensions within tolerance'
          },
          {
            ipc_id: ipc1Id,
            line_no: 2,
            item_description: 'Piston Rod - Surface Finish',
            drawing_no: 'DWG-PR-002',
            model: 'HYD-CYL-500',
            qty_per_set: 1,
            total_qty: 50,
            qc_status: 'OK',
            remarks: 'Chrome plating verified'
          },
          {
            ipc_id: ipc1Id,
            line_no: 3,
            item_description: 'End Cap - Drilling & Tapping',
            drawing_no: 'DWG-EC-003',
            model: 'HYD-CYL-500',
            qty_per_set: 2,
            total_qty: 100,
            qc_status: 'OK',
            remarks: 'Thread depth checked'
          }
        );
      }

      // Details for IPC2026002
      const ipc2Id = insertedCheckLists.find(c => c.ipc_no === 'IPC2026002')?.id;
      if (ipc2Id) {
        details.push(
          {
            ipc_id: ipc2Id,
            line_no: 1,
            item_description: 'Frame Side Panel - Welding',
            drawing_no: 'DWG-FSP-101',
            model: 'FRAME-ASM-200',
            qty_per_set: 2,
            total_qty: 200,
            qc_status: 'OK',
            remarks: 'Weld quality inspected'
          },
          {
            ipc_id: ipc2Id,
            line_no: 2,
            item_description: 'Cross Member - Assembly',
            drawing_no: 'DWG-CM-102',
            model: 'FRAME-ASM-200',
            qty_per_set: 3,
            total_qty: 300,
            qc_status: 'OK',
            remarks: 'Bolt torque verified'
          }
        );
      }

      if (details.length > 0) {
        await queryInterface.bulkInsert('in_process_checklist_detail', details, {});
      }
    }

    console.log('In-Process CheckList seed data inserted successfully.');
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.bulkDelete('in_process_checklist_detail', null, {});
    await queryInterface.bulkDelete('in_process_checklist', null, {});
  }
};
