import 'dotenv/config';
import { randomUUID } from 'crypto';
import mysql from 'mysql2/promise';
import bcrypt from 'bcryptjs';

async function main() {
  const databaseUrl = process.env.DATABASE_URL;
  let connection: mysql.Connection;

  if (databaseUrl) {
    connection = await mysql.createConnection(databaseUrl);
  } else {
    connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      port: parseInt(process.env.DB_PORT || '') || 3306,
      user: process.env.DB_USERNAME || 'root',
      password: process.env.DB_PASSWORD || '',
      database: process.env.DB_DATABASE || 'tracker',
    });
  }

  try {
    console.log('Starting seed script...');
    const ownerId = '640bed22-2b5f-4d86-b42c-b20c487954e9';

    // 1. Ensure Owner exists and has password Admin@123456
    const [owners] = await connection.query<mysql.RowDataPacket[]>(
      'SELECT id FROM owner WHERE id = ?',
      [ownerId]
    );

    const hashedAdminPassword = bcrypt.hashSync('Admin@123456', 10);
    const defaultImage = JSON.stringify({
      file_name: 'uploads/common/profile.webp',
      original_name: 'profile.webp',
      file_size: 8192,
      mime_type: 'image/webp'
    });

    if (owners.length === 0) {
      console.log('Inserting owner Hridoy...');
      await connection.query(
        `INSERT INTO owner (id, name, email, username, password, address, phone, image, role, otp, status, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP(3), CURRENT_TIMESTAMP(3))`,
        [
          ownerId,
          'Hridoy',
          'hridoyhaque.info@gmail.com',
          'ridoy',
          hashedAdminPassword,
          'Dhaka, Bangladesh',
          '+880123456789',
          defaultImage,
          'owner',
          null,
          'active'
        ]
      );
    } else {
      console.log('Updating owner Hridoy...');
      await connection.query(
        `UPDATE owner SET password = ?, status = ? WHERE id = ?`,
        [hashedAdminPassword, 'active', ownerId]
      );
    }

    // 2. Query access roles
    const [roles] = await connection.query<mysql.RowDataPacket[]>(
      'SELECT id, slug FROM access_roles WHERE owner_id = ?',
      [ownerId]
    );

    let memberRoleId = '';
    const memberRole = roles.find((r) => r.slug === 'member');
    if (memberRole) {
      memberRoleId = memberRole.id;
    }

    // 3. Ensure Company exists
    const [companies] = await connection.query<mysql.RowDataPacket[]>(
      'SELECT id FROM company WHERE owner_id = ?',
      [ownerId]
    );
    if (companies.length === 0) {
      console.log('Seeding company...');
      await connection.query(
        `INSERT INTO company (id, name, company_code, email, phone, address, website, logo, description, founded_at, industry, status, owner_id, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP(3), CURRENT_TIMESTAMP(3))`,
        [
          randomUUID(),
          'Wincasoft Solutions',
          'WIN',
          'info@wincasoft.com',
          '+880123456789',
          'Dhaka, Bangladesh',
          'https://wincasoft.com',
          defaultImage,
          'Software Development Company',
          new Date(),
          'Information Technology',
          'active',
          ownerId
        ]
      );
    }

    // 4. Ensure Working Schedule exists
    const [schedules] = await connection.query<mysql.RowDataPacket[]>(
      'SELECT id FROM working_schedule WHERE owner_id = ?',
      [ownerId]
    );
    if (schedules.length === 0) {
      console.log('Seeding working schedule...');
      await connection.query(
        `INSERT INTO working_schedule (id, working_days, working_days_per_week, working_hours_per_day, office_start_time, office_end_time, half_day_min_time, half_day_max_time, timezone, monthly_correction_limit, owner_id, status, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP(3), CURRENT_TIMESTAMP(3))`,
        [
          randomUUID(),
          JSON.stringify(['monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday']),
          6,
          8,
          '2026-01-01 09:00:00',
          '2026-01-01 17:00:00',
          4,
          6,
          'Asia/Dhaka',
          2,
          ownerId,
          'active'
        ]
      );
    }

    // 5. Ensure Leave Policy exists
    const [leavePolicies] = await connection.query<mysql.RowDataPacket[]>(
      'SELECT id FROM leave_policy WHERE owner_id = ?',
      [ownerId]
    );
    if (leavePolicies.length === 0) {
      console.log('Seeding leave policy...');
      await connection.query(
        `INSERT INTO leave_policy (id, annual_leave_days, sick_leave_days, casual_leave_days, maternity_leave_days, paternity_leave_days, personal_leave_days, compensatory_leave_days, unpaid_leave_days, owner_id, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP(3), CURRENT_TIMESTAMP(3))`,
        [
          randomUUID(),
          15,
          10,
          10,
          90,
          10,
          5,
          0,
          15,
          ownerId
        ]
      );
    }

    // Helpers to insert or return ID
    const getOrInsert = async (table: string, nameField: string, codeField: string | null, name: string, code: string | null, extraCols = '', extraVals: any[] = []) => {
      const queryStr = codeField 
        ? `SELECT id FROM \`${table}\` WHERE owner_id = ? AND \`${codeField}\` = ?`
        : `SELECT id FROM \`${table}\` WHERE owner_id = ? AND \`${nameField}\` = ?`;
      const params = codeField ? [ownerId, code] : [ownerId, name];
      const [rows] = await connection.query<mysql.RowDataPacket[]>(queryStr, params);
      if (rows.length > 0) return rows[0].id;

      const id = randomUUID();
      const insertQuery = codeField
        ? `INSERT INTO \`${table}\` (id, owner_id, \`${nameField}\`, \`${codeField}\` ${extraCols}, created_at, updated_at) VALUES (?, ?, ?, ? ${extraVals.map(() => '?').map(v => ', ' + v).join('')}, CURRENT_TIMESTAMP(3), CURRENT_TIMESTAMP(3))`
        : `INSERT INTO \`${table}\` (id, owner_id, \`${nameField}\` ${extraCols}, created_at, updated_at) VALUES (?, ?, ? ${extraVals.map(() => '?').map(v => ', ' + v).join('')}, CURRENT_TIMESTAMP(3), CURRENT_TIMESTAMP(3))`;
      
      const insertParams = codeField ? [id, ownerId, name, code, ...extraVals] : [id, ownerId, name, ...extraVals];
      await connection.query(insertQuery, insertParams);
      console.log(`Seeded ${table}: ${name}`);
      return id;
    };

    // 6. Departments
    const engDeptId = await getOrInsert('departments', 'name', 'department_code', 'Engineering', 'ENG');
    const hrDeptId = await getOrInsert('departments', 'name', 'department_code', 'Human Resources', 'HR');
    const qaDeptId = await getOrInsert('departments', 'name', 'department_code', 'Quality Assurance', 'QA');

    // 7. Designations
    const sseDesigId = await getOrInsert('designations', 'name', 'designation_code', 'Senior Software Engineer', 'SSE');
    const qaDesigId = await getOrInsert('designations', 'name', 'designation_code', 'QA Engineer', 'QAE');
    const hrmDesigId = await getOrInsert('designations', 'name', 'designation_code', 'HR Manager', 'HRM');

    // 8. Locations
    const dhkLocId = await getOrInsert('locations', 'name', 'location_code', 'Dhaka Head Office', 'DHK_HQ');
    const remoteLocId = await getOrInsert('locations', 'name', 'location_code', 'Remote', 'REMOTE');

    // 9. Employment Types
    const ftEmpId = await getOrInsert('employement_types', 'name', null, 'Full-Time', null, ', description', ['Regular full-time employee']);
    const ptEmpId = await getOrInsert('employement_types', 'name', null, 'Part-Time', null, ', description', ['Regular part-time employee']);

    // 10. Hiring Sources
    const linkedinSourceId = await getOrInsert('hiring_sources', 'name', null, 'LinkedIn', null, ', description', ['LinkedIn job board']);
    const referralSourceId = await getOrInsert('hiring_sources', 'name', null, 'Employee Referral', null, ', description', ['Referral']);

    // Re-query memberRoleId if needed
    if (!memberRoleId) {
      const [mRole] = await connection.query<mysql.RowDataPacket[]>(
        'SELECT id FROM access_roles WHERE owner_id = ? AND slug = ?',
        [ownerId, 'member']
      );
      if (mRole.length > 0) memberRoleId = mRole[0].id;
    }

    // 11. Seed members
    const hashedMemberPassword = bcrypt.hashSync('User@123456', 10);
    const membersToSeed = [
      {
        email: 'alice@wincasoft.com',
        work_email: 'alice.work@wincasoft.com',
        employee_id: 'WIN-001',
        first_name: 'Alice',
        last_name: 'Smith',
        dept_id: engDeptId,
        desig_id: sseDesigId,
        loc_id: dhkLocId,
        emp_type_id: ftEmpId,
        source_id: linkedinSourceId,
      },
      {
        email: 'bob@wincasoft.com',
        work_email: 'bob.work@wincasoft.com',
        employee_id: 'WIN-002',
        first_name: 'Bob',
        last_name: 'Jones',
        dept_id: qaDeptId,
        desig_id: qaDesigId,
        loc_id: remoteLocId,
        emp_type_id: ptEmpId,
        source_id: referralSourceId,
      },
      {
        email: 'charlie@wincasoft.com',
        work_email: 'charlie.work@wincasoft.com',
        employee_id: 'WIN-003',
        first_name: 'Charlie',
        last_name: 'HR',
        dept_id: hrDeptId,
        desig_id: hrmDesigId,
        loc_id: dhkLocId,
        emp_type_id: ftEmpId,
        source_id: linkedinSourceId,
      }
    ];

    for (const m of membersToSeed) {
      const [exist] = await connection.query<mysql.RowDataPacket[]>(
        'SELECT id FROM members WHERE owner_id = ? AND email = ?',
        [ownerId, m.email]
      );

      if (exist.length === 0) {
        console.log(`Seeding member ${m.first_name} ${m.last_name}...`);
        await connection.query(
          `INSERT INTO members (
            id, email, work_email, employee_id, image, nickname, first_name, last_name, password,
            about, expertise, work_phone, phone, father_name, mother_name,
            emergency_contact_name, emergency_contact_phone, emergency_contact_relationship,
            role, access_role_id, marital_status,
            present_address_line_one, present_address_line_two, present_address_city, present_address_country, present_address_state, present_address_zip_code,
            permanent_address_line_one, permanent_address_line_two, permanent_address_city, permanent_address_country, permanent_address_state, permanent_address_zip_code,
            gender, date_of_birth, date_of_joining,
            department_id, designation_id, location_id, employment_type_id, hiring_source_id,
            reporting_manager_id, status, owner_id, created_at, updated_at
          ) VALUES (
            ?, ?, ?, ?, ?, ?, ?, ?, ?,
            ?, ?, ?, ?, ?, ?,
            ?, ?, ?,
            ?, ?, ?,
            ?, ?, ?, ?, ?, ?,
            ?, ?, ?, ?, ?, ?,
            ?, ?, CURRENT_TIMESTAMP(3),
            ?, ?, ?, ?, ?,
            NULL, 'active', ?, CURRENT_TIMESTAMP(3), CURRENT_TIMESTAMP(3)
          )`,
          [
            randomUUID(),
            m.email,
            m.work_email,
            m.employee_id,
            defaultImage,
            m.first_name,
            m.first_name,
            m.last_name,
            hashedMemberPassword,
            'Staff member',
            'IT',
            '+88011111111',
            '+88022222222',
            'Father Name',
            'Mother Name',
            'Emergency Name',
            '+88033333333',
            'Friend',
            'member',
            memberRoleId || null,
            'single',
            'Address 1',
            'Address 2',
            'Dhaka',
            'Bangladesh',
            'Dhaka',
            '1200',
            'Address 1',
            'Address 2',
            'Dhaka',
            'Bangladesh',
            'Dhaka',
            '1200',
            'male',
            new Date('1995-01-01'),
            m.dept_id,
            m.desig_id,
            m.loc_id,
            m.emp_type_id,
            m.source_id,
            ownerId
          ]
        );
      }
    }

    console.log('Database seeding successfully completed.');
  } finally {
    await connection.end();
  }
}

main().catch((err) => {
  console.error('Seeding error:', err);
  process.exit(1);
});
