{"Customers": {"columns": [[0, "customer_id", "INTEGER", 0, null, 1], [1, "customer_first_name", "VARCHAR(50)", 0, null, 0], [2, "customer_middle_initial", "VARCHAR(1)", 0, null, 0], [3, "customer_last_name", "VARCHAR(50)", 0, null, 0], [4, "gender", "VARCHAR(1)", 0, null, 0], [5, "email_address", "VARCHAR(255)", 0, null, 0], [6, "login_name", "VARCHAR(80)", 0, null, 0], [7, "login_password", "VARCHAR(20)", 0, null, 0], [8, "phone_number", "VARCHAR(255)", 0, null, 0], [9, "town_city", "VARCHAR(50)", 0, null, 0], [10, "state_county_province", "VARCHAR(50)", 0, null, 0], [11, "country", "VARCHAR(50)", 0, null, 0]], "primary_keys": ["customer_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Orders": {"columns": [[0, "order_id", "INTEGER", 0, null, 1], [1, "customer_id", "INTEGER", 1, null, 0], [2, "date_order_placed", "DATETIME", 1, null, 0], [3, "order_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["order_id"], "foreign_keys": [[0, 0, "Customers", "customer_id", "customer_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Invoices": {"columns": [[0, "invoice_number", "INTEGER", 0, null, 1], [1, "order_id", "INTEGER", 1, null, 0], [2, "invoice_date", "DATETIME", 0, null, 0]], "primary_keys": ["invoice_number"], "foreign_keys": [[0, 0, "Orders", "order_id", "order_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Accounts": {"columns": [[0, "account_id", "INTEGER", 0, null, 1], [1, "customer_id", "INTEGER", 1, null, 0], [2, "date_account_opened", "DATETIME", 0, null, 0], [3, "account_name", "VARCHAR(50)", 0, null, 0], [4, "other_account_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["account_id"], "foreign_keys": [[0, 0, "Customers", "customer_id", "customer_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Product_Categories": {"columns": [[0, "production_type_code", "VARCHAR(15)", 0, null, 1], [1, "product_type_description", "VARCHAR(80)", 0, null, 0], [2, "vat_rating", "DECIMAL(19,4)", 0, null, 0]], "primary_keys": ["production_type_code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "production_type_code"]]], "check_constraints": []}, "Products": {"columns": [[0, "product_id", "INTEGER", 0, null, 1], [1, "parent_product_id", "INTEGER", 0, null, 0], [2, "production_type_code", "VARCHAR(15)", 1, null, 0], [3, "unit_price", "DECIMAL(19,4)", 0, null, 0], [4, "product_name", "VARCHAR(80)", 0, null, 0], [5, "product_color", "VARCHAR(20)", 0, null, 0], [6, "product_size", "VARCHAR(20)", 0, null, 0]], "primary_keys": ["product_id"], "foreign_keys": [[0, 0, "Product_Categories", "production_type_code", "production_type_code", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Financial_Transactions": {"columns": [[0, "transaction_id", "INTEGER", 1, null, 0], [1, "account_id", "INTEGER", 1, null, 0], [2, "invoice_number", "INTEGER", 0, null, 0], [3, "transaction_type", "VARCHAR(15)", 1, null, 0], [4, "transaction_date", "DATETIME", 0, null, 0], [5, "transaction_amount", "DECIMAL(19,4)", 0, null, 0], [6, "transaction_comment", "VARCHAR(255)", 0, null, 0], [7, "other_transaction_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Accounts", "account_id", "account_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Invoices", "invoice_number", "invoice_number", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Order_Items": {"columns": [[0, "order_item_id", "INTEGER", 0, null, 1], [1, "order_id", "INTEGER", 1, null, 0], [2, "product_id", "INTEGER", 1, null, 0], [3, "product_quantity", "VARCHAR(50)", 0, null, 0], [4, "other_order_item_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["order_item_id"], "foreign_keys": [[0, 0, "Orders", "order_id", "order_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Products", "product_id", "product_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Invoice_Line_Items": {"columns": [[0, "order_item_id", "INTEGER", 1, null, 0], [1, "invoice_number", "INTEGER", 1, null, 0], [2, "product_id", "INTEGER", 1, null, 0], [3, "product_title", "VARCHAR(80)", 0, null, 0], [4, "product_quantity", "VARCHAR(50)", 0, null, 0], [5, "product_price", "DECIMAL(19,4)", 0, null, 0], [6, "derived_product_cost", "DECIMAL(19,4)", 0, null, 0], [7, "derived_vat_payable", "DECIMAL(19,4)", 0, null, 0], [8, "derived_total_cost", "DECIMAL(19,4)", 0, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Products", "product_id", "product_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Invoices", "invoice_number", "invoice_number", "NO ACTION", "NO ACTION", "NONE"], [2, 0, "Order_Items", "order_item_id", "order_item_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}}
CREATE TABLE Customers (
    customer_id INTEGER   PRIMARY KEY,
    customer_first_name VARCHAR(50),
    customer_middle_initial VARCHAR(1),
    customer_last_name VARCHAR(50),
    gender VARCHAR(1),
    email_address VARCHAR(255),
    login_name VARCHAR(80),
    login_password VARCHAR(20),
    phone_number VARCHAR(255),
    town_city VARCHAR(50),
    state_county_province VARCHAR(50),
    country VARCHAR(50)
);

CREATE TABLE Orders (
    order_id INTEGER   PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    date_order_placed DATETIME NOT NULL,
    order_details VARCHAR(255),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE Invoices (
    invoice_number INTEGER   PRIMARY KEY,
    order_id INTEGER NOT NULL,
    invoice_date DATETIME,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);

CREATE TABLE Accounts (
    account_id INTEGER   PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    date_account_opened DATETIME,
    account_name VARCHAR(50),
    other_account_details VARCHAR(255),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE Product_Categories (
    production_type_code VARCHAR(15)   PRIMARY KEY,
    product_type_description VARCHAR(80),
    vat_rating DECIMAL(19,4),
    UNIQUE (production_type_code)
);

CREATE TABLE Products (
    product_id INTEGER   PRIMARY KEY,
    parent_product_id INTEGER,
    production_type_code VARCHAR(15) NOT NULL,
    unit_price DECIMAL(19,4),
    product_name VARCHAR(80),
    product_color VARCHAR(20),
    product_size VARCHAR(20),
    FOREIGN KEY (production_type_code) REFERENCES Product_Categories(production_type_code)
);

CREATE TABLE Financial_Transactions (
    transaction_id INTEGER NOT NULL,
    account_id INTEGER NOT NULL,
    invoice_number INTEGER,
    transaction_type VARCHAR(15) NOT NULL,
    transaction_date DATETIME,
    transaction_amount DECIMAL(19,4),
    transaction_comment VARCHAR(255),
    other_transaction_details VARCHAR(255),
    FOREIGN KEY (account_id) REFERENCES Accounts(account_id),
    FOREIGN KEY (invoice_number) REFERENCES Invoices(invoice_number)
);

CREATE TABLE Order_Items (
    order_item_id INTEGER   PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    product_quantity VARCHAR(50),
    other_order_item_details VARCHAR(255),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE Invoice_Line_Items (
    order_item_id INTEGER NOT NULL,
    invoice_number INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    product_title VARCHAR(80),
    product_quantity VARCHAR(50),
    product_price DECIMAL(19,4),
    derived_product_cost DECIMAL(19,4),
    derived_vat_payable DECIMAL(19,4),
    derived_total_cost DECIMAL(19,4),
    FOREIGN KEY (product_id) REFERENCES Products(product_id),
    FOREIGN KEY (invoice_number) REFERENCES Invoices(invoice_number),
    FOREIGN KEY (order_item_id) REFERENCES Order_Items(order_item_id)
);