CREATE TABLE IF NOT EXISTS schema_migrations (
    version VARCHAR(50) PRIMARY KEY,
    applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL UNIQUE,
    username VARCHAR(255) NULL,
    first_name VARCHAR(255) NULL,
    last_name VARCHAR(255) NULL,
    balance BIGINT NOT NULL DEFAULT 0,
    state VARCHAR(100) NULL,
    state_data JSON NULL,
    menu_message_id BIGINT NULL,
    is_blocked TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'ربات توسط کاربر مسدود شده؛ با پیام بعدی صفر می‌شود',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_users_last_seen (last_seen_at),
    INDEX idx_users_blocked (is_blocked)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admins (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL UNIQUE,
    role ENUM('owner','admin','support') NOT NULL DEFAULT 'admin',
    active TINYINT(1) NOT NULL DEFAULT 1,
    added_by BIGINT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_admins_active_role (active, role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value LONGTEXT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sponsor_channels (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    chat_id VARCHAR(100) NOT NULL UNIQUE,
    username VARCHAR(255) NULL,
    title VARCHAR(255) NOT NULL,
    invite_link VARCHAR(500) NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_by BIGINT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sponsors_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS plans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    price BIGINT UNSIGNED NOT NULL,
    duration_days SMALLINT UNSIGNED NOT NULL DEFAULT 30,
    traffic_gb DECIMAL(10,2) UNSIGNED NULL COMMENT 'NULL یعنی نامحدود',
    devices SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_plans_active_sort (active, sort_order, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    plan_id BIGINT UNSIGNED NOT NULL,
    plan_name VARCHAR(255) NOT NULL,
    amount BIGINT UNSIGNED NOT NULL,
    duration_days SMALLINT UNSIGNED NOT NULL,
    traffic_gb DECIMAL(10,2) UNSIGNED NULL COMMENT 'مشخصات سرویس در لحظه خرید',
    devices SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    status ENUM('awaiting_delivery','delivered','cancelled') NOT NULL DEFAULT 'awaiting_delivery',
    delivery_type ENUM('text','document') NULL,
    delivery_content LONGTEXT NULL,
    delivered_by BIGINT NULL,
    cancelled_by BIGINT NULL,
    cancel_reason VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    delivered_at DATETIME NULL,
    cancelled_at DATETIME NULL,
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT fk_orders_plan FOREIGN KEY (plan_id) REFERENCES plans(id),
    INDEX idx_orders_user (user_id, id),
    INDEX idx_orders_status (status, id),
    INDEX idx_orders_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wallet_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    type ENUM('credit','debit','refund','adjustment') NOT NULL,
    amount BIGINT NOT NULL,
    balance_after BIGINT NOT NULL,
    reference_type VARCHAR(50) NULL,
    reference_id BIGINT NULL,
    description VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_wallet_user FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_wallet_user (user_id, id),
    INDEX idx_wallet_reference (reference_type, reference_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    amount BIGINT UNSIGNED NOT NULL,
    receipt_type ENUM('photo','document') NOT NULL,
    receipt_file_id TEXT NOT NULL,
    receipt_unique_id VARCHAR(255) NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    reviewed_by BIGINT NULL,
    review_note VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reviewed_at DATETIME NULL,
    CONSTRAINT fk_payments_user FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY uq_payment_receipt_unique (receipt_unique_id),
    INDEX idx_payments_status (status, id),
    INDEX idx_payments_user (user_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tickets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    subject VARCHAR(255) NOT NULL DEFAULT 'پشتیبانی',
    status ENUM('open','answered','closed') NOT NULL DEFAULT 'open',
    assigned_admin BIGINT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    closed_at DATETIME NULL,
    CONSTRAINT fk_tickets_user FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_tickets_status (status, updated_at),
    INDEX idx_tickets_user (user_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ticket_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_id BIGINT UNSIGNED NOT NULL,
    sender_type ENUM('user','admin') NOT NULL,
    sender_telegram_id BIGINT NOT NULL,
    message_type ENUM('text','photo','document') NOT NULL DEFAULT 'text',
    message_text LONGTEXT NULL,
    file_id TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ticket_messages_ticket FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    INDEX idx_ticket_messages_ticket (ticket_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS broadcasts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_telegram_id BIGINT NOT NULL,
    message_text LONGTEXT NOT NULL,
    status ENUM('queued','processing','completed') NOT NULL DEFAULT 'queued',
    total_count INT UNSIGNED NOT NULL DEFAULT 0,
    sent_count INT UNSIGNED NOT NULL DEFAULT 0,
    failed_count INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    INDEX idx_broadcasts_status (status, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS broadcast_targets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    broadcast_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    status ENUM('pending','processing','sent','failed') NOT NULL DEFAULT 'pending',
    error_message VARCHAR(500) NULL,
    processing_started_at DATETIME NULL,
    processed_at DATETIME NULL,
    CONSTRAINT fk_broadcast_targets_broadcast FOREIGN KEY (broadcast_id) REFERENCES broadcasts(id) ON DELETE CASCADE,
    CONSTRAINT fk_broadcast_targets_user FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY uq_broadcast_user (broadcast_id, user_id),
    INDEX idx_broadcast_targets_pending (status, id),
    INDEX idx_broadcast_targets_broadcast (broadcast_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS processed_updates (
    update_id BIGINT PRIMARY KEY,
    status ENUM('processing','completed','failed') NOT NULL DEFAULT 'processing',
    attempts SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    claimed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processed_at DATETIME NULL,
    error_message VARCHAR(500) NULL,
    INDEX idx_processed_updates_status (status, claimed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    actor_telegram_id BIGINT NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(100) NULL,
    entity_id BIGINT NULL,
    details JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_actor (actor_telegram_id, id),
    INDEX idx_audit_action (action, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings (setting_key, setting_value) VALUES
('maintenance', '0'),
('min_charge', '50000'),
('card_number', '6037-0000-0000-0000'),
('card_holder', 'نام دارنده کارت'),
('welcome_text', '👋 سلام {name}، خوش آمدید\n\nاز این ربات می‌توانید سرویس خود را خریداری، کیف پول را شارژ و با پشتیبانی در ارتباط باشید.'),
('guide_text', '📚 راهنمای استفاده\n\n۱) از بخش خرید، سرویس موردنظر را انتخاب کنید.\n۲) کیف پول را شارژ کنید.\n۳) پس از خرید، اطلاعات اتصال توسط پشتیبانی ارسال می‌شود.\n۴) برای هرگونه مشکل از بخش پشتیبانی پیام بفرستید.')
ON DUPLICATE KEY UPDATE setting_value = setting_value;

INSERT IGNORE INTO schema_migrations (version) VALUES ('2.1.0');