--CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS hstore;

CREATE DATABASE pager_data;
CREATE USER admin WITH ENCRYPTED PASSWORD '1234';
GRANT ALL PRIVILEGES ON DATABASE pager_data TO admin;

-- === Devices ===
CREATE TABLE Devices (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),

    euid VARCHAR(16) NOT NULL UNIQUE, -- LoRaWAN Extended Unique ID (hex, 8 bytes)
    name VARCHAR(64) NOT NULL, -- ! Not Unique

    fw_version VARCHAR(24) NOT NULL,

    charge NUMERIC(5,2) CHECK (charge >= 0 AND charge <= 100), -- battery in %
    last_online TIMESTAMPTZ, -- ! NULL

    -- Location fields:
    latitude    DOUBLE PRECISION CHECK (latitude >= -90 AND latitude <= 90),
    longitude   DOUBLE PRECISION CHECK (longitude >= -180 AND longitude <= 180),
    altitude    DOUBLE PRECISION
);

CREATE TABLE Messages (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),

    sender_id INT NOT NULL REFERENCES Devices(id) ON DELETE SET NULL,
    receiver_id INT REFERENCES Devices(id) ON DELETE SET NULL, -- ! NULL
    payload VARCHAR(256)
);

CREATE TABLE Statuses (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),

    device_id INT NOT NULL REFERENCES Devices(id) ON DELETE CASCADE,
    payload VARCHAR(256)
);

-- === Roles ===
CREATE TABLE Roles (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),

    name VARCHAR(64) NOT NULL UNIQUE,
    description VARCHAR(128) NOT NULL DEFAULT 'Role description'
);

INSERT INTO Roles (name, description) VALUES ('Admin', 'Should do everything.'); -- Admin role

CREATE TABLE Permissions (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),

    name VARCHAR(64) NOT NULL UNIQUE,
    description VARCHAR(128) NOT NULL DEFAULT 'Permission description'
);

INSERT INTO Permissions (name, description) VALUES ('Everything', 'Can do everything.'); -- Admin permission

-- Join table for Roles & Permissions
CREATE TABLE RolesPermissions (
    role_id INT NOT NULL REFERENCES Roles(id),
    permission_id INT NOT NULL REFERENCES Permissions(id)
);

INSERT INTO RolesPermissions (role_id, permission_id) VALUES (1, 1); -- Tie together role & permission

-- === Users ===
CREATE TABLE Users (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),

    name VARCHAR(64) UNIQUE NOT NULL,
    password VARCHAR(64) NOT NULL,
    role_id INT DEFAULT 2 REFERENCES Roles(id) ON DELETE SET DEFAULT, -- default low privilleges
    last_online TIMESTAMPTZ -- ! NULL
);

INSERT INTO Users (name, password, role_id) VALUES ('Admin', 'admin', 1); -- Admin user

CREATE TABLE NFC_Cards (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),

    user_id INT REFERENCES Users(id) ON DELETE SET NULL, -- ! NULL
    device_id INT REFERENCES Devices(id) ON DELETE SET NULL, -- ! NULL
    uid VARCHAR(7) NOT NULL UNIQUE,
    CONSTRAINT uid_length CHECK (length(uid) = 4 OR length(uid) = 7) -- Check for valid UUID
);

-- === Logs ===
CREATE TABLE Logs (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),

    db_user TEXT DEFAULT current_user
);

CREATE TABLE LogsUsers (
    log_id INT REFERENCES Logs(id) ON DELETE CASCADE,
    user_id INT REFERENCES Users(id) ON DELETE CASCADE
);

CREATE TABLE LogsDevices (
    log_id INT REFERENCES Logs(id) ON DELETE CASCADE,
    device_id INT REFERENCES Devices(id) ON DELETE CASCADE
);

CREATE TABLE Changes (
    id SERIAL PRIMARY KEY,
    log_id INT NOT NULL REFERENCES Logs(id) ON DELETE CASCADE,
    table_name TEXT NOT NULL,
    operation TEXT NOT NULL,
    old_value hstore, -- ! NULL
    new_value hstore, -- ! NULL
    CONSTRAINT change_diff CHECK (old_value != new_value)
);