-- Schema Supabase pour FraudShield AI SaaS
-- Production-grade schema with RLS policies

-- Table: transactions (core)
CREATE TABLE transactions (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id TEXT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    currency TEXT DEFAULT 'EUR',
    merchant TEXT,
    country TEXT,
    device_id TEXT,
    ip_address INET,
    category TEXT CHECK (category IN ('retail', 'crypto', 'gaming', 'travel', 'other')),
    risk_score DECIMAL(3,2) CHECK (risk_score >= 0 AND risk_score <= 1),
    ml_score DECIMAL(3,2),
    rule_score DECIMAL(3,2),
    anomaly_score DECIMAL(3,2),
    graph_score DECIMAL(3,2),
    decision TEXT CHECK (decision IN ('ALLOW', 'REVIEW', 'BLOCK')),
    reasons JSONB DEFAULT '[]',
    velocity_1h INTEGER DEFAULT 0,
    velocity_24h INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    processed_at TIMESTAMP WITH TIME ZONE,
    CONSTRAINT check_scores CHECK (risk_score BETWEEN 0 AND 1)
);

-- Index for performance
CREATE INDEX idx_transactions_user_time ON transactions(user_id, created_at DESC);
CREATE INDEX idx_transactions_decision ON decision WHERE decision IN ('BLOCK', 'REVIEW');
CREATE INDEX idx_transactions_risk ON risk_score DESC;

-- Table: user_profiles (behavioral memory)
CREATE TABLE user_profiles (
    user_id TEXT PRIMARY KEY,
    avg_amount DECIMAL(12,2) DEFAULT 100,
    total_transactions INTEGER DEFAULT 0,
    home_country TEXT DEFAULT 'FR',
    risk_level DECIMAL(3,2) DEFAULT 0.0,
    device_history JSONB DEFAULT '[]',
    geo_history JSONB DEFAULT '[]',
    time_pattern JSONB DEFAULT '{}',
    velocity_threshold INTEGER DEFAULT 10,
    last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Table: fraud_feedback (auto-learning loop)
CREATE TABLE fraud_feedback (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    transaction_id UUID REFERENCES transactions(id) ON DELETE CASCADE,
    is_fraud BOOLEAN NOT NULL,
    confirmed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    label_source TEXT CHECK (label_source IN ('manual', 'chargeback', 'auto')),
    confidence DECIMAL(3,2),
    feedback_data JSONB
);

-- Table: device_fingerprints
CREATE TABLE device_fingerprints (
    device_id TEXT PRIMARY KEY,
    user_id TEXT REFERENCES user_profiles(user_id),
    fingerprint_hash TEXT,
    trust_score DECIMAL(3,2) DEFAULT 0.5,
    first_seen TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_seen TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    risk_flags JSONB DEFAULT '[]',
    canvas_entropy DECIMAL(5,2),
    browser_entropy DECIMAL(5,2)
);

-- Table: graph_relations (network detection)
CREATE TABLE graph_relations (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    source_id TEXT NOT NULL,
    target_id TEXT NOT NULL,
    relation_type TEXT CHECK (relation_type IN ('user-device', 'user-ip', 'device-ip', 'user-merchant', 'ip-merchant')),
    strength DECIMAL(3,2) DEFAULT 1.0,
    fraud_count INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(source_id, target_id, relation_type)
);

-- Table: mcp_logs (orchestration tracking)
CREATE TABLE mcp_logs (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    transaction_id UUID REFERENCES transactions(id),
    mcp_version TEXT,
    ml_latency_ms INTEGER,
    rule_latency_ms INTEGER,
    graph_latency_ms INTEGER,
    total_latency_ms INTEGER,
    orchestration_metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Row Level Security (RLS)
ALTER TABLE transactions ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE fraud_feedback ENABLE ROW LEVEL SECURITY;

-- RLS Policies for multi-tenant SaaS
CREATE POLICY tenant_isolation_transactions ON transactions
    USING (auth.uid()::text = user_id OR auth.role() = 'service_role');

CREATE POLICY tenant_isolation_profiles ON user_profiles
    USING (auth.uid()::text = user_id OR auth.role() = 'service_role');

-- Function: Update user profile stats
CREATE OR REPLACE FUNCTION update_user_profile()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_profiles (user_id, avg_amount, total_transactions, home_country)
    VALUES (NEW.user_id, NEW.amount, 1, NEW.country)
    ON CONFLICT (user_id) DO UPDATE SET
        avg_amount = (user_profiles.avg_amount * user_profiles.total_transactions + NEW.amount) / (user_profiles.total_transactions + 1),
        total_transactions = user_profiles.total_transactions + 1,
        last_updated = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to auto-update profiles
CREATE TRIGGER trigger_update_profile
AFTER INSERT ON transactions
FOR EACH ROW EXECUTE FUNCTION update_user_profile();

-- Function: Real-time fraud alerts ( edge function compatible)
CREATE OR REPLACE FUNCTION check_fraud_threshold()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.risk_score > 0.8 THEN
        -- Trigger edge function or webhook
        PERFORM pg_notify('fraud_alert', json_build_object(
            'transaction_id', NEW.id,
            'user_id', NEW.user_id,
            'score', NEW.risk_score,
            'decision', NEW.decision
        )::text);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_fraud_alert
AFTER INSERT ON transactions
FOR EACH ROW EXECUTE FUNCTION check_fraud_threshold();