<?php
/**
 * ESO Data Sync API — receives data from the daily pull script
 * POST with JSON body + API key for auth
 * Creates tables if needed, inserts/updates data
 */
require_once __DIR__ . '/../config/db.php';

// API key auth (not session-based — called from cron script)
$apiKey = $_SERVER['HTTP_X_API_KEY'] ?? '';
$expectedKey = getenv('ESO_SYNC_KEY') ?: 'cfd-eso-sync-2026';

if ($_SERVER['REQUEST_METHOD'] !== 'POST' || $apiKey !== $expectedKey) {
    http_response_code(403);
    echo json_encode(['error' => 'Unauthorized']);
    exit;
}

$db = getDB();
$input = json_decode(file_get_contents('php://input'), true);
$action = $input['action'] ?? '';

header('Content-Type: application/json');

// Auto-create tables if they don't exist
$db->exec("
    CREATE TABLE IF NOT EXISTS eso_ems_daily (
        id INT AUTO_INCREMENT PRIMARY KEY,
        report_date DATE NOT NULL UNIQUE,
        total_incidents INT DEFAULT 0,
        unique_incidents INT DEFAULT 0,
        total_transports INT DEFAULT 0,
        cardiac_arrests INT DEFAULT 0,
        stemis INT DEFAULT 0,
        strokes INT DEFAULT 0,
        overdoses INT DEFAULT 0,
        pediatric_calls INT DEFAULT 0,
        avg_response_time_sec FLOAT DEFAULT 0,
        median_response_time_sec FLOAT DEFAULT 0,
        pct_under_goal FLOAT DEFAULT 0,
        top_impressions JSON,
        top_cad_natures JSON,
        hospital_transports JSON,
        unit_counts JSON,
        hourly_volume JSON,
        medic_turnout_json JSON,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");

$db->exec("
    CREATE TABLE IF NOT EXISTS eso_ems_incidents (
        id INT AUTO_INCREMENT PRIMARY KEY,
        report_date DATE NOT NULL,
        incident_number VARCHAR(30) NOT NULL,
        unit VARCHAR(20),
        shift VARCHAR(50),
        dispatched_at DATETIME,
        on_scene_at DATETIME,
        response_time_sec INT,
        cad_nature VARCHAR(200),
        primary_impression VARCHAR(200),
        destination VARCHAR(200),
        transport_disposition VARCHAR(200),
        patient_age INT,
        patient_gender VARCHAR(10),
        narrative TEXT,
        is_cardiac_arrest BOOLEAN DEFAULT FALSE,
        is_stemi BOOLEAN DEFAULT FALSE,
        is_stroke BOOLEAN DEFAULT FALSE,
        is_overdose BOOLEAN DEFAULT FALSE,
        is_pediatric BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_incident_unit (incident_number, unit)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");

$db->exec("
    CREATE TABLE IF NOT EXISTS eso_cardiac_arrests (
        id INT AUTO_INCREMENT PRIMARY KEY,
        incident_number VARCHAR(30) NOT NULL,
        unit VARCHAR(20),
        dispatched_at DATETIME,
        cad_nature VARCHAR(200),
        primary_impression VARCHAR(200),
        destination VARCHAR(200),
        transport_disposition VARCHAR(200),
        narrative TEXT,
        patient_age INT,
        patient_gender VARCHAR(10),
        initial_rhythm VARCHAR(100),
        rosc_occurred VARCHAR(20),
        cardiac_arrest_etiology VARCHAR(200),
        arrest_witnessed_by VARCHAR(100),
        cpr_initiated_by VARCHAR(100),
        defibrillated BOOLEAN,
        end_of_event VARCHAR(200),
        hde_ed_diagnosis VARCHAR(500),
        hde_ed_disposition VARCHAR(200),
        hde_hosp_diagnosis VARCHAR(500),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_arrest_unit (incident_number, unit)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");

// Add missing columns/tables
try { $db->exec("ALTER TABLE eso_ems_daily ADD COLUMN medic_turnout_json JSON AFTER hourly_volume"); } catch (Exception $e) {}
try { $db->exec("ALTER TABLE eso_personnel ADD COLUMN fire_incidents_ytd INT DEFAULT 0 AFTER patient_contacts_ytd"); } catch (Exception $e) {}
try { $db->exec("ALTER TABLE eso_personnel ADD COLUMN fire_pt_contacts_ytd INT DEFAULT 0 AFTER fire_incidents_ytd"); } catch (Exception $e) {}
try { $db->exec("ALTER TABLE eso_personnel ADD COLUMN patient_contacts_ytd INT DEFAULT 0 AFTER incident_count_ytd"); } catch (Exception $e) {}

$db->exec("
    CREATE TABLE IF NOT EXISTS eso_personnel (
        id INT AUTO_INCREMENT PRIMARY KEY, employee_number VARCHAR(20) NOT NULL UNIQUE,
        first_name VARCHAR(100), last_name VARCHAR(100), full_name VARCHAR(200),
        certification VARCHAR(100), ems_level VARCHAR(100), hire_date DATE,
        tenure_days INT, work_status VARCHAR(50), work_email VARCHAR(200),
        station VARCHAR(50), unit_assignment VARCHAR(50), shift_assignment VARCHAR(50),
        battalion VARCHAR(50), incident_count_ytd INT DEFAULT 0, patient_contacts_ytd INT DEFAULT 0, fire_incidents_ytd INT DEFAULT 0, fire_pt_contacts_ytd INT DEFAULT 0, primary_unit VARCHAR(20),
        primary_shift VARCHAR(50), last_incident_date DATE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");

$db->exec("
    CREATE TABLE IF NOT EXISTS eso_fire_briefings (
        id INT AUTO_INCREMENT PRIMARY KEY, incident_number VARCHAR(30) NOT NULL UNIQUE,
        incident_type VARCHAR(200), incident_date DATETIME, address VARCHAR(300),
        latitude DECIMAL(10,6), longitude DECIMAL(10,6), district VARCHAR(50),
        shift_name VARCHAR(50), oic_name VARCHAR(100), oic_rank VARCHAR(50),
        property_loss DECIMAL(12,2), content_loss DECIMAL(12,2),
        property_value DECIMAL(12,2), content_value DECIMAL(12,2),
        alarm_dt DATETIME, arrival_dt DATETIME, controlled_dt DATETIME, cleared_dt DATETIME,
        response_time_sec INT, water_on_fire_sec INT, arrival_to_controlled_sec INT, total_scene_time_sec INT,
        supp_apparatus INT, ems_apparatus INT, supp_personnel INT, ems_personnel INT,
        injury_or_death VARCHAR(50), narrative TEXT,
        apparatus_json JSON, fire_module_json JSON, civilian_casualties_json JSON, ff_casualties_json JSON,
        ai_summary TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");

$db->exec("
    CREATE TABLE IF NOT EXISTS eso_fire_daily (
        id INT AUTO_INCREMENT PRIMARY KEY, report_date DATE NOT NULL UNIQUE,
        total_incidents INT DEFAULT 0, structure_fires INT DEFAULT 0, vehicle_fires INT DEFAULT 0,
        false_alarms INT DEFAULT 0, hazmat INT DEFAULT 0, good_intent INT DEFAULT 0,
        service_calls INT DEFAULT 0, rescue_ems INT DEFAULT 0,
        total_property_loss DECIMAL(12,2) DEFAULT 0, total_content_loss DECIMAL(12,2) DEFAULT 0,
        avg_response_time_sec FLOAT DEFAULT 0,
        incident_types JSON, district_counts JSON, apparatus_counts JSON, hourly_volume JSON,
        apparatus_turnout_json JSON,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");
try { $db->exec("ALTER TABLE eso_fire_daily ADD COLUMN apparatus_turnout_json JSON AFTER hourly_volume"); } catch (Exception $e) {}

$db->exec("
    CREATE TABLE IF NOT EXISTS eso_fire_incidents (
        id INT AUTO_INCREMENT PRIMARY KEY, report_date DATE NOT NULL,
        incident_number VARCHAR(30) NOT NULL, incident_type VARCHAR(200),
        incident_type_group VARCHAR(100), incident_date DATETIME,
        location_address VARCHAR(300), district VARCHAR(50),
        estimated_property_loss DECIMAL(12,2), estimated_content_loss DECIMAL(12,2),
        response_time_sec INT, narrative TEXT, apparatus_count INT, personnel_count INT,
        injury_or_death VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_fire_incident (incident_number)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");

switch ($action) {
    case 'sync_daily':
        $d = $input['data'] ?? [];
        $stmt = $db->prepare("
            INSERT INTO eso_ems_daily
            (report_date, total_incidents, unique_incidents, total_transports,
             cardiac_arrests, stemis, strokes, overdoses, pediatric_calls,
             avg_response_time_sec, median_response_time_sec, pct_under_goal,
             top_impressions, top_cad_natures, hospital_transports, unit_counts, hourly_volume, medic_turnout_json)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            ON DUPLICATE KEY UPDATE
                total_incidents=VALUES(total_incidents),
                unique_incidents=VALUES(unique_incidents),
                total_transports=VALUES(total_transports),
                cardiac_arrests=VALUES(cardiac_arrests),
                stemis=VALUES(stemis),
                strokes=VALUES(strokes),
                overdoses=VALUES(overdoses),
                pediatric_calls=VALUES(pediatric_calls),
                avg_response_time_sec=VALUES(avg_response_time_sec),
                median_response_time_sec=VALUES(median_response_time_sec),
                pct_under_goal=VALUES(pct_under_goal),
                top_impressions=VALUES(top_impressions),
                top_cad_natures=VALUES(top_cad_natures),
                hospital_transports=VALUES(hospital_transports),
                unit_counts=VALUES(unit_counts),
                hourly_volume=VALUES(hourly_volume),
                medic_turnout_json=VALUES(medic_turnout_json),
                updated_at=NOW()
        ");
        $stmt->execute([
            $d['report_date'], $d['total_incidents'], $d['unique_incidents'],
            $d['total_transports'], $d['cardiac_arrests'], $d['stemis'],
            $d['strokes'], $d['overdoses'], $d['pediatric_calls'],
            $d['avg_response_time_sec'], $d['median_response_time_sec'],
            $d['pct_under_goal'], $d['top_impressions'], $d['top_cad_natures'],
            $d['hospital_transports'], $d['unit_counts'], $d['hourly_volume'],
            $d['medic_turnout_json'] ?? null,
        ]);
        echo json_encode(['success' => true, 'action' => 'sync_daily']);
        break;

    case 'sync_incidents':
        $rows = $input['data'] ?? [];
        $inserted = 0;
        $stmt = $db->prepare("
            INSERT IGNORE INTO eso_ems_incidents
            (report_date, incident_number, unit, shift, dispatched_at, on_scene_at,
             response_time_sec, cad_nature, primary_impression, destination,
             transport_disposition, patient_age, patient_gender, narrative,
             is_cardiac_arrest, is_stemi, is_stroke, is_overdose, is_pediatric)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
        ");
        foreach ($rows as $r) {
            try {
                $stmt->execute([
                    $r['report_date'], $r['incident_number'], $r['unit'], $r['shift'],
                    $r['dispatched_at'], $r['on_scene_at'], $r['response_time_sec'],
                    $r['cad_nature'], $r['primary_impression'], $r['destination'],
                    $r['transport_disposition'], $r['patient_age'], $r['patient_gender'],
                    $r['narrative'], $r['is_cardiac_arrest'], $r['is_stemi'],
                    $r['is_stroke'], $r['is_overdose'], $r['is_pediatric'],
                ]);
                if ($stmt->rowCount() > 0) $inserted++;
            } catch (Exception $e) { /* skip dupes */ }
        }
        echo json_encode(['success' => true, 'action' => 'sync_incidents', 'inserted' => $inserted]);
        break;

    case 'sync_arrests':
        $rows = $input['data'] ?? [];
        $inserted = 0;
        $stmt = $db->prepare("
            INSERT INTO eso_cardiac_arrests
            (incident_number, unit, dispatched_at, cad_nature, primary_impression,
             destination, transport_disposition, narrative, patient_age, patient_gender,
             initial_rhythm, rosc_occurred, cardiac_arrest_etiology, arrest_witnessed_by,
             cpr_initiated_by, defibrillated, end_of_event,
             hde_ed_diagnosis, hde_ed_disposition, hde_hosp_diagnosis)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            ON DUPLICATE KEY UPDATE
                initial_rhythm=VALUES(initial_rhythm), rosc_occurred=VALUES(rosc_occurred)
        ");
        foreach ($rows as $r) {
            try {
                $stmt->execute([
                    $r['incident_number'], $r['unit'], $r['dispatched_at'],
                    $r['cad_nature'], $r['primary_impression'], $r['destination'],
                    $r['transport_disposition'], $r['narrative'], $r['patient_age'],
                    $r['patient_gender'], $r['initial_rhythm'], $r['rosc_occurred'],
                    $r['cardiac_arrest_etiology'], $r['arrest_witnessed_by'],
                    $r['cpr_initiated_by'], $r['defibrillated'], $r['end_of_event'],
                    $r['hde_ed_diagnosis'], $r['hde_ed_disposition'], $r['hde_hosp_diagnosis'],
                ]);
                if ($stmt->rowCount() > 0) $inserted++;
            } catch (Exception $e) { /* skip dupes */ }
        }
        echo json_encode(['success' => true, 'action' => 'sync_arrests', 'inserted' => $inserted]);
        break;

    case 'sync_personnel':
        $rows = $input['data'] ?? [];
        $inserted = 0;
        $stmt = $db->prepare("
            INSERT INTO eso_personnel
            (employee_number, first_name, last_name, full_name, certification, ems_level,
             hire_date, tenure_days, work_status, work_email, station, unit_assignment,
             shift_assignment, battalion, incident_count_ytd, patient_contacts_ytd, fire_incidents_ytd, fire_pt_contacts_ytd, primary_unit, last_incident_date)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            ON DUPLICATE KEY UPDATE
                first_name=VALUES(first_name), last_name=VALUES(last_name), certification=VALUES(certification),
                incident_count_ytd=VALUES(incident_count_ytd), patient_contacts_ytd=VALUES(patient_contacts_ytd),
                fire_incidents_ytd=VALUES(fire_incidents_ytd), fire_pt_contacts_ytd=VALUES(fire_pt_contacts_ytd),
                primary_unit=VALUES(primary_unit), last_incident_date=VALUES(last_incident_date), updated_at=NOW()
        ");
        foreach ($rows as $r) {
            try {
                $stmt->execute([
                    $r['employee_number'], $r['first_name'], $r['last_name'], $r['full_name'],
                    $r['certification'], $r['ems_level'], $r['hire_date'], $r['tenure_days'],
                    $r['work_status'], $r['work_email'], $r['station'], $r['unit_assignment'],
                    $r['shift_assignment'], $r['battalion'], $r['incident_count_ytd'], $r['patient_contacts_ytd'] ?? 0,
                    $r['fire_incidents_ytd'] ?? 0, $r['fire_pt_contacts_ytd'] ?? 0,
                    $r['primary_unit'], $r['last_incident_date'],
                ]);
                if ($stmt->rowCount() > 0) $inserted++;
            } catch (Exception $e) {}
        }
        echo json_encode(['success' => true, 'action' => 'sync_personnel', 'inserted' => $inserted]);
        break;

    case 'sync_briefing':
        $d = $input['data'] ?? [];
        $stmt = $db->prepare("
            INSERT INTO eso_fire_briefings
            (incident_number, incident_type, incident_date, address, latitude, longitude,
             district, shift_name, oic_name, property_loss, content_loss,
             alarm_dt, arrival_dt, controlled_dt, cleared_dt,
             response_time_sec, water_on_fire_sec, arrival_to_controlled_sec, total_scene_time_sec,
             supp_apparatus, ems_apparatus, supp_personnel, ems_personnel,
             injury_or_death, narrative, apparatus_json, fire_module_json,
             civilian_casualties_json, ff_casualties_json, ai_summary)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            ON DUPLICATE KEY UPDATE
                narrative=VALUES(narrative), ai_summary=VALUES(ai_summary),
                apparatus_json=VALUES(apparatus_json), fire_module_json=VALUES(fire_module_json)
        ");
        $stmt->execute([
            $d['incident_number'], $d['incident_type'], $d['incident_date'], $d['address'],
            $d['latitude'], $d['longitude'], $d['district'] ?? '', $d['shift_name'] ?? '',
            $d['oic_name'] ?? '', $d['property_loss'], $d['content_loss'],
            $d['alarm_dt'], $d['arrival_dt'], $d['controlled_dt'], $d['cleared_dt'],
            $d['response_time_sec'], $d['water_on_fire_sec'], $d['arrival_to_controlled_sec'],
            $d['total_scene_time_sec'], $d['supp_apparatus'], $d['ems_apparatus'],
            $d['supp_personnel'], $d['ems_personnel'], $d['injury_or_death'] ?? '',
            $d['narrative'] ?? '', $d['apparatus_json'] ?? '[]', $d['fire_module_json'] ?? '{}',
            $d['civilian_casualties_json'] ?? '[]', $d['ff_casualties_json'] ?? '[]',
            $d['ai_summary'] ?? null,
        ]);
        echo json_encode(['success' => true, 'action' => 'sync_briefing']);
        break;

    case 'sync_fire_daily':
        $d = $input['data'] ?? [];
        $stmt = $db->prepare("
            INSERT INTO eso_fire_daily
            (report_date, total_incidents, structure_fires, vehicle_fires, false_alarms,
             hazmat, good_intent, service_calls, rescue_ems,
             total_property_loss, total_content_loss, avg_response_time_sec,
             incident_types, district_counts, apparatus_counts, hourly_volume, apparatus_turnout_json)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            ON DUPLICATE KEY UPDATE
                total_incidents=VALUES(total_incidents), structure_fires=VALUES(structure_fires),
                vehicle_fires=VALUES(vehicle_fires), false_alarms=VALUES(false_alarms),
                hazmat=VALUES(hazmat), good_intent=VALUES(good_intent),
                service_calls=VALUES(service_calls), rescue_ems=VALUES(rescue_ems),
                total_property_loss=VALUES(total_property_loss), total_content_loss=VALUES(total_content_loss),
                avg_response_time_sec=VALUES(avg_response_time_sec),
                incident_types=VALUES(incident_types), district_counts=VALUES(district_counts),
                apparatus_counts=VALUES(apparatus_counts), hourly_volume=VALUES(hourly_volume),
                apparatus_turnout_json=VALUES(apparatus_turnout_json),
                updated_at=NOW()
        ");
        $stmt->execute([
            $d['report_date'], $d['total_incidents'], $d['structure_fires'], $d['vehicle_fires'],
            $d['false_alarms'], $d['hazmat'], $d['good_intent'], $d['service_calls'], $d['rescue_ems'],
            $d['total_property_loss'], $d['total_content_loss'], $d['avg_response_time_sec'],
            $d['incident_types'], $d['district_counts'], $d['apparatus_counts'] ?? '{}', $d['hourly_volume'],
            $d['apparatus_turnout_json'] ?? '{}',
        ]);
        echo json_encode(['success' => true, 'action' => 'sync_fire_daily']);
        break;

    case 'sync_fire_incidents':
        $rows = $input['data'] ?? [];
        $inserted = 0;
        $stmt = $db->prepare("
            INSERT IGNORE INTO eso_fire_incidents
            (report_date, incident_number, incident_type, incident_type_group, incident_date,
             location_address, district, estimated_property_loss, estimated_content_loss,
             response_time_sec, narrative, apparatus_count, personnel_count, injury_or_death)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)
        ");
        foreach ($rows as $r) {
            try {
                $stmt->execute([
                    $r['report_date'], $r['incident_number'], $r['incident_type'], $r['incident_type_group'],
                    $r['incident_date'], $r['location_address'], $r['district'],
                    $r['estimated_property_loss'], $r['estimated_content_loss'],
                    $r['response_time_sec'], $r['narrative'], $r['apparatus_count'],
                    $r['personnel_count'], $r['injury_or_death'],
                ]);
                if ($stmt->rowCount() > 0) $inserted++;
            } catch (Exception $e) {}
        }
        echo json_encode(['success' => true, 'action' => 'sync_fire_incidents', 'inserted' => $inserted]);
        break;

    default:
        echo json_encode(['error' => 'Unknown action']);
}
