-- OPS PLATFORM — Database Setup
-- Run once on your MySQL server

CREATE DATABASE IF NOT EXISTS ops_platform CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ops_platform;

CREATE TABLE IF NOT EXISTS ops_staff (
    uid         VARCHAR(50)     NOT NULL PRIMARY KEY,
    name        VARCHAR(100)    NOT NULL,
    role        VARCHAR(50)     NOT NULL DEFAULT 'Bodyguard',
    added       BIGINT          NOT NULL DEFAULT 0,
    created_at  TIMESTAMP       DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ops_events (
    id          VARCHAR(25)     NOT NULL PRIMARY KEY,
    type        VARCHAR(30)     NOT NULL,   -- clock_in, clock_out, meal, counter
    uid         VARCHAR(50),               -- NFC tag UID
    name        VARCHAR(100),              -- staff name (denormalised for speed)
    extra       TEXT,                      -- JSON: {meal, countType}
    ts          BIGINT          NOT NULL,   -- unix ms
    created_at  TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_type (type),
    INDEX idx_ts   (ts),
    INDEX idx_uid  (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;