text: --! Previous: sha1:d2f9c3b9a6a308384237f8bd2899fd19bfc6b4f6
  --! Hash: sha1:6ece23f47e605b3e05c6a230857276f5cc42607f
  
  -- Enter migration here
  drop type if exists sketch_geometry_type cascade;
  create type sketch_geometry_type as enum (
    'POLYGON',
    'LINESTRING',
    'POINT',
    'COLLECTION',
    'CHOOSE_FEATURE'
  );
  
  drop table if exists sketch_classes cascade;
  create table sketch_classes (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    project_id int not null references projects(id) on delete cascade,
    name text not null,
    geometry_type sketch_geometry_type not null default 'POLYGON',
    allow_multi boolean not null default false,
    is_archived boolean not null default false,
    mapbox_gl_style text,
    is_my_plans_option boolean not null default true,
    geoprocessing_project_url text CHECK (geoprocessing_project_url::text ~* 'https?:\/\/(www\.)?[-a-zA-Z0-9@:%._\+~#=]{2,255}\.[a-z]{2,9}\y([-a-zA-Z0-9@:%_\+.~#?&//=]*)$'::text),
    geoprocessing_client_url text CHECK (geoprocessing_client_url::text ~* 'https?:\/\/(www\.)?[-a-zA-Z0-9@:%._\+~#=]{2,255}\.[a-z]{2,9}\y([-a-zA-Z0-9@:%_\+.~#?&//=]*)$'::text),
    geoprocessing_client_name text,
    unique(project_id, name)
  );
  
  create index on sketch_classes (project_id);
  
  comment on table sketch_classes is '
  @omit all
  Sketch Classes act as a schema for sketches drawn by users.
  ';
  
  comment on constraint sketch_classes_project_id_name_key on sketch_classes is '@omit';
  comment on constraint sketch_classes_project_id_fkey on sketch_classes is '@simpleCollections only';
  
  comment on column sketch_classes.geometry_type is '
  Geometry type users digitize. COLLECTION types act as a feature collection and have no drawn geometry.
  ';
  
  comment on column sketch_classes.allow_multi is '
  If set to try, a geometry_type of POLYGON would allow for both POLYGONs and MULTIPOLYGONs after preprocessing or on spatial file upload. Users will still digitize single features. Note that this feature should be used seldomly, since for planning purposes it is unlikely to have non-contiguous zones.
  ';
  
  comment on column sketch_classes.is_archived is '
  If set to true, (non-admin) users should not be able to digitize new features using this sketch class, but they should still be able to access the sketch class in order to render existing sketches of this type.
  ';
  
  comment on column sketch_classes.mapbox_gl_style is '
  Style used to render sketches of this type.
  ';
  
  comment on column sketch_classes.is_my_plans_option is '
  If set to true, show as an option in the digitizing tools. If set to false, this sketch class may be solely for survey responses.
  ';
  
  grant select on table sketch_classes to anon;
  
  alter table sketch_classes enable row level security;
  
  -- Check for read-only (or otherwise) access to a project
  create or replace function session_has_project_access (pid int)
    returns boolean
    language sql
    security definer
    stable
    as $$
      -- Here we give access if the project is public, if the session is an admin,
      -- or if the session belongs to an approved participant
      select exists(
        select
          1
        from 
          projects 
        where 
          projects.id = pid and 
          access_control = 'public'
      ) or 
      session_is_admin(pid) or 
      session_is_approved_participant(pid)
    $$;
  
  grant execute on function session_has_project_access to anon;
  comment on function session_has_project_access is '@omit';
  
  -- Access control policies
  
  drop policy if exists sketch_classes_select on sketch_classes;
  create policy sketch_classes_select on sketch_classes for select using (
    session_has_project_access(project_id)
  );
  
  grant update(name, is_archived, allow_multi, mapbox_gl_style, geoprocessing_client_name, geoprocessing_client_url, geoprocessing_project_url) on sketch_classes to seasketch_user;
  grant insert, delete on sketch_classes to seasketch_user;
  create policy sketch_classes_admin on sketch_classes for all using (
    session_is_admin(project_id)
  ) with check ( session_is_admin(project_id) );
  
  -- Digitizing access control
  
  -- Add sketch_class model to access_control_lists table
  alter table access_control_lists drop column if exists sketch_class_id;
  alter table access_control_lists add column sketch_class_id int unique 
    references sketch_classes (id) on delete cascade;
  
  -- Update related model constraints. Note you need to make sure not to clobber
  -- the checks for other models in the process of adding this new one
  alter table access_control_lists drop constraint if exists access_control_list_has_related_model;
  alter table access_control_lists add constraint 
    access_control_list_has_related_model check (
      (
          (sketch_class_id is not null)::integer +
          (forum_id is not null)::integer
      ) = 1
    );
  
  -- Add an index to make acl lookups by forum_id efficient
  create unique index on access_control_lists (sketch_class_id) 
    where sketch_class_id is not null;
  
  -- Triggers should be added for all models to create acls with defaults
  create or replace function create_sketch_class_acl() returns trigger
      security definer
      language plpgsql
      AS $$
  begin
    insert into
      access_control_lists(project_id, sketch_class_id, type)
      values(new.project_id, new.id, 'public'::access_control_list_type);
        return new;
  end;
  $$;
  
  -- Enable the trigger function
  create trigger trig_create_sketch_class_acl after insert on sketch_classes 
    for each row execute function create_sketch_class_acl();
  
  -- Function assumes user already has access to the sketch class
  create or replace function can_digitize(scid int)
    returns boolean
    language sql
    stable
    security definer
    as $$
      with acl as (
        select id, type, project_id from access_control_lists where sketch_class_id = scid
      )
      select 
        (
          exists(select 1 from sketch_classes where id = scid and is_archived = false) and
          exists(select 1 from acl where type = 'public') or 
          (
            exists(select 1 from acl where type = 'group') and 
            current_setting('session.user_id', TRUE) != '' and 
            exists (
              select 1 from access_control_list_groups 
                where access_control_list_id = (select id from acl) and group_id in (
                  select group_id from project_group_members where user_id = nullif(current_setting('session.user_id', TRUE), '')::integer
                )
            )
          )
        ) or session_is_admin((select project_id from acl))
    $$;
  
  create or replace function sketch_classes_can_digitize(sketch_class sketch_classes)
    returns boolean
    language sql
    stable
    as $$
      select can_digitize(sketch_class.id);
    $$;
  
  grant execute on function sketch_classes_can_digitize to anon;
  grant execute on function can_digitize to anon;
  
  comment on function sketch_classes_can_digitize is '
  Whether the user is allowed to digitize sketches of this type. Digitizing is controlled by admins via access control lists, and archived sketch classes can only be digitized by admins.
  ';
  
  create extension if not exists postgis;
  
  create or replace function create_bbox(geom Geometry)
    returns real[4]
    language sql
    IMMUTABLE
    security definer
    as $$
      select array[st_xmin(geom)::real, st_ymin(geom)::real, st_xmax(geom)::real, st_ymax(geom)::real];
    $$;
  
  grant execute on function create_bbox to anon;
  grant execute on function st_npoints to anon;
  
  drop table if exists sketches cascade;
  create table sketches (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text not null,
    sketch_class_id int not null references sketch_classes(id) on delete cascade,
    user_id int not null references users(id) on delete cascade,
    collection_id int references sketches(id) on delete cascade,
    -- TODO: survey_response_id int refereneces survey_responses(id) on delete cascade,
    copy_of int references sketches(id) on delete set null,
    user_geom geometry(geometry, 4326),
    geom geometry(geometry, 4326),
    bbox real[4] generated always as (create_bbox(geom)) stored,
    num_vertices int generated always as (st_npoints(geom)) stored
  );
  
  create index on sketches (user_id);
  create index on sketches (user_id, sketch_class_id);
  create index on sketches(sketch_class_id);
  create index on sketches(collection_id);
  create index on sketches(copy_of);
  
  -- Prevent deletion when there are too many related sketches
  drop table if exists sketch_folders cascade;
  create table sketch_folders (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text not null,
    user_id int not null references users(id) on delete cascade,
    project_id int not null references projects(id) on delete cascade,
    folder_id int references sketch_folders(id) on delete cascade,
    collection_id int references sketches(id) on delete cascade
    CONSTRAINT has_single_or_no_parent_folder_or_collection CHECK (
      folder_id = null or collection_id = null
    )
  );
  
  alter table sketches add column folder_id int references sketch_folders(id) on delete cascade;
  alter table sketches add constraint has_single_or_no_parent_folder_or_collection CHECK (folder_id = null or collection_id = null);
  
  
  create index on sketch_folders (user_id);
  create index on sketch_folders (user_id, project_id);
  comment on column sketch_folders.project_id is '@omit many';
  
  comment on table sketch_folders is '
  @omit all
  ';
  
  grant all on sketch_folders to seasketch_user;
  alter table sketch_folders enable row level security;
  create policy sketch_folders_policy on sketch_folders for all to seasketch_user 
    using (it_me(user_id)) with check (it_me(user_id));
  
  create or replace function before_sketch_folders_insert_or_update()
    returns trigger
    language plpgsql
    as $$
      declare
        parent_project_id int;
      begin
        if NEW.folder_id is null and NEW.collection_id is null then
          return NEW;
        else
          if NEW.folder_id is not null then
            select 
              project_id 
            into 
              parent_project_id 
            from 
              sketch_folders 
            where 
              id = NEW.folder_id;
          end if;
          if NEW.collection_id is not null then
            select 
              project_id 
            into 
              parent_project_id 
            from 
              sketch_classes 
            where 
              id in (
                select 
                  sketch_class_id 
                from 
                  sketches 
                where 
                  id = NEW.collection_id
              );
          end if;
          if NEW.project_id is null or NEW.project_id = parent_project_id then
            return NEW;
          else
            raise exception 'project_id of parent does not match % %', NEW.project_id, parent_project_id;
          end if;
        end if;
      end
    $$;
  
  create trigger before_sketch_folders_insert_or_update_trigger 
    before insert or update on sketch_folders 
    for each row execute procedure before_sketch_folders_insert_or_update();
  
  create or replace function sketch_classes_prohibit_delete() 
    returns trigger
    language plpgsql
    security definer
    as $$
      declare
        count int;
      begin
        select count(*) into count from sketches where sketch_class_id = OLD.id;
        if count > 10 then
          raise exception 'Has > 10 related sketches. Try archiving instead.';
        else
          return OLD;
        end if;
      end
    $$;
  
  create trigger sketch_classes_prohibit_delete_t 
    before delete on sketch_classes for each row 
      execute procedure sketch_classes_prohibit_delete();
  
  comment on constraint sketches_folder_id_fkey on sketches is '@omit many';
  comment on constraint sketches_collection_id_fkey on sketches is '@omit many';
  comment on constraint sketches_copy_of_fkey on sketches is '
  @omit many
  @fieldName copiedFrom
  ';
  comment on table sketches is '
  @omit many
  ';
  -- comment on column sketches.geom is '@omit';
  
  comment on constraint sketches_sketch_class_id_fkey on sketches is '@omit many';
  
  -- Create valid children join table
  drop table if exists sketch_classes_valid_children cascade;
  create table sketch_classes_valid_children (
    parent_id int not null references sketch_classes(id) on delete cascade,
    child_id int not null references sketch_classes(id) on delete cascade,
    unique(parent_id, child_id)
  );
  create index on sketch_classes_valid_children(parent_id);
  create index on sketch_classes_valid_children(child_id);
  comment on table sketch_classes_valid_children is '@omit';
  
  -- constrain to collections
  create or replace function before_valid_children_insert_or_update()
    returns trigger
    language plpgsql
    as $$
      declare
        child_geometry_type sketch_geometry_type;
        parent_geometry_type sketch_geometry_type;
      begin
        select geometry_type into parent_geometry_type from sketch_classes where id = NEW.parent_id;
        select geometry_type into child_geometry_type from sketch_classes where id = NEW.child_id;
        if parent_geometry_type = 'COLLECTION'::sketch_geometry_type then
          if child_geometry_type = 'COLLECTION'::sketch_geometry_type then
            raise exception 'Nested collections are not allowed.';
          else
            return NEW;
          end if;
        else
          raise exception 'Valid children can only be specified for collections.';
        end if;
      end
    $$;
  
  create trigger before_valid_children_insert_or_update_trigger
    before insert or update on sketch_classes_valid_children
      for each row execute procedure before_valid_children_insert_or_update();
  
  -- Create add, remove, accessor methods for sketch_classes_valid_children
  
  create or replace function add_valid_child_sketch_class(parent int, child int)
    returns void
    language plpgsql
    security definer
    as $$
      begin
        if session_is_admin((select project_id from sketch_classes where id = parent)) then
          if exists(select project_id from sketch_classes where id = child and project_id in (select project_id from sketch_classes where id = parent)) then
            insert into sketch_classes_valid_children (parent_id, child_id) values (parent, child);
          else
            raise exception 'Sketch classes must be in the same project.';
          end if;
        else
          raise exception 'Must be an admin for the project.';
        end if;
      end
    $$;
  
  grant execute on function add_valid_child_sketch_class to seasketch_user;
  
  create or replace function remove_valid_child_sketch_class(parent int, child int)
    returns void
    language plpgsql
    security definer
    as $$
      begin
        if session_is_admin((select project_id from sketch_classes where id = parent)) then
          delete from sketch_classes_valid_children where parent_id = parent and child_id = child;
        else
          raise exception 'Must be an admin for the project.';
        end if;
      end
    $$;
  
  grant execute on function remove_valid_child_sketch_class to seasketch_user;
  
  create or replace function sketch_classes_valid_children(sketch_class sketch_classes)
    returns setof sketch_classes
    language sql
    stable
    security definer
    as $$
      select
        sketch_classes.*
      from
        sketch_classes
      where
        id in (
          select 
            child_id 
          from 
            sketch_classes_valid_children 
          where
            parent_id = sketch_class.id
        )
    $$;
  
  grant execute on function sketch_classes_valid_children to anon;
  
  comment on function sketch_classes_valid_children is '
  @simpleCollections only
  ';
  
  create or replace function before_sketch_insert_or_update()
    returns trigger
    language plpgsql
    security definer
    as $$
      declare
        class_geometry_type sketch_geometry_type;
        allow_multi boolean;
        incoming_geometry_type text;
        new_geometry_type text;
      begin
        select 
          geometry_type, 
          sketch_classes.allow_multi
        into 
          class_geometry_type, 
          allow_multi, 
          incoming_geometry_type 
        from 
          sketch_classes 
        where 
          id = NEW.sketch_class_id;
        if NEW.folder_id is not null and NEW.collection_id is not null then
          raise exception 'Parent cannot be to both folder and collection';
        end if;
        if class_geometry_type = 'COLLECTION' then
          -- geom must be present unless a collection
          if NEW.geom is not null or NEW.user_geom is not null then
            raise exception 'Collections should not have geometry';
          else
            -- no nested collections
            if NEW.collection_id is not null then
              raise exception 'Nested collections are not allowed';
            else
              return NEW;
            end if;
          end if;
        else
          select geometrytype(NEW.geom) into new_geometry_type;
          -- geometry type must match sketch_class.geometry_type and sketch_class.allow_multi
          if (new_geometry_type = class_geometry_type::text) or (allow_multi and new_geometry_type like '%' || class_geometry_type::text) then
            -- if specifying a collection_id, must be in it's valid_children
            if NEW.collection_id is null or not exists(select 1 from sketch_classes_valid_children where parent_id in (select sketch_class_id from sketches where id = NEW.collection_id)) or exists(select 1 from sketch_classes_valid_children where parent_id in (select sketch_class_id from sketches where id = NEW.collection_id) and child_id = NEW.sketch_class_id) then
              return NEW;
            else
              raise exception 'Sketch is not a valid child of collection';
            end if;
          else
            raise exception 'Geometry type does not match sketch class';
          end if;
        end if;
      end
    $$;
  
  create trigger before_sketch_insert_or_update_trigger 
    before insert or update on sketches 
    for each row execute procedure before_sketch_insert_or_update();
  
  -- Users manage their own content and nobody else has access.
  -- Shared sketches will be handled in the forums schema using a custom query
  grant select on sketches to seasketch_user;
  grant update (collection_id, user_geom, geom, name) on sketches to seasketch_user;
  grant insert, delete on sketches to seasketch_user;
  alter table sketches enable row level security;
  drop policy if exists sketches_select on sketches;
  create policy sketches_select on sketches for select using (it_me(user_id));
  drop policy if exists sketches_insert on sketches;
  create policy sketches_insert on sketches for insert with check (it_me(user_id) and can_digitize(sketch_class_id));
  drop policy if exists sketches_delete on sketches;
  create policy sketches_delete on sketches for delete using (it_me(user_id));
  drop policy if exists sketches_update on sketches;
  create policy sketches_updated on sketches for update using (it_me(user_id)) with check (it_me(user_id));
  
  comment on column sketches.num_vertices is '@omit create';
  comment on column sketches.bbox is '@omit create';
  comment on column sketches.copy_of is '@omit create';
  
  create or replace function sketch_classes_sketch_count(sketch_class sketch_classes)
    returns bigint
    language sql
    stable
    security definer
    as $$
      select count(*) from sketches where sketch_class_id = sketch_class.id
    $$;
  
  grant execute on function sketch_classes_sketch_count to anon;
  
  comment on function sketch_classes_sketch_count is 'Number of sketches created with this sketch class';
  
  create or replace function my_sketches("projectId" integer)
    returns setof sketches
    language sql
    stable
    as $$
      select
        *
      from
        sketches
      where
        it_me(user_id) and sketch_class_id in (
          select id from sketch_classes where project_id = "projectId");
    $$;
  
  grant execute on function my_sketches to seasketch_user;
  comment on function my_sketches is '@simpleCollections only';
  
  create or replace function projects_my_sketches(project projects)
    returns setof sketches
    language sql
    stable
    as $$
    select my_sketches(project.id)
  $$;
  
  grant execute on function projects_my_sketches to seasketch_user;
  comment on function projects_my_sketches is '
  @simpleCollections only
  A list of all sketches for this project and the current user session
  ';
  
  grant execute on function st_geomfromgeojson(text) to anon;
  grant execute on function geometry(geometry, int, boolean) to anon;
  grant execute on function geometrytype(geometry) to anon;
  
  create or replace function my_folders("projectId" integer)
    returns setof sketch_folders
    language sql
    stable
    as $$
      select * from sketch_folders where sketch_folders.project_id = "projectId" and it_me(sketch_folders.user_id);
    $$;
  
  grant execute on function my_folders to seasketch_user;
  
  comment on function my_folders is '@simpleCollections only';
  
  create or replace function projects_my_folders(project projects)
    returns setof sketch_folders
    language sql
    stable
    as $$
    select my_folders(project.id)
  $$;
  
  grant execute on function projects_my_folders to seasketch_user;
  comment on function projects_my_folders is '
  @simpleCollections only
  List of all folders created by this user.
  ';
  