database_init/02_create_advanced_face_tables.sql

-- Advanced Face Capture TimescaleDB Schema
-- Creates hypertables for real-time face capture metrics and analysis

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

-- Face capture sessions table
CREATE TABLE IF NOT EXISTS face_capture_sessions (
    timestamp TIMESTAMPTZ NOT NULL,
    session_id TEXT NOT NULL,
    user_id TEXT,
    device_info JSONB,
    camera_settings JSONB,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (timestamp, session_id)
);

-- Real-time quality metrics table
CREATE TABLE IF NOT EXISTS face_quality_metrics (
    timestamp TIMESTAMPTZ NOT NULL,
    session_id TEXT NOT NULL,
    frame_number INTEGER,
    sharpness_score REAL,
    illumination_score REAL,
    pose_score REAL,
    eye_distance_score REAL,
    motion_blur_score REAL,
    overall_score REAL,
    confidence REAL,
    processing_time_ms REAL,
    landmarks JSONB,
    pose_angles JSONB,
    is_frontal BOOLEAN,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (timestamp, session_id, frame_number)
);

-- Processed face captures table
CREATE TABLE IF NOT EXISTS processed_face_captures (
    timestamp TIMESTAMPTZ NOT NULL,
    session_id TEXT NOT NULL,
    quality_score REAL,
    pose_angles JSONB,
    is_frontal BOOLEAN,
    face_box JSONB,
    landmark_count INTEGER,
    processing_time_ms REAL,
    morphing_detection_result JSONB,
    liveness_detection_result JSONB,
    identity_verification_result JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (timestamp, session_id)
);

-- WebRTC performance metrics table
CREATE TABLE IF NOT EXISTS webrtc_performance_metrics (
    timestamp TIMESTAMPTZ NOT NULL,
    session_id TEXT NOT NULL,
    fps REAL,
    frame_drops INTEGER,
    latency_ms REAL,
    bandwidth_kbps REAL,
    resolution_width INTEGER,
    resolution_height INTEGER,
    codec_info JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (timestamp, session_id)
);

-- System health metrics table
CREATE TABLE IF NOT EXISTS system_health_metrics (
    timestamp TIMESTAMPTZ NOT NULL,
    component_name TEXT NOT NULL,
    health_score REAL,
    status TEXT,
    metrics JSONB,
    alerts JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (timestamp, component_name)
);

-- Model performance tracking table
CREATE TABLE IF NOT EXISTS model_performance_metrics (
    timestamp TIMESTAMPTZ NOT NULL,
    model_name TEXT NOT NULL,
    model_version TEXT,
    accuracy_score REAL,
    processing_time_ms REAL,
    memory_usage_mb REAL,
    gpu_utilization REAL,
    inference_count INTEGER,
    error_count INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (timestamp, model_name)
);

-- Create hypertables for time-series optimization
SELECT create_hypertable('face_capture_sessions', 'timestamp', if_not_exists => TRUE);
SELECT create_hypertable('face_quality_metrics', 'timestamp', if_not_exists => TRUE);
SELECT create_hypertable('processed_face_captures', 'timestamp', if_not_exists => TRUE);
SELECT create_hypertable('webrtc_performance_metrics', 'timestamp', if_not_exists => TRUE);
SELECT create_hypertable('system_health_metrics', 'timestamp', if_not_exists => TRUE);
SELECT create_hypertable('model_performance_metrics', 'timestamp', if_not_exists => TRUE);

-- Create indexes for optimal query performance
CREATE INDEX IF NOT EXISTS idx_face_quality_session 
    ON face_quality_metrics (session_id, timestamp);

CREATE INDEX IF NOT EXISTS idx_face_quality_overall_score 
    ON face_quality_metrics (overall_score, timestamp);

CREATE INDEX IF NOT EXISTS idx_face_quality_frontal 
    ON face_quality_metrics (is_frontal, timestamp);

CREATE INDEX IF NOT EXISTS idx_processed_captures_session 
    ON processed_face_captures (session_id, timestamp);

CREATE INDEX IF NOT EXISTS idx_processed_captures_quality 
    ON processed_face_captures (quality_score, timestamp);

CREATE INDEX IF NOT EXISTS idx_webrtc_performance_session 
    ON webrtc_performance_metrics (session_id, timestamp);

CREATE INDEX IF NOT EXISTS idx_system_health_component 
    ON system_health_metrics (component_name, timestamp);

CREATE INDEX IF NOT EXISTS idx_model_performance_name 
    ON model_performance_metrics (model_name, timestamp);

-- Create continuous aggregates for real-time analytics
CREATE MATERIALIZED VIEW IF NOT EXISTS face_quality_hourly
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 hour', timestamp) AS bucket,
    session_id,
    AVG(overall_score) AS avg_quality,
    MAX(overall_score) AS max_quality,
    MIN(overall_score) AS min_quality,
    COUNT(*) AS frame_count,
    COUNT(CASE WHEN is_frontal THEN 1 END) AS frontal_frames,
    AVG(processing_time_ms) AS avg_processing_time
FROM face_quality_metrics
GROUP BY bucket, session_id;

CREATE MATERIALIZED VIEW IF NOT EXISTS system_performance_hourly
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 hour', timestamp) AS bucket,
    AVG(fps) AS avg_fps,
    SUM(frame_drops) AS total_frame_drops,
    AVG(latency_ms) AS avg_latency,
    AVG(bandwidth_kbps) AS avg_bandwidth
FROM webrtc_performance_metrics
GROUP BY bucket;

CREATE MATERIALIZED VIEW IF NOT EXISTS model_performance_daily
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 day', timestamp) AS bucket,
    model_name,
    AVG(accuracy_score) AS avg_accuracy,
    AVG(processing_time_ms) AS avg_processing_time,
    SUM(inference_count) AS total_inferences,
    SUM(error_count) AS total_errors
FROM model_performance_metrics
GROUP BY bucket, model_name;

-- Set up data retention policies (keep detailed data for 30 days, aggregated for 1 year)
SELECT add_retention_policy('face_quality_metrics', INTERVAL '30 days', if_not_exists => TRUE);
SELECT add_retention_policy('webrtc_performance_metrics', INTERVAL '30 days', if_not_exists => TRUE);
SELECT add_retention_policy('system_health_metrics', INTERVAL '90 days', if_not_exists => TRUE);
SELECT add_retention_policy('model_performance_metrics', INTERVAL '90 days', if_not_exists => TRUE);

-- Set up compression policies for older data
SELECT add_compression_policy('face_quality_metrics', INTERVAL '7 days', if_not_exists => TRUE);
SELECT add_compression_policy('processed_face_captures', INTERVAL '7 days', if_not_exists => TRUE);
SELECT add_compression_policy('webrtc_performance_metrics', INTERVAL '7 days', if_not_exists => TRUE);

-- Create refresh policies for continuous aggregates
SELECT add_continuous_aggregate_policy('face_quality_hourly',
    start_offset => INTERVAL '1 day',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour',
    if_not_exists => TRUE);

SELECT add_continuous_aggregate_policy('system_performance_hourly',
    start_offset => INTERVAL '1 day',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour',
    if_not_exists => TRUE);

SELECT add_continuous_aggregate_policy('model_performance_daily',
    start_offset => INTERVAL '7 days',
    end_offset => INTERVAL '1 day',
    schedule_interval => INTERVAL '1 day',
    if_not_exists => TRUE);

-- Create views for common queries
CREATE OR REPLACE VIEW recent_face_sessions AS
SELECT 
    session_id,
    MIN(timestamp) AS start_time,
    MAX(timestamp) AS end_time,
    COUNT(*) AS total_frames,
    AVG(overall_score) AS avg_quality,
    COUNT(CASE WHEN is_frontal THEN 1 END) AS frontal_frames
FROM face_quality_metrics
WHERE timestamp > NOW() - INTERVAL '1 hour'
GROUP BY session_id
ORDER BY start_time DESC;

CREATE OR REPLACE VIEW system_health_dashboard AS
SELECT 
    component_name,
    timestamp,
    health_score,
    status,
    metrics
FROM system_health_metrics
WHERE timestamp > NOW() - INTERVAL '5 minutes'
ORDER BY timestamp DESC, component_name;

CREATE OR REPLACE VIEW real_time_quality_stats AS
SELECT 
    COUNT(*) AS total_frames_last_minute,
    AVG(overall_score) AS avg_quality_last_minute,
    COUNT(CASE WHEN is_frontal THEN 1 END) AS frontal_frames_last_minute,
    AVG(processing_time_ms) AS avg_processing_time_last_minute
FROM face_quality_metrics
WHERE timestamp > NOW() - INTERVAL '1 minute';

-- Grant permissions to morphguard user
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO morphguard;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO morphguard;

-- Insert system health check
INSERT INTO system_health_metrics (timestamp, component_name, health_score, status, metrics)
VALUES (NOW(), 'database_schema', 1.0, 'healthy', '{"tables_created": true, "hypertables_created": true, "indexes_created": true}');

COMMENT ON TABLE face_capture_sessions IS 'Stores WebRTC face capture session metadata';
COMMENT ON TABLE face_quality_metrics IS 'Real-time face quality assessment metrics for each frame';
COMMENT ON TABLE processed_face_captures IS 'Final processed face capture results with AI analysis';
COMMENT ON TABLE webrtc_performance_metrics IS 'WebRTC streaming performance metrics';
COMMENT ON TABLE system_health_metrics IS 'System component health monitoring';
COMMENT ON TABLE model_performance_metrics IS 'AI model performance tracking and monitoring';