-- Created on: 16/01/2023
-- @author: Andoni Aranguren Ubierna
-- Adaptations: 02/2023
@author: Sergio Campos

use urbanite_recommender;

CREATE TABLE `action` (
  `Action_id` int(11) NOT NULL AUTO_INCREMENT,
  `Action_name` varchar(255) DEFAULT NULL,
  `lat` decimal(10,8) NOT NULL,
  `lon` decimal(10,8) NOT NULL,
  `fecha_hora` date DEFAULT NULL,
  `popularity` int(11) DEFAULT '0',
  PRIMARY KEY (`Action_id`),
  UNIQUE KEY `lat` (`lat`,`lon`,`Action_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `chosen_action` (
  `plan_id` int(11) NOT NULL,
  `time_slot` int(11) NOT NULL,
  `Action_id` int(11) NOT NULL,
  `order_position` int(11) DEFAULT NULL,
  PRIMARY KEY (`plan_id`,`time_slot`,`Action_id`),
  UNIQUE KEY `plan_id` (`plan_id`,`time_slot`,`Action_id`),
  KEY `Action_id` (`Action_id`),
  CONSTRAINT `chosen_action_ibfk_1` FOREIGN KEY (`Action_id`) REFERENCES `action` (`Action_id`),
  CONSTRAINT `chosen_action_ibfk_2` FOREIGN KEY (`plan_id`, `time_slot`) REFERENCES `plan_detail` (`plan_id`, `time_slot`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `kpi` (
  `KPI_id` int(11) NOT NULL AUTO_INCREMENT,
  `KPI_name` varchar(50) NOT NULL,
  `popularity` int(11) DEFAULT '0',
  `KPI_level` int(11) DEFAULT NULL,
  `KPI_level_0` varchar(45) DEFAULT NULL,
  `KPI_level_1` varchar(45) DEFAULT NULL,
  `KPI_level_2` varchar(45) DEFAULT NULL,
  `Absolute` tinyint(4) DEFAULT NULL,
  `Relative` tinyint(4) DEFAULT NULL,
  `Geographical` tinyint(4) DEFAULT NULL,
  `Zone_Id` int(11) DEFAULT NULL,
  `Pilot_Id` varchar(45) DEFAULT NULL,
  `Use_case_Id` varchar(6) DEFAULT NULL,
  PRIMARY KEY (`KPI_id`),
  UNIQUE KEY `KPI_name` (`KPI_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `kpi_action` (
  `Action_id` int(11) NOT NULL,
  `kpi_id` int(11) NOT NULL,
  PRIMARY KEY (`Action_id`,`kpi_id`),
  UNIQUE KEY `Action_id` (`Action_id`,`kpi_id`),
  CONSTRAINT `kpi_action_ibfk_1` FOREIGN KEY (`Action_id`) REFERENCES `action` (`Action_id`),
  CONSTRAINT `kpi_action_ibfk_2` FOREIGN KEY (`Action_id`) REFERENCES `kpi` (`KPI_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `plan` (
  `plan_id` int(11) NOT NULL AUTO_INCREMENT,
  `plan_date` datetime DEFAULT NULL,
  PRIMARY KEY (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `plan_detail` (
  `plan_id` int(11) NOT NULL,
  `time_slot` int(11) NOT NULL,
  `fecha_hora` time DEFAULT NULL,
  `arrive_by` tinyint(1) DEFAULT NULL,
  `from_lat` float NOT NULL,
  `from_lon` float NOT NULL,
  `to_lat` float NOT NULL,
  `to_lon` float NOT NULL,
  `bike_safety_index` int(11) DEFAULT NULL,
  `bike_safety_aggregate` int(11) DEFAULT NULL,
  `bikeability_index` int(11) DEFAULT NULL,
  `bikeability_aggregate` int(11) DEFAULT NULL,
  `vehicle_count_per_link` int(11) DEFAULT NULL,
  `vehicle_count` int(11) DEFAULT NULL,
  `capacity_to_moyua` int(11) DEFAULT NULL,
  `average_bus_speedl` int(11) DEFAULT NULL,
  `pedestrial_travel_time` int(11) DEFAULT NULL,
  `link_average_speed` int(11) DEFAULT NULL,
  `line_near_capacity` int(11) DEFAULT NULL,
  `congested_lines` int(11) DEFAULT NULL,
  `public_transport_user` int(11) DEFAULT NULL,
  `bicycle_user` int(11) DEFAULT NULL,
  `emission_total` int(11) DEFAULT NULL,
  `noise_total` int(11) DEFAULT NULL,
  `noise_global` int(11) DEFAULT NULL,
  PRIMARY KEY (`plan_id`,`time_slot`),
  CONSTRAINT `plan_detail_ibfk_1` FOREIGN KEY (`plan_id`) REFERENCES `plan` (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;