-- SQLite Schema for Sovereign AI Router
-- Version: 1.0
-- Created: 2026-03-12

-- Drop existing tables if they exist
DROP TABLE IF EXISTS requests;
DROP TABLE IF EXISTS workers;
DROP TABLE IF EXISTS metrics;

-- Create requests table for logging API calls
CREATE TABLE requests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    request_id TEXT NOT NULL,
    worker_id INTEGER NOT NULL,
    method TEXT NOT NULL,
    path TEXT NOT NULL,
    status_code INTEGER NOT NULL,
    latency_ms INTEGER NOT NULL,
    user_agent TEXT,
    ip_address TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_request_id UNIQUE (request_id)
);

-- Create workers table for worker process information
CREATE TABLE workers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    worker_id INTEGER NOT NULL UNIQUE,
    pid INTEGER NOT NULL,
    status TEXT NOT NULL CHECK(status IN ('active', 'inactive', 'error')),
    requests_handled INTEGER DEFAULT 0,
    errors_count INTEGER DEFAULT 0,
    cpu_usage REAL,
    memory_usage REAL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Create metrics table for system metrics
CREATE TABLE metrics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    metric_name TEXT NOT NULL,
    metric_value REAL NOT NULL,
    metric_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    tags TEXT, -- JSON string for additional metadata
    CONSTRAINT unique_metric UNIQUE (metric_name, metric_timestamp, tags)
);

-- Create indexes for performance
CREATE INDEX idx_requests_created_at ON requests(created_at);
CREATE INDEX idx_requests_status ON requests(status_code);
CREATE INDEX idx_metrics_timestamp ON metrics(metric_timestamp);
CREATE INDEX idx_metrics_name ON metrics(metric_name);

-- Create a trigger to update worker status
CREATE TRIGGER update_worker_updated_at
AFTER UPDATE ON workers
FOR EACH ROW
BEGIN
    UPDATE workers SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- Insert initial worker data
INSERT INTO workers (worker_id, pid, status) VALUES (1, 0, 'inactive');
INSERT INTO workers (worker_id, pid, status) VALUES (2, 0, 'inactive');
INSERT INTO workers (worker_id, pid, status) VALUES (3, 0, 'inactive');
INSERT INTO workers (worker_id, pid, status) VALUES (4, 0, 'inactive');
INSERT INTO workers (worker_id, pid, status) VALUES (5, 0, 'inactive');
INSERT INTO workers (worker_id, pid, status) VALUES (6, 0, 'inactive');
INSERT INTO workers (worker_id, pid, status) VALUES (7, 0, 'inactive');
INSERT INTO workers (worker_id, pid, status) VALUES (8, 0, 'inactive');

-- Create views for common queries
CREATE VIEW request_stats AS
SELECT 
    DATE(created_at) as date,
    COUNT(*) as total_requests,
    COUNT(CASE WHEN status_code >= 400 THEN 1 END) as error_count,
    AVG(latency_ms) as avg_latency,
    MAX(latency_ms) as max_latency
FROM requests
GROUP BY DATE(created_at)
ORDER BY date DESC;

CREATE VIEW worker_stats AS
SELECT 
    worker_id,
    status,
    requests_handled,
    errors_count,
    ROUND((errors_count * 100.0 / NULLIF(requests_handled, 0)), 2) as error_rate,
    cpu_usage,
    memory_usage,
    updated_at
FROM workers
ORDER BY worker_id;