-- schema.sql — multi-tenant HeyStar-style relay
CREATE DATABASE IF NOT EXISTS heystar_relay CHARACTER SET utf8mb4;
USE heystar_relay;

-- One row per school/client web app you integrate with.
CREATE TABLE schools (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(150) NOT NULL,
    short_code      VARCHAR(30)  NOT NULL UNIQUE,
    base_url        VARCHAR(255) NOT NULL,              -- e.g. https://alaminschoolapp.com
    api_key         VARCHAR(255) NOT NULL,               -- sent as X-API-Key to their app
    api_secret      VARCHAR(255) NOT NULL,               -- sent as Authorization: Bearer
    active          TINYINT(1) DEFAULT 1,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- One row per biometric device, anywhere, for any school.
CREATE TABLE devices (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    school_id       INT NOT NULL,
    device_key      VARCHAR(32) NOT NULL UNIQUE,         -- 18-digit serial, e.g. HF62A2024...
    device_secret   VARCHAR(255) NOT NULL,                -- comm password set ON the device
    label           VARCHAR(100) NULL,                    -- e.g. "Front Gate"
    purpose         ENUM('attendance','exam','exeat','teacher_attendance','other') DEFAULT 'attendance',
    -- Per-device endpoint override. If NULL, falls back to schools.base_url + default path.
    endpoint_url    VARCHAR(255) NULL,
    active          TINYINT(1) DEFAULT 1,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (school_id) REFERENCES schools(id)
);

-- Default endpoint path per purpose, used when a device has no endpoint_url override.
CREATE TABLE purpose_endpoints (
    purpose         VARCHAR(30) PRIMARY KEY,
    default_path    VARCHAR(255) NOT NULL   -- e.g. /api/heystar/attendance
);

INSERT INTO purpose_endpoints (purpose, default_path) VALUES
    ('attendance', '/api/heystar/attendance'),
    ('exam', '/api/heystar/exam'),
    ('exeat', '/api/heystar/exeat'),
    ('teacher_attendance', '/api/heystar/teacher-attendance'),
    ('other', '/api/heystar/event');

-- Small cache mapping person_sn -> name/category, per school (optional —
-- only needed if you want student/staff distinguished; defaults to
-- 'student' if not cached, since their API defaults that way too).
CREATE TABLE roster_cache (
    school_id       INT NOT NULL,
    person_sn       VARCHAR(32) NOT NULL,
    name            VARCHAR(150) NULL,
    category        ENUM('student','staff') NOT NULL DEFAULT 'student',
    cached_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (school_id, person_sn),
    FOREIGN KEY (school_id) REFERENCES schools(id)
);

-- Log every event received + whether forwarding succeeded — this is your
-- "Recent Sync Activity" equivalent, and essential for debugging.
CREATE TABLE event_log (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    device_id       INT NULL,
    device_key      VARCHAR(32) NOT NULL,
    person_sn       VARCHAR(32) NULL,
    forwarded_to    VARCHAR(255) NULL,
    http_status     INT NULL,
    success         TINYINT(1) DEFAULT 0,
    raw_payload     JSON NULL,
    response_body   TEXT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Simple admin login for the dashboard.
CREATE TABLE admin_users (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(50) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);