{"Third_Party_Companies": {"columns": [[0, "company_id", "INTEGER", 0, null, 1], [1, "company_type", "VARCHAR(5)", 1, null, 0], [2, "company_name", "VARCHAR(255)", 0, null, 0], [3, "company_address", "VARCHAR(255)", 0, null, 0], [4, "other_company_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["company_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Maintenance_Contracts": {"columns": [[0, "maintenance_contract_id", "INTEGER", 0, null, 1], [1, "maintenance_contract_company_id", "INTEGER", 1, null, 0], [2, "contract_start_date", "DATETIME", 0, null, 0], [3, "contract_end_date", "DATETIME", 0, null, 0], [4, "other_contract_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["maintenance_contract_id"], "foreign_keys": [[0, 0, "Third_Party_Companies", "maintenance_contract_company_id", "company_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Parts": {"columns": [[0, "part_id", "INTEGER", 0, null, 1], [1, "part_name", "VARCHAR(255)", 0, null, 0], [2, "chargeable_yn", "VARCHAR(1)", 0, null, 0], [3, "chargeable_amount", "VARCHAR(20)", 0, null, 0], [4, "other_part_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["part_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Skills": {"columns": [[0, "skill_id", "INTEGER", 0, null, 1], [1, "skill_code", "VARCHAR(20)", 0, null, 0], [2, "skill_description", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["skill_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Staff": {"columns": [[0, "staff_id", "INTEGER", 0, null, 1], [1, "staff_name", "VARCHAR(255)", 0, null, 0], [2, "gender", "VARCHAR(1)", 0, null, 0], [3, "other_staff_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["staff_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Assets": {"columns": [[0, "asset_id", "INTEGER", 0, null, 1], [1, "maintenance_contract_id", "INTEGER", 1, null, 0], [2, "supplier_company_id", "INTEGER", 1, null, 0], [3, "asset_details", "VARCHAR(255)", 0, null, 0], [4, "asset_make", "VARCHAR(20)", 0, null, 0], [5, "asset_model", "VARCHAR(20)", 0, null, 0], [6, "asset_acquired_date", "DATETIME", 0, null, 0], [7, "asset_disposed_date", "DATETIME", 0, null, 0], [8, "other_asset_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["asset_id"], "foreign_keys": [[0, 0, "Third_Party_Companies", "supplier_company_id", "company_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Maintenance_Contracts", "maintenance_contract_id", "maintenance_contract_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Asset_Parts": {"columns": [[0, "asset_id", "INTEGER", 1, null, 0], [1, "part_id", "INTEGER", 1, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Assets", "asset_id", "asset_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Parts", "part_id", "part_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Maintenance_Engineers": {"columns": [[0, "engineer_id", "INTEGER", 0, null, 1], [1, "company_id", "INTEGER", 1, null, 0], [2, "first_name", "VARCHAR(50)", 0, null, 0], [3, "last_name", "VARCHAR(50)", 0, null, 0], [4, "other_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["engineer_id"], "foreign_keys": [[0, 0, "Third_Party_Companies", "company_id", "company_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Engineer_Skills": {"columns": [[0, "engineer_id", "INTEGER", 1, null, 0], [1, "skill_id", "INTEGER", 1, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Skills", "skill_id", "skill_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Maintenance_Engineers", "engineer_id", "engineer_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Fault_Log": {"columns": [[0, "fault_log_entry_id", "INTEGER", 0, null, 1], [1, "asset_id", "INTEGER", 1, null, 0], [2, "recorded_by_staff_id", "INTEGER", 1, null, 0], [3, "fault_log_entry_datetime", "DATETIME", 0, null, 0], [4, "fault_description", "VARCHAR(255)", 0, null, 0], [5, "other_fault_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["fault_log_entry_id"], "foreign_keys": [[0, 0, "Staff", "recorded_by_staff_id", "staff_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Assets", "asset_id", "asset_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Engineer_Visits": {"columns": [[0, "engineer_visit_id", "INTEGER", 0, null, 1], [1, "contact_staff_id", "INTEGER", 0, null, 0], [2, "engineer_id", "INTEGER", 1, null, 0], [3, "fault_log_entry_id", "INTEGER", 1, null, 0], [4, "fault_status", "VARCHAR(10)", 1, null, 0], [5, "visit_start_datetime", "DATETIME", 0, null, 0], [6, "visit_end_datetime", "DATETIME", 0, null, 0], [7, "other_visit_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["engineer_visit_id"], "foreign_keys": [[0, 0, "Staff", "contact_staff_id", "staff_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Maintenance_Engineers", "engineer_id", "engineer_id", "NO ACTION", "NO ACTION", "NONE"], [2, 0, "Fault_Log", "fault_log_entry_id", "fault_log_entry_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Part_Faults": {"columns": [[0, "part_fault_id", "INTEGER", 0, null, 1], [1, "part_id", "INTEGER", 1, null, 0], [2, "fault_short_name", "VARCHAR(20)", 0, null, 0], [3, "fault_description", "VARCHAR(255)", 0, null, 0], [4, "other_fault_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["part_fault_id"], "foreign_keys": [[0, 0, "Parts", "part_id", "part_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Fault_Log_Parts": {"columns": [[0, "fault_log_entry_id", "INTEGER", 1, null, 0], [1, "part_fault_id", "INTEGER", 1, null, 0], [2, "fault_status", "VARCHAR(10)", 1, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Fault_Log", "fault_log_entry_id", "fault_log_entry_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Part_Faults", "part_fault_id", "part_fault_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Skills_Required_To_Fix": {"columns": [[0, "part_fault_id", "INTEGER", 1, null, 0], [1, "skill_id", "INTEGER", 1, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Skills", "skill_id", "skill_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Part_Faults", "part_fault_id", "part_fault_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}}
CREATE TABLE Third_Party_Companies (
    company_id INTEGER   PRIMARY KEY,
    company_type VARCHAR(5) NOT NULL,
    company_name VARCHAR(255),
    company_address VARCHAR(255),
    other_company_details VARCHAR(255)
);

CREATE TABLE Maintenance_Contracts (
    maintenance_contract_id INTEGER   PRIMARY KEY,
    maintenance_contract_company_id INTEGER NOT NULL,
    contract_start_date DATETIME,
    contract_end_date DATETIME,
    other_contract_details VARCHAR(255),
    FOREIGN KEY (maintenance_contract_company_id) REFERENCES Third_Party_Companies(company_id)
);

CREATE TABLE Parts (
    part_id INTEGER   PRIMARY KEY,
    part_name VARCHAR(255),
    chargeable_yn VARCHAR(1),
    chargeable_amount VARCHAR(20),
    other_part_details VARCHAR(255)
);

CREATE TABLE Skills (
    skill_id INTEGER   PRIMARY KEY,
    skill_code VARCHAR(20),
    skill_description VARCHAR(255)
);

CREATE TABLE Staff (
    staff_id INTEGER   PRIMARY KEY,
    staff_name VARCHAR(255),
    gender VARCHAR(1),
    other_staff_details VARCHAR(255)
);

CREATE TABLE Assets (
    asset_id INTEGER   PRIMARY KEY,
    maintenance_contract_id INTEGER NOT NULL,
    supplier_company_id INTEGER NOT NULL,
    asset_details VARCHAR(255),
    asset_make VARCHAR(20),
    asset_model VARCHAR(20),
    asset_acquired_date DATETIME,
    asset_disposed_date DATETIME,
    other_asset_details VARCHAR(255),
    FOREIGN KEY (supplier_company_id) REFERENCES Third_Party_Companies(company_id),
    FOREIGN KEY (maintenance_contract_id) REFERENCES Maintenance_Contracts(maintenance_contract_id)
);

CREATE TABLE Asset_Parts (
    asset_id INTEGER NOT NULL,
    part_id INTEGER NOT NULL,
    FOREIGN KEY (asset_id) REFERENCES Assets(asset_id),
    FOREIGN KEY (part_id) REFERENCES Parts(part_id)
);

CREATE TABLE Maintenance_Engineers (
    engineer_id INTEGER   PRIMARY KEY,
    company_id INTEGER NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    other_details VARCHAR(255),
    FOREIGN KEY (company_id) REFERENCES Third_Party_Companies(company_id)
);

CREATE TABLE Engineer_Skills (
    engineer_id INTEGER NOT NULL,
    skill_id INTEGER NOT NULL,
    FOREIGN KEY (skill_id) REFERENCES Skills(skill_id),
    FOREIGN KEY (engineer_id) REFERENCES Maintenance_Engineers(engineer_id)
);

CREATE TABLE Fault_Log (
    fault_log_entry_id INTEGER   PRIMARY KEY,
    asset_id INTEGER NOT NULL,
    recorded_by_staff_id INTEGER NOT NULL,
    fault_log_entry_datetime DATETIME,
    fault_description VARCHAR(255),
    other_fault_details VARCHAR(255),
    FOREIGN KEY (recorded_by_staff_id) REFERENCES Staff(staff_id),
    FOREIGN KEY (asset_id) REFERENCES Assets(asset_id)
);

CREATE TABLE Engineer_Visits (
    engineer_visit_id INTEGER   PRIMARY KEY,
    contact_staff_id INTEGER,
    engineer_id INTEGER NOT NULL,
    fault_log_entry_id INTEGER NOT NULL,
    fault_status VARCHAR(10) NOT NULL,
    visit_start_datetime DATETIME,
    visit_end_datetime DATETIME,
    other_visit_details VARCHAR(255),
    FOREIGN KEY (contact_staff_id) REFERENCES Staff(staff_id),
    FOREIGN KEY (engineer_id) REFERENCES Maintenance_Engineers(engineer_id),
    FOREIGN KEY (fault_log_entry_id) REFERENCES Fault_Log(fault_log_entry_id)
);

CREATE TABLE Part_Faults (
    part_fault_id INTEGER   PRIMARY KEY,
    part_id INTEGER NOT NULL,
    fault_short_name VARCHAR(20),
    fault_description VARCHAR(255),
    other_fault_details VARCHAR(255),
    FOREIGN KEY (part_id) REFERENCES Parts(part_id)
);

CREATE TABLE Fault_Log_Parts (
    fault_log_entry_id INTEGER NOT NULL,
    part_fault_id INTEGER NOT NULL,
    fault_status VARCHAR(10) NOT NULL,
    FOREIGN KEY (fault_log_entry_id) REFERENCES Fault_Log(fault_log_entry_id),
    FOREIGN KEY (part_fault_id) REFERENCES Part_Faults(part_fault_id)
);

CREATE TABLE Skills_Required_To_Fix (
    part_fault_id INTEGER NOT NULL,
    skill_id INTEGER NOT NULL,
    FOREIGN KEY (skill_id) REFERENCES Skills(skill_id),
    FOREIGN KEY (part_fault_id) REFERENCES Part_Faults(part_fault_id)
);