-- Better Auth organization plugin tables.
-- Adds organization, member, invitation tables used by the organization() plugin.

CREATE TABLE IF NOT EXISTS "organization" (
  "id"          text not null primary key,
  "name"        text not null,
  "slug"        text unique,
  "logo"        text,
  "created_at"  date not null,
  "metadata"    text
);

CREATE TABLE IF NOT EXISTS "member" (
  "id"              text not null primary key,
  "organization_id" text not null references "organization" ("id") on delete cascade,
  "user_id"         text not null references "user" ("id") on delete cascade,
  "role"            text not null,
  "created_at"      date not null
);

CREATE TABLE IF NOT EXISTS "invitation" (
  "id"              text not null primary key,
  "organization_id" text not null references "organization" ("id") on delete cascade,
  "email"           text not null,
  "role"            text,
  "status"          text not null,
  "expires_at"      date not null,
  "inviter_id"      text not null references "user" ("id") on delete cascade
);

CREATE INDEX IF NOT EXISTS "member_organization_id_idx" ON "member" ("organization_id");
CREATE INDEX IF NOT EXISTS "member_user_id_idx"         ON "member" ("user_id");
CREATE INDEX IF NOT EXISTS "invitation_organization_id_idx" ON "invitation" ("organization_id");