'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    // Get RAM Industries company ID and plants
    const [companies, plants, users] = await Promise.all([
      queryInterface.sequelize.query(
        "SELECT id FROM company WHERE name LIKE '%RAM Industries%' OR code = 'RI' OR code = 'COMP001' LIMIT 1"
      ),
      queryInterface.sequelize.query(
        "SELECT id, code, name FROM plant WHERE code = 'PLANT001' OR name LIKE '%Main Plant%' OR name LIKE '%RAM%' LIMIT 5"
      ),
      queryInterface.sequelize.query("SELECT id FROM user WHERE username = 'admin' LIMIT 1")
    ]);

    const companyId = companies[0].length > 0 ? companies[0][0].id : null;
    const plantRecords = plants[0] || [];
    const userId = users[0].length > 0 ? users[0][0].id : null;

    if (!companyId) {
      console.log('Company not found. Skipping storage location seeding.');
      return;
    }

    if (plantRecords.length === 0) {
      console.log('No plants found. Skipping storage location seeding.');
      return;
    }

    console.log(`Seeding storage locations for company ID: ${companyId}, Plants: ${plantRecords.length}`);

    // Define storage locations for manufacturing/engineering components company
    const storageLocations = [
      // Warehouse Locations
      {
        code: 'RM-STORE',
        description: 'Raw Material Store',
        type: 'WAREHOUSE',
        address: 'Building A, Ground Floor - Raw Material Storage Area'
      },
      {
        code: 'FG-WH',
        description: 'Finished Goods Warehouse',
        type: 'WAREHOUSE',
        address: 'Building B, Ground Floor - Finished Goods Storage'
      },
      {
        code: 'SFG-STORE',
        description: 'Semi-Finished Goods Store',
        type: 'WAREHOUSE',
        address: 'Building A, First Floor - Semi-Finished Goods Storage'
      },
      {
        code: 'PACK-AREA',
        description: 'Packing Area',
        type: 'WAREHOUSE',
        address: 'Building B, Ground Floor - Packing & Dispatch Area'
      },
      {
        code: 'DISPATCH',
        description: 'Dispatch Area',
        type: 'WAREHOUSE',
        address: 'Building B, Loading Bay - Dispatch & Shipping Area'
      },
      {
        code: 'CONS-STORE',
        description: 'Consumables Store',
        type: 'WAREHOUSE',
        address: 'Building A, Ground Floor - Consumables & Spares Storage'
      },
      {
        code: 'MNT-STORE',
        description: 'Maintenance Store',
        type: 'WAREHOUSE',
        address: 'Building C, Ground Floor - Maintenance Spares & Tools'
      },
      
      // Production Locations
      {
        code: 'PROD-FLR1',
        description: 'Production Floor - Assembly Line 1',
        type: 'PRODUCTION',
        address: 'Building A, First Floor - Assembly Production Area'
      },
      {
        code: 'PROD-FLR2',
        description: 'Production Floor - Assembly Line 2',
        type: 'PRODUCTION',
        address: 'Building A, First Floor - Assembly Production Area'
      },
      {
        code: 'WELD-AREA',
        description: 'Welding Area',
        type: 'PRODUCTION',
        address: 'Building A, Ground Floor - Welding Section'
      },
      {
        code: 'MACH-SHOP',
        description: 'Machine Shop',
        type: 'PRODUCTION',
        address: 'Building A, Ground Floor - CNC & Machining Section'
      },
      {
        code: 'FAB-AREA',
        description: 'Fabrication Area',
        type: 'PRODUCTION',
        address: 'Building A, Ground Floor - Sheet Metal Fabrication'
      },
      {
        code: 'PAINT-BOOTH',
        description: 'Paint Booth',
        type: 'PRODUCTION',
        address: 'Building A, Ground Floor - Painting & Coating Section'
      },
      
      // Quality Locations
      {
        code: 'QA-AREA',
        description: 'Quality Inspection Area',
        type: 'QUALITY',
        address: 'Building B, First Floor - Quality Control & Inspection'
      },
      {
        code: 'QA-LAB',
        description: 'Quality Lab',
        type: 'QUALITY',
        address: 'Building B, First Floor - Quality Testing Laboratory'
      },
      {
        code: 'INCOMING-QC',
        description: 'Incoming Inspection Area',
        type: 'QUALITY',
        address: 'Building A, Ground Floor - Incoming Material Inspection'
      },
      
      // Scrap & Blocked Locations
      {
        code: 'SCRAP-YARD',
        description: 'Scrap Yard',
        type: 'SCRAP',
        address: 'Building C, Outside - Scrap Material Storage Yard'
      },
      {
        code: 'QUARANTINE',
        description: 'Quarantine / Blocked Area',
        type: 'BLOCKED',
        address: 'Building A, Ground Floor - Quarantine & Blocked Materials'
      },
      {
        code: 'REJECT-AREA',
        description: 'Rejection Area',
        type: 'BLOCKED',
        address: 'Building A, Ground Floor - Rejected Material Storage'
      },
      
      // Other Locations
      {
        code: 'TOOL-ROOM',
        description: 'Tool Room',
        type: 'OTHER',
        address: 'Building C, Ground Floor - Tools, Fixtures & Gauges Storage'
      },
      {
        code: 'DRAWING-RM',
        description: 'Drawing Room',
        type: 'OTHER',
        address: 'Building B, First Floor - Engineering Drawing Storage'
      },
      {
        code: 'SAMPLE-AREA',
        description: 'Sample Storage Area',
        type: 'OTHER',
        address: 'Building B, First Floor - Sample & Reference Material Storage'
      }
    ];

    // Insert storage locations for each plant
    const insertPromises = [];
    const today = new Date().toISOString().split('T')[0];

    for (const plant of plantRecords) {
      const plantId = plant.id;
      console.log(`Seeding storage locations for plant: ${plant.name} (ID: ${plantId})`);

      for (const location of storageLocations) {
        // Check if location already exists for this plant
        const [existing] = await queryInterface.sequelize.query(
          `SELECT id FROM storage_location 
           WHERE company_id = ${companyId} 
           AND plant_id = ${plantId} 
           AND storage_location_code = '${location.code}' 
           LIMIT 1`
        );

        if (existing[0] && existing[0].length > 0) {
          console.log(`  Storage location ${location.code} already exists for plant ${plant.name}, skipping...`);
          continue;
        }

        insertPromises.push(
          queryInterface.bulkInsert('storage_location', [
            {
              company_id: companyId,
              plant_id: plantId,
              storage_location_code: location.code,
              description: location.description,
              storage_location_type: location.type,
              address: location.address,
              status: 'ACTIVE',
              effective_from: today,
              effective_to: null,
              created_by: userId,
              updated_by: userId,
              created_at: new Date(),
              updated_at: new Date()
            }
          ])
        );
      }
    }

    if (insertPromises.length > 0) {
      await Promise.all(insertPromises);
      console.log(`Successfully seeded ${insertPromises.length} storage locations across ${plantRecords.length} plant(s).`);
    } else {
      console.log('All storage locations already exist. No new records inserted.');
    }
  },

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

    const companyId = companies[0].length > 0 ? companies[0][0].id : null;

    if (!companyId) {
      console.log('Company not found. Skipping storage location deletion.');
      return;
    }

    // List of storage location codes to delete
    const locationCodes = [
      'RM-STORE', 'FG-WH', 'SFG-STORE', 'PACK-AREA', 'DISPATCH', 'CONS-STORE', 'MNT-STORE',
      'PROD-FLR1', 'PROD-FLR2', 'WELD-AREA', 'MACH-SHOP', 'FAB-AREA', 'PAINT-BOOTH',
      'QA-AREA', 'QA-LAB', 'INCOMING-QC',
      'SCRAP-YARD', 'QUARANTINE', 'REJECT-AREA',
      'TOOL-ROOM', 'DRAWING-RM', 'SAMPLE-AREA'
    ];

    await queryInterface.sequelize.query(
      `DELETE FROM storage_location 
       WHERE company_id = ${companyId} 
       AND storage_location_code IN ('${locationCodes.join("','")}')`
    );

    console.log('Storage locations deleted successfully.');
  }
};

