CREATE DATABASE IF NOT EXISTS cyclistsafe
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE cyclistsafe;

CREATE TABLE roles (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO roles (name) VALUES
('public'), ('advocacy'), ('council'), ('admin');

CREATE TABLE users (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    email         VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role_id       INT NOT NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

CREATE TABLE crash_cyclist (
    crash_id            INT PRIMARY KEY,
    crash_year          INT,
    crash_month         TINYINT,
    day_of_week         VARCHAR(16),
    time_interval       VARCHAR(32),
    lga                 VARCHAR(128),
    town                VARCHAR(128),
    latitude            DECIMAL(9,6),
    longitude           DECIMAL(9,6),
    degree              VARCHAR(32),
    degree_detailed     VARCHAR(64),
    speed_limit         INT,
    road_surface        VARCHAR(64),
    surface_condition   VARCHAR(64),
    weather             VARCHAR(64),
    street_lighting     VARCHAR(64),
    natural_lighting    VARCHAR(64),
    dca_code            VARCHAR(16),
    dca_description     VARCHAR(255),
    no_killed           INT,
    no_serious_inj      INT,
    no_mod_inj          INT,
    no_minor_inj        INT,
    INDEX idx_lga (lga),
    INDEX idx_geo (latitude, longitude),
    INDEX idx_time (crash_year, crash_month),
    INDEX idx_severity (degree)
);

CREATE TABLE risk_cell (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    cell_lat        DECIMAL(9,6),
    cell_lng        DECIMAL(9,6),
    crashes_total   INT NOT NULL DEFAULT 0,
    killed_total    INT NOT NULL DEFAULT 0,
    serious_total   INT NOT NULL DEFAULT 0,
    risk_index      DECIMAL(6,4) NOT NULL DEFAULT 0,
    INDEX idx_cell (cell_lat, cell_lng)
);
