{"Products": {"columns": [[0, "product_id", "INTEGER", 0, null, 1], [1, "product_name", "VARCHAR(20)", 0, null, 0], [2, "product_price", "DECIMAL(19,4)", 0, null, 0], [3, "product_description", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["product_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Addresses": {"columns": [[0, "address_id", "INTEGER", 0, null, 1], [1, "address_details", "VARCHAR(80)", 0, null, 0], [2, "city", "VARCHAR(50)", 0, null, 0], [3, "zip_postcode", "VARCHAR(20)", 0, null, 0], [4, "state_province_county", "VARCHAR(50)", 0, null, 0], [5, "country", "VARCHAR(50)", 0, null, 0]], "primary_keys": ["address_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Customers": {"columns": [[0, "customer_id", "INTEGER", 0, null, 1], [1, "payment_method", "VARCHAR(10)", 1, null, 0], [2, "customer_name", "VARCHAR(80)", 0, null, 0], [3, "customer_phone", "VARCHAR(80)", 0, null, 0], [4, "customer_email", "VARCHAR(80)", 0, null, 0], [5, "date_became_customer", "DATETIME", 0, null, 0]], "primary_keys": ["customer_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Regular_Orders": {"columns": [[0, "regular_order_id", "INTEGER", 0, null, 1], [1, "distributer_id", "INTEGER", 1, null, 0]], "primary_keys": ["regular_order_id"], "foreign_keys": [[0, 0, "Customers", "distributer_id", "customer_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Regular_Order_Products": {"columns": [[0, "regular_order_id", "INTEGER", 1, null, 0], [1, "product_id", "INTEGER", 1, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Regular_Orders", "regular_order_id", "regular_order_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Products", "product_id", "product_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Actual_Orders": {"columns": [[0, "actual_order_id", "INTEGER", 0, null, 1], [1, "order_status_code", "VARCHAR(10)", 1, null, 0], [2, "regular_order_id", "INTEGER", 1, null, 0], [3, "actual_order_date", "DATETIME", 0, null, 0]], "primary_keys": ["actual_order_id"], "foreign_keys": [[0, 0, "Regular_Orders", "regular_order_id", "regular_order_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Actual_Order_Products": {"columns": [[0, "actual_order_id", "INTEGER", 1, null, 0], [1, "product_id", "INTEGER", 1, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Actual_Orders", "actual_order_id", "actual_order_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Products", "product_id", "product_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Customer_Addresses": {"columns": [[0, "customer_id", "INTEGER", 1, null, 0], [1, "address_id", "INTEGER", 1, null, 0], [2, "date_from", "DATETIME", 1, null, 0], [3, "address_type", "VARCHAR(10)", 1, null, 0], [4, "date_to", "DATETIME", 0, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Addresses", "address_id", "address_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Customers", "customer_id", "customer_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Delivery_Routes": {"columns": [[0, "route_id", "INTEGER", 0, null, 1], [1, "route_name", "VARCHAR(50)", 0, null, 0], [2, "other_route_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["route_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Delivery_Route_Locations": {"columns": [[0, "location_code", "VARCHAR(10)", 0, null, 1], [1, "route_id", "INTEGER", 1, null, 0], [2, "location_address_id", "INTEGER", 1, null, 0], [3, "location_name", "VARCHAR(50)", 0, null, 0]], "primary_keys": ["location_code"], "foreign_keys": [[0, 0, "Delivery_Routes", "route_id", "route_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Addresses", "location_address_id", "address_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "location_code"]]], "check_constraints": []}, "Trucks": {"columns": [[0, "truck_id", "INTEGER", 0, null, 1], [1, "truck_licence_number", "VARCHAR(20)", 0, null, 0], [2, "truck_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["truck_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Employees": {"columns": [[0, "employee_id", "INTEGER", 0, null, 1], [1, "employee_address_id", "INTEGER", 1, null, 0], [2, "employee_name", "VARCHAR(80)", 0, null, 0], [3, "employee_phone", "VARCHAR(80)", 0, null, 0]], "primary_keys": ["employee_id"], "foreign_keys": [[0, 0, "Addresses", "employee_address_id", "address_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Order_Deliveries": {"columns": [[0, "location_code", "VARCHAR(10)", 1, null, 0], [1, "actual_order_id", "INTEGER", 1, null, 0], [2, "delivery_status_code", "VARCHAR(10)", 1, null, 0], [3, "driver_employee_id", "INTEGER", 1, null, 0], [4, "truck_id", "INTEGER", 1, null, 0], [5, "delivery_date", "DATETIME", 0, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Employees", "driver_employee_id", "employee_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Delivery_Route_Locations", "location_code", "location_code", "NO ACTION", "NO ACTION", "NONE"], [2, 0, "Actual_Orders", "actual_order_id", "actual_order_id", "NO ACTION", "NO ACTION", "NONE"], [3, 0, "Trucks", "truck_id", "truck_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}}
CREATE TABLE Products (
    product_id INTEGER   PRIMARY KEY,
    product_name VARCHAR(20),
    product_price DECIMAL(19,4),
    product_description VARCHAR(255)
);

CREATE TABLE Addresses (
    address_id INTEGER   PRIMARY KEY,
    address_details VARCHAR(80),
    city VARCHAR(50),
    zip_postcode VARCHAR(20),
    state_province_county VARCHAR(50),
    country VARCHAR(50)
);

CREATE TABLE Customers (
    customer_id INTEGER   PRIMARY KEY,
    payment_method VARCHAR(10) NOT NULL,
    customer_name VARCHAR(80),
    customer_phone VARCHAR(80),
    customer_email VARCHAR(80),
    date_became_customer DATETIME
);

CREATE TABLE Regular_Orders (
    regular_order_id INTEGER   PRIMARY KEY,
    distributer_id INTEGER NOT NULL,
    FOREIGN KEY (distributer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE Regular_Order_Products (
    regular_order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    FOREIGN KEY (regular_order_id) REFERENCES Regular_Orders(regular_order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE Actual_Orders (
    actual_order_id INTEGER   PRIMARY KEY,
    order_status_code VARCHAR(10) NOT NULL,
    regular_order_id INTEGER NOT NULL,
    actual_order_date DATETIME,
    FOREIGN KEY (regular_order_id) REFERENCES Regular_Orders(regular_order_id)
);

CREATE TABLE Actual_Order_Products (
    actual_order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    FOREIGN KEY (actual_order_id) REFERENCES Actual_Orders(actual_order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE Customer_Addresses (
    customer_id INTEGER NOT NULL,
    address_id INTEGER NOT NULL,
    date_from DATETIME NOT NULL,
    address_type VARCHAR(10) NOT NULL,
    date_to DATETIME,
    FOREIGN KEY (address_id) REFERENCES Addresses(address_id),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE Delivery_Routes (
    route_id INTEGER   PRIMARY KEY,
    route_name VARCHAR(50),
    other_route_details VARCHAR(255)
);

CREATE TABLE Delivery_Route_Locations (
    location_code VARCHAR(10)   PRIMARY KEY,
    route_id INTEGER NOT NULL,
    location_address_id INTEGER NOT NULL,
    location_name VARCHAR(50),
    UNIQUE (location_code),
    FOREIGN KEY (route_id) REFERENCES Delivery_Routes(route_id),
    FOREIGN KEY (location_address_id) REFERENCES Addresses(address_id)
);

CREATE TABLE Trucks (
    truck_id INTEGER   PRIMARY KEY,
    truck_licence_number VARCHAR(20),
    truck_details VARCHAR(255)
);

CREATE TABLE Employees (
    employee_id INTEGER   PRIMARY KEY,
    employee_address_id INTEGER NOT NULL,
    employee_name VARCHAR(80),
    employee_phone VARCHAR(80),
    FOREIGN KEY (employee_address_id) REFERENCES Addresses(address_id)
);

CREATE TABLE Order_Deliveries (
    location_code VARCHAR(10) NOT NULL,
    actual_order_id INTEGER NOT NULL,
    delivery_status_code VARCHAR(10) NOT NULL,
    driver_employee_id INTEGER NOT NULL,
    truck_id INTEGER NOT NULL,
    delivery_date DATETIME,
    FOREIGN KEY (driver_employee_id) REFERENCES Employees(employee_id),
    FOREIGN KEY (location_code) REFERENCES Delivery_Route_Locations(location_code),
    FOREIGN KEY (actual_order_id) REFERENCES Actual_Orders(actual_order_id),
    FOREIGN KEY (truck_id) REFERENCES Trucks(truck_id)
);