-- HK Horse Racing Analytics - Database Schema
-- PostgreSQL 14+

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";  -- For fuzzy text search

-- ============================================
-- MASTER DATA TABLES
-- ============================================

-- Horses
CREATE TABLE horses (
    horse_id VARCHAR(20) PRIMARY KEY,  -- HKJC horse ID
    horse_name VARCHAR(100) NOT NULL,
    horse_name_en VARCHAR(100),
    country_of_origin VARCHAR(50),
    age INTEGER,
    sex VARCHAR(10),  -- Colt, Filly, Gelding, Horse, Mare
    color VARCHAR(30),
    sire VARCHAR(100),
    dam VARCHAR(100),
    dam_sire VARCHAR(100),
    owner VARCHAR(100),
    importer VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_horses_name ON horses(horse_name);
CREATE INDEX idx_horses_sire ON horses(sire);

-- Jockeys
CREATE TABLE jockeys (
    jockey_id VARCHAR(20) PRIMARY KEY,  -- HKJC jockey ID
    jockey_name VARCHAR(100) NOT NULL,
    jockey_name_en VARCHAR(100),
    license_type VARCHAR(20),  -- Jockey, Apprentice
    nationality VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_jockeys_name ON jockeys(jockey_name);

-- Trainers
CREATE TABLE trainers (
    trainer_id VARCHAR(20) PRIMARY KEY,  -- HKJC trainer ID
    trainer_name VARCHAR(100) NOT NULL,
    trainer_name_en VARCHAR(100),
    nationality VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_trainers_name ON trainers(trainer_name);

-- ============================================
-- RACE DATA TABLES
-- ============================================

-- Races
CREATE TABLE races (
    race_id VARCHAR(30) PRIMARY KEY,  -- Format: YYYYMMDD_V_RACE_NUMBER
    race_date DATE NOT NULL,
    race_time TIME,
    venue VARCHAR(20) NOT NULL,  -- Sha Tin, Happy Valley
    race_number INTEGER NOT NULL,
    race_name VARCHAR(200),
    race_class VARCHAR(20),  -- Class 1-5, Griffins, etc.
    race_type VARCHAR(50),  -- Handicap, Set Weights, etc.
    distance INTEGER,  -- in meters
    track VARCHAR(20),  -- Turf, All Weather
    course VARCHAR(20),  -- A, B, C course
    track_condition VARCHAR(20),  -- Good, Yielding, Soft, etc.
    going VARCHAR(50),
    prize_money_hkd DECIMAL(12,2),
    weather VARCHAR(20),
    temperature_c DECIMAL(5,2),
    humidity_pct INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(race_date, venue, race_number)
);

CREATE INDEX idx_races_date ON races(race_date DESC);
CREATE INDEX idx_races_venue ON races(venue);
CREATE INDEX idx_races_class ON races(race_class);

-- Runners (horses entered in races)
CREATE TABLE runners (
    runner_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    race_id VARCHAR(30) REFERENCES races(race_id),
    horse_id VARCHAR(20) REFERENCES horses(horse_id),
    jockey_id VARCHAR(20) REFERENCES jockeys(jockey_id),
    trainer_id VARCHAR(20) REFERENCES trainers(trainer_id),
    barrier INTEGER,
    horse_number INTEGER,  -- Race card number
    weight_carried DECIMAL(6,2),  -- in kg
    handicap_rating INTEGER,
    declared_weight DECIMAL(6,2),  -- in kg
    gear VARCHAR(100),  -- Equipment (blinkers, etc.)
    last_6_runs VARCHAR(20),  -- Form string e.g., "1-2-3-4-5-6"
    days_since_last_run INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(race_id, horse_id)
);

CREATE INDEX idx_runners_race ON runners(race_id);
CREATE INDEX idx_runners_horse ON runners(horse_id);
CREATE INDEX idx_runners_jockey ON runners(jockey_id);
CREATE INDEX idx_runners_trainer ON runners(trainer_id);

-- ============================================
-- RESULTS & PERFORMANCE TABLES
-- ============================================

-- Race Results
CREATE TABLE results (
    result_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    runner_id UUID REFERENCES runners(runner_id),
    race_id VARCHAR(30) REFERENCES races(race_id),
    finishing_position INTEGER,
    finish_time DECIMAL(8,3),  -- in seconds
    margin DECIMAL(8,3),  -- margin behind winner
    lengths_behind DECIMAL(6,2),
    sectional_time DECIMAL(8,3),
    running_position_600m INTEGER,  -- Position at 600m
    running_position_400m INTEGER,  -- Position at 400m
    running_position_200m INTEGER,  -- Position at 200m
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(race_id, runner_id)
);

CREATE INDEX idx_results_race ON results(race_id);
CREATE INDEX idx_results_position ON results(finishing_position);

-- Dividends
CREATE TABLE dividends (
    dividend_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    race_id VARCHAR(30) REFERENCES races(race_id),
    dividend_type VARCHAR(20),  -- WIN, PLACE, QUINELLA, QUINELLA_PLACE, etc.
    combination VARCHAR(50),  -- Winning numbers
    dividend_hkd DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_dividends_race ON dividends(race_id);

-- ============================================
-- MARKET DATA TABLES
-- ============================================

-- Odds History
CREATE TABLE odds_history (
    odds_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    runner_id UUID REFERENCES runners(runner_id),
    race_id VARCHAR(30) REFERENCES races(race_id),
    timestamp TIMESTAMP NOT NULL,
    win_odds DECIMAL(8,2),
    place_odds DECIMAL(8,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_odds_runner ON odds_history(runner_id);
CREATE INDEX idx_odds_timestamp ON odds_history(timestamp DESC);

-- ============================================
-- FEATURE ENGINEERING TABLES
-- ============================================

-- Engineered Features (for ML)
CREATE TABLE features (
    feature_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    runner_id UUID REFERENCES runners(runner_id),
    race_id VARCHAR(30) REFERENCES races(race_id),
    feature_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Form Features
    avg_finish_pos_last_6 DECIMAL(4,2),
    win_rate_last_6 DECIMAL(5,4),
    place_rate_last_6 DECIMAL(5,4),
    days_since_last_run INTEGER,
    runs_in_last_30_days INTEGER,
    
    -- Class Features
    class_rating INTEGER,
    class_diff_from_last INTEGER,
    
    -- Distance Features
    distance_starts INTEGER,
    distance_win_rate DECIMAL(5,4),
    distance_place_rate DECIMAL(5,4),
    distance_avg_margin DECIMAL(6,3),
    
    -- Track Features
    track_starts INTEGER,
    track_win_rate DECIMAL(5,4),
    track_place_rate DECIMAL(5,4),
    
    -- Jockey Features
    jockey_win_rate_30_days DECIMAL(5,4),
    jockey_place_rate_30_days DECIMAL(5,4),
    jockey_horse_win_rate DECIMAL(5,4),
    
    -- Trainer Features
    trainer_win_rate_30_days DECIMAL(5,4),
    trainer_place_rate_30_days DECIMAL(5,4),
    trainer_horse_win_rate DECIMAL(5,4),
    
    -- Weight Features
    weight_diff_from_last DECIMAL(6,2),
    weight_above_min DECIMAL(6,2),
    
    -- Barrier Features
    barrier_win_rate DECIMAL(5,4),
    barrier_place_rate DECIMAL(5,4),
    
    -- Market Features
    opening_odds DECIMAL(8,2),
    odds_movement_pct DECIMAL(6,2),
    market_support BOOLEAN,
    
    UNIQUE(runner_id, race_id)
);

CREATE INDEX idx_features_runner ON features(runner_id);
CREATE INDEX idx_features_race ON features(race_id);

-- ============================================
-- ML PREDICTIONS TABLES
-- ============================================

-- Model Predictions
CREATE TABLE predictions (
    prediction_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    runner_id UUID REFERENCES runners(runner_id),
    race_id VARCHAR(30) REFERENCES races(race_id),
    model_version VARCHAR(20),
    prediction_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Probability Estimates
    win_probability DECIMAL(5,4),
    place_probability DECIMAL(5,4),  -- Top 3
    show_probability DECIMAL(5,4),  -- Top 4
    
    -- Rankings
    win_rank INTEGER,
    place_rank INTEGER,
    
    -- Confidence
    confidence_score DECIMAL(5,4),
    
    -- Explainability
    top_features JSONB,  -- Top 5 features influencing prediction
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(runner_id, race_id, model_version)
);

CREATE INDEX idx_predictions_race ON predictions(race_id);
CREATE INDEX idx_predictions_win_prob ON predictions(win_probability DESC);

-- Model Performance
CREATE TABLE model_performance (
    performance_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    model_version VARCHAR(20),
    evaluation_date DATE,
    metric_name VARCHAR(50),
    metric_value DECIMAL(10,6),
    sample_size INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- LOGGING & MONITORING TABLES
-- ============================================

-- Data Ingestion Logs
CREATE TABLE ingestion_logs (
    log_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    source VARCHAR(50),
    data_type VARCHAR(50),
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    status VARCHAR(20),  -- SUCCESS, FAILED, PARTIAL
    records_processed INTEGER,
    records_failed INTEGER,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_ingestion_date ON ingestion_logs(created_at DESC);

-- System Logs
CREATE TABLE system_logs (
    log_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    log_level VARCHAR(20),  -- INFO, WARNING, ERROR
    component VARCHAR(50),
    message TEXT,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_system_date ON system_logs(created_at DESC);

-- ============================================
-- VIEWS FOR ANALYTICS
-- ============================================

-- View: Runner Performance Summary
CREATE VIEW runner_performance AS
SELECT 
    r.race_id,
    r.race_date,
    r.venue,
    r.distance,
    ru.horse_id,
    h.horse_name,
    ru.jockey_id,
    j.jockey_name,
    ru.trainer_id,
    t.trainer_name,
    res.finishing_position,
    res.finish_time,
    res.margin,
    o.win_odds,
    o.place_odds
FROM races r
JOIN runners ru ON r.race_id = ru.race_id
JOIN horses h ON ru.horse_id = h.horse_id
JOIN jockeys j ON ru.jockey_id = j.jockey_id
JOIN trainers t ON ru.trainer_id = t.trainer_id
LEFT JOIN results res ON ru.runner_id = res.runner_id
LEFT JOIN LATERAL (
    SELECT win_odds, place_odds 
    FROM odds_history 
    WHERE runner_id = ru.runner_id 
    ORDER BY timestamp DESC 
    LIMIT 1
) o ON true;

-- View: Model Accuracy Tracking
CREATE VIEW model_accuracy AS
SELECT 
    p.model_version,
    COUNT(*) as total_predictions,
    SUM(CASE WHEN res.finishing_position = 1 THEN 1 ELSE 0 END) as winners,
    SUM(CASE WHEN res.finishing_position <= 3 THEN 1 ELSE 0 END) as placed,
    AVG(CASE WHEN res.finishing_position = 1 THEN 1.0 ELSE 0.0 END) as win_rate,
    AVG(CASE WHEN res.finishing_position <= 3 THEN 1.0 ELSE 0.0 END) as place_rate
FROM predictions p
JOIN results res ON p.runner_id = res.runner_id
GROUP BY p.model_version;

-- ============================================
-- FUNCTIONS & TRIGGERS
-- ============================================

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

-- Apply to relevant tables
CREATE TRIGGER update_horses_updated_at BEFORE UPDATE ON horses
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_jockeys_updated_at BEFORE UPDATE ON jockeys
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_trainers_updated_at BEFORE UPDATE ON trainers
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
