<?php
/**
 * ارتقاء v3 — صندوق‌محور، فازها، POI، تشکیلات ستاد
 * C:\xampp\php\php.exe run-election-v3-install.php
 */
declare(strict_types=1);

if ( ! defined( 'ACC_BASE_PATH' ) ) {
    define( 'ACC_BASE_PATH', __DIR__ );
    require __DIR__ . '/standalone/bootstrap.php';
}

header( 'Content-Type: application/json; charset=utf-8' );
global $wpdb;

require_once ACC_PATH . 'includes/helpers/election-havadaran.php';

$pre     = $wpdb->prefix;
$charset = 'DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';
$log     = [];

$ddl = [
    "CREATE TABLE IF NOT EXISTS {$pre}election_districts (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        code VARCHAR(20) NOT NULL,
        name VARCHAR(120) NOT NULL,
        manager_user_id BIGINT UNSIGNED NULL,
        sort_order INT UNSIGNED NOT NULL DEFAULT 0,
        PRIMARY KEY (id),
        UNIQUE KEY idx_code (code)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}election_user_stations (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        user_id BIGINT UNSIGNED NOT NULL,
        station_id BIGINT UNSIGNED NOT NULL,
        role VARCHAR(30) NOT NULL DEFAULT 'supporter',
        is_primary TINYINT(1) NOT NULL DEFAULT 0,
        assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY idx_user_station (user_id, station_id),
        KEY idx_station (station_id)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}election_station_pois (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        station_id BIGINT UNSIGNED NOT NULL,
        user_id BIGINT UNSIGNED NOT NULL,
        poi_type VARCHAR(30) NOT NULL DEFAULT 'other',
        name VARCHAR(200) NOT NULL,
        address TEXT NULL,
        notes TEXT NULL,
        phase VARCHAR(30) NULL,
        verified TINYINT(1) NOT NULL DEFAULT 0,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY idx_station (station_id),
        KEY idx_user (user_id)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}election_phase_config (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        current_phase VARCHAR(30) NOT NULL DEFAULT 'pre_campaign',
        pre_campaign_end DATE NULL,
        campaign_start DATE NULL,
        campaign_end DATE NULL,
        election_date DATE NULL,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}election_station_tasks (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        user_id BIGINT UNSIGNED NOT NULL,
        station_id BIGINT UNSIGNED NOT NULL,
        phase VARCHAR(30) NOT NULL,
        task_type VARCHAR(40) NOT NULL,
        title VARCHAR(200) NOT NULL,
        body TEXT NOT NULL,
        status VARCHAR(30) NOT NULL DEFAULT 'pending',
        completed_at DATETIME NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY idx_user_phase (user_id, phase),
        KEY idx_station (station_id)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}election_briefings (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        title VARCHAR(200) NOT NULL,
        body TEXT NOT NULL,
        briefing_type VARCHAR(30) NOT NULL DEFAULT 'instruction',
        district_id BIGINT UNSIGNED NULL,
        station_id BIGINT UNSIGNED NULL,
        phase VARCHAR(30) NULL,
        audience VARCHAR(30) NOT NULL DEFAULT 'supporter',
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY idx_phase (phase),
        KEY idx_station (station_id)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}election_shipments (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        station_id BIGINT UNSIGNED NOT NULL,
        user_id BIGINT UNSIGNED NULL,
        package_id BIGINT UNSIGNED NULL,
        phase VARCHAR(30) NOT NULL DEFAULT 'campaign',
        title VARCHAR(200) NULL,
        status VARCHAR(30) NOT NULL DEFAULT 'pending',
        shipped_at DATETIME NULL,
        received_at DATETIME NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY idx_station (station_id),
        KEY idx_user (user_id)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}election_org_units (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        parent_id BIGINT UNSIGNED NULL,
        unit_type VARCHAR(40) NOT NULL DEFAULT 'unit',
        title VARCHAR(200) NOT NULL,
        slug VARCHAR(80) NULL,
        sort_order INT UNSIGNED NOT NULL DEFAULT 0,
        PRIMARY KEY (id),
        KEY idx_parent (parent_id)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}election_org_assignments (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        unit_id BIGINT UNSIGNED NOT NULL,
        user_id BIGINT UNSIGNED NULL,
        title_in_unit VARCHAR(120) NOT NULL,
        sort_order INT UNSIGNED NOT NULL DEFAULT 0,
        PRIMARY KEY (id),
        KEY idx_unit (unit_id)
    ) {$charset}",
];

foreach ( $ddl as $sql ) {
    $wpdb->query( $sql );
    $log[] = 'table_ok';
}

// Enhance stations table
$st = Election_Havadaran::stations_table();
$cols = [
    'district_id'         => 'BIGINT UNSIGNED NULL',
    'gis_neighborhood_id' => 'BIGINT UNSIGNED NULL',
    'voter_count'         => 'INT UNSIGNED NOT NULL DEFAULT 0',
    'manager_user_id'     => 'BIGINT UNSIGNED NULL',
];
foreach ( $cols as $col => $def ) {
    if ( ! $wpdb->get_var( "SHOW COLUMNS FROM {$st} LIKE '{$col}'" ) ) {
        $wpdb->query( "ALTER TABLE {$st} ADD COLUMN {$col} {$def}" );
        $log[] = "station_col:{$col}";
    }
}

// Enhance reports
$rt = Election_Havadaran::reports_table();
foreach ( [
    'station_id' => 'BIGINT UNSIGNED NULL',
    'phase'      => 'VARCHAR(30) NULL',
] as $col => $def ) {
    if ( ! $wpdb->get_var( "SHOW COLUMNS FROM {$rt} LIKE '{$col}'" ) ) {
        $wpdb->query( "ALTER TABLE {$rt} ADD COLUMN {$col} {$def}" );
    }
}

// Districts (شیراز — مناطق شهرداری نمونه)
$dt = Election_Havadaran::districts_table();
$districts = [
    [ 'D-1', 'منطقه ۱ — مرکزی', 1 ],
    [ 'D-2', 'منطقه ۲ — معالی‌آباد', 2 ],
    [ 'D-3', 'منطقه ۳ — درکی', 3 ],
    [ 'D-4', 'منطقه ۴ — ستارخان', 4 ],
];
$district_ids = [];
foreach ( $districts as [ $code, $name, $ord ] ) {
    $exists = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$dt} WHERE code=%s", $code ) );
    if ( $exists ) {
        $wpdb->update( $dt, [ 'name' => $name, 'sort_order' => $ord ], [ 'id' => $exists ] );
        $district_ids[ $code ] = $exists;
    } else {
        $wpdb->insert( $dt, [ 'code' => $code, 'name' => $name, 'sort_order' => $ord ] );
        $district_ids[ $code ] = (int) $wpdb->insert_id;
    }
}

// Link stations to districts + voter counts
$station_map = [
    'S-101' => [ 'D-1', 850 ],
    'S-102' => [ 'D-2', 920 ],
    'S-103' => [ 'D-3', 780 ],
    'S-104' => [ 'D-4', 1100 ],
];
foreach ( $station_map as $code => [ $dcode, $voters ] ) {
    $wpdb->query( $wpdb->prepare(
        "UPDATE {$st} SET district_id = %d, voter_count = %d WHERE code = %s",
        $district_ids[ $dcode ], $voters, $code
    ) );
}

// Phase config
$pt = Election_Havadaran::phase_config_table();
if ( ! (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$pt}" ) ) {
    $wpdb->insert( $pt, [
        'current_phase'    => 'pre_campaign',
        'pre_campaign_end' => '2026-07-24',
        'campaign_start'   => '2026-07-25',
        'campaign_end'     => '2026-08-03',
        'election_date'    => '2026-08-04',
    ] );
}

// User IDs
$user_ids = [];
foreach ( [
    'election_admin', 'election_branch_waste', 'election_branch_green', 'election_branch_dense',
    'election_supporter_01', 'election_supporter_02', 'election_supporter_03',
] as $login ) {
    $user_ids[ $login ] = (int) $wpdb->get_var( $wpdb->prepare(
        "SELECT ID FROM {$wpdb->users} WHERE user_login = %s", $login
    ) );
}

// District managers
$wpdb->update( $dt, [ 'manager_user_id' => $user_ids['election_branch_waste'] ?? null ], [ 'code' => 'D-1' ] );
$wpdb->update( $dt, [ 'manager_user_id' => $user_ids['election_branch_green'] ?? null ], [ 'code' => 'D-2' ] );
$wpdb->update( $dt, [ 'manager_user_id' => $user_ids['election_branch_dense'] ?? null ], [ 'code' => 'D-3' ] );

// Station managers on stations table
$station_ids = [];
foreach ( [ 'S-101', 'S-102', 'S-103', 'S-104' ] as $code ) {
    $station_ids[ $code ] = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$st} WHERE code=%s", $code ) );
}

// Multi-station assignments
$assignments = [
    'election_supporter_01' => [ 'S-101', 'S-102' ],
    'election_supporter_02' => [ 'S-102', 'S-103' ],
    'election_supporter_03' => [ 'S-103', 'S-104' ],
    'election_branch_waste' => [ 'S-101' ],
    'election_branch_green' => [ 'S-102', 'S-103' ],
    'election_branch_dense' => [ 'S-104' ],
];
foreach ( $assignments as $login => $codes ) {
    $uid = $user_ids[ $login ] ?? 0;
    if ( ! $uid ) continue;
    $sids = array_values( array_filter( array_map( fn( $c ) => $station_ids[ $c ] ?? 0, $codes ) ) );
    $role = str_contains( $login, 'branch' ) ? 'district_lead' : 'supporter';
    Election_Havadaran::assign_stations_to_user( $uid, $sids, $role );
    $log[] = "assign:{$login}=" . count( $sids );
}

// Sample POIs
$poi_t = Election_Havadaran::station_pois_table();
$sample_pois = [
    [ 'S-101', 'bakery', 'نانوایی سنگک زند', 'بلوار زند، نبش کوچه ۵' ],
    [ 'S-101', 'mosque', 'مسجد جامع زند', 'خیابان زند' ],
    [ 'S-101', 'school', 'دبستان شهید فلاح', 'جنب صندوق ۱۰۱' ],
    [ 'S-102', 'bakery', 'نانوایی فرهنگ‌شهر', 'معالی‌آباد' ],
    [ 'S-102', 'shop', 'مرکز خرید فرهنگ‌شهر', 'معالی‌آباد' ],
];
$sup_uid = $user_ids['election_supporter_01'] ?? 0;
foreach ( $sample_pois as [ $scode, $type, $name, $addr ] ) {
    $sid = $station_ids[ $scode ] ?? 0;
    if ( ! $sid || ! $sup_uid ) continue;
    $exists = (int) $wpdb->get_var( $wpdb->prepare(
        "SELECT id FROM {$poi_t} WHERE station_id=%d AND name=%s", $sid, $name
    ) );
    if ( ! $exists ) {
        $wpdb->insert( $poi_t, [
            'station_id' => $sid, 'user_id' => $sup_uid,
            'poi_type' => $type, 'name' => $name, 'address' => $addr,
            'phase' => 'pre_campaign', 'created_at' => current_time( 'mysql' ),
        ] );
    }
}

// Briefings (outbound: slogans + candidate actions)
$bt = Election_Havadaran::briefings_table();
$briefings = [
    [ 'شعار منطقه ۱ — زند', '«خدمت صادقانه، توسعه متوازن» — در محدوده صندوق‌های زند و معالی‌آباد', 'slogan', 'pre_campaign' ],
    [ 'اقدام کاندید در منطقه ۲', 'بازدید از پارک آزادی و وعده توسعه فضای سبز — برای هواداران فضای سبز', 'action', 'campaign' ],
    [ 'دستورالعمل روز انتخابات', 'حضور از ساعت ۷ صبح — تشویق مؤدبانه رأی‌دهندگان مردد — گزارش تا ۱۸', 'instruction', 'election_day' ],
];
foreach ( $briefings as [ $title, $body, $type, $phase ] ) {
    if ( ! $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$bt} WHERE title=%s", $title ) ) ) {
        $wpdb->insert( $bt, [
            'title' => $title, 'body' => $body, 'briefing_type' => $type,
            'phase' => $phase, 'audience' => 'supporter', 'created_at' => current_time( 'mysql' ),
        ] );
    }
}

// Shipments (campaign materials)
$sht = Election_Havadaran::shipments_table();
$pkg_id = (int) $wpdb->get_var( "SELECT id FROM " . Election_Havadaran::packages_table() . " WHERE size_type='21' LIMIT 1" );
foreach ( [ 'S-101', 'S-102', 'S-103' ] as $scode ) {
    $sid = $station_ids[ $scode ] ?? 0;
    if ( ! $sid ) continue;
    if ( ! $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$sht} WHERE station_id=%d", $sid ) ) ) {
        $wpdb->insert( $sht, [
            'station_id' => $sid,
            'user_id'    => $user_ids['election_supporter_01'] ?? null,
            'package_id' => $pkg_id ?: null,
            'phase'      => 'campaign',
            'title'      => 'بسته ۲۱ نفره — ' . $scode,
            'status'     => 'shipped',
            'shipped_at' => current_time( 'mysql' ),
            'created_at' => current_time( 'mysql' ),
        ] );
    }
}

// Org structure (ساختار اداری موقت)
$ut = Election_Havadaran::org_units_table();
$at = Election_Havadaran::org_assignments_table();

function elv3_org_unit( string $title, string $type, ?int $parent, int $ord ): int {
    global $wpdb, $ut;
    $exists = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$ut} WHERE title=%s AND parent_id <=> %s", $title, $parent ) );
    if ( $exists ) return $exists;
    $wpdb->insert( $ut, [
        'parent_id' => $parent, 'unit_type' => $type, 'title' => $title,
        'sort_order' => $ord,
    ] );
    return (int) $wpdb->insert_id;
}

if ( ! (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$ut}" ) ) {
    $central = elv3_org_unit( 'ستاد انتخابات', 'central', null, 1 );
    $units = [
        [ 'شورای هماهنگی', 'council', 1 ],
        [ 'واحد برنامه‌ریزی و هماهنگی تولید محتوا', 'unit', 2 ],
        [ 'حوزه رسانه و تبلیغات', 'unit', 3 ],
        [ 'حوزه مالی', 'unit', 4 ],
        [ 'حوزه اداری و پشتیبانی', 'unit', 5 ],
        [ 'سامانه‌های هوشمند (GIS/ERP)', 'unit', 6 ],
        [ 'حوزه شهرستان‌ها', 'county', 7 ],
        [ 'کمیته جوانان', 'committee', 8 ],
        [ 'کمیته اصناف', 'committee', 9 ],
        [ 'کمیته بانوان', 'committee', 10 ],
    ];
    $unit_ids = [];
    foreach ( $units as [ $title, $type, $ord ] ) {
        $unit_ids[] = elv3_org_unit( $title, $type, $central, $ord );
    }
    $admin_uid = $user_ids['election_admin'] ?? 0;
    if ( $admin_uid ) {
        $wpdb->insert( $at, [
            'unit_id' => $central, 'user_id' => $admin_uid,
            'title_in_unit' => 'مدیر سامانه هوادران', 'sort_order' => 1,
        ] );
    }
    $log[] = 'org_seeded';
}

echo json_encode( [
    'ok'       => true,
    'message'  => 'ارتقاء v3 — صندوق‌محور + فازها + POI + تشکیلات',
    'phase'    => Election_Havadaran::phase_info(),
    'districts'=> count( Election_Havadaran::list_districts() ),
    'stations' => count( Election_Havadaran::list_stations_enriched() ),
    'org'      => count( Election_Havadaran::org_tree() ),
    'log'      => $log,
], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT );
