CREATE TABLE IF NOT EXISTS "user" (
  "id" TEXT PRIMARY KEY NOT NULL,
  "name" TEXT,
  "email" TEXT NOT NULL,
  "email_verified" INTEGER NOT NULL DEFAULT 0,
  "image" TEXT,
  "created_at" INTEGER NOT NULL,
  "updated_at" INTEGER NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS "user_email_unique" ON "user" ("email");

CREATE TABLE IF NOT EXISTS "session" (
  "id" TEXT PRIMARY KEY NOT NULL,
  "expires_at" INTEGER NOT NULL,
  "token" TEXT NOT NULL,
  "created_at" INTEGER NOT NULL,
  "updated_at" INTEGER NOT NULL,
  "ip_address" TEXT,
  "user_agent" TEXT,
  "user_id" TEXT NOT NULL,
  FOREIGN KEY ("user_id") REFERENCES "user"("id")
);

CREATE UNIQUE INDEX IF NOT EXISTS "session_token_unique" ON "session" ("token");
CREATE INDEX IF NOT EXISTS "session_user_id_idx" ON "session" ("user_id");

CREATE TABLE IF NOT EXISTS "account" (
  "id" TEXT PRIMARY KEY NOT NULL,
  "account_id" TEXT NOT NULL,
  "provider_id" TEXT NOT NULL,
  "user_id" TEXT NOT NULL,
  "access_token" TEXT,
  "refresh_token" TEXT,
  "id_token" TEXT,
  "access_token_expires_at" INTEGER,
  "refresh_token_expires_at" INTEGER,
  "scope" TEXT,
  "password" TEXT,
  "created_at" INTEGER NOT NULL,
  "updated_at" INTEGER NOT NULL,
  FOREIGN KEY ("user_id") REFERENCES "user"("id")
);

CREATE INDEX IF NOT EXISTS "account_user_id_idx" ON "account" ("user_id");
CREATE INDEX IF NOT EXISTS "account_provider_account_idx" ON "account" ("provider_id", "account_id");

CREATE TABLE IF NOT EXISTS "verification" (
  "id" TEXT PRIMARY KEY NOT NULL,
  "identifier" TEXT NOT NULL,
  "value" TEXT NOT NULL,
  "expires_at" INTEGER NOT NULL,
  "created_at" INTEGER,
  "updated_at" INTEGER
);

CREATE INDEX IF NOT EXISTS "verification_identifier_idx" ON "verification" ("identifier");

CREATE TABLE IF NOT EXISTS "apikey" (
  "id" TEXT PRIMARY KEY NOT NULL,
  "config_id" TEXT,
  "name" TEXT,
  "start" TEXT,
  "prefix" TEXT,
  "key" TEXT NOT NULL,
  "reference_id" TEXT,
  "enabled" INTEGER DEFAULT 1,
  "rate_limit_enabled" INTEGER DEFAULT 1,
  "rate_limit_time_window" INTEGER,
  "rate_limit_max" INTEGER,
  "request_count" INTEGER DEFAULT 0,
  "remaining" INTEGER,
  "refill_interval" INTEGER,
  "refill_amount" INTEGER,
  "last_refill_at" INTEGER,
  "last_request" INTEGER,
  "permissions" TEXT,
  "metadata" TEXT,
  "expires_at" INTEGER,
  "created_at" INTEGER NOT NULL,
  "updated_at" INTEGER NOT NULL,
  "avatar_id" TEXT,
  FOREIGN KEY ("avatar_id") REFERENCES "avatars"("id") ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS "apikey_reference_id_idx" ON "apikey" ("reference_id");
CREATE INDEX IF NOT EXISTS "apikey_key_idx" ON "apikey" ("key");

-- 形象/表情包集合表
CREATE TABLE IF NOT EXISTS "avatars" (
  "id" TEXT PRIMARY KEY NOT NULL,
  "user_id" TEXT NOT NULL,
  "name" TEXT NOT NULL,
  "description" TEXT,
  "created_at" INTEGER NOT NULL,
  "updated_at" INTEGER NOT NULL,
  FOREIGN KEY ("user_id") REFERENCES "user"("id") ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS "avatars_user_id_idx" ON "avatars" ("user_id");

-- 表情表
CREATE TABLE IF NOT EXISTS "emojis" (
  "id" TEXT PRIMARY KEY NOT NULL,
  "avatar_id" TEXT NOT NULL,
  "label" TEXT NOT NULL,
  "url" TEXT NOT NULL,
  "description" TEXT,
  "trigger_type" TEXT NOT NULL DEFAULT 'message',
  "trigger_result" TEXT,
  "sort_order" INTEGER DEFAULT 0,
  "created_at" INTEGER NOT NULL,
  FOREIGN KEY ("avatar_id") REFERENCES "avatars"("id") ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS "emojis_avatar_id_idx" ON "emojis" ("avatar_id");
CREATE UNIQUE INDEX IF NOT EXISTS "emojis_avatar_default_unique_idx" ON "emojis" ("avatar_id") WHERE "trigger_type" = 'default';

CREATE TABLE IF NOT EXISTS "user_file" (
  "id" TEXT PRIMARY KEY NOT NULL,
  "user_id" TEXT NOT NULL,
  "filename" TEXT NOT NULL,
  "original_name" TEXT NOT NULL,
  "content_type" TEXT NOT NULL,
  "size" INTEGER NOT NULL,
  "s3_key" TEXT NOT NULL,
  "url" TEXT NOT NULL,
  "uploaded_at" INTEGER NOT NULL,
  FOREIGN KEY ("user_id") REFERENCES "user"("id") ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS "user_file_user_id_idx" ON "user_file" ("user_id");