CREATE DATABASE IF NOT EXISTS pigeon_race;
USE pigeon_race;

-- Admin table
CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default admin (username: admin, password: admin123)
INSERT INTO admins (username, password) VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

-- Players table
CREATE TABLE players (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    contact VARCHAR(20),
    city VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Pigeons table (each player can have multiple pigeons)
CREATE TABLE pigeons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    player_id INT NOT NULL,
    pigeon_name VARCHAR(100) NOT NULL,
    ring_number VARCHAR(50) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE
);

-- Race results table
CREATE TABLE race_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pigeon_id INT NOT NULL,
    race_date DATE NOT NULL,
    start_time TIME NOT NULL DEFAULT '06:00:00',
    landing_time TIME,
    total_duration INT, -- in minutes
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (pigeon_id) REFERENCES pigeons(id) ON DELETE CASCADE
);

-- Settings table for admin configuration
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(50) UNIQUE NOT NULL,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default settings
INSERT INTO settings (setting_key, setting_value) VALUES 
('total_pigeons', '7'),
('race_start_time', '06:00:00'),
('is_active', '1');