-- BlindMaster schema. Idempotent: safe on fresh DB and replays cleanly against
-- an existing one. Source of truth for table structure, constraints, indexes,
-- and triggers. Restored from oldVersion.sql backup + newer additions.

-- ── Trigger: auto-delete empty groups ───────────────────────────────────────
CREATE OR REPLACE FUNCTION public.delete_group_if_empty() RETURNS trigger
    LANGUAGE plpgsql AS $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM group_peripherals WHERE group_id = OLD.group_id) THEN
    DELETE FROM groups WHERE id = OLD.group_id;
  END IF;
  RETURN OLD;
END;
$$;

-- ── Core auth ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id                    INTEGER      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email                 TEXT         NOT NULL UNIQUE,
  password_hash_string  TEXT         NOT NULL,
  name                  TEXT,
  verification_token    TEXT,
  is_verified           BOOLEAN      NOT NULL DEFAULT FALSE,
  timezone              TEXT         DEFAULT 'America/Chicago',
  apns_token            TEXT,
  created_at            TIMESTAMPTZ  DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS users_id_idx ON users(id);

-- UNLOGGED: ephemeral session state — no WAL writes, cleared on crash (correct
-- behaviour; clients reconnect anyway). One row per user enforced by PK.
CREATE UNLOGGED TABLE IF NOT EXISTS user_tokens (
  user_id    INTEGER  PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  token      TEXT     NOT NULL,
  connected  BOOLEAN  DEFAULT FALSE,
  socket     VARCHAR(255)
);

-- Convert to UNLOGGED if the table already exists as a logged table.
-- user_tokens holds only live socket state; clearing it on restart is correct.
DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM pg_class WHERE relname = 'user_tokens' AND relpersistence = 'p') THEN
    ALTER TABLE user_tokens SET UNLOGGED;
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_user_tokens_token             ON user_tokens(token);
CREATE INDEX IF NOT EXISTS idx_user_tokens_active_socket     ON user_tokens(user_id, socket) WHERE connected = true;
CREATE INDEX IF NOT EXISTS idx_user_tokens_socket_connected  ON user_tokens(socket) WHERE connected = true;

CREATE TABLE IF NOT EXISTS password_reset_tokens (
  email       TEXT         PRIMARY KEY,
  token       TEXT         NOT NULL,
  expires_at  TIMESTAMPTZ  NOT NULL,
  created_at  TIMESTAMPTZ  DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS user_pending_emails (
  user_id        INTEGER      PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  pending_email  TEXT         NOT NULL,
  token          TEXT         NOT NULL,
  expires_at     TIMESTAMPTZ  NOT NULL
);

-- ── Hardware: hubs (devices) and their controlled blinds (peripherals) ───────
CREATE TABLE IF NOT EXISTS devices (
  id           INTEGER  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id      INTEGER  NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  device_name  TEXT     NOT NULL,
  max_ports    INTEGER  NOT NULL DEFAULT 4,
  battery_soc  SMALLINT,
  timezone     TEXT,
  UNIQUE (user_id, device_name)
);

CREATE INDEX IF NOT EXISTS idx_devices_user_id   ON devices(user_id);
CREATE INDEX IF NOT EXISTS idx_devices_max_ports ON devices(max_ports);

-- UNLOGGED: ephemeral device connection state, same rationale as user_tokens.
CREATE UNLOGGED TABLE IF NOT EXISTS device_tokens (
  device_id  INTEGER      NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
  token      TEXT         NOT NULL,
  connected  BOOLEAN      DEFAULT FALSE,
  socket     VARCHAR(255)
);

DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM pg_class WHERE relname = 'device_tokens' AND relpersistence = 'p') THEN
    ALTER TABLE device_tokens SET UNLOGGED;
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_device_tokens_token           ON device_tokens(token);
CREATE INDEX IF NOT EXISTS idx_device_tokens_device_id_connected ON device_tokens(device_id) WHERE connected = true;
CREATE INDEX IF NOT EXISTS idx_device_tokens_active_socket   ON device_tokens(device_id, socket) WHERE connected = true;
CREATE INDEX IF NOT EXISTS idx_device_tokens_socket_connected ON device_tokens(socket) WHERE connected = true;

-- autovacuum tuned aggressively: last_pos is updated on every blind movement.
CREATE TABLE IF NOT EXISTS peripherals (
  id                 INTEGER      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  device_id          INTEGER      NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
  user_id            INTEGER      NOT NULL REFERENCES users(id)   ON DELETE CASCADE,
  peripheral_number  INTEGER      NOT NULL,
  peripheral_name    TEXT         NOT NULL,
  calibrated         BOOLEAN      DEFAULT FALSE,
  await_calib        BOOLEAN      DEFAULT FALSE,
  last_pos           INTEGER      DEFAULT 0 CHECK (last_pos >= 0 AND last_pos <= 10),
  last_set           TIMESTAMPTZ,
  UNIQUE (device_id, peripheral_number),
  UNIQUE (device_id, peripheral_name)
) WITH (autovacuum_vacuum_scale_factor = 0.02);

CREATE INDEX IF NOT EXISTS idx_peripherals_device_id ON peripherals(device_id);
CREATE INDEX IF NOT EXISTS idx_peripherals_id_user   ON peripherals(id, user_id);

-- ── Multi-blind groups ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS groups (
  id          INTEGER      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id     INTEGER      NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name        TEXT         NOT NULL,
  timezone    TEXT,
  created_at  TIMESTAMPTZ  DEFAULT NOW(),
  UNIQUE (user_id, name)
);

CREATE INDEX IF NOT EXISTS idx_groups_user_id ON groups(user_id);

CREATE TABLE IF NOT EXISTS group_peripherals (
  group_id       INTEGER  NOT NULL REFERENCES groups(id)      ON DELETE CASCADE,
  peripheral_id  INTEGER  NOT NULL REFERENCES peripherals(id) ON DELETE CASCADE,
  PRIMARY KEY (group_id, peripheral_id)
);

CREATE INDEX IF NOT EXISTS idx_group_peripherals_group_id      ON group_peripherals(group_id);
CREATE INDEX IF NOT EXISTS idx_group_peripherals_peripheral_id ON group_peripherals(peripheral_id);

-- Trigger fires after any peripheral is removed from a group; deletes the group
-- itself if it is now empty.
DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_trigger WHERE tgname = 'trigger_cleanup_empty_groups'
  ) THEN
    CREATE TRIGGER trigger_cleanup_empty_groups
      AFTER DELETE ON group_peripherals
      FOR EACH ROW EXECUTE FUNCTION public.delete_group_if_empty();
  END IF;
END $$;

-- ── Schedules ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS schedules (
  id                INTEGER       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id           INTEGER       NOT NULL REFERENCES users(id)       ON DELETE CASCADE,
  schedule_type     VARCHAR(50)   NOT NULL CHECK (schedule_type IN ('peripheral', 'group')),
  peripheral_id     INTEGER       REFERENCES peripherals(id)          ON DELETE CASCADE,
  device_id         INTEGER       REFERENCES devices(id)              ON DELETE CASCADE,
  peripheral_number INTEGER,
  group_id          INTEGER       REFERENCES groups(id)               ON DELETE CASCADE,
  target_position   INTEGER       NOT NULL,
  cron_expression   VARCHAR(100)  NOT NULL,
  cron_minute       VARCHAR(50)   NOT NULL,
  cron_hour         VARCHAR(50)   NOT NULL,
  cron_days         VARCHAR(50)   NOT NULL,
  created_at        TIMESTAMPTZ   DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMPTZ   DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT peripheral_or_group CHECK (
    (schedule_type = 'peripheral' AND peripheral_id IS NOT NULL AND group_id IS NULL) OR
    (schedule_type = 'group'      AND group_id IS NOT NULL      AND peripheral_id IS NULL)
  )
);

CREATE INDEX IF NOT EXISTS idx_schedules_user_id ON schedules(user_id);

-- Prevent duplicate cron times for the same target.
CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_peripheral_schedule
  ON schedules(peripheral_id, cron_expression)
  WHERE schedule_type = 'peripheral';

CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_group_schedule
  ON schedules(group_id, cron_expression)
  WHERE schedule_type = 'group';

-- ── Idempotent column additions for older DBs ────────────────────────────────
ALTER TABLE users    ADD COLUMN IF NOT EXISTS apns_token       TEXT;
ALTER TABLE users    ADD COLUMN IF NOT EXISTS timezone         TEXT DEFAULT 'America/Chicago';
ALTER TABLE users    ADD COLUMN IF NOT EXISTS verification_token TEXT;
ALTER TABLE users    ADD COLUMN IF NOT EXISTS is_verified      BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE users    ADD COLUMN IF NOT EXISTS created_at       TIMESTAMPTZ DEFAULT NOW();
ALTER TABLE devices  ADD COLUMN IF NOT EXISTS battery_soc      SMALLINT;
ALTER TABLE devices  ADD COLUMN IF NOT EXISTS timezone         TEXT;
ALTER TABLE groups   ADD COLUMN IF NOT EXISTS timezone         TEXT;