-- ═══════════════════════════════════════════════════════════════════
--  my_media — Full Migration (Web + Admin + Telegram + Storage)
--  Run once to create all tables
-- ═══════════════════════════════════════════════════════════════════

-- 1. Web users
CREATE TABLE IF NOT EXISTS web_users (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    email         VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name          VARCHAR(255),
    is_admin      TINYINT(1) NOT NULL DEFAULT 0,
    status        ENUM('pending','active','suspended') NOT NULL DEFAULT 'active',
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Web subscriptions
CREATE TABLE IF NOT EXISTS subscriptions (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    user_id    INT NOT NULL,
    status     ENUM('active','expired','cancelled') DEFAULT 'active',
    starts_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES web_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. User lists
CREATE TABLE IF NOT EXISTS user_lists (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    user_id    INT NOT NULL,
    name       VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES web_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. List items
CREATE TABLE IF NOT EXISTS list_items (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    list_id    INT NOT NULL,
    link_id    INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (list_id) REFERENCES user_lists(id) ON DELETE CASCADE,
    FOREIGN KEY (link_id) REFERENCES links(id) ON DELETE CASCADE,
    UNIQUE KEY uq_list_link (list_id, link_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Subscription plans
CREATE TABLE IF NOT EXISTS subscription_plans (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    name             VARCHAR(100) NOT NULL,
    duration_days    INT NOT NULL DEFAULT 30,
    price            DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    storage_limit_mb INT NOT NULL DEFAULT 512,
    file_limit       INT NULL DEFAULT NULL,
    is_active        TINYINT(1) NOT NULL DEFAULT 1,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default plans
INSERT INTO subscription_plans (name, duration_days, price, storage_limit_mb, file_limit) VALUES
  ('تجريبي',    30,  0.00,  512,   50),
  ('شهري',      30,  9.99,  10240, NULL),
  ('ربع سنوي',  90,  24.99, 25600, NULL),
  ('سنوي',      365, 79.99, 51200, NULL)
ON DUPLICATE KEY UPDATE name = name;

-- 6. Telegram users
CREATE TABLE IF NOT EXISTS telegram_users (
    telegram_user_id BIGINT PRIMARY KEY,
    username         VARCHAR(255) NULL,
    first_name       VARCHAR(255) NOT NULL DEFAULT '',
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Telegram subscriptions
CREATE TABLE IF NOT EXISTS telegram_subscriptions (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    telegram_user_id BIGINT NOT NULL,
    plan_id          INT NOT NULL,
    storage_limit_mb INT NOT NULL DEFAULT 512,
    file_limit       INT NULL DEFAULT NULL,
    starts_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at       TIMESTAMP NULL,
    status           ENUM('active','expired','cancelled') NOT NULL DEFAULT 'active',
    FOREIGN KEY (telegram_user_id) REFERENCES telegram_users(telegram_user_id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id)          REFERENCES subscription_plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. Storage usage tracking
CREATE TABLE IF NOT EXISTS user_storage_stats (
    telegram_user_id BIGINT PRIMARY KEY,
    file_count       INT NOT NULL DEFAULT 0,
    storage_used_mb  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    last_updated     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (telegram_user_id) REFERENCES telegram_users(telegram_user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. Add user tracking columns to links (ignore error if columns already exist)
ALTER TABLE links
    ADD COLUMN IF NOT EXISTS telegram_user_id BIGINT NULL,
    ADD COLUMN IF NOT EXISTS file_size_mb DECIMAL(10,2) NULL DEFAULT 0.00;

-- Index for user lookups
CREATE INDEX IF NOT EXISTS idx_links_tg_user ON links (telegram_user_id);
