-- 1. Schema
------------------------------------------------------------

CREATE SCHEMA TLOG;
SET CURRENT SCHEMA TLOG;

-- 2. Tables
------------------------------------------------------------

CREATE TABLE TLOG.CUSTOMERS (
    CUSTOMER_ID   VARCHAR(10)  NOT NULL,
    COMPANY_NAME  VARCHAR(100) NOT NULL,
    CITY          VARCHAR(50),
    ZIP_CODE      VARCHAR(10),
    COUNTRY       VARCHAR(50),
    SEGMENT       VARCHAR(20),
    CREDIT_LIMIT  DECIMAL(15,2),
    CREATED_AT    DATE,
    CONSTRAINT PK_CUSTOMERS PRIMARY KEY (CUSTOMER_ID)
);

CREATE TABLE TLOG.DRIVERS (
    DRIVER_ID     VARCHAR(10)  NOT NULL,
    LAST_NAME     VARCHAR(50),
    FIRST_NAME    VARCHAR(50),
    LICENSE_TYPE  VARCHAR(10),
    AVAILABLE     SMALLINT,
    DEPOT         VARCHAR(50),
    CONSTRAINT PK_DRIVERS PRIMARY KEY (DRIVER_ID)
);

CREATE TABLE TLOG.ROUTES (
    ROUTE_REF     VARCHAR(20)  NOT NULL,
    LABEL         VARCHAR(100),
    REGION        VARCHAR(50),
    DRIVER_ID     VARCHAR(10),
    MAX_WEIGHT_KG DECIMAL(10,2),
    LOAD_KG       DECIMAL(10,2),
    DEPART_DATE   DATE,
    STATUS        VARCHAR(20),
    CONSTRAINT PK_ROUTES PRIMARY KEY (ROUTE_REF)
);

CREATE TABLE TLOG.SHIPMENTS (
    SHIPMENT_ID   VARCHAR(15) NOT NULL,
    CUSTOMER_ID   VARCHAR(10),
    ROUTE_REF     VARCHAR(20),
    ORDER_DATE    DATE,
    EXPECTED_DATE DATE,
    DELIVERY_DATE DATE,
    WEIGHT_KG     DECIMAL(10,2),
    AMOUNT        DECIMAL(12,2),
    STATUS        VARCHAR(20),
    DEST_CITY     VARCHAR(50),
    CONSTRAINT PK_SHIPMENTS PRIMARY KEY (SHIPMENT_ID)
);

CREATE TABLE TLOG.INCIDENTS (
    INCIDENT_ID   VARCHAR(15) NOT NULL,
    SHIPMENT_ID   VARCHAR(15),
    INCIDENT_DATE DATE,
    TYPE          VARCHAR(20),
    DESCRIPTION   VARCHAR(255),
    RESOLVED      CHAR(1),
    RESOLVED_AT   DATE,
    CONSTRAINT PK_INCIDENTS PRIMARY KEY (INCIDENT_ID)
);

-- 3. Foreign Keys
------------------------------------------------------------

ALTER TABLE TLOG.SHIPMENTS
ADD CONSTRAINT FK_SHIP_CUSTOMER
FOREIGN KEY (CUSTOMER_ID)
REFERENCES TLOG.CUSTOMERS (CUSTOMER_ID);

ALTER TABLE TLOG.SHIPMENTS
ADD CONSTRAINT FK_SHIP_ROUTE
FOREIGN KEY (ROUTE_REF)
REFERENCES TLOG.ROUTES (ROUTE_REF);

ALTER TABLE TLOG.INCIDENTS
ADD CONSTRAINT FK_INC_SHIPMENT
FOREIGN KEY (SHIPMENT_ID)
REFERENCES TLOG.SHIPMENTS (SHIPMENT_ID);

-- 4. dataset
------------------------------------------------------------

-- CUSTOMERS
INSERT INTO TLOG.CUSTOMERS VALUES
('CL0001','Batifroid SAS','Lyon','69003','FR','PREMIUM',150000.00,'2018-03-12'),
('CL0002','Metal Concept','Grenoble','38000','FR','PREMIUM',200000.00,'2019-07-01'),
('CL0003','Agro Supplies','Clermont','63000','FR','STANDARD',80000.00,'2020-01-15'),
('CL0004','Dispack Westr','Nantes','44000','FR','STANDARD',50000.00,'2021-05-20'),
('CL0005','Veritas Industry','Bordeaux','33000','FR','PREMIUM',180000.00,'2017-08-01'),
('CL0006','Nordbat','Lille','59000','FR','STANDARD',60000.00,'2022-02-14'),
('CL0007','Solis Energy','Marseille','13008','FR','PROSPECT',NULL,NULL),
('CL0008','Cargomix','Strasbourg','67000','FR','PREMIUM',220000.00,'2016-06-30');

-- DRIVERS
INSERT INTO TLOG.DRIVERS VALUES
('DR001','Moreau','Julien','CE',1,'Lyon'),
('DR002','Petit','Sandra','C',1,'Grenoble'),
('DR003','Dupuis','Marc','CE',0,'Lyon'),
('DR004','Renard','Ines','CE',1,'Nantes'),
('DR005','Garnier','Thomas','C',1,'Bordeaux');

-- ROUTES
INSERT INTO TLOG.ROUTES VALUES
('RTE-0101','Rhone-Alpes North','Rhône-Alpes','DR001',18000,15400,'2026-02-24','IN_PROGRESS'),
('RTE-0102','Rhone-Alpes South','Rhône-Alpes','DR002',18000,8200,'2026-02-24','IN_PROGRESS'),
('RTE-0103','Grand West','Pays de la Loire','DR004',20000,19800,'2026-02-24','IN_PROGRESS'),
('RTE-0104','Aquitaine Express','Nouvelle-Aquitaine','DR005',15000,12100,'2026-02-25','PLANNED'),
('RTE-0098','Alsace Return','Grand Est','DR003',18000,18000,'2026-02-20','COMPLETED'),
('RTE-0099','North Express','Hauts-de-France',NULL,18000,0,'2026-02-26','PLANNED');

-- SHIPMENTS
INSERT INTO TLOG.SHIPMENTS VALUES
('SHP-100001','CL0001','RTE-0101','2026-02-20','2026-02-24',NULL,2400,4329.00,'IN_TRANSIT','Lyon'),
('SHP-100002','CL0002','RTE-0101','2026-02-20','2026-02-24',NULL,5100,9189.00,'IN_TRANSIT','Grenoble'),
('SHP-100003','CL0003','RTE-0102','2026-02-21','2026-02-24',NULL,1800,3610.00,'INCIDENT','Clermont'),
('SHP-100004','CL0005','RTE-0102','2026-02-21','2026-02-24',NULL,3200,5769.00,'IN_TRANSIT','Bordeaux'),
('SHP-100005','CL0004','RTE-0103','2026-02-22','2026-02-24',NULL,6800,13610.00,'INCIDENT','Nantes'),
('SHP-100006','CL0008','RTE-0098','2026-02-18','2026-02-20','2026-02-20',4200,7569.00,'DELIVERED','Strasbourg'),
('SHP-100007','CL0001','RTE-0104','2026-02-24','2026-02-25',NULL,1900,3429.00,'PREPARING','Lyon'),
('SHP-100008','CL0006',NULL,'2026-02-23','2026-02-25',NULL,3100,6210.00,'PREPARING','Lille'),
('SHP-100009','CL0002','RTE-0101','2026-02-19','2026-02-22',NULL,2700,4869.00,'INCIDENT','Grenoble'),
('SHP-100010','CL0005',NULL,'2026-02-24','2026-02-27',NULL,4500,8109.00,'PREPARING','Marseille'),
('SHP-100011','CL0008','RTE-0098','2026-02-15','2026-02-20','2026-02-21',5500,9909.00,'DELIVERED','Strasbourg'),
('SHP-100012','CL0003',NULL,'2026-01-10','2026-01-15','2026-01-15',980,1970.00,'DELIVERED','Clermont'),
('SHP-100013','CL0001',NULL,'2026-01-20','2026-01-24','2026-01-24',1400,2529.00,'DELIVERED','Lyon'),
('SHP-100014','CL0002',NULL,'2026-01-05','2026-01-10','2026-01-10',3300,5949.00,'DELIVERED','Grenoble');

-- INCIDENTS
INSERT INTO TLOG.INCIDENTS VALUES
('INC-0001','SHP-100003','2026-02-24','ADDRESS','Delivery address not found, intercom out of order','0',NULL),
('INC-0002','SHP-100005','2026-02-24','DELAY','Traffic jam on A11, estimated delay 4 hours','0',NULL),
('INC-0003','SHP-100009','2026-02-22','DAMAGE','Pallet damaged during loading, customer notified','1','2026-03-02');