-- 1. Tell Snowflake WHERE to work
USE WAREHOUSE CROSSWALK_WH;
USE DATABASE SMART_CITY;
USE SCHEMA TRAFFIC_LOGS;

-- 2. Create the tables
CREATE TABLE IF NOT EXISTS CROSSING_LOGS (
  event_id           STRING        NOT NULL DEFAULT uuid_string(),
  timestamp          TIMESTAMP_NTZ NOT NULL DEFAULT current_timestamp(),
  pedestrian_type    STRING,
  duration_seconds   FLOAT,
  was_light_extended BOOLEAN,
  persons_count      INT,
  confidence_pct     FLOAT,
  notes              TEXT
);

CREATE TABLE IF NOT EXISTS JAYWALKING_VIOLATIONS (
  violation_id   STRING        NOT NULL DEFAULT uuid_string(),
  timestamp      TIMESTAMP_NTZ NOT NULL DEFAULT current_timestamp(),
  severity       STRING,                -- WARNING | VIOLATION | INFO
  description    TEXT,
  image_data     TEXT,                  -- base-64 encoded PNG frame
  image_filename STRING,               -- suggested download filename
  pedestrian_id  STRING,
  location       STRING DEFAULT 'Hen-Tersection Unit'
);

SELECT timestamp, severity, description
FROM SMART_CITY.TRAFFIC_LOGS.JAYWALKING_VIOLATIONS
WHERE description LIKE 'INTEGRATION_TEST%'
ORDER BY timestamp DESC
LIMIT 5;