-- db_schema.sql

-- Table: users
CREATE TABLE IF NOT EXISTS users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(64),
    first_name VARCHAR(128),
    last_name VARCHAR(128),
    is_admin TINYINT(1) DEFAULT 0,
    test_claimed TINYINT(1) DEFAULT 0,
    test_phone VARCHAR(32) DEFAULT NULL,
    balance INT DEFAULT 0, -- اضافه شده برای کیف پول
    has_wallet_bonus TINYINT(1) DEFAULT 0, -- آیا بونوس شارژ کیف پول دریافت کرده
    total_cashback INT DEFAULT 0, -- مجموع جوایز خرید دریافتی
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: orders
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    plan_code VARCHAR(64) NOT NULL,
    plan_title VARCHAR(255) NOT NULL,
    price INT NOT NULL,
    payment_method VARCHAR(50) DEFAULT NULL, -- اضافه شده برای روش پرداخت
    status ENUM('PENDING','APPROVED','REJECTED') DEFAULT 'PENDING',
    receipt_file_id VARCHAR(255) DEFAULT NULL,
    receipt_text MEDIUMTEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    approved_at TIMESTAMP NULL,
    rejected_at TIMESTAMP NULL,
    INDEX(user_id), INDEX(plan_code), INDEX(status),
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: configs
CREATE TABLE IF NOT EXISTS configs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plan_code VARCHAR(64) NOT NULL,
    title VARCHAR(255) NOT NULL,
    config_link TEXT DEFAULT NULL,
    config_text MEDIUMTEXT DEFAULT NULL,
    config_file_id VARCHAR(255) DEFAULT NULL,
    photo_file_id VARCHAR(255) DEFAULT NULL,
    is_used TINYINT(1) DEFAULT 0,
    used_by BIGINT DEFAULT NULL,
    used_at TIMESTAMP NULL,
    created_by BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX(plan_code),
    INDEX(is_used),
    CONSTRAINT fk_configs_admin FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_configs_used_by FOREIGN KEY (used_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: sales
CREATE TABLE IF NOT EXISTS sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT DEFAULT NULL,
    user_id BIGINT NOT NULL,
    plan_code VARCHAR(64) NOT NULL,
    plan_title VARCHAR(255) NOT NULL,
    price INT NOT NULL,
    config_data MEDIUMTEXT DEFAULT NULL,
    config_text MEDIUMTEXT DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    delivered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    INDEX(order_id), INDEX(user_id), INDEX(is_active),
    CONSTRAINT fk_sales_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_sales_user  FOREIGN KEY (user_id)  REFERENCES users(id)  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: wallet_transactions
CREATE TABLE IF NOT EXISTS wallet_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    amount INT NOT NULL,
    type ENUM('DEPOSIT', 'PURCHASE', 'WITHDRAW', 'REFUND', 'ADMIN') NOT NULL,
    status ENUM('PENDING', 'COMPLETED', 'REJECTED', 'CANCELLED') NOT NULL DEFAULT 'PENDING',
    description MEDIUMTEXT DEFAULT NULL,
    receipt_file_id VARCHAR(255) DEFAULT NULL,
    receipt_text MEDIUMTEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL,
    processed_at TIMESTAMP NULL,
    processed_by BIGINT DEFAULT NULL,
    INDEX(user_id),
    INDEX(type),
    INDEX(status),
    CONSTRAINT fk_wallet_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_wallet_processed_by FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: config_waiting_queue
CREATE TABLE IF NOT EXISTS config_waiting_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    user_id BIGINT NOT NULL,
    plan_code VARCHAR(64) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX(order_id), INDEX(user_id), INDEX(plan_code),
    CONSTRAINT fk_cwq_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_cwq_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: broadcast_logs
CREATE TABLE IF NOT EXISTS broadcast_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id BIGINT NOT NULL,
    message MEDIUMTEXT NOT NULL,
    total_users INT NOT NULL,
    success_count INT NOT NULL DEFAULT 0,
    failed_count INT NOT NULL DEFAULT 0,
    message_type VARCHAR(50) NOT NULL,
    status ENUM('PROCESSING', 'COMPLETED', 'COMPLETED_WITH_ERRORS', 'FAILED') NOT NULL DEFAULT 'PROCESSING',
    failed_users MEDIUMTEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    INDEX(admin_id),
    CONSTRAINT fk_broadcast_admin FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: settings (from miniapp/api/config.php)
CREATE TABLE IF NOT EXISTS `settings` (
    `key_name` VARCHAR(50) PRIMARY KEY,
    `value` TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: plans (پلن‌های فروشگاه)
CREATE TABLE IF NOT EXISTS plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(64) NOT NULL,
    duration_label VARCHAR(32) NOT NULL,
    users INT NOT NULL DEFAULT 1,
    price INT NOT NULL,
    plan_code VARCHAR(64) NOT NULL UNIQUE,
    title VARCHAR(255) NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL,
    INDEX(category),
    INDEX(plan_code),
    INDEX(is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: plan_categories (دسته‌بندی پلن‌ها)
CREATE TABLE IF NOT EXISTS plan_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(64) NOT NULL UNIQUE,
    name VARCHAR(128) NOT NULL,
    display_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default categories
INSERT IGNORE INTO plan_categories (code, name, display_order) VALUES
('VIP_1M', '👑 VIP یک‌ماهه (نامحدود)', 1),
('VIP_3M', '⏳ VIP سه‌ماهه (نامحدود)', 2),
('VOL_1M', '📦 حجمی یک‌ماهه', 3);

-- Table: test_accounts (اکانت‌های تست)
CREATE TABLE IF NOT EXISTS test_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    config_id INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    INDEX(user_id),
    CONSTRAINT fk_test_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_test_config FOREIGN KEY (config_id) REFERENCES configs(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add updated_at column to wallet_transactions if not exists
-- ALTER TABLE wallet_transactions ADD COLUMN updated_at TIMESTAMP NULL AFTER created_at;

-- Table: service_expiry_reminders (ردیابی یادآورهای ارسال شده)
CREATE TABLE IF NOT EXISTS service_expiry_reminders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_id INT NOT NULL,
    user_id BIGINT NOT NULL,
    reminder_type ENUM('2_DAYS', '1_DAY', 'EXPIRED') NOT NULL,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_reminder (sale_id, reminder_type),
    INDEX(sale_id),
    INDEX(user_id),
    CONSTRAINT fk_reminder_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_reminder_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
