CREATE TABLE "Users" (
  "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "email" VARCHAR(255) UNIQUE NOT NULL,
  "password" VARCHAR(255) NOT NULL,
  "role" VARCHAR(5) DEFAULT 'user' CHECK (role IN ('user', 'admin')),
  "lastLogin" TIMESTAMP WITH TIME ZONE,
  "status" VARCHAR(10) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'suspended')),
  "failedLoginAttempts" INTEGER DEFAULT 0,
  "passwordResetToken" VARCHAR(255),
  "passwordResetExpires" TIMESTAMP WITH TIME ZONE,
  "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  "deletedAt" TIMESTAMP WITH TIME ZONE
);

CREATE INDEX "users_email_idx" ON "Users"("email") WHERE "deletedAt" IS NULL;
CREATE INDEX "users_status_idx" ON "Users"("status") WHERE "deletedAt" IS NULL;
CREATE INDEX "users_role_idx" ON "Users"("role") WHERE "deletedAt" IS NULL;
CREATE INDEX "users_reset_token_idx" ON "Users"("passwordResetToken") WHERE "passwordResetToken" IS NOT NULL;

COMMENT ON TABLE "Users" IS 'Stores user account information with soft delete support';
COMMENT ON COLUMN "Users"."failedLoginAttempts" IS 'Tracks failed login attempts for account security';
COMMENT ON COLUMN "Users"."passwordResetToken" IS 'Token for password reset functionality';