-- Bulk Bahrain test data for Transport AI planning
-- Run against your ERP database (e.g. erp_foundation)
-- Requires: employees, companies, contracts, customer_sites, attendance, transport, transport_ai schemas
--
-- Creates:
--   1,000 customer sites + 1,005 transport locations (5 depots + 1,000 sites)
--   250 companies, 1,000 contracts, 3,000 staff
--   Shift assignments, contract staff (3 per contract), contract transport sites
--   100 vehicles, 80 drivers, 500 routes, 2,000 transport schedules
--
-- Safe to re-run: skips if employee BHST00001 already exists.
-- To re-seed: delete rows with prefixes BHST/BHC/BHS/BHCT/BHLOC/BHTRP/BHFLEET

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------------
-- Guard: skip when bulk data already loaded
-- ---------------------------------------------------------------------------
SET @seed_exists := (
    SELECT COUNT(*) FROM `employees`
    WHERE `employee_number` = 'BHST00001' AND `deleted_at` IS NULL
);

-- ---------------------------------------------------------------------------
-- Resolve reference IDs (branch, department, shifts, admin, customer group)
-- ---------------------------------------------------------------------------
SET @branch_id := COALESCE((SELECT id FROM branches WHERE code = 'HQ' LIMIT 1), 1);
SET @admin_id  := COALESCE((SELECT id FROM users WHERE username = 'admin' AND deleted_at IS NULL LIMIT 1), 1);
SET @now       := NOW();

SET @dept_id := (SELECT id FROM departments WHERE name = 'Operations' LIMIT 1);

INSERT INTO `departments` (`branch_id`, `name`, `status`, `created_at`, `updated_at`)
SELECT @branch_id, 'Operations', 'active', @now, @now
FROM DUAL
WHERE @seed_exists = 0
  AND @dept_id IS NULL;

SET @dept_id := COALESCE(@dept_id, (SELECT id FROM departments WHERE name = 'Operations' LIMIT 1), 1);

SET @shift_day  := COALESCE((SELECT id FROM attendance_shifts WHERE code = 'DAY'  LIMIT 1), 1);
SET @shift_morn := COALESCE((SELECT id FROM attendance_shifts WHERE code = 'MORN' LIMIT 1), @shift_day);
SET @shift_eve  := COALESCE((SELECT id FROM attendance_shifts WHERE code = 'EVE'  LIMIT 1), @shift_day);
SET @group_std  := COALESCE((SELECT id FROM customer_groups WHERE code = 'STD' LIMIT 1), NULL);

-- ---------------------------------------------------------------------------
-- Number generators (0–9 and 1–N sequences without recursion limits)
-- ---------------------------------------------------------------------------
DROP TEMPORARY TABLE IF EXISTS `_digits`;
CREATE TEMPORARY TABLE `_digits` (`d` INT NOT NULL PRIMARY KEY);
INSERT INTO `_digits` VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

DROP TEMPORARY TABLE IF EXISTS `_seq1k`;
CREATE TEMPORARY TABLE `_seq1k` AS
SELECT ones.d + tens.d * 10 + hundreds.d * 100 + 1 AS n
FROM `_digits` ones
CROSS JOIN `_digits` tens
CROSS JOIN `_digits` hundreds
WHERE ones.d + tens.d * 10 + hundreds.d * 100 < 1000;

DROP TEMPORARY TABLE IF EXISTS `_seq3k`;
CREATE TEMPORARY TABLE `_seq3k` AS
SELECT ones.d + tens.d * 10 + hundreds.d * 100 + thousands.d * 1000 + 1 AS n
FROM `_digits` ones
CROSS JOIN `_digits` tens
CROSS JOIN `_digits` hundreds
CROSS JOIN `_digits` thousands
WHERE ones.d + tens.d * 10 + hundreds.d * 100 + thousands.d * 1000 < 3000;

DROP TEMPORARY TABLE IF EXISTS `_seq250`;
CREATE TEMPORARY TABLE `_seq250` AS SELECT n FROM `_seq1k` WHERE n <= 250;

DROP TEMPORARY TABLE IF EXISTS `_seq2k`;
CREATE TEMPORARY TABLE `_seq2k` AS
SELECT s.n + x.x * 1000 AS n
FROM `_seq1k` s
CROSS JOIN (SELECT 0 AS x UNION SELECT 1) x
WHERE s.n + x.x * 1000 <= 2000;

DROP TEMPORARY TABLE IF EXISTS `_seq500`;
CREATE TEMPORARY TABLE `_seq500` AS SELECT n FROM `_seq1k` WHERE n <= 500;

DROP TEMPORARY TABLE IF EXISTS `_seq100`;
CREATE TEMPORARY TABLE `_seq100` AS SELECT n FROM `_seq1k` WHERE n <= 100;

DROP TEMPORARY TABLE IF EXISTS `_seq80`;
CREATE TEMPORARY TABLE `_seq80` AS SELECT n FROM `_seq1k` WHERE n <= 80;

DROP TEMPORARY TABLE IF EXISTS `_seq5`;
CREATE TEMPORARY TABLE `_seq5` AS SELECT n FROM `_seq1k` WHERE n <= 5;

-- ---------------------------------------------------------------------------
-- 5 depots
-- ---------------------------------------------------------------------------
INSERT INTO `transport_locations`
    (`number`, `name`, `code`, `location_type`, `address`, `city`, `country`,
     `latitude`, `longitude`, `status`, `created_by`, `created_at`, `updated_at`)
SELECT
    CONCAT('BHLOC-D', LPAD(n, 3, '0')),
    ELT(n,
        'Central Depot — Manama',
        'Northern Depot — Muharraq',
        'Southern Depot — Riffa',
        'Western Depot — Budaiya',
        'Eastern Depot — Hidd'
    ),
    CONCAT('DEP-', LPAD(n, 3, '0')),
    'depot',
    CONCAT('Industrial Area, ', ELT(n, 'Manama', 'Muharraq', 'Riffa', 'Budaiya', 'Hidd')),
    ELT(n, 'Manama', 'Muharraq', 'Riffa', 'Budaiya', 'Hidd'),
    'Bahrain',
    ROUND(ELT(n, 26.2235, 26.2572, 26.1299, 26.2289, 26.2450) + ((n * 17) % 200 - 100) / 10000, 7),
    ROUND(ELT(n, 50.5876, 50.6119, 50.5550, 50.4539, 50.6500) + ((n * 23) % 200 - 100) / 10000, 7),
    'active', @admin_id, @now, @now
FROM `_seq5`
WHERE @seed_exists = 0;

-- ---------------------------------------------------------------------------
-- 1,000 customer sites
-- ---------------------------------------------------------------------------
INSERT INTO `customer_sites`
    (`name`, `code`, `site_type`, `address`, `city`, `country`, `description`,
     `status`, `created_by`, `created_at`, `updated_at`)
SELECT
    CONCAT(
        ELT(1 + ((n - 1) MOD 15),
            'Manama','Muharraq','Riffa','Hamad Town','Isa Town','Sitra','Budaiya',
            'Juffair','Seef','Sanad','Zallaq','Hidd','Aali','Jidhafs','Tubli'
        ),
        ' ',
        ELT(1 + (n MOD 10), 'Office','Mall','Warehouse','Hospital','School','Factory','Hotel','Site','Depot','Camp'),
        ' — Block ', 100 + (n MOD 900)
    ),
    CONCAT('BHS', LPAD(n, 4, '0')),
    ELT(1 + (n MOD 10), 'office','mall','warehouse','hospital','school','factory','hotel','site','depot','camp'),
    CONCAT('Road ', 4400 + (n MOD 600), ', Block ', 100 + (n MOD 900)),
    ELT(1 + ((n - 1) MOD 15),
        'Manama','Muharraq','Riffa','Hamad Town','Isa Town','Sitra','Budaiya',
        'Juffair','Seef','Sanad','Zallaq','Hidd','Aali','Jidhafs','Tubli'
    ),
    'Bahrain',
    'Bulk seed site for transport testing',
    'active', @admin_id, @now, @now
FROM `_seq1k`
WHERE @seed_exists = 0;

-- ---------------------------------------------------------------------------
-- 1,000 transport site locations (with GPS)
-- ---------------------------------------------------------------------------
INSERT INTO `transport_locations`
    (`number`, `name`, `code`, `location_type`, `address`, `city`, `country`,
     `latitude`, `longitude`, `status`, `created_by`, `created_at`, `updated_at`)
SELECT
    CONCAT('BHLOC-S', LPAD(n, 5, '0')),
    CONCAT(
        ELT(1 + ((n - 1) MOD 15),
            'Manama','Muharraq','Riffa','Hamad Town','Isa Town','Sitra','Budaiya',
            'Juffair','Seef','Sanad','Zallaq','Hidd','Aali','Jidhafs','Tubli'
        ),
        ' ',
        ELT(1 + (n MOD 10), 'Office','Mall','Warehouse','Hospital','School','Factory','Hotel','Site','Depot','Camp'),
        ' — Block ', 100 + (n MOD 900)
    ),
    CONCAT('SITE-', LPAD(n, 5, '0')),
    IF(n MOD 7 = 0, 'hub', 'customer'),
    CONCAT('Road ', 4400 + (n MOD 600), ', Block ', 100 + (n MOD 900)),
    ELT(1 + ((n - 1) MOD 15),
        'Manama','Muharraq','Riffa','Hamad Town','Isa Town','Sitra','Budaiya',
        'Juffair','Seef','Sanad','Zallaq','Hidd','Aali','Jidhafs','Tubli'
    ),
    'Bahrain',
    ROUND(ELT(1 + ((n - 1) MOD 15),
        26.2235, 26.2572, 26.1299, 26.1153, 26.1736, 26.1547, 26.2289,
        26.2128, 26.2361, 26.2117, 26.0536, 26.2450, 26.1550, 26.2050, 26.1900
    ) + ((n * 17) MOD 200 - 100) / 10000, 7),
    ROUND(ELT(1 + ((n - 1) MOD 15),
        50.5876, 50.6119, 50.5550, 50.5065, 50.5478, 50.6206, 50.4539,
        50.6070, 50.5411, 50.5650, 50.5136, 50.6500, 50.5250, 50.5450, 50.5700
    ) + ((n * 23) MOD 200 - 100) / 10000, 7),
    'active', @admin_id, @now, @now
FROM `_seq1k`
WHERE @seed_exists = 0;

-- ---------------------------------------------------------------------------
-- 250 companies
-- ---------------------------------------------------------------------------
INSERT INTO `companies`
    (`name`, `code`, `email`, `phone`, `industry`, `customer_group_id`, `customer_type`, `is_one_time`,
     `site_id`, `address`, `city`, `country`, `status`, `notes`, `created_by`, `created_at`, `updated_at`)
SELECT
    CONCAT(
        ELT(1 + (n MOD 10), 'Facilities','Retail','Hospitality','Healthcare','Construction',
            'Logistics','Manufacturing','Education','Banking','Technology'),
        ' Services WLL — ',
        ELT(1 + ((n - 1) MOD 15),
            'Manama','Muharraq','Riffa','Hamad Town','Isa Town','Sitra','Budaiya',
            'Juffair','Seef','Sanad','Zallaq','Hidd','Aali','Jidhafs','Tubli'
        )
    ),
    CONCAT('BHC', LPAD(n, 4, '0')),
    CONCAT('contact', LPAD(n, 4, '0'), '@bhseed.demo'),
    CONCAT('+97317', LPAD(10000 + n, 5, '0')),
    ELT(1 + (n MOD 10), 'Facilities','Retail','Hospitality','Healthcare','Construction',
        'Logistics','Manufacturing','Education','Banking','Technology'),
    @group_std,
    ELT(1 + (n MOD 5), 'service','service','service','product','project'),
    0,
    (SELECT id FROM customer_sites WHERE code = CONCAT('BHS', LPAD(1 + ((n - 1) MOD 1000), 4, '0')) LIMIT 1),
    CONCAT('Office ', n, ', ',
        ELT(1 + ((n - 1) MOD 15),
            'Manama','Muharraq','Riffa','Hamad Town','Isa Town','Sitra','Budaiya',
            'Juffair','Seef','Sanad','Zallaq','Hidd','Aali','Jidhafs','Tubli'
        )
    ),
    ELT(1 + ((n - 1) MOD 15),
        'Manama','Muharraq','Riffa','Hamad Town','Isa Town','Sitra','Budaiya',
        'Juffair','Seef','Sanad','Zallaq','Hidd','Aali','Jidhafs','Tubli'
    ),
    'Bahrain',
    'active',
    'Bulk seed company',
    @admin_id, @now, @now
FROM `_seq250`
WHERE @seed_exists = 0;

-- ---------------------------------------------------------------------------
-- 1,000 contracts
-- ---------------------------------------------------------------------------
INSERT INTO `contracts`
    (`number`, `title`, `company_id`, `contract_type`, `status`,
     `start_date`, `end_date`, `value`, `total_hours`, `hourly_rate`,
     `location`, `project_name`, `notes`, `created_by`, `created_at`, `updated_at`)
SELECT
    CONCAT('BHCT-', LPAD(n, 5, '0')),
    CONCAT(
        UPPER(LEFT(ELT(1 + (n MOD 5), 'service','service','service','product','project'), 1)),
        LOWER(SUBSTRING(ELT(1 + (n MOD 5), 'service','service','service','product','project'), 2)),
        ' Contract — ',
        ELT(1 + ((n - 1) MOD 15),
            'Manama','Muharraq','Riffa','Hamad Town','Isa Town','Sitra','Budaiya',
            'Juffair','Seef','Sanad','Zallaq','Hidd','Aali','Jidhafs','Tubli'
        ),
        ' Site ', n
    ),
    (SELECT id FROM companies WHERE code = CONCAT('BHC', LPAD(1 + ((n - 1) MOD 250), 4, '0')) LIMIT 1),
    ELT(1 + (n MOD 5), 'service','service','service','product','project'),
    CASE
        WHEN n <= 950 THEN 'active'
        WHEN n <= 980 THEN 'draft'
        ELSE 'expired'
    END,
    CASE
        WHEN n <= 950 THEN DATE_SUB(CURDATE(), INTERVAL (6 + (n MOD 12)) MONTH)
        WHEN n <= 980 THEN DATE_ADD(CURDATE(), INTERVAL 1 MONTH)
        ELSE DATE_SUB(CURDATE(), INTERVAL 24 MONTH)
    END,
    CASE
        WHEN n <= 950 THEN DATE_ADD(CURDATE(), INTERVAL (6 + (n MOD 18)) MONTH)
        WHEN n <= 980 THEN DATE_ADD(CURDATE(), INTERVAL 13 MONTH)
        ELSE DATE_SUB(CURDATE(), INTERVAL 2 MONTH)
    END,
    ROUND(500 + (n MOD 50) * 175 + (n MOD 7) * 250, 2),
    160 * (12 + (n MOD 6)),
    ROUND((500 + (n MOD 50) * 175 + (n MOD 7) * 250) / GREATEST(1, 160 * 6), 2),
    ELT(1 + ((n - 1) MOD 15),
        'Manama','Muharraq','Riffa','Hamad Town','Isa Town','Sitra','Budaiya',
        'Juffair','Seef','Sanad','Zallaq','Hidd','Aali','Jidhafs','Tubli'
    ),
    CONCAT('Project ',
        ELT(1 + ((n - 1) MOD 15),
            'Manama','Muharraq','Riffa','Hamad Town','Isa Town','Sitra','Budaiya',
            'Juffair','Seef','Sanad','Zallaq','Hidd','Aali','Jidhafs','Tubli'
        ),
        '-', n
    ),
    'Bulk seed contract for transport AI testing',
    @admin_id, @now, @now
FROM `_seq1k`
WHERE @seed_exists = 0;

-- Contract lines (1 per contract)
INSERT INTO `contract_lines`
    (`contract_id`, `item_code`, `item_name`, `quantity`, `unit`, `unit_price`, `line_total`, `sort_order`, `created_at`)
SELECT
    c.id,
    CONCAT('SVC-', LPAD(1 + (s.n MOD 100), 4, '0')),
    'Monthly service package',
    6 + (s.n MOD 12),
    'month',
    ROUND(400 + (s.n MOD 20) * 25, 2),
    ROUND((400 + (s.n MOD 20) * 25) * (6 + (s.n MOD 12)), 2),
    1,
    @now
FROM `_seq1k` s
JOIN `contracts` c ON c.number = CONCAT('BHCT-', LPAD(s.n, 5, '0'))
WHERE @seed_exists = 0;

-- ---------------------------------------------------------------------------
-- 3,000 staff (all active for full contract coverage)
-- ---------------------------------------------------------------------------
INSERT INTO `employees`
    (`employee_number`, `full_name`, `department_id`, `branch_id`, `job_title`,
     `email`, `phone`, `mobile`, `cpr_number`, `nationality`, `date_of_birth`, `gender`,
     `marital_status`, `address`, `city`, `country`,
     `emergency_contact_name`, `emergency_contact_phone`,
     `hire_date`, `employment_type`, `bank_name`, `bank_account`,
     `status`, `created_by`, `created_at`, `updated_at`)
SELECT
    CONCAT('BHST', LPAD(n, 5, '0')),
    CONCAT(
        IF(n MOD 3 = 0,
            ELT(1 + (n MOD 15), 'Fatima','Noor','Maryam','Sara','Layla','Aisha','Huda','Amira','Dana','Reem','Mariam','Latifa','Zainab','Salma','Hessa'),
            ELT(1 + (n MOD 15), 'Ahmed','Mohammed','Hassan','Khalid','Omar','Yusuf','Ali','Rashid','Tariq','Faisal','Saeed','Hamad','Jassim','Majid','Nasser')
        ),
        ' ',
        ELT(1 + (n MOD 20), 'Al-Khalifa','Al-Mannai','Al-Jalahma','Hassan','Ibrahim','Ahmed','Darwish','Mahmood','Nasser','Abdulla','Hameed','Saleh','Juma','Faisal','Ali','Mohammed','Khalifa','Al-Binali','Al-Dosari','Al-Hajri')
    ),
    @dept_id,
    @branch_id,
    ELT(1 + (n MOD 10), 'Cleaner','Security Guard','Technician','Supervisor','Operator','Driver','Maintenance','Porter','Steward','Labourer'),
    CONCAT('staff', LPAD(n, 5, '0'), '@bhseed.demo'),
    CONCAT('+97336', LPAD(100000 + n, 6, '0')),
    CONCAT('+97366', LPAD(100000 + n, 6, '0')),
    CONCAT('9', LPAD(100000000 + n, 9, '0')),
    ELT(1 + (n MOD 9), 'Bahraini','Indian','Pakistani','Filipino','Bangladeshi','Egyptian','Jordanian','Nepalese','Sri Lankan'),
    DATE_SUB(CURDATE(), INTERVAL (22 + (n MOD 28)) YEAR),
    IF(n MOD 3 = 0, 'female', 'male'),
    IF(n MOD 2 = 1, 'married', 'single'),
    CONCAT('Building ', (n MOD 90) + 10, ', Road ', 4400 + (n MOD 500), ', Block ', 200 + (n MOD 800)),
    ELT(1 + ((n - 1) MOD 15),
        'Manama','Muharraq','Riffa','Hamad Town','Isa Town','Sitra','Budaiya',
        'Juffair','Seef','Sanad','Zallaq','Hidd','Aali','Jidhafs','Tubli'
    ),
    'Bahrain',
    'Emergency Contact',
    CONCAT('+97339', LPAD(100000 + n, 6, '0')),
    DATE_SUB(CURDATE(), INTERVAL (3 + (n MOD 60)) MONTH),
    IF(n MOD 11 = 0, 'part_time', 'full_time'),
    IF(n MOD 2 = 1, 'NBB', 'BBK'),
    CONCAT('BH00SEED', LPAD(n, 10, '0')),
    'active',
    @admin_id, @now, @now
FROM `_seq3k`
WHERE @seed_exists = 0;

-- Shift assignments for all staff
INSERT INTO `employee_shift_assignments`
    (`employee_id`, `shift_id`, `effective_from`, `created_by`, `created_at`, `updated_at`)
SELECT
    e.id,
    ELT(1 + (s.n MOD 3), @shift_day, @shift_morn, @shift_eve),
    DATE_SUB(CURDATE(), INTERVAL 12 MONTH),
    @admin_id, @now, @now
FROM `_seq3k` s
JOIN `employees` e ON e.employee_number = CONCAT('BHST', LPAD(s.n, 5, '0'))
WHERE @seed_exists = 0;

-- 3 staff per contract (3,000 assignments across 1,000 contracts)
INSERT INTO `contract_employees`
    (`contract_id`, `employee_id`, `role_on_contract`, `hours_allocated`, `created_at`)
SELECT
    c.id,
    e.id,
    ELT(1 + ((s.n - 1) MOD 5), 'Staff','Supervisor','Technician','Operator','Team Lead'),
    160 + (((s.n - 1) MOD 3) * 40),
    @now
FROM `_seq3k` s
JOIN `contracts` c ON c.number = CONCAT('BHCT-', LPAD(CEIL(s.n / 3), 5, '0'))
JOIN `employees` e ON e.employee_number = CONCAT('BHST', LPAD(s.n, 5, '0'))
WHERE @seed_exists = 0;

-- Contract transport site mappings (active contracts only)
INSERT INTO `transport_contract_sites`
    (`contract_id`, `transport_location_id`, `default_shift_id`, `status`, `notes`, `created_by`, `created_at`, `updated_at`)
SELECT
    c.id,
    l.id,
    ELT(1 + (s.n MOD 3), @shift_day, @shift_morn, @shift_eve),
    'active',
    'Bulk seed contract site mapping',
    @admin_id, @now, @now
FROM `_seq1k` s
JOIN `contracts` c ON c.number = CONCAT('BHCT-', LPAD(s.n, 5, '0')) AND c.status = 'active'
JOIN `transport_locations` l ON l.number = CONCAT('BHLOC-S', LPAD(s.n, 5, '0'))
WHERE @seed_exists = 0;

-- ---------------------------------------------------------------------------
-- Fleet: 100 vehicles, 80 drivers
-- ---------------------------------------------------------------------------
INSERT INTO `transport_vehicles`
    (`number`, `registration_number`, `make`, `model`, `year`, `vehicle_type`, `fuel_type`,
     `capacity`, `fuel_consumption_l_per_100km`, `odometer_km`, `current_location_id`,
     `status`, `created_by`, `created_at`, `updated_at`)
SELECT
    CONCAT('BHFLEET-V', LPAD(n, 4, '0')),
    CONCAT('BH-', 1000 + n, '-', CHAR(65 + (n MOD 26))),
    ELT(1 + (n MOD 6), 'Toyota','Nissan','Hyundai','Mercedes','Ford','Isuzu'),
    CASE ELT(1 + (n MOD 6), 'van','van','van','bus','car','truck')
        WHEN 'bus' THEN 'Coaster'
        WHEN 'van' THEN 'Hiace'
        ELSE CONCAT('Model-', n MOD 50)
    END,
    2018 + (n MOD 7),
    ELT(1 + (n MOD 6), 'van','van','van','bus','car','truck'),
    IF(n MOD 5 = 0, 'diesel', 'petrol'),
    CASE ELT(1 + (n MOD 6), 'van','van','van','bus','car','truck')
        WHEN 'bus' THEN 30 + (n MOD 15)
        WHEN 'van' THEN 12 + (n MOD 8)
        WHEN 'car' THEN 4
        WHEN 'truck' THEN 8
        ELSE 15
    END,
    CASE ELT(1 + (n MOD 6), 'van','van','van','bus','car','truck')
        WHEN 'bus' THEN 22.00
        WHEN 'van' THEN 12.00
        ELSE 8.00
    END,
    10000 + n * 137,
    (SELECT id FROM transport_locations WHERE number = CONCAT('BHLOC-D', LPAD(1 + (n MOD 5), 3, '0')) LIMIT 1),
    IF(n MOD 19 = 0, 'maintenance', 'active'),
    @admin_id, @now, @now
FROM `_seq100`
WHERE @seed_exists = 0;

INSERT INTO `transport_drivers`
    (`number`, `employee_id`, `license_number`, `license_class`, `license_expiry`, `phone`, `status`, `created_by`, `created_at`, `updated_at`)
SELECT
    CONCAT('BHFLEET-D', LPAD(n, 4, '0')),
    e.id,
    CONCAT('BH-LIC-', 200000 + n),
    IF(n MOD 4 = 0, 'C', 'B'),
    DATE_ADD(CURDATE(), INTERVAL (6 + (n MOD 24)) MONTH),
    CONCAT('+97339', LPAD(200000 + n, 6, '0')),
    IF(n MOD 13 = 0, 'inactive', 'active'),
    @admin_id, @now, @now
FROM `_seq80` s
JOIN `employees` e ON e.employee_number = CONCAT('BHST', LPAD(s.n, 5, '0'))
WHERE @seed_exists = 0;

-- 500 routes
INSERT INTO `transport_routes`
    (`number`, `name`, `code`, `origin_location_id`, `destination_location_id`,
     `distance_km`, `estimated_duration_minutes`, `status`, `created_by`, `created_at`, `updated_at`)
SELECT
    CONCAT('BHRT-', LPAD(n, 5, '0')),
    CONCAT('Depot Route ', n),
    CONCAT('R-', LPAD(n, 5, '0')),
    (SELECT id FROM transport_locations WHERE number = CONCAT('BHLOC-D', LPAD(1 + (n MOD 5), 3, '0')) LIMIT 1),
    (SELECT id FROM transport_locations WHERE number = CONCAT('BHLOC-S', LPAD(n, 5, '0')) LIMIT 1),
    ROUND(5 + (n MOD 35) + (n MOD 7) * 1.5, 2),
    15 + (n MOD 45),
    'active',
    @admin_id, @now, @now
FROM `_seq500`
WHERE @seed_exists = 0;

-- 2,000 transport schedules
INSERT INTO `transport_schedules`
    (`number`, `title`, `route_id`, `vehicle_id`, `driver_id`, `schedule_date`,
     `departure_time`, `arrival_time`, `priority`, `status`, `notes`,
     `started_at`, `completed_at`, `created_by`, `created_at`, `updated_at`)
SELECT
    CONCAT('BHTRP-', LPAD(n, 6, '0')),
    CONCAT('Workforce Run ', n, ' — ', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL ((n MOD 60) - 30) DAY), '%d %b')),
    (SELECT id FROM transport_routes WHERE number = CONCAT('BHRT-', LPAD(1 + ((n - 1) MOD 500), 5, '0')) LIMIT 1),
    (SELECT id FROM transport_vehicles WHERE number = CONCAT('BHFLEET-V', LPAD(1 + ((n - 1) MOD 100), 4, '0')) AND status = 'active' LIMIT 1),
    (SELECT id FROM transport_drivers WHERE number = CONCAT('BHFLEET-D', LPAD(1 + ((n - 1) MOD 80), 4, '0')) AND status = 'active' LIMIT 1),
    DATE_ADD(CURDATE(), INTERVAL ((n MOD 60) - 30) DAY),
    TIME(CONCAT(LPAD(5 + (n MOD 4), 2, '0'), ':', LPAD((n MOD 4) * 15, 2, '0'), ':00')),
    TIME(CONCAT(LPAD(6 + (n MOD 4) + (n MOD 2), 2, '0'), ':', LPAD(((n MOD 4) * 15 + 30) MOD 60, 2, '0'), ':00')),
    ELT(1 + (n MOD 6), 'low','normal','normal','normal','high','urgent'),
    CASE
        WHEN n <= 600  THEN 'planned'
        WHEN n <= 1300 THEN 'scheduled'
        WHEN n <= 1700 THEN 'completed'
        WHEN n <= 1900 THEN 'in_progress'
        ELSE 'cancelled'
    END,
    'Bulk seed transport schedule',
    CASE
        WHEN n > 1300 AND n <= 1900 THEN CONCAT(DATE_ADD(CURDATE(), INTERVAL ((n MOD 60) - 30) DAY), ' ', LPAD(5 + (n MOD 4), 2, '0'), ':', LPAD((n MOD 4) * 15, 2, '0'), ':00')
        ELSE NULL
    END,
    IF(n > 1300 AND n <= 1700,
        CONCAT(DATE_ADD(CURDATE(), INTERVAL ((n MOD 60) - 30) DAY), ' ', LPAD(6 + (n MOD 4) + (n MOD 2), 2, '0'), ':', LPAD(((n MOD 4) * 15 + 30) MOD 60, 2, '0'), ':00'),
        NULL
    ),
    @admin_id, @now, @now
FROM `_seq2k`
WHERE @seed_exists = 0;

-- ---------------------------------------------------------------------------
-- Cleanup temp tables
-- ---------------------------------------------------------------------------
DROP TEMPORARY TABLE IF EXISTS `_digits`;
DROP TEMPORARY TABLE IF EXISTS `_seq1k`;
DROP TEMPORARY TABLE IF EXISTS `_seq3k`;
DROP TEMPORARY TABLE IF EXISTS `_seq250`;
DROP TEMPORARY TABLE IF EXISTS `_seq2k`;
DROP TEMPORARY TABLE IF EXISTS `_seq500`;
DROP TEMPORARY TABLE IF EXISTS `_seq100`;
DROP TEMPORARY TABLE IF EXISTS `_seq80`;
DROP TEMPORARY TABLE IF EXISTS `_seq5`;

-- Summary (visible in phpMyAdmin / CLI client)
SELECT 'transport_bulk_seed complete' AS message,
       (SELECT COUNT(*) FROM customer_sites WHERE code LIKE 'BHS%') AS customer_sites,
       (SELECT COUNT(*) FROM companies WHERE code LIKE 'BHC%') AS companies,
       (SELECT COUNT(*) FROM contracts WHERE number LIKE 'BHCT-%') AS contracts,
       (SELECT COUNT(*) FROM employees WHERE employee_number LIKE 'BHST%') AS employees,
       (SELECT COUNT(*) FROM contract_employees ce JOIN employees e ON e.id = ce.employee_id WHERE e.employee_number LIKE 'BHST%') AS contract_staff,
       (SELECT COUNT(*) FROM transport_contract_sites tcs JOIN contracts c ON c.id = tcs.contract_id WHERE c.number LIKE 'BHCT-%') AS contract_sites,
       (SELECT COUNT(*) FROM transport_locations WHERE number LIKE 'BHLOC-%') AS transport_locations,
       (SELECT COUNT(*) FROM transport_vehicles WHERE number LIKE 'BHFLEET-%') AS vehicles,
       (SELECT COUNT(*) FROM transport_drivers WHERE number LIKE 'BHFLEET-D%') AS drivers,
       (SELECT COUNT(*) FROM transport_schedules WHERE number LIKE 'BHTRP-%') AS schedules;
