<?php
/**
 * Demo / test data loader for local testing.
 * Run: php scripts/seed_demo_data.php
 *
 * Creates 3 users (boss, hrm, attendance) + enough sample rows across modules.
 * Safe to re-run: skips if demo marker user "boss" already exists.
 */

declare(strict_types=1);

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$host = '127.0.0.1';
$user = 'root';
$pass = '';
$db   = 'erp_foundation';

// Optional: read from project .env next to this script's parent
$envFile = dirname(__DIR__) . DIRECTORY_SEPARATOR . '.env';
if (is_file($envFile)) {
    foreach (file($envFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) as $line) {
        $line = trim($line);
        if ($line === '' || $line[0] === '#' || strpos($line, '=') === false) {
            continue;
        }
        list($k, $v) = array_map('trim', explode('=', $line, 2));
        $v = trim($v, " \t\"'");
        if ($k === 'database.default.hostname') {
            $host = $v;
        } elseif ($k === 'database.default.username') {
            $user = $v;
        } elseif ($k === 'database.default.password') {
            $pass = $v;
        } elseif ($k === 'database.default.database') {
            $db = $v;
        }
    }
}

$m = new mysqli($host, $user, $pass, $db);
$m->set_charset('utf8mb4');

$now  = date('Y-m-d H:i:s');
$today = date('Y-m-d');
$year  = (int) date('Y');
$password = 'Demo@1234';
$hash = password_hash($password, PASSWORD_BCRYPT, ['cost' => 12]);

function uuid(): string
{
    $data = random_bytes(16);
    $data[6] = chr((ord($data[6]) & 0x0f) | 0x40);
    $data[8] = chr((ord($data[8]) & 0x3f) | 0x80);
    return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4));
}

function id(mysqli $m): int
{
    return (int) $m->insert_id;
}

function one(mysqli $m, string $sql)
{
    $r = $m->query($sql);
    return $r ? $r->fetch_assoc() : null;
}

function exists(mysqli $m, string $table, string $where): bool
{
    $r = $m->query("SELECT 1 FROM `{$table}` WHERE {$where} LIMIT 1");
    return $r && $r->num_rows > 0;
}

function insert(mysqli $m, string $table, array $data): int
{
    $cols = [];
    $vals = [];
    foreach ($data as $k => $v) {
        $cols[] = "`{$k}`";
        if ($v === null) {
            $vals[] = 'NULL';
        } else {
            $vals[] = "'" . $m->real_escape_string((string) $v) . "'";
        }
    }
    $m->query('INSERT INTO `' . $table . '` (' . implode(',', $cols) . ') VALUES (' . implode(',', $vals) . ')');
    return id($m);
}

echo "Seeding demo data into {$db}...\n";

if (exists($m, 'users', "username = 'boss' AND deleted_at IS NULL")) {
    echo "Demo users already exist (boss found). Skipping to avoid duplicates.\n";
    echo "Delete users boss/hrm/attendance (and related EMPDEMO* employees) to re-seed.\n";
    exit(0);
}

$admin = one($m, 'SELECT id FROM users WHERE username = \'admin\' AND deleted_at IS NULL LIMIT 1');
$adminId = $admin ? (int) $admin['id'] : 1;
$branch = one($m, "SELECT id FROM branches WHERE code = 'HQ' LIMIT 1");
$branchId = $branch ? (int) $branch['id'] : 1;

// ---------------------------------------------------------------------------
// Departments
// ---------------------------------------------------------------------------
$deptIds = [];
$depts = [
    'Administration' => null,
    'Human Resources' => null,
    'Operations' => null,
    'Sales' => null,
    'Finance' => null,
    'Warehouse' => null,
];
foreach ($depts as $name => $_) {
    $row = one($m, "SELECT id FROM departments WHERE name = '" . $m->real_escape_string($name) . "' LIMIT 1");
    if ($row) {
        $deptIds[$name] = (int) $row['id'];
    } else {
        $deptIds[$name] = insert($m, 'departments', [
            'branch_id'  => $branchId,
            'name'       => $name,
            'status'     => 'active',
            'created_at' => $now,
            'updated_at' => $now,
        ]);
    }
}

// ---------------------------------------------------------------------------
// Roles + permissions
// ---------------------------------------------------------------------------
function ensureRole(mysqli $m, string $slug, string $name, string $desc, string $now): int
{
    $row = one($m, "SELECT id FROM roles WHERE slug = '" . $m->real_escape_string($slug) . "' LIMIT 1");
    if ($row) {
        return (int) $row['id'];
    }
    return insert($m, 'roles', [
        'name'        => $name,
        'slug'        => $slug,
        'description' => $desc,
        'is_system'   => 0,
        'created_at'  => $now,
        'updated_at'  => $now,
    ]);
}

function grantAll(mysqli $m, int $roleId, string $now): void
{
    $m->query(
        "INSERT INTO role_permissions (role_id, permission_id, created_at)
         SELECT {$roleId}, p.id, '{$now}'
         FROM permissions p
         WHERE NOT EXISTS (
             SELECT 1 FROM role_permissions rp
             WHERE rp.role_id = {$roleId} AND rp.permission_id = p.id
         )"
    );
}

function grantGroups(mysqli $m, int $roleId, array $groups, string $now): void
{
    $in = "'" . implode("','", array_map(function ($g) use ($m) {
        return $m->real_escape_string($g);
    }, $groups)) . "'";
    $m->query(
        "INSERT INTO role_permissions (role_id, permission_id, created_at)
         SELECT {$roleId}, p.id, '{$now}'
         FROM permissions p
         WHERE p.`group` IN ({$in})
           AND NOT EXISTS (
             SELECT 1 FROM role_permissions rp
             WHERE rp.role_id = {$roleId} AND rp.permission_id = p.id
           )"
    );
}

function grantSlugs(mysqli $m, int $roleId, array $slugs, string $now): void
{
    $in = "'" . implode("','", array_map(function ($s) use ($m) {
        return $m->real_escape_string($s);
    }, $slugs)) . "'";
    $m->query(
        "INSERT INTO role_permissions (role_id, permission_id, created_at)
         SELECT {$roleId}, p.id, '{$now}'
         FROM permissions p
         WHERE p.slug IN ({$in})
           AND NOT EXISTS (
             SELECT 1 FROM role_permissions rp
             WHERE rp.role_id = {$roleId} AND rp.permission_id = p.id
           )"
    );
}

$bossRoleId = ensureRole($m, 'boss', 'Boss / Managing Director', 'Full business access for testing (like admin).', $now);
$hrmRoleId  = ensureRole($m, 'hrm_manager', 'HR Manager', 'HRM modules: employees, leave, attendance, payroll, assets, documents.', $now);
$attRoleId  = ensureRole($m, 'attendance_clerk', 'Attendance Clerk', 'Attendance viewing and clocking only.', $now);

grantAll($m, $bossRoleId, $now);

$hrmGroups = [
    'dashboard', 'departments', 'employees',
    'leave_types', 'leave_balances', 'leave_requests', 'leave_calendar', 'holidays',
    'attendance', 'attendance_corrections', 'attendance_shifts',
    'payroll_settings', 'payroll_salary', 'payroll_runs', 'payroll_payslips', 'payroll_reports', 'payroll_settlements',
    'document_types', 'employee_documents', 'asset_types', 'employee_assets',
    'scheduling', 'messages', 'company',
];
grantGroups($m, $hrmRoleId, $hrmGroups, $now);

grantSlugs($m, $attRoleId, [
    'dashboard.view',
    'attendance.view', 'attendance.clock', 'attendance.manual',
    'attendance_corrections.view', 'attendance_corrections.create',
    'messages.view', 'messages.send',
], $now);

// ---------------------------------------------------------------------------
// Users
// ---------------------------------------------------------------------------
function createUser(mysqli $m, array $u, string $hash, string $now): int
{
    return insert($m, 'users', [
        'uuid'                 => uuid(),
        'first_name'           => $u['first_name'],
        'last_name'            => $u['last_name'],
        'username'             => $u['username'],
        'email'                => $u['email'],
        'phone'                => $u['phone'] ?? null,
        'password_hash'        => $hash,
        'password_changed_at'  => $now,
        'must_change_password' => 0,
        'role_id'              => $u['role_id'],
        'branch_id'            => $u['branch_id'],
        'department_id'        => $u['department_id'],
        'status'               => 'active',
        'email_verified_at'    => $now,
        'created_at'           => $now,
        'updated_at'           => $now,
    ]);
}

$bossUserId = createUser($m, [
    'first_name' => 'Khalid', 'last_name' => 'Al-Mahmood',
    'username' => 'boss', 'email' => 'boss@demo.local', 'phone' => '+97336000001',
    'role_id' => $bossRoleId, 'branch_id' => $branchId, 'department_id' => $deptIds['Administration'],
], $hash, $now);

$hrmUserId = createUser($m, [
    'first_name' => 'Fatima', 'last_name' => 'Hassan',
    'username' => 'hrm', 'email' => 'hrm@demo.local', 'phone' => '+97336000002',
    'role_id' => $hrmRoleId, 'branch_id' => $branchId, 'department_id' => $deptIds['Human Resources'],
], $hash, $now);

$attUserId = createUser($m, [
    'first_name' => 'Yusuf', 'last_name' => 'Ali',
    'username' => 'attendance', 'email' => 'attendance@demo.local', 'phone' => '+97336000003',
    'role_id' => $attRoleId, 'branch_id' => $branchId, 'department_id' => $deptIds['Operations'],
], $hash, $now);

echo "Users: boss / hrm / attendance (password: {$password})\n";

// ---------------------------------------------------------------------------
// Employees
// ---------------------------------------------------------------------------
$employees = [
    ['EMPDEMO01', 'Khalid Al-Mahmood', 'Administration', 'Managing Director', 'boss@demo.local', $bossUserId, 'male', '1980-03-12', 2200, 300, 400, 100],
    ['EMPDEMO02', 'Fatima Hassan', 'Human Resources', 'HR Manager', 'hrm@demo.local', $hrmUserId, 'female', '1988-07-21', 950, 100, 200, 50],
    ['EMPDEMO03', 'Yusuf Ali', 'Operations', 'Attendance Clerk', 'attendance@demo.local', $attUserId, 'male', '1995-11-02', 450, 50, 80, 40],
    ['EMPDEMO04', 'Ahmed Saleh', 'Operations', 'Field Supervisor', 'ahmed.saleh@demo.local', null, 'male', '1990-01-15', 650, 80, 120, 50],
    ['EMPDEMO05', 'Noor Ibrahim', 'Sales', 'Sales Executive', 'noor.ibrahim@demo.local', null, 'female', '1993-05-08', 700, 80, 150, 60],
    ['EMPDEMO06', 'Hassan Juma', 'Warehouse', 'Store Keeper', 'hassan.juma@demo.local', null, 'male', '1987-09-30', 500, 50, 100, 40],
    ['EMPDEMO07', 'Maryam Faisal', 'Finance', 'Accountant', 'maryam.faisal@demo.local', null, 'female', '1991-12-18', 800, 100, 150, 50],
    ['EMPDEMO08', 'Omar Khalifa', 'Operations', 'Technician', 'omar.khalifa@demo.local', null, 'male', '1996-04-04', 480, 50, 80, 40],
];

$empIds = [];
$shift = one($m, "SELECT id FROM attendance_shifts WHERE code = 'DAY' LIMIT 1");
$shiftId = $shift ? (int) $shift['id'] : 1;
$leaveTypes = [];
$lt = $m->query('SELECT id, code, days_per_year FROM leave_types');
while ($row = $lt->fetch_assoc()) {
    $leaveTypes[$row['code']] = $row;
}

foreach ($employees as $i => $e) {
    $empId = insert($m, 'employees', [
        'employee_number' => $e[0],
        'full_name'       => $e[1],
        'department_id'   => $deptIds[$e[2]],
        'branch_id'       => $branchId,
        'job_title'       => $e[3],
        'email'           => $e[4],
        'phone'           => '+9733611000' . ($i + 1),
        'mobile'          => '+9733612000' . ($i + 1),
        'cpr_number'      => '89010' . str_pad((string) ($i + 1), 4, '0', STR_PAD_LEFT),
        'nationality'     => 'Bahraini',
        'date_of_birth'   => $e[7],
        'gender'          => $e[6],
        'marital_status'  => $i % 2 ? 'married' : 'single',
        'address'         => 'Building ' . (10 + $i) . ', Road 42, Block 3',
        'city'            => 'Manama',
        'country'         => 'Bahrain',
        'emergency_contact_name'  => 'Family Contact',
        'emergency_contact_phone' => '+9733600990' . $i,
        'hire_date'       => date('Y-m-d', strtotime('-' . (18 + $i * 3) . ' months')),
        'employment_type' => 'full_time',
        'bank_name'       => 'NBB',
        'bank_account'    => 'BH00DEMO0000000' . ($i + 1),
        'user_id'         => $e[5],
        'status'          => 'active',
        'created_by'      => $adminId,
        'created_at'      => $now,
        'updated_at'      => $now,
    ]);
    $empIds[$e[0]] = $empId;

    insert($m, 'employee_shift_assignments', [
        'employee_id'    => $empId,
        'shift_id'       => $shiftId,
        'effective_from' => date('Y-m-d', strtotime('-6 months')),
        'created_by'     => $hrmUserId,
        'created_at'     => $now,
        'updated_at'     => $now,
    ]);

    foreach ($leaveTypes as $code => $ltRow) {
        $entitled = (float) $ltRow['days_per_year'];
        $used = $code === 'AL' ? 2.0 : ($code === 'SL' ? 1.0 : 0.0);
        insert($m, 'leave_balances', [
            'employee_id'   => $empId,
            'leave_type_id' => (int) $ltRow['id'],
            'year'          => $year,
            'entitled'      => $entitled,
            'used'          => $used,
            'created_at'    => $now,
            'updated_at'    => $now,
        ]);
    }

    insert($m, 'employee_salary_structures', [
        'employee_id'         => $empId,
        'effective_from'      => date('Y-m-d', strtotime('-12 months')),
        'basic_salary'        => $e[8],
        'social_allowance'    => $e[9],
        'housing_allowance'   => $e[10],
        'transport_allowance' => $e[11],
        'other_allowances'    => 0,
        'payment_method'      => 'bank',
        'created_by'          => $hrmUserId,
        'created_at'          => $now,
        'updated_at'          => $now,
    ]);
}

// Leave requests
$alId = (int) $leaveTypes['AL']['id'];
$slId = (int) $leaveTypes['SL']['id'];
insert($m, 'leave_requests', [
    'employee_id' => $empIds['EMPDEMO04'], 'leave_type_id' => $alId,
    'start_date' => date('Y-m-d', strtotime('+5 days')), 'end_date' => date('Y-m-d', strtotime('+7 days')),
    'days' => 3, 'reason' => 'Family trip', 'status' => 'pending',
    'created_by' => $hrmUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'leave_requests', [
    'employee_id' => $empIds['EMPDEMO05'], 'leave_type_id' => $slId,
    'start_date' => date('Y-m-d', strtotime('-10 days')), 'end_date' => date('Y-m-d', strtotime('-9 days')),
    'days' => 2, 'reason' => 'Flu', 'status' => 'approved',
    'approved_by' => $hrmUserId, 'approved_at' => $now,
    'created_by' => $hrmUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'leave_requests', [
    'employee_id' => $empIds['EMPDEMO08'], 'leave_type_id' => $alId,
    'start_date' => date('Y-m-d', strtotime('-20 days')), 'end_date' => date('Y-m-d', strtotime('-18 days')),
    'days' => 3, 'reason' => 'Personal', 'status' => 'rejected',
    'approved_by' => $hrmUserId, 'approved_at' => $now, 'rejection_reason' => 'Busy season — please rebook',
    'created_by' => $hrmUserId, 'created_at' => $now, 'updated_at' => $now,
]);

insert($m, 'holidays', [
    'name' => 'Labour Day', 'date' => $year . '-05-01', 'is_recurring' => 1,
    'description' => 'Public holiday', 'created_by' => $hrmUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'holidays', [
    'name' => 'National Day', 'date' => $year . '-12-16', 'is_recurring' => 1,
    'description' => 'Bahrain National Day', 'created_by' => $hrmUserId, 'created_at' => $now, 'updated_at' => $now,
]);

// Attendance last 10 weekdays for a few employees
$attEmployees = ['EMPDEMO03', 'EMPDEMO04', 'EMPDEMO05', 'EMPDEMO06', 'EMPDEMO08'];
$d = new DateTime('today');
$daysAdded = 0;
while ($daysAdded < 10) {
    $d->modify('-1 day');
    $n = (int) $d->format('N');
    if ($n >= 6) {
        continue; // skip Fri/Sat-ish weekend approx (Fri=5 Sat=6) — use Sat/Sun
    }
    if ($n === 5 || $n === 6) {
        continue;
    }
    $date = $d->format('Y-m-d');
    foreach ($attEmployees as $idx => $eno) {
        $late = ($idx + $daysAdded) % 4 === 0;
        $absent = ($eno === 'EMPDEMO08' && $daysAdded === 2);
        if ($absent) {
            insert($m, 'attendance_records', [
                'employee_id' => $empIds[$eno], 'shift_id' => $shiftId, 'attendance_date' => $date,
                'status' => 'absent', 'source' => 'manual', 'created_by' => $attUserId,
                'created_at' => $now, 'updated_at' => $now,
            ]);
            continue;
        }
        $inH = $late ? '08:22:00' : '07:55:00';
        $outH = '17:05:00';
        insert($m, 'attendance_records', [
            'employee_id' => $empIds[$eno], 'shift_id' => $shiftId, 'attendance_date' => $date,
            'clock_in' => $date . ' ' . $inH, 'clock_out' => $date . ' ' . $outH,
            'status' => $late ? 'late' : 'present',
            'late_minutes' => $late ? 22 : 0,
            'work_minutes' => 480,
            'source' => 'clock',
            'created_by' => $attUserId,
            'created_at' => $now, 'updated_at' => $now,
        ]);
    }
    $daysAdded++;
}

// Assets
$lap = one($m, "SELECT id FROM asset_types WHERE code = 'LAP' LIMIT 1");
$mob = one($m, "SELECT id FROM asset_types WHERE code = 'MOB' LIMIT 1");
if ($lap) {
    insert($m, 'employee_asset_assignments', [
        'employee_id' => $empIds['EMPDEMO02'], 'asset_type_id' => (int) $lap['id'],
        'asset_name' => 'Dell Latitude 5540', 'identifier' => 'LAP-HR-001',
        'quantity' => 1, 'assigned_date' => date('Y-m-d', strtotime('-90 days')),
        'status' => 'assigned', 'condition_on_assign' => 'Good',
        'assigned_by' => $hrmUserId, 'created_by' => $hrmUserId, 'created_at' => $now, 'updated_at' => $now,
    ]);
}
if ($mob) {
    insert($m, 'employee_asset_assignments', [
        'employee_id' => $empIds['EMPDEMO05'], 'asset_type_id' => (int) $mob['id'],
        'asset_name' => 'iPhone 14', 'identifier' => 'MOB-SAL-002',
        'quantity' => 1, 'assigned_date' => date('Y-m-d', strtotime('-60 days')),
        'status' => 'assigned', 'condition_on_assign' => 'New',
        'assigned_by' => $hrmUserId, 'created_by' => $hrmUserId, 'created_at' => $now, 'updated_at' => $now,
    ]);
}

// Payroll run (draft)
$runId = insert($m, 'payroll_runs', [
    'period_year' => $year, 'period_month' => (int) date('n', strtotime('first day of last month')),
    'title' => 'Demo payroll — previous month', 'status' => 'calculated',
    'run_date' => date('Y-m-d', strtotime('first day of this month')),
    'notes' => 'Sample calculated run for testing payslips UI',
    'created_by' => $hrmUserId, 'created_at' => $now, 'updated_at' => $now,
]);
foreach (['EMPDEMO02', 'EMPDEMO03', 'EMPDEMO04', 'EMPDEMO05'] as $eno) {
    $basic = 0;
    foreach ($employees as $e) {
        if ($e[0] === $eno) {
            $basic = $e[8] + $e[9] + $e[10] + $e[11];
            break;
        }
    }
    $sio = round($basic * 0.08, 3);
    insert($m, 'payroll_items', [
        'payroll_run_id' => $runId, 'employee_id' => $empIds[$eno],
        'days_worked' => 26, 'days_absent' => 0, 'unpaid_leave_days' => 0,
        'gross_earnings' => $basic, 'total_deductions' => $sio,
        'net_pay' => $basic - $sio, 'employer_sio' => round($basic * 0.18, 3),
        'employer_eosb' => round($basic * 0.042, 3),
        'employer_total_cost' => $basic + round($basic * 0.18, 3) + round($basic * 0.042, 3),
        'status' => 'draft', 'created_at' => $now, 'updated_at' => $now,
    ]);
}

echo "HRM: employees, leave, attendance, assets, payroll seeded\n";

// ---------------------------------------------------------------------------
// Sales / CRM
// ---------------------------------------------------------------------------
$siteId = insert($m, 'customer_sites', [
    'name' => 'Bahrain City Centre', 'code' => 'BCC', 'site_type' => 'mall',
    'address' => 'Road 4608', 'city' => 'Manama', 'country' => 'Bahrain',
    'status' => 'active', 'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);

$companies = [
    ['GULFTECH', 'Gulf Tech Trading', 'VIP', 'product', 'info@gulftech.demo', '+97317001111', 'Technology'],
    ['CLEANPRO', 'CleanPro Facilities', 'STD', 'service', 'ops@cleanpro.demo', '+97317002222', 'Facilities'],
    ['MARINA', 'Marina Retail WLL', 'PARTNER', 'project', 'sales@marina.demo', '+97317003333', 'Retail'],
];
$companyIds = [];
$groupMap = [];
$gr = $m->query('SELECT id, code FROM customer_groups');
while ($row = $gr->fetch_assoc()) {
    $groupMap[$row['code']] = (int) $row['id'];
}

foreach ($companies as $c) {
    $cid = insert($m, 'companies', [
        'name' => $c[1], 'code' => $c[0], 'email' => $c[4], 'phone' => $c[5],
        'industry' => $c[6], 'customer_group_id' => $groupMap[$c[2]] ?? null,
        'customer_type' => $c[3], 'site_id' => $siteId,
        'address' => 'Office Park, Seef', 'city' => 'Manama', 'country' => 'Bahrain',
        'status' => 'active', 'notes' => 'Demo customer',
        'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
    ]);
    $companyIds[$c[0]] = $cid;
    insert($m, 'contacts', [
        'company_id' => $cid, 'first_name' => 'Primary', 'last_name' => 'Contact',
        'email' => 'contact@' . strtolower($c[0]) . '.demo', 'phone' => $c[5],
        'job_title' => 'Procurement Manager', 'is_primary' => 1, 'status' => 'active',
        'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
    ]);
    insert($m, 'customer_history', [
        'company_id' => $cid, 'event_type' => 'note', 'title' => 'Account opened',
        'description' => 'Demo account created for testing', 'event_at' => $now,
        'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
    ]);
}

$web = one($m, "SELECT id FROM lead_sources WHERE code = 'WEB' LIMIT 1");
$ref = one($m, "SELECT id FROM lead_sources WHERE code = 'REF' LIMIT 1");
insert($m, 'leads', [
    'first_name' => 'Sara', 'last_name' => 'Abdulla', 'company_name' => 'Bright Offices',
    'email' => 'sara@bright.demo', 'phone' => '+97339001111', 'job_title' => 'Facilities Head',
    'lead_source_id' => $web ? (int) $web['id'] : null, 'assigned_to' => $bossUserId,
    'score' => 45, 'status' => 'new', 'priority' => 'high', 'estimated_value' => 8500,
    'notes' => 'Interested in cleaning contract', 'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'leads', [
    'first_name' => 'Rashid', 'last_name' => 'Nasser', 'company_name' => 'Island Cafe',
    'email' => 'rashid@island.demo', 'phone' => '+97339002222',
    'lead_source_id' => $ref ? (int) $ref['id'] : null, 'assigned_to' => $bossUserId,
    'score' => 30, 'status' => 'contacted', 'priority' => 'medium', 'estimated_value' => 3200,
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'leads', [
    'first_name' => 'Layla', 'last_name' => 'Hameed', 'company_name' => 'Desert Logistics',
    'email' => 'layla@desert.demo', 'phone' => '+97339003333',
    'lead_source_id' => $web ? (int) $web['id'] : null, 'assigned_to' => $bossUserId,
    'score' => 70, 'status' => 'qualified', 'priority' => 'high', 'estimated_value' => 15000,
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);

$prop = one($m, "SELECT id, default_probability FROM sales_stages WHERE code = 'PROP' LIMIT 1");
$nego = one($m, "SELECT id, default_probability FROM sales_stages WHERE code = 'NEGO' LIMIT 1");
$qual = one($m, "SELECT id, default_probability FROM sales_stages WHERE code = 'QUAL' LIMIT 1");
$contact1 = one($m, 'SELECT id FROM contacts WHERE company_id = ' . (int) $companyIds['GULFTECH'] . ' LIMIT 1');

$opp1 = insert($m, 'opportunities', [
    'name' => 'Gulf Tech annual IT support', 'company_id' => $companyIds['GULFTECH'],
    'contact_id' => $contact1 ? (int) $contact1['id'] : null,
    'sales_stage_id' => $nego ? (int) $nego['id'] : null,
    'expected_revenue' => 24000, 'probability' => $nego ? (int) $nego['default_probability'] : 75,
    'expected_close_date' => date('Y-m-d', strtotime('+20 days')), 'status' => 'open',
    'description' => '12-month support retainer', 'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'opportunities', [
    'name' => 'CleanPro mall night shift', 'company_id' => $companyIds['CLEANPRO'],
    'sales_stage_id' => $prop ? (int) $prop['id'] : null,
    'expected_revenue' => 18000, 'probability' => $prop ? (int) $prop['default_probability'] : 50,
    'expected_close_date' => date('Y-m-d', strtotime('+35 days')), 'status' => 'open',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'opportunities', [
    'name' => 'Marina fit-out project', 'company_id' => $companyIds['MARINA'],
    'sales_stage_id' => $qual ? (int) $qual['id'] : null,
    'expected_revenue' => 45000, 'probability' => 10, 'status' => 'open',
    'expected_close_date' => date('Y-m-d', strtotime('+60 days')),
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);

insert($m, 'sales_activities', [
    'activity_type' => 'call', 'subject' => 'Follow up with Gulf Tech',
    'opportunity_id' => $opp1, 'company_id' => $companyIds['GULFTECH'],
    'status' => 'planned', 'priority' => 'high',
    'scheduled_at' => date('Y-m-d H:i:s', strtotime('+1 day 10:00')),
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'sales_activities', [
    'activity_type' => 'meeting', 'subject' => 'Site walkthrough — CleanPro',
    'company_id' => $companyIds['CLEANPRO'], 'status' => 'completed', 'priority' => 'normal',
    'scheduled_at' => date('Y-m-d H:i:s', strtotime('-3 days 14:00')),
    'completed_at' => date('Y-m-d H:i:s', strtotime('-3 days 15:30')),
    'duration_minutes' => 90, 'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);

$plId = insert($m, 'price_lists', [
    'name' => 'Standard BHD', 'code' => 'STD-BHD', 'currency' => 'BHD', 'status' => 'active',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'price_list_items', [
    'price_list_id' => $plId, 'item_code' => 'SVC-CLEAN', 'item_name' => 'Cleaning Service (month)',
    'unit' => 'month', 'unit_price' => 850, 'sort_order' => 1, 'created_at' => $now,
]);
insert($m, 'price_list_items', [
    'price_list_id' => $plId, 'item_code' => 'SVC-IT', 'item_name' => 'IT Support Retainer',
    'unit' => 'month', 'unit_price' => 1200, 'sort_order' => 2, 'created_at' => $now,
]);

$qtId = insert($m, 'quotations', [
    'number' => 'QT-DEMO-001', 'company_id' => $companyIds['GULFTECH'],
    'contact_id' => $contact1 ? (int) $contact1['id'] : null, 'opportunity_id' => $opp1,
    'price_list_id' => $plId, 'quotation_date' => $today,
    'valid_until' => date('Y-m-d', strtotime('+30 days')),
    'status' => 'sent', 'subtotal' => 14400, 'tax_amount' => 0, 'total' => 14400,
    'notes' => '12 months IT support', 'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'quotation_lines', [
    'quotation_id' => $qtId, 'item_code' => 'SVC-IT', 'item_name' => 'IT Support Retainer',
    'quantity' => 12, 'unit' => 'month', 'unit_price' => 1200, 'line_total' => 14400,
    'sort_order' => 1, 'created_at' => $now,
]);

$soId = insert($m, 'sales_orders', [
    'number' => 'SO-DEMO-001', 'quotation_id' => $qtId, 'company_id' => $companyIds['CLEANPRO'],
    'order_date' => date('Y-m-d', strtotime('-15 days')),
    'required_date' => date('Y-m-d', strtotime('+15 days')),
    'status' => 'confirmed', 'subtotal' => 2550, 'tax_amount' => 0, 'total' => 2550,
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'sales_order_lines', [
    'sales_order_id' => $soId, 'item_code' => 'SVC-CLEAN', 'item_name' => 'Cleaning Service (month)',
    'quantity' => 3, 'unit' => 'month', 'unit_price' => 850, 'line_total' => 2550,
    'sort_order' => 1, 'created_at' => $now,
]);

$invId = insert($m, 'invoices', [
    'number' => 'INV-DEMO-001', 'company_id' => $companyIds['CLEANPRO'], 'sales_order_id' => $soId,
    'invoice_date' => date('Y-m-d', strtotime('-10 days')),
    'due_date' => date('Y-m-d', strtotime('+20 days')),
    'status' => 'issued', 'subtotal' => 850, 'tax_amount' => 0, 'total' => 850, 'amount_paid' => 0,
    'issued_at' => $now, 'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'invoice_lines', [
    'invoice_id' => $invId, 'item_code' => 'SVC-CLEAN', 'item_name' => 'Cleaning Service (month)',
    'quantity' => 1, 'unit' => 'month', 'unit_price' => 850, 'line_total' => 850,
    'sort_order' => 1, 'created_at' => $now,
]);

$terrId = insert($m, 'sales_territories', [
    'name' => 'Capital Governorate', 'code' => 'CAP', 'region' => 'Bahrain', 'status' => 'active',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
$repId = insert($m, 'sales_representatives', [
    'name' => 'Noor Ibrahim', 'code' => 'REP-NOOR', 'employee_id' => $empIds['EMPDEMO05'],
    'territory_id' => $terrId, 'email' => 'noor.ibrahim@demo.local', 'status' => 'active',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'sales_targets', [
    'sales_rep_id' => $repId, 'territory_id' => $terrId,
    'period_start' => date('Y-m-01'), 'period_end' => date('Y-m-t'),
    'target_amount' => 20000, 'achieved_amount' => 4500, 'status' => 'active',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);

// Contract
$ctId = insert($m, 'contracts', [
    'number' => 'CT-DEMO-001', 'title' => 'CleanPro Facilities — Night Shift',
    'company_id' => $companyIds['CLEANPRO'], 'contract_type' => 'service', 'status' => 'active',
    'start_date' => date('Y-m-d', strtotime('-2 months')),
    'end_date' => date('Y-m-d', strtotime('+10 months')),
    'value' => 10200, 'location' => 'Bahrain City Centre',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'contract_lines', [
    'contract_id' => $ctId, 'item_code' => 'SVC-CLEAN', 'item_name' => 'Night cleaning',
    'quantity' => 12, 'unit' => 'month', 'unit_price' => 850, 'line_total' => 10200,
    'sort_order' => 1, 'created_at' => $now,
]);
insert($m, 'contract_employees', [
    'contract_id' => $ctId, 'employee_id' => $empIds['EMPDEMO04'],
    'role_on_contract' => 'Supervisor', 'created_at' => $now,
]);
insert($m, 'contract_employees', [
    'contract_id' => $ctId, 'employee_id' => $empIds['EMPDEMO08'],
    'role_on_contract' => 'Technician', 'created_at' => $now,
]);

echo "Sales/CRM/Contracts seeded\n";

// ---------------------------------------------------------------------------
// Inventory
// ---------------------------------------------------------------------------
$catId = insert($m, 'product_categories', [
    'name' => 'Cleaning Supplies', 'code' => 'CLEAN', 'status' => 'active',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
$cat2 = insert($m, 'product_categories', [
    'name' => 'IT Hardware', 'code' => 'IT', 'status' => 'active',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
$brandId = insert($m, 'product_brands', [
    'name' => 'Generic', 'code' => 'GEN', 'status' => 'active',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
$uom = one($m, "SELECT id FROM units_of_measure WHERE code = 'PCS' LIMIT 1");
if (!$uom) {
    $uomId = insert($m, 'units_of_measure', [
        'name' => 'Piece', 'code' => 'PCS', 'symbol' => 'pc', 'status' => 'active',
        'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
    ]);
} else {
    $uomId = (int) $uom['id'];
}
$wh = one($m, "SELECT id FROM warehouses WHERE code = 'MAIN' LIMIT 1");
$whId = $wh ? (int) $wh['id'] : 1;
insert($m, 'warehouses', [
    'name' => 'Site Store — BCC', 'code' => 'BCC', 'city' => 'Manama', 'country' => 'Bahrain',
    'is_default' => 0, 'status' => 'active', 'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);

$products = [
    ['DET-5L', 'Floor Detergent 5L', $catId, 2.500, 4.000, 20],
    ['BAG-LG', 'Garbage Bags Large (roll)', $catId, 1.200, 2.000, 50],
    ['GLV-M', 'Nitrile Gloves M', $catId, 0.800, 1.500, 30],
    ['KB-USB', 'USB Keyboard', $cat2, 4.000, 7.500, 5],
    ['MSE-WL', 'Wireless Mouse', $cat2, 3.500, 6.000, 5],
];
$productIds = [];
foreach ($products as $p) {
    $pid = insert($m, 'products', [
        'name' => $p[1], 'sku' => $p[0], 'barcode' => '628' . substr(md5($p[0]), 0, 10),
        'category_id' => $p[2], 'brand_id' => $brandId, 'unit_id' => $uomId,
        'cost_price' => $p[3], 'sale_price' => $p[4], 'reorder_level' => $p[5],
        'status' => 'active', 'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
    ]);
    $productIds[$p[0]] = $pid;
    insert($m, 'stock_balances', [
        'product_id' => $pid, 'warehouse_id' => $whId,
        'quantity' => $p[5] * 3, 'reserved_qty' => 0,
        'updated_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
    ]);
}

$invAssetId = insert($m, 'inventory_assets', [
    'asset_tag' => 'EQ-DEMO-001', 'name' => 'Industrial Vacuum', 'category' => 'Equipment',
    'location' => 'Main Warehouse', 'serial_number' => 'VAC-99821',
    'purchase_date' => date('Y-m-d', strtotime('-1 year')), 'purchase_cost' => 185.000,
    'status' => 'active', 'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'inventory_assets', [
    'asset_tag' => 'EQ-DEMO-002', 'name' => 'Floor Scrubber', 'category' => 'Equipment',
    'location' => 'BCC Store', 'serial_number' => 'SCR-44102',
    'purchase_date' => date('Y-m-d', strtotime('-8 months')), 'purchase_cost' => 420.000,
    'status' => 'active', 'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);

echo "Inventory seeded\n";

// ---------------------------------------------------------------------------
// Procurement
// ---------------------------------------------------------------------------
$sup1 = insert($m, 'suppliers', [
    'name' => 'Bahrain Supplies Co', 'code' => 'SUP-BSC', 'email' => 'sales@bsc.demo',
    'phone' => '+97317550001', 'city' => 'Manama', 'country' => 'Bahrain', 'status' => 'active',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
$sup2 = insert($m, 'suppliers', [
    'name' => 'Gulf Paper Traders', 'code' => 'SUP-GPT', 'email' => 'orders@gpt.demo',
    'phone' => '+97317550002', 'city' => 'Riffa', 'country' => 'Bahrain', 'status' => 'active',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'supplier_contacts', [
    'supplier_id' => $sup1, 'first_name' => 'Tariq', 'last_name' => 'Saleem',
    'email' => 'tariq@bsc.demo', 'is_primary' => 1, 'status' => 'active',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);

$prId = insert($m, 'purchase_requests', [
    'number' => 'PR-DEMO-001', 'request_date' => date('Y-m-d', strtotime('-7 days')),
    'needed_by' => date('Y-m-d', strtotime('+7 days')), 'department' => 'Operations',
    'status' => 'approved', 'subtotal' => 125, 'tax_amount' => 0, 'total' => 125,
    'approved_by' => $bossUserId, 'approved_at' => $now,
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'purchase_request_lines', [
    'purchase_request_id' => $prId, 'item_code' => 'DET-5L', 'item_name' => 'Floor Detergent 5L',
    'quantity' => 50, 'unit' => 'pcs', 'unit_price' => 2.5, 'line_total' => 125,
    'sort_order' => 1, 'created_at' => $now,
]);

$poId = insert($m, 'purchase_orders', [
    'number' => 'PO-DEMO-001', 'supplier_id' => $sup1,
    'order_date' => date('Y-m-d', strtotime('-5 days')),
    'expected_date' => date('Y-m-d', strtotime('+3 days')),
    'status' => 'approved', 'subtotal' => 125, 'tax_amount' => 0, 'total' => 125,
    'approved_by' => $bossUserId, 'approved_at' => $now,
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'purchase_order_lines', [
    'purchase_order_id' => $poId, 'item_code' => 'DET-5L', 'item_name' => 'Floor Detergent 5L',
    'quantity' => 50, 'unit' => 'pcs', 'unit_price' => 2.5, 'line_total' => 125,
    'sort_order' => 1, 'created_at' => $now,
]);

$grnId = insert($m, 'goods_receipts', [
    'number' => 'GRN-DEMO-001', 'purchase_order_id' => $poId, 'supplier_id' => $sup1,
    'receipt_date' => date('Y-m-d', strtotime('-2 days')), 'status' => 'posted',
    'posted_by' => $bossUserId, 'posted_at' => $now,
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'goods_receipt_lines', [
    'goods_receipt_id' => $grnId, 'item_code' => 'DET-5L', 'item_name' => 'Floor Detergent 5L',
    'quantity' => 50, 'unit' => 'pcs', 'sort_order' => 1, 'created_at' => $now,
]);

echo "Procurement seeded\n";

// ---------------------------------------------------------------------------
// Accounting
// ---------------------------------------------------------------------------
$coa = [
    ['1000', 'Assets', 'asset', 0],
    ['1100', 'Cash on Hand', 'asset', 1],
    ['1200', 'Bank — NBB Current', 'asset', 1],
    ['1300', 'Accounts Receivable', 'asset', 1],
    ['1400', 'Inventory', 'asset', 1],
    ['2000', 'Liabilities', 'liability', 0],
    ['2100', 'Accounts Payable', 'liability', 1],
    ['3000', 'Equity', 'equity', 0],
    ['3100', 'Owner Equity', 'equity', 1],
    ['4000', 'Revenue', 'revenue', 0],
    ['4100', 'Service Revenue', 'revenue', 1],
    ['5000', 'Expenses', 'expense', 0],
    ['5100', 'Operating Expenses', 'expense', 1],
    ['5200', 'Salaries Expense', 'expense', 1],
];
$coaIds = [];
foreach ($coa as $a) {
    $existing = one($m, "SELECT id FROM chart_of_accounts WHERE code = '{$a[0]}' LIMIT 1");
    if ($existing) {
        $coaIds[$a[0]] = (int) $existing['id'];
        continue;
    }
    $coaIds[$a[0]] = insert($m, 'chart_of_accounts', [
        'code' => $a[0], 'name' => $a[1], 'type' => $a[2], 'is_postable' => $a[3],
        'status' => 'active', 'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
    ]);
}

$fyCode = 'FY' . $year;
$fy = one($m, "SELECT id FROM fiscal_years WHERE code = '{$fyCode}' LIMIT 1");
if ($fy) {
    $fyId = (int) $fy['id'];
} else {
    $fyId = insert($m, 'fiscal_years', [
        'name' => 'Fiscal Year ' . $year, 'code' => $fyCode,
        'start_date' => $year . '-01-01', 'end_date' => $year . '-12-31',
        'status' => 'open', 'is_current' => 1,
        'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
    ]);
}

$jeId = insert($m, 'journal_entries', [
    'number' => 'JE-DEMO-001', 'entry_date' => date('Y-m-d', strtotime('-5 days')),
    'fiscal_year_id' => $fyId, 'reference' => 'Opening demo',
    'description' => 'Demo posted journal entry', 'status' => 'posted',
    'total_debit' => 5000, 'total_credit' => 5000,
    'posted_by' => $bossUserId, 'posted_at' => $now,
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'journal_entry_lines', [
    'journal_entry_id' => $jeId, 'account_id' => $coaIds['1200'], 'description' => 'Bank',
    'debit' => 5000, 'credit' => 0, 'sort_order' => 1, 'created_at' => $now,
]);
insert($m, 'journal_entry_lines', [
    'journal_entry_id' => $jeId, 'account_id' => $coaIds['3100'], 'description' => 'Equity',
    'debit' => 0, 'credit' => 5000, 'sort_order' => 2, 'created_at' => $now,
]);

$ciId = insert($m, 'customer_invoices', [
    'number' => 'AR-DEMO-001', 'company_id' => $companyIds['CLEANPRO'],
    'invoice_date' => date('Y-m-d', strtotime('-12 days')),
    'due_date' => date('Y-m-d', strtotime('+18 days')),
    'status' => 'posted', 'subtotal' => 850, 'tax_amount' => 0, 'total' => 850, 'amount_paid' => 0,
    'posted_at' => $now, 'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'customer_invoice_lines', [
    'customer_invoice_id' => $ciId, 'item_code' => 'SVC-CLEAN', 'item_name' => 'Cleaning Service',
    'quantity' => 1, 'unit' => 'month', 'unit_price' => 850, 'line_total' => 850,
    'sort_order' => 1, 'created_at' => $now,
]);

$sbId = insert($m, 'supplier_bills', [
    'number' => 'AP-DEMO-001', 'supplier_id' => $sup1,
    'bill_date' => date('Y-m-d', strtotime('-4 days')),
    'due_date' => date('Y-m-d', strtotime('+26 days')),
    'status' => 'posted', 'subtotal' => 125, 'tax_amount' => 0, 'total' => 125, 'amount_paid' => 0,
    'posted_at' => $now, 'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'supplier_bill_lines', [
    'supplier_bill_id' => $sbId, 'item_code' => 'DET-5L', 'item_name' => 'Floor Detergent 5L',
    'quantity' => 50, 'unit' => 'pcs', 'unit_price' => 2.5, 'line_total' => 125,
    'sort_order' => 1, 'created_at' => $now,
]);

$bankId = insert($m, 'bank_accounts', [
    'code' => 'NBB-MAIN', 'name' => 'NBB Operating Account', 'bank_name' => 'National Bank of Bahrain',
    'account_number' => '001234567890', 'currency' => 'BHD', 'account_id' => $coaIds['1200'],
    'opening_balance' => 5000, 'status' => 'active',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
$cashId = insert($m, 'cash_accounts', [
    'code' => 'PETTY', 'name' => 'Petty Cash', 'currency' => 'BHD', 'account_id' => $coaIds['1100'],
    'opening_balance' => 200, 'status' => 'active',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);

insert($m, 'tax_rates', [
    'name' => 'VAT 10%', 'code' => 'VAT10', 'rate' => 10.0000, 'tax_type' => 'vat',
    'is_recoverable' => 1, 'status' => 'active',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);

$ccId = insert($m, 'cost_centers', [
    'code' => 'OPS', 'name' => 'Operations', 'status' => 'active',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'budgets', [
    'number' => 'BUD-DEMO-001', 'name' => 'Ops expenses ' . $year, 'fiscal_year' => $year,
    'period_from' => $year . '-01-01', 'period_to' => $year . '-12-31',
    'account_id' => $coaIds['5100'], 'cost_center_id' => $ccId, 'amount' => 50000,
    'status' => 'active', 'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'expenses', [
    'number' => 'EXP-DEMO-001', 'expense_date' => date('Y-m-d', strtotime('-3 days')),
    'amount' => 45.500, 'expense_account_id' => $coaIds['5100'],
    'pay_from_type' => 'cash', 'pay_from_id' => $cashId, 'cost_center_id' => $ccId,
    'payee' => 'Office supplies shop', 'status' => 'approved', 'approved_at' => $now,
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);

$acat = insert($m, 'asset_categories', [
    'code' => 'EQ', 'name' => 'Equipment', 'useful_life_months' => 60,
    'depreciation_method' => 'straight_line', 'status' => 'active',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
// Check asset_categories columns - may differ
// If insert failed we'd have thrown. Continue carefully.

echo "Accounting seeded\n";

// ---------------------------------------------------------------------------
// Operations
// ---------------------------------------------------------------------------
$wo1 = insert($m, 'work_orders', [
    'number' => 'WO-DEMO-001', 'title' => 'Night cleaning — Level 2',
    'description' => 'Full clean of food court and corridors',
    'priority' => 'normal', 'status' => 'in_progress',
    'scheduled_date' => $today, 'due_date' => date('Y-m-d', strtotime('+1 day')),
    'location' => 'Bahrain City Centre L2',
    'started_at' => date('Y-m-d H:i:s', strtotime('-2 hours')),
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'work_order_staff', [
    'work_order_id' => $wo1, 'employee_id' => $empIds['EMPDEMO04'], 'role' => 'Supervisor',
    'assigned_at' => $now, 'assigned_by' => $bossUserId,
]);
insert($m, 'work_order_staff', [
    'work_order_id' => $wo1, 'employee_id' => $empIds['EMPDEMO08'], 'role' => 'Technician',
    'assigned_at' => $now, 'assigned_by' => $bossUserId,
]);
if ($invAssetId) {
    insert($m, 'work_order_equipment', [
        'work_order_id' => $wo1, 'inventory_asset_id' => $invAssetId,
        'assigned_at' => $now, 'assigned_by' => $bossUserId,
    ]);
}

insert($m, 'work_orders', [
    'number' => 'WO-DEMO-002', 'title' => 'Deep clean — VIP lounge',
    'priority' => 'high', 'status' => 'open',
    'scheduled_date' => date('Y-m-d', strtotime('+2 days')),
    'due_date' => date('Y-m-d', strtotime('+3 days')),
    'location' => 'Marina Retail',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'work_orders', [
    'number' => 'WO-DEMO-003', 'title' => 'Equipment maintenance — scrubber',
    'priority' => 'low', 'status' => 'pending_approval',
    'scheduled_date' => date('Y-m-d', strtotime('-1 day')),
    'due_date' => $today, 'location' => 'Main Warehouse',
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);

$taskId = insert($m, 'operations_tasks', [
    'number' => 'OT-DEMO-001', 'title' => 'Restock cleaning cart supplies',
    'priority' => 'normal', 'status' => 'open',
    'due_date' => date('Y-m-d', strtotime('+1 day')),
    'assignee_employee_id' => $empIds['EMPDEMO06'],
    'created_by' => $bossUserId, 'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'operations_task_checklist_items', [
    'task_id' => $taskId, 'title' => 'Check detergent stock', 'is_done' => 1, 'sort_order' => 1,
    'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'operations_task_checklist_items', [
    'task_id' => $taskId, 'title' => 'Refill gloves', 'is_done' => 0, 'sort_order' => 2,
    'created_at' => $now, 'updated_at' => $now,
]);

insert($m, 'operations_approval_requests', [
    'number' => 'OAR-DEMO-001',
    'request_type' => 'work',
    'title' => 'Approve overtime for Level 2 clean',
    'description' => 'Extra 2 hours requested for food court deep clean',
    'amount' => null,
    'requester_user_id' => $attUserId,
    'approver_user_id' => $bossUserId,
    'status' => 'pending',
    'created_by' => $attUserId,
    'created_at' => $now,
    'updated_at' => $now,
]);

echo "Operations seeded\n";

// Messaging sample
$conv = one($m, "SELECT id FROM message_conversations WHERE type = 'public' AND is_system = 1 LIMIT 1");
if ($conv) {
    $convId = (int) $conv['id'];
    foreach ([$bossUserId, $hrmUserId, $attUserId] as $uid) {
        if (!exists($m, 'message_participants', "conversation_id = {$convId} AND user_id = {$uid}")) {
            insert($m, 'message_participants', [
                'conversation_id' => $convId, 'user_id' => $uid, 'joined_at' => $now,
            ]);
        }
    }
    insert($m, 'messages', [
        'conversation_id' => $convId, 'sender_id' => $bossUserId,
        'body' => 'Welcome to the demo environment — use boss / hrm / attendance to explore.',
        'created_at' => $now, 'updated_at' => $now,
    ]);
}

$dmId = insert($m, 'message_conversations', [
    'type' => 'direct', 'created_by' => $bossUserId, 'is_system' => 0,
    'created_at' => $now, 'updated_at' => $now,
]);
insert($m, 'message_participants', ['conversation_id' => $dmId, 'user_id' => $bossUserId, 'joined_at' => $now]);
insert($m, 'message_participants', ['conversation_id' => $dmId, 'user_id' => $hrmUserId, 'joined_at' => $now]);
insert($m, 'messages', [
    'conversation_id' => $dmId, 'sender_id' => $bossUserId,
    'body' => 'Please review the pending leave requests today.',
    'created_at' => $now, 'updated_at' => $now,
]);

echo "\n==============================================\n";
echo " Demo data ready\n";
echo "----------------------------------------------\n";
echo " boss        / {$password}   (full access)\n";
echo " hrm         / {$password}   (HR manager)\n";
echo " attendance  / {$password}   (attendance only)\n";
echo " admin remains unchanged\n";
echo "==============================================\n";
