-- Ensenada Run Database Schema
-- Database: ensenadarun

CREATE DATABASE IF NOT EXISTS ensenadarun;
USE ensenadarun;

-- Table: user
-- Stores user account information for admin authentication
CREATE TABLE IF NOT EXISTS `user` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_username (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: session
-- Stores user authentication sessions
CREATE TABLE IF NOT EXISTS `session` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `bearer_uuid` BINARY(16) NOT NULL UNIQUE,
  `user_id` INT NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_bearer_uuid (`bearer_uuid`),
  INDEX idx_user_id (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: role
-- Stores user roles for permissions
CREATE TABLE IF NOT EXISTS `role` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL UNIQUE,
  INDEX idx_name (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: role_user
-- Many-to-many relationship between users and roles
CREATE TABLE IF NOT EXISTS `role_user` (
  `user_id` INT NOT NULL,
  `role_id` INT NOT NULL,
  PRIMARY KEY (`user_id`, `role_id`),
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`role_id`) REFERENCES `role`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: image
-- Stores uploaded images
CREATE TABLE IF NOT EXISTS `image` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `filename` VARCHAR(255) NOT NULL,
  `original_filename` VARCHAR(255) NOT NULL,
  `path` VARCHAR(500) NOT NULL,
  `content_type` VARCHAR(100) NOT NULL,
  `size` INT NOT NULL,
  `width` INT NOT NULL,
  `height` INT NOT NULL,
  `user_id` INT NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_id (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: attachment
-- Stores uploaded file attachments
CREATE TABLE IF NOT EXISTS `attachment` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `filename` VARCHAR(255) NOT NULL,
  `original_filename` VARCHAR(255) NOT NULL,
  `path` VARCHAR(500) NOT NULL,
  `content_type` VARCHAR(100) NOT NULL,
  `size` INT NOT NULL,
  `extension` VARCHAR(10) NOT NULL,
  `user_id` INT NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_id (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: race
-- Stores athletic race information
CREATE TABLE IF NOT EXISTS `race` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `city` VARCHAR(100) NOT NULL,
  `main_distance` VARCHAR(50) NOT NULL,
  `poster_image_id` INT NOT NULL,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `page_link` VARCHAR(500),
  `registration_link` VARCHAR(500),
  `type` VARCHAR(100) NOT NULL,
  `datetime_start` DATETIME NOT NULL,
  `description` TEXT,
  `is_popular` BOOLEAN DEFAULT FALSE,
  `accepted` BOOLEAN NOT NULL DEFAULT 0,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted` DATETIME NULL DEFAULT NULL,
  INDEX idx_datetime_start (`datetime_start`),
  INDEX idx_deleted (`deleted`),
  INDEX idx_city (`city`),
  INDEX idx_type (`type`),
  INDEX idx_is_popular (`is_popular`),
  INDEX idx_accepted (`accepted`),
  FOREIGN KEY (`poster_image_id`) REFERENCES `image`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: gallery_link
-- Stores external photo gallery links for races
CREATE TABLE IF NOT EXISTS `gallery_link` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `race_id` INT NOT NULL,
  `url` VARCHAR(500) NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted` DATETIME NULL DEFAULT NULL,
  INDEX idx_race_id (`race_id`),
  INDEX idx_deleted (`deleted`),
  FOREIGN KEY (`race_id`) REFERENCES `race`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default roles
INSERT INTO `role` (`name`) VALUES
  ('admin'),
  ('user')
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- Insert default admin user
-- Password: admin (Note: In production, use password_hash() for security)
INSERT INTO `user` (`username`, `password`) VALUES
  ('admin', 'admin')
ON DUPLICATE KEY UPDATE `password` = VALUES(`password`);

-- Assign admin role to admin user
INSERT INTO `role_user` (`user_id`, `role_id`)
SELECT u.id, r.id
FROM `user` u, `role` r
WHERE u.username = 'admin' AND r.name = 'admin'
ON DUPLICATE KEY UPDATE `user_id` = VALUES(`user_id`);

-- Show table structure
SHOW TABLES;
