-- # Put the your SQL below migration seperator.
-- !UP

create table if not exists organizations (
  id bigint generated by default as identity primary key,
  public_id text not null unique,
  owner_id bigint references users(id) on delete cascade,
  name text not null,
  description text not null,
  image_url text null,
  website_url text null,
  is_verified bool default false,
  is_featured bool default false,
  status smallint default 1,
  created_at timestamptz default current_timestamp,
  updated_at timestamptz default current_timestamp
);

alter sequence organizations_id_seq restart with 10000;

alter table organizations enable row level security;

create policy "all organizations are viewable by everyone." on organizations
for select using (true);

create policy "users can only insert organizations they own." on organizations
for insert with check (public.get_current_user_id() = owner_id);

create policy "users can only update organizations they own." on organizations
for update using (public.get_current_user_id() = owner_id);

create policy "users can only delete organizations they own." on organizations
for delete using (public.get_current_user_id() = owner_id);

create or replace function is_current_user_owner_of_organization(organization_id bigint)
returns boolean
language plpgsql
as $$
begin
    return exists(
      select 1
      from organizations
      where id = organization_id and owner_id = public.get_current_user_id()
    );
end;
$$;

create table if not exists organization_members (
  organization_id bigint references organizations(id) on delete cascade,
  user_id bigint references users(id) on delete cascade,
  role smallint default 1,
  status smallint default 1,
  created_at timestamptz default current_timestamp,
  updated_at timestamptz default current_timestamp,
  primary key (organization_id, user_id)
);

alter table organization_members enable row level security;

create policy "owners can only view organization members." on organization_members
for select using (public.is_current_user_owner_of_organization(organization_id));

create policy "owners can only insert organization members." on organization_members
for insert with check (public.is_current_user_owner_of_organization(organization_id));

create policy "owners can only update organization members." on organization_members
for update using (public.is_current_user_owner_of_organization(organization_id));

create policy "owners can only delete organization members." on organization_members
for delete using (public.is_current_user_owner_of_organization(organization_id));

create or replace function is_current_user_member_of_organization(organization_id bigint)
returns boolean
language plpgsql stable
as $$
begin
    return exists(
      select 1
      from organization_members
      where organization_id = organization_id and user_id = public.get_current_user_id()
    ) or public.is_current_user_owner_of_organization(organization_id);
end;
$$;

create trigger tr_mod_updated_at
before update on organizations
for each row
execute function moddatetime(updated_at);

insert into organizations
  (id, public_id, owner_id, name, description, is_verified, is_featured)
values
  (1, 'acme', 1, 'acme corporation', 'the acme corporation is a fictional corporation that features prominently in the road runner/wile e. coyote animated shorts as a running gag. the company manufactures outlandish products that fail or backfire catastrophically at the worst possible times. the name is also used as a generic title in many cartoons, especially those made by warner bros. and films, tv series, commercials and comic strips.', true, false);

-- !DOWN

drop trigger if exists tr_mod_updated_at on organizations;
drop function if exists is_current_user_member_of_organization;
-- reverse order of deletion as is_current_user_owner_of_organization
-- depends on organization_members
drop table if exists organization_members;
drop function if exists is_current_user_owner_of_organization;
drop table if exists organizations;
