-- Migration: Add revision status, labels, and comparison support
-- Phase 1 of enhanced revision control

-- Add status field to revisions
-- Values: 'draft' (default), 'review', 'released', 'obsolete'
ALTER TABLE revisions ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT 'draft';

-- Add labels array for arbitrary tags (e.g., 'prototype', 'v1.0', 'customer-approved')
ALTER TABLE revisions ADD COLUMN IF NOT EXISTS labels TEXT[] NOT NULL DEFAULT '{}';

-- Add index for filtering by status
CREATE INDEX IF NOT EXISTS idx_revisions_status ON revisions(status);

-- Add index for label searches (GIN index for array containment queries)
CREATE INDEX IF NOT EXISTS idx_revisions_labels ON revisions USING GIN(labels);

-- Add composite index for common query pattern (item + status)
CREATE INDEX IF NOT EXISTS idx_revisions_item_status ON revisions(item_id, status);

-- Create a view for easy revision comparison data
CREATE OR REPLACE VIEW revision_summary AS
SELECT
    r.id,
    r.item_id,
    i.part_number,
    r.revision_number,
    r.status,
    r.labels,
    r.file_key IS NOT NULL AS has_file,
    r.file_size,
    r.file_checksum,
    r.created_at,
    r.created_by,
    r.comment,
    jsonb_object_keys(r.properties) AS property_keys,
    (SELECT COUNT(*) FROM jsonb_object_keys(r.properties)) AS property_count
FROM revisions r
JOIN items i ON i.id = r.item_id;

-- Add check constraint for valid status values
ALTER TABLE revisions DROP CONSTRAINT IF EXISTS revisions_status_check;
ALTER TABLE revisions ADD CONSTRAINT revisions_status_check
    CHECK (status IN ('draft', 'review', 'released', 'obsolete'));

-- Comment on new columns
COMMENT ON COLUMN revisions.status IS 'Workflow status: draft, review, released, obsolete';
COMMENT ON COLUMN revisions.labels IS 'Arbitrary tags/labels for categorization';