-- Fixture Manager Database Schema
-- MySQL DDL Script for automated database creation

-- Create database if it doesn't exist
CREATE DATABASE IF NOT EXISTS fixture_manager 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE fixture_manager;

-- Users table for authentication
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(80) NOT NULL UNIQUE,
    email VARCHAR(120) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    is_admin BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Primary matches table storing core fixture data
CREATE TABLE IF NOT EXISTS matches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    match_number INT NOT NULL UNIQUE COMMENT 'Match # from fixture file',
    fighter1_township VARCHAR(255) NOT NULL COMMENT 'Fighter1 (Township)',
    fighter2_township VARCHAR(255) NOT NULL COMMENT 'Fighter2 (Township)', 
    venue_kampala_township VARCHAR(255) NOT NULL COMMENT 'Venue (Kampala Township)',
    
    -- System fields
    start_time DATETIME NULL COMMENT 'Match start time',
    end_time DATETIME NULL COMMENT 'Match end time',
    result VARCHAR(255) NULL COMMENT 'Match result/outcome',
    filename VARCHAR(1024) NOT NULL COMMENT 'Original fixture filename',
    file_sha1sum VARCHAR(255) NOT NULL COMMENT 'SHA1 checksum of fixture file',
    fixture_id VARCHAR(255) NOT NULL UNIQUE COMMENT 'Unique fixture identifier',
    active_status BOOLEAN DEFAULT FALSE COMMENT 'Active status flag',
    
    -- ZIP file related fields
    zip_filename VARCHAR(1024) NULL COMMENT 'Associated ZIP filename',
    zip_sha1sum VARCHAR(255) NULL COMMENT 'SHA1 checksum of ZIP file',
    zip_upload_status ENUM('pending', 'uploading', 'completed', 'failed') DEFAULT 'pending',
    zip_upload_progress DECIMAL(5,2) DEFAULT 0.00 COMMENT 'Upload progress percentage',
    
    -- Metadata
    created_by INT NULL COMMENT 'User who created this record',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_match_number (match_number),
    INDEX idx_fixture_id (fixture_id),
    INDEX idx_active_status (active_status),
    INDEX idx_file_sha1sum (file_sha1sum),
    INDEX idx_zip_sha1sum (zip_sha1sum),
    INDEX idx_zip_upload_status (zip_upload_status),
    INDEX idx_created_by (created_by),
    
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Secondary outcomes table with foreign key relationships
CREATE TABLE IF NOT EXISTS match_outcomes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    match_id INT NOT NULL COMMENT 'Foreign key to matches table',
    column_name VARCHAR(255) NOT NULL COMMENT 'Result column name from fixture file',
    float_value DECIMAL(10,2) NOT NULL COMMENT 'Float value with 2-decimal precision',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_match_id (match_id),
    INDEX idx_column_name (column_name),
    INDEX idx_float_value (float_value),
    
    FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE,
    UNIQUE KEY unique_match_column (match_id, column_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- File uploads tracking table
CREATE TABLE IF NOT EXISTS file_uploads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(1024) NOT NULL,
    original_filename VARCHAR(1024) NOT NULL,
    file_path VARCHAR(2048) NOT NULL,
    file_size BIGINT NOT NULL,
    file_type ENUM('fixture', 'zip') NOT NULL,
    mime_type VARCHAR(255) NOT NULL,
    sha1sum VARCHAR(255) NOT NULL,
    upload_status ENUM('uploading', 'completed', 'failed', 'processing') DEFAULT 'uploading',
    upload_progress DECIMAL(5,2) DEFAULT 0.00,
    error_message TEXT NULL,
    
    -- Associated match (for ZIP files)
    match_id INT NULL,
    
    -- User tracking
    uploaded_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_filename (filename),
    INDEX idx_sha1sum (sha1sum),
    INDEX idx_upload_status (upload_status),
    INDEX idx_file_type (file_type),
    INDEX idx_match_id (match_id),
    INDEX idx_uploaded_by (uploaded_by),
    
    FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE SET NULL,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- System logs table for comprehensive logging
CREATE TABLE IF NOT EXISTS system_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    level ENUM('DEBUG', 'INFO', 'WARNING', 'ERROR', 'CRITICAL') NOT NULL,
    message TEXT NOT NULL,
    module VARCHAR(255) NULL,
    function_name VARCHAR(255) NULL,
    line_number INT NULL,
    
    -- Context information
    user_id INT NULL,
    match_id INT NULL,
    upload_id INT NULL,
    session_id VARCHAR(255) NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    
    -- Additional metadata
    extra_data JSON NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_level (level),
    INDEX idx_created_at (created_at),
    INDEX idx_user_id (user_id),
    INDEX idx_match_id (match_id),
    INDEX idx_upload_id (upload_id),
    INDEX idx_module (module),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE SET NULL,
    FOREIGN KEY (upload_id) REFERENCES file_uploads(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Session management table
CREATE TABLE IF NOT EXISTS user_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(255) NOT NULL UNIQUE,
    user_id INT NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_session_id (session_id),
    INDEX idx_user_id (user_id),
    INDEX idx_expires_at (expires_at),
    INDEX idx_is_active (is_active),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create default admin user (password: admin123 - CHANGE IN PRODUCTION!)
INSERT INTO users (username, email, password_hash, is_admin) 
VALUES (
    'admin', 
    'admin@fixture-daemon.local', 
    '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj3bp.Gm.F5e', -- admin123
    TRUE
) ON DUPLICATE KEY UPDATE username=username;

-- Create indexes for performance optimization
CREATE INDEX idx_matches_composite ON matches(active_status, zip_upload_status, created_at);
CREATE INDEX idx_outcomes_composite ON match_outcomes(match_id, column_name);
CREATE INDEX idx_uploads_composite ON file_uploads(upload_status, file_type, created_at);
CREATE INDEX idx_logs_composite ON system_logs(level, created_at, user_id);

-- Create views for common queries
CREATE OR REPLACE VIEW active_matches AS
SELECT 
    m.*,
    COUNT(mo.id) as outcome_count,
    GROUP_CONCAT(CONCAT(mo.column_name, ':', mo.float_value) SEPARATOR ';') as outcomes
FROM matches m
LEFT JOIN match_outcomes mo ON m.id = mo.match_id
WHERE m.active_status = TRUE
GROUP BY m.id;

CREATE OR REPLACE VIEW upload_summary AS
SELECT 
    DATE(created_at) as upload_date,
    file_type,
    upload_status,
    COUNT(*) as count,
    SUM(file_size) as total_size,
    AVG(upload_progress) as avg_progress
FROM file_uploads
GROUP BY DATE(created_at), file_type, upload_status;

-- Set up proper permissions (adjust as needed for your environment)
-- GRANT SELECT, INSERT, UPDATE, DELETE ON fixture_manager.* TO 'fixture_user'@'localhost';
-- FLUSH PRIVILEGES;

-- Display schema creation completion
SELECT 'Database schema created successfully!' as status;