-- migrate:up
-- Operadores del sistema (admin del bot) + sesiones server-side.
-- No tienen tenant_id porque el sistema es mono-tenant; si en algún momento se
-- vuelve multi-tenant, se agrega y listo.

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE system_users (
  id            BIGSERIAL PRIMARY KEY,
  email         CITEXT UNIQUE NOT NULL,
  name          TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  active        BOOLEAN NOT NULL DEFAULT true,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_login_at TIMESTAMPTZ
);

CREATE TABLE system_sessions (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     BIGINT NOT NULL REFERENCES system_users(id) ON DELETE CASCADE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at  TIMESTAMPTZ NOT NULL,
  ip          INET,
  user_agent  TEXT,
  revoked_at  TIMESTAMPTZ
);
CREATE INDEX system_sessions_user_id_idx ON system_sessions (user_id);
CREATE INDEX system_sessions_expires_at_idx ON system_sessions (expires_at);

-- Trigger para mantener updated_at en system_users.
CREATE OR REPLACE FUNCTION system_users_touch_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER system_users_updated_trigger
BEFORE UPDATE ON system_users
FOR EACH ROW EXECUTE FUNCTION system_users_touch_updated_at();

-- migrate:down
DROP TRIGGER IF EXISTS system_users_updated_trigger ON system_users;
DROP FUNCTION IF EXISTS system_users_touch_updated_at();
DROP TABLE IF EXISTS system_sessions;
DROP TABLE IF EXISTS system_users;