CREATE DATABASE Taxi;

CREATE USER 'taxi_user'@'localhost' IDENTIFIED BY 'taxi_pass';

grant all privileges on Taxi.* to 'taxi_user'@'localhost' identified by 'taxi_pass';

USE Taxi

CREATE TABLE confirmation_codes (
    id          BIGINT NOT NULL,
    code        BIGINT NOT NULL,
    confirmed   VARCHAR(20),
    created_at  DATETIME NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO confirmation_codes (id, code, confirmed, created_at) VALUES (1921234741, 183923, 'false', NOW());

CREATE TABLE users (
    id          BIGINT NOT NULL,
    phone       CHAR(40) NOT NULL,
    name        VARCHAR(64) NOT NULL,
    surname     VARCHAR(64) NOT NULL,
    language    VARCHAR(64),
    email       VARCHAR(64) NOT NULL,
    password    CHAR(40) NOT NULL,
    confirmed   BIGINT NOT NULL, 
    PRIMARY KEY (id),
    FOREIGN KEY (confirmed) REFERENCES confirmation_codes(id)
 );

INSERT INTO users (id, phone, name, surname, language, email, password, confirmed) VALUES (234j28n4, 3801833293, 'Boris', 'Jhonsonyuk', 'english', 'boris@greatbritain.com', 'Pass123', 1921234741);

CREATE TABLE user_photos (
    id          BIGINT NOT NULL,
    photo       VARCHAR(64),
    owner       BIGINT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (owner) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE bank_cards (
    id          BIGINT NOT NULL,
    number      BIGINT NOT NULL,
    validity    CHAR(40) NOT NULL,
    cvv         CHAR(40) NOT NULL,
    country     CHAR(40) NOT NULL,
    owner       BIGINT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (owner) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE saved_places (
    id      BIGINT NOT NULL,
    address VARCHAR(100) NOT NULL,
    name    VARCHAR(64),
    user    BIGINT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (user) REFERENCES users(id) ON DELETE CASCADE    
);

CREATE TABLE taxi_classes (
    id      BIGINT NOT NULL,
    name    VARCHAR(64) NOT NULL,
    PRIMARY KEY (id)
);
INSERT INTO taxi_classes (id, name) VALUES (120943242, "Standart");
/* Economy, Standard, Comfort, Business */

CREATE TABLE orders (
    id                      BIGINT NOT NULL,
    passenger               BIGINT NOT NULL,
    departure_address       VARCHAR(100) NOT NULL,
    destination_address     VARCHAR(100) NOT NULL,
    date                    DATETIME NOT NULL,
    taxi_class              BIGINT NOT NULL,
    payment_method          VARCHAR(40) NOT NULL,
    status                  VARCHAR(40),
    PRIMARY KEY (id),
    FOREIGN KEY (passenger) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (taxi_class) REFERENCES taxi_classes(id)
);
INSERT INTO orders (id, passenger, departure_address, destination_address, date, taxi_class, payment_method, status) VALUES (198393238, 192127401, "Sadova 3", "Mahistralna 37d", Now(), 120943242, "card", "done");

CREATE TABLE trips (
    id                  BIGINT NOT NULL,
    custom              BIGINT NOT NULL,
    driver              BIGINT NOT NULL,
    cost                INT,
    driver_rating       INT,
    passenger_rating    INT,
    duration            VARCHAR(64),
    distance            VARCHAR(64),
    PRIMARY KEY (id),
    FOREIGN KEY (custom) REFERENCES orders(id),
    FOREIGN KEY (driver) REFERENCES drivers(id)
);
INSERT INTO trips (id, custom, driver, cost, driver_rating, passenger_rating, duration, distance) VALUES (913892837, 198393238, 29839237, 145, 5, 5, 32, 9);

CREATE TABLE cars (
    id              BIGINT NOT NULL,
    plate_numbers   VARCHAR(40) NOT NULL,
    model           VARCHAR(64) NOT NULL,
    taxi_class      BIGINT NOT NULL,
    car_type        VARCHAR(64),
    seats           INT,
    load_capacity   BIGINT,
    baby_chair      INT,
    PRIMARY KEY (id),
    FOREIGN KEY (taxi_class) REFERENCES taxi_classes(id)
);
INSERT INTO cars (id, plate_numbers, model, taxi_class, car_type, seats, load_capacity, baby_chair) VALUES (198327321081, 'ВН 1488 НВ', 'Honda Civic', 120943242, 'Hatchback', 5, 70, 0);

CREATE TABLE drivers (
    id                  BIGINT NOT NULL,
    car                 BIGINT NOT NULL,
    phone               VARCHAR(64),
    name                VARCHAR(64),
    surname             VARCHAR(64),
    driving_category    VARCHAR(64),
    region              BIGINT NOT NULL,
    trips               INT,
    password            VARCHAR(64),
    PRIMARY KEY (id),
    FOREIGN KEY (car) REFERENCES cars(id),
    FOREIGN KEY (region) REFERENCES cities(id)
);
INSERT INTO drivers (id, car, phone, name, surname, driving_category, region, trips, password) VALUES (837498439134, 198327321081, 380981703323, 'Denys', 'Bohdan', 'B', 1892741241, 43, '5dFPufP87YUqMax50q+RZg==');

CREATE TABLE driver_photos (
    id          BIGINT NOT NULL,
    photo       VARCHAR(64),
    owner       BIGINT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (owner) REFERENCES drivers(id) ON DELETE CASCADE
);
INSERT INTO driver_photos (id, photo, owner) VALUES (297539579234, 'photo_837498439134', 837498439134);

CREATE TABLE driver_ratings (
    id      BIGINT NOT NULL,
    driver  BIGINT NOT NULL,
    value   FLOAT DEFAULT 0.0,
    PRIMARY KEY (id),
    FOREIGN KEY (driver) REFERENCES drivers(id) ON DELETE CASCADE
);
INSERT INTO driver_ratings (id, driver, value) VALUES (458227307285, 837498439134, 4.32);

/*
SELECT *
FROM orders, trips
WHERE trips.order = orders.id
    AND  orders.passenger = ?

/*

/*

ENGINE=InnoDB, DEFAULT CHARSET=utf8;

*/