-- ═══════════════════════════════════════════════════════════
-- kunstlopapp_results — baza wyników norweskich łyżwiarzy
-- Schemat identyczny ze skatingapp_results + composite PK
-- ═══════════════════════════════════════════════════════════

CREATE DATABASE IF NOT EXISTS `kunstlopapp_results`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE `kunstlopapp_results`;

-- Organisations
CREATE TABLE `organisations` (
  `org_code` varchar(10) NOT NULL,
  `name` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`org_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Competitions
CREATE TABLE `competitions` (
  `competition_code` varchar(20) NOT NULL,
  `name` varchar(200) NOT NULL,
  `discipline_code` varchar(10) NOT NULL,
  `logical_date` date NOT NULL,
  `venue_code` varchar(20) DEFAULT NULL,
  `venue_name` varchar(200) DEFAULT NULL,
  `feed_version` smallint unsigned NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`competition_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Events (composite PK)
CREATE TABLE `events` (
  `event_code` varchar(50) NOT NULL,
  `competition_code` varchar(20) NOT NULL,
  `discipline` varchar(10) NOT NULL,
  `gender` char(1) NOT NULL,
  `event_key` varchar(30) NOT NULL,
  `event_type` varchar(10) NOT NULL,
  `sort_order` tinyint unsigned NOT NULL DEFAULT '1',
  `name` varchar(200) NOT NULL,
  `name_short` varchar(100) NOT NULL,
  PRIMARY KEY (`event_code`, `competition_code`),
  KEY `idx_events_competition` (`competition_code`),
  CONSTRAINT `fk_events_competition` FOREIGN KEY (`competition_code`) REFERENCES `competitions` (`competition_code`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Phases (composite PK)
CREATE TABLE `phases` (
  `phase_code` varchar(50) NOT NULL,
  `competition_code` varchar(20) NOT NULL,
  `event_code` varchar(50) NOT NULL,
  `phase_key` varchar(10) NOT NULL,
  `sort_order` tinyint unsigned NOT NULL DEFAULT '1',
  `name` varchar(200) NOT NULL,
  `name_short` varchar(100) NOT NULL,
  PRIMARY KEY (`phase_code`, `competition_code`),
  KEY `idx_phases_event` (`event_code`),
  KEY `fk_phases_competition` (`competition_code`),
  CONSTRAINT `fk_phases_competition` FOREIGN KEY (`competition_code`) REFERENCES `competitions` (`competition_code`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Event Units (composite PK)
CREATE TABLE `event_units` (
  `unit_code` varchar(60) NOT NULL,
  `competition_code` varchar(20) NOT NULL,
  `event_code` varchar(50) NOT NULL,
  `phase_code` varchar(50) NOT NULL,
  `unit_key` varchar(15) NOT NULL,
  `unit_type` varchar(10) NOT NULL,
  `program_abbrev` varchar(5) DEFAULT NULL,
  `is_medal` tinyint(1) NOT NULL DEFAULT '0',
  `is_scheduled` tinyint(1) NOT NULL DEFAULT '1',
  `name` varchar(200) NOT NULL,
  `name_short` varchar(100) NOT NULL,
  PRIMARY KEY (`unit_code`, `competition_code`),
  KEY `idx_eu_event` (`event_code`),
  KEY `idx_eu_phase` (`phase_code`),
  KEY `fk_eu_competition` (`competition_code`),
  CONSTRAINT `fk_eu_competition` FOREIGN KEY (`competition_code`) REFERENCES `competitions` (`competition_code`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Athletes
CREATE TABLE `athletes` (
  `athlete_code` varchar(20) NOT NULL,
  `given_name` varchar(100) NOT NULL,
  `family_name` varchar(100) NOT NULL,
  `gender` char(1) NOT NULL,
  `birth_date` date DEFAULT NULL,
  `home_org_code` varchar(10) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`athlete_code`),
  KEY `idx_athletes_family` (`family_name`),
  KEY `fk_athletes_org` (`home_org_code`),
  CONSTRAINT `fk_athletes_org` FOREIGN KEY (`home_org_code`) REFERENCES `organisations` (`org_code`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Teams
CREATE TABLE `teams` (
  `team_code` varchar(20) NOT NULL,
  `competition_code` varchar(20) NOT NULL,
  `org_code` varchar(10) DEFAULT NULL,
  `name` varchar(200) NOT NULL,
  `name_short` varchar(100) DEFAULT NULL,
  `team_type` varchar(10) DEFAULT NULL,
  `gender` char(1) NOT NULL DEFAULT 'X',
  `status` varchar(20) NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`team_code`, `competition_code`),
  KEY `idx_teams_competition` (`competition_code`),
  CONSTRAINT `fk_teams_competition` FOREIGN KEY (`competition_code`) REFERENCES `competitions` (`competition_code`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Team Members
CREATE TABLE `team_members` (
  `team_code` varchar(20) NOT NULL,
  `competition_code` varchar(20) NOT NULL,
  `athlete_code` varchar(20) NOT NULL,
  `member_order` tinyint unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`team_code`, `competition_code`, `athlete_code`),
  KEY `fk_tm_athlete` (`athlete_code`),
  CONSTRAINT `fk_tm_athlete` FOREIGN KEY (`athlete_code`) REFERENCES `athletes` (`athlete_code`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_tm_team` FOREIGN KEY (`team_code`, `competition_code`) REFERENCES `teams` (`team_code`, `competition_code`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Registrations
CREATE TABLE `registrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `competition_code` varchar(20) NOT NULL,
  `event_code` varchar(50) NOT NULL,
  `athlete_code` varchar(20) DEFAULT NULL,
  `team_code` varchar(20) DEFAULT NULL,
  `org_code` varchar(10) DEFAULT NULL,
  `club_name` varchar(200) DEFAULT NULL,
  `entry_order` smallint unsigned DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_reg_athlete` (`competition_code`, `event_code`, `athlete_code`),
  UNIQUE KEY `uq_reg_team` (`competition_code`, `event_code`, `team_code`),
  KEY `idx_reg_athlete` (`athlete_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Unit Results (composite unique keys)
CREATE TABLE `unit_results` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `unit_code` varchar(60) NOT NULL,
  `competition_code` varchar(20) NOT NULL,
  `event_code` varchar(50) NOT NULL,
  `athlete_code` varchar(20) DEFAULT NULL,
  `team_code` varchar(20) DEFAULT NULL,
  `org_code` varchar(10) DEFAULT NULL,
  `rank` tinyint unsigned DEFAULT NULL,
  `result_total` decimal(7,2) DEFAULT NULL,
  `result_type` varchar(10) NOT NULL DEFAULT 'POINTS',
  `irm_code` varchar(10) DEFAULT NULL,
  `start_order` smallint unsigned DEFAULT NULL,
  `sort_order` smallint unsigned DEFAULT NULL,
  `tes_total` decimal(7,2) DEFAULT NULL,
  `tes_base_total` decimal(7,2) DEFAULT NULL,
  `pcs_total` decimal(7,2) DEFAULT NULL,
  `deduction_total` decimal(7,2) DEFAULT '0.00',
  `planned_elements` json DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `finish_time` time DEFAULT NULL,
  `result_status` varchar(20) NOT NULL DEFAULT 'OFFICIAL',
  `feed_version` smallint unsigned NOT NULL DEFAULT '1',
  `imported_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_unit_athlete` (`unit_code`, `competition_code`, `athlete_code`),
  UNIQUE KEY `uq_unit_team` (`unit_code`, `competition_code`, `team_code`),
  KEY `idx_ur_athlete` (`athlete_code`),
  KEY `idx_ur_team` (`team_code`),
  KEY `idx_ur_competition` (`competition_code`),
  KEY `idx_ur_event` (`event_code`),
  CONSTRAINT `fk_ur_competition` FOREIGN KEY (`competition_code`) REFERENCES `competitions` (`competition_code`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_ur_unit_comp` FOREIGN KEY (`unit_code`, `competition_code`) REFERENCES `event_units` (`unit_code`, `competition_code`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Result Elements
CREATE TABLE `result_elements` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `unit_result_id` int unsigned NOT NULL,
  `unit_code` varchar(60) NOT NULL,
  `athlete_code` varchar(20) DEFAULT NULL,
  `team_code` varchar(20) DEFAULT NULL,
  `competition_code` varchar(20) NOT NULL,
  `element_pos` tinyint unsigned NOT NULL,
  `element_code` varchar(30) NOT NULL,
  `element_desc` varchar(200) DEFAULT NULL,
  `base_value` decimal(6,2) DEFAULT NULL,
  `goe` decimal(5,2) DEFAULT NULL,
  `score` decimal(6,2) NOT NULL,
  `info_flag` varchar(10) DEFAULT NULL,
  `info_flag_2` varchar(10) DEFAULT NULL,
  `judge_scores` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_re_unit_result` (`unit_result_id`),
  CONSTRAINT `fk_re_unit_result` FOREIGN KEY (`unit_result_id`) REFERENCES `unit_results` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Result Components
CREATE TABLE `result_components` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `unit_result_id` int unsigned NOT NULL,
  `unit_code` varchar(60) NOT NULL,
  `athlete_code` varchar(20) DEFAULT NULL,
  `team_code` varchar(20) DEFAULT NULL,
  `competition_code` varchar(20) NOT NULL,
  `component_pos` varchar(5) NOT NULL,
  `factor` decimal(4,2) DEFAULT NULL,
  `raw_score` decimal(5,2) DEFAULT NULL,
  `weighted_score` decimal(7,2) NOT NULL,
  `judge_scores` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_rc_unit_component` (`unit_result_id`, `component_pos`),
  CONSTRAINT `fk_rc_unit_result` FOREIGN KEY (`unit_result_id`) REFERENCES `unit_results` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cumulative Results
CREATE TABLE `cumulative_results` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `competition_code` varchar(20) NOT NULL,
  `event_code` varchar(50) NOT NULL,
  `athlete_code` varchar(20) DEFAULT NULL,
  `team_code` varchar(20) DEFAULT NULL,
  `org_code` varchar(10) DEFAULT NULL,
  `final_rank` tinyint unsigned DEFAULT NULL,
  `result_total` decimal(7,2) DEFAULT NULL,
  `result_type` varchar(10) NOT NULL DEFAULT 'POINTS',
  `irm_code` varchar(10) DEFAULT NULL,
  `sort_order` smallint unsigned DEFAULT NULL,
  `phase_results` json DEFAULT NULL,
  `result_status` varchar(20) NOT NULL DEFAULT 'OFFICIAL',
  `last_unit` varchar(60) DEFAULT NULL,
  `feed_version` smallint unsigned NOT NULL DEFAULT '1',
  `imported_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cr_athlete` (`competition_code`, `event_code`, `athlete_code`),
  UNIQUE KEY `uq_cr_team` (`competition_code`, `event_code`, `team_code`),
  KEY `idx_cr_athlete` (`athlete_code`),
  KEY `idx_cr_team` (`team_code`),
  KEY `idx_cr_event` (`event_code`),
  CONSTRAINT `fk_cr_competition` FOREIGN KEY (`competition_code`) REFERENCES `competitions` (`competition_code`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Import Log
CREATE TABLE `import_log` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `imported_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `competition_code` varchar(20) NOT NULL,
  `event_code` varchar(50) NOT NULL,
  `segment_sp` varchar(200) DEFAULT NULL,
  `segment_fs` varchar(200) DEFAULT NULL,
  `athlete_codes` json DEFAULT NULL,
  `unit_result_ids` json DEFAULT NULL,
  `cumulative_ids` json DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `rolled_back` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_il_comp` (`competition_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
