-- 001_push_schema.sql
-- Requires: CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE SCHEMA IF NOT EXISTS push;

CREATE TABLE IF NOT EXISTS push.devices (
    device_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    mitglieder_id INTEGER NOT NULL,
    device_token TEXT NOT NULL UNIQUE,
    device_name TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    paired_at TIMESTAMP DEFAULT NOW(),
    last_seen TIMESTAMP,
    CONSTRAINT fk_mitglied
        FOREIGN KEY (mitglieder_id)
        REFERENCES public.rhs_mitglieder(mitglieder_id)
        ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS push.webpush_subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id UUID NOT NULL,
    endpoint TEXT NOT NULL UNIQUE,
    p256dh TEXT NOT NULL,
    auth TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    last_success TIMESTAMP,
    CONSTRAINT fk_device
        FOREIGN KEY (device_id)
        REFERENCES push.devices(device_id)
        ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS push.status_current (
    mitglieder_id INTEGER PRIMARY KEY,
    status INTEGER NOT NULL CHECK (status IN (1,2,3,4,6)),
    changed_at TIMESTAMP DEFAULT NOW(),
    changed_by TEXT
);

CREATE TABLE IF NOT EXISTS push.status_log (
    id BIGSERIAL PRIMARY KEY,
    mitglieder_id INTEGER NOT NULL,
    status INTEGER NOT NULL CHECK (status IN (1,2,3,4,6)),
    changed_at TIMESTAMP DEFAULT NOW(),
    changed_by TEXT
);

CREATE TABLE IF NOT EXISTS push.pairing_codes (
    code VARCHAR(6) PRIMARY KEY,
    mitglieder_id INTEGER NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    used_at TIMESTAMP
);

-- Helpful indexes
CREATE INDEX IF NOT EXISTS idx_status_log_mid_time ON push.status_log(mitglieder_id, changed_at DESC);
