lemans24.sql

-- MySQL Script generated by MySQL Workbench
-- Sun Jun 16 19:11:06 2024
-- Model: Le Mans 24h    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema lemans24
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema lemans24
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `lemans24` DEFAULT CHARACTER SET utf8mb4 ;
USE `lemans24` ;

-- -----------------------------------------------------
-- Table `lemans24`.`drivers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`drivers` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(16) NULL COMMENT 'Royal, military or academic title',
  `fname` VARCHAR(32) NULL,
  `lname` VARCHAR(32) NOT NULL,
  `nickname` VARCHAR(32) NULL,
  `nm_suffix` VARCHAR(4) NULL COMMENT 'Name suffix (e.g. Jr., Sr., III)',
  `full_name` VARCHAR(64) GENERATED ALWAYS AS (concat((case when ((not((`title` like 'Baron %'))) and (not((`title` like 'Earl %'))) and (not((`title` like 'Lord %')))) then concat(`title`,' ') else '' end),(case when isnull(`fname`) then '' else concat(`fname`,' ') end),(case when isnull(`nickname`) then '' else concat('"',`nickname`,'" ') end),`lname`,(case when ((`title` like 'Baron %') or (`title` like 'Earl %') or (`title` like 'Lord %')) then concat(', ',`title`) else '' end),(case when isnull(`nm_suffix`) then '' else concat(' ',`nm_suffix`) end))) VIRTUAL COMMENT 'Driver full name including title, names, nickname and suffix',
  `sex` ENUM('M', 'F') NULL,
  `born` DATE NULL,
  `country` CHAR(4) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_driver_unq` (`lname` ASC, `sex` ASC, `country` ASC, `fname` ASC, `nickname` ASC, `title` ASC, `nm_suffix` ASC) VISIBLE,
  INDEX `idx_driver_sex` (`sex` ASC) VISIBLE,
  FULLTEXT INDEX `idx_driver_full` (`title`, `fname`, `lname`, `nickname`, `nm_suffix`) VISIBLE)
ENGINE = InnoDB
COMMENT = 'Drivers register';


-- -----------------------------------------------------
-- Table `lemans24`.`circuits`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`circuits` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `since` DATE NOT NULL,
  `length_km` DECIMAL(5,3) NULL,
  `length_mi` DECIMAL(5,3) GENERATED ALWAYS AS (ROUND(length_km * 0.621371192, 3)) VIRTUAL,
  `layout` LONGBLOB NULL,
  `changes` VARCHAR(128) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
COMMENT = 'Circutis register';


-- -----------------------------------------------------
-- Table `lemans24`.`races`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`races` (
  `id` INT NOT NULL,
  `circuit_id` INT NOT NULL,
  `event_date` DATE NOT NULL,
  `start_time` TIME NOT NULL,
  `cancelled` TINYINT NOT NULL,
  `cancellation_reason` VARCHAR(128) NULL,
  `distance_km` DECIMAL(10,3) NULL,
  `distance_mi` DECIMAL(10,3) GENERATED ALWAYS AS (ROUND(distance_km   * 0.621371192, 3)) VIRTUAL,
  `laps` DECIMAL(7,3) NOT NULL COMMENT 'Number of laps',
  `avg_speed_kmh` DECIMAL(5,2) GENERATED ALWAYS AS (ROUND(distance_km   / 24         , 2)),
  `avg_speed_mph` DECIMAL(5,2) GENERATED ALWAYS AS (ROUND(avg_speed_kmh * 0.621371192, 2)) VIRTUAL,
  PRIMARY KEY (`id`),
  INDEX `fk_race_circuit_idx` (`circuit_id` ASC) VISIBLE,
  CONSTRAINT `fk_race_circuit`
    FOREIGN KEY (`circuit_id`)
    REFERENCES `lemans24`.`circuits` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Races register';


-- -----------------------------------------------------
-- Table `lemans24`.`cars`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`cars` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `car_class` VARCHAR(12) NULL,
  `car_chassis` VARCHAR(64) NOT NULL,
  `car_engine` VARCHAR(64) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_car_unq` (`car_class` ASC, `car_chassis` ASC, `car_engine` ASC) VISIBLE)
ENGINE = InnoDB
COMMENT = 'Cars register';


-- -----------------------------------------------------
-- Table `lemans24`.`car_numbers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`car_numbers` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `race_id` INT NOT NULL,
  `car_id` INT NULL,
  `nbr` VARCHAR(3) CHARACTER SET 'ascii' NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_car_nums_race_idx` (`race_id` ASC) VISIBLE,
  INDEX `fk_car_nums_car_idx` (`car_id` ASC) VISIBLE,
  CONSTRAINT `fk_car_nums_race`
    FOREIGN KEY (`race_id`)
    REFERENCES `lemans24`.`races` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_car_nums_car`
    FOREIGN KEY (`car_id`)
    REFERENCES `lemans24`.`cars` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Car number per race';


-- -----------------------------------------------------
-- Table `lemans24`.`results`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`results` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `race_id` INT NOT NULL,
  `car_id` INT NOT NULL,
  `pos` VARCHAR(3) NOT NULL,
  `laps` INT NULL,
  `distance` DECIMAL(8,3) NULL,
  `racing_time` TIME NULL,
  `reason` VARCHAR(128) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_result_race_idx` (`race_id` ASC) VISIBLE,
  INDEX `fk_results_car_idx` (`car_id` ASC) VISIBLE,
  INDEX `idx_results_pos` (`pos` ASC) VISIBLE,
  CONSTRAINT `fk_results_race`
    FOREIGN KEY (`race_id`)
    REFERENCES `lemans24`.`races` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_results_car`
    FOREIGN KEY (`car_id`)
    REFERENCES `lemans24`.`car_numbers` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Driver results per race';


-- -----------------------------------------------------
-- Table `lemans24`.`results_in`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`results_in` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `race_yr` INT NOT NULL COMMENT 'Race year',
  `pos` VARCHAR(3) NOT NULL COMMENT 'Position - either number or NC (not classified), DNF (do not finish) or DSQ (disqualified)',
  `car_class` VARCHAR(12) NOT NULL COMMENT 'Car class (litres or named)',
  `car_nbr` VARCHAR(3) NOT NULL COMMENT 'Car number',
  `team_cntry` VARCHAR(16) NOT NULL COMMENT 'Team country (ISO 3166 3 characters code)',
  `team_name` VARCHAR(128) NOT NULL COMMENT 'Team name',
  `drivers_cntry` VARCHAR(32) NOT NULL COMMENT 'Driver countries (ISO 3166 3 characters code) separated',
  `drivers_name` VARCHAR(256) NOT NULL COMMENT 'Drivers names',
  `car_chassis` VARCHAR(64) NOT NULL COMMENT 'Chassis name',
  `car_engine` VARCHAR(64) NOT NULL COMMENT 'Engine name',
  `car_tyres` VARCHAR(16) NULL COMMENT 'Tyres manufacturer',
  `laps` INT NULL COMMENT 'Laps completed',
  `distance` DECIMAL(8,3) NULL COMMENT 'Distance covered',
  `racing_time` TIME NULL COMMENT 'Racing time',
  `reason` VARCHAR(128) NULL COMMENT 'Reason for NC, DNF, DSQ',
  `processed` TINYINT NOT NULL DEFAULT 0 COMMENT 'Record processed or not',
  PRIMARY KEY (`id`),
  INDEX `idx_processed` (`processed` ASC) VISIBLE)
ENGINE = InnoDB
COMMENT = 'Table for results input';


-- -----------------------------------------------------
-- Table `lemans24`.`teams`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`teams` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(64) NOT NULL,
  `country` CHAR(4) NULL,
  `private_entrant` TINYINT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_team_unq` (`title` ASC, `country` ASC) VISIBLE)
ENGINE = InnoDB
COMMENT = 'Teams register';


-- -----------------------------------------------------
-- Table `lemans24`.`tyres`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`tyres` (
  `id` CHAR(2) NOT NULL,
  `brand` VARCHAR(16) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
COMMENT = 'Tyre brands register';


-- -----------------------------------------------------
-- Table `lemans24`.`car_tyres`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`car_tyres` (
  `car_id` INT NOT NULL,
  `tyre_id` CHAR(2) NOT NULL,
  PRIMARY KEY (`car_id`, `tyre_id`),
  INDEX `fk_car_tyres_tyre_idx` (`tyre_id` ASC) VISIBLE,
  CONSTRAINT `fk_car_tyres_car`
    FOREIGN KEY (`car_id`)
    REFERENCES `lemans24`.`car_numbers` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_car_tyres_tyre`
    FOREIGN KEY (`tyre_id`)
    REFERENCES `lemans24`.`tyres` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `lemans24`.`driver_results`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`driver_results` (
  `driver_id` INT NOT NULL,
  `result_id` INT NOT NULL,
  `ord_num` INT NOT NULL COMMENT 'Order number',
  PRIMARY KEY (`driver_id`, `result_id`),
  INDEX `fk_driver_results_result_idx` (`result_id` ASC) VISIBLE,
  CONSTRAINT `fk_drv_res_driver`
    FOREIGN KEY (`driver_id`)
    REFERENCES `lemans24`.`drivers` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_drv_res_result`
    FOREIGN KEY (`result_id`)
    REFERENCES `lemans24`.`results` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Connect drivers to results';


-- -----------------------------------------------------
-- Table `lemans24`.`team_results`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`team_results` (
  `team_id` INT NOT NULL,
  `result_id` INT NOT NULL,
  `ord_num` INT NOT NULL COMMENT 'Order number',
  PRIMARY KEY (`team_id`, `result_id`),
  INDEX `fk_tm_res_res_idx` (`result_id` ASC) VISIBLE,
  CONSTRAINT `fk_tm_res_team`
    FOREIGN KEY (`team_id`)
    REFERENCES `lemans24`.`teams` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_tm_res_res`
    FOREIGN KEY (`result_id`)
    REFERENCES `lemans24`.`results` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Connect teams to results';

USE `lemans24` ;

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`Circuits`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`Circuits` (`Years` INT, `Length_km` INT, `Length_mi` INT, `Changes` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`Races`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`Races` (`id` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`RaceDistKmRecords`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`RaceDistKmRecords` (`Year` INT, `Distance_km` INT, `Laps` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`RaceLapsRecords`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`RaceLapsRecords` (`Year` INT, `Distance_km` INT, `Laps` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`RaceAvgSpdKmhRecords`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`RaceAvgSpdKmhRecords` (`Year` INT, `Distance_km` INT, `Laps` INT, `AvgSpeed_kmh` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`Results`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`Results` (`Race` INT, `Pos` INT, `Class` INT, `CarNb` INT, `TCountry` INT, `Team` INT, `DCountry` INT, `Drivers` INT, `Chassis` INT, `Engine` INT, `Tyre` INT, `Laps` INT, `Distance` INT, `RacingTime` INT, `Reason` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`Winners`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`Winners` (`id` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`WinningCarNumbers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`WinningCarNumbers` (`Number` INT, `Years` INT, `Percent` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`MostWinsByDriver`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`MostWinsByDriver` (`Driver` INT, `Wins` INT, `FirstWin` INT, `LastWin` INT, `Years` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`MostWinsByTeam`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`MostWinsByTeam` (`Team` INT, `Wins` INT, `FirstWin` INT, `LastWin` INT, `Years` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`MostWinsByTeamWAppr`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`MostWinsByTeamWAppr` (`Team` INT, `Wins` INT, `FirstWin` INT, `LastWin` INT, `Years` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`TotalStartsByDriver`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`TotalStartsByDriver` (`Driver` INT, `DCountry` INT, `Starts` INT, `FirstStart` INT, `LastStart` INT, `Years` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`MostPodiumsByDriver`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`MostPodiumsByDriver` (`Driver` INT, `FstPlace` INT, `SndPlace` INT, `TrdPlace` INT, `TtlPodiums` INT, `FirstPodium` INT, `LastPodium` INT, `Years` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`FemaleDrivers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`FemaleDrivers` (`Name` INT, `Country` INT, `Years` INT, `Starts` INT, `BestFinish` INT, `OvWins` INT, `OvTop10` INT, `OvTop10Years` INT, `ClassWins` INT, `ClassWinYears` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`AllWomenTeams`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`AllWomenTeams` (`Year` INT, `Team` INT, `Drivers` INT, `Car` INT, `Finish` INT, `Inclass` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`FemaleDriversByCntry`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`FemaleDriversByCntry` (`Country` INT, `First` INT, `Drivers` INT, `Starts` INT, `Top10` INT, `Top10Years` INT, `ClassWins` INT, `ClassWinYears` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`DriversWithNickname`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`DriversWithNickname` (`Name` INT, `Sex` INT, `Country` INT, `Years` INT, `Starts` INT, `BestFinish` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`AllDriversResults`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`AllDriversResults` (`Name` INT, `Sex` INT, `Country` INT, `Years` INT, `Starts` INT, `BestFinish` INT, `OvWins` INT, `OvTop10` INT, `OvTop10Years` INT, `ClassWins` INT, `ClassWinYears` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`MostWinsByConstructor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`MostWinsByConstructor` (`Constructor` INT, `Wins` INT, `FirstWin` INT, `LastWin` INT, `Years` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`MostWinsByCar`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`MostWinsByCar` (`Car` INT, `Wins` INT, `FirstWin` INT, `LastWin` INT, `Years` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`WinnersByClass`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`WinnersByClass` (`id` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`RacesByCarsFinished`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`RacesByCarsFinished` (`Year` INT, `Started` INT, `Finished` INT, `FinishedPercent` INT, `Disqualified` INT, `Not_classified` INT, `Retired` INT);

-- -----------------------------------------------------
-- Placeholder table for view `lemans24`.`MostWinsByNation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lemans24`.`MostWinsByNation` (`Nationality` INT, `Wins` INT, `Drivers` INT, `FirstWin` INT, `LastWin` INT, `Years` INT);

-- -----------------------------------------------------
-- function check_position
-- -----------------------------------------------------

DELIMITER //
USE `lemans24`//
CREATE FUNCTION check_position(pos VARCHAR(3))
RETURNS BOOL NO SQL
BEGIN
  /* Either a number */
  IF pos RLIKE '^[1-9][0-9]*$' THEN
    RETURN TRUE;
  /* Ore one of these values
   * DNA - Did Not Attend
   * DNF - Did Not Finish
   * DNP - Did Not Practice
   * DNQ - Did Not Qualify
   * DNS - Did Not Start
   * DSQ - Disqualified
   * NC  - Not classified
   * RES - Reserve
   */
  ELSEIF pos IN ('DNA', 'DNF', 'DNP', 'DNQ', 'DNS', 'DSQ', 'NC', 'RES') THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END//

DELIMITER ;

-- -----------------------------------------------------
-- procedure cleanup
-- -----------------------------------------------------

DELIMITER //
USE `lemans24`//
CREATE PROCEDURE cleanup()
BEGIN
  DELETE FROM driver_results WHERE driver_id > 0;
  ALTER TABLE driver_results AUTO_INCREMENT = 1;

  DELETE FROM team_results WHERE team_id > 0;
  ALTER TABLE team_results AUTO_INCREMENT = 1;

  DELETE FROM results WHERE id > 0;
  ALTER TABLE results AUTO_INCREMENT = 1;

  DELETE FROM car_tyres WHERE car_id > 0;
  ALTER TABLE car_tyres AUTO_INCREMENT = 1;

  DELETE FROM car_numbers WHERE id > 0;
  ALTER TABLE car_numbers AUTO_INCREMENT = 1;

  DELETE FROM cars WHERE id > 0;
  ALTER TABLE cars AUTO_INCREMENT = 1;

  DELETE FROM drivers WHERE id > 0;
  ALTER TABLE drivers AUTO_INCREMENT = 1;

  DELETE FROM teams WHERE id > 0;
  ALTER TABLE teams AUTO_INCREMENT = 1;

  UPDATE results_in
     SET processed = FALSE
   WHERE processed = TRUE;
END//

DELIMITER ;

-- -----------------------------------------------------
-- procedure process_results
-- -----------------------------------------------------

DELIMITER //
USE `lemans24`//
CREATE PROCEDURE process_results()
BEGIN
  /* Input results - table results_in */
  DECLARE res_id            INT;
  DECLARE res_race_yr       INT;
  DECLARE res_pos           VARCHAR(3);
  DECLARE res_car_class     VARCHAR(12);
  DECLARE res_car_nbr       VARCHAR(3);
  DECLARE res_team_cntry    VARCHAR(16);
  DECLARE res_team_name     VARCHAR(128);
  DECLARE res_drivers_cntry VARCHAR(32);
  DECLARE res_drivers_name  VARCHAR(256);
  DECLARE res_car_chassis   VARCHAR(64);
  DECLARE res_car_engine    VARCHAR(64);
  DECLARE res_car_tyres     VARCHAR(16);
  DECLARE res_laps          INT;
  DECLARE res_distance      DECIMAL(8,3);
  DECLARE res_racing_time   TIME(3);
  DECLARE res_reason        VARCHAR(128);

  /* Car data */
  DECLARE new_car_id   INT;
  DECLARE new_carnb_id INT;

  /* Result */
  DECLARE new_res_id INT;

  /* Team data */
  DECLARE new_team_id    INT;
  DECLARE new_team_name  VARCHAR(64);
  DECLARE new_team_cntry CHAR(4);
  DECLARE new_team_priv  TINYINT;
  DECLARE new_team_ord   INT;

  /* Driver data */
  DECLARE new_driver_id  INT;
  DECLARE new_drv_name   VARCHAR(64);
  DECLARE new_drv_title  VARCHAR(16);
  DECLARE new_drv_fname  VARCHAR(32);
  DECLARE new_drv_lname  VARCHAR(32);
  DECLARE new_drv_nick   VARCHAR(32);
  DECLARE new_drv_nm_sfx VARCHAR(4);
  DECLARE new_drv_sex    CHAR(1);
  DECLARE new_drv_cntry  CHAR(4);
  DECLARE new_drv_ord    INT;

  /* Cursor and handling */
  DECLARE done INT DEFAULT FALSE;
  DECLARE cur_results CURSOR FOR
    SELECT id,
           race_yr,
           pos,
           car_class,
           car_nbr,
           team_cntry,
           team_name,
           drivers_cntry,
           drivers_name,
           car_chassis,
           car_engine,
           car_tyres,
           laps,
           distance,
           racing_time,
           reason
      FROM results_in
     WHERE processed IS FALSE;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur_results;

  res_loop: LOOP
    SET new_car_id    = NULL;
    SET new_carnb_id  = NULL;
    SET new_team_id   = NULL;
    SET new_driver_id = NULL;

    FETCH cur_results
     INTO res_id,
          res_race_yr,
          res_pos,
          res_car_class,
          res_car_nbr,
          res_team_cntry,
          res_team_name,
          res_drivers_cntry,
          res_drivers_name,
          res_car_chassis,
          res_car_engine,
          res_car_tyres,
          res_laps,
          res_distance,
          res_racing_time,
          res_reason;

    IF done THEN
      LEAVE res_loop;
    END IF;

    /* Create car */
    IF res_car_class != '' OR res_car_chassis != '' OR res_car_engine != '' THEN
      BEGIN
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET new_car_id = NULL;

        SET res_car_class  = NULLIF(res_car_class, '');
        SET res_car_engine = NULLIF(res_car_engine, '');

        /* Check if car exists */
        SELECT id
          INTO new_car_id
          FROM cars
         WHERE (   (car_class IS NULL AND res_car_class IS NULL)
                OR car_class = res_car_class
               )
           AND car_chassis = res_car_chassis
           AND (   (car_engine IS NULL AND res_car_engine IS NULL)
                OR car_engine  = res_car_engine
               );

        /* else create it */
        IF new_car_id IS NULL THEN
          INSERT INTO cars (car_class, car_chassis, car_engine)
          VALUES (res_car_class, res_car_chassis, res_car_engine);

          SET new_car_id = LAST_INSERT_ID();
        END IF;
      END;
    ELSE /* car not defined */
      SET new_car_id = NULL;
    END IF;

    INSERT INTO car_numbers (race_id, car_id, nbr)
    VALUES (res_race_yr, new_car_id, res_car_nbr);

    SET new_carnb_id = LAST_INSERT_ID();

    /* Car tyres */
    IF res_car_tyres IS NOT NULL THEN
      INSERT INTO car_tyres (car_id, tyre_id)
      SELECT new_carnb_id, id
        FROM tyres
       WHERE brand = res_car_tyres;
    END IF;

    /* Create result */
    INSERT INTO results (race_id, car_id, pos, laps, distance, racing_time, reason)
    VALUES (res_race_yr, new_carnb_id, res_pos, res_laps, res_distance, res_racing_time, res_reason);

    SET new_res_id = LAST_INSERT_ID();

    /* Strip double quotes */
    IF res_team_name LIKE '%"' THEN
      SET res_team_name = REPLACE(SUBSTR(res_team_name, 1, CHAR_LENGTH(res_team_name) - 1), '""', '"');
    END IF;

    /* Process team(s) */
    SET new_team_ord = 0;
    WHILE res_team_name IS NOT NULL DO
      SET new_team_name  = SUBSTRING_INDEX(res_team_name, '|', 1);
      SET new_team_cntry = SUBSTRING_INDEX(res_team_cntry, '|', 1);
      SET new_team_priv = FALSE;
      SET new_team_ord   = new_team_ord + 1;

      /* Detect private entrants */
      IF new_team_name LIKE '% (private entrant)' THEN
        SET new_team_priv = TRUE;
        SET new_team_name = REPLACE(new_team_name, ' (private entrant)', '');
      END IF;

      IF new_team_name != '' THEN
        BEGIN
          DECLARE CONTINUE HANDLER FOR NOT FOUND SET new_team_id = NULL;

          SET new_team_cntry = NULLIF(new_team_cntry, '');

          SELECT id
            INTO new_team_id
            FROM teams
           WHERE title   = new_team_name
             AND (   (country IS NULL AND new_team_cntry IS NULL)
                  OR country = new_team_cntry
                 )
             AND private_entrant = new_team_priv;

          IF new_team_id IS NULL THEN
            INSERT INTO teams (title, country, private_entrant)
            VALUES (new_team_name, new_team_cntry, new_team_priv);

            SET new_team_id = LAST_INSERT_ID();
          END IF;
        END;

        INSERT INTO team_results (team_id, result_id, ord_num)
        VALUES (new_team_id, new_res_id, new_team_ord);
      END IF;

      IF NOT INSTR(res_team_name, '|') THEN
        SET res_team_name  = NULL;
        SET res_team_cntry = NULL;
      ELSE
        SET res_team_name  = SUBSTR(res_team_name , INSTR(res_team_name , '|') + 1);
        SET res_team_cntry = SUBSTR(res_team_cntry, INSTR(res_team_cntry, '|') + 1);
      END IF;
    END WHILE; /* teams */

    /* Strip double quotes */
    IF res_drivers_name LIKE '%"' THEN
      SET res_drivers_name = REPLACE(SUBSTR(res_drivers_name, 1, CHAR_LENGTH(res_drivers_name) - 1), '""', '"');
    END IF;

    /* Process driver(s) */
    SET new_drv_ord = 0;
    WHILE res_drivers_name IS NOT NULL DO
      SET new_drv_title = NULL;
      SET new_drv_name  = SUBSTRING_INDEX(res_drivers_name, '|', 1);
      SET new_drv_nick  = NULL;
      SET new_drv_nm_sfx = NULL;
      SET new_drv_cntry = SUBSTRING_INDEX(res_drivers_cntry, '|', 1);
      SET new_drv_sex   = 'M';
      SET new_drv_ord   = new_drv_ord + 1;

      /* Detect females */
      IF new_drv_name LIKE 'Mme%'  OR
         new_drv_name LIKE 'Miss%' OR
         new_drv_name LIKE 'Mrs.%'
      THEN
        SET new_drv_sex  = 'F';
        SET new_drv_name = SUBSTR(new_drv_name, INSTR(new_drv_name, ' ') + 1);
      END IF;

      /* Detect titles in front of name */
      IF new_drv_name LIKE 'Baron %'   OR /* Royal titles */
         new_drv_name LIKE 'Comte %'   OR /* Comte = Count */
         new_drv_name LIKE 'Count %'   OR
        (new_drv_name LIKE 'Earl %'
         AND new_drv_name <> 'Earl Bamber') OR /* except Earl Bamber */
         new_drv_name LIKE 'Graf %'    OR
         new_drv_name LIKE 'Lord %'    OR
         new_drv_name LIKE 'Marquis %' OR
         new_drv_name LIKE 'Prince %'  OR
         new_drv_name LIKE 'Sir %'     OR
         new_drv_name LIKE 'Capt.%'    OR /* Military titles, Captain */
         new_drv_name LIKE 'Colonel %' OR
         new_drv_name LIKE 'Lt. %'     OR /* Lieutenant */
         new_drv_name LIKE 'Maj %'     OR /* Major */
         new_drv_name LIKE 'Dr.%'         /* Academic titles, Doctor */
      THEN
        IF new_drv_name LIKE 'Lt. Cdr.%' THEN /* Lieutenant commander */
          SET new_drv_title = SUBSTR(new_drv_name, 1, 8);
          SET new_drv_name = SUBSTR(new_drv_name, 10);
        ELSE
          SET new_drv_title = SUBSTR(new_drv_name, 1, INSTR(new_drv_name, ' ') - 1);
          SET new_drv_name = SUBSTR(new_drv_name, INSTR(new_drv_name, ' ') + 1);
	    END IF;
      END IF;

      /* Detect titles after name */
      IF new_drv_name LIKE '%, Baron%' OR
         new_drv_name LIKE '%, Lord%'  OR
         new_drv_name LIKE '%, Earl%'
      THEN
        SET new_drv_title = SUBSTR(new_drv_name, INSTR(new_drv_name, ',') + 2);
        SET new_drv_name = SUBSTR(new_drv_name, 1, INSTR(new_drv_name, ',') - 1);
      END IF;

      /* Detect name suffixes */
      IF new_drv_name LIKE '% Jr.' OR
         new_drv_name LIKE '% Sr.' OR
         new_drv_name LIKE '% III' /* third */
      THEN
        SET new_drv_nm_sfx = SUBSTRING_INDEX(new_drv_name, ' ', -1);
        SET new_drv_name = SUBSTR(new_drv_name, 1, LENGTH(new_drv_name) - 4);
      END IF;

      IF new_drv_name != '' AND new_drv_cntry != '' THEN
      BEGIN
        DECLARE tmp_drv_name   VARCHAR(64) DEFAULT new_drv_name;

        /* Extract nickname */
        IF new_drv_name LIKE '%"%"%' THEN   /* name with two double quotes */
          BEGIN
            DECLARE fst_qt_idx INTEGER DEFAULT INSTR(new_drv_name, '"');
            DECLARE snd_qt_idx INTEGER DEFAULT LOCATE('"', new_drv_name, fst_qt_idx + 1);

            SET tmp_drv_name = SUBSTRING(new_drv_name, 1, fst_qt_idx - 1);
            SET tmp_drv_name = CONCAT(tmp_drv_name, TRIM(SUBSTRING(new_drv_name, snd_qt_idx + 1)));
            SET new_drv_nick = SUBSTRING(new_drv_name, fst_qt_idx + 1, snd_qt_idx - fst_qt_idx - 1);
          END;
        END IF;

        IF new_drv_name LIKE '%''%''%' THEN /* name with two single quotes */
          BEGIN
            DECLARE fst_qt_idx INTEGER DEFAULT INSTR(new_drv_name, '''');
            DECLARE snd_qt_idx INTEGER DEFAULT LOCATE('''', new_drv_name, fst_qt_idx + 1);

            SET tmp_drv_name = SUBSTRING(new_drv_name, 1, fst_qt_idx - 1);
            SET tmp_drv_name = CONCAT(tmp_drv_name, TRIM(SUBSTRING(new_drv_name, snd_qt_idx + 1)));
            SET new_drv_nick = SUBSTRING(new_drv_name, fst_qt_idx + 1, snd_qt_idx - fst_qt_idx - 1);
          END;
        END IF;

        /* clean braces in name and trim */
        SET new_drv_name = REPLACE(tmp_drv_name, '(', '');
        SET new_drv_name = REPLACE(new_drv_name, ')', '');
        SET new_drv_name = TRIM(new_drv_name);

        /* Split driver name - first name to the first space */
        SET new_drv_fname = SUBSTRING_INDEX(new_drv_name, ' ', 1);
        /* last name everything else */
        SET new_drv_lname = SUBSTR(new_drv_name, INSTR(new_drv_name, ' ') + 1);

        IF new_drv_fname = new_drv_lname THEN
          SET new_drv_fname = NULL;
        END IF;

        IF new_drv_fname = 'f.n.u.' OR new_drv_fname = '' THEN /* first name unknown */
          SET new_drv_fname = NULL;
        END IF;

        BEGIN
          DECLARE CONTINUE HANDLER FOR NOT FOUND SET new_driver_id = NULL;

          /* Query shoud be unique or non-unique index lookup on idx_driver_unq */
          SELECT id
            INTO new_driver_id
            FROM drivers
           WHERE (   (title IS NULL AND new_drv_title IS NULL) /* no title */
                  OR title = new_drv_title
                 )
                 /* if first name unknown */
             AND (   (fname IS NULL AND new_drv_fname IS NULL)
                  OR fname = new_drv_fname
                 )
             AND lname   = new_drv_lname
                 /* if no nickname */
             AND (   (nickname IS NULL AND new_drv_nick IS NULL )
                  OR nickname = new_drv_nick
                 )
                 /* if no name suffix */
             AND (   (nm_suffix IS NULL AND new_drv_nm_sfx IS NULL )
                  OR nm_suffix = new_drv_nm_sfx
                 )
             AND sex     = new_drv_sex
             AND country = new_drv_cntry;

          IF new_driver_id IS NULL THEN
            INSERT INTO drivers
              (title,
               fname, lname, nm_suffix, nickname,
               sex, country)
            VALUES
              (new_drv_title,
               new_drv_fname, new_drv_lname, new_drv_nm_sfx, new_drv_nick,
               new_drv_sex, new_drv_cntry);

            SET new_driver_id = LAST_INSERT_ID();
          END IF;
        END;
      END;
      END IF;

      IF new_driver_id IS NOT NULL THEN
        INSERT INTO driver_results (driver_id, result_id, ord_num)
        VALUES (new_driver_id, new_res_id, new_drv_ord);
      END IF;

      IF NOT INSTR(res_drivers_name, '|') THEN
        SET res_drivers_name  = NULL;
        SET res_drivers_cntry = NULL;
      ELSE
        SET res_drivers_name  = SUBSTR(res_drivers_name , INSTR(res_drivers_name , '|') + 1);
        SET res_drivers_cntry = SUBSTR(res_drivers_cntry, INSTR(res_drivers_cntry, '|') + 1);
      END IF;
    END WHILE; /* drivers */

    /* Mark record processed */
    UPDATE results_in
       SET processed = TRUE
     WHERE id = res_id;
  END LOOP; /* res_loop */

  CLOSE cur_results;
END//

DELIMITER ;

-- -----------------------------------------------------
-- function num_to_pos
-- -----------------------------------------------------

DELIMITER //
USE `lemans24`//
CREATE FUNCTION num_to_pos(num INT)
  RETURNS VARCHAR(3)
  DETERMINISTIC
BEGIN
  RETURN CASE 
           WHEN num BETWEEN 1 AND 90
             THEN CAST(num AS CHAR)
           WHEN num = 92 THEN 'NC'
           WHEN num = 93 THEN 'DNF'
           WHEN num = 94 THEN 'DSQ'
           WHEN num = 95 THEN 'DNS'
           WHEN num = 96 THEN 'DNQ'
           WHEN num = 97 THEN 'DNP'
           WHEN num = 98 THEN 'RES'
           WHEN num = 99 THEN 'DNA'
         END;
END//

DELIMITER ;

-- -----------------------------------------------------
-- function pos_to_num
-- -----------------------------------------------------

DELIMITER //
USE `lemans24`//
CREATE FUNCTION pos_to_num(pos VARCHAR(3))
  RETURNS INT
  DETERMINISTIC
BEGIN
  RETURN CASE
           WHEN pos RLIKE '^[1-9][0-9]*$'
             THEN CAST(pos AS UNSIGNED)
           WHEN pos = 'NC'  THEN 92
           WHEN pos = 'DNF' THEN 93
           WHEN pos = 'DSQ' THEN 94
           WHEN pos = 'DNS' THEN 95
           WHEN pos = 'DNQ' THEN 96
           WHEN pos = 'DNP' THEN 97
           WHEN pos = 'RES' THEN 98
           WHEN pos = 'DNA' THEN 99
         END;
END//

DELIMITER ;

-- -----------------------------------------------------
-- function drv_full_name
-- -----------------------------------------------------

DELIMITER //
USE `lemans24`//
CREATE FUNCTION drv_full_name(title     VARCHAR(16),
                              fname     VARCHAR(32),
                              lname     VARCHAR(32),
                              nickname  VARCHAR(32),
                              nm_suffix VARCHAR(4))
  RETURNS VARCHAR(64) NO SQL
BEGIN
  DECLARE full_name VARCHAR(64) DEFAULT '';

  /* first add royal title in front if title with name */
  IF title NOT LIKE 'Baron %' AND
     title NOT LIKE 'Earl %'  AND
     title NOT LIKE 'Lord %'
  THEN
    SET full_name := CONCAT(full_name, title, ' ');
  END IF;

  /* then add first name if known */
  IF fname IS NOT NULL THEN
    SET full_name := CONCAT(full_name, fname, ' ');
  END IF;

  /* then add nickname if any */
  IF nickname IS NOT NULL THEN
    SET full_name := CONCAT(full_name, '"', nickname, '" ');
  END IF;

  /* then add last name */
  SET full_name := CONCAT(full_name, lname);

  /* then add title after name */
  IF title LIKE 'Baron %' OR
     title LIKE 'Earl %'  OR
     title LIKE 'Lord %'
  THEN
    SET full_name := CONCAT(full_name, ', ', title); 
  END IF;

  /* finally add name suffix if any */
  IF nm_suffix IS NOT NULL THEN
    SET full_name := CONCAT(full_name, ' ', nm_suffix);
  END IF;

  RETURN full_name;
END//

DELIMITER ;

-- -----------------------------------------------------
-- function check_car_number
-- -----------------------------------------------------

DELIMITER //
USE `lemans24`//
CREATE FUNCTION check_car_number(car_nbr VARCHAR(3))
RETURNS BOOL NO SQL
BEGIN
  /* Car numbers should be non-zero padded integers */
  IF    car_nbr RLIKE '^[1-9][0-9]{0,2}$'
    /* or 007, 008 and 009 like between 2006 and 2011 */
    OR  car_nbr RLIKE '^00[7-9]$'
    /* or just zero */
    OR  car_nbr RLIKE '^0$'
  THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END//

DELIMITER ;

-- -----------------------------------------------------
-- View `lemans24`.`Circuits`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`Circuits`;
USE `lemans24`;
CREATE  OR REPLACE VIEW Circuits AS
SELECT CASE
         WHEN YEAR(C.since) != (SELECT MIN(YEAR(since))
                                  FROM circuits
                                 WHERE YEAR(since) > YEAR(C.since)
                               ) - 1
         THEN
           CONCAT(YEAR(C.since), '-', (SELECT MIN(YEAR(since))
                                         FROM circuits
                                        WHERE YEAR(since) > YEAR(C.since)
                                      ) - 1
                 )
         ELSE
           YEAR(C.since)
       END         AS Years,
       C.length_km AS Length_km,
       C.length_mi AS Length_mi,
       C.changes   AS `Changes`
  FROM circuits C;

-- -----------------------------------------------------
-- View `lemans24`.`Races`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`Races`;
USE `lemans24`;
CREATE  OR REPLACE VIEW Races AS
SELECT ROW_NUMBER() OVER()  AS `Number`,
       CONCAT(YEAR(R.event_date), ' ', '24 Hours of Le Mans')
                            AS Race,
       CONCAT(DATE_FORMAT(R.event_date, '%Y-%m-%d'), '..', DAY(R.event_date)+1)
                            AS Dates,
       C.length_km          AS CircuitLenght_km,
       R.laps               AS Laps,
       R.distance_km        AS Distance_km,
       R.distance_mi        AS Distance_mi,
       R.avg_speed_kmh      AS AvgSpeed_kmh,
       R.avg_speed_mph      AS AvgSpeed_mih
  FROM races    R,
       circuits C
 WHERE R.cancelled  = FALSE
   AND R.circuit_id = C.id;

-- -----------------------------------------------------
-- View `lemans24`.`RaceDistKmRecords`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`RaceDistKmRecords`;
USE `lemans24`;
CREATE OR REPLACE VIEW RaceDistKmRecords AS
SELECT YEAR(R.event_date) AS `Year`,
       R.distance_km      AS Distance_km,
       R.laps             AS Laps
  FROM races    R
 WHERE R.cancelled = FALSE
   AND (   R.distance_km > (SELECT MAX(distance_km)
                              FROM races
                             WHERE YEAR(event_date) < YEAR(R.event_date)
                           )
        OR R.id = (SELECT MIN(id) FROM races)
       );

-- -----------------------------------------------------
-- View `lemans24`.`RaceLapsRecords`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`RaceLapsRecords`;
USE `lemans24`;
CREATE OR REPLACE VIEW RaceLapsRecords AS
SELECT YEAR(R.event_date) AS `Year`,
       R.distance_km      AS Distance_km,
       R.laps             AS Laps
  FROM races    R
 WHERE R.cancelled = FALSE
   AND (   R.laps > (SELECT MAX(laps)
                       FROM races
                      WHERE YEAR(event_date) < YEAR(R.event_date)
                    )
        OR R.id = (SELECT MIN(id) FROM races)
       );

-- -----------------------------------------------------
-- View `lemans24`.`RaceAvgSpdKmhRecords`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`RaceAvgSpdKmhRecords`;
USE `lemans24`;
CREATE OR REPLACE VIEW RaceAvgSpdKmhRecords AS
SELECT YEAR(R.event_date) AS `Year`,
       R.distance_km      AS Distance_km,
       R.laps             AS Laps,
       R.avg_speed_kmh    AS AvgSpeed_kmh
  FROM races    R
 WHERE R.cancelled = FALSE
   AND (   R.avg_speed_kmh > (SELECT MAX(avg_speed_kmh)
                                FROM races
                               WHERE YEAR(event_date) < YEAR(R.event_date)
                             )
        OR R.id = (SELECT MIN(id) FROM races)
       );

-- -----------------------------------------------------
-- View `lemans24`.`Results`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`Results`;
USE `lemans24`;
CREATE  OR REPLACE VIEW Results AS
SELECT R.id             AS Race,
       RES.pos          AS Pos,
       C.car_class      AS Class,
       CN.nbr           AS CarNb,
       GROUP_CONCAT(TM.country        ORDER BY TMR.ord_num ASC SEPARATOR '|')
                        AS TCountry,
       GROUP_CONCAT(DISTINCT TM.title ORDER BY TMR.ord_num ASC SEPARATOR '|')
                        AS Team,
       GROUP_CONCAT(D.country         ORDER BY DR.ord_num  ASC SEPARATOR '|')
                        AS DCountry,
       GROUP_CONCAT(DISTINCT CONCAT(IFNULL(D.fname, 'f.n.u.'), ' ', D.lname)
                                      ORDER BY DR.ord_num  ASC SEPARATOR '|')
                        AS Drivers,
       C.car_chassis    AS Chassis,
       C.car_engine     AS `Engine`,
       GROUP_CONCAT(DISTINCT T.brand SEPARATOR '|')
                        AS Tyre,
       RES.laps         AS Laps,
       RES.distance     AS Distance,
       RES.racing_time  AS RacingTime,
       RES.reason       AS Reason
  FROM results RES
       INNER JOIN races           R   ON RES.race_id    = R.id
       INNER JOIN car_numbers     CN  ON RES.car_id     = CN.id
                                     AND CN.race_id     = R.id
       LEFT JOIN  cars            C   ON CN.car_id      = C.id
       LEFT JOIN  car_tyres       CT  ON CT.car_id      = CN.id
       LEFT JOIN  tyres           T   ON CT.tyre_id     = T.id
       LEFT JOIN  driver_results  DR  ON DR.result_id   = RES.id
       LEFT JOIN  drivers         D   ON DR.driver_id   = D.id
       LEFT JOIN  team_results    TMR ON TMR.result_id  = RES.id
       LEFT JOIN  teams           TM  ON TMR.team_id    = TM.id
 GROUP BY R.id,
          RES.pos,
          C.car_class,
          CN.nbr,
          C.car_chassis,
          C.car_engine,
          RES.laps,
          RES.distance,
          RES.racing_time,
          RES.reason
 ORDER BY R.id                ASC,
          pos_to_num(RES.pos) ASC,
          RES.distance        DESC;

-- -----------------------------------------------------
-- View `lemans24`.`Winners`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`Winners`;
USE `lemans24`;
CREATE OR REPLACE VIEW Winners AS
SELECT ROW_NUMBER() OVER()  AS `Edition`,
       R.id                 AS `Year`,
       CN.nbr               AS CarNbr,
       GROUP_CONCAT(DISTINCT TM.title ORDER BY TMR.ord_num ASC SEPARATOR ' / ')
                            AS Team,
       GROUP_CONCAT(CONCAT(IFNULL(D.fname, 'f.n.u.'), ' ', D.lname)
                                      ORDER BY DR.ord_num  ASC SEPARATOR ' / ')
                            AS Drivers,
       CONCAT(C.car_chassis, ' / ', C.car_engine)
                            AS Car,
       T.brand              AS Tyres
  FROM races          R,
       results        RES,
       team_results   TMR,
       teams          TM,
       driver_results DR,
       drivers        D,
       car_numbers    CN,
       cars           C,
       car_tyres      CT,
       tyres          T
 WHERE RES.race_id    = R.id
   AND TMR.result_id  = RES.id
   AND TMR.team_id    = TM.id
   AND DR.result_id   = RES.id
   AND DR.driver_id   = D.id
   AND CN.car_id      = C.id
   AND CN.race_id     = R.id
   AND RES.car_id     = CN.id
   AND CT.car_id      = CN.id
   AND CT.tyre_id     = T.id
   AND R.cancelled    = 0 /* not cancelled */
   AND RES.pos        = 1 /* winners */
 GROUP BY R.id
 ORDER BY R.id;

-- -----------------------------------------------------
-- View `lemans24`.`WinningCarNumbers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`WinningCarNumbers`;
USE `lemans24`;
CREATE  OR REPLACE VIEW WinningCarNumbers AS
SELECT CN.nbr                                                     AS `Number`,
       GROUP_CONCAT(R.id ORDER BY R.id SEPARATOR ', ')            AS Years,
       ROUND((COUNT(*) / (SELECT COUNT(*) FROM races)) * 100, 2)  AS Percent
  FROM races R
       INNER JOIN results    RES  ON RES.race_id = R.id
       INNER JOIN car_numbers CN  ON CN.id = RES.car_id AND CN.race_id = R.id
 WHERE RES.pos = 1
GROUP BY CN.nbr
ORDER BY CN.nbr ASC;

-- -----------------------------------------------------
-- View `lemans24`.`MostWinsByDriver`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`MostWinsByDriver`;
USE `lemans24`;
CREATE OR REPLACE VIEW MostWinsByDriver AS
SELECT CONCAT(IFNULL(D.fname, 'f.n.u.'), ' ', D.lname)  AS Driver,
       COUNT(*)                                         AS Wins,
       MIN(R.id)                                        AS FirstWin,
       MAX(R.id)                                        AS LastWin,
       GROUP_CONCAT(R.id ORDER BY R.id SEPARATOR ', ')  AS Years
  FROM races          R,
       results        RES,
       driver_results DR,
       drivers        D
 WHERE RES.race_id  = R.id
   AND DR.result_id = RES.id
   AND DR.driver_id = D.id
   AND RES.pos      = 1
 GROUP BY D.id
 ORDER BY Wins   DESC,
          Years  ASC,
          Driver ASC;

-- -----------------------------------------------------
-- View `lemans24`.`MostWinsByTeam`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`MostWinsByTeam`;
USE `lemans24`;
CREATE OR REPLACE VIEW MostWinsByTeam AS
SELECT TM.title                                         AS Team,
       COUNT(*)                                         AS Wins,
       MIN(R.id)                                        AS FirstWin,
       MAX(R.id)                                        AS LastWin,
       GROUP_CONCAT(R.id ORDER BY R.id SEPARATOR ', ')  AS Years
  FROM races        R,
       results      RES,
       team_results TMR,
       teams        TM
 WHERE RES.race_id   = R.id
   AND TMR.result_id = RES.id
   AND TMR.team_id   = TM.id
   AND RES.pos       = 1
 GROUP BY TM.id
 ORDER BY Wins  DESC,
          Years ASC,
          Team  ASC;

-- -----------------------------------------------------
-- View `lemans24`.`MostWinsByTeamWAppr`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`MostWinsByTeamWAppr`;
USE `lemans24`;
CREATE OR REPLACE VIEW MostWinsByTeamWAppr AS
SELECT CASE /* some approximations */
         WHEN TM.title LIKE '%Joest%'                     THEN 'Joest Racing'
         WHEN TM.title LIKE '%Porsche%'
          AND TM.title NOT LIKE 'Martini Racing Porsche%' THEN 'Porsche'
         WHEN TM.title LIKE '%Ferrari%'                   THEN 'Ferrari'
         WHEN TM.title LIKE '%Jaguar%'                    THEN 'Jaguar'
         WHEN TM.title LIKE '%Peugeot%'                   THEN 'Peugeot'
         ELSE TM.title
       END                                              AS Team,
       COUNT(*)                                         AS Wins,
       MIN(R.id)                                        AS FirstWin,
       MAX(R.id)                                        AS LastWin,
       GROUP_CONCAT(R.id ORDER BY R.id SEPARATOR ', ')  AS Years
  FROM races        R,
       results      RES,
       team_results TMR,
       teams        TM
 WHERE RES.race_id   = R.id
   AND TMR.result_id = RES.id
   AND TMR.team_id   = TM.id
   AND RES.pos       = 1
 GROUP BY Team
 ORDER BY Wins  DESC,
          Years ASC,
          Team  ASC;

-- -----------------------------------------------------
-- View `lemans24`.`TotalStartsByDriver`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`TotalStartsByDriver`;
USE `lemans24`;
CREATE OR REPLACE VIEW TotalStartsByDriver AS
SELECT CONCAT(IFNULL(D.fname, 'f.n.u.'), ' ', D.lname)  AS Driver,
       D.country                                        AS DCountry,
       COUNT(DISTINCT R.id)                             AS `Starts`,
       MIN(R.id)                                        AS FirstStart,
       MAX(R.id)                                        AS LastStart,
       GROUP_CONCAT(DISTINCT R.id
                    ORDER BY R.id SEPARATOR ', ')       AS Years
  FROM races          R,
       results        RES,
       driver_results DR,
       drivers        D
 WHERE RES.race_id  = R.id
   AND DR.result_id = RES.id
   AND DR.driver_id = D.id
   AND RES.pos NOT IN ('DNA', 'DNP', 'DNQ', 'DNS', 'RES')
 GROUP BY D.id
 ORDER BY `Starts`  DESC,
          Years     ASC,
          Driver    ASC;

-- -----------------------------------------------------
-- View `lemans24`.`MostPodiumsByDriver`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`MostPodiumsByDriver`;
USE `lemans24`;
CREATE OR REPLACE VIEW MostPodiumsByDriver AS
SELECT CONCAT(IFNULL(D.fname, 'f.n.u.'), ' ', D.lname)  AS Driver,
       SUM(CASE WHEN RES.pos = 1 THEN 1 ELSE 0 END)     AS FstPlace,
       SUM(CASE WHEN RES.pos = 2 THEN 1 ELSE 0 END)     AS SndPlace,
       SUM(CASE WHEN RES.pos = 3 THEN 1 ELSE 0 END)     AS TrdPlace,
       COUNT(*)                                         AS TtlPodiums,
       MIN(R.id)                                        AS FirstPodium,
       MAX(R.id)                                        AS LastPodium,
       GROUP_CONCAT(CONCAT(R.id, ' (', RES.pos, ')')
                    ORDER BY R.id SEPARATOR ', ')       AS Years
  FROM races          R,
       results        RES,
       driver_results DR,
       drivers        D
 WHERE RES.race_id  = R.id
   AND DR.result_id = RES.id
   AND DR.driver_id = D.id
   AND RES.pos IN (1, 2, 3)
 GROUP BY D.id
 ORDER BY TtlPodiums DESC,
          FstPlace   DESC,
          SndPlace   DESC,
          TrdPlace   DESC,
          Driver     ASC;

-- -----------------------------------------------------
-- View `lemans24`.`FemaleDrivers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`FemaleDrivers`;
USE `lemans24`;
CREATE  OR REPLACE VIEW FemaleDrivers AS
SELECT `Name`, Country, Years, `Starts`, BestFinish,
       OvWins, OvTop10, OvTop10Years,
       ClassWins, ClassWinYears
  FROM AllDriversResults
 WHERE Sex = 'Female';

-- -----------------------------------------------------
-- View `lemans24`.`AllWomenTeams`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`AllWomenTeams`;
USE `lemans24`;
CREATE  OR REPLACE VIEW AllWomenTeams AS
SELECT R.id                                       AS `Year`,
       GROUP_CONCAT(DISTINCT TM.title
                    ORDER BY TR.ord_num
                    SEPARATOR ', ')               AS Team,
       GROUP_CONCAT(CONCAT(D.fname, ' ', D.lname)
                    ORDER BY DR.ord_num
                    SEPARATOR ', ')               AS Drivers,
       C.car_chassis                              AS Car,
       RES.pos                                    AS Finish,
       NULL                                       AS Inclass
  FROM drivers        D,
       driver_results DR,
       races          R,
       results        RES,
       car_numbers    CN,
       cars           C,
       team_results   TR,
       teams          TM
 WHERE DR.driver_id = D.id
   AND DR.result_id = RES.id
   AND RES.race_id  = R.id
   AND RES.car_id   = CN.id
   AND CN.car_id    = C.id
   AND TR.result_id = RES.id
   AND TR.team_id   = TM.id
   AND D.sex        = 'F'
   /* all women => no man in the team */
   AND NOT EXISTS (SELECT 1
                     FROM driver_results IDR,
                          drivers ID
                    WHERE IDR.result_id = RES.id
                      AND IDR.driver_id = ID.id
                      AND ID.sex = 'M'
                  )
   AND RES.pos NOT IN ('DNA', 'DNP', 'DNQ', 'DNS', 'RES')
 GROUP BY R.id, RES.id
 ORDER BY R.id                ASC,
          RES.distance        DESC,
          pos_to_num(RES.pos) ASC;

-- -----------------------------------------------------
-- View `lemans24`.`FemaleDriversByCntry`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`FemaleDriversByCntry`;
USE `lemans24`;
CREATE  OR REPLACE VIEW FemaleDriversByCntry AS
SELECT D.country                                       AS Country,
       MIN(R.id)                                       AS `First`,
       COUNT(DISTINCT D.id)                            AS Drivers,
       COUNT(*)                                        AS `Starts`,
       SUM(CASE WHEN RES.pos BETWEEN 1 AND 10
                THEN 1 ELSE 0 END)                     AS Top10,
       GROUP_CONCAT(DISTINCT
                    CASE WHEN RES.pos BETWEEN 1 AND 10
                         THEN CONCAT(R.id, ' (', RES.pos, ')')
                         ELSE NULL
                    END
                    ORDER BY R.id SEPARATOR ', ')      AS Top10Years,
       SUM(CASE
             WHEN RES.pos =
               (SELECT MIN(pos_to_num(IR.pos)) /* best class position */
                  FROM results     IR,
                       car_numbers ICN,
                       cars        IC
                 WHERE IR.car_id  = ICN.id
                   AND ICN.car_id  = IC.id
                   AND IR.pos NOT IN ('DNA', 'DNF', 'DNP', 'DNQ', 'DNS', 'DSQ', 'NC', 'RES')
                   AND IR.race_id   = R.id
                   AND IC.car_class = C.car_class
               )
             THEN 1
             ELSE 0
           END
          )                                           AS ClassWins,
       GROUP_CONCAT(
           DISTINCT
           CASE
             WHEN RES.pos =
               (SELECT MIN(pos_to_num(IR.pos)) /* best class position */
                  FROM results     IR,
                       car_numbers ICN,
                       cars        IC
                 WHERE IR.car_id  = ICN.id
                   AND ICN.car_id  = IC.id
                   AND IR.pos NOT IN ('DNA', 'DNF', 'DNP', 'DNQ', 'DNS', 'DSQ', 'NC', 'RES')
                   AND IR.race_id   = R.id
                   AND IC.car_class = C.car_class
               )
             THEN CONCAT(R.id, ' (', C.car_class, ')')
             ELSE NULL
           END
           ORDER BY R.id SEPARATOR ', ')              AS ClassWinYears
  FROM drivers        D,
       driver_results DR,
       races          R,
       results        RES,
       car_numbers    CN,
       cars           C
 WHERE DR.driver_id = D.id
   AND DR.result_id = RES.id
   AND RES.race_id  = R.id
   AND RES.car_id   = CN.id
   AND CN.car_id    = C.id
   AND D.sex        = 'F'
   AND RES.pos NOT IN ('DNA', 'DNP', 'DNQ', 'DNS', 'RES')
 GROUP BY D.country
 ORDER BY COUNT(DISTINCT D.id)  DESC,
          `First`               ASC;

-- -----------------------------------------------------
-- View `lemans24`.`DriversWithNickname`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`DriversWithNickname`;
USE `lemans24`;
CREATE  OR REPLACE VIEW DriversWithNickname AS
SELECT CONCAT('"', D.nickname, '"',
              CASE WHEN IFNULL(D.fname, '') = '' AND IFNULL(D.lname, '') = '' THEN ''
              ELSE CONCAT(' (', IFNULL(D.fname, ''), ' ', IFNULL(D.lname, ''), ')')
              END)                                     AS `Name`,
       CASE D.sex
         WHEN 'F' THEN 'Female'
         ELSE 'Male'
       END                                             AS Sex,
       D.country                                       AS Country,
       GROUP_CONCAT(R.id ORDER BY R.id SEPARATOR ', ') AS Years,
       COUNT(*)                                        AS `Starts`,
       num_to_pos(MIN(pos_to_num(RES.pos)))            AS BestFinish
  FROM drivers        D,
       driver_results DR,
       races          R,
       results        RES
 WHERE DR.driver_id = D.id
   AND DR.result_id = RES.id
   AND RES.race_id  = R.id
   AND D.nickname IS NOT NULL
 GROUP BY D.id, D.country
 ORDER BY MIN(R.id) ASC, `Name`;

-- -----------------------------------------------------
-- View `lemans24`.`AllDriversResults`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`AllDriversResults`;
USE `lemans24`;
CREATE OR REPLACE VIEW AllDriversResults AS
SELECT D.full_name                                     AS `Name`,
       CASE D.sex
         WHEN 'F' THEN 'Female'
         ELSE 'Male'
       END                                             AS Sex,
       D.country                                       AS Country,
       GROUP_CONCAT(R.id ORDER BY R.id SEPARATOR ', ') AS Years,
       COUNT(*)                                        AS `Starts`,
       num_to_pos(MIN(pos_to_num(RES.pos)))            AS BestFinish,
       SUM(CASE WHEN RES.pos = 1 THEN 1 ELSE 0 END)    AS OvWins,
       SUM(CASE WHEN RES.pos BETWEEN 1 AND 10
                THEN 1 ELSE 0 END)                     AS OvTop10,
       GROUP_CONCAT(CASE WHEN RES.pos BETWEEN 1 AND 10
                         THEN CONCAT(R.id, ' (', RES.pos, ')')
                         ELSE NULL
                    END
                    ORDER BY R.id SEPARATOR ', ')      AS OvTop10Years,
       /*SUM(CASE
             WHEN RES.pos =
               (SELECT MIN(pos_to_num(IR.pos)) /* best class position *
                  FROM results     IR,
                       car_numbers ICN,
                       cars        IC
                 WHERE IR.car_id  = ICN.id
                   AND ICN.car_id  = IC.id
                   AND IR.pos NOT IN ('DNA', 'DNF', 'DNP', 'DNQ', 'DNS', 'DSQ', 'NC', 'RES')
                   AND IR.race_id   = R.id
                   AND IC.car_class = C.car_class
               )
             THEN 1
             ELSE 0
           END
          )*/ NULL                                     AS ClassWins,
       /*GROUP_CONCAT(
           CASE
             WHEN RES.pos =
               (SELECT MIN(pos_to_num(IR.pos)) /* best class position *
                  FROM results     IR,
                       car_numbers ICN,
                       cars        IC
                 WHERE IR.car_id  = ICN.id
                   AND ICN.car_id  = IC.id
                   AND IR.pos NOT IN ('DNA', 'DNF', 'DNP', 'DNQ', 'DNS', 'DSQ', 'NC', 'RES')
                   AND IR.race_id   = R.id
                   AND IC.car_class = C.car_class
               )
             THEN CONCAT(R.id, ' (', C.car_class, ')')
             ELSE NULL
           END
           ORDER BY R.id SEPARATOR ', ')*/ NULL        AS ClassWinYears
  FROM drivers        D,
       driver_results DR,
       races          R,
       results        RES,
       car_numbers    CN,
       cars           C
 WHERE DR.driver_id = D.id
   AND DR.result_id = RES.id
   AND RES.race_id  = R.id
   AND RES.car_id   = CN.id
   AND CN.car_id    = C.id
   AND CN.race_id   = R.id
   AND RES.pos NOT IN ('DNA', 'DNP', 'DNQ', 'DNS', 'RES')
 GROUP BY D.id, D.country
 ORDER BY `Name`;

-- -----------------------------------------------------
-- View `lemans24`.`MostWinsByConstructor`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`MostWinsByConstructor`;
USE `lemans24`;
CREATE OR REPLACE VIEW MostWinsByConstructor AS
SELECT CASE /* adjust engine constructor names */
         WHEN CAR.car_engine LIKE 'Alfa Romeo%'         THEN 'Alfa Romeo'
         WHEN CAR.car_engine LIKE 'Aston Martin%'       THEN 'Aston Martin'
         WHEN CAR.car_engine LIKE 'Chenard et Walcker%' THEN 'Chenard & Walcker'
         WHEN CAR.car_engine LIKE 'Ford Cosworth%'      THEN 'Ford Cosworth'
         WHEN CAR.car_engine LIKE 'Matra%'              THEN 'Matra-Simca'
         ELSE SUBSTR(CAR.car_engine, 1, INSTR(CAR.car_engine, ' '))
       END                                              AS Constructor,
       COUNT(*)                                         AS Wins,
       MIN(R.id)                                        AS FirstWin,
       MAX(R.id)                                        AS LastWin,
       GROUP_CONCAT(R.id ORDER BY R.id SEPARATOR ', ')  AS Years
  FROM races        R,
       results      RES,
       car_numbers  CN,
       cars         CAR
 WHERE RES.race_id   = R.id
   AND RES.car_id    = CN.id
   AND RES.pos       = 1
   AND CN.race_id    = R.id
   AND CN.car_id     = CAR.id
 GROUP BY Constructor
 ORDER BY Wins        DESC,
          Years       ASC,
          Constructor ASC;

-- -----------------------------------------------------
-- View `lemans24`.`MostWinsByCar`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`MostWinsByCar`;
USE `lemans24`;
CREATE OR REPLACE VIEW MostWinsByCar AS
SELECT CASE /* adjust car names, commented are to match Wikipedia, see
             * https://en.wikipedia.org/wiki/List_of_24_hours_of_Le_Mans_records */
         WHEN CAR.car_chassis LIKE 'Alfa Romeo 8C 2300%'      THEN 'Alfa Romeo 8C 2300'
         WHEN CAR.car_chassis LIKE 'Audi R18%'                THEN 'Audi R18'
       /*WHEN CAR.car_chassis LIKE 'Bentley 3 Litre%'         THEN 'Bentley 3 Litre'*/
         WHEN CAR.car_chassis LIKE 'Bentley Speed Six%'       THEN 'Bentley Speed Six'
         WHEN CAR.car_chassis LIKE 'Bugatti Type 57%'         THEN 'Bugatti Type 57'
         WHEN CAR.car_chassis LIKE 'Ferrari 250 TR%'          THEN 'Ferrari 250 TR'
       /*WHEN CAR.car_chassis LIKE 'Ferrari 275%'             THEN 'Ferrari 275'*/
         WHEN CAR.car_chassis LIKE 'Ford GT40%'               THEN 'Ford GT40'
         WHEN CAR.car_chassis LIKE 'Ford Mk IV'               THEN 'Ford GT40'
         WHEN CAR.car_chassis LIKE 'Lorraine-Dietrich B3-6%'  THEN 'Lorraine-Dietrich B3-6'
         WHEN CAR.car_chassis LIKE 'Matra-Simca MS670%'       THEN 'Matra-Simca MS670'
         WHEN CAR.car_chassis LIKE 'Peugeot 905%'             THEN 'Peugeot 905'
         WHEN CAR.car_chassis LIKE 'Porsche 936%'             THEN 'Porsche 936'
         WHEN CAR.car_chassis LIKE 'Porsche 956%'             THEN 'Porsche 956'
         WHEN CAR.car_chassis LIKE '%Porsche WSC-95%'         THEN 'Porsche WSC-95'
         ELSE CAR.car_chassis
       END                                              AS Car,
       COUNT(*)                                         AS Wins,
       MIN(R.id)                                        AS FirstWin,
       MAX(R.id)                                        AS LastWin,
       GROUP_CONCAT(R.id ORDER BY R.id SEPARATOR ', ')  AS Years
  FROM races        R,
       results      RES,
       car_numbers  CN,
       cars         CAR
 WHERE RES.race_id   = R.id
   AND RES.car_id    = CN.id
   AND RES.pos       = 1
   AND CN.race_id    = R.id
   AND CN.car_id     = CAR.id
 GROUP BY Car
 ORDER BY Wins  DESC,
          Years ASC,
          Car   ASC;

-- -----------------------------------------------------
-- View `lemans24`.`WinnersByClass`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`WinnersByClass`;
USE `lemans24`;
CREATE OR REPLACE VIEW WinnersByClass AS
WITH ClassWinners AS (
SELECT R.id                 AS `Year`,
       DENSE_RANK() OVER (PARTITION BY R.id, C.car_class
                              ORDER BY R.id, pos_to_num(RES.pos) ASC)
                            AS InClass,
       pos_to_num(RES.pos)  AS Overall,
       C.car_class          AS Class,
       CN.nbr               AS CarNbr,
       GROUP_CONCAT(DISTINCT TM.title ORDER BY TMR.ord_num ASC SEPARATOR ' / ')
                            AS Team,
       GROUP_CONCAT(CONCAT(IFNULL(D.fname, 'f.n.u.'), ' ', D.lname)
                                      ORDER BY DR.ord_num  ASC SEPARATOR ' / ')
                            AS Drivers,
       CONCAT(C.car_chassis, ' / ', C.car_engine)
                            AS Car,
       T.brand              AS Tyres
  FROM races          R,
       results        RES,
       team_results   TMR,
       teams          TM,
       driver_results DR,
       drivers        D,
       car_numbers    CN,
       cars           C,
       car_tyres      CT,
       tyres          T
 WHERE RES.race_id    = R.id
   AND TMR.result_id  = RES.id
   AND TMR.team_id    = TM.id
   AND DR.result_id   = RES.id
   AND DR.driver_id   = D.id
   AND CN.car_id      = C.id
   AND CN.race_id     = R.id
   AND RES.car_id     = CN.id
   AND CT.car_id      = CN.id
   AND CT.tyre_id     = T.id
   AND R.cancelled    = 0 /* not cancelled */
   AND RES.pos NOT IN ('DNA', 'DNF', 'DNP', 'DNQ', 'DNS', 'NC', 'RES')
 GROUP BY RES.id
)
SELECT `Year`, Overall, Class, CarNbr, Team, Drivers, Car, Tyres
  FROM ClassWinners
 WHERE InClass = 1
 ORDER BY `Year`, Overall;

-- -----------------------------------------------------
-- View `lemans24`.`RacesByCarsFinished`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`RacesByCarsFinished`;
USE `lemans24`;
CREATE OR REPLACE VIEW RacesByCarsFinished AS
SELECT race_id                                                                       AS `Year`,
       SUM(CASE WHEN pos NOT REGEXP '^(DNA|DNP|DNQ|DNS|RES)$'     THEN 1 ELSE 0 END) AS Started,
       SUM(CASE WHEN pos NOT REGEXP '^(DNA|DNP|DNQ|DNF|DNS|RES)$' THEN 1 ELSE 0 END) AS Finished,
       SUM(CASE WHEN pos NOT REGEXP '^(DNA|DNP|DNQ|DNF|DNS|RES)$' THEN 1 ELSE 0 END) * 100 /
       SUM(CASE WHEN pos NOT REGEXP '^(DNA|DNP|DNQ|DNS|RES)$'     THEN 1 ELSE 0 END) AS FinishedPercent,
       SUM(CASE WHEN pos = 'DSQ'                                  THEN 1 ELSE 0 END) AS Disqualified,
       SUM(CASE WHEN pos = 'NC'                                   THEN 1 ELSE 0 END) AS Not_classified,
       SUM(CASE WHEN pos = 'DNF'                                  THEN 1 ELSE 0 END) AS Retired
  FROM results RES
 WHERE race_id != 1936 /* cancelled */
 GROUP BY race_id
 ORDER BY Finished DESC;

-- -----------------------------------------------------
-- View `lemans24`.`MostWinsByNation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `lemans24`.`MostWinsByNation`;
USE `lemans24`;
CREATE OR REPLACE VIEW MostWinsByNation AS
SELECT D.country                                        AS Nationality,
       COUNT(*)                                         AS Wins,
       COUNT(DISTINCT D.id)                             AS Drivers,
       MIN(R.id)                                        AS FirstWin,
       MAX(R.id)                                        AS LastWin,
       GROUP_CONCAT(R.id ORDER BY R.id SEPARATOR ', ')  AS Years
  FROM races          R,
       results        RES,
       driver_results DR,
       drivers        D
 WHERE RES.race_id  = R.id
   AND DR.result_id = RES.id
   AND DR.driver_id = D.id
   AND RES.pos      = 1
 GROUP BY D.country
 ORDER BY Wins        DESC,
          Years       ASC,
          Nationality ASC;
USE `lemans24`;

DELIMITER //
USE `lemans24`//
CREATE TRIGGER races_calc_laps_bi BEFORE INSERT ON races FOR EACH ROW
BEGIN
  DECLARE cir_length_km DECIMAL(5,3);

  SELECT length_km
    INTO cir_length_km
    FROM circuits
   WHERE id = NEW.circuit_id;

  SET NEW.laps = NEW.distance_km / cir_length_km;
END//

USE `lemans24`//
CREATE TRIGGER races_calc_laps_bu BEFORE UPDATE ON races FOR EACH ROW
BEGIN
  DECLARE cir_length_km DECIMAL(5,3);

  IF NEW.distance_km <> OLD.distance_km THEN
    SELECT length_km
      INTO cir_length_km
      FROM circuits
     WHERE id = NEW.circuit_id;

    SET NEW.laps = NEW.distance_km / cir_length_km;
  END IF;
END//

USE `lemans24`//
CREATE TRIGGER results_checks_bi
BEFORE INSERT ON results
FOR EACH ROW
BEGIN
  IF NOT check_position(NEW.pos) THEN
    SET @errmsg = 'Position sould be number or special value (e.g. NC, DNF, DSQ, etc.)!';
    SIGNAL SQLSTATE '10102' SET MESSAGE_TEXT = @errmsg;
  END IF;
END//

USE `lemans24`//
CREATE TRIGGER results_checks_bu
BEFORE UPDATE ON results
FOR EACH ROW
BEGIN
  IF NEW.pos <> OLD.pos THEN
    IF NOT check_position(NEW.pos) THEN
      SET @errmsg = 'Position sould be number or special value (e.g. NC, DNF, DSQ, etc.)!';
      SIGNAL SQLSTATE '10102' SET MESSAGE_TEXT = @errmsg;
    END IF;
  END IF;
END//

USE `lemans24`//
CREATE TRIGGER results_in_checks_bi
BEFORE INSERT ON results_in
FOR EACH ROW
BEGIN
  IF NEW.race_yr < 1923 THEN
    SET @errmsg = 'Race year should be 1923 or later!';
    SIGNAL SQLSTATE '10101' SET MESSAGE_TEXT = @errmsg;
  END IF;

  IF NOT check_position(NEW.pos) THEN
    SET @errmsg = 'Position sould be number or special value (e.g. NC, DNF, DSQ, etc.)!';
    SIGNAL SQLSTATE '10102' SET MESSAGE_TEXT = @errmsg;
  END IF;

  IF NOT check_car_number(NEW.car_nbr) THEN
    SET @errmsg = 'Car numbers are positive integers (0-999), possibly starting with two zeroes!';
    SIGNAL SQLSTATE '10103' SET MESSAGE_TEXT = @errmsg;
  END IF;

  /* no tyre data -> NULL */
  IF NEW.car_tyres = '' THEN
    SET NEW.car_tyres = NULL;
  END IF;

  /* no laps data -> NULL */
  IF NEW.laps = 0 THEN
    SET NEW.laps = NULL;
  END IF;

  /* no distance data -> NULL */
  IF NEW.distance = 0.0 THEN
    SET NEW.distance = NULL;
  END IF;

  /* no racing time data -> NULL */
  IF NEW.racing_time = '00:00:00' THEN
    SET NEW.racing_time = NULL;
  END IF;

  /* no reason for NC, DNF, DSQ -> NULL */
  IF NEW.reason = '' THEN
    SET NEW.reason = NULL;
  END IF;
END//

USE `lemans24`//
CREATE TRIGGER results_in_checks_bu
BEFORE UPDATE ON results_in
FOR EACH ROW
BEGIN
  IF NEW.race_yr <> OLD.race_yr THEN
    IF NEW.race_yr < 1923 THEN
      SET @errmsg = 'Race year should be 1923 or later!';
      SIGNAL SQLSTATE '10101' SET MESSAGE_TEXT = @errmsg;
    END IF;
  END IF;

  IF NEW.pos <> OLD.pos THEN
    IF NOT check_position(NEW.pos) THEN
      SET @errmsg = 'Position sould be number or special value (e.g. NC, DNF, DSQ, etc.)!';
      SIGNAL SQLSTATE '10102' SET MESSAGE_TEXT = @errmsg;
    END IF;
  END IF;

  IF NEW.car_nbr <> OLD.car_nbr THEN
    IF NOT check_car_number(NEW.car_nbr) THEN
      SET @errmsg = 'Car numbers are positive integers (0-999), possibly starting with two zeroes!';
      SIGNAL SQLSTATE '10103' SET MESSAGE_TEXT = @errmsg;
    END IF;
  END IF;

  IF NEW.car_tyres <> OLD.car_tyres THEN
    /* no tyre data -> NULL */
    IF NEW.car_tyres = '' THEN
      SET NEW.car_tyres = NULL;
    END IF;
  END IF;

  IF NEW.laps <> OLD.laps THEN
    /* no laps data -> NULL */
    IF NEW.laps = 0 THEN
      SET NEW.laps = NULL;
    END IF;
  END IF;

  IF NEW.distance <> OLD.distance THEN
    /* no distance data -> NULL */
    IF NEW.distance = 0.0 THEN
      SET NEW.distance = NULL;
    END IF;
  END IF;

  IF NEW.racing_time <> OLD.racing_time THEN
    /* no racing time data -> NULL */
    IF NEW.racing_time = '00:00:00' THEN
      SET NEW.racing_time = NULL;
    END IF;
  END IF;

  IF NEW.reason <> OLD.reason THEN
    /* no reason for NC, DNF, DSQ -> NULL */
    IF NEW.reason = '' THEN
      SET NEW.reason = NULL;
    END IF;
  END IF;
END//


DELIMITER ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;