-- Template Manager Database Schema
-- Self-learning template and feature management system

-- Drop tables if they exist (for development)
DROP TABLE IF EXISTS feature_usage CASCADE;
DROP TABLE IF EXISTS custom_features CASCADE;
DROP TABLE IF EXISTS template_features CASCADE;
DROP TABLE IF EXISTS templates CASCADE;

-- Enable UUID extension (only if we have permission)
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'uuid-ossp') THEN
        BEGIN
            CREATE EXTENSION "uuid-ossp";
        EXCEPTION WHEN insufficient_privilege THEN
            RAISE NOTICE 'uuid-ossp extension creation failed due to insufficient privileges. Using alternative UUID generation.';
        END;
    END IF;
END $$;

-- Templates table
CREATE TABLE templates (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  type VARCHAR(100) NOT NULL UNIQUE,
  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),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Template features table
CREATE TABLE template_features (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  template_id UUID REFERENCES templates(id) ON DELETE CASCADE,
  feature_id VARCHAR(100) NOT NULL,
  name VARCHAR(200) NOT NULL,
  description TEXT,
  feature_type VARCHAR(50) NOT NULL CHECK (feature_type IN ('essential', 'suggested', 'custom')),
  complexity VARCHAR(50) NOT NULL CHECK (complexity IN ('low', 'medium', 'high')),
  display_order INTEGER DEFAULT 0,
  usage_count INTEGER DEFAULT 0,
  user_rating FLOAT DEFAULT 0 CHECK (user_rating >= 0 AND user_rating <= 5),
  is_default BOOLEAN DEFAULT true,
  created_by_user BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(template_id, feature_id)
);

-- Feature usage tracking
CREATE TABLE feature_usage (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  template_id UUID REFERENCES templates(id) ON DELETE CASCADE,
  feature_id UUID REFERENCES template_features(id) ON DELETE CASCADE,
  user_session VARCHAR(100),
  project_id VARCHAR(100),
  selected_at TIMESTAMP DEFAULT NOW()
);

-- User-added custom features
CREATE TABLE custom_features (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  template_id UUID REFERENCES templates(id) ON DELETE CASCADE,
  name VARCHAR(200) NOT NULL,
  description TEXT,
  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()
);

-- Indexes for performance
CREATE INDEX idx_templates_category ON templates(category);
CREATE INDEX idx_templates_type ON templates(type);
CREATE INDEX idx_template_features_template_id ON template_features(template_id);
CREATE INDEX idx_template_features_type ON template_features(feature_type);
CREATE INDEX idx_template_features_usage_count ON template_features(usage_count DESC);
CREATE INDEX idx_feature_usage_template_id ON feature_usage(template_id);
CREATE INDEX idx_feature_usage_selected_at ON feature_usage(selected_at DESC);
CREATE INDEX idx_custom_features_template_id ON custom_features(template_id);
CREATE INDEX idx_custom_features_approved ON custom_features(approved);

-- Update timestamps trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Apply triggers
CREATE TRIGGER update_templates_updated_at BEFORE UPDATE ON templates
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_template_features_updated_at BEFORE UPDATE ON template_features
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_custom_features_updated_at BEFORE UPDATE ON custom_features
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Insert success message
INSERT INTO templates (type, title, description, category) 
VALUES ('_migration_test', 'Migration Test', 'Schema created successfully', 'System')
ON CONFLICT (type) DO NOTHING;

SELECT 'Template Manager database schema created successfully!' as message;