-- migrations/0001_init.sql
-- gen_random_uuid() is core since PostgreSQL 13. Kept for older images.
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS users (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username            TEXT NOT NULL,
    email               TEXT,
    password_hash       TEXT NOT NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    reset_token_hash    TEXT,
    reset_token_expires TIMESTAMPTZ
);

CREATE UNIQUE INDEX IF NOT EXISTS users_username_key
    ON users (lower(username));

CREATE UNIQUE INDEX IF NOT EXISTS users_email_key
    ON users (lower(email)) WHERE email IS NOT NULL;

CREATE TABLE IF NOT EXISTS markers (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    description TEXT,
    lat         DOUBLE PRECISION NOT NULL,
    lon         DOUBLE PRECISION NOT NULL,
    category    TEXT,
    color       TEXT,
    is_shared   BOOLEAN NOT NULL DEFAULT false,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS markers_owner_idx  ON markers (owner_id);
CREATE INDEX IF NOT EXISTS markers_shared_idx ON markers (id) WHERE is_shared;