-- ============================================
-- VIDEO MANAGEMENT SYSTEM - DATABASE SCHEMA
-- Compatible with phpMyAdmin
-- ============================================

-- Create database (run this separately if needed)
-- CREATE DATABASE IF NOT EXISTS video_management CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE video_management;

-- ============================================
-- TABLE: arenas
-- Stores arena information
-- ============================================
CREATE TABLE IF NOT EXISTS arenas (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    logo VARCHAR(255) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    address VARCHAR(255) DEFAULT NULL,
    phone VARCHAR(50) DEFAULT NULL,
    email VARCHAR(100) DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_arena_name (name),
    INDEX idx_arenas_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: courts
-- Stores court information linked to arenas
-- ============================================
CREATE TABLE IF NOT EXISTS courts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    arena_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_arena_court (arena_id, name),
    CONSTRAINT fk_courts_arena FOREIGN KEY (arena_id)
        REFERENCES arenas(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: videos
-- Stores video metadata
-- ============================================
CREATE TABLE IF NOT EXISTS videos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    arena_id INT UNSIGNED NOT NULL,
    court_id INT UNSIGNED NOT NULL,
    type VARCHAR(50) NOT NULL,
    filename VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) DEFAULT NULL,
    file_size BIGINT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_videos_arena (arena_id),
    INDEX idx_videos_court (court_id),
    INDEX idx_videos_type (type),
    INDEX idx_videos_created (created_at),
    INDEX idx_videos_search (arena_id, court_id, type, created_at),
    CONSTRAINT fk_videos_arena FOREIGN KEY (arena_id)
        REFERENCES arenas(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_videos_court FOREIGN KEY (court_id)
        REFERENCES courts(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: cleanup_logs
-- Logs cleanup script executions
-- ============================================
CREATE TABLE IF NOT EXISTS cleanup_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    videos_deleted INT UNSIGNED DEFAULT 0,
    files_deleted INT UNSIGNED DEFAULT 0,
    bytes_freed BIGINT UNSIGNED DEFAULT 0,
    errors TEXT DEFAULT NULL,
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;