-- Migration: Add custom_templates table
-- This follows the same pattern as custom_features but for templates

-- Note: Using gen_random_uuid() which is available by default in PostgreSQL

-- Create custom_templates table (only if it doesn't exist)
CREATE TABLE IF NOT EXISTS custom_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type VARCHAR(100) NOT NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT,
  icon VARCHAR(50),
  category VARCHAR(100) NOT NULL,
  gradient VARCHAR(100),
  border VARCHAR(100),
  text VARCHAR(100),
  subtext VARCHAR(100),
  complexity VARCHAR(50) NOT NULL CHECK (complexity IN ('low', 'medium', 'high')),
  business_rules JSONB,
  technical_requirements JSONB,
  approved BOOLEAN DEFAULT false,
  usage_count INTEGER DEFAULT 1,
  created_by_user_session VARCHAR(100),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  -- Admin approval workflow fields
  status VARCHAR(50) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'duplicate')),
  admin_notes TEXT,
  admin_reviewed_at TIMESTAMP,
  admin_reviewed_by VARCHAR(100),
  canonical_template_id UUID REFERENCES templates(id) ON DELETE SET NULL,
  similarity_score FLOAT CHECK (similarity_score >= 0 AND similarity_score <= 1)
);

-- Create indexes for performance (only if they don't exist)
CREATE INDEX IF NOT EXISTS idx_custom_templates_type ON custom_templates(type);
CREATE INDEX IF NOT EXISTS idx_custom_templates_category ON custom_templates(category);
CREATE INDEX IF NOT EXISTS idx_custom_templates_status ON custom_templates(status);
CREATE INDEX IF NOT EXISTS idx_custom_templates_approved ON custom_templates(approved);
CREATE INDEX IF NOT EXISTS idx_custom_templates_usage_count ON custom_templates(usage_count DESC);
CREATE INDEX IF NOT EXISTS idx_custom_templates_created_at ON custom_templates(created_at DESC);

-- Apply update trigger (only if it doesn't exist)
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_custom_templates_updated_at') THEN
        CREATE TRIGGER update_custom_templates_updated_at BEFORE UPDATE ON custom_templates
            FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
    END IF;
END $$;

-- Insert success message
SELECT 'Custom templates table created successfully!' as message;
