-- ============================================================================
--  PHOA Water - MySQL schema + ingest service account
-- ============================================================================
--  Run this ONCE, as a MySQL administrator (e.g. root), on the PC that will
--  host the database (the same machine the PHOA ingest service runs on):
--
--      mysql -u root -p < schema_phoa_water.sql
--
--  It creates:
--    * the `phoa_water` database (utf8mb4)
--    * the three tables the ingest service writes: clients, readings, events
--    * the `phoa_water` application account, with the same default password
--      the ingest service installer pre-fills, so ingestion works out of the
--      box (user phoa_water / PHOAwater123)
--
--  Run this BEFORE installing the ingest service. The installer pre-fills the
--  same user, password, and database, so you can just accept its defaults.
--
--  >>> The account is restricted to LOCAL connections (localhost / 127.0.0.1)
--  >>> with data-only (SELECT/INSERT/UPDATE/DELETE) privileges, so the known
--  >>> default password is fine for a normal single-machine install: it cannot
--  >>> be used to log in from another computer. ONLY if your MySQL server is
--  >>> reachable from other machines, change the password in both CREATE USER
--  >>> lines below and enter the same value in the installer.
-- ============================================================================

CREATE DATABASE IF NOT EXISTS `phoa_water`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE `phoa_water`;

-- ---------------------------------------------------------------------------
--  clients: one row per mesh node, upserted on every batch so fw/chip/RSSI
--  stay current. Referenced by readings.node_id.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `clients` (
  `node_id`       TINYINT UNSIGNED  NOT NULL,
  `chip`          VARCHAR(16)       NOT NULL,
  `fw_version`    VARCHAR(16)       NOT NULL,
  `first_seen`    DATETIME          NOT NULL,
  `last_seen`     DATETIME          NOT NULL,
  `last_rssi_dbm` DECIMAL(5,1)      DEFAULT NULL,
  `last_lqi`      TINYINT UNSIGNED  DEFAULT NULL,
  `notes`         VARCHAR(255)      DEFAULT NULL,
  PRIMARY KEY (`node_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  readings: one row per decoded meter reading. volume_gal is DECIMAL(12,1)
--  so 0.1-gallon precision (P282/P290 tenths) is preserved. node_id must
--  exist in clients (the ingest service upserts the client first, in the same
--  transaction).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `readings` (
  `id`                  BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `node_id`             TINYINT UNSIGNED  NOT NULL DEFAULT 0,
  `meter_id`            INT UNSIGNED      NOT NULL DEFAULT 0,
  `protocol`            SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `volume_gal`          DECIMAL(12,1)     NOT NULL DEFAULT 0.0,
  `flow_gpm`            FLOAT             DEFAULT 0,
  `freq_hz`             INT UNSIGNED      DEFAULT 0,
  `rssi_dbm`            DECIMAL(5,1)      DEFAULT 0.0,
  `gateway_received_at` DATETIME          NOT NULL,
  `logged_at`           DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_meter_time` (`meter_id`, `gateway_received_at`),
  KEY `idx_node_time`  (`node_id`,  `gateway_received_at`),
  CONSTRAINT `fk_readings_node`
    FOREIGN KEY (`node_id`) REFERENCES `clients` (`node_id`) ON UPDATE CASCADE,
  CONSTRAINT `chk_readings_volume_nonneg` CHECK (`volume_gal` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  events: node lifecycle / diagnostic events forwarded by the gateway.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `events` (
  `id`                  BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `node_id`             TINYINT UNSIGNED  DEFAULT NULL,
  `event_type`          VARCHAR(32)       NOT NULL,
  `payload_json`        JSON              DEFAULT NULL,
  `gateway_received_at` DATETIME          NOT NULL,
  `logged_at`           DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_type_time` (`event_type`, `gateway_received_at`),
  KEY `idx_node_time` (`node_id`,    `gateway_received_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  Application account used by the ingest service. Created for both localhost
--  (named pipe / socket) and 127.0.0.1 (TCP loopback) so it works regardless
--  of how the ODBC driver connects. DML only: no DDL/DROP rights by design.
-- ---------------------------------------------------------------------------
CREATE USER IF NOT EXISTS 'phoa_water'@'localhost' IDENTIFIED BY 'PHOAwater123';
CREATE USER IF NOT EXISTS 'phoa_water'@'127.0.0.1' IDENTIFIED BY 'PHOAwater123';

GRANT SELECT, INSERT, UPDATE, DELETE ON `phoa_water`.* TO 'phoa_water'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `phoa_water`.* TO 'phoa_water'@'127.0.0.1';

FLUSH PRIVILEGES;
