-- ===========================================================================
--  ZF MDT - Schema de la base
-- ===========================================================================
--  MySQL 5.7+ / MariaDB 10.3+
--
--  A importer une seule fois :
--      mysql -u root -p zf_mdt < database/schema.sql
--  ou via phpMyAdmin (onglet Importer).
--
--  Conventions :
--    * tous les horodatages sont des entiers UNIX (comme cote GMod), ce qui
--      evite toute conversion de fuseau entre le jeu et le site
--    * les identifiants Steam sont stockes en VARCHAR(20) : un SteamID64
--      depasse la precision d'un entier signe dans certains langages
--    * suppression en cascade uniquement la ou une ligne orpheline n'aurait
--      aucun sens (journal d'un appel supprime, par exemple)
-- ===========================================================================


-- ===========================================================================
--  COMPTES DU PERSONNEL
-- ===========================================================================
-- Un compte = un agent. Cree automatiquement a la premiere connexion Steam.
-- Le premier compte cree devient chef de departement : sans cela, personne
-- ne pourrait promouvoir personne.
CREATE TABLE IF NOT EXISTS `mdt_users` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `steamid64`     VARCHAR(20)  NOT NULL,
    `name`          VARCHAR(64)  NOT NULL DEFAULT '',
    `avatar`        VARCHAR(255) NOT NULL DEFAULT '',
    `profile_url`   VARCHAR(255) NOT NULL DEFAULT '',

    -- Affectation : departement (police / ems / fire) et grade interne.
    `dept`          VARCHAR(16)  NOT NULL DEFAULT '',
    `rank`          VARCHAR(24)  NOT NULL DEFAULT '',
    `badge`         VARCHAR(16)  NOT NULL DEFAULT '',
    `callsign`      VARCHAR(24)  NOT NULL DEFAULT '',

    -- 0 = compte suspendu, il ne peut plus se connecter.
    `active`        TINYINT(1)   NOT NULL DEFAULT 1,
    -- 1 = acces a l'administration du site, independamment du grade.
    `is_admin`      TINYINT(1)   NOT NULL DEFAULT 0,

    `notes`         TEXT         NULL,
    `created`       INT UNSIGNED NOT NULL DEFAULT 0,
    `last_login`    INT UNSIGNED NOT NULL DEFAULT 0,
    `last_ip`       VARCHAR(45)  NOT NULL DEFAULT '',

    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_users_steam` (`steamid64`),
    KEY `ix_users_dept` (`dept`, `active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================================================
--  FICHIER DES PERSONNES
-- ===========================================================================
-- Etat civil des habitants. Cree par le jeu a la premiere connexion d'un
-- joueur, ou a la main par un agent pour un personnage hors ligne.
CREATE TABLE IF NOT EXISTS `mdt_citizens` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `steamid64`     VARCHAR(20)  NULL,
    -- Numero d'identification du casier de l'Etat de New York.
    `nysid`         VARCHAR(12)  NOT NULL DEFAULT '',

    `first_name`    VARCHAR(48)  NOT NULL DEFAULT '',
    `last_name`     VARCHAR(48)  NOT NULL DEFAULT '',
    `dob`           VARCHAR(10)  NOT NULL DEFAULT '',
    `sex`           CHAR(1)      NOT NULL DEFAULT 'M',
    `height_cm`     SMALLINT UNSIGNED NOT NULL DEFAULT 175,
    `weight_kg`     SMALLINT UNSIGNED NOT NULL DEFAULT 75,
    `hair`          VARCHAR(24)  NOT NULL DEFAULT '',
    `eyes`          VARCHAR(24)  NOT NULL DEFAULT '',

    `address`       VARCHAR(120) NOT NULL DEFAULT '',
    `phone`         VARCHAR(24)  NOT NULL DEFAULT '',
    `occupation`    VARCHAR(64)  NOT NULL DEFAULT '',
    `avatar`        VARCHAR(255) NOT NULL DEFAULT '',

    -- Titres : 0 aucun, 1 valide, 2 suspendu, 3 revoque, 4 expire.
    `lic_driver`    TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `lic_firearm`   TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `lic_business`  TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `lic_hack`      TINYINT UNSIGNED NOT NULL DEFAULT 0,
    -- Points d'infraction cumules (11 = suspension dans l'Etat de NY).
    `dmv_points`    TINYINT UNSIGNED NOT NULL DEFAULT 0,

    -- Marqueurs, en liste separee par des virgules : armed,edp,gang...
    `flags`         VARCHAR(255) NOT NULL DEFAULT '',
    `medical`       TEXT         NULL,
    `notes`         TEXT         NULL,

    `created`       INT UNSIGNED NOT NULL DEFAULT 0,
    `updated`       INT UNSIGNED NOT NULL DEFAULT 0,
    `last_seen`     INT UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_citizens_steam` (`steamid64`),
    KEY `ix_citizens_name` (`last_name`, `first_name`),
    KEY `ix_citizens_nysid` (`nysid`),
    KEY `ix_citizens_seen` (`last_seen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================================================
--  IMMATRICULATIONS
-- ===========================================================================
CREATE TABLE IF NOT EXISTS `mdt_vehicles` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `plate`         VARCHAR(12)  NOT NULL,
    `owner_id`      INT UNSIGNED NULL,
    `owner_name`    VARCHAR(96)  NOT NULL DEFAULT '',

    `make`          VARCHAR(32)  NOT NULL DEFAULT '',
    `model`         VARCHAR(48)  NOT NULL DEFAULT '',
    `color`         VARCHAR(24)  NOT NULL DEFAULT '',
    `year`          SMALLINT UNSIGNED NOT NULL DEFAULT 0,

    -- 0 non immatricule, 1 en regle, 2 expiree, 3 suspendue.
    `reg_status`    TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `insured`       TINYINT(1)   NOT NULL DEFAULT 1,
    -- stolen,noins,bolo,impound
    `flags`         VARCHAR(120) NOT NULL DEFAULT '',
    `notes`         TEXT         NULL,

    `created`       INT UNSIGNED NOT NULL DEFAULT 0,
    `updated`       INT UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_vehicles_plate` (`plate`),
    KEY `ix_vehicles_owner` (`owner_id`),
    CONSTRAINT `fk_vehicles_owner` FOREIGN KEY (`owner_id`)
        REFERENCES `mdt_citizens` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================================================
--  CASIER
-- ===========================================================================
-- type : arrest | citation | warning | contact | incident | conviction
-- Une entree n'est jamais supprimee : elle est annulee (voided) avec son
-- motif. C'est ce qui rend le fichier opposable.
CREATE TABLE IF NOT EXISTS `mdt_records` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `citizen_id`    INT UNSIGNED NOT NULL,
    `type`          VARCHAR(16)  NOT NULL,
    `title`         VARCHAR(120) NOT NULL DEFAULT '',

    -- Identifiants des chefs retenus, separes par des virgules.
    `charges`       TEXT         NULL,
    `narrative`     MEDIUMTEXT   NULL,
    `location`      VARCHAR(120) NOT NULL DEFAULT '',

    `fine`          INT UNSIGNED NOT NULL DEFAULT 0,
    `jail`          INT UNSIGNED NOT NULL DEFAULT 0,
    `felony_count`  TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `points`        TINYINT UNSIGNED NOT NULL DEFAULT 0,

    `officer_id`    INT UNSIGNED NULL,
    `officer_name`  VARCHAR(64)  NOT NULL DEFAULT '',
    `officer_unit`  VARCHAR(24)  NOT NULL DEFAULT '',
    `dept`          VARCHAR(16)  NOT NULL DEFAULT 'police',
    `call_id`       INT UNSIGNED NULL,

    `sealed`        TINYINT(1)   NOT NULL DEFAULT 0,
    `voided`        TINYINT(1)   NOT NULL DEFAULT 0,
    `void_reason`   VARCHAR(255) NOT NULL DEFAULT '',
    `created`       INT UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (`id`),
    KEY `ix_records_citizen` (`citizen_id`, `created`),
    KEY `ix_records_officer` (`officer_id`),
    KEY `ix_records_type` (`type`, `created`),
    CONSTRAINT `fk_records_citizen` FOREIGN KEY (`citizen_id`)
        REFERENCES `mdt_citizens` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_records_officer` FOREIGN KEY (`officer_id`)
        REFERENCES `mdt_users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================================================
--  MANDATS
-- ===========================================================================
-- status : pending | active | served | cleared | rejected | expired
CREATE TABLE IF NOT EXISTS `mdt_warrants` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `citizen_id`    INT UNSIGNED NOT NULL,
    `type`          VARCHAR(12)  NOT NULL DEFAULT 'arrest',
    `status`        VARCHAR(12)  NOT NULL DEFAULT 'pending',

    `reason`        TEXT         NULL,
    `charges`       TEXT         NULL,

    `requested_by`  INT UNSIGNED NULL,
    `requested_name` VARCHAR(64) NOT NULL DEFAULT '',
    `decided_by`    INT UNSIGNED NULL,
    `decided_name`  VARCHAR(64)  NOT NULL DEFAULT '',

    `created`       INT UNSIGNED NOT NULL DEFAULT 0,
    `decided`       INT UNSIGNED NOT NULL DEFAULT 0,
    `expires`       INT UNSIGNED NOT NULL DEFAULT 0,
    `closed`        INT UNSIGNED NOT NULL DEFAULT 0,
    `close_reason`  VARCHAR(255) NOT NULL DEFAULT '',

    PRIMARY KEY (`id`),
    KEY `ix_warrants_citizen` (`citizen_id`, `status`),
    KEY `ix_warrants_status` (`status`, `created`),
    CONSTRAINT `fk_warrants_citizen` FOREIGN KEY (`citizen_id`)
        REFERENCES `mdt_citizens` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================================================
--  AVIS DE RECHERCHE
-- ===========================================================================
CREATE TABLE IF NOT EXISTS `mdt_bolos` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `dept`          VARCHAR(16)  NOT NULL DEFAULT 'police',
    `category`      VARCHAR(12)  NOT NULL DEFAULT 'person',

    `title`         VARCHAR(120) NOT NULL DEFAULT '',
    `description`   TEXT         NULL,
    `plate`         VARCHAR(12)  NOT NULL DEFAULT '',
    `citizen_id`    INT UNSIGNED NULL,
    `last_seen`     VARCHAR(160) NOT NULL DEFAULT '',
    `priority`      TINYINT UNSIGNED NOT NULL DEFAULT 3,

    `active`        TINYINT(1)   NOT NULL DEFAULT 1,
    `author_id`     INT UNSIGNED NULL,
    `author_name`   VARCHAR(64)  NOT NULL DEFAULT '',
    `created`       INT UNSIGNED NOT NULL DEFAULT 0,
    `expires`       INT UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (`id`),
    KEY `ix_bolos_active` (`active`, `dept`, `priority`),
    KEY `ix_bolos_plate` (`plate`),
    CONSTRAINT `fk_bolos_citizen` FOREIGN KEY (`citizen_id`)
        REFERENCES `mdt_citizens` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================================================
--  UNITES EN SERVICE
-- ===========================================================================
-- Alimentee par le serveur de jeu. Une unite qui cesse d'emettre est
-- consideree hors service au bout de game.unit_timeout secondes.
CREATE TABLE IF NOT EXISTS `mdt_units` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `steamid64`     VARCHAR(20)  NOT NULL,
    `user_id`       INT UNSIGNED NULL,
    `callsign`      VARCHAR(24)  NOT NULL,
    `name`          VARCHAR(64)  NOT NULL DEFAULT '',
    `dept`          VARCHAR(16)  NOT NULL DEFAULT 'police',
    `rank`          VARCHAR(24)  NOT NULL DEFAULT '',

    `status`        VARCHAR(16)  NOT NULL DEFAULT 'available',
    `call_id`       INT UNSIGNED NULL,

    -- Derniere position connue, en coordonnees Source.
    `pos_x`         INT          NOT NULL DEFAULT 0,
    `pos_y`         INT          NOT NULL DEFAULT 0,
    `pos_z`         INT          NOT NULL DEFAULT 0,
    `location`      VARCHAR(120) NOT NULL DEFAULT '',
    `health`        SMALLINT     NOT NULL DEFAULT 100,
    `in_vehicle`    TINYINT(1)   NOT NULL DEFAULT 0,

    `since`         INT UNSIGNED NOT NULL DEFAULT 0,
    `last_seen`     INT UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_units_steam` (`steamid64`),
    UNIQUE KEY `uq_units_callsign` (`callsign`),
    KEY `ix_units_dept` (`dept`, `status`),
    KEY `ix_units_call` (`call_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================================================
--  APPELS
-- ===========================================================================
-- status : pending | dispatched | onscene | closed
CREATE TABLE IF NOT EXISTS `mdt_calls` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`        VARCHAR(16)  NOT NULL DEFAULT '',
    `dept`          VARCHAR(16)  NOT NULL DEFAULT 'police',

    `type_code`     VARCHAR(12)  NOT NULL DEFAULT 'OTHER',
    `type_label`    VARCHAR(96)  NOT NULL DEFAULT '',
    `priority`      TINYINT UNSIGNED NOT NULL DEFAULT 4,
    `status`        VARCHAR(12)  NOT NULL DEFAULT 'pending',

    `location`      VARCHAR(160) NOT NULL DEFAULT '',
    `cross_street`  VARCHAR(160) NOT NULL DEFAULT '',
    `pos_x`         INT          NOT NULL DEFAULT 0,
    `pos_y`         INT          NOT NULL DEFAULT 0,
    `pos_z`         INT          NOT NULL DEFAULT 0,

    `caller_name`   VARCHAR(96)  NOT NULL DEFAULT '',
    `caller_sid`    VARCHAR(20)  NOT NULL DEFAULT '',
    `caller_phone`  VARCHAR(24)  NOT NULL DEFAULT '',
    `narrative`     TEXT         NULL,

    -- manual | 911 | auto | panic
    `source`        VARCHAR(12)  NOT NULL DEFAULT 'manual',

    `created`       INT UNSIGNED NOT NULL DEFAULT 0,
    `dispatched`    INT UNSIGNED NOT NULL DEFAULT 0,
    `onscene`       INT UNSIGNED NOT NULL DEFAULT 0,
    `closed`        INT UNSIGNED NOT NULL DEFAULT 0,
    `disposition`   VARCHAR(16)  NOT NULL DEFAULT '',
    `close_note`    TEXT         NULL,
    `closed_by`     INT UNSIGNED NULL,

    PRIMARY KEY (`id`),
    KEY `ix_calls_board` (`dept`, `status`, `priority`, `created`),
    KEY `ix_calls_number` (`number`),
    KEY `ix_calls_created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Unites engagees sur un appel. Table de liaison plutot qu'une liste en
-- texte : cela permet de retrouver l'historique d'une unite.
CREATE TABLE IF NOT EXISTS `mdt_call_units` (
    `call_id`       INT UNSIGNED NOT NULL,
    `callsign`      VARCHAR(24)  NOT NULL,
    `unit_id`       INT UNSIGNED NULL,
    `assigned`      INT UNSIGNED NOT NULL DEFAULT 0,
    `onscene`       INT UNSIGNED NOT NULL DEFAULT 0,
    `released`      INT UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (`call_id`, `callsign`),
    KEY `ix_callunits_callsign` (`callsign`),
    CONSTRAINT `fk_callunits_call` FOREIGN KEY (`call_id`)
        REFERENCES `mdt_calls` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Main courante d'un appel.
CREATE TABLE IF NOT EXISTS `mdt_call_log` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `call_id`       INT UNSIGNED NOT NULL,
    `ts`            INT UNSIGNED NOT NULL DEFAULT 0,
    `author`        VARCHAR(64)  NOT NULL DEFAULT '',
    `unit`          VARCHAR(24)  NOT NULL DEFAULT '',
    `text`          TEXT         NULL,
    -- create | assign | unassign | onscene | note | backup | close
    `kind`          VARCHAR(12)  NOT NULL DEFAULT 'note',

    PRIMARY KEY (`id`),
    KEY `ix_calllog_call` (`call_id`, `ts`),
    CONSTRAINT `fk_calllog_call` FOREIGN KEY (`call_id`)
        REFERENCES `mdt_calls` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================================================
--  RAPPORTS
-- ===========================================================================
-- kind : uf61 | tri | field | pcr | nfirs
CREATE TABLE IF NOT EXISTS `mdt_reports` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `kind`          VARCHAR(12)  NOT NULL,
    `number`        VARCHAR(24)  NOT NULL DEFAULT '',
    `dept`          VARCHAR(16)  NOT NULL DEFAULT 'police',
    `title`         VARCHAR(160) NOT NULL DEFAULT '',

    `call_id`       INT UNSIGNED NULL,
    `citizen_id`    INT UNSIGNED NULL,
    -- Contenu du formulaire, en JSON. Le schema depend de kind.
    `data`          MEDIUMTEXT   NULL,

    `author_id`     INT UNSIGNED NULL,
    `author_name`   VARCHAR(64)  NOT NULL DEFAULT '',
    `author_unit`   VARCHAR(24)  NOT NULL DEFAULT '',
    -- draft | submitted
    `status`        VARCHAR(12)  NOT NULL DEFAULT 'draft',

    `created`       INT UNSIGNED NOT NULL DEFAULT 0,
    `updated`       INT UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_reports_number` (`number`),
    KEY `ix_reports_author` (`author_id`, `updated`),
    KEY `ix_reports_citizen` (`citizen_id`),
    CONSTRAINT `fk_reports_citizen` FOREIGN KEY (`citizen_id`)
        REFERENCES `mdt_citizens` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================================================
--  JOURNAL D'AUDIT
-- ===========================================================================
-- Toute consultation du fichier laisse une trace. flagged = comportement
-- juge anormal (consultation de sa propre fiche, rafale de recherches).
CREATE TABLE IF NOT EXISTS `mdt_audit` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ts`            INT UNSIGNED NOT NULL DEFAULT 0,
    `user_id`       INT UNSIGNED NULL,
    `actor_name`    VARCHAR(64)  NOT NULL DEFAULT '',
    `actor_unit`    VARCHAR(24)  NOT NULL DEFAULT '',
    `dept`          VARCHAR(16)  NOT NULL DEFAULT '',

    `action`        VARCHAR(32)  NOT NULL DEFAULT '',
    `target`        VARCHAR(160) NOT NULL DEFAULT '',
    `detail`        VARCHAR(255) NOT NULL DEFAULT '',
    `ip`            VARCHAR(45)  NOT NULL DEFAULT '',
    `flagged`       TINYINT(1)   NOT NULL DEFAULT 0,

    PRIMARY KEY (`id`),
    KEY `ix_audit_ts` (`ts`),
    KEY `ix_audit_user` (`user_id`, `ts`),
    KEY `ix_audit_flagged` (`flagged`, `ts`),
    CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`)
        REFERENCES `mdt_users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================================================
--  FILE D'ACTIONS VERS LE JEU
-- ===========================================================================
-- GMod ne peut pas recevoir de requete entrante : il interroge cette file.
-- Le site y depose ce qui doit se produire en jeu (poser un wanted, alerter
-- une unite affectee, lever un mandat).
CREATE TABLE IF NOT EXISTS `mdt_queue` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `action`        VARCHAR(32)  NOT NULL,
    `payload`       TEXT         NULL,
    `created`       INT UNSIGNED NOT NULL DEFAULT 0,
    `consumed`      INT UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (`id`),
    KEY `ix_queue_pending` (`consumed`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================================================
--  ZONES D'ADRESSE
-- ===========================================================================
-- Lieux nommes de la carte, captures en jeu par le staff.
CREATE TABLE IF NOT EXISTS `mdt_zones` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`          VARCHAR(96)  NOT NULL,
    `pos_x`         INT          NOT NULL DEFAULT 0,
    `pos_y`         INT          NOT NULL DEFAULT 0,
    `pos_z`         INT          NOT NULL DEFAULT 0,
    `radius`        INT UNSIGNED NOT NULL DEFAULT 400,
    `kind`          VARCHAR(16)  NOT NULL DEFAULT 'address',

    PRIMARY KEY (`id`),
    KEY `ix_zones_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

