-- 001: Core schema — users, sessions, RBAC, audit, settings, governorates, status groups

-- +up
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  email VARCHAR(255) UNIQUE,
  full_name VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT FALSE,
  must_change_password BOOLEAN NOT NULL DEFAULT TRUE,
  is_demo BOOLEAN NOT NULL DEFAULT FALSE,
  failed_login_count SMALLINT NOT NULL DEFAULT 0,
  locked_until TIMESTAMPTZ,
  last_login_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by BIGINT REFERENCES users(id),
  updated_by BIGINT REFERENCES users(id)
);

-- Sessions are opaque server-side tokens (sha-256 hash stored, raw token only in cookie)
CREATE TABLE sessions (
  id CHAR(64) PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at TIMESTAMPTZ NOT NULL,
  last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  revoked_at TIMESTAMPTZ,
  ip_address INET,
  user_agent VARCHAR(255)
);
CREATE INDEX idx_sessions_user ON sessions(user_id);
CREATE INDEX idx_sessions_expiry ON sessions(expires_at);

CREATE TABLE roles (
  id BIGSERIAL PRIMARY KEY,
  code VARCHAR(64) NOT NULL UNIQUE,
  name_ar VARCHAR(128) NOT NULL,
  description TEXT,
  is_system BOOLEAN NOT NULL DEFAULT FALSE,
  is_enabled BOOLEAN NOT NULL DEFAULT TRUE,
  -- Roles are defined now but may only be assigned to people after governance confirmation (G-1/Q3)
  governance_confirmed BOOLEAN NOT NULL DEFAULT FALSE,
  requires_scope BOOLEAN NOT NULL DEFAULT FALSE,
  sort_order SMALLINT NOT NULL DEFAULT 100
);

CREATE TABLE permissions (
  id BIGSERIAL PRIMARY KEY,
  code VARCHAR(100) NOT NULL UNIQUE,
  module VARCHAR(50) NOT NULL,
  action VARCHAR(50) NOT NULL,
  description TEXT,
  is_sensitive BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE role_permissions (
  role_id BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  permission_id BIGINT NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
  PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE user_roles (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role_id BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  granted_by BIGINT REFERENCES users(id),
  granted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  revoked_at TIMESTAMPTZ,
  reason VARCHAR(500),
  UNIQUE (user_id, role_id)
);
CREATE INDEX idx_user_roles_role ON user_roles(role_id);

CREATE TABLE user_role_scopes (
  id BIGSERIAL PRIMARY KEY,
  user_role_id BIGINT NOT NULL REFERENCES user_roles(id) ON DELETE CASCADE,
  scope_type VARCHAR(32) NOT NULL CHECK (scope_type IN ('governorate', 'organizational_unit')),
  scope_id BIGINT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_role_id, scope_type, scope_id)
);

-- Append-only audit log; trigger blocks UPDATE/DELETE at the database level
CREATE TABLE audit_logs (
  id BIGSERIAL PRIMARY KEY,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  actor_user_id BIGINT REFERENCES users(id),
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(50),
  entity_id BIGINT,
  changed_fields JSONB,
  ip_address INET,
  user_agent VARCHAR(255),
  context JSONB
);
CREATE INDEX idx_audit_actor_time ON audit_logs(actor_user_id, occurred_at);
CREATE INDEX idx_audit_action ON audit_logs(action);
CREATE INDEX idx_audit_entity ON audit_logs(entity_type, entity_id);

CREATE OR REPLACE FUNCTION forbid_audit_mutation() RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION 'audit_logs is append-only';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_append_only
  BEFORE UPDATE OR DELETE ON audit_logs
  FOR EACH ROW EXECUTE FUNCTION forbid_audit_mutation();

CREATE TABLE settings (
  key VARCHAR(100) PRIMARY KEY,
  value TEXT NOT NULL,
  value_type VARCHAR(20) NOT NULL DEFAULT 'string',
  updated_by BIGINT REFERENCES users(id),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Public reference data (Egypt's 27 governorates) — general geographic reference, not organizational data
CREATE TABLE governorates (
  id SMALLINT PRIMARY KEY,
  code VARCHAR(4) NOT NULL UNIQUE,
  name_ar VARCHAR(64) NOT NULL,
  name_en VARCHAR(64) NOT NULL
);

-- Configurable status machinery (values deliberately NOT seeded: they require governance approval)
CREATE TABLE status_groups (
  id BIGSERIAL PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  name_ar VARCHAR(128) NOT NULL
);

CREATE TABLE statuses (
  id BIGSERIAL PRIMARY KEY,
  group_id BIGINT NOT NULL REFERENCES status_groups(id) ON DELETE CASCADE,
  code VARCHAR(50) NOT NULL,
  name_ar VARCHAR(128) NOT NULL,
  sort_order SMALLINT NOT NULL DEFAULT 100,
  is_terminal BOOLEAN NOT NULL DEFAULT FALSE,
  UNIQUE (group_id, code)
);

CREATE TABLE status_transitions (
  group_id BIGINT NOT NULL REFERENCES status_groups(id) ON DELETE CASCADE,
  from_status_id BIGINT NOT NULL REFERENCES statuses(id) ON DELETE CASCADE,
  to_status_id BIGINT NOT NULL REFERENCES statuses(id) ON DELETE CASCADE,
  requires_permission VARCHAR(100),
  requires_evidence BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (group_id, from_status_id, to_status_id)
);

-- +down
DROP TABLE IF EXISTS status_transitions;
DROP TABLE IF EXISTS statuses;
DROP TABLE IF EXISTS status_groups;
DROP TABLE IF EXISTS governorates;
DROP TABLE IF EXISTS settings;
DROP TRIGGER IF EXISTS trg_audit_append_only ON audit_logs;
DROP FUNCTION IF EXISTS forbid_audit_mutation();
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS user_role_scopes;
DROP TABLE IF EXISTS user_roles;
DROP TABLE IF EXISTS role_permissions;
DROP TABLE IF EXISTS permissions;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS sessions;
DROP TABLE IF EXISTS users;