{"Discount_Coupons": {"columns": [[0, "coupon_id", "INTEGER", 0, null, 1], [1, "date_issued", "DATETIME", 0, null, 0], [2, "coupon_amount", "DECIMAL(19,4)", 0, null, 0]], "primary_keys": ["coupon_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Customers": {"columns": [[0, "customer_id", "INTEGER", 0, null, 1], [1, "coupon_id", "INTEGER", 1, null, 0], [2, "good_or_bad_customer", "VARCHAR(4)", 0, null, 0], [3, "first_name", "VARCHAR(80)", 0, null, 0], [4, "last_name", "VARCHAR(80)", 0, null, 0], [5, "gender_mf", "VARCHAR(1)", 0, null, 0], [6, "date_became_customer", "DATETIME", 0, null, 0], [7, "date_last_hire", "DATETIME", 0, null, 0]], "primary_keys": ["customer_id"], "foreign_keys": [[0, 0, "Discount_Coupons", "coupon_id", "coupon_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Bookings": {"columns": [[0, "booking_id", "INTEGER", 0, null, 1], [1, "customer_id", "INTEGER", 1, null, 0], [2, "booking_status_code", "VARCHAR(10)", 1, null, 0], [3, "returned_damaged_yn", "VARCHAR(40)", 0, null, 0], [4, "booking_start_date", "DATETIME", 0, null, 0], [5, "booking_end_date", "DATETIME", 0, null, 0], [6, "count_hired", "VARCHAR(40)", 0, null, 0], [7, "amount_payable", "DECIMAL(19,4)", 0, null, 0], [8, "amount_of_discount", "DECIMAL(19,4)", 0, null, 0], [9, "amount_outstanding", "DECIMAL(19,4)", 0, null, 0], [10, "amount_of_refund", "DECIMAL(19,4)", 0, null, 0]], "primary_keys": ["booking_id"], "foreign_keys": [[0, 0, "Customers", "customer_id", "customer_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Products_for_Hire": {"columns": [[0, "product_id", "INTEGER", 0, null, 1], [1, "product_type_code", "VARCHAR(15)", 1, null, 0], [2, "daily_hire_cost", "DECIMAL(19,4)", 0, null, 0], [3, "product_name", "VARCHAR(80)", 0, null, 0], [4, "product_description", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["product_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Payments": {"columns": [[0, "payment_id", "INTEGER", 0, null, 1], [1, "booking_id", "INTEGER", 0, null, 0], [2, "customer_id", "INTEGER", 1, null, 0], [3, "payment_type_code", "VARCHAR(15)", 1, null, 0], [4, "amount_paid_in_full_yn", "VARCHAR(1)", 0, null, 0], [5, "payment_date", "DATETIME", 0, null, 0], [6, "amount_due", "DECIMAL(19,4)", 0, null, 0], [7, "amount_paid", "DECIMAL(19,4)", 0, null, 0]], "primary_keys": ["payment_id"], "foreign_keys": [[0, 0, "Customers", "customer_id", "customer_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Bookings", "booking_id", "booking_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Products_Booked": {"columns": [[0, "booking_id", "INTEGER", 1, null, 1], [1, "product_id", "INTEGER", 1, null, 2], [2, "returned_yn", "VARCHAR(1)", 0, null, 0], [3, "returned_late_yn", "VARCHAR(1)", 0, null, 0], [4, "booked_count", "INTEGER", 0, null, 0], [5, "booked_amount", "FLOAT", 0, null, 0]], "primary_keys": ["booking_id", "product_id"], "foreign_keys": [[0, 0, "Products_for_Hire", "product_id", "product_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Bookings", "booking_id", "booking_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "booking_id"], [1, 1, "product_id"]]], "check_constraints": []}, "View_Product_Availability": {"columns": [[0, "product_id", "INTEGER", 1, null, 0], [1, "booking_id", "INTEGER", 1, null, 0], [2, "status_date", "DATETIME", 0, null, 1], [3, "available_yn", "VARCHAR(1)", 0, null, 0]], "primary_keys": ["status_date"], "foreign_keys": [[0, 0, "Products_for_Hire", "product_id", "product_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Bookings", "booking_id", "booking_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 2, "status_date"]]], "check_constraints": []}}
CREATE TABLE Discount_Coupons (
    coupon_id INTEGER   PRIMARY KEY,
    date_issued DATETIME,
    coupon_amount DECIMAL(19,4)
);

CREATE TABLE Customers (
    customer_id INTEGER   PRIMARY KEY,
    coupon_id INTEGER NOT NULL,
    good_or_bad_customer VARCHAR(4),
    first_name VARCHAR(80),
    last_name VARCHAR(80),
    gender_mf VARCHAR(1),
    date_became_customer DATETIME,
    date_last_hire DATETIME,
    FOREIGN KEY (coupon_id) REFERENCES Discount_Coupons(coupon_id)
);

CREATE TABLE Bookings (
    booking_id INTEGER   PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    booking_status_code VARCHAR(10) NOT NULL,
    returned_damaged_yn VARCHAR(40),
    booking_start_date DATETIME,
    booking_end_date DATETIME,
    count_hired VARCHAR(40),
    amount_payable DECIMAL(19,4),
    amount_of_discount DECIMAL(19,4),
    amount_outstanding DECIMAL(19,4),
    amount_of_refund DECIMAL(19,4),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE Products_for_Hire (
    product_id INTEGER   PRIMARY KEY,
    product_type_code VARCHAR(15) NOT NULL,
    daily_hire_cost DECIMAL(19,4),
    product_name VARCHAR(80),
    product_description VARCHAR(255)
);

CREATE TABLE Payments (
    payment_id INTEGER   PRIMARY KEY,
    booking_id INTEGER,
    customer_id INTEGER NOT NULL,
    payment_type_code VARCHAR(15) NOT NULL,
    amount_paid_in_full_yn VARCHAR(1),
    payment_date DATETIME,
    amount_due DECIMAL(19,4),
    amount_paid DECIMAL(19,4),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    FOREIGN KEY (booking_id) REFERENCES Bookings(booking_id)
);

CREATE TABLE Products_Booked (
    booking_id INTEGER NOT NULL  PRIMARY KEY,
    product_id INTEGER NOT NULL  PRIMARY KEY,
    returned_yn VARCHAR(1),
    returned_late_yn VARCHAR(1),
    booked_count INTEGER,
    booked_amount FLOAT,
    PRIMARY KEY (booking_id, product_id),
    UNIQUE (booking_id, product_id),
    FOREIGN KEY (product_id) REFERENCES Products_for_Hire(product_id),
    FOREIGN KEY (booking_id) REFERENCES Bookings(booking_id)
);

CREATE TABLE View_Product_Availability (
    product_id INTEGER NOT NULL,
    booking_id INTEGER NOT NULL,
    status_date DATETIME   PRIMARY KEY,
    available_yn VARCHAR(1),
    UNIQUE (status_date),
    FOREIGN KEY (product_id) REFERENCES Products_for_Hire(product_id),
    FOREIGN KEY (booking_id) REFERENCES Bookings(booking_id)
);