-- Planner MCP Database Initialization Script
-- This script sets up the initial database structure, users, and permissions

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";

-- Create application database user (if not exists)
DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_catalog.pg_user WHERE usename = 'planner_mcp_app') THEN
        CREATE USER planner_mcp_app WITH PASSWORD 'CHANGE_ME_IN_PRODUCTION';
    END IF;
END
$$;

-- Grant necessary permissions
GRANT CONNECT ON DATABASE planner_mcp_development TO planner_mcp_app;
GRANT CONNECT ON DATABASE planner_mcp_staging TO planner_mcp_app;
GRANT CONNECT ON DATABASE planner_mcp TO planner_mcp_app;

-- Schema creation
CREATE SCHEMA IF NOT EXISTS planner_mcp;
GRANT USAGE ON SCHEMA planner_mcp TO planner_mcp_app;
GRANT CREATE ON SCHEMA planner_mcp TO planner_mcp_app;

-- Set default schema
ALTER USER planner_mcp_app SET search_path = planner_mcp, public;

-- Create audit schema for compliance
CREATE SCHEMA IF NOT EXISTS audit;
GRANT USAGE ON SCHEMA audit TO planner_mcp_app;
GRANT CREATE ON SCHEMA audit TO planner_mcp_app;

-- Logging and monitoring setup
CREATE SCHEMA IF NOT EXISTS monitoring;
GRANT USAGE ON SCHEMA monitoring TO planner_mcp_app;
GRANT CREATE ON SCHEMA monitoring TO planner_mcp_app;

-- Create database health check function
CREATE OR REPLACE FUNCTION public.health_check()
RETURNS json
LANGUAGE plpgsql
AS $$
DECLARE
    result json;
BEGIN
    SELECT json_build_object(
        'status', 'healthy',
        'timestamp', extract(epoch from now()),
        'database', current_database(),
        'version', version(),
        'connections', (
            SELECT count(*) FROM pg_stat_activity 
            WHERE state = 'active'
        )
    ) INTO result;
    
    RETURN result;
END;
$$;

-- Grant execute permission on health check function
GRANT EXECUTE ON FUNCTION public.health_check() TO planner_mcp_app;

-- Create performance monitoring function
CREATE OR REPLACE FUNCTION monitoring.get_performance_stats()
RETURNS json
LANGUAGE plpgsql
AS $$
DECLARE
    result json;
BEGIN
    SELECT json_build_object(
        'active_connections', (
            SELECT count(*) FROM pg_stat_activity WHERE state = 'active'
        ),
        'database_size', pg_size_pretty(pg_database_size(current_database())),
        'cache_hit_ratio', (
            SELECT round(
                sum(blks_hit) * 100.0 / sum(blks_hit + blks_read), 2
            )
            FROM pg_stat_database 
            WHERE datname = current_database()
        ),
        'slow_queries', (
            SELECT count(*) FROM pg_stat_statements 
            WHERE mean_time > 1000
        )
    ) INTO result;
    
    RETURN result;
END;
$$;

-- Grant execute permission on performance stats function
GRANT EXECUTE ON FUNCTION monitoring.get_performance_stats() TO planner_mcp_app;

-- Create audit trigger function for change tracking
CREATE OR REPLACE FUNCTION audit.audit_trigger_func()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    audit_row audit.audit_log%ROWTYPE;
    include_values boolean = false;
    log_diffs boolean = false;
    h_old jsonb;
    h_new jsonb;
    excluded_cols text[] = ARRAY[]::text[];
BEGIN
    -- Check if auditing is enabled for this table
    IF TG_ARGV[0]::boolean THEN
        include_values = true;
    END IF;
    
    IF TG_ARGV[1]::boolean THEN
        log_diffs = true;
    END IF;
    
    IF TG_NARGS > 2 THEN
        excluded_cols = TG_ARGV[2]::text[];
    END IF;
    
    audit_row = ROW(
        nextval('audit.audit_log_id_seq'),  -- id
        TG_TABLE_SCHEMA::text,              -- schema_name
        TG_TABLE_NAME::text,                -- table_name
        TG_RELID,                           -- relid
        session_user::text,                 -- session_user_name
        current_timestamp,                  -- action_tstamp
        statement_timestamp(),              -- stmt_tstamp
        clock_timestamp(),                  -- clock_tstamp
        txid_current(),                     -- transaction_id
        current_setting('application_name'), -- application_name
        inet_client_addr(),                 -- client_addr
        inet_client_port(),                 -- client_port
        current_query(),                    -- client_query
        TG_OP::audit.audit_operation,       -- action
        NULL, NULL                          -- row_data, changed_fields
    );
    
    IF NOT TG_ARGV[0]::boolean IS DISTINCT FROM 'f'::boolean THEN
        audit_row.client_query = NULL;
    END IF;
    
    IF TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW' THEN
        audit_row.row_data = hstore(OLD.*);
        audit_row.changed_fields = (hstore(NEW.*) - audit_row.row_data) - excluded_cols;
        
        IF audit_row.changed_fields = hstore('') THEN
            -- All changed fields are ignored. Skip this update.
            RETURN NULL;
        END IF;
    ELSIF TG_OP = 'DELETE' AND TG_LEVEL = 'ROW' THEN
        audit_row.row_data = hstore(OLD.*);
    ELSIF TG_OP = 'INSERT' AND TG_LEVEL = 'ROW' THEN
        audit_row.row_data = hstore(NEW.*);
    END IF;
    
    -- Insert audit record
    INSERT INTO audit.audit_log VALUES (audit_row.*);
    
    RETURN NULL;
END;
$$;

-- Create audit log table
CREATE TABLE IF NOT EXISTS audit.audit_log (
    id bigserial PRIMARY KEY,
    schema_name text NOT NULL,
    table_name text NOT NULL,
    relid oid NOT NULL,
    session_user_name text,
    action_tstamp timestamp with time zone NOT NULL DEFAULT now(),
    stmt_tstamp timestamp with time zone NOT NULL DEFAULT statement_timestamp(),
    clock_tstamp timestamp with time zone NOT NULL DEFAULT clock_timestamp(),
    transaction_id bigint,
    application_name text,
    client_addr inet,
    client_port integer,
    client_query text,
    action audit.audit_operation NOT NULL,
    row_data hstore,
    changed_fields hstore
);

-- Create enum for audit operations
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'audit_operation') THEN
        CREATE TYPE audit.audit_operation AS ENUM (
            'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE'
        );
    END IF;
END
$$;

-- Create indexes for audit log
CREATE INDEX IF NOT EXISTS audit_log_relid_idx ON audit.audit_log(relid);
CREATE INDEX IF NOT EXISTS audit_log_action_tstamp_idx ON audit.audit_log(action_tstamp);
CREATE INDEX IF NOT EXISTS audit_log_action_idx ON audit.audit_log(action);

-- Create performance monitoring table
CREATE TABLE IF NOT EXISTS monitoring.performance_metrics (
    id bigserial PRIMARY KEY,
    timestamp timestamp with time zone NOT NULL DEFAULT now(),
    metric_name text NOT NULL,
    metric_value numeric,
    tags jsonb,
    UNIQUE(timestamp, metric_name, tags)
);

CREATE INDEX IF NOT EXISTS performance_metrics_timestamp_idx ON monitoring.performance_metrics(timestamp);
CREATE INDEX IF NOT EXISTS performance_metrics_name_idx ON monitoring.performance_metrics(metric_name);

-- Create configuration table for runtime settings
CREATE TABLE IF NOT EXISTS planner_mcp.system_config (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    key text NOT NULL UNIQUE,
    value jsonb NOT NULL,
    description text,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    created_by text NOT NULL DEFAULT session_user,
    updated_by text NOT NULL DEFAULT session_user
);

-- Insert default system configuration
INSERT INTO planner_mcp.system_config (key, value, description) VALUES
    ('max_concurrent_plans', '100', 'Maximum number of concurrent plan generations'),
    ('default_plan_timeout', '300000', 'Default plan generation timeout in milliseconds'),
    ('quality_threshold_minimum', '60', 'Minimum quality score for plan acceptance'),
    ('quality_threshold_world_class', '85', 'Quality score threshold for world-class certification'),
    ('auto_enhancement_enabled', 'true', 'Enable automatic plan enhancement'),
    ('max_enhancement_iterations', '5', 'Maximum number of enhancement iterations'),
    ('circuit_breaker_threshold', '10', 'Circuit breaker failure threshold'),
    ('circuit_breaker_timeout', '60000', 'Circuit breaker timeout in milliseconds')
ON CONFLICT (key) DO NOTHING;

-- Create migration tracking table
CREATE TABLE IF NOT EXISTS planner_mcp.schema_migrations (
    version text PRIMARY KEY,
    applied_at timestamp with time zone NOT NULL DEFAULT now(),
    applied_by text NOT NULL DEFAULT session_user,
    description text
);

-- Create session management table
CREATE TABLE IF NOT EXISTS planner_mcp.user_sessions (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id uuid NOT NULL,
    session_token text NOT NULL UNIQUE,
    refresh_token text,
    expires_at timestamp with time zone NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    client_ip inet,
    user_agent text,
    active boolean NOT NULL DEFAULT true
);

CREATE INDEX IF NOT EXISTS user_sessions_user_id_idx ON planner_mcp.user_sessions(user_id);
CREATE INDEX IF NOT EXISTS user_sessions_expires_at_idx ON planner_mcp.user_sessions(expires_at);
CREATE INDEX IF NOT EXISTS user_sessions_active_idx ON planner_mcp.user_sessions(active) WHERE active = true;

-- Create plan cache table
CREATE TABLE IF NOT EXISTS planner_mcp.plan_cache (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    cache_key text NOT NULL UNIQUE,
    plan_data jsonb NOT NULL,
    quality_score integer,
    expires_at timestamp with time zone NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    access_count integer NOT NULL DEFAULT 0,
    last_accessed_at timestamp with time zone NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS plan_cache_expires_at_idx ON planner_mcp.plan_cache(expires_at);
CREATE INDEX IF NOT EXISTS plan_cache_quality_score_idx ON planner_mcp.plan_cache(quality_score);

-- Create automatic cleanup function for expired records
CREATE OR REPLACE FUNCTION planner_mcp.cleanup_expired_records()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    -- Clean up expired sessions
    DELETE FROM planner_mcp.user_sessions 
    WHERE expires_at < now() OR active = false;
    
    -- Clean up expired cache entries
    DELETE FROM planner_mcp.plan_cache 
    WHERE expires_at < now();
    
    -- Clean up old audit logs (keep 2 years)
    DELETE FROM audit.audit_log 
    WHERE action_tstamp < now() - interval '2 years';
    
    -- Clean up old performance metrics (keep 30 days)
    DELETE FROM monitoring.performance_metrics 
    WHERE timestamp < now() - interval '30 days';
    
    -- Log cleanup completion
    INSERT INTO monitoring.performance_metrics (metric_name, metric_value, tags)
    VALUES ('cleanup_completed', 1, '{"type": "maintenance"}'::jsonb);
END;
$$;

-- Create maintenance notification function
CREATE OR REPLACE FUNCTION planner_mcp.notify_maintenance_required()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    database_size bigint;
    connection_count integer;
    slow_query_count integer;
BEGIN
    -- Check database size
    SELECT pg_database_size(current_database()) INTO database_size;
    
    -- Check connection count
    SELECT count(*) FROM pg_stat_activity INTO connection_count;
    
    -- Check slow query count
    SELECT count(*) FROM pg_stat_statements 
    WHERE mean_time > 1000 INTO slow_query_count;
    
    -- Send notifications if thresholds are exceeded
    IF database_size > 10737418240 THEN -- 10GB
        PERFORM pg_notify('maintenance_required', 
            json_build_object('type', 'database_size', 'value', database_size)::text);
    END IF;
    
    IF connection_count > 80 THEN
        PERFORM pg_notify('maintenance_required', 
            json_build_object('type', 'high_connections', 'value', connection_count)::text);
    END IF;
    
    IF slow_query_count > 50 THEN
        PERFORM pg_notify('maintenance_required', 
            json_build_object('type', 'slow_queries', 'value', slow_query_count)::text);
    END IF;
END;
$$;

-- Grant necessary permissions to application user
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA planner_mcp TO planner_mcp_app;
GRANT SELECT ON ALL TABLES IN SCHEMA audit TO planner_mcp_app;
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA monitoring TO planner_mcp_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA planner_mcp TO planner_mcp_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA audit TO planner_mcp_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA monitoring TO planner_mcp_app;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA planner_mcp TO planner_mcp_app;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA monitoring TO planner_mcp_app;

-- Set up default privileges for future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA planner_mcp GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO planner_mcp_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA planner_mcp GRANT USAGE, SELECT ON SEQUENCES TO planner_mcp_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA planner_mcp GRANT EXECUTE ON FUNCTIONS TO planner_mcp_app;

-- Create initial migration record
INSERT INTO planner_mcp.schema_migrations (version, description) VALUES
    ('001_initial_setup', 'Initial database schema and configuration')
ON CONFLICT (version) DO NOTHING;

-- Log successful initialization
INSERT INTO monitoring.performance_metrics (metric_name, metric_value, tags)
VALUES ('database_initialized', 1, '{"version": "001", "timestamp": "'||now()||'"}'::jsonb);

-- Database initialization complete
SELECT 'Database initialization completed successfully' AS status;