spider/deploy/migrate_v2.sql

-- Migration: v1 (pipeline) -> v2 (plugin architecture)
-- Compatible with MySQL 5.7+
--
-- BEFORE running:
--   1. Stop the old application
--   2. Backup: mysqldump -u root -p spider > spider_backup_$(date +%Y%m%d).sql

-- 1. Create keywords table (merge managed_seeds + managed_keywords)
CREATE TABLE IF NOT EXISTS keywords (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    keyword VARCHAR(255) NOT NULL,
    industry_tag VARCHAR(100) DEFAULT '',
    enabled TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE INDEX idx_keyword (keyword)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO keywords (keyword, industry_tag, enabled, created_at)
SELECT keyword, COALESCE(category,''), IF(status='active', 1, 0), created_at
FROM managed_keywords;

INSERT IGNORE INTO keywords (keyword, industry_tag, enabled, created_at)
SELECT channel_name, 'seed', IF(status='active', 1, 0), created_at
FROM managed_seeds;

-- 2. Recreate merchants_raw with new schema
CREATE TABLE merchants_raw_v2 (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tg_username VARCHAR(255) NOT NULL,
    tg_link VARCHAR(500) DEFAULT '',
    merchant_name VARCHAR(500) DEFAULT '',
    website VARCHAR(2048) DEFAULT '',
    email VARCHAR(255) DEFAULT '',
    phone VARCHAR(100) DEFAULT '',
    source_type VARCHAR(50) NOT NULL DEFAULT 'web',
    source_name VARCHAR(500) DEFAULT '',
    source_url VARCHAR(2048) DEFAULT '',
    original_text TEXT,
    industry_tag VARCHAR(100) DEFAULT '',
    status VARCHAR(20) DEFAULT 'raw',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tg_username (tg_username),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO merchants_raw_v2 (id, tg_username, tg_link, merchant_name, website, email, phone,
    source_type, source_name, source_url, original_text, industry_tag, status, created_at)
SELECT id, COALESCE(tg_username,''), CONCAT('https://t.me/', COALESCE(tg_username,'')),
    COALESCE(merchant_name,''), COALESCE(website,''), COALESCE(email,''), COALESCE(phone,''),
    COALESCE(source_type,'web'), '', COALESCE(source_id, ''),
    COALESCE(original_message, ''), COALESCE(industry, ''),
    CASE WHEN status IN ('raw', 'glm_parsed') THEN 'raw' ELSE 'raw' END,
    created_at
FROM merchants_raw;

RENAME TABLE merchants_raw TO merchants_raw_old, merchants_raw_v2 TO merchants_raw;

-- 3. Recreate merchants_clean with new schema
CREATE TABLE merchants_clean_v2 (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tg_username VARCHAR(255) DEFAULT '',
    tg_link VARCHAR(500) DEFAULT '',
    merchant_name VARCHAR(500) DEFAULT '',
    website VARCHAR(2048) DEFAULT '',
    email VARCHAR(255) DEFAULT '',
    phone VARCHAR(100) DEFAULT '',
    source_count INT DEFAULT 1,
    all_sources JSON,
    industry_tag VARCHAR(100) DEFAULT '',
    level VARCHAR(10) DEFAULT 'Cold',
    status VARCHAR(20) NOT NULL DEFAULT 'valid',
    is_alive TINYINT(1) DEFAULT 0,
    last_checked_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE INDEX idx_tg_username (tg_username),
    INDEX idx_status (status),
    INDEX idx_level (level),
    INDEX idx_industry_tag (industry_tag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO merchants_clean_v2 (id, tg_username, tg_link, merchant_name, website, email, phone,
    source_count, all_sources, industry_tag, level, status, is_alive, created_at, updated_at)
SELECT id, COALESCE(tg_username,''), CONCAT('https://t.me/', COALESCE(tg_username,'')),
    COALESCE(merchant_name,''), COALESCE(website,''), COALESCE(email,''), COALESCE(phone,''),
    COALESCE(source_count,1), COALESCE(source_links, CAST('[]' AS JSON)),
    COALESCE(industry, ''),
    CASE
        WHEN quality_score >= 60 THEN 'Hot'
        WHEN quality_score >= 30 THEN 'Warm'
        ELSE 'Cold'
    END,
    status,
    CASE WHEN status = 'valid' THEN 1 ELSE 0 END,
    created_at, updated_at
FROM merchants_clean;

RENAME TABLE merchants_clean TO merchants_clean_old, merchants_clean_v2 TO merchants_clean;

-- 4. Recreate channels with new schema (simpler than ALTER for enum->varchar)
CREATE TABLE channels_v2 (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    channel_id BIGINT DEFAULT 0,
    access_hash BIGINT DEFAULT 0,
    status VARCHAR(20) DEFAULT 'pending',
    last_message_id INT DEFAULT 0,
    merchants_found INT DEFAULT 0,
    source VARCHAR(50) NOT NULL DEFAULT 'search',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE INDEX idx_username (username),
    INDEX idx_status (status),
    INDEX idx_source (source)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO channels_v2 (id, username, status, last_message_id, source, created_at, updated_at)
SELECT id, username, status, last_message_id, source, created_at, updated_at
FROM channels;

RENAME TABLE channels TO channels_old, channels_v2 TO channels;

-- 5. Create task_logs table
CREATE TABLE IF NOT EXISTS task_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_type VARCHAR(50) NOT NULL,
    plugin_name VARCHAR(100) DEFAULT '',
    status VARCHAR(20) DEFAULT 'pending',
    items_processed INT DEFAULT 0,
    merchants_added INT DEFAULT 0,
    errors_count INT DEFAULT 0,
    started_at DATETIME,
    finished_at DATETIME,
    detail TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_task_type (task_type),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Done! Old tables preserved as *_old for safety.
-- After verifying the new system works, run:
--   DROP TABLE IF EXISTS merchants_raw_old;
--   DROP TABLE IF EXISTS merchants_clean_old;
--   DROP TABLE IF EXISTS channels_old;
--   DROP TABLE IF EXISTS managed_seeds;
--   DROP TABLE IF EXISTS managed_keywords;
--   DROP TABLE IF EXISTS managed_settings;
--   DROP TABLE IF EXISTS config_revisions;
--   DROP TABLE IF EXISTS nav_sites;
--   DROP TABLE IF EXISTS tasks;