CREATE TABLE `drivers` (
  `code` varchar(5) NOT NULL,
  `pin` varchar(4) DEFAULT NULL,
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `doc_number` varchar(12) DEFAULT NULL,
  `enabled` bit(1) NOT NULL,
  `company_id` int(11) NOT NULL,
  `telephone` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`code`),
  UNIQUE KEY `unique_driver_document` (`company_id`,`doc_number`),
  KEY `IDX_companyId_drivers` (`company_id`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

CREATE TABLE `Service` (
  `idService` int(11) NOT NULL,
  `initDateTime` datetime DEFAULT NULL,
  `lineId` int(11) NOT NULL,
  `subLineId` int(11) NOT NULL,
  `originStopId` int(11) NOT NULL,
  `endDateTime` datetime DEFAULT NULL,
  `driverId` int(11) DEFAULT NULL,
  `deviceUID` varchar(11) NOT NULL,
  `reinforcement` bit(1) DEFAULT NULL,
  `direction` enum('ONE_WAY','RETURN','CIRCULAR') DEFAULT NULL,
  PRIMARY KEY (`idService`,`deviceUID`),
  KEY `IDX_init_end_Service` (`initDateTime`,`endDateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

CREATE TABLE `ServiceData` (
  `idService` int(11) NOT NULL,
  `deviceUID` varchar(20) NOT NULL,
  `hardwareId` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`idService`,`deviceUID`),
  CONSTRAINT `FK_SERVICE_SERVICE_DATA` FOREIGN KEY (`idService`, `deviceUID`) REFERENCES `Service` (`idService`, `deviceUID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;