ALTER TABLE "tenants"
  ADD COLUMN "settings" jsonb DEFAULT '{"locale":"en-ZA","timeZone":"Africa/Johannesburg"}'::jsonb NOT NULL;
--> statement-breakpoint
ALTER TABLE "workspaces"
  ADD COLUMN "settings" jsonb DEFAULT '{"locale":"en-ZA","timeZone":"Africa/Johannesburg","dateStyle":"medium"}'::jsonb NOT NULL;
--> statement-breakpoint
ALTER TABLE "users"
  ADD COLUMN "platform_administrator" boolean DEFAULT false NOT NULL,
  ADD COLUMN "locale" varchar(20) DEFAULT 'en-ZA' NOT NULL,
  ADD COLUMN "time_zone" varchar(80) DEFAULT 'Africa/Johannesburg' NOT NULL,
  ADD COLUMN "email_verified_at" timestamp with time zone;
--> statement-breakpoint
ALTER TABLE "tenant_memberships"
  ADD CONSTRAINT "tenant_memberships_role_check"
  CHECK ("role" IN (
    'organisation_administrator',
    'analyst',
    'responder',
    'publisher',
    'approver',
    'executive',
    'read_only_client'
  ));
--> statement-breakpoint
ALTER TABLE "audit_events"
  ADD COLUMN "workspace_id" uuid;
--> statement-breakpoint
ALTER TABLE "audit_events"
  ADD CONSTRAINT "audit_events_tenant_workspace_fk"
  FOREIGN KEY ("tenant_id", "workspace_id")
  REFERENCES "public"."workspaces"("tenant_id", "id")
  ON DELETE restrict ON UPDATE no action;
--> statement-breakpoint
CREATE INDEX "audit_events_workspace_occurred_idx"
  ON "audit_events" USING btree ("tenant_id", "workspace_id", "occurred_at");
--> statement-breakpoint
CREATE TABLE "password_credentials" (
  "user_id" uuid PRIMARY KEY NOT NULL,
  "password_hash" text NOT NULL,
  "password_changed_at" timestamp with time zone DEFAULT now() NOT NULL,
  "failed_attempts" integer DEFAULT 0 NOT NULL,
  "locked_until" timestamp with time zone,
  CONSTRAINT "password_credentials_user_id_fk"
    FOREIGN KEY ("user_id") REFERENCES "public"."users"("id")
    ON DELETE cascade ON UPDATE no action
);
--> statement-breakpoint
CREATE TABLE "sessions" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "token_hash" varchar(64) NOT NULL,
  "user_id" uuid NOT NULL,
  "active_tenant_id" uuid,
  "active_workspace_id" uuid,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  "last_seen_at" timestamp with time zone DEFAULT now() NOT NULL,
  "idle_expires_at" timestamp with time zone NOT NULL,
  "expires_at" timestamp with time zone NOT NULL,
  "revoked_at" timestamp with time zone,
  "user_agent_hash" varchar(64),
  CONSTRAINT "sessions_user_id_fk"
    FOREIGN KEY ("user_id") REFERENCES "public"."users"("id")
    ON DELETE cascade ON UPDATE no action,
  CONSTRAINT "sessions_active_tenant_workspace_fk"
    FOREIGN KEY ("active_tenant_id", "active_workspace_id")
    REFERENCES "public"."workspaces"("tenant_id", "id")
    ON DELETE restrict ON UPDATE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX "sessions_token_hash_unique" ON "sessions" USING btree ("token_hash");
--> statement-breakpoint
CREATE INDEX "sessions_user_active_idx"
  ON "sessions" USING btree ("user_id", "revoked_at", "expires_at");
--> statement-breakpoint
CREATE TABLE "password_reset_tokens" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "user_id" uuid NOT NULL,
  "token_hash" varchar(64) NOT NULL,
  "expires_at" timestamp with time zone NOT NULL,
  "used_at" timestamp with time zone,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  CONSTRAINT "password_reset_tokens_user_id_fk"
    FOREIGN KEY ("user_id") REFERENCES "public"."users"("id")
    ON DELETE cascade ON UPDATE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX "password_reset_tokens_hash_unique"
  ON "password_reset_tokens" USING btree ("token_hash");
--> statement-breakpoint
CREATE TABLE "workspace_memberships" (
  "tenant_id" uuid NOT NULL,
  "workspace_id" uuid NOT NULL,
  "user_id" uuid NOT NULL,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  CONSTRAINT "workspace_memberships_pk" PRIMARY KEY ("tenant_id", "workspace_id", "user_id"),
  CONSTRAINT "workspace_memberships_tenant_workspace_fk"
    FOREIGN KEY ("tenant_id", "workspace_id")
    REFERENCES "public"."workspaces"("tenant_id", "id")
    ON DELETE cascade ON UPDATE no action,
  CONSTRAINT "workspace_memberships_tenant_user_fk"
    FOREIGN KEY ("tenant_id", "user_id")
    REFERENCES "public"."tenant_memberships"("tenant_id", "user_id")
    ON DELETE cascade ON UPDATE no action
);
--> statement-breakpoint
CREATE INDEX "workspace_memberships_user_idx"
  ON "workspace_memberships" USING btree ("user_id", "tenant_id");
--> statement-breakpoint
CREATE TABLE "invitations" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "tenant_id" uuid NOT NULL,
  "workspace_id" uuid,
  "email" varchar(320) NOT NULL,
  "role" varchar(60) NOT NULL,
  "token_hash" varchar(64) NOT NULL,
  "invited_by_user_id" uuid NOT NULL,
  "expires_at" timestamp with time zone NOT NULL,
  "accepted_at" timestamp with time zone,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  CONSTRAINT "invitations_role_check"
    CHECK ("role" IN (
      'organisation_administrator',
      'analyst',
      'responder',
      'publisher',
      'approver',
      'executive',
      'read_only_client'
    )),
  CONSTRAINT "invitations_tenant_id_fk"
    FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id")
    ON DELETE cascade ON UPDATE no action,
  CONSTRAINT "invitations_tenant_workspace_fk"
    FOREIGN KEY ("tenant_id", "workspace_id")
    REFERENCES "public"."workspaces"("tenant_id", "id")
    ON DELETE cascade ON UPDATE no action,
  CONSTRAINT "invitations_invited_by_user_id_fk"
    FOREIGN KEY ("invited_by_user_id") REFERENCES "public"."users"("id")
    ON DELETE restrict ON UPDATE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX "invitations_token_hash_unique"
  ON "invitations" USING btree ("token_hash");
--> statement-breakpoint
CREATE INDEX "invitations_tenant_email_idx"
  ON "invitations" USING btree ("tenant_id", "email");
--> statement-breakpoint
ALTER TABLE "workspace_memberships" ENABLE ROW LEVEL SECURITY;
--> statement-breakpoint
ALTER TABLE "invitations" ENABLE ROW LEVEL SECURITY;
--> statement-breakpoint
CREATE POLICY "workspace_memberships_tenant_isolation" ON "workspace_memberships"
  USING (
    "tenant_id" = nullif(current_setting('app.current_tenant_id', true), '')::uuid
  )
  WITH CHECK (
    "tenant_id" = nullif(current_setting('app.current_tenant_id', true), '')::uuid
  );
--> statement-breakpoint
CREATE POLICY "invitations_tenant_isolation" ON "invitations"
  USING (
    "tenant_id" = nullif(current_setting('app.current_tenant_id', true), '')::uuid
  )
  WITH CHECK (
    "tenant_id" = nullif(current_setting('app.current_tenant_id', true), '')::uuid
  );