-- FireRed LoRA Job Queue Schema
-- Run this in Supabase SQL editor

CREATE TABLE IF NOT EXISTS edit_jobs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),

  -- Input
  prompt TEXT NOT NULL,
  input_image_url TEXT NOT NULL,          -- R2 URL
  steps INT DEFAULT 6,
  cfg FLOAT DEFAULT 1.0,
  seed INT DEFAULT 42,

  -- Status: queued → processing → completed → failed
  status TEXT DEFAULT 'queued' CHECK (status IN ('queued', 'processing', 'completed', 'failed', 'cancelled')),

  -- Output
  output_image_url TEXT,                   -- R2 URL when done
  output_b64 TEXT,                         -- Optional: inline base64

  -- Timing
  estimated_time_s FLOAT DEFAULT 3.5,
  processing_started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  actual_time_s FLOAT,

  -- Routing
  gpu_id TEXT,                             -- Which GPU/replica processed this
  baseten_request_id TEXT,                 -- Baseten async request ID
  priority INT DEFAULT 1,                  -- 0=highest, 1=default, 2=low

  -- Error handling
  error_message TEXT,
  retry_count INT DEFAULT 0,
  max_retries INT DEFAULT 3,

  -- Metadata
  client_id TEXT,                          -- Who submitted this job
  metadata JSONB DEFAULT '{}'
);

-- Indexes for queue operations
CREATE INDEX IF NOT EXISTS idx_edit_jobs_status ON edit_jobs(status);
CREATE INDEX IF NOT EXISTS idx_edit_jobs_status_priority ON edit_jobs(status, priority, created_at);
CREATE INDEX IF NOT EXISTS idx_edit_jobs_client ON edit_jobs(client_id);
CREATE INDEX IF NOT EXISTS idx_edit_jobs_created ON edit_jobs(created_at);

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER edit_jobs_updated_at
  BEFORE UPDATE ON edit_jobs
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

-- Queue stats view
CREATE OR REPLACE VIEW queue_stats AS
SELECT
  status,
  COUNT(*) as count,
  AVG(actual_time_s) FILTER (WHERE status = 'completed') as avg_time_s,
  MIN(created_at) FILTER (WHERE status = 'queued') as oldest_queued,
  COUNT(*) FILTER (WHERE status = 'queued') as queue_depth
FROM edit_jobs
GROUP BY status;

-- Enable RLS
ALTER TABLE edit_jobs ENABLE ROW LEVEL SECURITY;

-- Allow service role full access
CREATE POLICY "Service role full access" ON edit_jobs
  FOR ALL USING (true) WITH CHECK (true);
