-- ===========================================================================
-- Migration: Cameras module (Hik-Connect + manual stream/snapshot fallback)
-- Date: 2026-07-29
--
-- Lets a farm register its cameras and view live footage / snapshots inside
-- PFMIS. Two providers per camera:
--   hikconnect — the platform's Hik-Connect Open Platform account (AppKey +
--                AppSecret in config.php) is called SERVER-SIDE to mint a
--                browser-playable HLS live URL and snapshot for a device serial.
--   manual     — a directly-pasted HLS stream URL and/or snapshot URL, so the
--                page is useful before the Hik-Connect keys are approved (and
--                for any camera that already exposes an HLS/snapshot URL).
--
-- 'cameras' is a normal toggleable farm module, gated by the role system like
-- every other module. Idempotent.
-- ===========================================================================

CREATE TABLE IF NOT EXISTS cameras (
  id            INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  name          VARCHAR(120)  NOT NULL,
  -- which house/area this camera watches (free text, usually a managed_lists house)
  house         VARCHAR(60)   NULL,
  provider      ENUM('hikconnect','manual') NOT NULL DEFAULT 'hikconnect',
  -- Hik-Connect: the device serial + channel the stream/snapshot is fetched for
  device_serial VARCHAR(64)   NULL,
  channel_no    INT UNSIGNED  NOT NULL DEFAULT 1,
  -- device verification code, needed by Hik-Connect for an encrypted stream.
  -- Sensitive — never returned to the browser (mapOut omits it).
  verify_code   VARCHAR(64)   NULL,
  -- manual provider: directly-playable HLS + snapshot URLs
  stream_url    VARCHAR(500)  NULL,
  snapshot_url  VARCHAR(500)  NULL,
  status        ENUM('active','inactive') NOT NULL DEFAULT 'active',
  sort_order    INT           NOT NULL DEFAULT 0,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_cameras_farm (farm_id, sort_order),
  CONSTRAINT fk_cameras_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Cached Hik-Connect access token (valid ~days) so we don't re-authenticate on
-- every view. Single row (id = 1), upserted on refresh. Shared platform-wide
-- because the operator holds ONE Hik-Connect account for all farms.
CREATE TABLE IF NOT EXISTS hik_token (
  id           TINYINT UNSIGNED PRIMARY KEY DEFAULT 1,
  access_token VARCHAR(255)  NOT NULL,
  -- some Hik-Connect accounts return a per-account API area/domain to call for
  -- subsequent requests; kept here when present.
  area_domain  VARCHAR(191)  NULL,
  expires_at   TIMESTAMP     NOT NULL,
  updated_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;