-- Mastreams PostgreSQL Schema
-- Run: psql -U postgres -d mastreams -f schema.sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ─────────────────────────────────────────
-- CATEGORIES
-- ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS categories (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    slug        VARCHAR(100) UNIQUE NOT NULL,
    is_active   BOOLEAN DEFAULT true,
    display_order INTEGER DEFAULT 0,
    created_at  TIMESTAMP DEFAULT NOW()
);

INSERT INTO categories (name, slug, display_order) VALUES
  ('Drama','drama',1),('Thriller','thriller',2),('Crime','crime',3),
  ('Documentary','documentary',4),('Comedy','comedy',5),
  ('Romance','romance',6),('Action','action',7)
ON CONFLICT (slug) DO NOTHING;

-- ─────────────────────────────────────────
-- MOVIES
-- ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS movies (
    id              SERIAL PRIMARY KEY,
    title           VARCHAR(255) NOT NULL,
    description     TEXT,
    category_id     INTEGER REFERENCES categories(id),
    tags            TEXT[] DEFAULT '{}',
    rating          DECIMAL(2,1) DEFAULT 0,
    year            INTEGER,
    duration        VARCHAR(30),

    -- R2 storage keys (not full URLs — constructed at runtime)
    poster_key      TEXT,
    banner_key      TEXT,
    -- video_key will be added when Cloudflare Stream is integrated
    -- For now we store direct R2 key for the raw/transcoded files
    video_sd_key    TEXT,   -- 360p transcoded
    video_hd_key    TEXT,   -- 720p transcoded
    video_fhd_key   TEXT,   -- 1080p transcoded
    video_raw_key   TEXT,   -- original upload (kept for re-transcoding)

    size_sd_mb      INTEGER DEFAULT 0,
    size_hd_mb      INTEGER DEFAULT 0,
    size_fhd_mb     INTEGER DEFAULT 0,

    is_rental       BOOLEAN DEFAULT false,
    rental_price    DECIMAL(10,2) DEFAULT 0,
    is_trending     BOOLEAN DEFAULT false,
    is_local        BOOLEAN DEFAULT false,
    status          VARCHAR(20) DEFAULT 'draft', -- draft | published
    views           INTEGER DEFAULT 0,
    downloads       INTEGER DEFAULT 0,

    -- Transcoding state (Cloudflare Stream: coming soon)
    transcode_status VARCHAR(20) DEFAULT 'pending', -- pending | processing | done | failed
    transcode_job_id TEXT,  -- reserved for Cloudflare Stream job ID

    created_at      TIMESTAMP DEFAULT NOW(),
    updated_at      TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_movies_status    ON movies(status);
CREATE INDEX IF NOT EXISTS idx_movies_trending  ON movies(is_trending);
CREATE INDEX IF NOT EXISTS idx_movies_category  ON movies(category_id);

-- ─────────────────────────────────────────
-- USERS  