<?php
/**
 * نصب ماژول بازاریاب خدمات HVAC
 * php run-service-marketer-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';

$ddl = [
    "CREATE TABLE IF NOT EXISTS {$pre}svc_marketer_accounts (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        phone VARCHAR(20) NOT NULL,
        display_name VARCHAR(200) NOT NULL DEFAULT '',
        email VARCHAR(200) NULL,
        city VARCHAR(100) NULL,
        marketer_code VARCHAR(16) NOT NULL,
        tier VARCHAR(20) NOT NULL DEFAULT 'starter',
        status VARCHAR(20) NOT NULL DEFAULT 'active',
        total_leads INT NOT NULL DEFAULT 0,
        converted_leads INT NOT NULL DEFAULT 0,
        total_earned DECIMAL(14,2) NOT NULL DEFAULT 0,
        balance_pending DECIMAL(14,2) NOT NULL DEFAULT 0,
        balance_paid DECIMAL(14,2) NOT NULL DEFAULT 0,
        bank_info JSON 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 marketer_code (marketer_code),
        UNIQUE KEY phone (phone),
        KEY tier (tier),
        KEY status (status)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}svc_marketer_leads (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        marketer_id BIGINT UNSIGNED NOT NULL,
        customer_name VARCHAR(200) NOT NULL DEFAULT '',
        customer_phone VARCHAR(20) NOT NULL,
        customer_city VARCHAR(100) NULL,
        service_interest VARCHAR(40) NOT NULL DEFAULT 'repair',
        notes TEXT NULL,
        status VARCHAR(30) NOT NULL DEFAULT 'introduced',
        request_id BIGINT UNSIGNED NULL,
        order_value DECIMAL(14,2) NOT NULL DEFAULT 0,
        introduced_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        contacted_at DATETIME NULL,
        converted_at DATETIME NULL,
        expires_at DATETIME NULL,
        PRIMARY KEY (id),
        KEY marketer_id (marketer_id),
        KEY customer_phone (customer_phone),
        KEY status (status),
        KEY request_id (request_id)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}svc_marketer_commissions (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        marketer_id BIGINT UNSIGNED NOT NULL,
        lead_id BIGINT UNSIGNED NULL,
        event_type VARCHAR(40) NOT NULL,
        order_value DECIMAL(14,2) NOT NULL DEFAULT 0,
        commission_pct DECIMAL(6,3) NOT NULL DEFAULT 0,
        commission_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
        status VARCHAR(20) NOT NULL DEFAULT 'pending',
        ref_type VARCHAR(40) NULL,
        ref_id BIGINT UNSIGNED NULL,
        dedupe_key VARCHAR(80) NULL,
        note VARCHAR(255) NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        approved_at DATETIME NULL,
        paid_at DATETIME NULL,
        PRIMARY KEY (id),
        UNIQUE KEY dedupe (marketer_id, dedupe_key),
        KEY marketer_id (marketer_id),
        KEY status (status)
    ) {$charset}",

    "CREATE TABLE IF NOT EXISTS {$pre}svc_marketer_payouts (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        marketer_id BIGINT UNSIGNED NOT NULL,
        amount DECIMAL(14,2) NOT NULL DEFAULT 0,
        status VARCHAR(20) NOT NULL DEFAULT 'requested',
        bank_snapshot JSON NULL,
        admin_note VARCHAR(255) NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        processed_at DATETIME NULL,
        PRIMARY KEY (id),
        KEY marketer_id (marketer_id),
        KEY status (status)
    ) {$charset}",
];

foreach ( $ddl as $sql ) {
    $wpdb->query( $sql );
}

/* ستون marketer در درخواست‌ها */
$req_table = $pre . 'svc_requests';
$col = $wpdb->get_var( "SHOW COLUMNS FROM {$req_table} LIKE 'marketer_id'" );
if ( ! $col ) {
    $wpdb->query( "ALTER TABLE {$req_table} ADD COLUMN marketer_id BIGINT UNSIGNED NULL AFTER session_key" );
    $wpdb->query( "ALTER TABLE {$req_table} ADD KEY marketer_id (marketer_id)" );
}

echo json_encode( [
    'success' => true,
    'tables'  => [
        'svc_marketer_accounts'    => (bool) $wpdb->get_var( "SHOW TABLES LIKE '{$pre}svc_marketer_accounts'" ),
        'svc_marketer_leads'       => (bool) $wpdb->get_var( "SHOW TABLES LIKE '{$pre}svc_marketer_leads'" ),
        'svc_marketer_commissions' => (bool) $wpdb->get_var( "SHOW TABLES LIKE '{$pre}svc_marketer_commissions'" ),
        'svc_marketer_payouts'     => (bool) $wpdb->get_var( "SHOW TABLES LIKE '{$pre}svc_marketer_payouts'" ),
        'svc_requests.marketer_id' => (bool) $wpdb->get_var( "SHOW COLUMNS FROM {$req_table} LIKE 'marketer_id'" ),
    ],
], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT );
