-- Migration: Add Admin Approval for Custom Features
-- This migration adds admin approval workflow functionality to the existing template manager

-- 1. Add status and admin fields to custom_features (only if they don't exist)
DO $$
BEGIN
    -- Add status column if it doesn't exist
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'custom_features' AND column_name = 'status') THEN
        ALTER TABLE custom_features ADD COLUMN status VARCHAR(20) CHECK (status IN ('pending', 'approved', 'rejected', 'duplicate'));
    END IF;
    
    -- Add admin_notes column if it doesn't exist
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'custom_features' AND column_name = 'admin_notes') THEN
        ALTER TABLE custom_features ADD COLUMN admin_notes TEXT;
    END IF;
    
    -- Add admin_reviewed_at column if it doesn't exist
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'custom_features' AND column_name = 'admin_reviewed_at') THEN
        ALTER TABLE custom_features ADD COLUMN admin_reviewed_at TIMESTAMP;
    END IF;
    
    -- Add admin_reviewed_by column if it doesn't exist
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'custom_features' AND column_name = 'admin_reviewed_by') THEN
        ALTER TABLE custom_features ADD COLUMN admin_reviewed_by VARCHAR(100);
    END IF;
    
    -- Add canonical_feature_id column if it doesn't exist
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'custom_features' AND column_name = 'canonical_feature_id') THEN
        ALTER TABLE custom_features ADD COLUMN canonical_feature_id UUID REFERENCES template_features(id) ON DELETE SET NULL;
    END IF;
    
    -- Add similarity_score column if it doesn't exist
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'custom_features' AND column_name = 'similarity_score') THEN
        ALTER TABLE custom_features ADD COLUMN similarity_score FLOAT;
    END IF;
END $$;

-- Set default values for existing rows (only if status column exists and has data)
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'custom_features' AND column_name = 'status') THEN
        UPDATE custom_features 
        SET status = CASE 
          WHEN approved = true THEN 'approved'
          ELSE 'pending'
        END
        WHERE status IS NULL;
        
        -- Now alter the column to be NOT NULL (only if it's not already NOT NULL)
        IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'custom_features' AND column_name = 'status' AND is_nullable = 'YES') THEN
            ALTER TABLE custom_features ALTER COLUMN status SET NOT NULL;
        END IF;
        
        -- Set default value (only if it doesn't already have one)
        IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'custom_features' AND column_name = 'status' AND column_default IS NOT NULL) THEN
            ALTER TABLE custom_features ALTER COLUMN status SET DEFAULT 'pending';
        END IF;
    END IF;
END $$;

-- 2. Create a table for feature synonyms/aliases (only if it doesn't exist)
CREATE TABLE IF NOT EXISTS feature_synonyms (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  feature_id UUID NOT NULL REFERENCES template_features(id) ON DELETE CASCADE,
  synonym VARCHAR(200) NOT NULL,
  created_by VARCHAR(100),
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(synonym)
);

-- 3. Add index for faster lookups (only if they don't exist)
CREATE INDEX IF NOT EXISTS idx_custom_features_status ON custom_features(status);
CREATE INDEX IF NOT EXISTS idx_custom_features_created_at ON custom_features(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_feature_synonyms_synonym ON feature_synonyms(synonym);

-- 4. Admin notifications table (only if it doesn't exist)
CREATE TABLE IF NOT EXISTS admin_notifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type VARCHAR(50) NOT NULL,
  message TEXT NOT NULL,
  reference_id UUID,
  reference_type VARCHAR(50),
  is_read BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW(),
  read_at TIMESTAMP
);

-- 5. Create indexes for admin notifications (only if they don't exist)
CREATE INDEX IF NOT EXISTS idx_admin_notifications_type ON admin_notifications(type);
CREATE INDEX IF NOT EXISTS idx_admin_notifications_is_read ON admin_notifications(is_read);
CREATE INDEX IF NOT EXISTS idx_admin_notifications_created_at ON admin_notifications(created_at DESC);

-- 6. Clean up orphaned custom_features records before updating status
DO $$
BEGIN
    -- Delete custom_features records that reference non-existent templates
    DELETE FROM custom_features 
    WHERE NOT EXISTS (SELECT 1 FROM templates WHERE id = custom_features.template_id AND is_active = true)
    AND NOT EXISTS (SELECT 1 FROM custom_templates WHERE id = custom_features.template_id);
    
    -- Update existing custom_features to have 'approved' status if they were previously approved
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'custom_features' AND column_name = 'status') THEN
        UPDATE custom_features 
        SET status = CASE 
          WHEN approved = true THEN 'approved'
          ELSE 'pending'
        END,
        admin_reviewed_at = CASE 
          WHEN approved = true THEN created_at
          ELSE NULL
        END,
        admin_reviewed_by = CASE 
          WHEN approved = true THEN 'system_migration'
          ELSE NULL
        END
        WHERE status IS NULL OR admin_reviewed_at IS NULL;
    END IF;
END $$;

-- 7. Insert success message
INSERT INTO templates (type, title, description, category) 
VALUES ('_admin_workflow_migration', 'Admin Workflow Migration', 'Admin approval workflow schema created successfully', 'System')
ON CONFLICT (type) DO NOTHING;

SELECT 'Admin approval workflow database schema created successfully!' as message;
