<?php

/**

 * نصب/ارتقای جداول اقتصاد ملک (schema v17.6 — هم‌خوان با install.php)

 * C:\xampp\php\php.exe run-melk-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;



$pre     = $wpdb->prefix;

$charset = 'DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';

$migrated = [];



function melk_table_has_column( string $table, string $col ): bool {

    global $wpdb;

    $cols = $wpdb->get_col("SHOW COLUMNS FROM {$table}", 0);

    return in_array( $col, $cols ?: [], true );

}



$org_tbl = $pre . 'melk_org_assignments';

if ( $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $org_tbl)) ) {

    if ( ! melk_table_has_column( $org_tbl, 'unit_type' ) ) {

        $wpdb->query('DROP TABLE IF EXISTS ' . $pre . 'melk_case_notes');

        $wpdb->query('DROP TABLE IF EXISTS ' . $pre . 'melk_case_participants');

        $wpdb->query('DROP TABLE IF EXISTS ' . $pre . 'melk_cases');

        $wpdb->query('DROP TABLE IF EXISTS ' . $pre . 'melk_org_assignments');

        $migrated[] = 'dropped_legacy_melk_tables';

    }

}



$ddl = [

    "CREATE TABLE IF NOT EXISTS {$pre}melk_org_assignments (

        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

        unit_type VARCHAR(20) NOT NULL DEFAULT 'district',

        unit_id INT NOT NULL,

        user_id BIGINT UNSIGNED NOT NULL,

        role_slug VARCHAR(50) NOT NULL DEFAULT 'district_lead',

        user_name VARCHAR(120) NULL,

        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

        PRIMARY KEY (id),

        UNIQUE KEY idx_unit_role (unit_type, unit_id, role_slug),

        KEY idx_user (user_id)

    ) {$charset}",



    "CREATE TABLE IF NOT EXISTS {$pre}melk_cases (

        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

        case_key VARCHAR(40) NOT NULL,

        parcel_id INT DEFAULT NULL,

        title VARCHAR(200) NOT NULL,

        status VARCHAR(30) NOT NULL DEFAULT 'open',

        district_id INT DEFAULT NULL,

        neighborhood_id INT DEFAULT NULL,

        block_code VARCHAR(40) NULL,

        renovation_code VARCHAR(200) NULL,

        created_by BIGINT UNSIGNED NOT NULL DEFAULT 0,

        created_by_name VARCHAR(120) NULL,

        assigned_to BIGINT UNSIGNED NOT NULL DEFAULT 0,

        assigned_name VARCHAR(120) NULL,

        summary TEXT 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 case_key (case_key),

        KEY idx_parcel (parcel_id),

        KEY idx_district (district_id),

        KEY idx_neighborhood (neighborhood_id),

        KEY idx_status (status),

        KEY idx_renovation (renovation_code)

    ) {$charset}",



    "CREATE TABLE IF NOT EXISTS {$pre}melk_case_participants (

        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

        case_id BIGINT UNSIGNED NOT NULL,

        user_id BIGINT UNSIGNED NOT NULL,

        user_name VARCHAR(120) NULL,

        role_type VARCHAR(40) NOT NULL DEFAULT 'agent',

        added_by BIGINT UNSIGNED NULL,

        added_by_name VARCHAR(120) NULL,

        joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

        PRIMARY KEY (id),

        UNIQUE KEY idx_case_user (case_id, user_id),

        KEY idx_user (user_id)

    ) {$charset}",



    "CREATE TABLE IF NOT EXISTS {$pre}melk_case_notes (

        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

        case_id BIGINT UNSIGNED NOT NULL,

        user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,

        user_name VARCHAR(120) NULL,

        body TEXT NOT NULL,

        note_type VARCHAR(30) NOT NULL DEFAULT 'note',

        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

        PRIMARY KEY (id),

        KEY idx_case (case_id)

    ) {$charset}",

];



foreach ( $ddl as $sql ) {

    $wpdb->query( $sql );

}

$part_tbl = $pre . 'melk_case_participants';
$part_cols = $wpdb->get_col( "SHOW COLUMNS FROM {$part_tbl}" );
if ( $part_cols && ! in_array( 'added_by', $part_cols, true ) ) {
    $wpdb->query( "ALTER TABLE {$part_tbl} ADD COLUMN added_by BIGINT UNSIGNED NULL AFTER role_type" );
}
if ( $part_cols && ! in_array( 'added_by_name', $part_cols, true ) ) {
    $wpdb->query( "ALTER TABLE {$part_tbl} ADD COLUMN added_by_name VARCHAR(120) NULL AFTER added_by" );
}

$case_tbl = $pre . 'melk_cases';
$case_cols = $wpdb->get_col( "SHOW COLUMNS FROM {$case_tbl}" );
if ( $case_cols && ! in_array( 'renovation_code', $case_cols, true ) ) {
    $wpdb->query( "ALTER TABLE {$case_tbl} ADD COLUMN renovation_code VARCHAR(200) NULL AFTER block_code" );
    $wpdb->query( "ALTER TABLE {$case_tbl} ADD INDEX idx_renovation (renovation_code)" );
}



$menu = $wpdb->get_row($wpdb->prepare(

    "SELECT id, is_active FROM {$pre}acl_menus WHERE menu_key = %s", 'eqtesad_melk'

), ARRAY_A);

if ( $menu ) {

    $wpdb->update($pre . 'acl_menus', ['is_active' => 0], ['menu_key' => 'eqtesad_melk']);

} else {

    $wpdb->insert($pre . 'acl_menus', [

        'menu_key' => 'eqtesad_melk', 'parent_key' => '',

        'title' => '🏛️ اقتصاد ملک', 'icon' => '🏛️',

        'route' => 'eqtesad_melk', 'required_permission' => 'melk.view',

        'sort_order' => 18, 'is_active' => 0,

    ]);

}



// ── نقش‌ها و مجوزهای melk (اگر install.php کامل اجرا نشده) ──
$melk_roles = [
    ['district_lead',       'سرگروه منطقه',        'مدیریت منطقه و هماهنگی بنگاه',  12, 1],
    ['neighborhood_manager','مدیر محله',         'مدیریت محله و بلوک‌های ملک',     13, 1],
    ['melk_specialist',    'کارشناس مشترک ملک',   'حقوقی، شهرداری، ثبت و ...',      14, 1],
    ['melk_coordinator',   'هماهنگ‌کننده ملک',    'نظارت سراسری شبکه بنگاه',       15, 1],
];
foreach ( $melk_roles as $r ) {
    if ( ! $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$pre}acl_roles WHERE slug=%s", $r[0] ) ) ) {
        $wpdb->insert( "{$pre}acl_roles", [
            'slug' => $r[0], 'name' => $r[1], 'description' => $r[2],
            'sort_order' => $r[3], 'is_system' => $r[4],
        ] );
    }
}

$melk_perms = [
    ['melk.view', 'مشاهده پورتال اقتصاد ملک', 'melk', 'portal', 'view'],
    ['melk.manage', 'مدیریت سازمان بنگاه', 'melk', 'org', 'manage'],
    ['melk.cases.create', 'ایجاد پرونده ملک', 'melk', 'cases', 'create'],
    ['melk.cases.edit', 'ویرایش پرونده ملک', 'melk', 'cases', 'edit'],
    ['melk.cases.participate', 'مشارکت در پرونده (کارشناس)', 'melk', 'cases', 'participate'],
];
foreach ( $melk_perms as $p ) {
    if ( ! $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$pre}acl_permissions WHERE permission_key=%s", $p[0] ) ) ) {
        $wpdb->insert( "{$pre}acl_permissions", [
            'permission_key' => $p[0], 'label' => $p[1],
            'module' => $p[2], 'resource' => $p[3], 'action' => $p[4], 'is_system' => 1,
        ] );
    }
}

$melk_role_perms = [
    'district_lead'        => ['melk.view','melk.manage','melk.cases.create','melk.cases.edit','gis.view','gis.parcels.view','gis.parcels.edit','gis.parcels.lock'],
    'neighborhood_manager' => ['melk.view','melk.cases.create','melk.cases.edit','gis.view','gis.parcels.view','gis.parcels.edit','gis.parcels.lock'],
    'melk_specialist'      => ['melk.view','melk.cases.participate','gis.view','gis.parcels.view'],
    'melk_coordinator'     => ['melk.view','melk.manage','melk.cases.create','melk.cases.edit','melk.cases.participate','gis.view','gis.parcels.view','gis.parcels.edit'],
];
foreach ( $melk_role_perms as $slug => $perms ) {
    $role_id = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$pre}acl_roles WHERE slug=%s", $slug ) );
    if ( ! $role_id ) continue;
    foreach ( $perms as $pk ) {
        $wpdb->query( $wpdb->prepare(
            "INSERT IGNORE INTO {$pre}acl_role_permissions (role_id, permission_key) VALUES (%d, %s)",
            $role_id, $pk
        ) );
    }
}

$super_id = (int) $wpdb->get_var( "SELECT id FROM {$pre}acl_roles WHERE slug='super_admin' LIMIT 1" );
if ( $super_id ) {
    foreach ( array_column( $melk_perms, 0 ) as $pk ) {
        $wpdb->query( $wpdb->prepare(
            "INSERT IGNORE INTO {$pre}acl_role_permissions (role_id, permission_key) VALUES (%d, %s)",
            $super_id, $pk
        ) );
    }
}

// GIS perms may be missing too — ensure gis.* exist for melk roles
$gis_perms = [
    ['gis.view', 'مشاهده GIS', 'gis', 'map', 'view'],
    ['gis.parcels.view', 'مشاهده پارسل', 'gis', 'parcels', 'view'],
    ['gis.parcels.edit', 'ویرایش پارسل', 'gis', 'parcels', 'edit'],
    ['gis.parcels.lock', 'قفل پارسل', 'gis', 'parcels', 'lock'],
];
foreach ( $gis_perms as $p ) {
    if ( ! $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$pre}acl_permissions WHERE permission_key=%s", $p[0] ) ) ) {
        $wpdb->insert( "{$pre}acl_permissions", [
            'permission_key' => $p[0], 'label' => $p[1],
            'module' => $p[2], 'resource' => $p[3], 'action' => $p[4], 'is_system' => 1,
        ] );
    }
}



update_option('acc_engine_db_version', '17.7');



if ( empty( $GLOBALS['_melk_install_quiet'] ) ) {

echo json_encode([

    'success'    => true,

    'db_version' => get_option('acc_engine_db_version'),

    'migrated'   => $migrated,

    'tables'     => [

        'melk_org_assignments' => melk_table_has_column($org_tbl, 'unit_type') ? 'ok' : 'missing',

        'melk_cases'           => (bool) $wpdb->get_var("SHOW TABLES LIKE '{$pre}melk_cases'"),

        'melk_case_participants' => (bool) $wpdb->get_var("SHOW TABLES LIKE '{$pre}melk_case_participants'"),

        'melk_case_notes'      => (bool) $wpdb->get_var("SHOW TABLES LIKE '{$pre}melk_case_notes'"),

    ],

    'menu' => $wpdb->get_row($wpdb->prepare(

        "SELECT id, is_active FROM {$pre}acl_menus WHERE menu_key = %s", 'eqtesad_melk'

    ), ARRAY_A),

], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);

}

