CREATE TABLE IF NOT EXISTS `golf_tournaments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `slug` varchar(100) NOT NULL,
  `name` varchar(255) NOT NULL,
  `event_date` date DEFAULT NULL,
  `course_par` smallint(6) NOT NULL DEFAULT 69,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_slug` (`slug`),
  KEY `idx_active_sort` (`is_active`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `golf_scoring_teams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tournament_id` int(11) DEFAULT NULL,
  `golf_league_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `color` varchar(20) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tournament` (`tournament_id`),
  KEY `idx_league` (`golf_league_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `golf_scoring_players` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `team_id` int(11) DEFAULT NULL,
  `tournament_id` int(11) DEFAULT NULL,
  `golf_league_id` int(11) DEFAULT NULL,
  `golf_league_booking_id` int(11) DEFAULT NULL,
  `display_name` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_team` (`team_id`),
  KEY `idx_tournament` (`tournament_id`),
  KEY `idx_league` (`golf_league_id`),
  KEY `idx_booking` (`golf_league_booking_id`),
  KEY `idx_display_name` (`display_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `golf_scoring_rounds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tournament_id` int(11) DEFAULT NULL,
  `golf_league_id` int(11) DEFAULT NULL,
  `round_date` date NOT NULL,
  `round_label` varchar(100) NOT NULL,
  `is_locked` tinyint(1) NOT NULL DEFAULT 0,
  `pins_sent_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tournament` (`tournament_id`),
  KEY `idx_league` (`golf_league_id`),
  KEY `idx_round_date` (`round_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `golf_scoring_scores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `round_id` int(11) NOT NULL,
  `player_id` int(11) NOT NULL,
  `hole_1` smallint(6) DEFAULT NULL,
  `hole_2` smallint(6) DEFAULT NULL,
  `hole_3` smallint(6) DEFAULT NULL,
  `hole_4` smallint(6) DEFAULT NULL,
  `hole_5` smallint(6) DEFAULT NULL,
  `hole_6` smallint(6) DEFAULT NULL,
  `hole_7` smallint(6) DEFAULT NULL,
  `hole_8` smallint(6) DEFAULT NULL,
  `hole_9` smallint(6) DEFAULT NULL,
  `hole_10` smallint(6) DEFAULT NULL,
  `hole_11` smallint(6) DEFAULT NULL,
  `hole_12` smallint(6) DEFAULT NULL,
  `hole_13` smallint(6) DEFAULT NULL,
  `hole_14` smallint(6) DEFAULT NULL,
  `hole_15` smallint(6) DEFAULT NULL,
  `hole_16` smallint(6) DEFAULT NULL,
  `hole_17` smallint(6) DEFAULT NULL,
  `hole_18` smallint(6) DEFAULT NULL,
  `total` smallint(6) NOT NULL DEFAULT 0,
  `pin_hash` char(64) DEFAULT NULL,
  `pin_salt` char(16) DEFAULT NULL,
  `pin_plain` varchar(8) DEFAULT NULL,
  `pin_sent_at` datetime DEFAULT NULL,
  `auth_token` char(40) DEFAULT NULL,
  `auth_token_expires_at` datetime DEFAULT NULL,
  `updated_by_player_id` int(11) DEFAULT NULL,
  `updated_by_admin` tinyint(1) NOT NULL DEFAULT 0,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_round_player` (`round_id`, `player_id`),
  KEY `idx_auth_token` (`auth_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'golf_scoring_scores' AND COLUMN_NAME = 'pin_plain');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE `golf_scoring_scores` ADD COLUMN `pin_plain` varchar(8) DEFAULT NULL AFTER `pin_salt`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS `golf_score_audit` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `round_id` int(11) NOT NULL,
  `target_player_id` int(11) NOT NULL,
  `hole` tinyint(4) NOT NULL,
  `old_value` smallint(6) DEFAULT NULL,
  `new_value` smallint(6) DEFAULT NULL,
  `edited_by_player_id` int(11) DEFAULT NULL,
  `edited_by_admin_user` varchar(100) DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_round_target_at` (`round_id`, `target_player_id`, `at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `golf_pin_attempts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `round_id` int(11) NOT NULL,
  `display_name_norm` varchar(255) NOT NULL,
  `ip` varchar(45) NOT NULL,
  `success` tinyint(1) NOT NULL DEFAULT 0,
  `at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_round_name_ip_at` (`round_id`, `display_name_norm`, `ip`, `at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
