-- P0: Core API Infrastructure
-- 1. Update assets table for frame tracking
ALTER TABLE videos ADD COLUMN IF NOT EXISTS total_frames BIGINT DEFAULT 0;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS processing_status VARCHAR(20) DEFAULT 'REGISTERED';

-- 2. Create Jobs table for scheduling and tracking
CREATE TABLE IF NOT EXISTS jobs (
    id UUID PRIMARY KEY,
    asset_uuid VARCHAR(32) NOT NULL REFERENCES dev.videos(uuid) ON DELETE CASCADE,
    processor_list TEXT[],
    assigned_processor_id UUID,
    rule VARCHAR(20),
    status VARCHAR(20) DEFAULT 'QUEUED',
    total_frames BIGINT DEFAULT 0,
    processed_frames BIGINT DEFAULT 0,
    error_message TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 3. Create chunks_rule1 table for sentence-level chunking
CREATE TABLE IF NOT EXISTS chunks_rule1 (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    asset_uuid VARCHAR(32) NOT NULL REFERENCES dev.videos(uuid) ON DELETE CASCADE,
    start_frame BIGINT NOT NULL,
    end_frame BIGINT NOT NULL,
    content TEXT NOT NULL,
    speaker_id VARCHAR(50),
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_chunks_rule1_asset ON chunks_rule1(asset_uuid);
CREATE INDEX IF NOT EXISTS idx_chunks_rule1_frames ON chunks_rule1(start_frame, end_frame);

-- 4. Indexes
CREATE INDEX IF NOT EXISTS idx_jobs_asset ON jobs(asset_uuid);
CREATE INDEX IF NOT EXISTS idx_jobs_status ON jobs(status);
