schema.sql

-- Unison: Crowdsourced Lyrics Database Schema
-- Designed for PostgreSQL

-- Public keys table (cryptographic identity)
CREATE TABLE IF NOT EXISTS public_keys (
    key_id TEXT PRIMARY KEY,
    public_key TEXT NOT NULL,
    created_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER)
);

-- Users table (identity + reputation)
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    key_id TEXT UNIQUE NOT NULL,
    reputation DOUBLE PRECISION DEFAULT 1.0,
    vote_count INTEGER DEFAULT 0,
    avg_vote DOUBLE PRECISION DEFAULT 0,
    created_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER)
);

CREATE INDEX IF NOT EXISTS idx_users_key_id ON users(key_id);

-- User-set nickname (overrides generated petname)
ALTER TABLE users ADD COLUMN IF NOT EXISTS nickname TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS nickname_lower TEXT
    GENERATED ALWAYS AS (LOWER(nickname)) STORED;
ALTER TABLE users ADD COLUMN IF NOT EXISTS nickname_updated_at INTEGER;
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_nickname_lower
    ON users(nickname_lower) WHERE nickname_lower IS NOT NULL;

-- Discord account links (Discord user <-> Better Lyrics key)
CREATE TABLE IF NOT EXISTS discord_links (
    discord_id TEXT PRIMARY KEY,
    key_id TEXT UNIQUE NOT NULL,
    discord_username TEXT,
    linked_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER)
);

CREATE INDEX IF NOT EXISTS idx_discord_links_key_id ON discord_links(key_id);

-- Main lyrics table
CREATE TABLE IF NOT EXISTS lyrics (
    id SERIAL PRIMARY KEY,

    -- YouTube video identifier (primary lookup key)
    video_id TEXT NOT NULL,

    -- Metadata for display
    song TEXT NOT NULL,
    artist TEXT NOT NULL,
    album TEXT,
    isrc TEXT,  -- International Standard Recording Code
    duration INTEGER NOT NULL,  -- in seconds

    -- Normalized values for search (lowercase, stripped)
    song_norm TEXT NOT NULL,
    artist_norm TEXT NOT NULL,

    -- Lyrics content (gzip compressed, base64 encoded)
    lyrics TEXT NOT NULL,
    format TEXT CHECK(format IN ('ttml', 'lrc', 'plain')) NOT NULL DEFAULT 'lrc',

    -- Metadata
    language TEXT,
    sync_type TEXT CHECK(sync_type IN ('richsync', 'linesync', 'plain')) NOT NULL DEFAULT 'linesync',

    -- Quality metrics (raw counts)
    score INTEGER DEFAULT 0,
    upvotes INTEGER DEFAULT 0,
    downvotes INTEGER DEFAULT 0,

    -- Reputation-weighted metrics (computed by batch job)
    effective_score DOUBLE PRECISION DEFAULT 0,
    vote_count INTEGER DEFAULT 0,
    diversity_bonus INTEGER DEFAULT 0,
    confidence TEXT CHECK(confidence IN ('low', 'medium', 'high')) DEFAULT 'low',
    score_updated_at INTEGER,

    -- Timestamps
    created_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER),
    updated_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER),

    -- Submitter info
    submitter_id INTEGER REFERENCES users(id)
);

-- Indexes for lookups
CREATE INDEX IF NOT EXISTS idx_lyrics_video_id ON lyrics(video_id);
CREATE INDEX IF NOT EXISTS idx_lyrics_song_artist ON lyrics(song_norm, artist_norm);

-- Votes table (for quality control)
CREATE TABLE IF NOT EXISTS votes (
    id SERIAL PRIMARY KEY,
    lyrics_id INTEGER NOT NULL REFERENCES lyrics(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id),
    vote INTEGER CHECK(vote IN (-1, 1)) NOT NULL,
    is_self_vote INTEGER DEFAULT 0,
    created_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER),

    UNIQUE(lyrics_id, user_id)
);

CREATE INDEX IF NOT EXISTS idx_votes_lyrics ON votes(lyrics_id);
CREATE INDEX IF NOT EXISTS idx_votes_user ON votes(user_id);

-- Reports table (for flagging bad lyrics)
CREATE TABLE IF NOT EXISTS reports (
    id SERIAL PRIMARY KEY,
    lyrics_id INTEGER NOT NULL REFERENCES lyrics(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id),
    reason TEXT CHECK(reason IN ('wrong_song', 'bad_sync', 'offensive', 'spam', 'other')) NOT NULL,
    details TEXT,
    created_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER),

    UNIQUE(lyrics_id, user_id)
);

CREATE INDEX IF NOT EXISTS idx_reports_lyrics ON reports(lyrics_id);
CREATE INDEX IF NOT EXISTS idx_lyrics_effective_score ON lyrics(effective_score DESC);

-- Migrations
ALTER TABLE lyrics ADD COLUMN IF NOT EXISTS isrc TEXT;

-- Trigram search support
CREATE EXTENSION IF NOT EXISTS pg_trgm;
ALTER TABLE lyrics ADD COLUMN IF NOT EXISTS album_norm TEXT;
UPDATE lyrics SET album_norm = LOWER(TRIM(album)) WHERE album IS NOT NULL AND album_norm IS NULL;
CREATE INDEX IF NOT EXISTS idx_lyrics_song_norm_trgm ON lyrics USING GIN (song_norm gin_trgm_ops);
CREATE INDEX IF NOT EXISTS idx_lyrics_artist_norm_trgm ON lyrics USING GIN (artist_norm gin_trgm_ops);
CREATE INDEX IF NOT EXISTS idx_lyrics_album_norm_trgm ON lyrics USING GIN (album_norm gin_trgm_ops);

-- Full-text search on lyrics content
ALTER TABLE lyrics ADD COLUMN IF NOT EXISTS lyrics_text_search tsvector;
CREATE INDEX IF NOT EXISTS idx_lyrics_text_search ON lyrics USING GIN (lyrics_text_search);

-- Multi-variant lyrics: allow multiple entries per video_id
-- Drop the unique constraint so multiple submissions can coexist
ALTER TABLE lyrics DROP CONSTRAINT IF EXISTS lyrics_video_id_key;

-- Drop the unique submitter constraint (users can submit multiple variants, capped in app logic)
DROP INDEX IF EXISTS idx_lyrics_video_submitter;

-- Composite index for efficient "best variant" lookups
CREATE INDEX IF NOT EXISTS idx_lyrics_video_id_ranking
    ON lyrics(video_id, effective_score DESC);

-- Soft delete for submissions: preserves vote/reputation signal
ALTER TABLE lyrics ADD COLUMN IF NOT EXISTS deleted_at INTEGER;
ALTER TABLE lyrics ADD COLUMN IF NOT EXISTS deleted_by_user_id INTEGER REFERENCES users(id);
ALTER TABLE lyrics ADD COLUMN IF NOT EXISTS deleted_by_role TEXT
    CHECK (deleted_by_role IN ('submitter', 'admin'));
ALTER TABLE lyrics ADD COLUMN IF NOT EXISTS deletion_reason TEXT;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE constraint_name = 'lyrics_deletion_consistency'
    ) THEN
        ALTER TABLE lyrics ADD CONSTRAINT lyrics_deletion_consistency CHECK (
            (deleted_at IS NULL AND deleted_by_user_id IS NULL AND deleted_by_role IS NULL)
            OR (deleted_at IS NOT NULL AND deleted_by_user_id IS NOT NULL AND deleted_by_role IS NOT NULL)
        );
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_lyrics_active ON lyrics(id) WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_lyrics_submitter_created
    ON lyrics(submitter_id, created_at DESC)
    WHERE deleted_at IS NULL;

-- Reputation penalty idempotency: tracks whether the auto-hide / dirty-delete
-- penalty has already been applied for this row.
ALTER TABLE lyrics ADD COLUMN IF NOT EXISTS reputation_penalized BOOLEAN DEFAULT FALSE;

-- Language detection metadata.
-- `language` keeps its semantic meaning (NULL = unknown).
-- `language_source` records whether the language came from the submitter or
-- from the detection service ('submitter' or 'detector'). NULL means we never
-- looked at it (legacy rows).
-- `language_detector_version` records the version of the detection pipeline.
-- NULL means the detector was unreachable when the row was last touched, so
-- the backfill should retry. Non-NULL with the current version means the row
-- is up to date.
-- `language_detection_attempted_at` records the last attempt timestamp for
-- observability.
ALTER TABLE lyrics ADD COLUMN IF NOT EXISTS language_source TEXT
    CHECK (language_source IS NULL OR language_source IN ('submitter', 'detector'));
ALTER TABLE lyrics ADD COLUMN IF NOT EXISTS language_detector_version SMALLINT;
ALTER TABLE lyrics ADD COLUMN IF NOT EXISTS language_detection_attempted_at TIMESTAMPTZ;

-- Index for backfill sweep: pick up rows where detector wasn't authoritative.
CREATE INDEX IF NOT EXISTS idx_lyrics_language_backfill
    ON lyrics(id)
    WHERE COALESCE(language_source, 'detector') <> 'submitter'
      AND deleted_at IS NULL;

-- Lyrics requests: demand signal for songs missing synced lyrics
CREATE TABLE IF NOT EXISTS requested_songs (
    video_id TEXT PRIMARY KEY,
    song TEXT NOT NULL,
    artist TEXT NOT NULL,
    thumbnail_url TEXT,
    first_requested_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER),
    last_requested_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER)
);

CREATE TABLE IF NOT EXISTS lyrics_requests (
    id BIGSERIAL PRIMARY KEY,
    video_id TEXT NOT NULL REFERENCES requested_songs(video_id) ON DELETE CASCADE,
    requester_id TEXT NOT NULL,
    requester_type TEXT CHECK(requester_type IN ('extension', 'discord')) NOT NULL DEFAULT 'extension',
    weight DOUBLE PRECISION NOT NULL DEFAULT 1.0,
    created_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER),

    UNIQUE(video_id, requester_id, requester_type)
);

CREATE INDEX IF NOT EXISTS idx_lyrics_requests_created ON lyrics_requests(created_at);

-- Request fulfillments: recognition when a synced submission fills demand
CREATE TABLE IF NOT EXISTS request_fulfillments (
    id BIGSERIAL PRIMARY KEY,
    video_id TEXT NOT NULL,
    lyrics_id INTEGER NOT NULL REFERENCES lyrics(id) ON DELETE CASCADE,
    submitter_id INTEGER REFERENCES users(id),
    demand_snapshot DOUBLE PRECISION NOT NULL,
    request_count_snapshot INTEGER NOT NULL,
    fulfilled_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER)
);

CREATE INDEX IF NOT EXISTS idx_request_fulfillments_submitter
    ON request_fulfillments(submitter_id);
CREATE INDEX IF NOT EXISTS idx_request_fulfillments_video_fulfilled
    ON request_fulfillments(video_id, fulfilled_at DESC);
CREATE INDEX IF NOT EXISTS idx_request_fulfillments_lyrics
    ON request_fulfillments(lyrics_id);

-- Translation cache: durable per-song translation + romanization from the Google
-- lyrics_translate proxy, keyed so Google is hit at most once per unique song+language
CREATE TABLE IF NOT EXISTS translation_cache (
    id                    BIGSERIAL PRIMARY KEY,
    lyrics_hash           TEXT        NOT NULL,
    from_lang             TEXT        NOT NULL,
    to_lang               TEXT        NOT NULL,
    provider              TEXT        NOT NULL DEFAULT 'google-lyrics-translate',
    video_id              TEXT,
    line_count            INT         NOT NULL,
    detected_source_lang  TEXT,
    has_romanization      BOOLEAN     NOT NULL DEFAULT FALSE,
    is_negative           BOOLEAN     NOT NULL DEFAULT FALSE,
    source_lines          JSONB       NOT NULL,
    lines                 JSONB       NOT NULL,
    google_version        TEXT,
    google_id             TEXT,
    google_token          TEXT,
    http_status           INT,
    raw_payload           TEXT,
    parser_version        INT         NOT NULL,
    failure_count         INT         NOT NULL DEFAULT 0,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at            TIMESTAMPTZ NOT NULL,

    UNIQUE (lyrics_hash, from_lang, to_lang, provider)
);

ALTER TABLE translation_cache ADD COLUMN IF NOT EXISTS failure_count INT NOT NULL DEFAULT 0;

CREATE INDEX IF NOT EXISTS translation_cache_expires_idx ON translation_cache (expires_at);
CREATE INDEX IF NOT EXISTS translation_cache_video_idx   ON translation_cache (video_id);
CREATE INDEX IF NOT EXISTS translation_cache_to_lang_idx ON translation_cache (to_lang);

CREATE TABLE IF NOT EXISTS migration_requests (
    id BIGSERIAL PRIMARY KEY,
    session_id TEXT,
    discord_id TEXT NOT NULL,
    old_key TEXT NOT NULL,
    new_key TEXT NOT NULL,
    status TEXT NOT NULL CHECK (status IN ('preview', 'committed', 'failed')),
    moved_submissions INTEGER NOT NULL DEFAULT 0,
    moved_votes INTEGER NOT NULL DEFAULT 0,
    moved_reports INTEGER NOT NULL DEFAULT 0,
    moved_fulfillments INTEGER NOT NULL DEFAULT 0,
    collisions_dropped INTEGER NOT NULL DEFAULT 0,
    snapshot JSONB,
    error TEXT,
    created_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER),
    updated_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER)
);

CREATE INDEX IF NOT EXISTS idx_migration_requests_discord ON migration_requests(discord_id);

-- ---- gamified reputation ----

CREATE TABLE IF NOT EXISTS contribution_events (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    delta INTEGER NOT NULL,
    kind TEXT NOT NULL,
    ref_type TEXT NOT NULL DEFAULT '',
    ref_id INTEGER NOT NULL DEFAULT 0,
    created_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER),
    UNIQUE(user_id, kind, ref_type, ref_id)
);

CREATE TABLE IF NOT EXISTS badge_awards (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    badge_key TEXT NOT NULL,
    tier INTEGER,
    awarded_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER),
    context TEXT,
    UNIQUE(user_id, badge_key)
);

CREATE TABLE IF NOT EXISTS committee_members (
    user_id INTEGER PRIMARY KEY REFERENCES users(id),
    added_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER),
    added_by TEXT
);

CREATE TABLE IF NOT EXISTS boosts (
    id SERIAL PRIMARY KEY,
    booster_id INTEGER NOT NULL REFERENCES users(id),
    lyrics_id INTEGER NOT NULL REFERENCES lyrics(id) ON DELETE CASCADE,
    created_at INTEGER NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW())::INTEGER),
    revoked_at INTEGER
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_boosts_active_lyric
    ON boosts(lyrics_id) WHERE revoked_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_boosts_booster ON boosts(booster_id);

CREATE TABLE IF NOT EXISTS rejections (
    id SERIAL PRIMARY KEY,
    lyrics_id INTEGER NOT NULL REFERENCES lyrics(id) ON DELETE CASCADE,
    rejected_by INTEGER NOT NULL REFERENCES users(id),
    rejected_at INTEGER NOT NULL,
    note TEXT,
    revoked_at INTEGER
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_rejections_active
    ON rejections(lyrics_id) WHERE revoked_at IS NULL;

ALTER TABLE lyrics ADD COLUMN IF NOT EXISTS committee_approved_at INTEGER;
ALTER TABLE lyrics ADD COLUMN IF NOT EXISTS committee_approved_by INTEGER REFERENCES users(id);

ALTER TABLE users ADD COLUMN IF NOT EXISTS featured_badges TEXT;