'use strict';
const bcrypt = require('bcryptjs');

module.exports = {
  async up(queryInterface, Sequelize) {
    // Get company and admin user
    const [companies, adminUser] = 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 FROM user WHERE username = 'admin' LIMIT 1")
    ]);

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

    if (!companyId) {
      console.log('Company not found. Skipping department and user seeding.');
      return;
    }

    console.log(`Seeding production departments and users for company ID: ${companyId}`);

    const defaultPassword = await bcrypt.hash('Password@123', 10);

    // Step 1: Add/Update Production Departments (if they don't exist)
    const productionDepartments = [
      { code: 'LASER', name: 'Laser' },
      { code: 'BEND', name: 'Bending' },
      { code: 'WELD', name: 'Welding' }, // Might already exist, will check
      { code: 'PC', name: 'Powder Coating' },
      { code: 'PACK', name: 'Packing' }
    ];

    const departmentMap = {};
    const departmentIds = [];

    for (const dept of productionDepartments) {
      // Check if department already exists
      const [existing] = await queryInterface.sequelize.query(
        `SELECT id FROM department WHERE company_id = :companyId AND department_code = :code LIMIT 1`,
        {
          replacements: { companyId, code: dept.code },
          type: Sequelize.QueryTypes.SELECT
        }
      );

      if (existing) {
        console.log(`Department ${dept.code} already exists with ID: ${existing.id}`);
        departmentMap[dept.code] = existing.id;
        departmentIds.push(existing.id);
      } else {
        // Insert new department
        const [inserted] = await queryInterface.sequelize.query(
          `INSERT INTO department (
            company_id, department_code, department_name, 
            status, process_order_no, process_end_department,
            created_by, updated_by, created_at, updated_at
          ) VALUES (
            :companyId, :code, :name, 
            'ACTIVE', 0, 0,
            :adminUserId, :adminUserId, NOW(), NOW()
          )`,
          {
            replacements: { 
              companyId, 
              code: dept.code, 
              name: dept.name,
              adminUserId: adminUserId || null
            },
            type: Sequelize.QueryTypes.INSERT
          }
        );

        const newDeptId = inserted[0] || inserted;
        departmentMap[dept.code] = newDeptId;
        departmentIds.push(newDeptId);
        console.log(`Created department ${dept.code} (${dept.name}) with ID: ${newDeptId}`);
      }
    }

    // Step 2: Create users for each department
    const departmentUsers = [
      {
        username: 'laser.user',
        email: 'laser@ramindustries.com',
        first_name: 'Laser',
        last_name: 'Operator',
        department_code: 'LASER',
        phone: '+91-9876543301'
      },
      {
        username: 'bending.user',
        email: 'bending@ramindustries.com',
        first_name: 'Bending',
        last_name: 'Operator',
        department_code: 'BEND',
        phone: '+91-9876543302'
      },
      {
        username: 'welding.user',
        email: 'welding@ramindustries.com',
        first_name: 'Welding',
        last_name: 'Operator',
        department_code: 'WELD',
        phone: '+91-9876543303'
      },
      {
        username: 'powdercoating.user',
        email: 'powdercoating@ramindustries.com',
        first_name: 'Powder Coating',
        last_name: 'Operator',
        department_code: 'PC',
        phone: '+91-9876543304'
      },
      {
        username: 'packing.user',
        email: 'packing@ramindustries.com',
        first_name: 'Packing',
        last_name: 'Operator',
        department_code: 'PACK',
        phone: '+91-9876543305'
      }
    ];

    for (const deptUser of departmentUsers) {
      const deptId = departmentMap[deptUser.department_code];
      if (!deptId) {
        console.warn(`Department ${deptUser.department_code} not found. Skipping user ${deptUser.username}`);
        continue;
      }

      // Check if user already exists
      const [existingUser] = await queryInterface.sequelize.query(
        `SELECT id FROM user WHERE username = :username OR email = :email LIMIT 1`,
        {
          replacements: { username: deptUser.username, email: deptUser.email },
          type: Sequelize.QueryTypes.SELECT
        }
      );

      if (existingUser) {
        // Update existing user's department
        await queryInterface.sequelize.query(
          `UPDATE user SET department_id = :deptId, updated_at = NOW() WHERE id = :userId`,
          {
            replacements: { deptId, userId: existingUser.id },
            type: Sequelize.QueryTypes.UPDATE
          }
        );
        console.log(`Updated user ${deptUser.username} with department ${deptUser.department_code}`);
      } else {
        // Insert new user
        const [inserted] = await queryInterface.sequelize.query(
          `INSERT INTO user (
            company_id, username, email, password_hash,
            first_name, last_name, phone, department_id,
            is_super_admin, is_active, is_locked, status,
            created_at, updated_at
          ) VALUES (
            :companyId, :username, :email, :passwordHash,
            :firstName, :lastName, :phone, :deptId,
            0, 1, 0, 'ACTIVE',
            NOW(), NOW()
          )`,
          {
            replacements: {
              companyId,
              username: deptUser.username,
              email: deptUser.email,
              passwordHash: defaultPassword,
              firstName: deptUser.first_name,
              lastName: deptUser.last_name,
              phone: deptUser.phone,
              deptId
            },
            type: Sequelize.QueryTypes.INSERT
          }
        );
        const newUserId = inserted[0] || inserted;
        console.log(`Created user ${deptUser.username} for department ${deptUser.department_code} with ID: ${newUserId}`);
      }
    }

    // Step 3: Assign departments to existing users that don't have a department
    // Get all existing departments - using raw query
    const allDepartmentsResult = await queryInterface.sequelize.query(
      `SELECT id, department_code, department_name FROM department WHERE company_id = :companyId AND status = 'ACTIVE' ORDER BY id`,
      {
        replacements: { companyId },
        type: Sequelize.QueryTypes.SELECT
      }
    );

    const allDepartments = Array.isArray(allDepartmentsResult) ? allDepartmentsResult : [];

    if (allDepartments && allDepartments.length > 0) {
      console.log(`Found ${allDepartments.length} active departments.`);
      
      // Get all users without departments (using raw query to handle NULL properly)
      const usersWithoutDeptResult = await queryInterface.sequelize.query(
        `SELECT id, username, email, first_name, last_name, department_id 
         FROM user 
         WHERE company_id = :companyId AND (department_id IS NULL OR department_id = 0) AND status = 'ACTIVE'
         ORDER BY id`,
        {
          replacements: { companyId },
          type: Sequelize.QueryTypes.SELECT
        }
      );

      const usersWithoutDept = Array.isArray(usersWithoutDeptResult) ? usersWithoutDeptResult : [];

      if (usersWithoutDept && usersWithoutDept.length > 0) {
        console.log(`Found ${usersWithoutDept.length} users without departments. Assigning departments...`);

        // Assign departments based on username/email patterns or default to Production department
        for (const user of usersWithoutDept) {
          let assignedDeptId = null;

          // Try to match department based on username or email
          const usernameLower = (user.username || '').toLowerCase();
          const emailLower = (user.email || '').toLowerCase();

          for (const dept of allDepartments) {
            const deptCodeLower = (dept.department_code || '').toLowerCase();
            const deptNameLower = (dept.department_name || '').toLowerCase();

            if (usernameLower.includes(deptCodeLower) || 
                usernameLower.includes(deptNameLower) ||
                emailLower.includes(deptCodeLower) ||
                emailLower.includes(deptNameLower)) {
              assignedDeptId = dept.id;
              break;
            }
          }

          // If no match found, assign to Production department or first available
          if (!assignedDeptId) {
            const prodDept = allDepartments.find(d => 
              d.department_code === 'PROD' || 
              (d.department_name && d.department_name.toLowerCase().includes('production'))
            );
            assignedDeptId = prodDept ? prodDept.id : (allDepartments[0] ? allDepartments[0].id : null);
          }

          if (assignedDeptId) {
            await queryInterface.sequelize.query(
              `UPDATE user SET department_id = :deptId, updated_at = NOW() WHERE id = :userId`,
              {
                replacements: { deptId: assignedDeptId, userId: user.id },
                type: Sequelize.QueryTypes.UPDATE
              }
            );
            const assignedDept = allDepartments.find(d => d.id === assignedDeptId);
            console.log(`Assigned department ${assignedDept ? (assignedDept.department_code || assignedDept.id) : assignedDeptId} to user ${user.username}`);
          } else {
            console.warn(`Could not assign department to user ${user.username} - no departments available`);
          }
        }
        console.log(`Completed assigning departments to ${usersWithoutDept.length} users.`);
      } else {
        console.log('All users already have departments assigned.');
      }
    } else {
      console.warn('No departments found. Cannot assign departments to users.');
    }

    console.log('Production departments and users seeding completed successfully!');
  },

  async down(queryInterface, Sequelize) {
    // Get company ID
    const [companiesResult] = 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 }
    );

    const companies = Array.isArray(companiesResult) ? companiesResult : (companiesResult ? [companiesResult] : []);

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

    const companyId = companies[0].id;

    // Delete users created by this seeder
    await queryInterface.sequelize.query(
      `DELETE FROM user WHERE username IN (
        'laser.user', 'bending.user', 'welding.user', 
        'powdercoating.user', 'packing.user'
      ) AND company_id = :companyId`,
      {
        replacements: { companyId },
        type: Sequelize.QueryTypes.DELETE
      }
    );

    // Note: We don't delete departments as they might be in use by other records
    // Only remove if they were created by this seeder (check created_at or other criteria)
    // For safety, we'll keep departments in place
    console.log('Rollback completed. Departments are kept for data integrity.');
  }
};
