database_init/03_create_enhancement_tables.sql

-- Enhancement Features Database Schema
-- Creates tables for XAI, A/B testing, model compression, and training pipeline integration

-- Create extension if not exists
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- XAI Analysis Results Table
CREATE TABLE IF NOT EXISTS xai_analysis (
    timestamp TIMESTAMPTZ NOT NULL,
    analysis_id TEXT NOT NULL,
    session_id TEXT NOT NULL,
    user_id TEXT,
    method_results JSONB,
    confidence_calibration JSONB,
    interpretation_score REAL,
    processing_time_ms REAL,
    model_version TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (timestamp, analysis_id)
);

-- Create hypertable for XAI analysis
SELECT create_hypertable('xai_analysis', 'timestamp', if_not_exists => TRUE);

-- A/B Test Results Table (for real-time monitoring)
CREATE TABLE IF NOT EXISTS ab_test_results (
    timestamp TIMESTAMPTZ NOT NULL,
    result_id TEXT NOT NULL,
    experiment_id TEXT NOT NULL,
    variant_id TEXT NOT NULL,
    user_id TEXT,
    session_id TEXT,
    metrics JSONB,
    processing_time_ms REAL,
    device_info JSONB,
    ip_address INET,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (timestamp, result_id)
);

-- Create hypertable for A/B test results
SELECT create_hypertable('ab_test_results', 'timestamp', if_not_exists => TRUE);

-- Model Compression Metrics Table
CREATE TABLE IF NOT EXISTS model_compression_metrics (
    timestamp TIMESTAMPTZ NOT NULL,
    compression_id TEXT NOT NULL,
    model_name TEXT,
    original_size_mb REAL,
    compressed_size_mb REAL,
    compression_ratio REAL,
    speedup_ratio REAL,
    accuracy_drop REAL,
    compression_config JSONB,
    processing_time_ms REAL,
    target_platform TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (timestamp, compression_id)
);

-- Create hypertable for compression metrics
SELECT create_hypertable('model_compression_metrics', 'timestamp', if_not_exists => TRUE);

-- Automated Training Pipeline Results
CREATE TABLE IF NOT EXISTS training_pipeline_results (
    timestamp TIMESTAMPTZ NOT NULL,
    pipeline_id TEXT NOT NULL,
    dataset_size INTEGER,
    training_config JSONB,
    hyperparameter_results JSONB,
    active_learning_results JSONB,
    final_metrics JSONB,
    training_time_hours REAL,
    data_efficiency_gain REAL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (timestamp, pipeline_id)
);

-- Create hypertable for training results
SELECT create_hypertable('training_pipeline_results', 'timestamp', if_not_exists => TRUE);

-- Dataset Curation Results
CREATE TABLE IF NOT EXISTS dataset_curation_results (
    timestamp TIMESTAMPTZ NOT NULL,
    curation_id TEXT NOT NULL,
    original_size INTEGER,
    final_size INTEGER,
    quality_threshold REAL,
    average_quality REAL,
    duplicates_removed INTEGER,
    low_quality_removed INTEGER,
    processing_time_ms REAL,
    curation_config JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (timestamp, curation_id)
);

-- Create hypertable for curation results
SELECT create_hypertable('dataset_curation_results', 'timestamp', if_not_exists => TRUE);

-- User Enhancement Preferences
CREATE TABLE IF NOT EXISTS user_enhancement_preferences (
    user_id TEXT PRIMARY KEY,
    xai_enabled BOOLEAN DEFAULT true,
    preferred_xai_methods TEXT[] DEFAULT ARRAY['integrated_gradients', 'shap'],
    ab_testing_participation BOOLEAN DEFAULT true,
    compression_preference TEXT DEFAULT 'balanced', -- fast, balanced, accurate
    confidence_calibration_enabled BOOLEAN DEFAULT true,
    interpretability_level TEXT DEFAULT 'medium', -- low, medium, high
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enhancement Session Tracking
CREATE TABLE IF NOT EXISTS enhancement_sessions (
    timestamp TIMESTAMPTZ NOT NULL,
    session_id TEXT NOT NULL,
    user_id TEXT,
    enhancement_type TEXT, -- xai, compression, ab_test, training
    configuration JSONB,
    results JSONB,
    processing_time_ms REAL,
    status TEXT, -- started, completed, failed
    error_message TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (timestamp, session_id, enhancement_type)
);

-- Create hypertable for enhancement sessions
SELECT create_hypertable('enhancement_sessions', 'timestamp', if_not_exists => TRUE);

-- Model Performance Tracking (Enhanced)
CREATE TABLE IF NOT EXISTS enhanced_model_performance (
    timestamp TIMESTAMPTZ NOT NULL,
    model_id TEXT NOT NULL,
    model_version TEXT,
    accuracy REAL,
    precision_score REAL,
    recall REAL,
    f1_score REAL,
    inference_time_ms REAL,
    memory_usage_mb REAL,
    interpretability_score REAL,
    confidence_calibration_score REAL,
    compression_ratio REAL,
    deployment_target TEXT, -- mobile, server, edge
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (timestamp, model_id)
);

-- Create hypertable for enhanced performance tracking
SELECT create_hypertable('enhanced_model_performance', 'timestamp', if_not_exists => TRUE);

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_xai_analysis_session ON xai_analysis (session_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_xai_analysis_user ON xai_analysis (user_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_ab_test_experiment ON ab_test_results (experiment_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_ab_test_variant ON ab_test_results (variant_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_compression_model ON model_compression_metrics (model_name, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_training_pipeline ON training_pipeline_results (timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_enhancement_sessions_user ON enhancement_sessions (user_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_enhancement_sessions_type ON enhancement_sessions (enhancement_type, timestamp DESC);

-- Create materialized views for analytics
CREATE MATERIALIZED VIEW IF NOT EXISTS xai_daily_stats AS
SELECT 
    DATE(timestamp) as date,
    COUNT(*) as total_analyses,
    COUNT(DISTINCT session_id) as unique_sessions,
    AVG(interpretation_score) as avg_interpretation_score,
    AVG(processing_time_ms) as avg_processing_time,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY interpretation_score) as median_interpretation_score
FROM xai_analysis
WHERE timestamp >= NOW() - INTERVAL '90 days'
GROUP BY DATE(timestamp)
ORDER BY date DESC;

CREATE MATERIALIZED VIEW IF NOT EXISTS ab_test_daily_stats AS
SELECT 
    DATE(timestamp) as date,
    experiment_id,
    variant_id,
    COUNT(*) as test_count,
    AVG((metrics->>'accuracy')::float) as avg_accuracy,
    AVG((metrics->>'latency')::float) as avg_latency,
    STDDEV((metrics->>'accuracy')::float) as accuracy_stddev
FROM ab_test_results
WHERE timestamp >= NOW() - INTERVAL '90 days'
GROUP BY DATE(timestamp), experiment_id, variant_id
ORDER BY date DESC, experiment_id, variant_id;

CREATE MATERIALIZED VIEW IF NOT EXISTS compression_efficiency_stats AS
SELECT 
    DATE(timestamp) as date,
    target_platform,
    COUNT(*) as compression_count,
    AVG(compression_ratio) as avg_compression_ratio,
    AVG(speedup_ratio) as avg_speedup_ratio,
    AVG(accuracy_drop) as avg_accuracy_drop,
    AVG(processing_time_ms) as avg_processing_time
FROM model_compression_metrics
WHERE timestamp >= NOW() - INTERVAL '90 days'
GROUP BY DATE(timestamp), target_platform
ORDER BY date DESC, target_platform;

-- Add retention policies for data lifecycle management
SELECT add_retention_policy('xai_analysis', INTERVAL '1 year');
SELECT add_retention_policy('ab_test_results', INTERVAL '1 year');
SELECT add_retention_policy('model_compression_metrics', INTERVAL '2 years');
SELECT add_retention_policy('training_pipeline_results', INTERVAL '2 years');
SELECT add_retention_policy('dataset_curation_results', INTERVAL '1 year');
SELECT add_retention_policy('enhancement_sessions', INTERVAL '6 months');

-- Add continuous aggregate policies for materialized views
SELECT add_continuous_aggregate_policy('xai_daily_stats',
    start_offset => INTERVAL '1 month',
    end_offset => INTERVAL '1 day',
    schedule_interval => INTERVAL '1 hour');

SELECT add_continuous_aggregate_policy('ab_test_daily_stats',
    start_offset => INTERVAL '1 month',
    end_offset => INTERVAL '1 day',
    schedule_interval => INTERVAL '1 hour');

SELECT add_continuous_aggregate_policy('compression_efficiency_stats',
    start_offset => INTERVAL '1 month',
    end_offset => INTERVAL '1 day',
    schedule_interval => INTERVAL '1 hour');

-- Create views for easy data access
CREATE VIEW enhancement_user_summary AS
SELECT 
    u.user_id,
    u.xai_enabled,
    u.ab_testing_participation,
    u.compression_preference,
    COUNT(DISTINCT xa.analysis_id) as total_xai_analyses,
    COUNT(DISTINCT ab.result_id) as total_ab_tests,
    AVG(xa.interpretation_score) as avg_interpretation_score,
    MAX(xa.timestamp) as last_xai_analysis,
    MAX(ab.timestamp) as last_ab_test
FROM user_enhancement_preferences u
LEFT JOIN xai_analysis xa ON u.user_id = xa.user_id 
    AND xa.timestamp >= NOW() - INTERVAL '30 days'
LEFT JOIN ab_test_results ab ON u.user_id = ab.user_id 
    AND ab.timestamp >= NOW() - INTERVAL '30 days'
GROUP BY u.user_id, u.xai_enabled, u.ab_testing_participation, u.compression_preference;

CREATE VIEW enhancement_performance_overview AS
SELECT 
    'XAI Analysis' as enhancement_type,
    COUNT(*) as total_operations,
    AVG(processing_time_ms) as avg_processing_time,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY processing_time_ms) as p95_processing_time
FROM xai_analysis
WHERE timestamp >= NOW() - INTERVAL '24 hours'

UNION ALL

SELECT 
    'Model Compression' as enhancement_type,
    COUNT(*) as total_operations,
    AVG(processing_time_ms) as avg_processing_time,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY processing_time_ms) as p95_processing_time
FROM model_compression_metrics
WHERE timestamp >= NOW() - INTERVAL '24 hours'

UNION ALL

SELECT 
    'A/B Testing' as enhancement_type,
    COUNT(*) as total_operations,
    AVG(processing_time_ms) as avg_processing_time,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY processing_time_ms) as p95_processing_time
FROM ab_test_results
WHERE timestamp >= NOW() - INTERVAL '24 hours';

-- Grant permissions (adjust user as needed)
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO morphguard_app;
GRANT SELECT ON ALL VIEWS IN SCHEMA public TO morphguard_app;

-- Insert sample user preferences
INSERT INTO user_enhancement_preferences (user_id, xai_enabled, preferred_xai_methods, compression_preference)
VALUES 
    ('admin', true, ARRAY['integrated_gradients', 'shap', 'lime'], 'accurate'),
    ('demo_user', true, ARRAY['integrated_gradients'], 'balanced')
ON CONFLICT (user_id) DO NOTHING;

-- Add comments for documentation
COMMENT ON TABLE xai_analysis IS 'Stores XAI analysis results with interpretability scores and explanations';
COMMENT ON TABLE ab_test_results IS 'Real-time A/B test results for performance monitoring';
COMMENT ON TABLE model_compression_metrics IS 'Model compression performance and efficiency metrics';
COMMENT ON TABLE training_pipeline_results IS 'Automated training pipeline results and configurations';
COMMENT ON TABLE user_enhancement_preferences IS 'User preferences for enhancement features';
COMMENT ON TABLE enhancement_sessions IS 'Session tracking for all enhancement operations';

-- Create functions for common queries
CREATE OR REPLACE FUNCTION get_user_enhancement_stats(p_user_id TEXT, p_days INTEGER DEFAULT 30)
RETURNS JSON AS $$
DECLARE
    result JSON;
BEGIN
    SELECT json_build_object(
        'user_id', p_user_id,
        'period_days', p_days,
        'xai_analyses', COALESCE(xai_count, 0),
        'ab_tests', COALESCE(ab_count, 0),
        'avg_interpretation_score', COALESCE(avg_score, 0),
        'last_activity', COALESCE(last_activity, '1970-01-01'::timestamp)
    ) INTO result
    FROM (
        SELECT 
            COUNT(DISTINCT xa.analysis_id) as xai_count,
            COUNT(DISTINCT ab.result_id) as ab_count,
            AVG(xa.interpretation_score) as avg_score,
            GREATEST(MAX(xa.timestamp), MAX(ab.timestamp)) as last_activity
        FROM user_enhancement_preferences u
        LEFT JOIN xai_analysis xa ON u.user_id = xa.user_id 
            AND xa.timestamp >= NOW() - (p_days || ' days')::INTERVAL
        LEFT JOIN ab_test_results ab ON u.user_id = ab.user_id 
            AND ab.timestamp >= NOW() - (p_days || ' days')::INTERVAL
        WHERE u.user_id = p_user_id
    ) stats;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_user_enhancement_stats IS 'Get comprehensive enhancement statistics for a user';

-- Trigger to automatically refresh materialized views
CREATE OR REPLACE FUNCTION refresh_enhancement_views()
RETURNS TRIGGER AS $$
BEGIN
    -- Only refresh during business hours to avoid performance impact
    IF EXTRACT(hour FROM NOW()) BETWEEN 9 AND 17 THEN
        REFRESH MATERIALIZED VIEW CONCURRENTLY xai_daily_stats;
        REFRESH MATERIALIZED VIEW CONCURRENTLY ab_test_daily_stats;
        REFRESH MATERIALIZED VIEW CONCURRENTLY compression_efficiency_stats;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Create triggers (commented out for manual control)
-- CREATE TRIGGER refresh_views_on_xai_insert
--     AFTER INSERT ON xai_analysis
--     FOR EACH STATEMENT
--     EXECUTE FUNCTION refresh_enhancement_views();

COMMENT ON FUNCTION refresh_enhancement_views IS 'Automatically refresh materialized views for analytics';

-- Create policy for row-level security (if needed)
-- ALTER TABLE user_enhancement_preferences ENABLE ROW LEVEL SECURITY;
-- CREATE POLICY user_own_preferences ON user_enhancement_preferences
--     FOR ALL TO morphguard_app
--     USING (user_id = current_setting('app.current_user', true));

-- Final success message
DO $$
BEGIN
    RAISE NOTICE 'Enhancement database schema created successfully!';
    RAISE NOTICE 'Tables created: xai_analysis, ab_test_results, model_compression_metrics, training_pipeline_results, dataset_curation_results, user_enhancement_preferences, enhancement_sessions, enhanced_model_performance';
    RAISE NOTICE 'Materialized views created: xai_daily_stats, ab_test_daily_stats, compression_efficiency_stats';
    RAISE NOTICE 'Functions created: get_user_enhancement_stats, refresh_enhancement_views';
END $$;