-- 003_codex.sql
-- Per-client Codex/ChatGPT credentials + image content type

-- Codex credentials per user
CREATE TABLE IF NOT EXISTS codex_credentials (
  user_id           UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  encrypted_auth    TEXT NOT NULL,
  status            TEXT NOT NULL DEFAULT 'connected'
                    CHECK (status IN ('connected','disconnected','expired')),
  connected_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_used_at      TIMESTAMPTZ,
  last_refreshed_at TIMESTAMPTZ
);

-- Add image content type to enum (idempotent via DO block)
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM pg_type t
    JOIN pg_enum e ON t.oid = e.enumtypid
    WHERE t.typname = 'content_type_enum'
  ) THEN
    ALTER TYPE content_type_enum ADD VALUE IF NOT EXISTS 'image';
  END IF;
EXCEPTION WHEN others THEN NULL;
END$$;

-- Add file_path column to generated_content for PNG storage
ALTER TABLE generated_content
  ADD COLUMN IF NOT EXISTS file_path TEXT;

-- Index for fast per-user codex status lookup
CREATE INDEX IF NOT EXISTS idx_codex_creds_user
  ON codex_credentials(user_id);