-- ═══════════════════════════════════════════════════════════════════
--  my_media v2 — Subscription + Telegram User + Storage Migration
--  Run once on the server MySQL database
-- ═══════════════════════════════════════════════════════════════════

-- 1. Add storage columns to existing subscription_plans
ALTER TABLE subscription_plans
    ADD COLUMN storage_limit_mb INT NOT NULL DEFAULT 512
        COMMENT 'Storage limit in MB for this plan',
    ADD COLUMN file_limit INT NULL DEFAULT NULL
        COMMENT 'Max number of files (NULL = unlimited)';

-- 2. Update existing plans with storage limits
UPDATE subscription_plans SET storage_limit_mb = 512,   file_limit = 50   WHERE price = 0;
UPDATE subscription_plans SET storage_limit_mb = 10240, file_limit = NULL WHERE name = 'شهري';
UPDATE subscription_plans SET storage_limit_mb = 25600, file_limit = NULL WHERE name = 'ربع سنوي';
UPDATE subscription_plans SET storage_limit_mb = 51200, file_limit = NULL WHERE name = 'سنوي';

-- 3. 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;

-- 4. Telegram subscriptions (one per user, latest is active)
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
        COMMENT 'Per-user override — admin can change independently of plan',
    file_limit        INT NULL DEFAULT NULL
        COMMENT 'Per-user file count override',
    starts_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at        TIMESTAMP NULL COMMENT 'NULL = never expires (free plan)',
    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;

-- 5. Storage usage tracking per Telegram user
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;

-- 6. Link saved files to Telegram users
ALTER TABLE links
    ADD COLUMN telegram_user_id BIGINT NULL AFTER description,
    ADD COLUMN file_size_mb     DECIMAL(10,2) NULL DEFAULT 0.00 AFTER telegram_user_id;

CREATE INDEX idx_links_tg_user ON links (telegram_user_id);
