-- ────────────────────────────────────────────────────────────────────────────
-- Addendum: Video Scene Maps (video_timestamp → room label mapping)
-- Appended to schema.sql for Sprint 1 milestone.
-- ────────────────────────────────────────────────────────────────────────────

-- TABLE: video_scene_maps
-- Stores the timestamp-to-room mapping for each marketing video.
-- Uploaded once per inventory item (CSV parsed and inserted by the API).
-- Format: scene_no, start_ms, end_ms, room_type, description
-- This allows NemoClaw to correlate a biometric reaction at T=45000ms with
-- "Master Bedroom" for contextual QD scoring.

CREATE TABLE IF NOT EXISTS video_scene_maps (
  id             UUID      PRIMARY KEY DEFAULT gen_random_uuid(),
  video_asset_id TEXT      NOT NULL,  -- Matches inventory item slug / asset filename
  scene_no       INTEGER   NOT NULL,
  start_ms       BIGINT    NOT NULL,
  end_ms         BIGINT    NOT NULL,
  room_type      TEXT      NOT NULL,  -- e.g. 'Living Room', 'Master Bedroom', 'Balcony'
  description    TEXT,                -- Optional: 'Ocean-facing balcony with pool view'
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (video_asset_id, scene_no)
);

CREATE INDEX IF NOT EXISTS idx_scenes_asset_range
  ON video_scene_maps (video_asset_id, start_ms, end_ms);

-- ────────────────────────────────────────────────────────────────────────────
-- TABLE: perception_sessions
-- Tracks each PerceptionPlayer session (assigned or auto mode).
-- Assigned Mode: lead_id is set before session starts.
-- Auto Mode    : lead_id is NULL; auto_mode_matched_at populated post hoc.
-- ────────────────────────────────────────────────────────────────────────────

DO $$ BEGIN
  CREATE TYPE session_mode_enum AS ENUM ('assigned', 'auto');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

CREATE TABLE IF NOT EXISTS perception_sessions (
  id                    UUID              PRIMARY KEY DEFAULT gen_random_uuid(),
  session_mode          session_mode_enum NOT NULL DEFAULT 'assigned',
  lead_id               UUID              REFERENCES leads_intelligence(id) ON DELETE SET NULL,
  video_asset_id        TEXT              NOT NULL,
  started_at            TIMESTAMPTZ       NOT NULL DEFAULT NOW(),
  ended_at              TIMESTAMPTZ,
  final_qd_score        INTEGER           CHECK (final_qd_score BETWEEN 1 AND 100),
  -- For auto mode: the lead_id matched after session by face/plate recognition
  auto_mode_matched_at  TIMESTAMPTZ,
  -- JSONB blob with auto-mode gathered data: face_hash, plate, vehicle_class, etc.
  auto_mode_evidence    JSONB             DEFAULT '{}',
  broker_user_id        UUID              REFERENCES users_and_roles(id) ON DELETE SET NULL,
  created_at            TIMESTAMPTZ       NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sessions_lead ON perception_sessions (lead_id, started_at DESC);
CREATE INDEX IF NOT EXISTS idx_sessions_unmatched
  ON perception_sessions (started_at DESC)
  WHERE session_mode = 'auto' AND lead_id IS NULL;

-- ────────────────────────────────────────────────────────────────────────────
-- TABLE: cctv_events
-- Records each parking/entry visitor event from CCTV feeds.
-- License plates, vehicle class, NemoClaw wealth indicator.
-- ────────────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS cctv_events (
  id                UUID      PRIMARY KEY DEFAULT gen_random_uuid(),
  zone              TEXT      NOT NULL,         -- 'Parking Entry', 'Main Gate', 'Zone A', etc.
  license_plate     TEXT,                       -- Raw OCR text
  vehicle_class     TEXT,                       -- 'luxury' | 'standard' | 'unknown'
  wealth_indicator  TEXT,                       -- 'HNI' | 'standard' | 'unknown'
  nemoclaw_tags     TEXT[]    NOT NULL DEFAULT '{}',
  nemoclaw_notes    TEXT,
  linked_lead_id    UUID      REFERENCES leads_intelligence(id) ON DELETE SET NULL,
  linked_session_id UUID      REFERENCES perception_sessions(id) ON DELETE SET NULL,
  captured_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  raw_payload       JSONB     NOT NULL DEFAULT '{}'  -- Full CCTV frame metadata
);

CREATE INDEX IF NOT EXISTS idx_cctv_plate ON cctv_events (license_plate, captured_at DESC);
CREATE INDEX IF NOT EXISTS idx_cctv_zone  ON cctv_events (zone, captured_at DESC);
CREATE INDEX IF NOT EXISTS idx_cctv_unlinked
  ON cctv_events (captured_at DESC)
  WHERE linked_lead_id IS NULL;