BEGIN;

ALTER TABLE app_user
  ADD COLUMN IF NOT EXISTS role TEXT NOT NULL DEFAULT 'USER';

CREATE INDEX IF NOT EXISTS idx_app_user_role ON app_user(role);

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'chk_app_user_role') THEN
    ALTER TABLE app_user
      ADD CONSTRAINT chk_app_user_role
      CHECK (role IN ('USER','ADMIN','SUPER_ADMIN'));
  END IF;
END $$;

UPDATE app_user
  SET role = 'SUPER_ADMIN'
WHERE role <> 'SUPER_ADMIN' AND COALESCE(is_super_admin, false) = true;

UPDATE app_user
  SET role = 'ADMIN'
WHERE role = 'USER' AND COALESCE(is_admin, false) = true;

DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'app_user' AND column_name = 'is_admin'
  ) THEN
    UPDATE app_user
      SET is_admin = (role IN ('ADMIN','SUPER_ADMIN'))
    WHERE is_admin IS DISTINCT FROM (role IN ('ADMIN','SUPER_ADMIN'));
  END IF;
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'app_user' AND column_name = 'is_super_admin'
  ) THEN
    UPDATE app_user
      SET is_super_admin = (role = 'SUPER_ADMIN')
    WHERE is_super_admin IS DISTINCT FROM (role = 'SUPER_ADMIN');
  END IF;
END $$;

CREATE TABLE IF NOT EXISTS admin_role_audit (
  id BIGSERIAL PRIMARY KEY,
  actor_user_id TEXT NOT NULL,
  target_user_id TEXT NOT NULL,
  old_role TEXT NOT NULL,
  new_role TEXT NOT NULL,
  changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION prevent_super_admin_delete()
RETURNS trigger AS $$
BEGIN
  IF OLD.role = 'SUPER_ADMIN' OR OLD.is_super_admin THEN
    RAISE EXCEPTION 'cannot delete super admin user';
  END IF;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

DROP VIEW IF EXISTS admin_user_metrics;
CREATE OR REPLACE VIEW admin_user_metrics AS
WITH session_stats AS (
  SELECT
    user_id,
    MIN(created_at) AS first_session_at,
    MAX(COALESCE(last_seen_at, created_at)) AS last_login_at
  FROM app_session
  GROUP BY user_id
),
run_stats AS (
  SELECT
    user_id,
    COUNT(*) AS runs_count,
    MAX(CASE WHEN status = 'RUNNING' THEN run_id END) AS active_run_id,
    MAX(CASE WHEN status = 'RUNNING' THEN status END) AS active_run_status,
    MIN(created_at) AS first_run_at
  FROM strategy_run
  GROUP BY user_id
),
broker_stats AS (
  SELECT user_id, BOOL_OR(connected) AS broker_connected
  FROM user_broker
  GROUP BY user_id
)
SELECT
  u.id AS user_id,
  u.username,
  u.role,
  (u.role IN ('ADMIN','SUPER_ADMIN')) AS is_admin,
  COALESCE(session_stats.first_session_at, run_stats.first_run_at) AS created_at,
  session_stats.last_login_at,
  COALESCE(run_stats.runs_count, 0) AS runs_count,
  run_stats.active_run_id,
  run_stats.active_run_status,
  COALESCE(broker_stats.broker_connected, FALSE) AS broker_connected
FROM app_user u
LEFT JOIN session_stats ON session_stats.user_id = u.id
LEFT JOIN run_stats ON run_stats.user_id = u.id
LEFT JOIN broker_stats ON broker_stats.user_id = u.id;

COMMIT;