<?php
/**
 * نصب جداول توسعه اقتصاد ملک — ساخت‌وساز، آگهی ملک، دسترسی
 * C:\xampp\php\php.exe run-melk-extensions-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';
$done    = [];

// افزودن ستون floors اگر وجود ندارد
function melk_ext_has_column(string $table, string $col): bool {
    global $wpdb;
    $cols = $wpdb->get_col("SHOW COLUMNS FROM `{$table}`", 0);
    return in_array($col, $cols ?: [], true);
}
$tbl = $pre . 'melk_construction_projects';
if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl)) && !melk_ext_has_column($tbl, 'floors')) {
    $wpdb->query("ALTER TABLE `{$tbl}` ADD COLUMN `floors` SMALLINT UNSIGNED DEFAULT NULL AFTER `area_sqm`");
    $done[] = 'alter:melk_construction_projects:floors';
}

$ddl = [
    "CREATE TABLE IF NOT EXISTS {$pre}melk_construction_projects (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        project_key VARCHAR(32) NOT NULL,
        title VARCHAR(200) NOT NULL,
        project_type VARCHAR(30) NOT NULL DEFAULT 'construction',
        status VARCHAR(30) NOT NULL DEFAULT 'draft',
        parcel_id INT DEFAULT NULL,
        case_id BIGINT UNSIGNED DEFAULT NULL,
        district_id INT DEFAULT NULL,
        neighborhood_id INT DEFAULT NULL,
        renovation_code VARCHAR(200) NULL,
        budget DECIMAL(18,2) DEFAULT NULL,
        area_sqm DECIMAL(12,2) DEFAULT NULL,
        floors SMALLINT UNSIGNED DEFAULT NULL,
        description TEXT NULL,
        created_by BIGINT UNSIGNED NOT NULL DEFAULT 0,
        created_by_name VARCHAR(120) 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 idx_project_key (project_key),
        KEY idx_status (status),
        KEY idx_district (district_id),
        KEY idx_created_by (created_by)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}melk_construction_participants (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        project_id BIGINT UNSIGNED NOT NULL,
        user_id BIGINT UNSIGNED NOT NULL,
        role_type VARCHAR(40) NOT NULL DEFAULT 'contractor',
        share_percent DECIMAL(5,2) DEFAULT NULL,
        status VARCHAR(30) NOT NULL DEFAULT 'active',
        notes VARCHAR(500) NULL,
        added_by BIGINT UNSIGNED NOT NULL DEFAULT 0,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY idx_proj_user (project_id, user_id),
        KEY idx_user (user_id)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}melk_listings (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        listing_key VARCHAR(32) NOT NULL,
        deal_type VARCHAR(30) NOT NULL DEFAULT 'sale',
        title VARCHAR(200) NOT NULL,
        description TEXT NULL,
        price DECIMAL(18,2) DEFAULT NULL,
        rent_period VARCHAR(30) NULL,
        area_sqm DECIMAL(12,2) DEFAULT NULL,
        rooms TINYINT UNSIGNED DEFAULT NULL,
        parcel_id INT DEFAULT NULL,
        district_id INT DEFAULT NULL,
        neighborhood_id INT DEFAULT NULL,
        renovation_code VARCHAR(200) NULL,
        visibility_mode VARCHAR(30) NOT NULL DEFAULT 'network',
        visibility_roles JSON NULL,
        visibility_user_ids JSON NULL,
        status VARCHAR(30) NOT NULL DEFAULT 'draft',
        created_by BIGINT UNSIGNED NOT NULL DEFAULT 0,
        created_by_name VARCHAR(120) 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 idx_listing_key (listing_key),
        KEY idx_deal (deal_type),
        KEY idx_status (status),
        KEY idx_district (district_id),
        KEY idx_created_by (created_by)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}melk_county_requests (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        request_key VARCHAR(32) NOT NULL,
        request_type VARCHAR(30) NOT NULL DEFAULT 'demand',
        title VARCHAR(200) NOT NULL,
        description TEXT NULL,
        priority VARCHAR(20) NOT NULL DEFAULT 'normal',
        status VARCHAR(30) NOT NULL DEFAULT 'draft',
        county VARCHAR(100) NOT NULL,
        city VARCHAR(100) NULL,
        agency_name VARCHAR(200) NULL,
        contact_phone VARCHAR(30) NULL,
        contact_email VARCHAR(120) NULL,
        assigned_to BIGINT UNSIGNED DEFAULT NULL,
        assigned_name VARCHAR(120) NULL,
        admin_note TEXT NULL,
        resolved_at DATETIME NULL,
        created_by BIGINT UNSIGNED NOT NULL DEFAULT 0,
        created_by_name VARCHAR(120) NULL,
        reviewed_by BIGINT UNSIGNED DEFAULT NULL,
        reviewed_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 idx_request_key (request_key),
        KEY idx_status (status),
        KEY idx_type (request_type),
        KEY idx_county (county),
        KEY idx_created_by (created_by)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}melk_building_specialists (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        user_id BIGINT UNSIGNED NOT NULL,
        specialty VARCHAR(40) NOT NULL DEFAULT 'other',
        license_no VARCHAR(80) NULL,
        years_experience SMALLINT UNSIGNED DEFAULT NULL,
        service_areas JSON NULL,
        county VARCHAR(100) NULL,
        city VARCHAR(100) NULL,
        contact_phone VARCHAR(30) NULL,
        contact_email VARCHAR(120) NULL,
        bio TEXT NULL,
        credentials TEXT NULL,
        status VARCHAR(30) NOT NULL DEFAULT 'pending_approval',
        approved_by BIGINT UNSIGNED DEFAULT NULL,
        approved_at DATETIME NULL,
        rejection_reason 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 idx_user (user_id),
        KEY idx_specialty (specialty),
        KEY idx_status (status)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}melk_specialist_tenders (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        tender_key VARCHAR(32) NOT NULL,
        title VARCHAR(200) NOT NULL,
        description TEXT NULL,
        specialty_required VARCHAR(40) NOT NULL DEFAULT 'any',
        project_type VARCHAR(40) NOT NULL DEFAULT 'design',
        location_county VARCHAR(100) NULL,
        location_city VARCHAR(100) NULL,
        budget_min BIGINT UNSIGNED DEFAULT NULL,
        budget_max BIGINT UNSIGNED DEFAULT NULL,
        deadline DATETIME NULL,
        status VARCHAR(30) NOT NULL DEFAULT 'open',
        created_by BIGINT UNSIGNED NOT NULL DEFAULT 0,
        created_by_name VARCHAR(120) NULL,
        source_type VARCHAR(30) NULL,
        source_id BIGINT UNSIGNED DEFAULT 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 idx_tender_key (tender_key),
        KEY idx_status (status),
        KEY idx_specialty (specialty_required),
        KEY idx_created_by (created_by)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}melk_specialist_bids (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        tender_id BIGINT UNSIGNED NOT NULL,
        specialist_id BIGINT UNSIGNED NOT NULL,
        user_id BIGINT UNSIGNED NOT NULL,
        amount BIGINT UNSIGNED NOT NULL DEFAULT 0,
        delivery_days SMALLINT UNSIGNED DEFAULT NULL,
        proposal_text TEXT NULL,
        status VARCHAR(30) NOT NULL DEFAULT 'submitted',
        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 idx_tender_specialist (tender_id, specialist_id),
        KEY idx_user (user_id),
        KEY idx_status (status)
    ) {$charset}",
];

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

// مهاجرت ستون‌های آگهی (نسخه‌های قبلی)
$listing_alters = [
    "ALTER TABLE {$pre}melk_listings ADD COLUMN property_type VARCHAR(40) NOT NULL DEFAULT 'residential_apartment' AFTER deal_type",
    "ALTER TABLE {$pre}melk_listings ADD COLUMN visibility_district_ids JSON NULL AFTER visibility_user_ids",
    "ALTER TABLE {$pre}melk_listings ADD COLUMN visibility_neighborhood_ids JSON NULL AFTER visibility_district_ids",
    "ALTER TABLE {$pre}melk_listings ADD COLUMN visibility_usernames JSON NULL AFTER visibility_neighborhood_ids",
    "ALTER TABLE {$pre}melk_listings ADD COLUMN form_template_key VARCHAR(80) NULL AFTER property_type",
    "ALTER TABLE {$pre}melk_listings ADD COLUMN form_data JSON NULL AFTER form_template_key",
];
foreach ( $listing_alters as $sql ) {
    try { $wpdb->query( $sql ); $done[] = 'alter_ok'; } catch ( \Throwable $e ) { /* exists */ }
}

// بانک فرم‌های آگهی ملک
$wpdb->query( "CREATE TABLE IF NOT EXISTS {$pre}melk_listing_form_templates (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    form_key VARCHAR(80) NOT NULL,
    title VARCHAR(200) NOT NULL,
    subtitle VARCHAR(200) NULL,
    icon VARCHAR(20) NULL DEFAULT '📋',
    deal_type VARCHAR(30) NOT NULL,
    property_type VARCHAR(40) NOT NULL,
    standard_ref VARCHAR(120) NULL,
    sections_json JSON NOT NULL,
    min_fields JSON NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    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 idx_form_key (form_key),
    KEY idx_deal (deal_type),
    KEY idx_active (is_active)
) {$charset}" );
$done[] = 'table_form_bank';

require_once ACC_PATH . 'includes/helpers/melk-listing-forms.php';
$form_seed = Melk_Listing_Forms::seed_to_db();
$done = array_merge( $done, $form_seed );

// بانک فرم‌های متخصصان ساختمان
$wpdb->query( "CREATE TABLE IF NOT EXISTS {$pre}melk_specialist_form_templates (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    form_key VARCHAR(80) NOT NULL,
    title VARCHAR(200) NOT NULL,
    subtitle VARCHAR(200) NULL,
    icon VARCHAR(20) NULL DEFAULT '📋',
    specialty VARCHAR(40) NOT NULL,
    standard_ref VARCHAR(120) NULL,
    sections_json JSON NOT NULL,
    min_fields JSON NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    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 idx_form_key (form_key),
    KEY idx_specialty (specialty),
    KEY idx_active (is_active)
) {$charset}" );
$done[] = 'table_spec_form_bank';

$spec_alters = [
    "ALTER TABLE {$pre}melk_building_specialists ADD COLUMN form_template_key VARCHAR(80) NULL AFTER specialty",
    "ALTER TABLE {$pre}melk_building_specialists ADD COLUMN form_data JSON NULL AFTER form_template_key",
];
foreach ( $spec_alters as $sql ) {
    try { $wpdb->query( $sql ); $done[] = 'alter_spec_ok'; } catch ( \Throwable $e ) { /* exists */ }
}

require_once ACC_PATH . 'includes/helpers/melk-specialist-forms.php';
$spec_form_seed = Melk_Specialist_Forms::seed_to_db();
$done = array_merge( $done, $spec_form_seed );

// بانک فرم‌های بنگاه شهرستان
$wpdb->query( "CREATE TABLE IF NOT EXISTS {$pre}melk_county_form_templates (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    form_key VARCHAR(80) NOT NULL,
    title VARCHAR(200) NOT NULL,
    subtitle VARCHAR(200) NULL,
    icon VARCHAR(20) NULL DEFAULT '📋',
    request_type VARCHAR(40) NOT NULL,
    standard_ref VARCHAR(120) NULL,
    sections_json JSON NOT NULL,
    min_fields JSON NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    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 idx_form_key (form_key),
    KEY idx_request_type (request_type),
    KEY idx_active (is_active)
) {$charset}" );
$done[] = 'table_county_form_bank';

$county_alters = [
    "ALTER TABLE {$pre}melk_county_requests ADD COLUMN form_template_key VARCHAR(80) NULL AFTER request_type",
    "ALTER TABLE {$pre}melk_county_requests ADD COLUMN form_data JSON NULL AFTER form_template_key",
];
foreach ( $county_alters as $sql ) {
    try { $wpdb->query( $sql ); $done[] = 'alter_county_ok'; } catch ( \Throwable $e ) { /* exists */ }
}

require_once ACC_PATH . 'includes/helpers/melk-county-forms.php';
$county_form_seed = Melk_County_Forms::seed_to_db();
$done = array_merge( $done, $county_form_seed );

// دسترسی‌های جدید
$perms = [
    ['melk.construction.view',   'مشاهده پروژه ساخت', 'melk'],
    ['melk.construction.manage', 'مدیریت پروژه ساخت', 'melk'],
    ['melk.listings.view',       'مشاهده آگهی ملک', 'melk'],
    ['melk.listings.publish',    'انتشار آگهی ملک', 'melk'],
    ['melk.county.submit',       'ثبت درخواست بنگاه شهرستان', 'melk'],
    ['melk.county.manage',     'مدیریت درخواست‌های شهرستان', 'melk'],
    ['melk.specialists.view',    'مشاهده فهرست متخصصان', 'melk'],
    ['melk.specialists.register','ثبت‌نام متخصص ساختمان', 'melk'],
    ['melk.specialists.approve', 'تأیید متخصصان ساختمان', 'melk'],
    ['melk.specialists.bid',     'ارسال پیشنهاد قیمت (مناقصه)', 'melk'],
    ['melk.specialists.tender',  'مدیریت مناقصه متخصصان', 'melk'],
];

$melk_ext_roles = [
    ['melk_county_agency',     'بنگاه شهرستان',       'بنگاه‌های املاک خارج از شهر اصلی', 16, 1],
    ['melk_building_specialist','متخصص ساختمان',       'معمار، سازه، تاسیسات و ...',         17, 1],
];
foreach ( $melk_ext_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],
        ] );
        $done[] = "role:{$r[0]}";
    }
}

$melk_ext_roles = [
    ['agent', 'مشاور املاک', 'کارشناس بنگاه و مشاور معاملات', 11, 1],
];
foreach ( $melk_ext_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],
        ] );
        $done[] = "role:{$r[0]}";
    }
}

$melk_ext_role_perms = [
    'agent'                   => ['melk.view', 'melk.listings.publish', 'melk.listings.view'],
    'melk_county_agency'      => ['melk.view', 'melk.county.submit', 'melk.specialists.view'],
    'melk_building_specialist'=> ['melk.view', 'melk.specialists.register', 'melk.specialists.bid'],
    'melk_coordinator'        => ['melk.county.manage', 'melk.specialists.approve', 'melk.specialists.view', 'melk.specialists.tender', 'melk.listings.publish', 'melk.listings.view'],
    'district_lead'           => ['melk.specialists.view', 'melk.listings.publish', 'melk.listings.view'],
    'neighborhood_manager'    => ['melk.specialists.view', 'melk.listings.publish', 'melk.listings.view'],
    'melk_specialist'         => ['melk.listings.publish', 'melk.listings.view'],
];
foreach ( $melk_ext_role_perms as $slug => $perm_keys ) {
    $role_id = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$pre}acl_roles WHERE slug=%s", $slug ) );
    if ( ! $role_id ) continue;
    foreach ( $perm_keys as $pk ) {
        $wpdb->query( $wpdb->prepare(
            "INSERT IGNORE INTO {$pre}acl_role_permissions (role_id, permission_key) VALUES (%d, %s)",
            $role_id, $pk
        ) );
    }
}

foreach ( $perms as [ $key, $label, $group ] ) {
    $exists = $wpdb->get_var( $wpdb->prepare(
        "SELECT id FROM {$pre}acl_permissions WHERE permission_key = %s", $key
    ) );
    if ( ! $exists ) {
        $wpdb->insert( "{$pre}acl_permissions", [
            'permission_key' => $key,
            'label'          => $label,
            'group_slug'     => $group,
            'sort_order'     => 0,
        ] );
        $done[] = "perm:{$key}";
    }
}

$super_id = (int) $wpdb->get_var( "SELECT id FROM {$pre}acl_roles WHERE slug='super_admin' LIMIT 1" );
if ( $super_id ) {
    foreach ( array_column( $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
        ) );
    }
}

echo json_encode( [
    'ok'      => true,
    'message' => 'جداول توسعه اقتصاد ملک نصب شد',
    'steps'   => $done,
], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT );
