-- ============================================================================
-- Personal Assistant — authentication schema
-- ============================================================================
-- Run against your Postgres database, e.g.:
--   psql "$DATABASE_URL" -f db/schema.sql
--
-- The matching server code lives in:
--   src/web/auth.py      (langgraph_sdk.Auth JWT verification)
--   src/web/login.py     (/auth/signup, /auth/login custom routes)
-- ============================================================================

BEGIN;

-- ----------------------------------------------------------------------------
-- users
-- ----------------------------------------------------------------------------
-- One row per account. Passwords are stored as bcrypt hashes only — the
-- plaintext password never leaves the /auth/signup handler.
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,
    display_name    TEXT,
    role            TEXT        NOT NULL DEFAULT 'user',
    is_active       BOOLEAN     NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Case-insensitive uniqueness for usernames and emails.
CREATE UNIQUE INDEX IF NOT EXISTS users_username_lower_idx
    ON users (lower(username));

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

-- ----------------------------------------------------------------------------
-- updated_at trigger
-- ----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS users_set_updated_at ON users;
CREATE TRIGGER users_set_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION set_updated_at();

-- ----------------------------------------------------------------------------
-- token_usage
-- ----------------------------------------------------------------------------
-- One row per user per calendar month. ``period_start`` is always the first
-- day of the month (UTC); a new month simply gets a fresh row, which is how
-- the allowance "refreshes" on the first day of every month.
CREATE TABLE IF NOT EXISTS token_usage (
    user_id         UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    period_start    DATE        NOT NULL,
    tokens_used     BIGINT      NOT NULL DEFAULT 0,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, period_start)
);

DROP TRIGGER IF EXISTS token_usage_set_updated_at ON token_usage;
CREATE TRIGGER token_usage_set_updated_at
    BEFORE UPDATE ON token_usage
    FOR EACH ROW
    EXECUTE FUNCTION set_updated_at();

COMMIT;

-- ============================================================================
-- Notes
-- ============================================================================
-- * Requires the pgcrypto extension for gen_random_uuid(). On Postgres 13+
--   it is built in; on older versions run:
--       CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- * The application connects with the DATABASE_URL environment variable,
--   e.g.:  postgresql://user:password@localhost:5432/personal_assistant
-- * JWT signing uses the JWT_SECRET environment variable (HS256).
-- ============================================================================