-- ==============================================================================
-- Simple YouTube Watch-Time Tracking Portal - Initial Schema Migration
-- ==============================================================================

-- Enable UUID extension if not already available
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- 1. videos
CREATE TABLE IF NOT EXISTS videos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    youtube_video_id TEXT NOT NULL,
    title TEXT NOT NULL,
    thumbnail_url TEXT,
    duration_seconds INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index for searching youtube_video_id
CREATE INDEX IF NOT EXISTS idx_videos_youtube_id ON videos(youtube_video_id);

-- 2. recipients
CREATE TABLE IF NOT EXISTS recipients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 3. video_assignments
CREATE TABLE IF NOT EXISTS video_assignments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    video_id UUID NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
    recipient_id UUID NOT NULL REFERENCES recipients(id) ON DELETE CASCADE,
    token_hash TEXT NOT NULL UNIQUE,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index for fast token lookup
CREATE INDEX IF NOT EXISTS idx_assignments_token_hash ON video_assignments(token_hash);
CREATE INDEX IF NOT EXISTS idx_assignments_video_id ON video_assignments(video_id);
CREATE INDEX IF NOT EXISTS idx_assignments_recipient_id ON video_assignments(recipient_id);

-- 4. watch_sessions
CREATE TABLE IF NOT EXISTS watch_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    assignment_id UUID NOT NULL REFERENCES video_assignments(id) ON DELETE CASCADE,
    started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_heartbeat_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    ended_at TIMESTAMPTZ,
    total_watch_seconds NUMERIC NOT NULL DEFAULT 0,
    maximum_position_seconds NUMERIC NOT NULL DEFAULT 0,
    status TEXT NOT NULL DEFAULT 'Watching', -- 'Watching', 'Paused', 'Completed', 'Inactive'
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sessions_assignment_id ON watch_sessions(assignment_id);
CREATE INDEX IF NOT EXISTS idx_sessions_last_heartbeat ON watch_sessions(last_heartbeat_at);

-- 5. watch_events (for reliable deduplication and playback validation)
CREATE TABLE IF NOT EXISTS watch_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES watch_sessions(id) ON DELETE CASCADE,
    assignment_id UUID NOT NULL REFERENCES video_assignments(id) ON DELETE CASCADE,
    event_type TEXT NOT NULL, -- 'play', 'pause', 'seek', 'heartbeat', 'ended', 'visibility_hidden'
    position_seconds NUMERIC NOT NULL DEFAULT 0,
    playback_rate NUMERIC NOT NULL DEFAULT 1.0,
    sequence_number INTEGER NOT NULL,
    client_timestamp TIMESTAMPTZ,
    server_timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    delta_seconds NUMERIC NOT NULL DEFAULT 0,
    CONSTRAINT uq_session_sequence UNIQUE (session_id, sequence_number)
);

CREATE INDEX IF NOT EXISTS idx_events_session_id ON watch_events(session_id);
CREATE INDEX IF NOT EXISTS idx_events_assignment_id ON watch_events(assignment_id);

-- Enable Row Level Security (RLS) on all tables
ALTER TABLE videos ENABLE ROW LEVEL SECURITY;
ALTER TABLE recipients ENABLE ROW LEVEL SECURITY;
ALTER TABLE video_assignments ENABLE ROW LEVEL SECURITY;
ALTER TABLE watch_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE watch_events ENABLE ROW LEVEL SECURITY;

-- Service Role policies: Service role has full access (used by server-side API routes)
CREATE POLICY "Service role full access on videos" ON videos FOR ALL TO service_role USING (true) WITH CHECK (true);
CREATE POLICY "Service role full access on recipients" ON recipients FOR ALL TO service_role USING (true) WITH CHECK (true);
CREATE POLICY "Service role full access on video_assignments" ON video_assignments FOR ALL TO service_role USING (true) WITH CHECK (true);
CREATE POLICY "Service role full access on watch_sessions" ON watch_sessions FOR ALL TO service_role USING (true) WITH CHECK (true);
CREATE POLICY "Service role full access on watch_events" ON watch_events FOR ALL TO service_role USING (true) WITH CHECK (true);

-- Authenticated Admin Policies (direct authenticated access if using client-side auth)
CREATE POLICY "Authenticated admins can manage videos" ON videos FOR ALL TO authenticated USING (true) WITH CHECK (true);
CREATE POLICY "Authenticated admins can manage recipients" ON recipients FOR ALL TO authenticated USING (true) WITH CHECK (true);
CREATE POLICY "Authenticated admins can manage video_assignments" ON video_assignments FOR ALL TO authenticated USING (true) WITH CHECK (true);
CREATE POLICY "Authenticated admins can manage watch_sessions" ON watch_sessions FOR ALL TO authenticated USING (true) WITH CHECK (true);
CREATE POLICY "Authenticated admins can manage watch_events" ON watch_events FOR ALL TO authenticated USING (true) WITH CHECK (true);