-- ============================================================
-- CloudVault Storage Management Migration
-- Adds storage_plans and storage_requests tables
-- ============================================================

CREATE TABLE IF NOT EXISTS storage_plans (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    quota_bytes     BIGINT UNSIGNED NOT NULL,
    price_monthly   DECIMAL(10,2) NULL,
    price_yearly    DECIMAL(10,2) NULL,
    is_active       TINYINT(1) NOT NULL DEFAULT 1,
    sort_order      INT UNSIGNED NOT NULL DEFAULT 0,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS storage_requests (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         BIGINT UNSIGNED NOT NULL,
    requested_quota BIGINT UNSIGNED NOT NULL,
    reason          TEXT NULL,
    status          ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    reviewed_by     BIGINT UNSIGNED NULL,
    reviewed_at     TIMESTAMP NULL,
    admin_note      TEXT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO storage_plans (id, name, quota_bytes, price_monthly, price_yearly, sort_order) VALUES
(1, 'Free',           5368709120,   0.00,   0.00, 1),
(2, 'Starter',       16106127360,  4.99,  49.99, 2),
(3, 'Professional',  53687091200,  9.99,  99.99, 3),
(4, 'Business',     161061273600, 19.99, 199.99, 4),
(5, 'Enterprise',   536870912000, 49.99, 499.99, 5);

-- Gemini AI and Appearance settings (run: INSERT IGNORE to be safe on existing installs)
INSERT IGNORE INTO settings (`key`, `value`, `group`) VALUES
('gemini_api_key',    '',                 'ai'),
('gemini_model',      'gemini-2.0-flash', 'ai'),
('dark_mode_enabled', '0',               'appearance');