-- Migration Spec: 004_restore_document_chunk_1536_tenant_aware
-- Purpose: Normalize document chunk storage toward the Batch 6 target state.
-- Status: SPEC ONLY. Do not execute without explicit DB truth review and user approval.
-- Scope:
--   - Rename or create target table `document_chunk`.
--   - Preserve legacy `document_chunks` data where present.
--   - Add tenant-aware fields: user_id, project_id.
--   - Add vector/lifecycle fields: embedding, embedding_status, error_log.
--   - Enforce natural uniqueness on tenant + file + chunk.
--   - Keep vector dimension aligned to OpenAI text-embedding-3-small (1536).

BEGIN;

-- 1. Ensure pgvector is available before any vector column is created.
CREATE EXTENSION IF NOT EXISTS vector;

-- 2. Rename legacy table if it exists and target table does not exist yet.
DO $$
BEGIN
  IF to_regclass('public.document_chunks') IS NOT NULL
     AND to_regclass('public.document_chunk') IS NULL THEN
    ALTER TABLE public.document_chunks RENAME TO document_chunk;
  END IF;
END $$;

-- 3. Create target table if neither legacy nor target table exists.
CREATE TABLE IF NOT EXISTS public.document_chunk (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id text,
  project_id text,
  file_hash text NOT NULL,
  title text NOT NULL,
  chunk_index integer NOT NULL,
  metadata_header text,
  content text NOT NULL,
  embedding vector(1536),
  embedding_status text NOT NULL DEFAULT 'PENDING',
  error_log text,
  created_at timestamptz(6) NOT NULL DEFAULT now()
);

-- 4. Add target-state columns for renamed legacy tables.
ALTER TABLE public.document_chunk
  ADD COLUMN IF NOT EXISTS user_id text,
  ADD COLUMN IF NOT EXISTS project_id text,
  ADD COLUMN IF NOT EXISTS metadata_header text,
  ADD COLUMN IF NOT EXISTS embedding vector(1536),
  ADD COLUMN IF NOT EXISTS embedding_status text NOT NULL DEFAULT 'PENDING',
  ADD COLUMN IF NOT EXISTS error_log text,
  ADD COLUMN IF NOT EXISTS created_at timestamptz(6) NOT NULL DEFAULT now();

-- 5. Ensure required columns stay required after legacy normalization.
ALTER TABLE public.document_chunk
  ALTER COLUMN file_hash SET NOT NULL,
  ALTER COLUMN title SET NOT NULL,
  ALTER COLUMN chunk_index SET NOT NULL,
  ALTER COLUMN content SET NOT NULL,
  ALTER COLUMN embedding_status SET DEFAULT 'PENDING',
  ALTER COLUMN embedding_status SET NOT NULL;

-- 6. Replace legacy uniqueness with tenant-aware natural uniqueness.
ALTER TABLE public.document_chunk
  DROP CONSTRAINT IF EXISTS document_chunks_file_hash_chunk_index_key,
  DROP CONSTRAINT IF EXISTS document_chunk_file_hash_chunk_index_key,
  DROP CONSTRAINT IF EXISTS document_chunk_user_id_project_id_file_hash_chunk_index_key;

ALTER TABLE public.document_chunk
  ADD CONSTRAINT document_chunk_user_id_project_id_file_hash_chunk_index_key
  UNIQUE (user_id, project_id, file_hash, chunk_index);

-- 7. Add query-supporting indexes without forcing destructive changes.
CREATE INDEX IF NOT EXISTS document_chunk_user_project_idx
  ON public.document_chunk (user_id, project_id);

CREATE INDEX IF NOT EXISTS document_chunk_file_hash_idx
  ON public.document_chunk (file_hash);

CREATE INDEX IF NOT EXISTS document_chunk_embedding_status_idx
  ON public.document_chunk (embedding_status);

COMMIT;

-- Rollback Notes:
-- 1. Do not run rollback automatically on production. Capture DB backup first.
-- 2. If target table was renamed from `document_chunks`, rollback can rename it back:
--      ALTER TABLE public.document_chunk RENAME TO document_chunks;
-- 3. If only new columns were added and runtime must return to legacy behavior, keep the columns
--    but stop writing to user_id/project_id/embedding/embedding_status/error_log from application code.
-- 4. Dropping target columns is destructive and should only happen after confirming no comparative
--    retrieval or ingestion lifecycle code depends on them:
--      ALTER TABLE public.document_chunk DROP COLUMN IF EXISTS embedding;
--      ALTER TABLE public.document_chunk DROP COLUMN IF EXISTS embedding_status;
--      ALTER TABLE public.document_chunk DROP COLUMN IF EXISTS error_log;
--      ALTER TABLE public.document_chunk DROP COLUMN IF EXISTS user_id;
--      ALTER TABLE public.document_chunk DROP COLUMN IF EXISTS project_id;
-- 5. Recreate legacy uniqueness only after deduplicating tenant rows:
--      ALTER TABLE public.document_chunk ADD CONSTRAINT document_chunk_file_hash_chunk_index_key
--      UNIQUE (file_hash, chunk_index);
-- 6. If migration fails mid-transaction, PostgreSQL should rollback due to BEGIN/COMMIT scope.
--    Confirm with table inspection before retrying.
