
ALTER TABLE auth.login_attempt_tracker
ADD COLUMN IF NOT EXISTS success_count integer;

ALTER TABLE auth.login_attempt_tracker
ADD COLUMN IF NOT EXISTS last_success_at timestamptz;

ALTER TABLE auth.login_attempt_tracker
ADD COLUMN IF NOT EXISTS last_result text;

UPDATE auth.login_attempt_tracker
SET success_count = COALESCE(success_count, 0),
    last_result = COALESCE(NULLIF(BTRIM(last_result), ''), 'failure')
WHERE success_count IS NULL
   OR last_result IS NULL
   OR BTRIM(last_result) = '';

ALTER TABLE auth.login_attempt_tracker
ALTER COLUMN success_count SET DEFAULT 0;

ALTER TABLE auth.login_attempt_tracker
ALTER COLUMN success_count SET NOT NULL;

ALTER TABLE auth.login_attempt_tracker
ALTER COLUMN last_result SET DEFAULT 'failure';

ALTER TABLE auth.login_attempt_tracker
ALTER COLUMN last_result SET NOT NULL;

ALTER TABLE auth.login_attempt_tracker
DROP CONSTRAINT IF EXISTS login_attempt_tracker_last_result_check;

ALTER TABLE auth.login_attempt_tracker
ADD CONSTRAINT login_attempt_tracker_last_result_check
CHECK (last_result IN ('failure', 'success'));

COMMENT ON COLUMN auth.login_attempt_tracker.success_count IS
'Conteo acumulado de logins exitosos para el trio business_id + user_name + ip_address sin alterar el rate limit de fallos.';

COMMENT ON COLUMN auth.login_attempt_tracker.last_success_at IS
'Fecha y hora del ultimo login exitoso observado para la fila agregada del tracker.';

COMMENT ON COLUMN auth.login_attempt_tracker.last_result IS
'Resultado mas reciente observado por el tracker agregado. Valores permitidos: failure, success.';
