--
-- packages/acs-kernel/sql/site-nodes-create.sql
--
-- @author rhs@mit.edu
-- @creation-date 2000-09-05
-- @cvs-id $Id: site-nodes-create.sql,v 1.32 2024/09/11 06:15:48 gustafn Exp $
--


--
-- procedure inline_0/0
--
CREATE OR REPLACE FUNCTION inline_0(

) RETURNS integer AS $$
DECLARE
        dummy   integer;
BEGIN
  PERFORM acs_object_type__create_type (
    'site_node',
    'Site Node',
    'Site Nodes',
    'acs_object',
    'site_nodes',
    'node_id',
    'site_node',
    'f',
    null,
    null
    );

  return 0;
END;
$$ LANGUAGE plpgsql;

select inline_0 ();

drop function inline_0 ();


-- This table allows urls to be mapped to a node_ids.

create table site_nodes (
        node_id         integer constraint site_nodes_node_id_fk
                        references acs_objects (object_id)
                        constraint site_nodes_node_id_pk
                        primary key,
        parent_id       integer constraint site_nodes_parent_id_fk
                        references site_nodes (node_id),
        name            text
                        constraint site_nodes_name_ck
                        check (name not like '%/%'),
        constraint site_nodes_un
        unique (parent_id, name),
        -- Is it legal to create a child node?
        directory_p     boolean not null,
        -- Should urls that are logical children of this node be
        -- mapped to this node?
        pattern_p       boolean default false not null,
        object_id       integer constraint site_nodes_object_id_fk
                        references acs_objects (object_id)
);

--
-- Avoid potential loops on site_node parent_ids. A parent_id must be
-- different from the node_id. 
-- Note that this constraint is not guaranteed to avoid all loops; 
-- it is still possible to create indirect recursive
-- loops but excludes some real-world problems.
--
ALTER TABLE site_nodes ADD CONSTRAINT site_nodes_parent_id_ck CHECK (node_id <> parent_id);

create index site_nodes_object_id_idx on site_nodes (object_id);
create index site_nodes_parent_object_node_id_idx on site_nodes(parent_id, object_id, node_id);
create index site_nodes_parent_id_idx on site_nodes(parent_id);

--
-- procedure site_node__new/8
--

select define_function_args('site_node__new','node_id;null,parent_id;null,name,object_id;null,directory_p,pattern_p;f,creation_user;null,creation_ip;null');


CREATE OR REPLACE FUNCTION site_node__new(
   new__node_id integer,       -- default null
   new__parent_id integer,     -- default null
   new__name varchar,
   new__object_id integer,     -- default null
   new__directory_p boolean,
   new__pattern_p boolean,     -- default 'f'
   new__creation_user integer, -- default null
   new__creation_ip varchar    -- default null

) RETURNS integer AS $$
DECLARE
  v_node_id                   site_nodes.node_id%TYPE;
  v_directory_p               site_nodes.directory_p%TYPE;
BEGIN
    if new__parent_id is not null then
      select directory_p into v_directory_p
      from site_nodes
      where node_id = new__parent_id;

      if v_directory_p = 'f' then
        raise EXCEPTION '-20000: Node % is not a directory', new__parent_id;
      end if;
    end if;

    v_node_id := acs_object__new (
      new__node_id,
      'site_node',
      now(),
      new__creation_user,
      new__creation_ip,
      null,
      't',
      new__name,
      new__object_id
    );

    insert into site_nodes
     (node_id, parent_id, name, object_id, directory_p, pattern_p)
    values
     (v_node_id, new__parent_id, new__name, new__object_id,
      new__directory_p, new__pattern_p);

     return v_node_id;

END;
$$ LANGUAGE plpgsql;


-- procedure delete


-- added
select define_function_args('site_node__delete','node_id');

--
-- procedure site_node__delete/1
--
CREATE OR REPLACE FUNCTION site_node__delete(
   delete__node_id integer
) RETURNS integer AS $$
DECLARE
BEGIN
    delete from site_nodes
    where node_id = delete__node_id;

    PERFORM acs_object__delete(delete__node_id);

    return 0;
END;
$$ LANGUAGE plpgsql;


-- function find_pattern


-- added
select define_function_args('site_node__find_pattern','node_id');

--
-- procedure site_node__find_pattern/1
--
CREATE OR REPLACE FUNCTION site_node__find_pattern(
   find_pattern__node_id integer
) RETURNS integer AS $$
DECLARE
  v_pattern_p                   site_nodes.pattern_p%TYPE;
  v_parent_id                   site_nodes.node_id%TYPE;
BEGIN
    if find_pattern__node_id is null then
--      raise no_data_found;
        raise exception 'NO DATA FOUND';
    end if;

    select pattern_p, parent_id into v_pattern_p, v_parent_id
    from site_nodes
    where node_id = find_pattern__node_id;

    if v_pattern_p = 't' then
      return find_pattern__node_id;
    else
      return site_node__find_pattern(v_parent_id);
    end if;

END;
$$ LANGUAGE plpgsql;



select define_function_args('site_node__node_id','url,parent_id;null');
--
-- procedure site_node__node_id/2
--
CREATE OR REPLACE FUNCTION site_node__node_id(
   p_url varchar,
   p_parent_id integer default null
) RETURNS integer AS $$
DECLARE
  v_pos                  integer;
  v_first                site_nodes.name%TYPE;
  v_rest                 text;
  v_node_id              integer;
  v_pattern_p            site_nodes.pattern_p%TYPE;
  v_url                  text;
  v_directory_p          site_nodes.directory_p%TYPE;
  v_trailing_slash_p     boolean;
BEGIN
    v_url := p_url;

    if substr(v_url, length(v_url), 1) = '/' then
      --
      -- The URL ends with a / so it must be a directory. Strip the
      -- trailing slash.
      --
      v_trailing_slash_p := true;
      v_url := substr(v_url, 1, length(v_url) - 1);
    end if;

    --
    -- Split the URL on the first "/" into v_first and v_rest.
    --
    select position('/' in v_url) into v_pos;

    if v_pos = 0 then
      --
      -- No slash found
      --
      v_first := v_url;
      v_rest := null;
    else
      --
      -- Split URL
      --
      v_first := substr(v_url, 1, v_pos - 1);
      v_rest := substr(v_url, v_pos + 1);
    end if;

    if p_parent_id is not null then
      select node_id, directory_p into v_node_id, v_directory_p
      from site_nodes
      where parent_id = p_parent_id
      and name = v_first;
    else
      --
      -- This is typically just the query on the (empty) top-node.
      --
      select node_id, directory_p into v_node_id, v_directory_p
      from site_nodes
      where parent_id is null
      and name = v_first;
    end if;

    if NOT FOUND then
        return site_node__find_pattern(p_parent_id);
    end if;

    --
    -- v_first was found.
    --
    if v_rest is null then
      --
      -- We are at the end of the URL. If we have a trailing slash and
      -- the site node is not a directory, return the result of
      -- find_pattern(). Otherwise, return the found node_id
      --
      if v_trailing_slash_p is true and v_directory_p is false then
        return site_node__find_pattern(p_parent_id);
      else
        return v_node_id;
      end if;
    else
      --
      -- Call the function recursively on the v_rest chunk
      --
      return site_node__node_id(v_rest, v_node_id);
    end if;
END;
$$ LANGUAGE plpgsql;



select define_function_args('site_node__url','node_id');
--
-- procedure site_node__url/1
--

CREATE FUNCTION inline_0()
RETURNS integer AS $inline_0$
BEGIN

    -- raise notice 'starting site-nodes doing the recursive part -- vguerra';

    IF cmp_pg_version('8.4') >= 0 THEN
        -- recursive site_nodes recursive - START

        CREATE OR REPLACE FUNCTION site_node__url(
           url__node_id integer
        ) RETURNS varchar AS $$

            WITH RECURSIVE site_nodes_path(parent_id, path, directory_p, node_id) as (

                select parent_id, ARRAY[name || case when directory_p then '/' else ' ' end]::text[] as path, directory_p, node_id
                from site_nodes where node_id = url__node_id

                UNION ALL

                select sn.parent_id, sn.name::text || snr.path , sn.directory_p, snr.parent_id
                from site_nodes sn join site_nodes_path snr on sn.node_id = snr.parent_id
                where snr.parent_id is not null

            ) select array_to_string(path,'/') from site_nodes_path where parent_id is null

        $$ LANGUAGE sql strict stable;

        -- recursive site_nodes END

    ELSE

        CREATE OR REPLACE FUNCTION site_node__url(
           url__node_id integer
        ) RETURNS varchar AS $$
        DECLARE
          v_parent_id            site_nodes.node_id%TYPE;
          v_name                 site_nodes.name%TYPE;
          v_directory_p          site_nodes.directory_p%TYPE;
        BEGIN
            if url__node_id is null then
              return '';
            end if;

            select parent_id, name, directory_p into
                   v_parent_id, v_name, v_directory_p
            from site_nodes
            where node_id = url__node_id;

            if v_directory_p = 't' then
              return site_node__url(v_parent_id) || v_name || '/';
            else
              return site_node__url(v_parent_id) || v_name;
            end if;

        END;
        $$ LANGUAGE plpgsql;

    END IF;

    return null;
END;
$inline_0$ LANGUAGE plpgsql;

select inline_0();
drop function inline_0();

--
-- Local variables:
--   mode: sql
--   indent-tabs-mode: nil
-- End: