<?php
if (PHP_SAPI !== 'cli' && !defined('CMS_DB_UPGRADE_BOOTSTRAP_ONLY')) {
    http_response_code(404);
    exit;
}

if (!defined('ROOT_PATH')) {
    define('ROOT_PATH', str_replace('\\', '/', __DIR__) . '/');
}

require_once ROOT_PATH . 'core/db/db.php';
require_once ROOT_PATH . 'core/lib/cms_db_upgrade_support.php';

function cmsUpgradeTableExists($db, $table)
{
    return (bool) $db->fetch('SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA=? AND TABLE_NAME=? LIMIT 1', [
        $db->getConfig()['database'], $table,
    ]);
}

function cmsUpgradeColumnExists($db, $table, $column)
{
    return (bool) $db->fetch('SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND COLUMN_NAME=? LIMIT 1', [
        $db->getConfig()['database'], $table, $column,
    ]);
}

function cmsUpgradeIndexExists($db, $table, $indexName)
{
    foreach ($db->fetchAll("SHOW INDEX FROM {$table}") as $index) {
        if (($index['Key_name'] ?? '') === $indexName) return true;
    }
    return false;
}

function cmsRunDatabaseUpgrade()
{
    $db = \Core\Db\DB::getInstance();
    $results = [];
    $prefix = (string) ($db->getConfig()['prefix'] ?? 'yutou_');
    $coreSchema = require ROOT_PATH . 'core/db/schema.php';
    foreach ($coreSchema as $tableName => $sql) {
        $table = $db->table($tableName);
        if (!cmsUpgradeTableExists($db, $table)) {
            $db->query(str_replace('{prefix}', $prefix, $sql));
            $results[] = 'created table ' . $table;
        }
    }
    $definition = cmsDbUpgradeDefinition($prefix);

    foreach ($definition['tables'] as $table => $sql) {
        if (!cmsUpgradeTableExists($db, $table)) {
            $db->query($sql);
            $results[] = 'created table ' . $table;
        }
    }
    foreach ($definition['columns'] as $item) {
        if (cmsUpgradeTableExists($db, $item['table']) && !cmsUpgradeColumnExists($db, $item['table'], $item['column'])) {
            $db->query($item['sql']);
            $results[] = 'added column ' . $item['table'] . '.' . $item['column'];
        }
    }
    foreach ($definition['indexes'] as $item) {
        if (cmsUpgradeTableExists($db, $item['table']) && !cmsUpgradeIndexExists($db, $item['table'], $item['name'])) {
            $db->query($item['sql']);
            $results[] = 'added index ' . $item['table'] . '.' . $item['name'];
        }
    }

    $recruitApply = $db->table('recruit_apply');
    if (!cmsUpgradeTableExists($db, $recruitApply)) {
        $db->query("CREATE TABLE {$recruitApply} (
            id INT PRIMARY KEY AUTO_INCREMENT,member_id INT NOT NULL DEFAULT 0,recruit_id INT NOT NULL DEFAULT 0,
            name VARCHAR(100) NOT NULL DEFAULT '',phone VARCHAR(30) NOT NULL DEFAULT '',email VARCHAR(120) NOT NULL DEFAULT '',
            content TEXT,status TINYINT(1) NOT NULL DEFAULT 0,ip VARCHAR(50) NOT NULL DEFAULT '',
            create_time INT NOT NULL DEFAULT 0,update_time INT NOT NULL DEFAULT 0,
            KEY idx_member_id (member_id),KEY idx_recruit_id (recruit_id),KEY idx_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $results[] = 'created table ' . $recruitApply;
    }

    $extraTables = [
        'favorite' => "CREATE TABLE %s (id INT PRIMARY KEY AUTO_INCREMENT,member_id INT NOT NULL,content_id INT NOT NULL,content_type VARCHAR(20) NOT NULL DEFAULT 'product',create_time INT DEFAULT NULL,UNIQUE KEY uniq_member_content (member_id,content_id,content_type),KEY idx_member_time (member_id,create_time)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        'payment_event' => "CREATE TABLE %s (id BIGINT PRIMARY KEY AUTO_INCREMENT,provider VARCHAR(20) NOT NULL DEFAULT '',event_type VARCHAR(40) NOT NULL DEFAULT '',business_type VARCHAR(20) NOT NULL DEFAULT '',business_id INT NOT NULL DEFAULT 0,out_trade_no VARCHAR(64) NOT NULL DEFAULT '',transaction_id VARCHAR(64) NOT NULL DEFAULT '',amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,payload_hash CHAR(64) NOT NULL DEFAULT '',status VARCHAR(20) NOT NULL DEFAULT 'success',create_time INT NOT NULL DEFAULT 0,processed_time INT NOT NULL DEFAULT 0,UNIQUE KEY uk_provider_transaction (provider,transaction_id),KEY idx_business (business_type,business_id),KEY idx_out_trade_no (out_trade_no)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        'product_review' => "CREATE TABLE %s (id INT PRIMARY KEY AUTO_INCREMENT,product_id INT NOT NULL DEFAULT 0,order_id INT NOT NULL DEFAULT 0,member_id INT NOT NULL DEFAULT 0,rating TINYINT(1) NOT NULL DEFAULT 5,content VARCHAR(1000) NOT NULL DEFAULT '',reply VARCHAR(1000) NOT NULL DEFAULT '',status TINYINT(1) NOT NULL DEFAULT 0,create_time INT NOT NULL DEFAULT 0,update_time INT NOT NULL DEFAULT 0,audit_time INT NOT NULL DEFAULT 0,KEY idx_product_id (product_id),KEY idx_order_id (order_id),KEY idx_member_id (member_id),KEY idx_status (status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
        'order_refund' => "CREATE TABLE %s (id INT PRIMARY KEY AUTO_INCREMENT,order_id INT NOT NULL DEFAULT 0,order_sn VARCHAR(64) NOT NULL DEFAULT '',member_id INT NOT NULL DEFAULT 0,amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,reason VARCHAR(500) NOT NULL DEFAULT '',status TINYINT(1) NOT NULL DEFAULT 0,admin_remark VARCHAR(500) NOT NULL DEFAULT '',create_time INT NOT NULL DEFAULT 0,update_time INT NOT NULL DEFAULT 0,audit_time INT NOT NULL DEFAULT 0,refund_time INT NOT NULL DEFAULT 0,KEY idx_order_id (order_id),KEY idx_member_id (member_id),KEY idx_status (status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
    ];
    foreach ($extraTables as $tableName => $sql) {
        $table = $db->table($tableName);
        if (!cmsUpgradeTableExists($db, $table)) {
            $db->query(sprintf($sql, $table));
            $results[] = 'created table ' . $table;
        }
    }

    $columns = [
        'article' => [
            'is_paid' => "TINYINT(1) NOT NULL DEFAULT 0 AFTER status",
            'external_url' => "VARCHAR(255) DEFAULT '' AFTER author",
            'attachment_url' => "VARCHAR(255) DEFAULT '' AFTER content",
            'attachment_name' => "VARCHAR(120) DEFAULT '' AFTER attachment_url",
            'comment_enabled' => "TINYINT(1) NOT NULL DEFAULT 1 AFTER is_paid",
            'view_group_ids' => "VARCHAR(255) DEFAULT '' AFTER comment_enabled",
            'download_group_ids' => "VARCHAR(255) DEFAULT '' AFTER view_group_ids",
            'deleted_at' => "INT NOT NULL DEFAULT 0 AFTER update_time",
            'scheduled_publish_at' => "INT NOT NULL DEFAULT 0 AFTER release_time",
            'published_at' => "INT NOT NULL DEFAULT 0 AFTER scheduled_publish_at",
            'published_by' => "INT NOT NULL DEFAULT 0 AFTER published_at",
            'review_status' => "VARCHAR(20) NOT NULL DEFAULT 'approved'",
            'submitted_by' => "INT NOT NULL DEFAULT 0",
            'submitted_at' => "INT NOT NULL DEFAULT 0",
            'reviewed_by' => "INT NOT NULL DEFAULT 0",
            'reviewed_at' => "INT NOT NULL DEFAULT 0",
            'review_note' => "VARCHAR(500) NOT NULL DEFAULT ''",
        ],
        'product' => [
            'pictures' => "TEXT NULL COMMENT '产品多图JSON，最多5张' AFTER litpic",
            'external_url' => "VARCHAR(255) DEFAULT '' AFTER tags",
            'main_video_url' => "VARCHAR(255) DEFAULT '' AFTER litpic",
            'is_knowledge_paid' => "TINYINT(1) NOT NULL DEFAULT 0 AFTER status",
            'view_group_ids' => "VARCHAR(255) DEFAULT '' AFTER is_knowledge_paid",
            'param_spec' => "VARCHAR(255) DEFAULT '' AFTER product_code",
            'param_material' => "VARCHAR(255) DEFAULT '' AFTER param_spec",
            'param_color' => "VARCHAR(255) DEFAULT '' AFTER param_material",
            'param_brand' => "VARCHAR(255) DEFAULT '' AFTER param_color",
            'consult_button_mode' => "VARCHAR(32) DEFAULT 'pay' AFTER param_brand",
            'consult_phone' => "VARCHAR(120) DEFAULT '' AFTER consult_button_mode",
            'consult_wechat_qr' => "VARCHAR(255) DEFAULT '' AFTER consult_phone",
            'consult_email' => "VARCHAR(120) DEFAULT '' AFTER consult_wechat_qr",
            'deleted_at' => "INT NOT NULL DEFAULT 0 AFTER update_time",
        ],
        'guestbook' => ['member_id' => 'INT NOT NULL DEFAULT 0 AFTER channel_id'],
        'order' => ['ship_note' => "VARCHAR(500) NULL DEFAULT '' COMMENT '发货备注'"],
        'page' => ['deleted_at' => 'INT NOT NULL DEFAULT 0 AFTER update_time'],
        'cart' => ['specs' => "VARCHAR(255) NOT NULL DEFAULT '' AFTER product_id"],
    ];
    foreach ($columns as $tableName => $tableColumns) {
        $table = $db->table($tableName);
        if (!cmsUpgradeTableExists($db, $table)) continue;
        foreach ($tableColumns as $column => $sql) {
            if (!cmsUpgradeColumnExists($db, $table, $column)) {
                $db->query("ALTER TABLE {$table} ADD COLUMN {$column} {$sql}");
                $results[] = 'added column ' . $table . '.' . $column;
            }
        }
    }

    $indexes = [
        'article' => [
            'idx_article_channel_list' => '(channel_id,status,deleted_at,id)',
            'idx_article_schedule' => '(status,deleted_at,scheduled_publish_at)',
        ],
        'product' => [
            'idx_product_channel_list' => '(channel_id,status,deleted_at,id)',
        ],
        'guestbook' => [
            'idx_member_id' => '(member_id)',
            'idx_guestbook_show_list' => '(is_show,id)',
            'idx_guestbook_ip_time' => '(ip,create_time)',
        ],
        'comment' => [
            'idx_comment_content_list' => '(content_type,content_id,is_check,is_show,id)',
            'idx_comment_ip_time' => '(ip,create_time)',
        ],
    ];
    foreach ($indexes as $tableName => $definitions) {
        $fullTable = $db->table($tableName);
        foreach ($definitions as $indexName => $fields) {
            if (!cmsUpgradeIndexExists($db, $fullTable, $indexName)) {
                $db->query("ALTER TABLE {$fullTable} ADD KEY {$indexName} {$fields}");
                $results[] = $tableName . ' added index ' . $indexName;
            }
        }
    }

    $configTable = $db->table('config');
    $builderVersion = '20260718_builder_publish_01';
    $current = $db->fetch("SELECT id FROM {$configTable} WHERE config_key='builder_schema_version' LIMIT 1");
    if ($current) {
        $db->update('config', ['config_value' => $builderVersion, 'update_time' => time()], 'id=?', [$current['id']]);
    } else {
        $db->insert('config', ['config_key' => 'builder_schema_version', 'config_value' => $builderVersion, 'config_type' => 'text', 'config_group' => 'system', 'update_time' => time()]);
    }

    return ['success' => true, 'results' => $results];
}

if (PHP_SAPI === 'cli' && !defined('CMS_DB_UPGRADE_BOOTSTRAP_ONLY')) {
    echo json_encode(cmsRunDatabaseUpgrade(), JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT) . PHP_EOL;
}
