<?php
/**
 * Seed 50 realistic Vodacom-style tower sites for project 634 / contract 12.
 * Distribution matches the project name "Coast, Dar, Morogoro":
 *   · Coast:     15 sites (Bagamoyo / Kibaha / Kisarawe / Mkuranga)
 *   · Dar:       25 sites (Kinondoni / Ilala / Temeke / Ubungo / Kigamboni)
 *   · Morogoro:  10 sites (Morogoro Urban / Rural / Kilosa / Kilombero)
 *
 * Each site gets 1 primary POC (SITE_MANAGER by default; TECH for a subset).
 * ~30% of sites are POC-confirmed so PC has a mixed queue to work through.
 *
 * Idempotent: re-running wipes contract 12's sites + POCs first.
 */
define('DS', DIRECTORY_SEPARATOR);
define('ROOT', dirname(__DIR__));
require_once ROOT . DS . 'config' . DS . 'config.php';
require_once ROOT . DS . 'core' . DS . 'Application.php';
require_once ROOT . DS . 'core' . DS . 'Model.php';
require_once ROOT . DS . 'core' . DS . 'DB.php';
require_once ROOT . DS . 'app' . DS . 'models' . DS . 'ServiceContracts.php';
require_once ROOT . DS . 'app' . DS . 'models' . DS . 'ServiceContractSitePocs.php';

$CID = 12;
$UID = 5;   // PM user (project_manager_id on project 634)

$db  = new DB();
$sc  = new ServiceContracts();
$pm  = new ServiceContractSitePocs();

if (empty($sc->find($CID))) { echo "✗ contract $CID not found\n"; exit(1); }

/* ── Wipe existing ────────────────────────────────────────────── */
$db->insertUpdate("DELETE p FROM service_contract_site_pocs p JOIN service_contract_sites s ON s.c0d = p.site_id WHERE s.contract_id = :c", [':c' => $CID]);
$db->insertUpdate("DELETE FROM service_contract_sites WHERE contract_id = :c", [':c' => $CID]);
echo "· Wiped contract $CID sites + POCs\n";

/* ── Seed catalogue ───────────────────────────────────────────── */
// Each row: [code, name, region, district, address, lat, lng]
$sites = [
    /* Coast (15) ───────────────────────────────────────────── */
    ['CST-001','Coast-001 Bagamoyo Central',    'Coast','Bagamoyo',      'Plot 42, Kaole Rd',                      -6.4419, 38.8901],
    ['CST-002','Coast-002 Bagamoyo Kaole',      'Coast','Bagamoyo',      'Kaole ruins access rd, plot 12',         -6.4720, 38.9080],
    ['CST-003','Coast-003 Bagamoyo Chalinze',   'Coast','Bagamoyo',      'Chalinze junction, TANESCO side',        -6.6450, 38.3500],
    ['CST-004','Coast-004 Kibaha Central',      'Coast','Kibaha',        'Msangani St, near substation',           -6.7659, 38.9799],
    ['CST-005','Coast-005 Kibaha Tumbi',        'Coast','Kibaha',        'Tumbi hospital rd, plot 8',              -6.7810, 38.9450],
    ['CST-006','Coast-006 Kibaha Mlandizi',     'Coast','Kibaha',        'Mlandizi market, opposite police',       -6.7100, 38.7700],
    ['CST-007','Coast-007 Kisarawe Central',    'Coast','Kisarawe',      'Kisarawe DC office rd',                  -6.9020, 39.0800],
    ['CST-008','Coast-008 Kisarawe Maneromango','Coast','Kisarawe',      'Maneromango village center',             -7.0500, 38.9200],
    ['CST-009','Coast-009 Mkuranga Central',    'Coast','Mkuranga',      'Mkuranga town, near market',             -7.3170, 39.2110],
    ['CST-010','Coast-010 Mkuranga Vikindu',    'Coast','Mkuranga',      'Vikindu highway junction',               -7.1200, 39.2600],
    ['CST-011','Coast-011 Bagamoyo Zinga',      'Coast','Bagamoyo',      'Zinga fishing village access',           -6.3800, 38.9350],
    ['CST-012','Coast-012 Kibaha Ruvu',         'Coast','Kibaha',        'Ruvu bridge, TANROADS depot',            -6.8300, 38.7100],
    ['CST-013','Coast-013 Kisarawe Vikumburu',  'Coast','Kisarawe',      'Vikumburu ward office',                  -6.9500, 39.0100],
    ['CST-014','Coast-014 Mkuranga Kimanzichana','Coast','Mkuranga',     'Kimanzichana market rd',                 -7.2450, 39.1600],
    ['CST-015','Coast-015 Bagamoyo Msata',      'Coast','Bagamoyo',      'Msata trading center',                   -6.4800, 38.4500],

    /* Dar es Salaam (25) ──────────────────────────────────── */
    ['DSM-016','Dar-016 Kinondoni Msasani',     'Dar es Salaam','Kinondoni','Msasani Peninsula, next to Sea Cliff', -6.7449, 39.2755],
    ['DSM-017','Dar-017 Kinondoni Mikocheni',   'Dar es Salaam','Kinondoni','Mikocheni B, plot 4 Old Bagamoyo Rd',  -6.7620, 39.2530],
    ['DSM-018','Dar-018 Kinondoni Mwenge',      'Dar es Salaam','Kinondoni','Mwenge market roof-mount',             -6.7770, 39.2360],
    ['DSM-019','Dar-019 Kinondoni Sinza',       'Dar es Salaam','Kinondoni','Sinza Kijiweni, TIN centre',           -6.7690, 39.2100],
    ['DSM-020','Dar-020 Kinondoni Ubungo',      'Dar es Salaam','Ubungo',   'Ubungo bus terminal, ground-mount',    -6.7830, 39.2130],
    ['DSM-021','Dar-021 Kinondoni Kimara',      'Dar es Salaam','Ubungo',   'Kimara Mwisho, next to fuel station',  -6.8050, 39.1250],
    ['DSM-022','Dar-022 Kinondoni Manzese',     'Dar es Salaam','Ubungo',   'Manzese Argentina, opposite mosque',   -6.7900, 39.1900],
    ['DSM-023','Dar-023 Ilala Kariakoo',        'Dar es Salaam','Ilala',    'Kariakoo market roof, block 12',       -6.8180, 39.2760],
    ['DSM-024','Dar-024 Ilala Buguruni',        'Dar es Salaam','Ilala',    'Buguruni Malapa, plot 7B',             -6.8412, 39.2650],
    ['DSM-025','Dar-025 Ilala Vingunguti',      'Dar es Salaam','Ilala',    'Vingunguti industrial area',           -6.8600, 39.2500],
    ['DSM-026','Dar-026 Ilala Tabata',          'Dar es Salaam','Ilala',    'Tabata Segerea, near Mtaa office',     -6.8450, 39.2200],
    ['DSM-027','Dar-027 Ilala Chanika',         'Dar es Salaam','Ilala',    'Chanika highway, TANESCO junction',    -6.8800, 39.1300],
    ['DSM-028','Dar-028 Temeke Central',        'Dar es Salaam','Temeke',   'Temeke DC office roof',                -6.8500, 39.2790],
    ['DSM-029','Dar-029 Temeke Mbagala',        'Dar es Salaam','Temeke',   'Mbagala Zakhem, opposite market',      -6.9080, 39.2650],
    ['DSM-030','Dar-030 Temeke Chang\'ombe',    'Dar es Salaam','Temeke',   'Chang\'ombe TAZARA junction',          -6.8770, 39.2530],
    ['DSM-031','Dar-031 Temeke Kurasini',       'Dar es Salaam','Temeke',   'Kurasini port road, plot 5',           -6.8320, 39.2900],
    ['DSM-032','Dar-032 Temeke Yombo',          'Dar es Salaam','Temeke',   'Yombo Vituka, near police',            -6.8900, 39.2400],
    ['DSM-033','Dar-033 Kigamboni Ferry',       'Dar es Salaam','Kigamboni','Kigamboni ferry, ground-mount',        -6.8400, 39.3000],
    ['DSM-034','Dar-034 Kigamboni Kibada',      'Dar es Salaam','Kigamboni','Kibada, opposite mosque',              -6.9500, 39.3200],
    ['DSM-035','Dar-035 Ubungo Mabibo',         'Dar es Salaam','Ubungo',   'Mabibo hostel, TAZARA roof',           -6.8200, 39.2100],
    ['DSM-036','Dar-036 Ubungo Makuburi',       'Dar es Salaam','Ubungo',   'Makuburi TAZARA yard, ground-mount',   -6.8300, 39.2050],
    ['DSM-037','Dar-037 Kinondoni Kawe',        'Dar es Salaam','Kinondoni','Kawe beach, tower type II',            -6.7290, 39.2360],
    ['DSM-038','Dar-038 Kinondoni Tegeta',      'Dar es Salaam','Kinondoni','Tegeta A, near Nyerere school',        -6.6600, 39.2200],
    ['DSM-039','Dar-039 Ilala Mchikichini',     'Dar es Salaam','Ilala',    'Mchikichini ward office',              -6.8360, 39.2680],
    ['DSM-040','Dar-040 Temeke Tandika',        'Dar es Salaam','Temeke',   'Tandika Sokoni, plot 22',              -6.8730, 39.2620],

    /* Morogoro (10) ───────────────────────────────────────── */
    ['MRG-041','Morogoro-041 Urban Central',    'Morogoro','Morogoro Urban','Boma rd, opposite RC office',         -6.8235, 37.6610],
    ['MRG-042','Morogoro-042 Urban Sabasaba',   'Morogoro','Morogoro Urban','Sabasaba grounds, ground-mount',       -6.8300, 37.6520],
    ['MRG-043','Morogoro-043 Urban Kingolwira', 'Morogoro','Morogoro Urban','Kingolwira, near old Sisal factory',   -6.8500, 37.6600],
    ['MRG-044','Morogoro-044 Rural Mikese',     'Morogoro','Morogoro Rural','Mikese highway junction',              -6.7500, 37.9200],
    ['MRG-045','Morogoro-045 Rural Mvuha',      'Morogoro','Morogoro Rural','Mvuha ward, near dispensary',          -7.4500, 37.6800],
    ['MRG-046','Morogoro-046 Kilosa Central',   'Morogoro','Kilosa',        'Kilosa town, near TRC station',        -6.8360, 36.9840],
    ['MRG-047','Morogoro-047 Kilosa Mikumi',    'Morogoro','Kilosa',        'Mikumi park entrance, ground-mount',   -7.4000, 36.9800],
    ['MRG-048','Morogoro-048 Kilombero Ifakara','Morogoro','Kilombero',     'Ifakara town, opposite bank',          -8.1300, 36.6800],
    ['MRG-049','Morogoro-049 Kilombero Mang\'ula','Morogoro','Kilombero',  'Mang\'ula village center',             -7.8200, 36.9000],
    ['MRG-050','Morogoro-050 Kilosa Kimamba',   'Morogoro','Kilosa',        'Kimamba junction, TAZARA line',        -6.7900, 37.1800],
];

/* ── POC catalogue · rotated across sites ─────────────────────── */
$pocPool = [
    ['SITE_MANAGER','John Mwanza',    '+255712345001','john.mwanza@client.tz'],
    ['SITE_MANAGER','Halima Said',    '+255712345002','halima.said@client.tz'],
    ['SITE_MANAGER','Grace Mushi',    '+255712345003','grace.mushi@client.tz'],
    ['SITE_MANAGER','Peter Ndege',    '+255712345004','peter.ndege@client.tz'],
    ['SITE_MANAGER','Amina Juma',     '+255712345005','amina.juma@client.tz'],
    ['SITE_MANAGER','David Kileo',    '+255712345006','david.kileo@client.tz'],
    ['SITE_MANAGER','Rehema Mtega',   '+255712345007','rehema.mtega@client.tz'],
    ['SITE_MANAGER','Joseph Lyimo',   '+255712345008','joseph.lyimo@client.tz'],
    ['TECH',        'Emmanuel Ally',  '+255713111001','emmanuel.ally@client.tz'],
    ['TECH',        'Fatuma Kimario', '+255713111002','fatuma.kimario@client.tz'],
    ['TECH',        'Baraka Massawe', '+255713111003','baraka.massawe@client.tz'],
    ['TECH',        'Neema Chuwa',    '+255713111004','neema.chuwa@client.tz'],
];

$accessNotes = [
    'Gate access via security at main road; escort required after 18:00',
    'Site key held at branch office · call ahead',
    'BTS on tower; ladder access requires TSSR present',
    'Roof access via building manager (Mon–Fri 08:00–17:00 only)',
    '',
    'Site 40km from town · plan half-day trip',
    'Landowner requires 24-hr advance notice for access',
    'Ground-mount inside fenced compound; padlock combo with FS',
    '',
    'Access via rear alley; front gate locked after hours',
];

/* ── Insert ───────────────────────────────────────────────────── */
$added = 0; $confirmed = 0;
foreach ($sites as $i => $row) {
    [$code, $name, $region, $district, $address, $lat, $lng] = $row;
    $sid = (int)$sc->saveSite([
        'contract_id'      => $CID,
        'site_code'        => $code,
        'name'             => $name,
        'physical_address' => $address,
        'region'           => $region,
        'district'         => $district,
        'gps_lat'          => $lat,
        'gps_lng'          => $lng,
        'access_notes'     => $accessNotes[$i % count($accessNotes)],
        'is_active'        => 1,
    ]);
    if ($sid <= 0) { echo "✗ failed to save $code\n"; continue; }
    $added++;

    // 1 primary POC per site — rotate through the pool.
    [$role, $pname, $pphone, $pemail] = $pocPool[$i % count($pocPool)];
    $pm->save($sid, [
        'poc_role'   => $role,
        'poc_name'   => $pname,
        'poc_phone'  => $pphone,
        'poc_email'  => $pemail,
        'is_primary' => 1,
    ], $UID);

    // Every 4th site gets a secondary TECH POC (rough Vodacom ratio).
    if ($i % 4 === 0 && $role !== 'TECH') {
        $j = ($i + 8) % count($pocPool);
        [$role2, $pname2, $pphone2, $pemail2] = $pocPool[$j];
        $pm->save($sid, [
            'poc_role'   => 'TECH',
            'poc_name'   => $pname2,
            'poc_phone'  => $pphone2,
            'poc_email'  => $pemail2,
            'is_primary' => 0,
        ], $UID);
    }

    // ~30% pre-confirmed so PC has a mixed queue.
    if ($i % 3 === 0) {
        $pm->confirmSite($sid, $UID);
        $confirmed++;
    }
}

echo "\n=== Seeded ===\n";
echo "  Sites added:      $added\n";
echo "  POCs total:       " . count($pm->listForContract($CID)) . "\n";
echo "  Pre-confirmed:    $confirmed (rest await PC / PM confirmation)\n";
echo "\nOpen /Projects/sites/{$CID} to inspect.\n";
