drop policy "Allow auth'd users with permission to UPDATE" on "public"."accounts";

drop policy "Allow viewing own account or if user is some Admin" on "public"."accounts";

drop policy "Allow auth'd user with 'Create Budget' permission to INSERT" on "public"."budgets";

drop policy "Allow auth'd user with 'Delete Budget' permission to DELETE" on "public"."budgets";

drop policy "Allow auth'd user with 'Edit Budget' permission to UPDATE" on "public"."budgets";

drop policy "Allow auth'd user with 'View Budget' permission to READ" on "public"."budgets";

drop policy "Allow auth'd user with 'Edit Institution' permission to INSERT" on "public"."institutionOfficers";

drop policy "Allow auth'd user with 'View Institution' permission to READ" on "public"."institutionOfficers";

drop policy "Allow auth'd users with 'Edit Institution' permission to DELETE" on "public"."institutionOfficers";

drop policy "Allow auth'd users with 'Edit Institution' permission to UPDATE" on "public"."institutionOfficers";

drop policy "Allow auth'd users with 'Create Institution' permission to INSE" on "public"."institutions";

drop policy "Allow auth'd users with 'Delete Institution' permission to DELE" on "public"."institutions";

drop policy "Allow auth'd users with 'Edit Institution' permission to UPDATE" on "public"."institutions";

drop policy "Allow auth'd users with 'View Institution' permission to READ" on "public"."institutions";

drop policy "Allow authenticated users with permission to DELETE" on "public"."ministries";

drop policy "Allow authenticated users with permission to INSERT" on "public"."ministries";

drop policy "Allow authenticated users with permission to READ" on "public"."ministries";

drop policy "Allow authenticated users with permission to UPDATE" on "public"."ministries";

drop policy "Allow authenticated users with permission to DELETE" on "public"."ministryOfficers";

drop policy "Allow authenticated users with permission to INSERT" on "public"."ministryOfficers";

drop policy "Allow authenticated users with permission to READ" on "public"."ministryOfficers";

drop policy "Allow authenticated users with permission to UPDATE" on "public"."ministryOfficers";

drop policy "Allow create if has create settings permission" on "public"."settingsFundingSources";

drop policy "Allow delete if has delete settings permission" on "public"."settingsFundingSources";

drop policy "Allow edit if has edit settings permission" on "public"."settingsFundingSources";

drop policy "Allow fetch if has view settings permission" on "public"."settingsFundingSources";

drop policy "Allow create if has create settings permission" on "public"."settingsOperationSectors";

drop policy "Allow delete if has delete settings permission" on "public"."settingsOperationSectors";

drop policy "Allow edit if has edit settings permission" on "public"."settingsOperationSectors";

drop policy "Allow fetch if has view settings permission" on "public"."settingsOperationSectors";

drop policy "Allow create if has create settings permission" on "public"."settingsProcurementMethods";

drop policy "Allow delete if has delete settings permission" on "public"."settingsProcurementMethods";

drop policy "Allow edit if has edit settings permission" on "public"."settingsProcurementMethods";

drop policy "Allow authenticated users with permission to DELETE" on "public"."vendorDocuments";

drop policy "Allow authenticated users with permission to INSERT" on "public"."vendorDocuments";

drop policy "Allow authenticated users with permission to READ" on "public"."vendorDocuments";

drop policy "Allow authenticated users with permission to UPDATE" on "public"."vendorDocuments";

drop policy "Allow authenticated users with permission to DELETE" on "public"."vendorOfficers";

drop policy "Allow authenticated users with permission to INSERT" on "public"."vendorOfficers";

drop policy "Allow authenticated users with permission to READ" on "public"."vendorOfficers";

drop policy "Allow authenticated users with permission to UPDATE" on "public"."vendorOfficers";

drop policy "Allow authenticated users with permission to READ" on "public"."vendorSeniorOfficers";

drop policy "Allow authenticated users to with permission to DELETE" on "public"."vendors";

drop policy "Allow authenticated users with permission to INSERT" on "public"."vendors";

drop policy "Allow authenticated users with permission to READ" on "public"."vendors";

drop policy "Allow authenticated users with permission to UPDATE" on "public"."vendors";

CREATE INDEX "accounts_inviterId_idx" ON public.accounts USING btree ("inviterId");

CREATE INDEX "accounts_lockedBy_idx" ON public.accounts USING btree ("lockedBy");

CREATE INDEX accounts_role_idx ON public.accounts USING btree (role);

CREATE INDEX "budgets_awardedCompanyId_idx" ON public.budgets USING btree ("awardedCompanyId");

CREATE INDEX "budgets_creatorId_idx" ON public.budgets USING btree ("creatorId");

CREATE INDEX "budgets_fundingSourceId_idx" ON public.budgets USING btree ("fundingSourceId");

CREATE INDEX "budgets_institutionId_idx" ON public.budgets USING btree ("institutionId");

CREATE INDEX "budgets_ministryId_idx" ON public.budgets USING btree ("ministryId");

CREATE INDEX "budgets_procurementMethodId_idx" ON public.budgets USING btree ("procurementMethodId");

CREATE INDEX "budgets_sectorId_idx" ON public.budgets USING btree ("sectorId");

CREATE INDEX "institutionOfficers_institutionId_idx" ON public."institutionOfficers" USING btree ("institutionId");

CREATE INDEX "institutions_ministryId_idx" ON public.institutions USING btree ("ministryId");

CREATE INDEX "ministryOfficers_ministryId_idx" ON public."ministryOfficers" USING btree ("ministryId");

CREATE INDEX "roleHasPermissions_permission_idx" ON public."roleHasPermissions" USING btree (permission);

CREATE INDEX "roleHasPermissions_role_idx" ON public."roleHasPermissions" USING btree (role);

CREATE INDEX "settingsFundingSources_creatorId_idx" ON public."settingsFundingSources" USING btree ("creatorId");

CREATE INDEX "settingsOperationSectors_creatorId_idx" ON public."settingsOperationSectors" USING btree ("creatorId");

CREATE INDEX "settingsProcurementMethods_creatorId_idx" ON public."settingsProcurementMethods" USING btree ("creatorId");

CREATE INDEX "vendorDocuments_vendorId_idx" ON public."vendorDocuments" USING btree ("vendorId");

CREATE INDEX "vendorOfficers_vendorId_idx" ON public."vendorOfficers" USING btree ("vendorId");

CREATE INDEX "vendors_creatorId_idx" ON public.vendors USING btree ("creatorId");

CREATE INDEX "vendors_sectorId_idx" ON public.vendors USING btree ("sectorId");

create policy "Allow auth'd users with permission to UPDATE"
on "public"."accounts"
as permissive
for update
to authenticated
using (((( SELECT auth.uid() AS uid) = id) OR (("inviteStatus" <> 'Accepted'::"AccountInvitationStatus") AND "hasPermission"(('Edit Role: '::text || role), ( SELECT auth.uid() AS uid)))));


create policy "Allow viewing own account or if user is some Admin"
on "public"."accounts"
as permissive
for select
to authenticated
using (((( SELECT ( SELECT auth.uid() AS uid) AS uid) = id) OR "isSomeAdmin"(( SELECT auth.uid() AS uid)) OR (("hasPermission"('View Role: Vendor Officer'::text, ( SELECT auth.uid() AS uid)) OR "hasPermission"('View Vendor'::text, ( SELECT auth.uid() AS uid))) AND (EXISTS ( SELECT 1
   FROM "vendorSeniorOfficers"
  WHERE ("vendorSeniorOfficers".id = "vendorSeniorOfficers".id))))));


create policy "Allow auth'd user with 'Create Budget' permission to INSERT"
on "public"."budgets"
as permissive
for insert
to authenticated
with check ("hasPermission"('Create Budget'::text, ( SELECT auth.uid() AS uid)));


create policy "Allow auth'd user with 'Delete Budget' permission to DELETE"
on "public"."budgets"
as permissive
for delete
to authenticated
using ("hasPermission"('Delete Budget'::text, ( SELECT auth.uid() AS uid)));


create policy "Allow auth'd user with 'Edit Budget' permission to UPDATE"
on "public"."budgets"
as permissive
for update
to authenticated
using ("hasPermission"('Edit Budget'::text, ( SELECT auth.uid() AS uid)));


create policy "Allow auth'd user with 'View Budget' permission to READ"
on "public"."budgets"
as permissive
for select
to authenticated
using ("hasPermission"('View Budget'::text, ( SELECT auth.uid() AS uid)));


create policy "Allow auth'd user with 'Edit Institution' permission to INSERT"
on "public"."institutionOfficers"
as permissive
for insert
to authenticated
with check ("hasPermission"('Edit Institution'::text, ( SELECT auth.uid() AS uid)));


create policy "Allow auth'd user with 'View Institution' permission to READ"
on "public"."institutionOfficers"
as permissive
for select
to authenticated
using ("hasPermission"('View Institution'::text, ( SELECT auth.uid() AS uid)));


create policy "Allow auth'd users with 'Edit Institution' permission to DELETE"
on "public"."institutionOfficers"
as permissive
for delete
to authenticated
using ("hasPermission"('Edit Institution'::text, ( SELECT auth.uid() AS uid)));


create policy "Allow auth'd users with 'Edit Institution' permission to UPDATE"
on "public"."institutionOfficers"
as permissive
for update
to authenticated
using ("hasPermission"('Edit Institution'::text, ( SELECT auth.uid() AS uid)));


create policy "Allow auth'd users with 'Create Institution' permission to INSE"
on "public"."institutions"
as permissive
for insert
to authenticated
with check ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Create Institution'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow auth'd users with 'Delete Institution' permission to DELE"
on "public"."institutions"
as permissive
for delete
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Delete Institution'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow auth'd users with 'Edit Institution' permission to UPDATE"
on "public"."institutions"
as permissive
for update
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Edit Institution'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow auth'd users with 'View Institution' permission to READ"
on "public"."institutions"
as permissive
for select
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'View Institution'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to DELETE"
on "public"."ministries"
as permissive
for delete
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Delete Ministry'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to INSERT"
on "public"."ministries"
as permissive
for insert
to authenticated
with check ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Create Ministry'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to READ"
on "public"."ministries"
as permissive
for select
to authenticated
using ("hasPermission"('View Ministry'::text, ( SELECT auth.uid() AS uid)));


create policy "Allow authenticated users with permission to UPDATE"
on "public"."ministries"
as permissive
for update
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Edit Ministry'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to DELETE"
on "public"."ministryOfficers"
as permissive
for delete
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Edit Ministry'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to INSERT"
on "public"."ministryOfficers"
as permissive
for insert
to authenticated
with check ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Edit Ministry'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to READ"
on "public"."ministryOfficers"
as permissive
for select
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'View Ministry'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to UPDATE"
on "public"."ministryOfficers"
as permissive
for update
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Edit Ministry'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow create if has create settings permission"
on "public"."settingsFundingSources"
as permissive
for insert
to authenticated
with check ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Create Settings'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow delete if has delete settings permission"
on "public"."settingsFundingSources"
as permissive
for delete
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Delete Settings'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow edit if has edit settings permission"
on "public"."settingsFundingSources"
as permissive
for update
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Edit Settings'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow fetch if has view settings permission"
on "public"."settingsFundingSources"
as permissive
for select
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'View Settings'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow create if has create settings permission"
on "public"."settingsOperationSectors"
as permissive
for insert
to authenticated
with check ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Create Settings'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow delete if has delete settings permission"
on "public"."settingsOperationSectors"
as permissive
for delete
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Delete Settings'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow edit if has edit settings permission"
on "public"."settingsOperationSectors"
as permissive
for update
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Edit Settings'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow fetch if has view settings permission"
on "public"."settingsOperationSectors"
as permissive
for select
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'View Settings'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow create if has create settings permission"
on "public"."settingsProcurementMethods"
as permissive
for insert
to authenticated
with check ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Create Settings'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow delete if has delete settings permission"
on "public"."settingsProcurementMethods"
as permissive
for delete
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Delete Settings'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow edit if has edit settings permission"
on "public"."settingsProcurementMethods"
as permissive
for update
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Edit Settings'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to DELETE"
on "public"."vendorDocuments"
as permissive
for delete
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Edit Vendor'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to INSERT"
on "public"."vendorDocuments"
as permissive
for insert
to authenticated
with check ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Edit Vendor'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to READ"
on "public"."vendorDocuments"
as permissive
for select
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'View Vendor'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to UPDATE"
on "public"."vendorDocuments"
as permissive
for update
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Edit Vendor'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to DELETE"
on "public"."vendorOfficers"
as permissive
for delete
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Edit Vendor'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to INSERT"
on "public"."vendorOfficers"
as permissive
for insert
to authenticated
with check ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Edit Vendor'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to READ"
on "public"."vendorOfficers"
as permissive
for select
to authenticated
using ("hasPermission"('View Vendor'::text, ( SELECT auth.uid() AS uid)));


create policy "Allow authenticated users with permission to UPDATE"
on "public"."vendorOfficers"
as permissive
for update
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Edit Vendor'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to READ"
on "public"."vendorSeniorOfficers"
as permissive
for select
to authenticated
using (("hasPermission"('View Role: Vendor Officer'::text, ( SELECT auth.uid() AS uid)) OR "hasPermission"('View Vendor'::text, ( SELECT auth.uid() AS uid))));


create policy "Allow authenticated users to with permission to DELETE"
on "public"."vendors"
as permissive
for delete
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Delete Vendor'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to INSERT"
on "public"."vendors"
as permissive
for insert
to authenticated
with check ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Create Vendor'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to READ"
on "public"."vendors"
as permissive
for select
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'View Vendor'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));


create policy "Allow authenticated users with permission to UPDATE"
on "public"."vendors"
as permissive
for update
to authenticated
using ((EXISTS ( SELECT true
   FROM "roleHasPermissions" rhp
  WHERE ((rhp.permission = 'Edit Vendor'::text) AND (rhp.role = ( SELECT acc.role
           FROM accounts acc
          WHERE (acc.id = ( SELECT auth.uid() AS uid))
         LIMIT 1))))));



create table "storage"."prefixes" (
    "bucket_id" text not null,
    "name" text not null,
    "level" integer not null generated always as (storage.get_level(name)) stored,
    "created_at" timestamp with time zone default now(),
    "updated_at" timestamp with time zone default now()
);


alter table "storage"."prefixes" enable row level security;

alter table "storage"."objects" add column "level" integer;

CREATE UNIQUE INDEX idx_name_bucket_level_unique ON storage.objects USING btree (name COLLATE "C", bucket_id, level);

CREATE INDEX idx_objects_lower_name ON storage.objects USING btree ((path_tokens[level]), lower(name) text_pattern_ops, bucket_id, level);

CREATE INDEX idx_prefixes_lower_name ON storage.prefixes USING btree (bucket_id, level, ((string_to_array(name, '/'::text))[level]), lower(name) text_pattern_ops);

CREATE UNIQUE INDEX objects_bucket_id_level_idx ON storage.objects USING btree (bucket_id, level, name COLLATE "C");

CREATE UNIQUE INDEX prefixes_pkey ON storage.prefixes USING btree (bucket_id, level, name);

alter table "storage"."prefixes" add constraint "prefixes_pkey" PRIMARY KEY using index "prefixes_pkey";

alter table "storage"."prefixes" add constraint "prefixes_bucketId_fkey" FOREIGN KEY (bucket_id) REFERENCES storage.buckets(id) not valid;

alter table "storage"."prefixes" validate constraint "prefixes_bucketId_fkey";

set check_function_bodies = off;

CREATE OR REPLACE FUNCTION storage.add_prefixes(_bucket_id text, _name text)
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
    prefixes text[];
BEGIN
    prefixes := "storage"."get_prefixes"("_name");

    IF array_length(prefixes, 1) > 0 THEN
        INSERT INTO storage.prefixes (name, bucket_id)
        SELECT UNNEST(prefixes) as name, "_bucket_id" ON CONFLICT DO NOTHING;
    END IF;
END;
$function$
;

CREATE OR REPLACE FUNCTION storage.delete_prefix(_bucket_id text, _name text)
 RETURNS boolean
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN
    -- Check if we can delete the prefix
    IF EXISTS(
        SELECT FROM "storage"."prefixes"
        WHERE "prefixes"."bucket_id" = "_bucket_id"
          AND level = "storage"."get_level"("_name") + 1
          AND "prefixes"."name" COLLATE "C" LIKE "_name" || '/%'
        LIMIT 1
    )
    OR EXISTS(
        SELECT FROM "storage"."objects"
        WHERE "objects"."bucket_id" = "_bucket_id"
          AND "storage"."get_level"("objects"."name") = "storage"."get_level"("_name") + 1
          AND "objects"."name" COLLATE "C" LIKE "_name" || '/%'
        LIMIT 1
    ) THEN
    -- There are sub-objects, skip deletion
    RETURN false;
    ELSE
        DELETE FROM "storage"."prefixes"
        WHERE "prefixes"."bucket_id" = "_bucket_id"
          AND level = "storage"."get_level"("_name")
          AND "prefixes"."name" = "_name";
        RETURN true;
    END IF;
END;
$function$
;

CREATE OR REPLACE FUNCTION storage.delete_prefix_hierarchy_trigger()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    prefix text;
BEGIN
    prefix := "storage"."get_prefix"(OLD."name");

    IF coalesce(prefix, '') != '' THEN
        PERFORM "storage"."delete_prefix"(OLD."bucket_id", prefix);
    END IF;

    RETURN OLD;
END;
$function$
;

CREATE OR REPLACE FUNCTION storage.enforce_bucket_name_length()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
    if length(new.name) > 100 then
        raise exception 'bucket name "%" is too long (% characters). Max is 100.', new.name, length(new.name);
    end if;
    return new;
end;
$function$
;

CREATE OR REPLACE FUNCTION storage.get_level(name text)
 RETURNS integer
 LANGUAGE sql
 IMMUTABLE STRICT
AS $function$
SELECT array_length(string_to_array("name", '/'), 1);
$function$
;

CREATE OR REPLACE FUNCTION storage.get_prefix(name text)
 RETURNS text
 LANGUAGE sql
 IMMUTABLE STRICT
AS $function$
SELECT
    CASE WHEN strpos("name", '/') > 0 THEN
             regexp_replace("name", '[\/]{1}[^\/]+\/?$', '')
         ELSE
             ''
        END;
$function$
;

CREATE OR REPLACE FUNCTION storage.get_prefixes(name text)
 RETURNS text[]
 LANGUAGE plpgsql
 IMMUTABLE STRICT
AS $function$
DECLARE
    parts text[];
    prefixes text[];
    prefix text;
BEGIN
    -- Split the name into parts by '/'
    parts := string_to_array("name", '/');
    prefixes := '{}';

    -- Construct the prefixes, stopping one level below the last part
    FOR i IN 1..array_length(parts, 1) - 1 LOOP
            prefix := array_to_string(parts[1:i], '/');
            prefixes := array_append(prefixes, prefix);
    END LOOP;

    RETURN prefixes;
END;
$function$
;

CREATE OR REPLACE FUNCTION storage.objects_insert_prefix_trigger()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    PERFORM "storage"."add_prefixes"(NEW."bucket_id", NEW."name");
    NEW.level := "storage"."get_level"(NEW."name");

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION storage.objects_update_prefix_trigger()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    old_prefixes TEXT[];
BEGIN
    -- Ensure this is an update operation and the name has changed
    IF TG_OP = 'UPDATE' AND (NEW."name" <> OLD."name" OR NEW."bucket_id" <> OLD."bucket_id") THEN
        -- Retrieve old prefixes
        old_prefixes := "storage"."get_prefixes"(OLD."name");

        -- Remove old prefixes that are only used by this object
        WITH all_prefixes as (
            SELECT unnest(old_prefixes) as prefix
        ),
        can_delete_prefixes as (
             SELECT prefix
             FROM all_prefixes
             WHERE NOT EXISTS (
                 SELECT 1 FROM "storage"."objects"
                 WHERE "bucket_id" = OLD."bucket_id"
                   AND "name" <> OLD."name"
                   AND "name" LIKE (prefix || '%')
             )
         )
        DELETE FROM "storage"."prefixes" WHERE name IN (SELECT prefix FROM can_delete_prefixes);

        -- Add new prefixes
        PERFORM "storage"."add_prefixes"(NEW."bucket_id", NEW."name");
    END IF;
    -- Set the new level
    NEW."level" := "storage"."get_level"(NEW."name");

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION storage.prefixes_insert_trigger()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    PERFORM "storage"."add_prefixes"(NEW."bucket_id", NEW."name");
    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION storage.search_legacy_v1(prefix text, bucketname text, limits integer DEFAULT 100, levels integer DEFAULT 1, offsets integer DEFAULT 0, search text DEFAULT ''::text, sortcolumn text DEFAULT 'name'::text, sortorder text DEFAULT 'asc'::text)
 RETURNS TABLE(name text, id uuid, updated_at timestamp with time zone, created_at timestamp with time zone, last_accessed_at timestamp with time zone, metadata jsonb)
 LANGUAGE plpgsql
 STABLE
AS $function$
declare
    v_order_by text;
    v_sort_order text;
begin
    case
        when sortcolumn = 'name' then
            v_order_by = 'name';
        when sortcolumn = 'updated_at' then
            v_order_by = 'updated_at';
        when sortcolumn = 'created_at' then
            v_order_by = 'created_at';
        when sortcolumn = 'last_accessed_at' then
            v_order_by = 'last_accessed_at';
        else
            v_order_by = 'name';
        end case;

    case
        when sortorder = 'asc' then
            v_sort_order = 'asc';
        when sortorder = 'desc' then
            v_sort_order = 'desc';
        else
            v_sort_order = 'asc';
        end case;

    v_order_by = v_order_by || ' ' || v_sort_order;

    return query execute
        'with folders as (
           select path_tokens[$1] as folder
           from storage.objects
             where objects.name ilike $2 || $3 || ''%''
               and bucket_id = $4
               and array_length(objects.path_tokens, 1) <> $1
           group by folder
           order by folder ' || v_sort_order || '
     )
     (select folder as "name",
            null as id,
            null as updated_at,
            null as created_at,
            null as last_accessed_at,
            null as metadata from folders)
     union all
     (select path_tokens[$1] as "name",
            id,
            updated_at,
            created_at,
            last_accessed_at,
            metadata
     from storage.objects
     where objects.name ilike $2 || $3 || ''%''
       and bucket_id = $4
       and array_length(objects.path_tokens, 1) = $1
     order by ' || v_order_by || ')
     limit $5
     offset $6' using levels, prefix, search, bucketname, limits, offsets;
end;
$function$
;

CREATE OR REPLACE FUNCTION storage.search_v1_optimised(prefix text, bucketname text, limits integer DEFAULT 100, levels integer DEFAULT 1, offsets integer DEFAULT 0, search text DEFAULT ''::text, sortcolumn text DEFAULT 'name'::text, sortorder text DEFAULT 'asc'::text)
 RETURNS TABLE(name text, id uuid, updated_at timestamp with time zone, created_at timestamp with time zone, last_accessed_at timestamp with time zone, metadata jsonb)
 LANGUAGE plpgsql
 STABLE
AS $function$
declare
    v_order_by text;
    v_sort_order text;
begin
    case
        when sortcolumn = 'name' then
            v_order_by = 'name';
        when sortcolumn = 'updated_at' then
            v_order_by = 'updated_at';
        when sortcolumn = 'created_at' then
            v_order_by = 'created_at';
        when sortcolumn = 'last_accessed_at' then
            v_order_by = 'last_accessed_at';
        else
            v_order_by = 'name';
        end case;

    case
        when sortorder = 'asc' then
            v_sort_order = 'asc';
        when sortorder = 'desc' then
            v_sort_order = 'desc';
        else
            v_sort_order = 'asc';
        end case;

    v_order_by = v_order_by || ' ' || v_sort_order;

    return query execute
        'with folders as (
           select (string_to_array(name, ''/''))[level] as name
           from storage.prefixes
             where lower(prefixes.name) like lower($2 || $3) || ''%''
               and bucket_id = $4
               and level = $1
           order by name ' || v_sort_order || '
     )
     (select name,
            null as id,
            null as updated_at,
            null as created_at,
            null as last_accessed_at,
            null as metadata from folders)
     union all
     (select path_tokens[level] as "name",
            id,
            updated_at,
            created_at,
            last_accessed_at,
            metadata
     from storage.objects
     where lower(objects.name) like lower($2 || $3) || ''%''
       and bucket_id = $4
       and level = $1
     order by ' || v_order_by || ')
     limit $5
     offset $6' using levels, prefix, search, bucketname, limits, offsets;
end;
$function$
;

CREATE OR REPLACE FUNCTION storage.search_v2(prefix text, bucket_name text, limits integer DEFAULT 100, levels integer DEFAULT 1, start_after text DEFAULT ''::text)
 RETURNS TABLE(key text, name text, id uuid, updated_at timestamp with time zone, created_at timestamp with time zone, metadata jsonb)
 LANGUAGE plpgsql
 STABLE
AS $function$
BEGIN
    RETURN query EXECUTE
        $sql$
        SELECT * FROM (
            (
                SELECT
                    split_part(name, '/', $4) AS key,
                    name || '/' AS name,
                    NULL::uuid AS id,
                    NULL::timestamptz AS updated_at,
                    NULL::timestamptz AS created_at,
                    NULL::jsonb AS metadata
                FROM storage.prefixes
                WHERE name COLLATE "C" LIKE $1 || '%'
                AND bucket_id = $2
                AND level = $4
                AND name COLLATE "C" > $5
                ORDER BY prefixes.name COLLATE "C" LIMIT $3
            )
            UNION ALL
            (SELECT split_part(name, '/', $4) AS key,
                name,
                id,
                updated_at,
                created_at,
                metadata
            FROM storage.objects
            WHERE name COLLATE "C" LIKE $1 || '%'
                AND bucket_id = $2
                AND level = $4
                AND name COLLATE "C" > $5
            ORDER BY name COLLATE "C" LIMIT $3)
        ) obj
        ORDER BY name COLLATE "C" LIMIT $3;
        $sql$
        USING prefix, bucket_name, limits, levels, start_after;
END;
$function$
;

CREATE OR REPLACE FUNCTION storage.extension(name text)
 RETURNS text
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
DECLARE
    _parts text[];
    _filename text;
BEGIN
    SELECT string_to_array(name, '/') INTO _parts;
    SELECT _parts[array_length(_parts,1)] INTO _filename;
    RETURN reverse(split_part(reverse(_filename), '.', 1));
END
$function$
;

CREATE OR REPLACE FUNCTION storage.foldername(name text)
 RETURNS text[]
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
DECLARE
    _parts text[];
BEGIN
    -- Split on "/" to get path segments
    SELECT string_to_array(name, '/') INTO _parts;
    -- Return everything except the last segment
    RETURN _parts[1 : array_length(_parts,1) - 1];
END
$function$
;

CREATE OR REPLACE FUNCTION storage.get_size_by_bucket()
 RETURNS TABLE(size bigint, bucket_id text)
 LANGUAGE plpgsql
 STABLE
AS $function$
BEGIN
    return query
        select sum((metadata->>'size')::bigint) as size, obj.bucket_id
        from "storage".objects as obj
        group by obj.bucket_id;
END
$function$
;

CREATE OR REPLACE FUNCTION storage.search(prefix text, bucketname text, limits integer DEFAULT 100, levels integer DEFAULT 1, offsets integer DEFAULT 0, search text DEFAULT ''::text, sortcolumn text DEFAULT 'name'::text, sortorder text DEFAULT 'asc'::text)
 RETURNS TABLE(name text, id uuid, updated_at timestamp with time zone, created_at timestamp with time zone, last_accessed_at timestamp with time zone, metadata jsonb)
 LANGUAGE plpgsql
AS $function$
declare
    can_bypass_rls BOOLEAN;
begin
    SELECT rolbypassrls
    INTO can_bypass_rls
    FROM pg_roles
    WHERE rolname = coalesce(nullif(current_setting('role', true), 'none'), current_user);

    IF can_bypass_rls THEN
        RETURN QUERY SELECT * FROM storage.search_v1_optimised(prefix, bucketname, limits, levels, offsets, search, sortcolumn, sortorder);
    ELSE
        RETURN QUERY SELECT * FROM storage.search_legacy_v1(prefix, bucketname, limits, levels, offsets, search, sortcolumn, sortorder);
    END IF;
end;
$function$
;

grant delete on table "storage"."prefixes" to "anon";

grant insert on table "storage"."prefixes" to "anon";

grant references on table "storage"."prefixes" to "anon";

grant select on table "storage"."prefixes" to "anon";

grant trigger on table "storage"."prefixes" to "anon";

grant truncate on table "storage"."prefixes" to "anon";

grant update on table "storage"."prefixes" to "anon";

grant delete on table "storage"."prefixes" to "authenticated";

grant insert on table "storage"."prefixes" to "authenticated";

grant references on table "storage"."prefixes" to "authenticated";

grant select on table "storage"."prefixes" to "authenticated";

grant trigger on table "storage"."prefixes" to "authenticated";

grant truncate on table "storage"."prefixes" to "authenticated";

grant update on table "storage"."prefixes" to "authenticated";

grant delete on table "storage"."prefixes" to "service_role";

grant insert on table "storage"."prefixes" to "service_role";

grant references on table "storage"."prefixes" to "service_role";

grant select on table "storage"."prefixes" to "service_role";

grant trigger on table "storage"."prefixes" to "service_role";

grant truncate on table "storage"."prefixes" to "service_role";

grant update on table "storage"."prefixes" to "service_role";

CREATE TRIGGER enforce_bucket_name_length_trigger BEFORE INSERT OR UPDATE OF name ON storage.buckets FOR EACH ROW EXECUTE FUNCTION storage.enforce_bucket_name_length();

CREATE TRIGGER objects_delete_delete_prefix AFTER DELETE ON storage.objects FOR EACH ROW EXECUTE FUNCTION storage.delete_prefix_hierarchy_trigger();

CREATE TRIGGER objects_insert_create_prefix BEFORE INSERT ON storage.objects FOR EACH ROW EXECUTE FUNCTION storage.objects_insert_prefix_trigger();

CREATE TRIGGER objects_update_create_prefix BEFORE UPDATE ON storage.objects FOR EACH ROW WHEN (((new.name <> old.name) OR (new.bucket_id <> old.bucket_id))) EXECUTE FUNCTION storage.objects_update_prefix_trigger();

CREATE TRIGGER prefixes_create_hierarchy BEFORE INSERT ON storage.prefixes FOR EACH ROW WHEN ((pg_trigger_depth() < 1)) EXECUTE FUNCTION storage.prefixes_insert_trigger();

CREATE TRIGGER prefixes_delete_hierarchy AFTER DELETE ON storage.prefixes FOR EACH ROW EXECUTE FUNCTION storage.delete_prefix_hierarchy_trigger();


