-- Migration: Add venues table and venue_id to fixtures
-- Date: 2026-01-17

-- Create venues table
CREATE TABLE IF NOT EXISTS venues (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(500),
    city VARCHAR(255),
    country VARCHAR(100),
    capacity INT,
    surface VARCHAR(50),
    image VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_venues_city (city),
    INDEX idx_venues_country (country)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add venue_id column to fixtures table
ALTER TABLE fixtures ADD COLUMN venue_id INT NULL AFTER venue_city;

-- Add index for venue_id
ALTER TABLE fixtures ADD INDEX idx_fixtures_venue_id (venue_id);

-- Note: We're not adding FK constraint because venues may not exist yet when fixtures are inserted
-- The application will handle venue data integrity
