DROP TABLE IF EXISTS `swracetype`;

CREATE TABLE `swracetype` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT '',
  `description` varchar(200) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `swracetype` (`id`, `name`, `description`, `created_at`, `updated_at`)
VALUES
	(1, ' Pursuit', NULL, '2017-10-29 20:07:01', '2017-10-29 20:07:01');

ALTER TABLE `swrace` ADD COLUMN `race_type_id` INT(11) UNSIGNED NOT NULL DEFAULT 1 AFTER `event_id`;

ALTER TABLE `swrace` ADD KEY `fk_race_type` (`race_type_id`);

ALTER TABLE `swrace` ADD CONSTRAINT `fk_race_type` FOREIGN KEY (`race_type_id`) REFERENCES `swracetype` (`id`);


CREATE TABLE `swhandicaptype` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `decsription` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `swhandicaptype` (`id`, `name`, `decsription`, `created_at`, `updated_at`)
VALUES
	(1, 'Plymouth Yardstick', NULL, '2017-10-26 22:42:26', '2017-10-26 22:42:26'),
	(2, 'IRC', NULL, '2017-10-26 22:42:39', '2017-10-26 22:42:39'),
	(3, 'MOCRA', NULL, '2017-10-26 22:43:06', '2017-10-26 22:43:30'),
	(4, 'IMS', NULL, '2017-10-26 22:45:00', '2017-10-26 22:45:00');

RENAME TABLE `swhandicaps` TO `swhandicap`;

ALTER TABLE ADD KEY `fk_hand_type` (`type_id`);
ALTER TABLE `swhandicap` ADD CONSTRAINT `fk_hand_type` FOREIGN KEY (`type_id`) REFERENCES `swhandicaptype` (`id`);

DROP TABLE IF EXISTS `swline`;    

CREATE TABLE `swline` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `race_id` int(11) unsigned NOT NULL,
  `start_line` text NOT NULL,
  `start_color` varchar(9) NOT NULL DEFAULT '',
  `finish_line` text NOT NULL,
  `finish_color` varchar(9) DEFAULT NULL,
  `handicap_id` int(11) unsigned NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_race_line` (`race_id`),
  KEY `fk_hand_line` (`handicap_id`),
  CONSTRAINT `fk_hand_line` FOREIGN KEY (`handicap_id`) REFERENCES `swhandicap` (`id`),
  CONSTRAINT `fk_race_line` FOREIGN KEY (`race_id`) REFERENCES `swrace` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `swplaybackstate` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `swplaybackstate` (`id`, `name`, `created_at`, `updated_at`)
VALUES
	(1, 'PAUSED', '2017-11-01 07:18:26', '2017-11-01 07:18:26'),
	(2, 'STOPPED', '2017-11-01 07:18:37', '2017-11-01 07:18:37'),
	(3, 'PLAYING', '2017-11-01 07:18:43', '2017-11-01 07:18:43');

CREATE TABLE `swplayback` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `state_id` int(11) unsigned NOT NULL,
  `race_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_play_state` (`state_id`),
  KEY `fk_play_race` (`race_id`),
  CONSTRAINT `fk_play_race` FOREIGN KEY (`race_id`) REFERENCES `swrace` (`id`),
  CONSTRAINT `fk_play_state` FOREIGN KEY (`state_id`) REFERENCES `swplaybackstate` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `swboatclassstarttime` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `race_id` int(11) unsigned NOT NULL,
  `boat_class_id` int(11) unsigned NOT NULL,
  `start_time_datetime` datetime NOT NULL,
  `start_time_hhmmss` varchar(8) NOT NULL DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_time_race` (`race_id`),
  KEY `fk_time_class` (`boat_class_id`),
  CONSTRAINT `fk_time_class` FOREIGN KEY (`boat_class_id`) REFERENCES `swclass` (`id`),
  CONSTRAINT `fk_time_race` FOREIGN KEY (`race_id`) REFERENCES `swrace` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=269 DEFAULT CHARSET=utf8;

CREATE TABLE `swraceboatclass` (
  `race_id` int(11) unsigned NOT NULL,
  `boat_class_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`race_id`,`boat_class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
