-- TR.EE schema (PostgreSQL). Mirrors the Node/Prisma models.
-- Apply with: psql "$DATABASE_URL" -f schema.sql

CREATE TABLE IF NOT EXISTS clients (
    id          TEXT PRIMARY KEY,
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creators (
    id          TEXT PRIMARY KEY,
    name        TEXT NOT NULL,
    handle      TEXT NOT NULL,
    platforms   JSONB NOT NULL DEFAULT '{}',   -- { instagram, tiktok, youtube, snapchat }
    stats       JSONB,
    external_id TEXT,
    created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS users (
    id         TEXT PRIMARY KEY,
    login_id   TEXT UNIQUE NOT NULL,
    password   TEXT NOT NULL,                  -- store a hash in production
    name       TEXT NOT NULL,
    role       TEXT NOT NULL,                  -- agency | client | creator
    active     BOOLEAN NOT NULL DEFAULT TRUE,
    client_id  TEXT REFERENCES clients(id) ON DELETE CASCADE,
    creator_id TEXT REFERENCES creators(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS campaigns (
    id         TEXT PRIMARY KEY,
    name       TEXT NOT NULL,
    brief      TEXT,
    budget     INTEGER NOT NULL DEFAULT 0,
    status     TEXT NOT NULL DEFAULT 'sourcing',
    start_date TEXT,
    end_date   TEXT,
    tracking   JSONB NOT NULL DEFAULT '{}',    -- { hashtag, igMention, ttMention }
    client_id  TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS campaign_profiles (
    id          TEXT PRIMARY KEY,
    campaign_id TEXT NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
    creator_id  TEXT NOT NULL REFERENCES creators(id) ON DELETE CASCADE,
    status      TEXT NOT NULL DEFAULT 'pending',   -- pending | approved | rejected
    client_note TEXT,
    email_sent  BOOLEAN NOT NULL DEFAULT FALSE,
    spark_code  TEXT,
    committed   INTEGER NOT NULL DEFAULT 1,
    track_tags  JSONB NOT NULL DEFAULT '[]',       -- [{ value, platform }]
    start_date  TEXT,
    end_date    TEXT,
    content     JSONB NOT NULL DEFAULT '{}'        -- current in-flight video + review rounds
);

CREATE TABLE IF NOT EXISTS deliverables (
    id         TEXT PRIMARY KEY,
    profile_id TEXT NOT NULL REFERENCES campaign_profiles(id) ON DELETE CASCADE,
    video_name TEXT NOT NULL,
    rounds     JSONB NOT NULL DEFAULT '[]',
    post_link  TEXT,
    posted_at  TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS posts (
    id            TEXT PRIMARY KEY,
    profile_id    TEXT NOT NULL REFERENCES campaign_profiles(id) ON DELETE CASCADE,
    platform      TEXT NOT NULL,
    external_id   TEXT,
    link          TEXT NOT NULL,
    tracked_via   TEXT NOT NULL,               -- hashtag | igmention | ttmention | spark | tag
    tracked_value TEXT NOT NULL,
    metrics       JSONB,
    posted_at     TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS tokens (
    id         TEXT PRIMARY KEY,
    platform   TEXT NOT NULL,                  -- instagram | tiktok
    creator_id TEXT REFERENCES creators(id) ON DELETE CASCADE,
    access     TEXT NOT NULL,
    refresh    TEXT,
    expires_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_campaigns_client ON campaigns(client_id);
CREATE INDEX IF NOT EXISTS idx_profiles_campaign ON campaign_profiles(campaign_id);
CREATE INDEX IF NOT EXISTS idx_posts_profile ON posts(profile_id);