wevads-arsenal/database-schema.sql

-- ============================================
-- WEVAL MIND DATABASE SCHEMA
-- Version: 1.0 - Février 2026
-- ============================================

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================
-- HAMID IA TABLES
-- ============================================

-- Providers configuration
CREATE TABLE hamid_providers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    api_url VARCHAR(255),
    api_key_encrypted TEXT,
    model VARCHAR(100),
    priority INT DEFAULT 99,
    timeout_ms INT DEFAULT 10000,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'testing', 'failed')),
    avg_latency_ms FLOAT DEFAULT 0,
    success_rate FLOAT DEFAULT 100,
    total_requests INT DEFAULT 0,
    last_used TIMESTAMP,
    config JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Request history
CREATE TABLE hamid_requests (
    id SERIAL PRIMARY KEY,
    request_uuid UUID DEFAULT uuid_generate_v4(),
    provider_id INT REFERENCES hamid_providers(id),
    prompt TEXT,
    response TEXT,
    tokens_used INT,
    latency_ms INT,
    success BOOLEAN DEFAULT true,
    error_message TEXT,
    context JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW()
);

-- Knowledge Base
CREATE TABLE knowledge_base (
    id SERIAL PRIMARY KEY,
    category VARCHAR(100),
    title VARCHAR(255),
    content TEXT,
    source VARCHAR(100),
    tags TEXT[] DEFAULT '{}',
    embeddings_vector VECTOR(1536),
    access_count INT DEFAULT 0,
    last_accessed TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- KB Categories
CREATE TABLE kb_categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE,
    description TEXT,
    parent_id INT REFERENCES kb_categories(id),
    item_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- WEVAL MIND TABLES
-- ============================================

-- Autonomous cycles
CREATE TABLE mind_cycles (
    id SERIAL PRIMARY KEY,
    cycle_uuid UUID DEFAULT uuid_generate_v4(),
    trigger_type VARCHAR(20) DEFAULT 'cron' CHECK (trigger_type IN ('cron', 'manual', 'emergency', 'alert')),
    started_at TIMESTAMP DEFAULT NOW(),
    ended_at TIMESTAMP,
    status VARCHAR(20) DEFAULT 'running' CHECK (status IN ('running', 'completed', 'failed', 'interrupted')),
    health_score INT CHECK (health_score >= 0 AND health_score <= 100),
    issues_found INT DEFAULT 0,
    actions_taken INT DEFAULT 0,
    modules_run TEXT[] DEFAULT '{}',
    metrics_snapshot JSONB DEFAULT '{}',
    result_json JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Diagnostics
CREATE TABLE mind_diagnostics (
    id SERIAL PRIMARY KEY,
    cycle_id INT REFERENCES mind_cycles(id),
    severity VARCHAR(20) CHECK (severity IN ('CRITICAL', 'WARNING', 'INFO')),
    module VARCHAR(50),
    message TEXT,
    metrics_snapshot JSONB DEFAULT '{}',
    suggested_action VARCHAR(100),
    resolved BOOLEAN DEFAULT false,
    resolved_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Actions
CREATE TABLE mind_actions (
    id SERIAL PRIMARY KEY,
    cycle_id INT REFERENCES mind_cycles(id),
    diagnostic_id INT REFERENCES mind_diagnostics(id),
    action_type VARCHAR(50),
    target VARCHAR(255),
    params JSONB DEFAULT '{}',
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'executing', 'completed', 'failed', 'cancelled')),
    output TEXT,
    approved_by VARCHAR(50),
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Learnings
CREATE TABLE mind_learnings (
    id SERIAL PRIMARY KEY,
    diagnostic_type VARCHAR(100),
    action_taken VARCHAR(100),
    effectiveness_score FLOAT CHECK (effectiveness_score >= 0 AND effectiveness_score <= 1),
    before_metrics JSONB,
    after_metrics JSONB,
    lesson TEXT,
    applied_count INT DEFAULT 0,
    last_applied TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- ALERTS SYSTEM
-- ============================================

-- Alert configuration
CREATE TABLE alerts_config (
    id SERIAL PRIMARY KEY,
    channel VARCHAR(20) CHECK (channel IN ('telegram', 'email', 'dashboard', 'webhook', 'sms')),
    endpoint VARCHAR(255),
    min_severity VARCHAR(20) DEFAULT 'WARNING' CHECK (min_severity IN ('CRITICAL', 'WARNING', 'INFO')),
    active BOOLEAN DEFAULT true,
    config JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Alert history
CREATE TABLE alerts_log (
    id SERIAL PRIMARY KEY,
    severity VARCHAR(20) CHECK (severity IN ('CRITICAL', 'WARNING', 'INFO')),
    module VARCHAR(50),
    message TEXT,
    data JSONB DEFAULT '{}',
    channels_sent TEXT[] DEFAULT '{}',
    acknowledged BOOLEAN DEFAULT false,
    acknowledged_by VARCHAR(50),
    acknowledged_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- REPUTATION MONITORING
-- ============================================

CREATE TABLE reputation_checks (
    id SERIAL PRIMARY KEY,
    target VARCHAR(255),
    target_type VARCHAR(10) CHECK (target_type IN ('ip', 'domain', 'sender')),
    service VARCHAR(50),
    status VARCHAR(20) CHECK (status IN ('clean', 'listed', 'unknown', 'error')),
    details TEXT,
    score INT CHECK (score >= 0 AND score <= 100),
    checked_at TIMESTAMP DEFAULT NOW()
);

-- IP Reputation
CREATE TABLE ip_reputation (
    id SERIAL PRIMARY KEY,
    ip_address INET NOT NULL UNIQUE,
    spamhaus_status VARCHAR(20),
    barracuda_status VARCHAR(20),
    sorbs_status VARCHAR(20),
    spamcop_status VARCHAR(20),
    overall_score INT DEFAULT 100,
    last_check TIMESTAMP,
    check_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- SYSTEM METRICS
-- ============================================

CREATE TABLE system_metrics (
    id SERIAL PRIMARY KEY,
    cpu_percent FLOAT,
    ram_percent FLOAT,
    disk_percent FLOAT,
    load_1min FLOAT,
    load_5min FLOAT,
    load_15min FLOAT,
    network_in_mbps FLOAT,
    network_out_mbps FLOAT,
    io_wait_percent FLOAT,
    collected_at TIMESTAMP DEFAULT NOW()
);

-- Email metrics
CREATE TABLE email_metrics (
    id SERIAL PRIMARY KEY,
    date DATE NOT NULL,
    campaign_id INT,
    domain VARCHAR(255),
    sent_count INT DEFAULT 0,
    delivered_count INT DEFAULT 0,
    opened_count INT DEFAULT 0,
    clicked_count INT DEFAULT 0,
    bounced_count INT DEFAULT 0,
    complained_count INT DEFAULT 0,
    unsubscribed_count INT DEFAULT 0,
    bounce_rate FLOAT,
    open_rate FLOAT,
    click_rate FLOAT,
    collected_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(date, campaign_id, domain)
);

-- O365 accounts
CREATE TABLE o365_accounts (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    status VARCHAR(20) CHECK (status IN ('active', 'warming', 'blocked', 'disabled', 'quarantined')),
    last_used TIMESTAMP,
    sent_today INT DEFAULT 0,
    sent_total INT DEFAULT 0,
    success_rate FLOAT DEFAULT 0,
    last_check TIMESTAMP,
    config JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- BRAIN ENGINE INTEGRATION
-- ============================================

CREATE TABLE brain_configs (
    id SERIAL PRIMARY KEY,
    config_hash VARCHAR(64) UNIQUE,
    config_data JSONB NOT NULL,
    inbox_rate FLOAT,
    spam_rate FLOAT,
    test_count INT DEFAULT 0,
    winning BOOLEAN DEFAULT false,
    last_tested TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE brain_tests (
    id SERIAL PRIMARY KEY,
    config_id INT REFERENCES brain_configs(id),
    domain VARCHAR(255),
    inbox_count INT DEFAULT 0,
    spam_count INT DEFAULT 0,
    total_count INT DEFAULT 0,
    inbox_rate FLOAT,
    duration_ms INT,
    test_date DATE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- INDEXES FOR PERFORMANCE
-- ============================================

-- hamid_requests indexes
CREATE INDEX idx_hamid_requests_created_at ON hamid_requests(created_at);
CREATE INDEX idx_hamid_requests_provider_id ON hamid_requests(provider_id);
CREATE INDEX idx_hamid_requests_success ON hamid_requests(success);

-- mind_cycles indexes
CREATE INDEX idx_mind_cycles_started_at ON mind_cycles(started_at);
CREATE INDEX idx_mind_cycles_status ON mind_cycles(status);
CREATE INDEX idx_mind_cycles_health_score ON mind_cycles(health_score);

-- mind_diagnostics indexes
CREATE INDEX idx_mind_diagnostics_cycle_id ON mind_diagnostics(cycle_id);
CREATE INDEX idx_mind_diagnostics_severity ON mind_diagnostics(severity);
CREATE INDEX idx_mind_diagnostics_resolved ON mind_diagnostics(resolved);

-- alerts_log indexes
CREATE INDEX idx_alerts_log_created_at ON alerts_log(created_at);
CREATE INDEX idx_alerts_log_severity ON alerts_log(severity);
CREATE INDEX idx_alerts_log_acknowledged ON alerts_log(acknowledged);

-- reputation_checks indexes
CREATE INDEX idx_reputation_checks_target ON reputation_checks(target);
CREATE INDEX idx_reputation_checks_checked_at ON reputation_checks(checked_at);

-- email_metrics indexes
CREATE INDEX idx_email_metrics_date ON email_metrics(date);
CREATE INDEX idx_email_metrics_domain ON email_metrics(domain);

-- system_metrics indexes
CREATE INDEX idx_system_metrics_collected_at ON system_metrics(collected_at);

-- ============================================
-- INITIAL DATA
-- ============================================

-- Insert default alert channels
INSERT INTO alerts_config (channel, endpoint, min_severity, active) VALUES
('dashboard', 'internal://dashboard', 'INFO', true),
('telegram', 'https://api.telegram.org/bot{token}/sendMessage', 'WARNING', false),
('email', 'admin@wevads.com', 'CRITICAL', false);

-- Insert default HAMID providers
INSERT INTO hamid_providers (name, api_url, model, priority, timeout_ms, status) VALUES
('Cerebras', 'https://api.cerebras.ai/v1/chat/completions', 'cerebras-llama3.1-70b', 1, 10000, 'active'),
('Groq', 'https://api.groq.com/openai/v1/chat/completions', 'mixtral-8x7b-32768', 2, 10000, 'active'),
('DeepSeek', 'https://api.deepseek.com/v1/chat/completions', 'deepseek-chat', 3, 15000, 'active'),
('Gemini', 'https://generativelanguage.googleapis.com/v1beta/models/gemini-pro:generateContent', 'gemini-pro', 4, 15000, 'active'),
('Claude', 'https://api.anthropic.com/v1/messages', 'claude-3-sonnet-20240229', 5, 20000, 'active'),
('Hyperbolic', 'https://api.hyperbolic.xyz/v1/chat/completions', 'hyperbolic-7b', 6, 10000, 'active'),
('Mistral', 'https://api.mistral.ai/v1/chat/completions', 'mistral-medium', 7, 15000, 'active'),
('Cohere', 'https://api.cohere.ai/v1/generate', 'command-r-plus', 8, 15000, 'active'),
('SambaNova', 'https://api.sambanova.ai/v1/chat/completions', 'sn-llama-3.1-70b', 9, 10000, 'active'),
('Ollama', 'http://127.0.0.1:11434/api/chat', 'llama3.1', 10, 30000, 'active'),
('OpenRouter', 'https://openrouter.ai/api/v1/chat/completions', 'openrouter/quasar-alpha', 11, 20000, 'active');

-- Insert KB categories
INSERT INTO kb_categories (name, description) VALUES
('architecture', 'System architecture and infrastructure'),
('tracking', 'Tracking systems and conversion optimization'),
('database', 'Database performance and optimization'),
('workflow', 'End-to-end workflows and automation'),
('troubleshooting', 'Common problems and solutions'),
('best_practices', 'Best practices and guidelines');

-- ============================================
-- FUNCTIONS AND TRIGGERS
-- ============================================

-- Update timestamp trigger
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Apply trigger to tables with updated_at
DO $$ 
DECLARE 
    t text;
BEGIN 
    FOR t IN 
        SELECT table_name 
        FROM information_schema.columns 
        WHERE column_name = 'updated_at' 
        AND table_schema = 'public'
    LOOP
        EXECUTE format('DROP TRIGGER IF EXISTS update_%s_updated_at ON %I', t, t);
        EXECUTE format('CREATE TRIGGER update_%s_updated_at BEFORE UPDATE ON %I FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()', t, t);
    END LOOP;
END $$;

-- Function to log all changes
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100),
    record_id INT,
    action VARCHAR(10) CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
    old_data JSONB,
    new_data JSONB,
    changed_by VARCHAR(100) DEFAULT 'system',
    changed_at TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, record_id, action, new_data)
        VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', row_to_json(NEW));
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, record_id, action, old_data, new_data)
        VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', row_to_json(OLD), row_to_json(NEW));
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, record_id, action, old_data)
        VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', row_to_json(OLD));
    END IF;
    RETURN NULL;
END;
$$ language 'plpgsql';

-- Apply audit trigger to important tables
CREATE TRIGGER audit_hamid_providers AFTER INSERT OR UPDATE OR DELETE ON hamid_providers FOR EACH ROW EXECUTE FUNCTION log_changes();
CREATE TRIGGER audit_mind_actions AFTER INSERT OR UPDATE OR DELETE ON mind_actions FOR EACH ROW EXECUTE FUNCTION log_changes();
CREATE TRIGGER audit_alerts_config AFTER INSERT OR UPDATE OR DELETE ON alerts_config FOR EACH ROW EXECUTE FUNCTION log_changes();

-- ============================================
-- VIEWS FOR REPORTING
-- ============================================

-- Daily summary view
CREATE VIEW daily_summary AS
SELECT 
    DATE(created_at) as date,
    COUNT(*) as total_cycles,
    AVG(health_score) as avg_health_score,
    SUM(issues_found) as total_issues,
    SUM(actions_taken) as total_actions,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_cycles,
    COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed_cycles
FROM mind_cycles
GROUP BY DATE(created_at);

-- Provider performance view
CREATE VIEW provider_performance AS
SELECT 
    p.name,
    p.priority,
    p.status,
    COUNT(r.id) as total_requests,
    AVG(r.latency_ms) as avg_latency,
    SUM(CASE WHEN r.success THEN 1 ELSE 0 END) * 100.0 / COUNT(r.id) as success_rate,
    MIN(r.created_at) as first_request,
    MAX(r.created_at) as last_request
FROM hamid_providers p
LEFT JOIN hamid_requests r ON p.id = r.provider_id
GROUP BY p.id, p.name, p.priority, p.status
ORDER BY p.priority;

-- Current system health view
CREATE VIEW current_system_health AS
SELECT 
    'cpu' as metric,
    AVG(cpu_percent) as value,
    CASE 
        WHEN AVG(cpu_percent) > 90 THEN 'CRITICAL'
        WHEN AVG(cpu_percent) > 80 THEN 'WARNING'
        ELSE 'HEALTHY'
    END as status
FROM system_metrics 
WHERE collected_at > NOW() - INTERVAL '1 hour'
UNION ALL
SELECT 
    'memory',
    AVG(ram_percent),
    CASE 
        WHEN AVG(ram_percent) > 90 THEN 'CRITICAL'
        WHEN AVG(ram_percent) > 80 THEN 'WARNING'
        ELSE 'HEALTHY'
    END
FROM system_metrics 
WHERE collected_at > NOW() - INTERVAL '1 hour'
UNION ALL
SELECT 
    'disk',
    AVG(disk_percent),
    CASE 
        WHEN AVG(disk_percent) > 85 THEN 'CRITICAL'
        WHEN AVG(disk_percent) > 75 THEN 'WARNING'
        ELSE 'HEALTHY'
    END
FROM system_metrics 
WHERE collected_at > NOW() - INTERVAL '1 hour';

-- ============================================
-- GRANT PERMISSIONS
-- ============================================

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO adx_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO adx_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO adx_user;

-- ============================================
-- COMPLETION MESSAGE
-- ============================================

SELECT '✅ Database schema created successfully!' as message;
SELECT COUNT(*) as total_tables FROM information_schema.tables WHERE table_schema = 'public';