-- Upgrade from the Rust (pgrx) implementation to the pure SQL one.
--
--     alter extension pg_statecharts update;
--
-- Three things change:
--
--   1. The `version` column moves off the `semver` extension's type and onto
--      the fsm.semver domain, an integer array, so that nothing has to be
--      compiled to install pg_statecharts and versions still sort correctly.
--      The versions themselves are unchanged, but their representation is not:
--      a column that read '1.10.0' now reads '{1,10,0}'. Use
--      fsm.semver_text(version) to render one. The canonical cast is now
--      fsm.to_semver(), and an unqualified to_semver() is recreated once the
--      `semver` extension has released the name, so migrations generated by
--      earlier versions still deploy, revert and verify unchanged.
--
--   2. fsm.import_scxml_files and fsm.gen_statechart_sqitch_migrations move
--      out to the separate pg_statecharts_dev extension. They read and write
--      files on the database server, and there is no reason for that to be
--      installed in production. Install pg_statecharts_dev on the machines
--      that need them.
--
--   3. The fsm tables and sequences are registered with pg_dump. 0.0.0 never
--      did that, so a backup taken under it holds every application row with
--      its state_machine_id and none of the machines those ids point at. Take
--      a fresh backup once this upgrade has been applied; the old ones cannot
--      be repaired.
--
-- Once the upgrade has been applied nothing references pg_statecharts.so any
-- more, so it can be deleted from $(pg_config --pkglibdir).

\echo Use "ALTER EXTENSION pg_statecharts UPDATE" to load this file. \quit

-- The trigger bodies installed below write public.ltree, for the pg_restore
-- reason explained where they are defined. Refuse the upgrade rather than
-- install bodies that cannot resolve their own types; the same check runs on a
-- fresh install, in sql/schema.sql.
do $ltree_check$
declare
  ltree_schema name;
begin
  if to_regtype('public.ltree') is null then
    select n.nspname into ltree_schema
    from pg_extension e
    join pg_namespace n on n.oid = e.extnamespace
    where e.extname = 'ltree';

    raise exception 'pg_statecharts requires the ltree extension in the public schema'
      using detail = case
              when ltree_schema is null then 'ltree is not installed'
              else format('ltree is installed in schema %I', ltree_schema)
            end,
            hint = 'alter extension ltree set schema public; then run the update again';
  end if;
end
$ltree_check$;

-- The old file generating functions were C functions backed by pg_statecharts.so.
drop function if exists fsm.import_scxml_files(text, boolean, boolean);
drop function if exists fsm.gen_statechart_sqitch_migrations(text, text, boolean, boolean);

-- Nothing in the pure SQL extension is a C function, so anything still backed
-- by C in fsm is a function 0.0.0 created under a signature the two drops
-- above do not match. Its pg_statecharts.so goes away with the old package, so
-- say so now rather than let the next caller find out.
do $leftover_c$
declare
  leftover text;
begin
  select string_agg(p.oid::regprocedure::text, ', ' order by p.oid::regprocedure::text)
  into leftover
  from pg_proc p
  join pg_namespace n on n.oid = p.pronamespace
  join pg_language l on l.oid = p.prolang
  where n.nspname = 'fsm' and l.lanname = 'c';

  if leftover is not null then
    raise warning 'C functions left in fsm after the upgrade: %', leftover
      using hint = 'these are backed by pg_statecharts.so, which the 0.1.0 '
                   'package removes; drop them by hand';
  end if;
end
$leftover_c$;

-- 0.0.0 shipped '^[a-za-z0-9_]+$' for this constraint: the A-Z range had been
-- flattened to a second a-z by a stray lowercasing, so state ids containing
-- capital letters were rejected even though the sqitch deployment of the same
-- schema accepted them. The corrected pattern only ever accepts more than the
-- old one did, so no existing row can fail the revalidation.
alter table fsm.state drop constraint if exists id_must_be_alphanumeric;
alter table fsm.state add constraint id_must_be_alphanumeric
  check (id ~ '^[a-zA-Z0-9_]+$');

-- Everything semver related lives in the fsm schema now, so that the only
-- thing competing with the `semver` extension for a name is the deliberate
-- compatibility shim at the end of this file.
create domain fsm.semver as integer[]
  check (array_ndims(value) = 1
         and array_lower(value, 1) = 1
         and cardinality(value) = 3
         and array_position(value, null) is null
         and value[1] >= 0
         and value[2] >= 0
         and value[3] >= 0);

comment on domain fsm.semver is $comment$
    A semantic version as a three element integer array: {major, minor, patch}.
    '1.10.0' is array[1, 10, 0].

    Stored as integers rather than text so that ordering is correct by default.
    Arrays compare element by element, so `order by version desc` ranks 1.10.0
    above 1.9.0 with no helper function involved.

    Build one with fsm.to_semver('1.10.0') and render one with
    fsm.semver_text(version). Casting to text gives the array form, '{1,10,0}'.

    Prerelease and build metadata suffixes (1.0.0-rc1, 1.0.0+build5) are not
    supported.
$comment$;

-- fsm.semver has no room for prerelease or build metadata suffixes, but 0.0.0
-- stored versions in the semver extension's type, which accepts 1.0.0-rc1 and
-- 1.0.0+build5 happily. Converting such a row would fail inside the ALTER
-- below with a bare "invalid input syntax for type integer", naming nothing.
-- Find them first and say which rows they are.
do $precheck$
  declare
    offending text;
  begin
    select string_agg(format('%s %s', name, version::text), E'\n' order by name, version::text)
    into offending
    from fsm.statechart
    where version::text !~ '^\d+\.\d+\.\d+$';

    if offending is not null then
      raise exception 'cannot upgrade: some statechart versions carry a prerelease or build suffix, which fsm.semver does not support'
        using detail = offending,
              hint = 'rename those versions to plain major.minor.patch first, e.g. '
                     'update fsm.statechart set version = to_semver(''1.0.0'') '
                     'where name = ''...'' and version = to_semver(''1.0.0-rc1''), '
                     'then run ALTER EXTENSION pg_statecharts UPDATE again';
    end if;
  end;
$precheck$;

-- Rewrites the column in place. The (name, version) unique index is rebuilt
-- automatically as part of the type change.
--
-- semver only offers an explicit cast to text, which is fine inside a USING
-- expression, and the check above means every stored value has exactly three
-- numeric components.
alter table fsm.statechart
  alter column version type fsm.semver
  using string_to_array(version::text, '.')::integer[]::fsm.semver;

do $upgrade$
  declare
    dependents text;
  begin
    if not exists (select 1 from pg_extension where extname = 'semver') then
      return;
    end if;

    -- CREATE EXTENSION ... CASCADE recorded pg_statecharts as depending on the
    -- semver extension. Nothing in pg_statecharts uses semver any more, but
    -- that catalog entry would still make DROP EXTENSION semver fail with
    -- "extension pg_statecharts depends on extension semver", so drop the edge.
    delete from pg_depend
    where classid = 'pg_extension'::regclass
      and objid = (select oid from pg_extension where extname = 'pg_statecharts')
      and refclassid = 'pg_extension'::regclass
      and refobjid = (select oid from pg_extension where extname = 'semver');

    -- Our own objects are all fsm qualified and would not collide, but the
    -- unqualified compatibility shim below can only be created once the semver
    -- extension has released the name, so semver genuinely has to go. If
    -- anything outside pg_statecharts still uses it we would rather fail loudly
    -- here than leave the database in a half migrated state.
    begin
      drop extension semver;
    exception when dependent_objects_still_exist then
      get stacked diagnostics dependents = pg_exception_detail;

      raise exception 'cannot remove the semver extension because it is still in use'
        using detail = dependents,
              hint = 'pg_statecharts no longer needs semver, but something else '
                     'in this database does. Migrate those objects to the '
                     'fsm.semver domain (or another type), then run '
                     'ALTER EXTENSION pg_statecharts UPDATE again.';
    end;
  end;
$upgrade$;

create or replace function fsm.to_semver(version text)
returns fsm.semver as
$$
  declare
    dot_count integer;
    padded text;
  begin
    if version is null then
      return null;
    end if;

    dot_count := length(version) - length(replace(version, '.', ''));

    if dot_count > 2 then
      raise exception 'invalid version %', quote_literal(version)
        using hint = 'expected at most three components, e.g. 1.2.3';
    end if;

    -- pads with '.0' if the provided value has fewer than two dots, so that
    -- both '1' and '1.2' are accepted and normalised to '1.0.0' and '1.2.0'
    padded := version || repeat('.0', 2 - dot_count);

    if padded !~ '^\d+\.\d+\.\d+$' then
      raise exception 'invalid version %', quote_literal(version)
        using hint = 'expected digits separated by dots, e.g. 1.2.3';
    end if;

    return string_to_array(padded, '.')::integer[]::fsm.semver;
  end;
$$ language plpgsql immutable parallel safe;

comment on function fsm.to_semver(text) is $comment$
    Parses text into an fsm.semver, padding out omitted components so that '1'
    and '1.2' become 1.0.0 and 1.2.0.

    This is the canonical name. An unqualified to_semver(text) is also created
    when that name is free, for the benefit of statechart migrations generated
    against older versions of pg_statecharts.
$comment$;

create or replace function fsm.semver_text(version fsm.semver)
returns text as
$$
  select array_to_string(version, '.')
$$ language sql immutable strict parallel safe;

comment on function fsm.semver_text(fsm.semver) is $comment$
    Renders a version the way people write it: fsm.semver_text(array[1,10,0])
    is '1.10.0'. Casting to text instead gives the array form, '{1,10,0}'.
$comment$;

-- Compatibility shim, recreating the unqualified to_semver(text) that
-- migrations generated by 0.0.0 call. The `semver` extension owned that name
-- until the DO block above dropped it, so by this point it is normally free.
-- It is still guarded: an install that never had `semver` may have something
-- else of that name, and taking it would be wrong. See sql/type/semver.sql.
do $shim$
  begin
    if exists (
      select 1
      from pg_proc p
      where p.proname = 'to_semver'
        and p.pronamespace = current_schema()::regnamespace
        and pg_get_function_identity_arguments(p.oid) = 'text'
    ) then
      raise warning 'to_semver(text) already exists in schema %, skipping the pg_statecharts compatibility shim', current_schema()
        using hint = 'use fsm.to_semver(text) instead; migrations generated by '
                     'pg_statecharts 0.0.0 will need regenerating';
      return;
    end if;

    create function to_semver(version text)
    returns fsm.semver as
    $body$
      select fsm.to_semver(version)
    $body$ language sql immutable parallel safe;

    comment on function to_semver(text) is $c$
    Deprecated alias for fsm.to_semver(text), kept so that statechart
    migrations generated by pg_statecharts 0.0.0 keep working. Not created if
    the name was already taken, so do not rely on it in new code.
    $c$;
  end;
$shim$;

-- Recreated only so that an upgraded database provably matches a fresh 0.1.0
-- install. The body is unchanged: "order by version desc" was already correct
-- under the semver type and is still correct over an integer array, because
-- both compare numerically rather than lexicographically.
create or replace function fsm.get_latest_statechart(named text)
returns setof fsm.statechart as
$$
    select *
    from fsm.statechart
    where name = named
    order by version desc
    limit 1
$$ language sql
    strict
    stable
    parallel safe
    rows 1;

-- The two trigger functions that fire while pg_restore loads fsm.state and
-- fsm.transition. pg_restore runs with an empty search_path, where a bare
-- ltree type or the ltree operators cannot be resolved, so the 0.0.0 bodies
-- made every restore of the fsm data fail. These bodies are the same as in a
-- fresh install: the ltree references carry their schema, public.
create or replace function fsm.trig_set_state_parent_path() returns trigger as
$$
    declare
        path public.ltree;
    begin

        if NEW.parent_id is null then
            NEW.parent_path = coalesce(NEW.statechart_id, OLD.statechart_id)::text::public.ltree;

        elseif TG_OP = 'INSERT' or OLD.parent_id is null or OLD.parent_id != NEW.parent_id then
            select parent_path operator(public.||) id
            from fsm.state
            where id = NEW.parent_id and statechart_id = NEW.statechart_id and not is_final
            into path;

            if path is null then
                raise exception 'Invalid parent_id. It should exist and not be final: %', NEW.parent_id;
            end if;

            new.parent_path = path;
        end if;

        new.node_path = new.parent_path operator(public.||) new.id;

        return new;
    end;
$$ language plpgsql;

create or replace function fsm.trig_check_no_duplicate_event_handler() returns trigger as
$$
  declare
      duplicate_transitions jsonb;
  begin
    with changed_event as (
      select distinct statechart_id, event
      from changed
    )
    -- look at the entire tree to see if the same event name is used
    -- for a transition
    select jsonb_agg(t.*) into duplicate_transitions
    from fsm.transition t
    -- need to get the source state info for the transition
    -- specifically, we need to get its node_path
    join fsm.state s
      on  s.statechart_id = t.statechart_id
      and s.id = t.source_state
    -- get all children and parents on the source_state by using the node_path
    -- it is the count of this children nodes that will determine
    -- if the same event is used in another transition
    --
    -- The ltree operators carry an explicit schema. This trigger fires while
    -- pg_restore loads fsm.transition, and pg_restore runs with an empty
    -- search_path, where a bare <@ or @> cannot be resolved and the restore
    -- fails. ltree is installed into public; see set_state_parent_path.sql.
    join fsm.state relative
      on  relative.statechart_id = t.statechart_id
      and (relative.node_path operator(public.<@) s.node_path or relative.node_path operator(public.@>) s.node_path)
      and relative.id <> s.id
    join fsm.transition ct
      on  ct.statechart_id = relative.statechart_id
      and ct.source_state = relative.id
    join changed_event ce
      on  ce.statechart_id = ct.statechart_id
      and ce.event = ct.event
      and ce.statechart_id = t.statechart_id
      and ce.event = t.event;

    if jsonb_array_length(duplicate_transitions) > 0
    then
      raise exception $m$
      Cannot use the same event name for multiple transations in the same tree: %
      $m$, jsonb_pretty(duplicate_transitions);
    end if;

    return null;
  end;
$$ language plpgsql;

-- pg_dump leaves out an extension's tables unless the extension registers them
-- as configuration tables. This is what makes the fsm data part of a backup
-- from here on; the sequences are included so that a restored database keeps
-- numbering where the dumped one stopped.
--
-- fsm.state_machine_event and its sequence are left out on purpose. The table
-- is an inbox queue that fsm.handle_machine_events drains in the transaction
-- that fills it, so a committed row has already been applied to
-- fsm.state_machine_state and the state it produced is in the backup. The rest
-- is a debugging log that grows far too fast to carry in every dump, and
-- restoring it would re-fire the insert trigger on rows that pg_restore loads,
-- handling any stray unhandled event a second time on the restored database.
select pg_catalog.pg_extension_config_dump('fsm.statechart', '');
select pg_catalog.pg_extension_config_dump('fsm.state', '');
select pg_catalog.pg_extension_config_dump('fsm.transition', '');
select pg_catalog.pg_extension_config_dump('fsm.state_machine', '');
select pg_catalog.pg_extension_config_dump('fsm.state_machine_state', '');
select pg_catalog.pg_extension_config_dump('fsm.statechart_id_seq', '');
select pg_catalog.pg_extension_config_dump('fsm.state_machine_id_seq', '');