'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 FROM business_partner WHERE bp_type = "CUSTOMER" OR bp_type = "BOTH" LIMIT 3;',
      { 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 customer complaint 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;

    const complaints = [
      {
        company_id: companyId,
        plant_id: plantId,
        fiscal_year_id: fiscalYearId,
        ccr_no: 'CCR2026001',
        ccr_date: '2026-01-15',
        doc_no: 'RI/QA/F/08',
        rev_no: '0/01.10.18',
        rev_date: '2018-10-01',
        customer_id: customers.length > 0 ? customers[0].id : null,
        customer_name: 'ABC Manufacturing Ltd',
        mode_of_complaint: 'EMAIL',
        part_name: 'Hydraulic Cylinder Assembly',
        part_no: 'HC-2024-001',
        material_id: null,
        complaint_description: 'Oil leakage observed in hydraulic cylinder during operation. Customer reported loss of pressure after 50 hours of operation.',
        car_ref_no: 'CAR-2026-001',
        action_taken: 'Replaced O-ring seals. Conducted pressure test. Unit passed all quality checks.',
        closure_status: 'CLOSED',
        closure_date: '2026-01-25',
        remarks: 'Root cause: Improper torque during assembly. Corrective action implemented in assembly process.',
        assigned_to_user_id: userId,
        assigned_to_name: 'Quality Manager',
        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,
        ccr_no: 'CCR2026002',
        ccr_date: '2026-01-20',
        doc_no: 'RI/QA/F/08',
        rev_no: '0/01.10.18',
        rev_date: '2018-10-01',
        customer_id: customers.length > 1 ? customers[1].id : null,
        customer_name: 'XYZ Industries',
        mode_of_complaint: 'PHONE',
        part_name: 'Welded Frame Assembly',
        part_no: 'WFA-2024-045',
        material_id: null,
        complaint_description: 'Weld crack found at corner joint. Customer reported during final inspection before installation.',
        car_ref_no: 'CAR-2026-002',
        action_taken: 'Grinding and re-welding performed. NDT testing conducted. Frame replaced with new unit.',
        closure_status: 'IN_PROGRESS',
        closure_date: null,
        remarks: 'Investigation ongoing. Welding procedure under review.',
        assigned_to_user_id: userId,
        assigned_to_name: 'Welding Supervisor',
        status: 'SUBMITTED',
        created_by: userId,
        updated_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      },
      {
        company_id: companyId,
        plant_id: plantId,
        fiscal_year_id: fiscalYearId,
        ccr_no: 'CCR2026003',
        ccr_date: '2026-02-01',
        doc_no: 'RI/QA/F/08',
        rev_no: '0/01.10.18',
        rev_date: '2018-10-01',
        customer_id: customers.length > 2 ? customers[2].id : null,
        customer_name: 'DEF Engineering Pvt Ltd',
        mode_of_complaint: 'LETTER',
        part_name: 'Machined Shaft',
        part_no: 'MS-2024-089',
        material_id: null,
        complaint_description: 'Dimensional deviation in shaft diameter. Measured 50.2mm instead of specified 50.0mm ±0.05mm.',
        car_ref_no: 'CAR-2026-003',
        action_taken: 'Shaft re-machined to correct dimensions. Calibration of measuring instruments verified.',
        closure_status: 'RESOLVED',
        closure_date: '2026-02-05',
        remarks: 'Awaiting customer approval for closure.',
        assigned_to_user_id: userId,
        assigned_to_name: 'Production Manager',
        status: 'SUBMITTED',
        created_by: userId,
        updated_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      },
      {
        company_id: companyId,
        plant_id: plantId,
        fiscal_year_id: fiscalYearId,
        ccr_no: 'CCR2026004',
        ccr_date: '2026-02-07',
        doc_no: 'RI/QA/F/08',
        rev_no: '0/01.10.18',
        rev_date: '2018-10-01',
        customer_id: customers.length > 0 ? customers[0].id : null,
        customer_name: 'ABC Manufacturing Ltd',
        mode_of_complaint: 'ORAL',
        part_name: 'Painted Panel',
        part_no: 'PP-2024-112',
        material_id: null,
        complaint_description: 'Paint finish not uniform. Surface roughness observed in multiple areas.',
        car_ref_no: '',
        action_taken: '',
        closure_status: 'OPEN',
        closure_date: null,
        remarks: 'Complaint registered. Investigation to be initiated.',
        assigned_to_user_id: userId,
        assigned_to_name: 'QC Inspector',
        status: 'DRAFT',
        created_by: userId,
        updated_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      }
    ];

    await queryInterface.bulkInsert('customer_complaint_register', complaints, {});
    console.log('Customer complaint register seed data inserted successfully.');
  },

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