BEGIN;

ALTER TABLE dealer_users ADD COLUMN IF NOT EXISTS password_hash text;
ALTER TABLE dealer_users ADD COLUMN IF NOT EXISTS email_verified_at timestamptz;
ALTER TABLE dealer_users ADD COLUMN IF NOT EXISTS password_updated_at timestamptz;
ALTER TABLE dealer_users ADD COLUMN IF NOT EXISTS failed_login_attempts integer NOT NULL DEFAULT 0;
ALTER TABLE dealer_users ADD COLUMN IF NOT EXISTS locked_until timestamptz;

-- Existing active users previously proved control of their email through the one-time-link flow.
UPDATE dealer_users SET email_verified_at = coalesce(email_verified_at, last_signed_in_at, now())
WHERE auth_status = 'active' AND email_verified_at IS NULL;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'dealer_users_failed_login_attempts_check'
  ) THEN
    ALTER TABLE dealer_users ADD CONSTRAINT dealer_users_failed_login_attempts_check
      CHECK (failed_login_attempts >= 0);
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS dealer_users_locked_idx ON dealer_users (locked_until)
  WHERE locked_until IS NOT NULL;

COMMIT;