CREATE TABLE safety_alerts (
    id SERIAL,
    date date DEFAULT NULL,
    time varchar(255) DEFAULT NULL,
    location varchar(255) DEFAULT NULL,
    classification varchar(100) NOT NULL DEFAULT 'general',
    description text DEFAULT NULL,
    status varchar(50) DEFAULT 'open',
    investigator_id INTEGER DEFAULT NULL,
    submitted_by INTEGER DEFAULT NULL,
    form_data jsonb DEFAULT NULL,
    tenant_id INTEGER DEFAULT NULL,
    branch_id INTEGER DEFAULT NULL,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (id)
);

CREATE TABLE safety_alert_files (
    id SERIAL,
    alert_id INTEGER NOT NULL,
    file_path varchar(255) DEFAULT NULL,
    uploaded_by INTEGER DEFAULT NULL,
    tenant_id INTEGER DEFAULT NULL,
    branch_id INTEGER DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE safety_alert_logs (
    id SERIAL,
    alert_id INTEGER NOT NULL,
    action varchar(50) NOT NULL,
    details text DEFAULT NULL,
    user_id INTEGER DEFAULT NULL,
    logged_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    tenant_id INTEGER DEFAULT NULL,
    branch_id INTEGER DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);
CREATE INDEX safety_alert_logs_alert_idx ON safety_alert_logs(alert_id);
