'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    // Get company and user IDs
    const [companies, users, employees] = await Promise.all([
      queryInterface.sequelize.query("SELECT id FROM company WHERE code = 'COMP001' LIMIT 1"),
      queryInterface.sequelize.query("SELECT id FROM user WHERE username = 'admin' LIMIT 1"),
      queryInterface.sequelize.query("SELECT id FROM employee LIMIT 5")
    ]);

    if (companies[0].length === 0 || users[0].length === 0) {
      console.log('Required data not found. Skipping fabricator seeding.');
      return;
    }

    const companyId = companies[0][0].id;
    const userId = users[0][0].id;
    const employeeIds = employees[0].map(e => e.id);

    // Get additional users for linking
    const [additionalUsers] = await queryInterface.sequelize.query(
      "SELECT id FROM user WHERE status = 'ACTIVE' LIMIT 10"
    );

    const fabricators = [
      {
        company_id: companyId,
        fabricator_code: 'FAB001',
        fabricator_name: 'Ramesh Kumar',
        employee_id: employeeIds[0] || null,
        contact_number: '9876543210',
        email: 'ramesh.kumar@welding.com',
        qualification: 'Certified Welder - Level 3',
        certification_no: 'AWS-CW-2023-001',
        certification_date: '2023-01-15',
        certification_expiry: '2026-01-15',
        specialization: 'TIG Welding, MIG Welding, Stainless Steel',
        experience_years: 12.5,
        user_id: additionalUsers[0]?.id || userId,
        remarks: 'Specialized in stainless steel and high-precision welding',
        status: 'ACTIVE',
        created_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      },
      {
        company_id: companyId,
        fabricator_code: 'FAB002',
        fabricator_name: 'Suresh Patil',
        employee_id: employeeIds[1] || null,
        contact_number: '9876543211',
        email: 'suresh.patil@welding.com',
        qualification: 'Certified Welder - Level 2',
        certification_no: 'AWS-CW-2022-045',
        certification_date: '2022-06-20',
        certification_expiry: '2025-06-20',
        specialization: 'Arc Welding, Structural Steel',
        experience_years: 8.0,
        user_id: additionalUsers[1]?.id || userId,
        remarks: 'Expert in structural steel fabrication',
        status: 'ACTIVE',
        created_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      },
      {
        company_id: companyId,
        fabricator_code: 'FAB003',
        fabricator_name: 'Vijay Sharma',
        employee_id: employeeIds[2] || null,
        contact_number: '9876543212',
        email: 'vijay.sharma@welding.com',
        qualification: 'Certified Welder - Level 3',
        certification_no: 'AWS-CW-2023-078',
        certification_date: '2023-03-10',
        certification_expiry: '2026-03-10',
        specialization: 'TIG Welding, Aluminum, Exotic Metals',
        experience_years: 15.0,
        user_id: additionalUsers[2]?.id || userId,
        remarks: 'Specialist in aluminum and exotic metal welding',
        status: 'ACTIVE',
        created_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      },
      {
        company_id: companyId,
        fabricator_code: 'FAB004',
        fabricator_name: 'Anil Deshmukh',
        employee_id: employeeIds[3] || null,
        contact_number: '9876543213',
        email: 'anil.deshmukh@welding.com',
        qualification: 'Certified Welder - Level 2',
        certification_no: 'AWS-CW-2021-156',
        certification_date: '2021-09-15',
        certification_expiry: '2024-09-15',
        specialization: 'MIG Welding, Sheet Metal',
        experience_years: 6.5,
        user_id: additionalUsers[3]?.id || userId,
        remarks: 'Specialized in sheet metal and thin gauge welding',
        status: 'ACTIVE',
        created_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      },
      {
        company_id: companyId,
        fabricator_code: 'FAB005',
        fabricator_name: 'Prakash Jadhav',
        employee_id: employeeIds[4] || null,
        contact_number: '9876543214',
        email: 'prakash.jadhav@welding.com',
        qualification: 'Certified Welder - Level 3',
        certification_no: 'AWS-CW-2023-203',
        certification_date: '2023-05-20',
        certification_expiry: '2026-05-20',
        specialization: 'Arc Welding, Pipe Welding, Pressure Vessels',
        experience_years: 18.0,
        user_id: additionalUsers[4]?.id || userId,
        remarks: 'Expert in pipe welding and pressure vessel fabrication',
        status: 'ACTIVE',
        created_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      },
      {
        company_id: companyId,
        fabricator_code: 'FAB006',
        fabricator_name: 'Mahesh Rane',
        employee_id: null,
        contact_number: '9876543215',
        email: 'mahesh.rane@welding.com',
        qualification: 'Certified Welder - Level 1',
        certification_no: 'AWS-CW-2023-289',
        certification_date: '2023-08-01',
        certification_expiry: '2026-08-01',
        specialization: 'MIG Welding, General Fabrication',
        experience_years: 3.5,
        user_id: additionalUsers[5]?.id || userId,
        remarks: 'Junior welder, good for general fabrication work',
        status: 'ACTIVE',
        created_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      },
      {
        company_id: companyId,
        fabricator_code: 'FAB007',
        fabricator_name: 'Santosh Bhosale',
        employee_id: null,
        contact_number: '9876543216',
        email: 'santosh.bhosale@welding.com',
        qualification: 'Certified Welder - Level 2',
        certification_no: 'AWS-CW-2022-334',
        certification_date: '2022-11-10',
        certification_expiry: '2025-11-10',
        specialization: 'TIG Welding, Stainless Steel Pipes',
        experience_years: 9.0,
        user_id: additionalUsers[6]?.id || userId,
        remarks: 'Specialized in stainless steel pipe welding',
        status: 'ACTIVE',
        created_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      },
      {
        company_id: companyId,
        fabricator_code: 'FAB008',
        fabricator_name: 'Ganesh Pawar',
        employee_id: null,
        contact_number: '9876543217',
        email: 'ganesh.pawar@welding.com',
        qualification: 'Certified Welder - Level 3',
        certification_no: 'AWS-CW-2020-445',
        certification_date: '2020-04-15',
        certification_expiry: '2023-04-15',
        specialization: 'Arc Welding, Heavy Structural',
        experience_years: 20.0,
        user_id: additionalUsers[7]?.id || userId,
        remarks: 'Senior welder with extensive experience in heavy structural work',
        status: 'SUSPENDED',
        created_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      },
      {
        company_id: companyId,
        fabricator_code: 'FAB009',
        fabricator_name: 'Deepak Sawant',
        employee_id: null,
        contact_number: '9876543218',
        email: 'deepak.sawant@welding.com',
        qualification: 'Certified Welder - Level 2',
        certification_no: 'AWS-CW-2023-567',
        certification_date: '2023-07-25',
        certification_expiry: '2026-07-25',
        specialization: 'MIG Welding, Automotive Components',
        experience_years: 7.5,
        user_id: additionalUsers[8]?.id || userId,
        remarks: 'Specialized in automotive component welding',
        status: 'ACTIVE',
        created_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      },
      {
        company_id: companyId,
        fabricator_code: 'FAB010',
        fabricator_name: 'Rajendra Kale',
        employee_id: null,
        contact_number: '9876543219',
        email: 'rajendra.kale@welding.com',
        qualification: 'Certified Welder - Level 3',
        certification_no: 'AWS-CW-2022-678',
        certification_date: '2022-12-05',
        certification_expiry: '2025-12-05',
        specialization: 'TIG Welding, Precision Work, Instrumentation',
        experience_years: 14.0,
        user_id: additionalUsers[9]?.id || userId,
        remarks: 'Expert in precision welding for instrumentation and control panels',
        status: 'ACTIVE',
        created_by: userId,
        created_at: new Date(),
        updated_at: new Date()
      }
    ];

    await queryInterface.bulkInsert('fabricator', fabricators);
    console.log('✅ Successfully seeded 10 fabricators');
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.bulkDelete('fabricator', null, {});
    console.log('✅ Fabricators deleted');
  }
};
