-- Deploy webserver:00000000 to pg
BEGIN;

CREATE EXTENSION IF NOT EXISTS pg_cron;

CREATE SCHEMA IF NOT EXISTS audit;

CREATE TYPE audit.operation_type AS ENUM ('insert', 'update', 'delete');

CREATE TABLE IF NOT EXISTS public.users (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    name VARCHAR(32) NOT NULL UNIQUE,
    email VARCHAR(64) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS audit.users_history (
    id          UUID                    PRIMARY KEY DEFAULT uuidv7(),
    entity_id   UUID                    NOT NULL,
    operation   audit.operation_type    NOT NULL,
    value       JSONB,
    changed_at  TIMESTAMPTZ             NOT NULL DEFAULT NOW(),
    changed_by  UUID
);

-- Generic trigger function that records row changes to a paired history table.
-- The history table and schema are passed via TG_ARGV[0] and TG_ARGV[1].
CREATE OR REPLACE FUNCTION audit.record_history()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    history_schema TEXT := TG_ARGV[0];
    history_table  TEXT := TG_ARGV[1];
    history_id UUID := uuidv7();
    entity_row RECORD;
    op audit.operation_type;
    v_user UUID;
BEGIN
    BEGIN
        v_user := current_setting('app.current_user_id', true)::UUID;
    EXCEPTION WHEN OTHERS THEN v_user := NULL; END;

    op := lower(TG_OP);
    IF TG_OP = 'DELETE' THEN
        entity_row := OLD;
    ELSE
        entity_row := NEW;
    END IF;

    EXECUTE format(
        'INSERT INTO %I.%I (id, entity_id, operation, value, changed_at, changed_by) VALUES ($1, $2, $3, $4, NOW(), $5)',
        history_schema, history_table
    ) USING history_id, entity_row.id, op, row_to_json(entity_row)::jsonb, v_user;

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$;

-- Trigger on public.users -> audit.users_history
CREATE TRIGGER trg_users_history
    AFTER INSERT OR UPDATE OR DELETE ON public.users
    FOR EACH ROW
    EXECUTE FUNCTION audit.record_history('audit', 'users_history');

CREATE SCHEMA IF NOT EXISTS auth;

CREATE TABLE IF NOT EXISTS auth.auth (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    user_id UUID NOT NULL REFERENCES users ON DELETE CASCADE,
    password VARCHAR NOT NULL
);

CREATE SCHEMA IF NOT EXISTS cache;

CREATE UNLOGGED TABLE IF NOT EXISTS cache.sessions (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    token VARCHAR NOT NULL,
    expires_on TIMESTAMPTZ NOT NULL,
    user_id UUID REFERENCES users ON DELETE CASCADE
);

CREATE UNLOGGED TABLE IF NOT EXISTS cache.rate_limits (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    key TEXT NOT NULL UNIQUE,
    hits INT NOT NULL DEFAULT 1,
    window_start TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE OR REPLACE PROCEDURE expire_caches ()
LANGUAGE plpgsql
AS
$$
BEGIN
    DELETE FROM cache.sessions
    WHERE expires_on < NOW();

    DELETE FROM cache.rate_limits
    WHERE window_start < NOW() - INTERVAL '1 hour';

    COMMIT;
END;
$$;

SELECT cron.schedule('cache_cleanup', '0 * * * *', $$CALL expire_caches();$$);

COMMIT;