{"Customers": {"columns": [[0, "customer_id", "INTEGER", 1, null, 1], [1, "customer_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["customer_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Properties": {"columns": [[0, "property_id", "INTEGER", 1, null, 1], [1, "property_type_code", "CHAR(15)", 1, null, 0], [2, "property_address", "VARCHAR(255)", 0, null, 0], [3, "other_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["property_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Residents": {"columns": [[0, "resident_id", "INTEGER", 1, null, 1], [1, "property_id", "INTEGER", 1, null, 2], [2, "date_moved_in", "DATETIME", 1, null, 3], [3, "date_moved_out", "DATETIME", 1, null, 0], [4, "other_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["resident_id", "property_id", "date_moved_in"], "foreign_keys": [[0, 0, "Properties", "property_id", "property_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "resident_id"], [1, 1, "property_id"], [2, 2, "date_moved_in"]]], "check_constraints": []}, "Organizations": {"columns": [[0, "organization_id", "INTEGER", 1, null, 1], [1, "parent_organization_id", "INTEGER", 0, null, 0], [2, "organization_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["organization_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Services": {"columns": [[0, "service_id", "INTEGER", 1, null, 1], [1, "organization_id", "INTEGER", 1, null, 0], [2, "service_type_code", "CHAR(15)", 1, null, 0], [3, "service_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["service_id"], "foreign_keys": [[0, 0, "Organizations", "organization_id", "organization_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Residents_Services": {"columns": [[0, "resident_id", "INTEGER", 1, null, 1], [1, "service_id", "INTEGER", 1, null, 2], [2, "date_moved_in", "DATETIME", 0, null, 0], [3, "property_id", "INTEGER", 0, null, 0], [4, "date_requested", "DATETIME", 0, null, 0], [5, "date_provided", "DATETIME", 0, null, 0], [6, "other_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["resident_id", "service_id"], "foreign_keys": [[0, 0, "Residents", "resident_id", "resident_id", "NO ACTION", "NO ACTION", "NONE"], [0, 1, "Residents", "property_id", "property_id", "NO ACTION", "NO ACTION", "NONE"], [0, 2, "Residents", "date_moved_in", "date_moved_in", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Services", "service_id", "service_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "resident_id"], [1, 1, "service_id"]]], "check_constraints": []}, "Things": {"columns": [[0, "thing_id", "INTEGER", 1, null, 1], [1, "organization_id", "INTEGER", 1, null, 0], [2, "Type_of_Thing_Code", "CHAR(15)", 1, null, 0], [3, "service_type_code", "CHAR(10)", 1, null, 0], [4, "service_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["thing_id"], "foreign_keys": [[0, 0, "Organizations", "organization_id", "organization_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Customer_Events": {"columns": [[0, "Customer_Event_ID", "INTEGER", 1, null, 1], [1, "customer_id", "INTEGER", 0, null, 0], [2, "date_moved_in", "DATETIME", 0, null, 0], [3, "property_id", "INTEGER", 0, null, 0], [4, "resident_id", "INTEGER", 0, null, 0], [5, "thing_id", "INTEGER", 1, null, 0]], "primary_keys": ["Customer_Event_ID"], "foreign_keys": [[0, 0, "Residents", "resident_id", "resident_id", "NO ACTION", "NO ACTION", "NONE"], [0, 1, "Residents", "property_id", "property_id", "NO ACTION", "NO ACTION", "NONE"], [0, 2, "Residents", "date_moved_in", "date_moved_in", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Customers", "customer_id", "customer_id", "NO ACTION", "NO ACTION", "NONE"], [2, 0, "Things", "thing_id", "thing_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Customer_Event_Notes": {"columns": [[0, "Customer_Event_Note_ID", "INTEGER", 1, null, 1], [1, "Customer_Event_ID", "INTEGER", 1, null, 0], [2, "service_type_code", "CHAR(15)", 1, null, 0], [3, "resident_id", "INTEGER", 1, null, 0], [4, "property_id", "INTEGER", 1, null, 0], [5, "date_moved_in", "DATETIME", 1, null, 0]], "primary_keys": ["Customer_Event_Note_ID"], "foreign_keys": [[0, 0, "Customer_Events", "Customer_Event_ID", "Customer_Event_ID", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Timed_Status_of_Things": {"columns": [[0, "thing_id", "INTEGER", 1, null, 1], [1, "Date_and_Date", "DATETIME", 1, null, 2], [2, "Status_of_Thing_Code", "CHAR(15)", 1, null, 3]], "primary_keys": ["thing_id", "Date_and_Date", "Status_of_Thing_Code"], "foreign_keys": [[0, 0, "Things", "thing_id", "thing_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "thing_id"], [1, 1, "Date_and_Date"], [2, 2, "Status_of_Thing_Code"]]], "check_constraints": []}, "Timed_Locations_of_Things": {"columns": [[0, "thing_id", "INTEGER", 1, null, 1], [1, "Date_and_Time", "DATETIME", 1, null, 2], [2, "Location_Code", "CHAR(15)", 1, null, 3]], "primary_keys": ["thing_id", "Date_and_Time", "Location_Code"], "foreign_keys": [[0, 0, "Things", "thing_id", "thing_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "thing_id"], [1, 1, "Date_and_Time"], [2, 2, "Location_Code"]]], "check_constraints": []}}
CREATE TABLE Customers (
    customer_id INTEGER NOT NULL  PRIMARY KEY,
    customer_details VARCHAR(255)
);

CREATE TABLE Properties (
    property_id INTEGER NOT NULL  PRIMARY KEY,
    property_type_code CHAR(15) NOT NULL,
    property_address VARCHAR(255),
    other_details VARCHAR(255)
);

CREATE TABLE Residents (
    resident_id INTEGER NOT NULL  PRIMARY KEY,
    property_id INTEGER NOT NULL  PRIMARY KEY,
    date_moved_in DATETIME NOT NULL  PRIMARY KEY,
    date_moved_out DATETIME NOT NULL,
    other_details VARCHAR(255),
    PRIMARY KEY (resident_id, property_id, date_moved_in),
    UNIQUE (resident_id, property_id, date_moved_in),
    FOREIGN KEY (property_id) REFERENCES Properties(property_id)
);

CREATE TABLE Organizations (
    organization_id INTEGER NOT NULL  PRIMARY KEY,
    parent_organization_id INTEGER,
    organization_details VARCHAR(255)
);

CREATE TABLE Services (
    service_id INTEGER NOT NULL  PRIMARY KEY,
    organization_id INTEGER NOT NULL,
    service_type_code CHAR(15) NOT NULL,
    service_details VARCHAR(255),
    FOREIGN KEY (organization_id) REFERENCES Organizations(organization_id)
);

CREATE TABLE Residents_Services (
    resident_id INTEGER NOT NULL  PRIMARY KEY,
    service_id INTEGER NOT NULL  PRIMARY KEY,
    date_moved_in DATETIME,
    property_id INTEGER,
    date_requested DATETIME,
    date_provided DATETIME,
    other_details VARCHAR(255),
    PRIMARY KEY (resident_id, service_id),
    UNIQUE (resident_id, service_id),
    FOREIGN KEY (resident_id) REFERENCES Residents(resident_id),
    FOREIGN KEY (property_id) REFERENCES Residents(property_id),
    FOREIGN KEY (date_moved_in) REFERENCES Residents(date_moved_in),
    FOREIGN KEY (service_id) REFERENCES Services(service_id)
);

CREATE TABLE Things (
    thing_id INTEGER NOT NULL  PRIMARY KEY,
    organization_id INTEGER NOT NULL,
    Type_of_Thing_Code CHAR(15) NOT NULL,
    service_type_code CHAR(10) NOT NULL,
    service_details VARCHAR(255),
    FOREIGN KEY (organization_id) REFERENCES Organizations(organization_id)
);

CREATE TABLE Customer_Events (
    Customer_Event_ID INTEGER NOT NULL  PRIMARY KEY,
    customer_id INTEGER,
    date_moved_in DATETIME,
    property_id INTEGER,
    resident_id INTEGER,
    thing_id INTEGER NOT NULL,
    FOREIGN KEY (resident_id) REFERENCES Residents(resident_id),
    FOREIGN KEY (property_id) REFERENCES Residents(property_id),
    FOREIGN KEY (date_moved_in) REFERENCES Residents(date_moved_in),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    FOREIGN KEY (thing_id) REFERENCES Things(thing_id)
);

CREATE TABLE Customer_Event_Notes (
    Customer_Event_Note_ID INTEGER NOT NULL  PRIMARY KEY,
    Customer_Event_ID INTEGER NOT NULL,
    service_type_code CHAR(15) NOT NULL,
    resident_id INTEGER NOT NULL,
    property_id INTEGER NOT NULL,
    date_moved_in DATETIME NOT NULL,
    FOREIGN KEY (Customer_Event_ID) REFERENCES Customer_Events(Customer_Event_ID)
);

CREATE TABLE Timed_Status_of_Things (
    thing_id INTEGER NOT NULL  PRIMARY KEY,
    Date_and_Date DATETIME NOT NULL  PRIMARY KEY,
    Status_of_Thing_Code CHAR(15) NOT NULL  PRIMARY KEY,
    PRIMARY KEY (thing_id, Date_and_Date, Status_of_Thing_Code),
    UNIQUE (thing_id, Date_and_Date, Status_of_Thing_Code),
    FOREIGN KEY (thing_id) REFERENCES Things(thing_id)
);

CREATE TABLE Timed_Locations_of_Things (
    thing_id INTEGER NOT NULL  PRIMARY KEY,
    Date_and_Time DATETIME NOT NULL  PRIMARY KEY,
    Location_Code CHAR(15) NOT NULL  PRIMARY KEY,
    PRIMARY KEY (thing_id, Date_and_Time, Location_Code),
    UNIQUE (thing_id, Date_and_Time, Location_Code),
    FOREIGN KEY (thing_id) REFERENCES Things(thing_id)
);