const pool = require("./database");

async function initProductionTables() {
  try {
    // 1. Permanent Employees
    await pool.query(`
      CREATE TABLE IF NOT EXISTS production_employees (
        id VARCHAR(50) PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        role VARCHAR(100) DEFAULT 'Operator',
        department VARCHAR(100) DEFAULT 'Digital Printing',
        shift VARCHAR(50) DEFAULT 'Day',
        mobile VARCHAR(50)
      );
    `);

    // 2. Outsourced Workers
    await pool.query(`
      CREATE TABLE IF NOT EXISTS production_outsourced (
        id VARCHAR(50) PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        agency VARCHAR(255) DEFAULT 'Apex Labor Solutions',
        task VARCHAR(255) DEFAULT 'General Production',
        shift VARCHAR(50) DEFAULT 'Day'
      );
    `);

    // 3. Production Machines
    await pool.query(`
      CREATE TABLE IF NOT EXISTS production_machines (
        id VARCHAR(50) PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        status VARCHAR(50) DEFAULT 'Running',
        speed VARCHAR(100) DEFAULT '100m/min',
        last_maintenance DATE
      );
    `);

    // 4. Production Logs
    await pool.query(`
      CREATE TABLE IF NOT EXISTS production_logs (
        id SERIAL PRIMARY KEY,
        machine_id VARCHAR(50) REFERENCES production_machines(id) ON DELETE CASCADE,
        log_date DATE NOT NULL,
        worker_type VARCHAR(50),
        worker_id VARCHAR(50),
        processed_qty NUMERIC(10, 2) DEFAULT 0,
        order_ref VARCHAR(100)
      );
    `);

    console.log("Production management tables created/verified successfully.");

    // Seed sample data if empty
    await pool.query(`
      INSERT INTO production_employees (id, name, role, department, shift, mobile) VALUES
      ('EMP001', 'Arun Kumar', 'Senior Printer', 'Digital Printing', 'Day', '9876543210'),
      ('EMP002', 'Priya Dharshini', 'QC Specialist', 'Quality Control', 'Day', '9876543211'),
      ('EMP003', 'Karthik Raja', 'Machine Operator', 'Digital Printing', 'Night', '9876543212'),
      ('EMP004', 'Anitha Ramesh', 'Dyeing Specialist', 'Chemical Dyeing', 'Day', '9876543213'),
      ('EMP005', 'Rajesh Kannan', 'Stenter Operator', 'Stenter & Drying', 'Night', '9876543214'),
      ('EMP006', 'Suresh Kumar', 'Operator', 'Calendering Press', 'Day', '9876543215')
      ON CONFLICT (id) DO NOTHING;
    `);
    console.log("Sample permanent employees seeded.");

    await pool.query(`
      INSERT INTO production_outsourced (id, name, agency, task, shift) VALUES
      ('OUT-001', 'Manoj S', 'Apex Labor Solutions', 'Yarn Sorting & Feeding', 'Day'),
      ('OUT-002', 'Vignesh M', 'Prime Staffing', 'Dye Mixing Assistant', 'Night'),
      ('OUT-003', 'Murugan K', 'Apex Labor Solutions', 'Fabric Press Helper', 'Day'),
      ('OUT-004', 'Lakshmi Narayanan', 'Global Workforce', 'Fabric Inspection', 'Day')
      ON CONFLICT (id) DO NOTHING;
    `);
    console.log("Sample outsourced workers seeded.");

    await pool.query(`
      INSERT INTO production_machines (id, name, status, speed, last_maintenance) VALUES
      ('MAC-01', 'Kornit Allegro Digital Printer', 'Running', '150m/min', '2026-06-15'),
      ('MAC-02', 'Reggiani Renoir Superfast', 'Maintenance', '200m/min', '2026-07-01'),
      ('MAC-03', 'Mimaki Tiger-1800B', 'Running', '120m/min', '2026-05-20'),
      ('MAC-04', 'Zimmer Colaris Digital Printer', 'Running', '180m/min', '2026-06-30')
      ON CONFLICT (id) DO NOTHING;
    `);
    console.log("Sample production machines seeded.");

    const logsCount = await pool.query("SELECT COUNT(*) FROM production_logs");
    if (parseInt(logsCount.rows[0].count) === 0) {
      const todayStr = new Date().toISOString().split("T")[0];
      await pool.query(`
        INSERT INTO production_logs (machine_id, log_date, worker_type, worker_id, processed_qty, order_ref) VALUES
        ('MAC-01', '${todayStr}', 'permanent', 'EMP001', 450, 'ORD-2026-881'),
        ('MAC-01', '${todayStr}', 'outsourced', 'OUT-001', 320, 'ORD-2026-882'),
        ('MAC-02', '${todayStr}', 'permanent', 'EMP003', 600, 'ORD-2026-790'),
        ('MAC-03', '${todayStr}', 'permanent', 'EMP002', 280, 'ORD-2026-905'),
        ('MAC-03', '${todayStr}', 'outsourced', 'OUT-002', 410, 'ORD-2026-910'),
        ('MAC-04', '${todayStr}', 'permanent', 'EMP004', 530, 'ORD-2026-944'),
        ('MAC-04', '${todayStr}', 'outsourced', 'OUT-003', 390, 'ORD-2026-950');
      `);
      console.log("Sample production history logs seeded.");
    }

  } catch (err) {
    console.error("Error setting up production database tables:", err);
  } finally {
    await pool.end();
  }
}

initProductionTables();
