Schema for risk-management-plans

CREATE TABLE rmp_facility (
        epa_facility_id TEXT PRIMARY KEY,
        report TEXT,
        facility_name TEXT,
        facility_address TEXT,
        city TEXT,
        state TEXT,
        county TEXT,
        zip TEXT,
        facility_duns TEXT,
        latitude REAL,
        longitude REAL,
        receipt_date TEXT,
        report_created_date TEXT
    , popup TEXT);
CREATE TABLE rmp_chemical (
        chemical_id INTEGER PRIMARY KEY,
        chemical_name TEXT,
        cas_number TEXT,
        flammable_toxic TEXT
    );
CREATE TABLE rmp_facility_chemicals (
        facility_chemical_id INTEGER PRIMARY KEY,
        facility_id TEXT,
        chemical_id INTEGER,
        program_level TEXT,
        FOREIGN KEY (facility_id) REFERENCES rmp_facility(epa_facility_id),
        FOREIGN KEY (chemical_id) REFERENCES rmp_chemical(chemical_id)
    );
CREATE TABLE rmp_naics (
        naics_code TEXT PRIMARY KEY,
        naics_description TEXT
    );
CREATE TABLE rmp_facility_naics (
        facility_naics_id INTEGER PRIMARY KEY,
        facility_id TEXT,
        naics_code TEXT,
        FOREIGN KEY (facility_id) REFERENCES rmp_facility(epa_facility_id),
        FOREIGN KEY (naics_code) REFERENCES rmp_naics(naics_code)
    );
CREATE TABLE rmp_facility_accidents (
        facility_accident_id TEXT PRIMARY KEY,
        accident_id TEXT,
        facility_id TEXT,
        date_of_accident TEXT,
        time_accident_began TEXT,
        release_duration TEXT,
        naics_code TEXT,
        FOREIGN KEY (facility_id) REFERENCES rmp_facility(epa_facility_id),
        FOREIGN KEY (naics_code) REFERENCES rmp_naics(naics_code)
    );
CREATE TABLE rmp_accident_chemicals (
        accident_chemical_id INTEGER PRIMARY KEY,
        facility_accident_chemical_id TEXT,
        facility_accident_id TEXT,
        quantity_released_lbs TEXT,
        percent_weight TEXT,
        chemical_id INTEGER,
        FOREIGN KEY (facility_accident_id) REFERENCES rmp_facility_accidents(facility_accident_id),
        FOREIGN KEY (chemical_id) REFERENCES rmp_chemical(chemical_id)
    );
CREATE TABLE "tbl_accident_details" (
        EPA_Facility_ID TEXT,
        Accident_History_ID TEXT,
        Facility_Accident_ID TEXT PRIMARY KEY,
        Date_of_Accident TEXT,
        Time_Accident_Began TEXT,
        NAICS_Code_of_Process_Involved TEXT,
        NAICS_Description TEXT,
        Release_Duration_Hours TEXT,
        Release_Duration_Minutes TEXT,
        Gas_Release TEXT,
        Liquid_Spill_Evaporation TEXT,
        Fire TEXT,
        Explosion TEXT,
        Uncontrolled_Runaway_Reaction TEXT,
        Storage_Vessel TEXT,
        Piping TEXT,
        Process_Vessel TEXT,
        Transfer_Hose TEXT,
        Valve TEXT,
        Pump TEXT,
        Joint TEXT,
        Other_Release_Source TEXT,
        Wind_Speed TEXT,
        Wind_Speed_Units TEXT,
        Wind_Direction TEXT,
        Temperature TEXT,
        Atmospheric_Stability_Class TEXT,
        Precipitation_Present TEXT,
        Unknown_Weather_Conditions TEXT,
        Employee_Contractor_Deaths TEXT,
        Public_Responder_Deaths TEXT,
        Public_Deaths TEXT,
        Employee_Contractor_Injuries TEXT,
        Public_Responder_Injuries TEXT,
        Public_Injuries TEXT,
        On_Site_Property_Damage_Dollar INTEGER,
        Off_Site_Deaths TEXT,
        Off_Site_Hospitalizations TEXT,
        Off_Site_Public_Deaths TEXT,
        Off_Site_Other_Medical_Treatments TEXT,
        Evacuated TEXT,
        Sheltered_in_Place TEXT,
        Off_Site_Property_Damage_Dollar INTEGER,
        Fish_or_Animal_Kills TEXT,
        Tree_Lawn_Shrub_or_Crop_Damage TEXT,
        Water_Contamination TEXT,
        Soil_Contamination TEXT,
        Other_Environmental_Damage TEXT,
        Initiating_Event TEXT,
        Contributing_Equipment_Failure TEXT,
        Contributing_Human_Error TEXT,
        Contributing_Improper_Procedures TEXT,
        Contributing_Overpressurization TEXT,
        Contributing_Upset_Condition TEXT,
        Contributing_By_Pass_Condition TEXT,
        Contributing_Maintenance_Activity_Inactivity TEXT,
        Contributing_Process_Design_Failure TEXT,
        Contributing_Unsuitable_Equipment TEXT,
        Contributing_Unusual_Weather_Condition TEXT,
        Contributing_Management_Error TEXT,
        Contributing_Other TEXT,
        Off_Site_Responders_Notified TEXT,
        Change_Improved_Upgraded_Equipment TEXT,
        Change_Revised_Maintenance TEXT,
        Change_Revised_Training TEXT,
        Change_Revised_Operating_Procedures TEXT,
        Change_New_Process_Controls TEXT,
        Change_New_Mitigation_Systems TEXT,
        Change_Revised_Emergency_Response_Plan TEXT,
        Change_Changed_Process TEXT,
        Change_Reduced_Inventory TEXT,
        Change_None TEXT,
        Change_Other TEXT
    );
CREATE TABLE 'facility_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'facility_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE 'facility_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE 'facility_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE accident_chemicals_view (
    accident_chemical_id INTEGER PRIMARY KEY,
    accident_id TEXT,
    facility_id TEXT,
    facility_name TEXT,
    facility_address TEXT,
    city TEXT,
    state TEXT,
    county TEXT,
    date_of_accident TEXT,
    chemical_name TEXT,
    quantity_released_lbs TEXT,
    percent_weight TEXT
);
CREATE TABLE 'accident_chemicals_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'accident_chemicals_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE 'accident_chemicals_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE 'accident_chemicals_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE facility_accidents_view (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    facility_accident_id TEXT,
    accident_id TEXT,
    facility_id TEXT,
    facility_name TEXT,
    facility_address TEXT,
    city TEXT,
    state TEXT,
    county TEXT,
    date_of_accident TEXT,
    time_accident_began TEXT,
    release_duration TEXT,
    naics_code TEXT,
    chemical_names TEXT,
    latitude REAL,
    longitude REAL
, popup TEXT);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE 'facility_accidents_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'facility_accidents_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE 'facility_accidents_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE 'facility_accidents_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE facility_view (
    epa_facility_id TEXT PRIMARY KEY,
    report TEXT,
    facility_name TEXT,
    facility_address TEXT,
    city TEXT,
    state TEXT,
    county TEXT,
    zip TEXT,
    facility_duns TEXT,
    receipt_date TEXT,
    report_created_date TEXT,
    naics_codes TEXT,
    chemical_names TEXT,
    latitude REAL,
    longitude REAL,
    popup TEXT
);
CREATE INDEX idx_rmp_facility_naics_facility_id ON rmp_facility_naics(facility_id);
CREATE INDEX idx_rmp_facility_chemicals_facility_id ON rmp_facility_chemicals(facility_id);
CREATE INDEX idx_facility_id ON facility_view(epa_facility_id);
CREATE VIRTUAL TABLE facility_fts USING fts5(
    facility_name,
    facility_address,
    city,
    state,
    county,
    zip,
    facility_duns,
    receipt_date,
    chemical_names,
    content='facility_view',
    content_rowid='epa_facility_id'
);
CREATE VIRTUAL TABLE accident_chemicals_fts USING fts5(
    accident_id,
    facility_id,
    facility_name,
    facility_address,
    city,
    state,
    county,
    date_of_accident,
    chemical_name,
    quantity_released_lbs,
    percent_weight,
    content='accident_chemicals_view',
    content_rowid='accident_chemical_id',
    tokenize='unicode61'
);
CREATE VIRTUAL TABLE facility_accidents_fts USING fts5(
    facility_accident_id,
    accident_id,
    facility_id,
    facility_name,
    facility_address,
    city,
    state,
    county,
    date_of_accident,
    time_accident_began,
    release_duration,
    naics_code,
    chemical_names,
    content='facility_accidents_view',
    content_rowid='id',
    tokenize='unicode61'
);
CREATE INDEX idx_chemical_id ON accident_chemicals_view(accident_chemical_id);
CREATE INDEX idx_accident_id ON facility_accidents_view(facility_accident_id)