<?php
// Minimal seed script — skips the vendor autoloader (requires PHP 8) and uses raw PDO.
// Run: php _seed_checklists.php

$db = new PDO('mysql:host=localhost;dbname=orchview2;charset=utf8mb4', 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$tenantId = (int) $db->query("SELECT tenant_id FROM tenants WHERE subdomain = 'orchid' LIMIT 1")->fetchColumn();
if (!$tenantId) {
    die("ERROR: 'orchid' tenant not found. Run migrations first.\n");
}

$typeIdOf = function (string $code) use ($db, $tenantId) {
    $stmt = $db->prepare("SELECT asset_type_id FROM asset_types WHERE tenant_id = ? AND code = ? LIMIT 1");
    $stmt->execute([$tenantId, $code]);
    $val = $stmt->fetchColumn();
    return $val !== false ? (int) $val : null;
};

$genId    = $typeIdOf('generator');
$acId     = $typeIdOf('aircon');
$trafoId  = $typeIdOf('transformer');
$stabId   = $typeIdOf('stabilizer');
$panelId  = $typeIdOf('panel');
$motorId  = $typeIdOf('motor_starter');

$CHECKED     = json_encode(['CHECKED','NOT CHECKED']);
$OK_HIGH_LOW = json_encode(['OK','HIGH','LOW']);
$OK_NOT      = json_encode(['OK','NOT OK']);
$PASS_FAIL   = json_encode(['PASS','FAIL','NOT APPLICABLE']);

$templates = [
    ['code' => 'daily_gen_check',          'name' => 'Daily Generator Checklist',              'desc' => "Daily walk-around and readings for standby generators.",                                                                    'atid' => $genId,   'days' => 1,   'lead' => 0,  'prio' => 'medium',
        'tasks' => [
            ['Check engine oil level',                       'options', $OK_HIGH_LOW, 1],
            ['Check coolant level',                          'options', $OK_HIGH_LOW, 1],
            ['Check fuel level (record %)',                  'number',  null,          1],
            ['Record generator hours',                       'number',  null,          1],
            ['Check for oil / fuel / coolant leaks',         'options', $CHECKED,      1],
            ['Check battery terminals + electrolyte',        'options', $OK_NOT,       1],
            ['Inspect belts + hoses for cracks',             'options', $CHECKED,      0],
            ['Check exhaust for unusual smoke',              'options', $CHECKED,      0],
            ['Verify AVR / control panel indicators',        'options', $OK_NOT,       1],
            ['Photograph fuel gauge + hour meter',           'photo',   null,          0],
        ],
    ],
    ['code' => 'weekly_gen_load_test',      'name' => 'Weekly Generator Load Test',           'desc' => "No-break load test to verify generator can carry site load.",                                                               'atid' => $genId,   'days' => 7,   'lead' => 1,  'prio' => 'medium',
        'tasks' => [
            ['Confirm ATS in AUTO mode',                     'options', $OK_NOT,   1],
            ['Simulate mains failure via test switch',       'checkbox',null,       1],
            ['Record start-up time (seconds)',               'number',  null,       1],
            ['Record voltage L1/L2/L3 under load',           'text',    null,       1],
            ['Record frequency under load',                  'number',  null,       1],
            ['Restore mains + confirm ATS returns to normal','checkbox',null,       1],
        ],
    ],
    ['code' => 'gen_250h_service',          'name' => '250-hr Generator Oil & Filter Service', 'desc' => "Minor service — oil, oil filter, visual belts.",                                                                             'atid' => $genId,   'days' => 30,  'lead' => 5,  'prio' => 'high',
        'tasks' => [
            ['Isolate generator (LOTO)',                     'checkbox',null,       1],
            ['Drain engine oil (record litres)',             'number',  null,       1],
            ['Replace oil filter (record part number)',      'text',    null,       1],
            ['Refill with correct grade oil',                'text',    null,       1],
            ['Check air filter, clean or replace',           'options', json_encode(['CLEANED','REPLACED','OK AS-IS']), 1],
            ['Check + tighten hose clamps',                  'options', $OK_NOT,    1],
            ['Run engine + verify no leaks',                 'options', $OK_NOT,    1],
            ['Reset service-hour counter',                   'checkbox',null,       0],
        ],
    ],
    ['code' => 'gen_500h_service',          'name' => '500-hr Generator Full Service',         'desc' => "Major PM including fuel filters, air filter, coolant test.",                                                               'atid' => $genId,   'days' => 60,  'lead' => 7,  'prio' => 'high',
        'tasks' => [
            ['Isolate generator (LOTO)',                     'checkbox',null,       1],
            ['Drain + refill engine oil (litres)',           'number',  null,       1],
            ['Replace oil filter',                           'checkbox',null,       1],
            ['Replace primary fuel filter',                  'checkbox',null,       1],
            ['Replace secondary fuel filter',                'checkbox',null,       1],
            ['Replace air filter',                           'checkbox',null,       1],
            ['Test coolant SCA + freeze point',              'options', $PASS_FAIL, 1],
            ['Inspect belts + tension',                      'options', $OK_NOT,    1],
            ['Inspect radiator + clean fins',                'options', $OK_NOT,    1],
            ['Battery: check + top up electrolyte',          'options', $OK_NOT,    1],
            ['Load-test 30 min at >=75% rated',              'options', $PASS_FAIL, 1],
            ['Photograph completed work',                    'photo',   null,       0],
        ],
    ],
    ['code' => 'gen_annual_service',        'name' => 'Annual Generator Major Service',        'desc' => "Comprehensive annual overhaul-adjacent PM.",                                                                              'atid' => $genId,   'days' => 365, 'lead' => 14, 'prio' => 'high',
        'tasks' => [
            ['Isolate generator (LOTO) + tag',               'checkbox',null,       1],
            ['Full oil + all filters change',                'checkbox',null,       1],
            ['Coolant flush + refill',                       'checkbox',null,       1],
            ['Replace all rubber hoses if aged >5 yr',       'options', json_encode(['REPLACED','KEPT']), 1],
            ['Replace belts',                                'checkbox',null,       1],
            ['Injector inspection / test',                   'options', $PASS_FAIL, 1],
            ['Valve clearance check + adjust',               'options', $PASS_FAIL, 1],
            ['Alternator brush inspection',                  'options', $OK_NOT,    1],
            ['AVR calibration',                              'options', $PASS_FAIL, 1],
            ['Full electrical safety test (megger)',         'options', $PASS_FAIL, 1],
            ['ATS full function test',                       'options', $PASS_FAIL, 1],
            ['Enclosure paint + rust check',                 'options', $OK_NOT,    0],
            ['Update service log + labels',                  'checkbox',null,       1],
            ['Load-test 2 hrs at >=80% rated',               'options', $PASS_FAIL, 1],
            ['Photograph identification plate + hour meter', 'photo',   null,       1],
        ],
    ],
    ['code' => 'daily_power_sweep',         'name' => 'Daily Power, Lighting & Grounding Sweep','desc' => "General plant walk-through for power, consumables, lighting, grounding.",                                               'atid' => null,     'days' => 1,   'lead' => 0,  'prio' => 'medium',
        'tasks' => [
            ['Check for faulty lighting (lamp/ballast/starter)',     'options', $CHECKED, 1],
            ['Check for deteriorated light output',                  'options', $CHECKED, 1],
            ['Observe energy saving (lights off during day)',        'options', $CHECKED, 1],
            ['Observe cable safety (torn / exposed)',                'options', $OK_NOT,  1],
            ['Verify power supply stability',                        'options', $OK_NOT,  1],
            ['Check voltage consistency',                            'options', $OK_HIGH_LOW, 1],
            ['Check for unusual smell / noise from panels',          'options', $CHECKED, 1],
            ['Check for loose connections in accessible panels',     'options', $OK_NOT,  1],
            ['Verify emergency lighting operates',                   'options', $PASS_FAIL, 1],
            ['Ground / earth strap continuity check',                'options', $PASS_FAIL, 1],
        ],
    ],
    ['code' => 'monthly_ac_check',          'name' => 'Monthly AC Filter & Coil Check',        'desc' => "Basic monthly PM for split / package aircons.",                                                                          'atid' => $acId,    'days' => 30,  'lead' => 3,  'prio' => 'medium',
        'tasks' => [
            ['Clean or replace filter',                              'options', json_encode(['CLEANED','REPLACED']),1],
            ['Inspect + clean condenser coil',                       'options', $OK_NOT,   1],
            ['Check indoor drain (blockage)',                        'options', $OK_NOT,   1],
            ['Record suction + discharge pressures',                 'text',    null,      1],
            ['Record supply air temp (°C)',                          'number',  null,      1],
            ['Check thermostat setpoint',                            'text',    null,      0],
            ['Photograph coils after cleaning',                     'photo',   null,      0],
        ],
    ],
    ['code' => 'annual_ac_compressor_service','name' => 'Annual AC Compressor Service',          'desc' => "Full annual PM including refrigerant + electrical.",                                                                       'atid' => $acId,    'days' => 365, 'lead' => 14, 'prio' => 'high',
        'tasks' => [
            ['Isolate power + tag',                                  'checkbox',null,       1],
            ['Check + top up refrigerant',                           'options', json_encode(['TOPPED-UP','OK','LEAK FOUND']), 1],
            ['Leak test all joints',                                 'options', $PASS_FAIL, 1],
            ['Clean condenser + evaporator coils',                   'checkbox',null,       1],
            ['Check compressor oil level',                           'options', $OK_HIGH_LOW,1],
            ['Insulation resistance test on compressor motor',      'options', $PASS_FAIL, 1],
            ['Tighten electrical terminations',                     'checkbox',null,       1],
            ['Inspect + replace contactor if pitted',                'options', json_encode(['OK','REPLACED']), 1],
            ['Vibration + noise check',                              'options', $OK_NOT,    1],
            ['Test run + record temperatures',                       'text',    null,       1],
        ],
    ],
    ['code' => 'monthly_transformer',       'name' => 'Monthly Transformer Inspection',         'desc' => "Visual + basic readings on distribution transformer.",                                                                     'atid' => $trafoId, 'days' => 30,  'lead' => 3,  'prio' => 'high',
        'tasks' => [
            ['Oil level in main tank (record)',                      'options', $OK_HIGH_LOW,1],
            ['Silica gel breather colour (blue = ok)',               'options', json_encode(['BLUE','PINK','WHITE']), 1],
            ['Check for leaks on bushings + gaskets',                'options', $CHECKED,     1],
            ['Record oil temperature (°C)',                          'number',  null,         1],
            ['Record winding temperature (°C) if fitted',            'number',  null,         0],
            ['Bushing condition (chips / tracking)',                 'options', $OK_NOT,      1],
            ['Fence + earthing intact',                              'options', $OK_NOT,      1],
            ['Photograph nameplate + gauges',                        'photo',   null,         0],
        ],
    ],
    ['code' => 'annual_transformer_dga',    'name' => 'Annual Transformer DGA Oil Sampling',    'desc' => "Dissolved Gas Analysis to spot faults early.",                                                                            'atid' => $trafoId, 'days' => 365, 'lead' => 30, 'prio' => 'medium',
        'tasks' => [
            ['De-energise + LOTO transformer',                       'checkbox',null,     1],
            ['Draw oil sample using approved bottle',                'checkbox',null,     1],
            ['Record sample bottle ID',                              'text',    null,     1],
            ['Send to lab (record dispatch date)',                   'text',    null,     1],
            ['Attach lab report to WO when returned',                'photo',   null,     1],
        ],
    ],
    ['code' => 'monthly_ups_battery',       'name' => 'Monthly UPS / Stabilizer Battery Check', 'desc' => "Battery bank health for standby power.",                                                                                   'atid' => $stabId,  'days' => 30,  'lead' => 3,  'prio' => 'high',
        'tasks' => [
            ['Record float voltage per string',                      'text',    null,      1],
            ['Measure ambient temperature',                          'number',  null,      1],
            ['Check for corrosion on terminals',                     'options', $OK_NOT,   1],
            ['Check ventilation intake / exhaust',                   'options', $OK_NOT,   1],
            ['Verify alarms + display panel',                        'options', $OK_NOT,   1],
            ['Photograph any bulged / leaking battery',              'photo',   null,      0],
        ],
    ],
    ['code' => 'q_panel_thermographic',     'name' => 'Quarterly Panel Thermographic Scan',     'desc' => "IR camera scan to find hotspots before failure.",                                                                         'atid' => $panelId, 'days' => 90,  'lead' => 7,  'prio' => 'high',
        'tasks' => [
            ['Confirm load is normal / above 40%',                   'options', $OK_NOT,    1],
            ['Scan all incoming/outgoing terminations',              'checkbox',null,       1],
            ['Record max temperature hotspot (°C)',                  'number',  null,       1],
            ['Attach thermal image of any hotspot >60°C above ambient','photo', null,       0],
            ['Compare against previous scan (delta)',                'text',    null,       0],
        ],
    ],
    ['code' => 'q_motor_vibration',         'name' => 'Quarterly Motor Vibration Analysis',     'desc' => "Detects bearing wear + shaft misalignment early.",                                                                        'atid' => $motorId, 'days' => 90,  'lead' => 7,  'prio' => 'medium',
        'tasks' => [
            ['Verify motor is running under normal load',            'options', $OK_NOT,   1],
            ['Vibration reading — drive-end bearing (mm/s)',        'number',  null,      1],
            ['Vibration reading — non-drive-end bearing (mm/s)',    'number',  null,      1],
            ['Bearing temperature — drive end (°C)',                'number',  null,      1],
            ['Bearing temperature — non-drive end (°C)',            'number',  null,      1],
            ['Overall recommendation',                              'options', json_encode(['CONTINUE','MONITOR','SCHEDULE REPAIR','URGENT']), 1],
        ],
    ],
    ['code' => 'weekly_fire_extinguisher',  'name' => 'Weekly Fire Extinguisher Inspection',    'desc' => "Regulatory visual check on plant fire extinguishers.",                                                                    'atid' => null,     'days' => 7,   'lead' => 1,  'prio' => 'high',
        'tasks' => [
            ['Pressure gauge in green band',                         'options', $OK_NOT,    1],
            ['Seal + tamper pin intact',                             'options', $OK_NOT,    1],
            ['No visible damage / rust',                             'options', $OK_NOT,    1],
            ['Service label current (< 12 months)',                  'options', $PASS_FAIL, 1],
            ['Mounting bracket + signage present',                   'options', $OK_NOT,    1],
        ],
    ],
    ['code' => 'monthly_grounding_earth',   'name' => 'Monthly Grounding & Earth Resistance',   'desc' => "Verify earth pit resistance + strap continuity.",                                                                         'atid' => null,     'days' => 30,  'lead' => 5,  'prio' => 'high',
        'tasks' => [
            ['Visual: earth pit cover + strap intact',               'options', $OK_NOT,    1],
            ['Water top-up in earth pit if required',                'options', json_encode(['DONE','NOT NEEDED']), 1],
            ['Earth resistance value (Ω) — target <5',               'number',  null,       1],
            ['Body-to-earth continuity on equipment sampled',       'options', $PASS_FAIL, 1],
            ['Photograph earth pit + reading',                       'photo',   null,       0],
        ],
    ],
];

$inserted = 0;
$db->beginTransaction();
try {
    foreach ($templates as $tpl) {
        $stmt = $db->prepare("SELECT template_id FROM pm_templates WHERE tenant_id = ? AND code = ? LIMIT 1");
        $stmt->execute([$tenantId, $tpl['code']]);
        $templateId = $stmt->fetchColumn();

        if (!$templateId) {
            $db->prepare("INSERT INTO pm_templates (tenant_id, asset_type_id, code, name, description, interval_days, lead_days, priority, is_active, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, 1, NOW(), NOW())")->execute([
                $tenantId, $tpl['atid'], $tpl['code'], $tpl['name'], $tpl['desc'], $tpl['days'], $tpl['lead'], $tpl['prio'],
            ]);
            $templateId = (int) $db->lastInsertId();
        }

        $taskCount = 0;
        foreach ($tpl['tasks'] as $i => $task) {
            [$title, $inputType, $options, $required] = $task;
            $stmt = $db->prepare("SELECT template_task_id FROM pm_template_tasks WHERE template_id = ? AND title = ? LIMIT 1");
            $stmt->execute([$templateId, $title]);
            if (!$stmt->fetchColumn()) {
                $db->prepare("INSERT INTO pm_template_tasks (template_id, sort_order, title, input_type, options_json, is_required)
                    VALUES (?, ?, ?, ?, ?, ?)")->execute([
                    $templateId, ($i + 1) * 10, $title, $inputType, $options, $required ? 1 : 0,
                ]);
                $taskCount++;
            }
        }
        $inserted++;
        echo "Seeded: " . $tpl['name'] . " (" . $taskCount . " new tasks)\n";
    }
    $db->commit();
    echo "\nDone! " . $inserted . " checklist templates seeded.\n";
    echo "Now sync the app again — pull down on the Sync screen.\n";
} catch (Exception $e) {
    $db->rollBack();
    die("ERROR: " . $e->getMessage() . "\n");
}