-- Fix Schema Conflicts and Prepare for Clean Deployment
-- This script resolves duplicate key constraint violations

-- Create n8n schema if it doesn't exist
CREATE SCHEMA IF NOT EXISTS n8n;

-- Clean up any existing conflicting types/tables
DROP TYPE IF EXISTS claude_recommendations CASCADE;
DROP TABLE IF EXISTS claude_recommendations CASCADE;

-- Clean up n8n related tables if they exist in public schema
DROP TABLE IF EXISTS public.n8n_credentials_entity CASCADE;
DROP TABLE IF EXISTS public.n8n_execution_entity CASCADE;
DROP TABLE IF EXISTS public.n8n_workflow_entity CASCADE;
DROP TABLE IF EXISTS public.n8n_webhook_entity CASCADE;
DROP TABLE IF EXISTS public.n8n_tag_entity CASCADE;
DROP TABLE IF EXISTS public.n8n_workflows_tags CASCADE;

-- Reset any conflicting sequences
DROP SEQUENCE IF EXISTS claude_recommendations_id_seq CASCADE;

-- Ensure proper permissions for n8n schema
GRANT ALL PRIVILEGES ON SCHEMA n8n TO pipeline_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA n8n TO pipeline_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA n8n TO pipeline_admin;

-- Create claude_recommendations table properly (if needed by services)
CREATE TABLE IF NOT EXISTS claude_recommendations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    request_id VARCHAR(255) UNIQUE NOT NULL,
    domain VARCHAR(100) NOT NULL,
    budget DECIMAL(10,2) NOT NULL,
    preferred_technologies TEXT[],
    template_id UUID,
    stack_name VARCHAR(255) NOT NULL,
    monthly_cost DECIMAL(10,2) NOT NULL,
    setup_cost DECIMAL(10,2) NOT NULL,
    team_size VARCHAR(50) NOT NULL,
    development_time INTEGER NOT NULL,
    satisfaction INTEGER NOT NULL CHECK (satisfaction >= 0 AND satisfaction <= 100),
    success_rate INTEGER NOT NULL CHECK (success_rate >= 0 AND success_rate <= 100),
    frontend VARCHAR(100) NOT NULL,
    backend VARCHAR(100) NOT NULL,
    database VARCHAR(100) NOT NULL,
    cloud VARCHAR(100) NOT NULL,
    testing VARCHAR(100) NOT NULL,
    mobile VARCHAR(100),
    devops VARCHAR(100) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_claude_recommendations_request_id ON claude_recommendations(request_id);
CREATE INDEX IF NOT EXISTS idx_claude_recommendations_domain ON claude_recommendations(domain);
CREATE INDEX IF NOT EXISTS idx_claude_recommendations_created_at ON claude_recommendations(created_at);

-- Ensure proper ownership
ALTER TABLE claude_recommendations OWNER TO pipeline_admin;

-- Log the cleanup
INSERT INTO schema_migrations (service, version, description, applied_at) 
VALUES ('database-cleanup', 'fix_schema_conflicts', 'Fixed schema conflicts and prepared for clean deployment', CURRENT_TIMESTAMP)
ON CONFLICT (service, version) DO NOTHING;