'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    // Get company ID
    const [companies] = await queryInterface.sequelize.query(
      "SELECT id FROM company WHERE name LIKE '%RAM Industries%' OR code = 'RI' OR code = 'COMP001' LIMIT 1",
      { type: Sequelize.QueryTypes.SELECT }
    );

    if (!companies || companies.length === 0) {
      console.log('Company not found. Skipping department parent update.');
      return;
    }

    const companyId = companies.id;

    // Find Production department (PROD or department_name like 'Production')
    const [productionDepts] = await queryInterface.sequelize.query(
      `SELECT id, department_code, department_name 
       FROM department 
       WHERE company_id = :companyId 
       AND (department_code = 'PROD' OR department_name LIKE '%Production%')
       AND status = 'ACTIVE'
       LIMIT 1`,
      {
        replacements: { companyId },
        type: Sequelize.QueryTypes.SELECT
      }
    );

    if (!productionDepts) {
      console.log('Production department not found. Skipping parent department update.');
      return;
    }

    const productionDeptId = productionDepts.id;
    console.log(`Found Production department: ${productionDepts.department_code} (ID: ${productionDeptId})`);

    // Update production departments to have Production as parent
    const productionDepartmentCodes = ['LASER', 'BEND', 'WELD', 'PC', 'PACK'];
    
    for (const deptCode of productionDepartmentCodes) {
      const [dept] = await queryInterface.sequelize.query(
        `SELECT id, department_code, department_name, parent_department_id 
         FROM department 
         WHERE company_id = :companyId 
         AND department_code = :deptCode 
         AND status = 'ACTIVE'
         LIMIT 1`,
        {
          replacements: { companyId, deptCode },
          type: Sequelize.QueryTypes.SELECT
        }
      );

      if (dept) {
        // Only update if parent_department_id is NULL or different
        if (!dept.parent_department_id || dept.parent_department_id !== productionDeptId) {
          await queryInterface.sequelize.query(
            `UPDATE department 
             SET parent_department_id = :productionDeptId, updated_at = NOW() 
             WHERE id = :deptId`,
            {
              replacements: { productionDeptId, deptId: dept.id },
              type: Sequelize.QueryTypes.UPDATE
            }
          );
          console.log(`Updated ${dept.department_code} (${dept.department_name}) to have Production as parent`);
        } else {
          console.log(`${dept.department_code} (${dept.department_name}) already has Production as parent`);
        }
      } else {
        console.log(`Department ${deptCode} not found. Skipping.`);
      }
    }

    console.log('Production departments parent update completed successfully!');
  },

  async down(queryInterface, Sequelize) {
    // Get company ID
    const [companies] = await queryInterface.sequelize.query(
      "SELECT id FROM company WHERE name LIKE '%RAM Industries%' OR code = 'RI' OR code = 'COMP001' LIMIT 1",
      { type: Sequelize.QueryTypes.SELECT }
    );

    if (!companies || companies.length === 0) {
      console.log('Company not found. Skipping rollback.');
      return;
    }

    const companyId = companies.id;

    // Remove parent_department_id from production departments (set to NULL)
    const productionDepartmentCodes = ['LASER', 'BEND', 'WELD', 'PC', 'PACK'];
    
    for (const deptCode of productionDepartmentCodes) {
      await queryInterface.sequelize.query(
        `UPDATE department 
         SET parent_department_id = NULL, updated_at = NOW() 
         WHERE company_id = :companyId 
         AND department_code = :deptCode`,
        {
          replacements: { companyId, deptCode },
          type: Sequelize.QueryTypes.UPDATE
        }
      );
    }

    console.log('Rollback completed. Parent departments removed from production departments.');
  }
};
