-- =====================================================================
-- Movie Platform Schema — standalone PHP/MySQL version (no WordPress)
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- 1. USERS  (replaces wp_users entirely)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name                VARCHAR(150) NOT NULL,
    email               VARCHAR(255) NOT NULL,
    password_hash       VARCHAR(255) NOT NULL,
    role                ENUM('admin','blogger','viewer') NOT NULL DEFAULT 'viewer',
    is_premium          TINYINT(1) NOT NULL DEFAULT 0,
    premium_expires_at  DATETIME NULL,
    email_verified_at   DATETIME NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 2. BLOGGER PROFILES  (extends a user with role='blogger')
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bloggers (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             INT UNSIGNED NOT NULL,
    bio                 TEXT NULL,
    payout_method       ENUM('bank_transfer','paypal','crypto','other') DEFAULT 'bank_transfer',
    payout_details      JSON NULL,
    status              ENUM('pending','active','suspended') NOT NULL DEFAULT 'pending',
    revenue_share_pct   DECIMAL(5,2) NOT NULL DEFAULT 60.00,
    total_earned        DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_paid_out      DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_user (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 3. MOVIES
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS movies (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uploader_id         INT UNSIGNED NOT NULL,
    title               VARCHAR(255) NOT NULL,
    slug                VARCHAR(255) NOT NULL,
    description         TEXT NULL,
    poster_url          VARCHAR(500) NULL,
    trailer_url         VARCHAR(500) NULL,
    release_year        SMALLINT UNSIGNED NULL,
    genre               VARCHAR(150) NULL,
    language            VARCHAR(50) NULL,
    duration_seconds    INT UNSIGNED NULL,
    storage_provider    ENUM('s3','r2','bunny','cloudflare_stream','local') NOT NULL DEFAULT 'local',
    storage_key         VARCHAR(500) NULL,
    transcode_status    ENUM('pending','processing','ready','failed') NOT NULL DEFAULT 'pending',
    hls_manifest_url    VARCHAR(500) NULL,
    download_url        VARCHAR(500) NULL,
    file_size_mb        INT UNSIGNED NULL,
    is_premium_only     TINYINT(1) NOT NULL DEFAULT 0,
    licensing_status    ENUM('original','licensed','public_domain','pending_review') NOT NULL DEFAULT 'pending_review',
    status              ENUM('processing','live','rejected','removed') NOT NULL DEFAULT 'processing',
    rejection_reason    VARCHAR(500) NULL,
    views_count         BIGINT UNSIGNED NOT NULL DEFAULT 0,
    downloads_count     BIGINT UNSIGNED NOT NULL DEFAULT 0,
    watch_minutes_total BIGINT UNSIGNED NOT NULL DEFAULT 0,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_slug (slug),
    KEY idx_uploader (uploader_id),
    KEY idx_status (status),
    FULLTEXT KEY ft_title_desc (title, description),
    FOREIGN KEY (uploader_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 4. DMCA / TAKEDOWN LOG
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS takedown_requests (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    movie_id            INT UNSIGNED NOT NULL,
    requester_name      VARCHAR(255) NOT NULL,
    requester_email     VARCHAR(255) NOT NULL,
    claim_details       TEXT NOT NULL,
    status              ENUM('open','reviewing','removed','rejected') NOT NULL DEFAULT 'open',
    resolved_at         DATETIME NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (movie_id) REFERENCES movies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 5. VIEW / DOWNLOAD EVENTS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS events (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    movie_id            INT UNSIGNED NOT NULL,
    user_id             INT UNSIGNED NULL,
    event_type          ENUM('view_start','view_progress','download','ad_impression') NOT NULL,
    watch_seconds       INT UNSIGNED NULL,
    is_premium_viewer   TINYINT(1) NOT NULL DEFAULT 0,
    ip_address          VARBINARY(16) NULL,
    user_agent          VARCHAR(255) NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_movie_time (movie_id, created_at),
    FOREIGN KEY (movie_id) REFERENCES movies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 6. PLANS + SUBSCRIPTIONS (you still need a payment gateway, e.g. Stripe,
--    to actually charge cards — this just tracks entitlement state)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS plans (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name                VARCHAR(100) NOT NULL,
    price               DECIMAL(10,2) NOT NULL,
    billing_interval    ENUM('monthly','yearly','lifetime') NOT NULL DEFAULT 'monthly',
    max_downloads_per_month INT UNSIGNED NULL,
    is_active           TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscriptions (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             INT UNSIGNED NOT NULL,
    plan_id             INT UNSIGNED NOT NULL,
    external_ref        VARCHAR(255) NULL,       -- Strowallet checkout reference
    status              ENUM('active','past_due','cancelled','expired') NOT NULL DEFAULT 'active',
    current_period_end  DATETIME NOT NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_user (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tracks a Strowallet checkout from initiation through to verified payment.
-- Needed because the callback alone isn't proof of payment (see
-- StrowalletGateway::verifyPayment) — this row is how the callback handler
-- knows which user/plan a given reference belongs to.
CREATE TABLE IF NOT EXISTS payments (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             INT UNSIGNED NOT NULL,
    plan_id             INT UNSIGNED NOT NULL,
    reference           VARCHAR(255) NOT NULL,
    amount              DECIMAL(12,2) NOT NULL,
    currency            VARCHAR(10) NOT NULL,
    status              ENUM('pending','paid','failed') NOT NULL DEFAULT 'pending',
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    paid_at             DATETIME NULL,
    UNIQUE KEY uniq_reference (reference),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 7. EARNINGS + PAYOUTS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS earnings (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    blogger_id          INT UNSIGNED NOT NULL,
    movie_id            INT UNSIGNED NOT NULL,
    period_start        DATE NOT NULL,
    period_end          DATE NOT NULL,
    views_counted       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    ad_revenue_gross    DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    blogger_share       DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status              ENUM('pending','approved','paid') NOT NULL DEFAULT 'pending',
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (blogger_id) REFERENCES bloggers(id) ON DELETE CASCADE,
    FOREIGN KEY (movie_id) REFERENCES movies(id) ON DELETE CASCADE,
    KEY idx_period (period_start, period_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payouts (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    blogger_id          INT UNSIGNED NOT NULL,
    amount              DECIMAL(12,2) NOT NULL,
    method              VARCHAR(50) NOT NULL,
    reference           VARCHAR(255) NULL,
    status              ENUM('requested','processing','completed','failed') NOT NULL DEFAULT 'requested',
    requested_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at        DATETIME NULL,
    FOREIGN KEY (blogger_id) REFERENCES bloggers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 8. API KEYS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS api_keys (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             INT UNSIGNED NOT NULL,
    api_key_hash        CHAR(64) NOT NULL,
    label               VARCHAR(150) NULL,
    rate_limit_per_min  INT UNSIGNED NOT NULL DEFAULT 60,
    request_count       INT UNSIGNED NOT NULL DEFAULT 0,   -- requests seen in the current window
    window_started_at   DATETIME NULL,                     -- start of the current 1-minute window
    is_active           TINYINT(1) NOT NULL DEFAULT 1,
    last_used_at        DATETIME NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_hash (api_key_hash),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------------
-- Seed a default premium plan (adjust price/currency to taste — Strowallet
-- supports NGN and USD)
-- ---------------------------------------------------------------------
INSERT INTO plans (name, price, billing_interval, is_active)
VALUES ('Premium Monthly', 2500.00, 'monthly', 1);

-- ---------------------------------------------------------------------
-- Seed admin account for VerglasNetwork — CHANGE THIS PASSWORD immediately
-- after your first login (Dashboard has no built-in "change password" page
-- yet — update it directly via the `users` table with a freshly generated
-- hash: php -r "echo password_hash('YourNewPassword', PASSWORD_BCRYPT);"
--
--   Email:    admin@verglasnetwork.com
--   Password: Verglas@Admin2026!
--
-- This hash was generated with bcrypt (cost 10) and is compatible with
-- PHP's password_verify() — verified against the $2b$ format PHP accepts.
-- ---------------------------------------------------------------------
INSERT INTO users (name, email, password_hash, role)
VALUES ('Admin', 'admin@verglasnetwork.com', '$2b$10$ifVINEqYPgC9.zJ9X2sdb.i/jCUDjkwUqNIBwUZ44cAcQVNoeIqau', 'admin')
ON DUPLICATE KEY UPDATE email = email;

-- ---------------------------------------------------------------------
-- To create additional admins later, don't insert a hash by hand — generate one:
--   php -r "echo password_hash('YourStrongPassword', PASSWORD_BCRYPT);"
-- then:
--   INSERT INTO users (name, email, password_hash, role)
--   VALUES ('Admin', 'admin@example.com', '<paste hash here>', 'admin');
-- ---------------------------------------------------------------------