-- EventsServiceOrder Table Creation Script
-- This table stores event planning information for both Golf and Dinner events

CREATE TABLE IF NOT EXISTS EventsServiceOrder (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Basic Event Information
    event_name VARCHAR(255) NOT NULL,
    date_ordered DATE NOT NULL,
    event_date DATE NOT NULL,
    event_time TIME,
    
    -- Event Type Flags
    has_golf ENUM('Y', 'N') DEFAULT 'N',
    has_dinner ENUM('Y', 'N') DEFAULT 'N',
    
    -- Organiser Information
    organiser_name VARCHAR(255),
    organiser_email VARCHAR(255),
    organiser_telephone VARCHAR(50),
    
    -- Registration Dates
    registration_open_date DATE,
    registration_cutoff_date DATE,
    
    -- Golf Event Fields
    golf_group VARCHAR(50), -- Epic, Ladies, White tees
    golf_group_type VARCHAR(50), -- Stableford, Medal
    golf_event_format VARCHAR(50), -- Pairs, Scramble, Shambles
    golf_max_places INT,
    golf_tee_start_type VARCHAR(50), -- 1 tee, 2 tee, shotgun
    golf_print_scorecards ENUM('Y', 'N') DEFAULT 'N',
    golf_print_buggy_sheets ENUM('Y', 'N') DEFAULT 'N',
    golf_womens_tee INT, -- 60, 58, 56, 52, 50, 45
    golf_mens_tee INT, -- 60, 58, 56, 52, 50, 45
    golf_slope_percent DECIMAL(5,2),
    golf_payment_method VARCHAR(50), -- TeeOne, Creditcard
    golf_payment_amount DECIMAL(10,2),
    
    -- Golf Prizes
    golf_nearest_pin_1 ENUM('Y', 'N') DEFAULT 'N',
    golf_nearest_pin_1_hole INT,
    golf_nearest_pin_2 ENUM('Y', 'N') DEFAULT 'N',
    golf_nearest_pin_2_hole INT,
    golf_longest_drive ENUM('Y', 'N') DEFAULT 'N',
    golf_longest_drive_hole INT,
    
    -- Dinner Event Fields
    dinner_event_name VARCHAR(255),
    dinner_time TIME,
    dinner_no_tables INT,
    dinner_max_places INT,
    dinner_kitchen_deadline_date DATE, -- Date kitchen needs table plans and meals agreed
    dinner_starters TEXT,
    dinner_mains TEXT,
    dinner_desserts TEXT,
    dinner_entertainment ENUM('Y', 'N') DEFAULT 'N',
    dinner_entertainment_details TEXT,
    dinner_payment_method VARCHAR(50), -- Credit Card, Teeone
    dinner_cost_per_participant DECIMAL(10,2),
    
    -- Status and Timestamps
    status VARCHAR(50) DEFAULT 'Draft', -- Draft, Submitted, Approved, Completed
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_event_date (event_date),
    INDEX idx_status (status),
    INDEX idx_date_ordered (date_ordered)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
