<?php
/**
 * Service (Field Service) Migration
 * http://localhost/acc-engine/run-service-migrate.php
 */
declare(strict_types=1);

require_once __DIR__ . '/standalone/config.php';

try {
    $pdo = new PDO( ACC_DB_DSN, ACC_DB_USER, ACC_DB_PASS, [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ] );
    $pdo->exec( 'SET NAMES utf8mb4' );
} catch ( Throwable $e ) {
    die( '❌ DB: ' . $e->getMessage() );
}

$prefix  = ACC_DB_PREFIX;
$charset = 'ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';
$results = [];

function run_sql( PDO $pdo, string $label, string $sql ): void {
    global $results;
    try {
        $pdo->exec( $sql );
        $results[] = [ 'ok', $label ];
    } catch ( Throwable $e ) {
        $results[] = [ 'err', $label . ': ' . $e->getMessage() ];
    }
}

function table_exists( PDO $pdo, string $table ): bool {
    $stmt = $pdo->prepare( 'SHOW TABLES LIKE ?' );
    $stmt->execute( [ $table ] );
    return (bool) $stmt->fetch();
}

// ── svc_devices ──
if ( ! table_exists( $pdo, $prefix . 'svc_devices' ) ) {
    run_sql( $pdo, 'svc_devices',
        "CREATE TABLE `{$prefix}svc_devices` (
            id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            site_label       VARCHAR(200) NOT NULL DEFAULT '',
            customer_name    VARCHAR(200) NOT NULL DEFAULT '',
            phone            VARCHAR(20)  NOT NULL DEFAULT '',
            device_type      VARCHAR(100) NOT NULL DEFAULT '',
            brand            VARCHAR(100) NOT NULL DEFAULT '',
            model            VARCHAR(100) NOT NULL DEFAULT '',
            serial_no        VARCHAR(100) NOT NULL DEFAULT '',
            capacity         VARCHAR(50)  NOT NULL DEFAULT '',
            refrigerant_type VARCHAR(50)  NOT NULL DEFAULT '',
            install_location VARCHAR(200) NOT NULL DEFAULT '',
            operating_hours  INT UNSIGNED NOT NULL DEFAULT 0,
            last_service_date DATE NULL,
            notes            TEXT,
            status           VARCHAR(30)  NOT NULL DEFAULT 'active',
            created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY serial_no (serial_no),
            KEY site_label (site_label)
        ) {$charset}"
    );
} else {
    $results[] = [ 'skip', 'svc_devices exists' ];
}

// ── svc_requests ──
if ( ! table_exists( $pdo, $prefix . 'svc_requests' ) ) {
    run_sql( $pdo, 'svc_requests',
        "CREATE TABLE `{$prefix}svc_requests` (
            id                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            request_no             VARCHAR(30)  NOT NULL DEFAULT '',
            tracking_code          VARCHAR(6)   NOT NULL DEFAULT '',
            conversation_id        BIGINT UNSIGNED NULL,
            session_key            VARCHAR(64)  NOT NULL DEFAULT '',
            service_type           VARCHAR(30)  NOT NULL DEFAULT 'repair',
            status                 VARCHAR(30)  NOT NULL DEFAULT 'submitted',
            customer_name          VARCHAR(200) NOT NULL DEFAULT '',
            company                VARCHAR(200) NOT NULL DEFAULT '',
            phone                  VARCHAR(20)  NOT NULL DEFAULT '',
            email                  VARCHAR(200) NOT NULL DEFAULT '',
            address                TEXT,
            site_label             VARCHAR(200) NOT NULL DEFAULT '',
            device_id              BIGINT UNSIGNED NULL,
            device_type            VARCHAR(100) NOT NULL DEFAULT '',
            brand                  VARCHAR(100) NOT NULL DEFAULT '',
            model                  VARCHAR(100) NOT NULL DEFAULT '',
            serial_no              VARCHAR(100) NOT NULL DEFAULT '',
            capacity               VARCHAR(50)  NOT NULL DEFAULT '',
            refrigerant            VARCHAR(50)  NOT NULL DEFAULT '',
            install_location       VARCHAR(200) NOT NULL DEFAULT '',
            complaint_text         TEXT,
            complaint_tags         JSON,
            assigned_technician_id BIGINT UNSIGNED NULL,
            created_at             DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at             DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY tracking_code (tracking_code),
            UNIQUE KEY request_no (request_no),
            KEY session_key (session_key),
            KEY phone (phone),
            KEY status (status),
            KEY device_id (device_id)
        ) {$charset}"
    );
} else {
    $results[] = [ 'skip', 'svc_requests exists' ];
}

// ── svc_reports ──
if ( ! table_exists( $pdo, $prefix . 'svc_reports' ) ) {
    run_sql( $pdo, 'svc_reports',
        "CREATE TABLE `{$prefix}svc_reports` (
            id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            report_no           VARCHAR(30)  NOT NULL DEFAULT '',
            request_id          BIGINT UNSIGNED NULL,
            device_id           BIGINT UNSIGNED NULL,
            service_type        VARCHAR(30)  NOT NULL DEFAULT 'repair',
            status              ENUM('draft','final') NOT NULL DEFAULT 'draft',
            report_date         DATE NULL,
            customer_name       VARCHAR(200) NOT NULL DEFAULT '',
            company             VARCHAR(200) NOT NULL DEFAULT '',
            phone               VARCHAR(20)  NOT NULL DEFAULT '',
            email               VARCHAR(200) NOT NULL DEFAULT '',
            address             TEXT,
            site_label          VARCHAR(200) NOT NULL DEFAULT '',
            case_no             VARCHAR(30)  NOT NULL DEFAULT '',
            complaint_text      TEXT,
            complaint_tags      JSON,
            device_snapshot     JSON,
            technical_readings  JSON,
            diagnosis_result    TEXT,
            service_lines       JSON,
            part_lines          JSON,
            material_lines      JSON,
            extra_costs         JSON,
            recommendations     JSON,
            pm_offer            JSON,
            subtotal_services   DECIMAL(15,2) NOT NULL DEFAULT 0,
            subtotal_parts      DECIMAL(15,2) NOT NULL DEFAULT 0,
            subtotal_materials  DECIMAL(15,2) NOT NULL DEFAULT 0,
            subtotal_extra      DECIMAL(15,2) NOT NULL DEFAULT 0,
            discount            DECIMAL(15,2) NOT NULL DEFAULT 0,
            tax                 DECIMAL(15,2) NOT NULL DEFAULT 0,
            total               DECIMAL(15,2) NOT NULL DEFAULT 0,
            paid                DECIMAL(15,2) NOT NULL DEFAULT 0,
            balance             DECIMAL(15,2) NOT NULL DEFAULT 0,
            tech_signature      TEXT,
            customer_signature  TEXT,
            public_token        VARCHAR(64) NOT NULL DEFAULT '',
            created_by          BIGINT UNSIGNED NOT NULL DEFAULT 0,
            finalized_at        DATETIME NULL,
            created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY report_no (report_no),
            UNIQUE KEY public_token (public_token),
            KEY request_id (request_id),
            KEY device_id (device_id),
            KEY status (status)
        ) {$charset}"
    );
} else {
    $results[] = [ 'skip', 'svc_reports exists' ];
}

// ── ACL menu ──
$menuTable = $prefix . 'acl_menus';
if ( table_exists( $pdo, $menuTable ) ) {
    $stmt = $pdo->prepare( "SELECT id FROM `{$menuTable}` WHERE menu_key = 'service'" );
    $stmt->execute();
    if ( ! $stmt->fetch() ) {
        run_sql( $pdo, 'acl menu service',
            "INSERT INTO `{$menuTable}` (menu_key, parent_key, title, icon, route, required_permission, sort_order, is_active)
             VALUES ('service', '', '🔧 خدمات میدانی', '🔧', 'service', 'service.view', 16, 1)"
        );
    } else {
        $results[] = [ 'skip', 'menu service exists' ];
    }
}

// ── ACL permissions ──
$permTable = $prefix . 'acl_permissions';
if ( table_exists( $pdo, $permTable ) ) {
    $perms = [
        [ 'service.view', 'مشاهده خدمات میدانی', 'service', 'service', 'view' ],
        [ 'service.requests.manage', 'مدیریت درخواست‌ها', 'service', 'requests', 'manage' ],
        [ 'service.reports.manage', 'مدیریت گزارش فنی', 'service', 'reports', 'manage' ],
        [ 'service.devices.manage', 'مدیریت دستگاه‌ها', 'service', 'devices', 'manage' ],
    ];
    foreach ( $perms as $p ) {
        $stmt = $pdo->prepare( "SELECT id FROM `{$permTable}` WHERE permission_key = ?" );
        $stmt->execute( [ $p[0] ] );
        if ( ! $stmt->fetch() ) {
            run_sql( $pdo, 'perm ' . $p[0],
                "INSERT INTO `{$permTable}` (permission_key, label, module, resource, action, is_system)
                 VALUES ('{$p[0]}', '{$p[1]}', '{$p[2]}', '{$p[3]}', '{$p[4]}', 1)"
            );
        }
    }
}

// ── فاز ۲: dispatch + PM ──
$reqTable = $prefix . 'svc_requests';
if ( table_exists( $pdo, $reqTable ) ) {
    $cols = [
        [ 'scheduled_at', "DATETIME NULL AFTER assigned_technician_id" ],
        [ 'dispatch_notes', "TEXT NULL AFTER scheduled_at" ],
    ];
    foreach ( $cols as [ $col, $def ] ) {
        $stmt = $pdo->query( "SHOW COLUMNS FROM `{$reqTable}` LIKE '{$col}'" );
        if ( ! $stmt->fetch() ) {
            run_sql( $pdo, "add {$col}", "ALTER TABLE `{$reqTable}` ADD COLUMN `{$col}` {$def}" );
        }
    }
}

if ( ! table_exists( $pdo, $prefix . 'svc_pm_contracts' ) ) {
    run_sql( $pdo, 'svc_pm_contracts',
        "CREATE TABLE `{$prefix}svc_pm_contracts` (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            report_id       BIGINT UNSIGNED NULL,
            device_id       BIGINT UNSIGNED NULL,
            customer_name   VARCHAR(200) NOT NULL DEFAULT '',
            phone           VARCHAR(20)  NOT NULL DEFAULT '',
            site_label      VARCHAR(200) NOT NULL DEFAULT '',
            plan            VARCHAR(30)  NOT NULL DEFAULT 'monthly',
            status          VARCHAR(20)  NOT NULL DEFAULT 'active',
            next_visit_date DATE NULL,
            created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY next_visit (next_visit_date),
            KEY status (status)
        ) {$charset}"
    );
}

// ── فاز ۳: PM reminders + payments + cost chart ──
$devTable = $prefix . 'svc_devices';
if ( table_exists( $pdo, $devTable ) ) {
    $stmt = $pdo->query( "SHOW COLUMNS FROM `{$devTable}` LIKE 'estimated_value'" );
    if ( ! $stmt->fetch() ) {
        run_sql( $pdo, 'add estimated_value', "ALTER TABLE `{$devTable}` ADD COLUMN `estimated_value` DECIMAL(15,2) NOT NULL DEFAULT 0 AFTER `status`" );
    }
}

$pmTable = $prefix . 'svc_pm_contracts';
if ( table_exists( $pdo, $pmTable ) ) {
    $cols = [
        [ 'last_request_id', "BIGINT UNSIGNED NULL AFTER next_visit_date" ],
        [ 'last_reminder_at', "DATETIME NULL AFTER last_request_id" ],
    ];
    foreach ( $cols as [ $col, $def ] ) {
        $stmt = $pdo->query( "SHOW COLUMNS FROM `{$pmTable}` LIKE '{$col}'" );
        if ( ! $stmt->fetch() ) {
            run_sql( $pdo, "add {$col}", "ALTER TABLE `{$pmTable}` ADD COLUMN `{$col}` {$def}" );
        }
    }
}

if ( ! table_exists( $pdo, $prefix . 'svc_payments' ) ) {
    run_sql( $pdo, 'svc_payments',
        "CREATE TABLE `{$prefix}svc_payments` (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            report_id   BIGINT UNSIGNED NOT NULL,
            amount      DECIMAL(15,2) NOT NULL DEFAULT 0,
            authority   VARCHAR(64)  NOT NULL DEFAULT '',
            ref_id      VARCHAR(64)  NOT NULL DEFAULT '',
            status      VARCHAR(20)  NOT NULL DEFAULT 'pending',
            created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            paid_at     DATETIME NULL,
            PRIMARY KEY (id),
            KEY report_id (report_id),
            KEY authority (authority),
            KEY status (status)
        ) {$charset}"
    );
}

// ── فاز ۴: کارشناس ارشد / تیم ──
if ( ! table_exists( $pdo, $prefix . 'svc_technician_teams' ) ) {
    run_sql( $pdo, 'svc_technician_teams',
        "CREATE TABLE `{$prefix}svc_technician_teams` (
            id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            senior_user_id      BIGINT UNSIGNED NOT NULL,
            technician_user_id  BIGINT UNSIGNED NOT NULL,
            status              VARCHAR(20) NOT NULL DEFAULT 'active',
            created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY senior_tech (senior_user_id, technician_user_id),
            KEY senior_user_id (senior_user_id),
            KEY technician_user_id (technician_user_id),
            KEY status (status)
        ) {$charset}"
    );
} else {
    $results[] = [ 'skip', 'svc_technician_teams exists' ];
}

$permTable = $prefix . 'acl_permissions';
if ( table_exists( $pdo, $permTable ) ) {
    $newPerms = [
        [ 'service.senior.manage', 'پنل کارشناس ارشد', 'service', 'senior', 'manage' ],
        [ 'service.technician.panel', 'پنل کارشناس میدانی', 'service', 'technician', 'panel' ],
        [ 'service.dispatch', 'Dispatch خدمات میدانی', 'service', 'dispatch', 'manage' ],
    ];
    foreach ( $newPerms as $p ) {
        $stmt = $pdo->prepare( "SELECT id FROM `{$permTable}` WHERE permission_key = ?" );
        $stmt->execute( [ $p[0] ] );
        if ( ! $stmt->fetch() ) {
            run_sql( $pdo, 'perm ' . $p[0],
                "INSERT INTO `{$permTable}` (permission_key, label, module, resource, action, is_system)
                 VALUES ('{$p[0]}', '{$p[1]}', '{$p[2]}', '{$p[3]}', '{$p[4]}', 1)"
            );
        }
    }
}

$roleTable = $prefix . 'acl_roles';
if ( table_exists( $pdo, $roleTable ) ) {
    $roles = [
        [ 'service_senior', 'کارشناس ارشد خدمات', 'مدیریت تیم کارشناسان میدانی', 10 ],
        [ 'service_technician', 'کارشناس میدانی', 'پاسخگویی به درخواست‌های تخصیص‌یافته', 11 ],
    ];
    foreach ( $roles as $r ) {
        $stmt = $pdo->prepare( "SELECT id FROM `{$roleTable}` WHERE slug = ?" );
        $stmt->execute( [ $r[0] ] );
        if ( ! $stmt->fetch() ) {
            run_sql( $pdo, 'role ' . $r[0],
                "INSERT INTO `{$roleTable}` (slug, name, description, sort_order, is_system)
                 VALUES ('{$r[0]}', '{$r[1]}', '{$r[2]}', {$r[3]}, 1)"
            );
        }
    }
    $rolePermMap = [
        'service_senior' => [ 'service.view', 'service.senior.manage', 'service.requests.manage', 'service.reports.manage', 'service.dispatch' ],
        'service_technician' => [ 'service.view', 'service.technician.panel' ],
    ];
    foreach ( $rolePermMap as $slug => $perms ) {
        $rid = $pdo->query( "SELECT id FROM `{$roleTable}` WHERE slug = '{$slug}' LIMIT 1" )->fetchColumn();
        if ( ! $rid ) continue;
        foreach ( $perms as $pk ) {
            try {
                $pdo->prepare( "INSERT IGNORE INTO `{$prefix}acl_role_permissions` (role_id, permission_key) VALUES (?, ?)" )
                    ->execute( [ (int) $rid, $pk ] );
                $results[] = [ 'ok', "ACL {$slug} → {$pk}" ];
            } catch ( Throwable $e ) {
                $results[] = [ 'err', "ACL {$slug}: " . $e->getMessage() ];
            }
        }
    }
}

// ── ACL: مجوزهای service برای super_admin ──
$superRoleId = $pdo->query( "SELECT id FROM `{$prefix}acl_roles` WHERE slug = 'super_admin' LIMIT 1" )->fetchColumn();
if ( $superRoleId ) {
    $svcPerms = [ 'service.view', 'service.requests.manage', 'service.reports.manage', 'service.devices.manage',
        'service.senior.manage', 'service.technician.panel', 'service.dispatch' ];
    foreach ( $svcPerms as $pk ) {
        $stmt = $pdo->prepare(
            "INSERT IGNORE INTO `{$prefix}acl_role_permissions` (role_id, permission_key) VALUES (?, ?)"
        );
        try {
            $stmt->execute( [ (int) $superRoleId, $pk ] );
            $results[] = [ 'ok', "ACL super_admin → {$pk}" ];
        } catch ( Throwable $e ) {
            $results[] = [ 'err', "ACL {$pk}: " . $e->getMessage() ];
        }
    }
    // همگام کامل همه مجوزها با super_admin
    try {
        $pdo->exec(
            "INSERT IGNORE INTO `{$prefix}acl_role_permissions` (role_id, permission_key)
             SELECT " . (int) $superRoleId . ", permission_key FROM `{$prefix}acl_permissions`"
        );
        $results[] = [ 'ok', 'ACL super_admin full sync' ];
    } catch ( Throwable $e ) {
        $results[] = [ 'err', 'ACL sync: ' . $e->getMessage() ];
    }
}

?>
<!DOCTYPE html>
<html dir="rtl" lang="fa">
<head><meta charset="utf-8"><title>Service Migration</title>
<style>
body{font-family:Tahoma,sans-serif;padding:24px;background:#f8fafc;direction:rtl}
h2{color:#1e40af}ul{list-style:none;padding:0}li{padding:6px 12px;margin:4px 0;border-radius:6px;font-size:14px}
.ok{background:#dcfce7;color:#166534}.err{background:#fee2e2;color:#991b1b}.skip{background:#f1f5f9;color:#64748b}
.done{background:#1e40af;color:#fff;padding:12px 20px;border-radius:8px;margin-top:20px;display:inline-block}
</style></head>
<body>
<h2>🔧 Migration خدمات میدانی</h2>
<ul>
<?php foreach ( $results as [ $type, $msg ] ): ?>
<li class="<?= $type ?>"><?= htmlspecialchars( $msg ) ?></li>
<?php endforeach; ?>
</ul>
<?php if ( ! array_filter( $results, fn( $r ) => $r[0] === 'err' ) ): ?>
<p class="done">✅ Migration موفق — SR_Controller و ویجت را تست کنید.</p>
<?php endif; ?>
</body></html>
