-- Local SQLite schema (Room). No server, no accounts: all data is on the device.
-- Times are INTEGER epoch milliseconds UTC; local display uses Location.timezone.

CREATE TABLE locations (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  name          TEXT    NOT NULL,
  admin_area    TEXT,
  country_code  TEXT    NOT NULL,
  lat           REAL    NOT NULL CHECK (lat BETWEEN -90 AND 90),
  lon           REAL    NOT NULL CHECK (lon BETWEEN -180 AND 180),
  timezone      TEXT    NOT NULL,              -- IANA, from geocoding
  is_current    INTEGER NOT NULL DEFAULT 0 CHECK (is_current IN (0,1)),
  sort_order    INTEGER NOT NULL DEFAULT 0,
  created_at    INTEGER NOT NULL,
  updated_at    INTEGER NOT NULL
);
CREATE UNIQUE INDEX locations_one_current ON locations(is_current) WHERE is_current = 1;
CREATE UNIQUE INDEX locations_no_dupe ON locations(round(lat,2), round(lon,2)) WHERE is_current = 0;
CREATE INDEX locations_sort ON locations(sort_order);

CREATE TABLE card_layout (
  location_id INTEGER NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
  card_type   TEXT    NOT NULL CHECK (card_type IN
    ('precip','wind','uv','humidity','pressure','visibility','sun','aqi','pollen','map')),
  position    INTEGER NOT NULL,
  hidden      INTEGER NOT NULL DEFAULT 0 CHECK (hidden IN (0,1)),
  PRIMARY KEY (location_id, card_type)
);
CREATE INDEX card_layout_order ON card_layout(location_id, position);

-- Forecast cache: raw provider JSON + parsed columns the UI and widgets read fast.
CREATE TABLE forecast_cache (
  location_id   INTEGER PRIMARY KEY REFERENCES locations(id) ON DELETE CASCADE,
  fetched_at    INTEGER NOT NULL,
  current_json  TEXT    NOT NULL,
  hourly_json   TEXT    NOT NULL,   -- 48h
  daily_json    TEXT    NOT NULL,   -- 10 days
  air_json      TEXT,               -- AQI + pollen
  source        TEXT    NOT NULL DEFAULT 'open-meteo'
);
CREATE INDEX forecast_fetched ON forecast_cache(fetched_at);

CREATE TABLE alerts (
  id           TEXT    PRIMARY KEY,              -- provider identifier, dedupe key
  location_id  INTEGER NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
  event        TEXT    NOT NULL,
  severity     TEXT    NOT NULL CHECK (severity IN ('minor','moderate','severe','extreme')),
  headline     TEXT    NOT NULL,
  description  TEXT,
  instruction  TEXT,
  agency       TEXT    NOT NULL,
  onset        INTEGER NOT NULL,
  expires      INTEGER NOT NULL CHECK (expires > onset),
  notified     INTEGER NOT NULL DEFAULT 0 CHECK (notified IN (0,1))
);
CREATE INDEX alerts_loc_active ON alerts(location_id, expires);

CREATE TABLE widget_config (
  app_widget_id INTEGER PRIMARY KEY,             -- Android appWidgetId
  location_id   INTEGER REFERENCES locations(id) ON DELETE SET NULL, -- NULL = follow current location
  style         TEXT    NOT NULL DEFAULT 'auto' CHECK (style IN ('auto','dynamic_color','condition','transparent')),
  content       TEXT    NOT NULL DEFAULT 'auto' CHECK (content IN ('auto','now','hourly','daily'))
);
CREATE INDEX widget_loc ON widget_config(location_id);

CREATE TABLE notification_pref (
  location_id  INTEGER PRIMARY KEY REFERENCES locations(id) ON DELETE CASCADE,
  rain_alerts  INTEGER NOT NULL DEFAULT 0,
  severe       INTEGER NOT NULL DEFAULT 1
);

-- Settings live in Jetpack DataStore (typed key-value), not here:
-- temp_unit, precip_unit, wind_unit, pressure_unit, visibility_unit,
-- theme (system|light|dark), dynamic_color, animations, vibrations,
-- brief_enabled, notif_tomorrow_time, data_source.