\echo Use "CREATE EXTENSION pg_statecharts_dev" to load this file. \quit
-- pg_statecharts_dev 0.1.0
-- GENERATED FILE, DO NOT EDIT. Concatenated from the files in sql/parts.txt.

-- sql/type/scxml.sql
-- Intermediate representation of a parsed .scxml document, sitting between the
-- XML and the fsm.state / fsm.transition tables. Both import_scxml_files and
-- gen_statechart_sqitch_migrations work off these, so the two can never
-- disagree about what a chart means.

create type fsm.scxml_state as (
  id text,
  name text,
  parent_id text,
  is_initial boolean,
  is_final boolean,
  on_entry fsm_callback_name[],
  on_exit fsm_callback_name[]
);

create type fsm.scxml_transition as (
  event text,
  source_state text,
  target_state text
);

-- sql/function/files.sql
-- Finding, reading and writing files.
--
-- All of this happens on the database *server*, not on the client, so the
-- paths are paths on the database host and the postgres process needs
-- permission to use them. Running against Postgres in a container means the
-- paths have to be the ones inside the container.
--
-- The calling role needs pg_read_server_files to find and read .scxml files
-- and pg_write_server_files to generate migrations (superuser has both):
--
--     grant pg_read_server_files, pg_write_server_files to my_dev_user;

-- Strips trailing slashes so that joining paths never produces "a//b".
create or replace function fsm.__normalise_path(path text)
returns text as
$$
  select case
    when path is null then null
    when path = '' then ''
    else coalesce(nullif(regexp_replace(path, '/+$', ''), ''), '/')
  end
$$ language sql immutable;

-- Every .scxml file at or below source_path, in sorted order.
--
-- source_path may be a single .scxml file or a directory. Directories are only
-- descended into when recursive is true, and hidden entries are skipped.
create or replace function fsm.__find_scxml_files(source_path text, recursive boolean)
returns setof text as
$$
  declare
    path text := fsm.__normalise_path(source_path);
    entry text;
    child text;
  begin
    if path is null or path = '' then
      raise exception 'no source path given';
    end if;

    if pg_stat_file(path, true) is null then
      raise exception 'path does not exist: %', path
        using hint = 'the path is resolved on the database host, so when '
                     'Postgres runs in a container it has to be the path '
                     'inside the container';
    end if;

    if not (pg_stat_file(path)).isdir then
      if path !~ '\.scxml$' then
        raise exception 'not an .scxml file: %', path;
      end if;

      return next path;
      return;
    end if;

    for entry in select f from pg_ls_dir(path) as f loop
      child := path || '/' || entry;

      if (pg_stat_file(child)).isdir then
        if recursive and entry !~ '^\.' then
          return query select * from fsm.__find_scxml_files(child, true);
        end if;
      elsif entry ~ '\.scxml$' and entry !~ '^\.' then
        return next child;
      end if;
    end loop;
  end;
$$ language plpgsql stable;

-- Writes content to file_path, replacing whatever was there.
--
-- There is no "write a file" function in SQL, so this goes through COPY. Text
-- format would mangle backslashes and newlines, so the content is split into
-- one row per line and written as CSV with a delimiter and quote character
-- that cannot occur in the content. CSV never escapes backslashes, and with
-- nothing to quote the rows land in the file byte for byte. Empty lines are
-- written as NULL because CSV renders an empty *string* as a quoted "".
--
-- The one thing this cannot reproduce is a file that does not end in a
-- newline, because COPY terminates every row it writes. Content without a
-- trailing newline gets one. Every file this extension generates ends in a
-- newline anyway, so it makes no difference in practice.
create or replace function fsm.__write_file(file_path text, content text)
returns void as
$$
  declare
    dir text;
  begin
    -- deliberately not STRICT: a strict function returns NULL without running,
    -- which would turn "write this file" into a silent no-op
    if file_path is null then
      raise exception 'no output path given';
    end if;

    if content is null then
      raise exception 'refusing to write % because the content is null', file_path;
    end if;

    if content ~ E'[\r\x01\x02]' then
      raise exception 'refusing to write % because its content contains a carriage return or a control character',
        file_path
        using detail = 'those characters cannot survive the COPY based writer intact';
    end if;

    -- COPY quotes a line that is exactly "\." whatever the quote setting,
    -- because unquoted it would be read back as end of data. Nothing this
    -- extension generates contains such a line; refuse rather than corrupt.
    if content ~ E'(^|\n)\\\\\\.(\n|$)' then
      raise exception 'refusing to write % because a line consists of just a backslash and a dot',
        file_path
        using detail = 'COPY would quote that line and the file would no longer be written byte for byte';
    end if;

    if file_path !~ '/' then
      -- a bare file name is resolved against the data directory
      dir := '.';
    else
      dir := coalesce(nullif(regexp_replace(file_path, '/[^/]*$', ''), ''), '/');
    end if;

    if pg_stat_file(dir, true) is null then
      raise exception 'output directory does not exist: %', dir
        using hint = format('create it first, for example: mkdir -p %s', dir);
    end if;

    if not (pg_stat_file(dir)).isdir then
      raise exception 'not a directory: %', dir;
    end if;

    -- checked rather than "if not exists" so that repeated calls in one
    -- transaction do not each emit a NOTICE
    if to_regclass('pg_temp.__pg_statecharts_write') is null then
      create temp table __pg_statecharts_write (
        ord bigint,
        line text
      ) on commit drop;
    else
      truncate __pg_statecharts_write;
    end if;

    -- COPY terminates every row with a newline, so a trailing newline in the
    -- content would otherwise come out doubled.
    insert into __pg_statecharts_write (ord, line)
    select ord, nullif(line, '')
    from unnest(
      string_to_array(
        case when right(content, 1) = E'\n' then left(content, -1) else content end,
        E'\n'
      )
    ) with ordinality as lines(line, ord);

    begin
      execute format(
        'copy (select line from __pg_statecharts_write order by ord) to %L '
        'with (format csv, delimiter %L, quote %L, escape %L)',
        file_path, E'\x01', E'\x02', E'\x02'
      );
    exception when others then
      raise exception 'failed to write %: %', file_path, sqlerrm
        using hint = 'the file is written by the PostgreSQL server, so the '
                     'postgres process needs write permission on the '
                     'directory. When Postgres runs in a container, start the '
                     'container as your own user so the generated files end up '
                     'owned by you: docker compose run --user "$(id -u):$(id -g)"';
    end;
  end;
$$ language plpgsql volatile;

-- sql/function/parse_scxml.sql
-- Parsing .scxml documents into fsm.scxml_state / fsm.scxml_transition rows.
--
-- Everything here matches elements by local-name() rather than by namespace,
-- so a document is accepted whether or not it declares the SCXML namespace.
--
-- Attributes are always read through xmltable rather than by casting the
-- result of xpath() to text. Casting re-escapes the XML special characters, so
-- a state named "Pick & Pack" would come back as "Pick & Pack";
-- xmltable hands back the decoded value.

create or replace function fsm.__xml_attr(node xml, attribute_name text)
returns text as
$$
  select value
  from xmltable('/*' passing node columns value text path ('@' || attribute_name))
$$ language sql immutable;

-- Collects the <script src="..."/> children of an <onentry>/<onexit> element
-- into the callback array shape that fsm.state stores.
--
-- A src without a dot is taken to live in the public schema, otherwise it is
-- split on the first dot: "billing.charge_card" is charge_card in the billing
-- schema.
create or replace function fsm.__scxml_callbacks(node xml, element_name text)
returns fsm_callback_name[] as
$$
  select coalesce(
    array_agg(
      case
        when strpos(src, '.') = 0
          then row('public', src)::fsm_callback_name
        else
          row(
            left(src, strpos(src, '.') - 1),
            substr(src, strpos(src, '.') + 1)
          )::fsm_callback_name
      end
      order by ord
    ),
    array[]::fsm_callback_name[]
  )
  from xmltable(
    ('/*/*[local-name()="' || element_name || '"]/*[local-name()="script"]')
    passing node
    columns
      src text path '@src',
      ord for ordinality
  )
$$ language sql immutable strict;

-- Walks one <state>/<final>/<parallel> element and everything below it,
-- emitting one row per state in document order.
--
-- parent_id and is_initial cannot be worked out from the element itself, so
-- the caller passes them down: the parent knows which of its children the
-- <initial> element pointed at.
create or replace function fsm.__scxml_states(
  node xml,
  parent_id text,
  is_initial boolean
)
returns setof fsm.scxml_state as
$$
  declare
    tag text;
    state_id text;
    state_name text;
    is_final boolean;
    initial_target text;
    children xml[];
    child xml;
  begin
    tag := (xpath('local-name(/*)', node))[1]::text;
    state_id := fsm.__xml_attr(node, 'id');

    if state_id is null then
      raise exception 'found a <%> element without an id attribute', tag
        using hint = 'every state, final and parallel element needs a unique id';
    end if;

    -- Matches the previous behaviour: an unnamed state gets an empty name
    -- rather than being rejected.
    state_name := coalesce(fsm.__xml_attr(node, 'name'), '');
    is_final := tag = 'final';

    return next row(
      state_id,
      state_name,
      parent_id,
      is_initial,
      is_final,
      fsm.__scxml_callbacks(node, 'onentry'),
      -- a final state is never exited, so it can have no on_exit callbacks
      case
        when is_final then array[]::fsm_callback_name[]
        else fsm.__scxml_callbacks(node, 'onexit')
      end
    )::fsm.scxml_state;

    children := xpath(
      '/*/*[local-name()="state" or local-name()="final" or local-name()="parallel"]',
      node
    );

    if coalesce(array_length(children, 1), 0) = 0 then
      return;
    end if;

    if tag = 'parallel' then
      -- Every region of a parallel state is entered at once, so all of its
      -- children are initial and an <initial> element would be meaningless.
      foreach child in array children loop
        return query select * from fsm.__scxml_states(child, state_id, true);
      end loop;

      return;
    end if;

    initial_target := (
      select fsm.__xml_attr(t, 'target')
      from unnest(
        xpath('/*/*[local-name()="initial"]/*[local-name()="transition"]', node)
      ) as t
      limit 1
    );

    if initial_target is null then
      raise exception 'state "%" has child states but no initial state', state_id
        using hint = format(
          'add <initial><transition target="..."/></initial> inside <%s id="%s">',
          tag, state_id
        );
    end if;

    if not exists (
      select 1 from unnest(children) as c where fsm.__xml_attr(c, 'id') = initial_target
    ) then
      raise exception 'state "%" declares "%" as its initial state, but has no such child',
        state_id, initial_target
        using detail = format(
          'child states are: %s',
          (
            select coalesce(string_agg(fsm.__xml_attr(c, 'id'), ', '), '(none)')
            from unnest(children) as c
          )
        );
    end if;

    foreach child in array children loop
      return query
        select *
        from fsm.__scxml_states(child, state_id, fsm.__xml_attr(child, 'id') = initial_target);
    end loop;
  end;
$$ language plpgsql immutable;

-- All the states in a document, in document order.
create or replace function fsm.scxml_states(doc xml)
returns setof fsm.scxml_state as
$$
  declare
    root_initial text;
    children xml[];
    child xml;
  begin
    root_initial := fsm.__xml_attr(doc, 'initial');

    if root_initial is null then
      raise exception 'the <scxml> element has no initial attribute'
        using hint = 'add initial="<id of the starting state>" to <scxml>';
    end if;

    children := xpath(
      '/*[local-name()="scxml"]/*[local-name()="state" or local-name()="final" or local-name()="parallel"]',
      doc
    );

    if coalesce(array_length(children, 1), 0) = 0 then
      raise exception 'the statechart has no states';
    end if;

    if not exists (
      select 1 from unnest(children) as c where fsm.__xml_attr(c, 'id') = root_initial
    ) then
      raise exception 'the statechart declares "%" as its initial state, but has no such top level state',
        root_initial
        using detail = format(
          'top level states are: %s',
          (
            select string_agg(fsm.__xml_attr(c, 'id'), ', ')
            from unnest(children) as c
          )
        );
    end if;

    foreach child in array children loop
      return query
        select *
        from fsm.__scxml_states(child, null, fsm.__xml_attr(child, 'id') = root_initial);
    end loop;
  end;
$$ language plpgsql immutable strict;

-- All the transitions in a document, in document order.
--
-- Transitions inside <initial> are excluded because they are how a compound
-- state names its default child, not real transitions.
--
-- Every other <transition> has to carry both an event and a target, and must
-- not sit inside a <final> state. Eventless and targetless transitions exist
-- in SCXML but this implementation has never supported them: fsm.transition
-- has NOT NULL on both columns, and a final state cannot be left. They are
-- rejected here, by name, rather than left to surface as a bare NOT NULL
-- violation from the importer or -- worse -- be dropped from a generated
-- migration because string_agg skips the NULL row.
create or replace function fsm.scxml_transitions(doc xml)
returns setof fsm.scxml_transition as
$$
  declare
    t record;
  begin
    for t in
      select
        tagged.tag,
        fsm.__xml_attr(states.node, 'id') as source_state,
        x.event,
        x.target
      from unnest(
        xpath('//*[local-name()="state" or local-name()="parallel" or local-name()="final"]', doc)
      ) with ordinality as states(node, state_ord)
      cross join lateral (
        select (xpath('local-name(/*)', states.node))[1]::text as tag
      ) as tagged(tag)
      cross join lateral xmltable(
        '/*/*[local-name()="transition"]'
        passing states.node
        columns
          event text path '@event',
          target text path '@target',
          transition_ord for ordinality
      ) as x
      order by states.state_ord, x.transition_ord
    loop
      if t.tag = 'final' then
        raise exception 'final state "%" has a transition, but a final state cannot be left',
          t.source_state
          using hint = 'remove the <transition> from <final id="' || t.source_state || '">, '
                       'or make it a <state>';
      end if;

      if t.event is null or t.event = '' then
        raise exception 'a transition out of state "%" has no event attribute', t.source_state
          using hint = 'every <transition> needs event="..." and target="..."; '
                       'eventless transitions are not supported';
      end if;

      if t.target is null or t.target = '' then
        raise exception 'the transition on event "%" out of state "%" has no target attribute',
          t.event, t.source_state
          using hint = 'every <transition> needs event="..." and target="..."; '
                       'targetless (internal) transitions are not supported';
      end if;

      return next row(t.event, t.source_state, t.target)::fsm.scxml_transition;
    end loop;
  end;
$$ language plpgsql immutable strict;

-- Reads and parses a .scxml file, reporting which file failed rather than just
-- that some XML somewhere was malformed.
create or replace function fsm.__read_scxml(file_path text)
returns xml as
$$
  declare
    contents text;
    doc xml;
    root_tag text;
  begin
    begin
      contents := pg_read_file(file_path);
    exception when others then
      raise exception 'failed to read %: %', file_path, sqlerrm
        using hint = 'the file is read by the PostgreSQL server, so it has to '
                     'exist on the database host and be readable by the '
                     'postgres process';
    end;

    begin
      -- xmlparse(document ...) rather than ::xml, because the default
      -- XMLOPTION is CONTENT, under which any old text is a valid XML
      -- fragment. Casting "hello" to xml succeeds; parsing it as a document
      -- fails, which is what we want.
      doc := xmlparse(document contents);
    exception when others then
      raise exception 'failed to parse SCXML in %: %', file_path, sqlerrm
        using detail = 'the file has to be a well formed XML document';
    end;

    root_tag := (xpath('local-name(/*)', doc))[1]::text;

    if root_tag is distinct from 'scxml' then
      raise exception '% is not an SCXML document', file_path
        using detail = format('the root element is <%s>, expected <scxml>', root_tag);
    end if;

    return doc;
  end;
$$ language plpgsql stable strict;

-- The chart name declared on the <scxml> element.
create or replace function fsm.__scxml_name(doc xml, file_path text)
returns text as
$$
  declare
    chart_name text;
  begin
    chart_name := fsm.__xml_attr(doc, 'name');

    if chart_name is null or chart_name = '' then
      raise exception 'the <scxml> element in % has no name attribute', file_path
        using hint = 'add name="<statechart name>" to <scxml>';
    end if;

    return chart_name;
  end;
$$ language plpgsql immutable;

-- The chart version declared on the <scxml> element, exactly as written.
--
-- The raw string is returned rather than an fsm.semver because it is what ends
-- up in the generated migration's file name and in its fsm.to_semver(...) call.
-- Validation still happens here so that a bad version is reported against the
-- file it came from.
create or replace function fsm.__scxml_version(doc xml, file_path text)
returns text as
$$
  declare
    raw_version text;
    normalised fsm.semver;
  begin
    raw_version := fsm.__xml_attr(doc, 'version');

    if raw_version is null or raw_version = '' then
      raise exception 'the <scxml> element in % has no version attribute', file_path
        using hint = 'add version="1.0.0" to <scxml>';
    end if;

    begin
      normalised := fsm.to_semver(raw_version);
    exception when others then
      raise exception 'the <scxml> element in % has an invalid version %',
        file_path, quote_literal(raw_version)
        using hint = 'the version has to look like 1, 1.2 or 1.2.3';
    end;

    return raw_version;
  end;
$$ language plpgsql immutable;

-- sql/function/import_scxml_files.sql
-- Loads .scxml files straight into fsm.statechart / fsm.state / fsm.transition.
--
-- This is the shortcut for iterating on a chart locally. Use
-- fsm.gen_statechart_sqitch_migrations when the chart should be committed and
-- deployed like any other schema change.
--
-- Everything happens in the caller's transaction, so a failure part way
-- through leaves nothing behind.
-- Inserts the states and transitions of a parsed document under an existing
-- fsm.statechart row.
--
-- This is the single place where a chart's rows are written, shared by the
-- importer and by the migration generator's dry run, so that the two cannot
-- come to different conclusions about whether a chart is valid: every trigger
-- on fsm.state and fsm.transition fires here, for both.
create or replace function fsm.__insert_chart_definition(chart_id bigint, doc xml)
returns void as
$$
  insert into fsm.state (
    statechart_id, id, name, parent_id, is_initial, is_final, on_entry, on_exit
  )
  select
    chart_id, s.id, s.name, s.parent_id, s.is_initial, s.is_final, s.on_entry, s.on_exit
  from fsm.scxml_states(doc) as s;

  insert into fsm.transition (statechart_id, event, source_state, target_state)
  select chart_id, t.event, t.source_state, t.target_state
  from fsm.scxml_transitions(doc) as t;
$$ language sql volatile;

create or replace function fsm.import_scxml_files(
  source_path text,
  recursive boolean default false,
  on_conflict_do_nothing boolean default false
)
returns setof fsm.statechart as
$$
  declare
    file_path text;
    doc xml;
    chart fsm.statechart%rowtype;
    chart_name text;
    chart_version text;
    missing_functions text;
    err_message text;
    err_detail text;
    err_hint text;
    err_code text;
  begin
    for file_path in
      select * from fsm.__find_scxml_files(source_path, recursive) order by 1
    loop
      begin
        doc := fsm.__read_scxml(file_path);
        chart_name := fsm.__scxml_name(doc, file_path);
        chart_version := fsm.__scxml_version(doc, file_path);

        if on_conflict_do_nothing then
          insert into fsm.statechart (name, version)
          values (chart_name, fsm.to_semver(chart_version))
          on conflict do nothing
          returning statechart.* into chart;

          -- already imported, leave the existing chart untouched
          if not found then
            continue;
          end if;
        else
          begin
            insert into fsm.statechart (name, version)
            values (chart_name, fsm.to_semver(chart_version))
            returning statechart.* into chart;
          exception when unique_violation then
            raise exception '% version % has already been imported',
              chart_name, chart_version
              using hint = 'pass on_conflict_do_nothing => true to skip charts '
                           'that are already in the database, or bump the '
                           'version attribute in the .scxml file';
          end;
        end if;

        perform fsm.__insert_chart_definition(chart.id, doc);

        -- The on_entry/on_exit callbacks are looked up by name at run time, so
        -- a typo would otherwise only show up when the machine reaches that
        -- state. Catch it at import time instead.
        select string_agg(distinct format('%s.%s', schema_name, function_name), ', ')
        into missing_functions
        from fsm.state
        cross join lateral unnest(state.on_entry || state.on_exit)
        where state.statechart_id = chart.id
          and not exists (
            select 1
            from pg_proc p
            join pg_namespace n on p.pronamespace = n.oid
            where n.nspname = schema_name
              and p.proname = function_name
              and p.pronargs = 1
              and p.proargtypes[0] = 'fsm_event_payload'::regtype::oid
              -- an ordinary function, not a procedure, aggregate or window
              -- function: the runtime invokes callbacks with SELECT, which
              -- only works for prokind = 'f'
              and p.prokind = 'f'
          );

        if missing_functions is not null then
          -- rendered, because fsm.semver is an integer array and '{1,0,0}' is
          -- not what anyone wants to read in an error message
          raise exception '% version % references functions that do not exist: %',
            chart.name, fsm.semver_text(chart.version), missing_functions
            using hint = 'every on_entry and on_exit callback has to already '
                         'exist as an ordinary function taking exactly one '
                         'fsm_event_payload argument';
        end if;

        return next chart;
      exception when others then
        get stacked diagnostics
          err_code = returned_sqlstate,
          err_message = message_text,
          err_detail = pg_exception_detail,
          err_hint = pg_exception_hint;

        raise exception using
          errcode = err_code,
          message = err_message,
          detail = trim(both E'\n' from
            coalesce(err_detail, '') || E'\n' || format('while importing %s', file_path)),
          hint = nullif(err_hint, '');
      end;
    end loop;
  end;
$$ language plpgsql volatile;

comment on function fsm.import_scxml_files(text, boolean, boolean) is $comment$
    Reads every .scxml file at source_path and inserts it into the statechart
    tables, returning the fsm.statechart rows that were created.

    source_path may be a single file or a directory, and is resolved on the
    database host rather than on the client. Set recursive to also descend into
    subdirectories, and on_conflict_do_nothing to silently skip charts whose
    name and version are already in the database.

    Requires the pg_read_server_files role (or superuser).
$comment$;

-- sql/function/gen_statechart_sqitch_migrations.sql
-- Turning .scxml files into sqitch migrations.
--
-- The generated files are byte for byte what the previous Rust implementation
-- produced, so regenerating an existing chart does not show up as a diff.

-- The sqitch change name for a chart, e.g. statechart/billing/checkout-1.2.0.
-- Dots in the chart name become directory separators.
create or replace function fsm.__migration_name(chart_name text, chart_version text)
returns text as
$$
  select 'statechart/' || replace(chart_name, '.', '/') || '-' || chart_version
$$ language sql immutable strict;

-- Renders a callback array as SQL row literals: ('public', 'do_thing')
create or replace function fsm.__callback_literals(callbacks fsm_callback_name[])
returns text as
$$
  select coalesce(
    string_agg(
      '(' || quote_literal(c.schema_name) || ', ' || quote_literal(c.function_name) || ')',
      ',' order by c.ord
    ),
    ''
  )
  from unnest(callbacks) with ordinality as c(schema_name, function_name, ord)
$$ language sql immutable strict;

-- The "deploy" half of the migration: insert the chart, its states and its
-- transitions.
create or replace function fsm.__migration_deploy(
  doc xml,
  project text,
  chart_name text,
  chart_version text
)
returns text as
$deploy$
  declare
    state_rows text;
    transition_rows text;
  begin
    select string_agg(
      '(chart, '
        || quote_literal(s.id) || ', '
        || quote_literal(s.name) || ', '
        || coalesce(quote_literal(s.parent_id), 'null') || ', '
        || s.is_initial::text || ', '
        || s.is_final::text || ', '
        || 'array[' || fsm.__callback_literals(s.on_entry) || ']::fsm_callback_name[], '
        || 'array[' || fsm.__callback_literals(s.on_exit) || ']::fsm_callback_name[])',
      E',\n' order by s.ord
    )
    into state_rows
    from fsm.scxml_states(doc)
      with ordinality as s(id, name, parent_id, is_initial, is_final, on_entry, on_exit, ord);

    select string_agg(
      '(chart, '
        || quote_literal(t.event) || ', '
        || quote_literal(t.source_state) || ', '
        || quote_literal(t.target_state) || ')',
      E',\n' order by t.ord
    )
    into transition_rows
    from fsm.scxml_transitions(doc)
      with ordinality as t(event, source_state, target_state, ord);

    return
      '-- Deploy ' || project || ':'
        || fsm.__migration_name(chart_name, chart_version) || ' to pg' || E'\n'
      || E'\n'
      || '-- FILE AUTOMATICALLY GENERATED. MANUAL CHANGES MIGHT BE OVERWRITTEN' || E'\n'
      || E'\n'
      || 'BEGIN;' || E'\n'
      || 'do $$' || E'\n'
      || 'declare' || E'\n'
      || 'chart bigint;' || E'\n'
      || 'begin' || E'\n'
      || 'insert into fsm.statechart (name, version) values ('
        || quote_literal(chart_name) || ', fsm.to_semver('
        || quote_literal(chart_version) || ')) returning id into chart;' || E'\n'
      || 'insert into fsm.state (statechart_id, id, name, parent_id, is_initial, is_final, on_entry, on_exit) values' || E'\n'
      || state_rows || ';' || E'\n'
      -- A chart with no transitions at all is degenerate but should still
      -- produce a migration that runs, so the insert is left out entirely
      -- rather than emitted with an empty VALUES list.
      || case
           when transition_rows is null then ''
           else 'insert into fsm.transition (statechart_id, event, source_state, target_state) values' || E'\n'
                || transition_rows || ';' || E'\n'
         end
      || 'end' || E'\n'
      || '$$;' || E'\n'
      || 'COMMIT;' || E'\n';
  end;
$deploy$ language plpgsql immutable;

create or replace function fsm.__migration_revert(
  project text,
  chart_name text,
  chart_version text
)
returns text as
$$
  select
    '-- Revert ' || project || ':'
      || fsm.__migration_name(chart_name, chart_version) || ' from pg' || E'\n'
    || E'\n'
    || '-- FILE AUTOMATICALLY GENERATED. MANUAL CHANGES MIGHT BE OVERWRITTEN' || E'\n'
    || E'\n'
    || E'\n'
    || 'BEGIN;' || E'\n'
    || E'\n'
    || 'with chart as (' || E'\n'
    || '    delete from fsm.statechart' || E'\n'
    || '    where name = ' || quote_literal(chart_name) || E'\n'
    || '    and version = fsm.to_semver(' || quote_literal(chart_version) || ')' || E'\n'
    || '    returning id' || E'\n'
    || ')' || E'\n'
    || 'delete from fsm.state' || E'\n'
    || '    where statechart_id = (select id from chart);' || E'\n'
    || E'\n'
    || 'COMMIT;' || E'\n'
$$ language sql immutable strict;

create or replace function fsm.__migration_verify(
  project text,
  chart_name text,
  chart_version text
)
returns text as
$verify$
  select
    '-- Verify ' || project || ':'
      || fsm.__migration_name(chart_name, chart_version) || ' on pg' || E'\n'
    || E'\n'
    || '-- FILE AUTOMATICALLY GENERATED. MANUAL CHANGES MIGHT BE OVERWRITTEN' || E'\n'
    || E'\n'
    || 'BEGIN;' || E'\n'
    || E'\n'
    || '-- Verify that the statechart is added' || E'\n'
    || 'select 1 / count(*)' || E'\n'
    || 'from fsm.statechart' || E'\n'
    || 'where' || E'\n'
    || '    name = ' || quote_literal(chart_name) || E'\n'
    || '    and version = fsm.to_semver(' || quote_literal(chart_version) || ');' || E'\n'
    || E'\n'
    || '-- Verify that the functions that the statechart depends on exist' || E'\n'
    || 'do $$' || E'\n'
    || 'declare' || E'\n'
    || '    missing_funcs_count_ int;' || E'\n'
    || '    missing_funcs_ text;' || E'\n'
    || 'begin' || E'\n'
    || E'\n'
    || 'select' || E'\n'
    || '  string_agg(distinct format(''%s.%s'', schema_name, function_name), '', ''),' || E'\n'
    || '  count(*)' || E'\n'
    || 'into' || E'\n'
    || '  missing_funcs_,' || E'\n'
    || '  missing_funcs_count_' || E'\n'
    || 'from fsm.statechart' || E'\n'
    || 'join fsm.state' || E'\n'
    || '    on state.statechart_id = statechart.id' || E'\n'
    || ', lateral unnest(on_entry || on_exit)' || E'\n'
    || 'where' || E'\n'
    || '  statechart.name = ' || quote_literal(chart_name) || E'\n'
    || '  and statechart.version = fsm.to_semver(' || quote_literal(chart_version) || ')' || E'\n'
    || '  and not exists (' || E'\n'
    || '    select 1' || E'\n'
    || '    from pg_proc p' || E'\n'
    || '    join pg_namespace n' || E'\n'
    || '      on p.pronamespace = n.oid' || E'\n'
    || '    where' || E'\n'
    || '      n.nspname = schema_name' || E'\n'
    || '      and p.proname = function_name' || E'\n'
    || '      and p.pronargs = 1' || E'\n'
    || '      and p.proargtypes[0] = ''fsm_event_payload''::regtype::oid' || E'\n'
    || '  );' || E'\n'
    || E'\n'
    || 'if missing_funcs_count_ > 0 then' || E'\n'
    || '  raise exception' || E'\n'
    || '    $err$' || E'\n'
    || E'\n'
    || '    One or more missing or invalid functions: %' || E'\n'
    || '    All functions must take exactly one argument of the type fsm_event_payload' || E'\n'
    || E'\n'
    || '    $err$, missing_funcs_;' || E'\n'
    || 'end if;' || E'\n'
    || E'\n'
    || 'end' || E'\n'
    || '$$;' || E'\n'
    || E'\n'
    || 'ROLLBACK;' || E'\n'
$verify$ language sql immutable strict;

-- Runs a chart through the real fsm tables and throws the result away.
--
-- Parsing catches what can be seen in the XML alone. Everything else that
-- makes a chart invalid -- a transition between states with different parents,
-- an event handled at two levels of the same subtree, a target that is not a
-- state, two transitions on the same event out of one state -- is enforced by
-- the triggers and constraints on fsm.state and fsm.transition. A generated
-- migration that violates one of them would only fail at deploy time, far from
-- the file that caused it. So the generator inserts the chart for real, lets
-- every trigger fire, and then rolls the subtransaction back. The error is
-- exactly the one deploying would have produced, raised now.
--
-- The chart is inserted under a throwaway name so that this works even when
-- the same chart and version already sit in the database, as they do on a
-- development machine that imported it earlier.
create or replace function fsm.__check_chart_deploys(doc xml, chart_version text)
returns void as
$$
  declare
    chart_id bigint;
  begin
    begin
      insert into fsm.statechart (name, version)
      values ('__pg_statecharts_dry_run_' || md5(clock_timestamp()::text || random()::text),
              fsm.to_semver(chart_version))
      returning id into chart_id;

      perform fsm.__insert_chart_definition(chart_id, doc);

      -- The only way to abandon a subtransaction from plpgsql is to raise out
      -- of the block. This private SQLSTATE is caught right below; anything
      -- else raised above it is a genuine problem and propagates.
      raise exception using errcode = 'P0DRY', message = 'dry run complete';
    exception when sqlstate 'P0DRY' then
      null;
    end;
  end;
$$ language plpgsql volatile;

-- Generates a sqitch migration for every .scxml file at source_path and adds
-- it to the sqitch plan.
create or replace function fsm.gen_statechart_sqitch_migrations(
  source_path text,
  sqitch_plan_file_path text,
  recursive boolean default false
)
returns void as
$gen$
  declare
    plan_contents text;
    plan_project text;
    sqitch_dir text;
    plan_additions text := '';
    migration record;
    timestamp_str text;
  begin
    if pg_stat_file(sqitch_plan_file_path, true) is null then
      raise exception 'sqitch plan file does not exist: %', sqitch_plan_file_path
        using hint = 'the path is resolved on the database host, so when '
                     'Postgres runs in a container it has to be the path '
                     'inside the container';
    end if;

    begin
      plan_contents := pg_read_file(sqitch_plan_file_path);
    exception when others then
      raise exception 'failed to read sqitch plan file %: %',
        sqitch_plan_file_path, sqlerrm;
    end;

    select trim(substring(line from '^%project=(.*)$'))
    into plan_project
    from unnest(string_to_array(plan_contents, E'\n')) as line
    where line ~ '^%project='
    limit 1;

    if plan_project is null or plan_project = '' then
      raise exception 'could not find a %%project= line in %', sqitch_plan_file_path
        using hint = 'is this really a sqitch plan file?';
    end if;

    -- The migrations go next to the plan file. A bare file name means the data
    -- directory, which is where the server resolves relative paths.
    if sqitch_plan_file_path !~ '/' then
      sqitch_dir := '.';
    else
      sqitch_dir := coalesce(
        nullif(regexp_replace(sqitch_plan_file_path, '/[^/]*$', ''), ''),
        '/'
      );
    end if;

    -- Everything is parsed and rendered up front so that a chart that fails to
    -- parse does not leave half the migrations written to disk.
    if to_regclass('pg_temp.__pg_statecharts_migrations') is null then
      create temp table __pg_statecharts_migrations (
        file_path text,
        migration_name text,
        deploy text,
        revert text,
        verify text
      ) on commit drop;
    else
      truncate __pg_statecharts_migrations;
    end if;

    declare
      file_path text;
      doc xml;
      chart_name text;
      chart_version text;
      err_message text;
      err_detail text;
      err_hint text;
      err_code text;
    begin
      for file_path in
        select * from fsm.__find_scxml_files(source_path, recursive) order by 1
      loop
        begin
          doc := fsm.__read_scxml(file_path);
          chart_name := fsm.__scxml_name(doc, file_path);
          chart_version := fsm.__scxml_version(doc, file_path);

          -- Reject now what deploying the migration would reject later.
          perform fsm.__check_chart_deploys(doc, chart_version);

          insert into __pg_statecharts_migrations values (
            file_path,
            fsm.__migration_name(chart_name, chart_version),
            fsm.__migration_deploy(doc, plan_project, chart_name, chart_version),
            fsm.__migration_revert(plan_project, chart_name, chart_version),
            fsm.__migration_verify(plan_project, chart_name, chart_version)
          );
        exception when others then
          -- Most messages above already name the file. The ones from the
          -- parser and the triggers do not, so say which file was being
          -- generated, the way the importer does.
          get stacked diagnostics
            err_code = returned_sqlstate,
            err_message = message_text,
            err_detail = pg_exception_detail,
            err_hint = pg_exception_hint;

          raise exception using
            errcode = err_code,
            message = err_message,
            detail = trim(both E'\n' from
              coalesce(err_detail, '') || E'\n' || format('while generating a migration from %s', file_path)),
            hint = nullif(err_hint, '');
        end;
      end loop;
    end;

    -- Two files can render to the same migration name: the same chart name and
    -- version in both, or names that differ only in where a dot becomes a
    -- directory separator, such as 'a.b' and 'a/b'. Each chart is dry run in
    -- its own rolled back subtransaction, so the unique index on
    -- (name, version) never sees them together and cannot catch this. The
    -- write loop below would let the later file overwrite the earlier one's
    -- three files while adding a single plan entry, so the loser would vanish
    -- without a word.
    declare
      duplicates text;
    begin
      select string_agg(format('%s: %s', d.migration_name, d.files), E'\n' order by d.migration_name)
      into duplicates
      from (
        select migration_name, string_agg(file_path, ', ' order by file_path) as files
        from __pg_statecharts_migrations
        group by migration_name
        having count(*) > 1
      ) d;

      if duplicates is not null then
        raise exception 'more than one .scxml file generates the same migration'
          using detail = duplicates,
                hint = 'give the charts different names or versions; nothing has been written';
      end if;
    end;

    if not exists (select 1 from __pg_statecharts_migrations) then
      raise exception 'no .scxml files found at %', source_path
        using hint = case
          when recursive then 'check the path'
          else 'pass recursive => true to also look in subdirectories'
        end;
    end if;

    -- Check every output directory before writing anything, so that a missing
    -- verify/ directory cannot leave a half written set of migrations behind.
    -- "is not true" rather than "is null" so that a path that exists but is a
    -- regular file is caught here too, instead of on the write that follows.
    declare
      missing text[];
    begin
      select array_agg(distinct d.dir order by d.dir)
      into missing
      from __pg_statecharts_migrations m
      cross join lateral (
        values
          (sqitch_dir || '/deploy/' || m.migration_name),
          (sqitch_dir || '/revert/' || m.migration_name),
          (sqitch_dir || '/verify/' || m.migration_name)
      ) as paths(path)
      cross join lateral (
        select regexp_replace(paths.path, '/[^/]*$', '') as dir
      ) as d
      where (pg_stat_file(d.dir, true)).isdir is not true;

      if missing is not null then
        -- The hint lists exactly the directories that are missing, which is
        -- not always <sqitch_dir>/{deploy,revert,verify}/statechart: dots in a
        -- chart name become directory separators, so 'myschema.thingflow'
        -- needs .../statechart/myschema and 'a.b.c' needs .../statechart/a/b.
        -- mkdir -p creates the intermediate levels, so the leaf directories
        -- computed above are enough.
        --
        -- Paths are shell quoted when they contain anything outside a
        -- conservative safe set. Chart names are only length checked, so they
        -- can hold spaces or shell metacharacters, and this hint is meant to
        -- be pasted into a terminal.
        raise exception 'the sqitch project is missing output directories'
          using detail = array_to_string(missing, E'\n'),
                hint = 'create them first: mkdir -p ' || (
                  select string_agg(
                    case
                      when dir ~ '^[A-Za-z0-9_./@%+:-]+$' then dir
                      else $q$'$q$ || replace(dir, $q$'$q$, $q$'\''$q$) || $q$'$q$
                    end,
                    ' ' order by dir)
                  from unnest(missing) as dir
                );
      end if;
    end;

    timestamp_str := to_char(now() at time zone 'utc', 'YYYY-MM-DD"T"HH24:MI:SS"Z"');

    for migration in
      select * from __pg_statecharts_migrations order by migration_name
    loop
      perform fsm.__write_file(sqitch_dir || '/deploy/' || migration.migration_name || '.sql', migration.deploy);
      perform fsm.__write_file(sqitch_dir || '/revert/' || migration.migration_name || '.sql', migration.revert);
      perform fsm.__write_file(sqitch_dir || '/verify/' || migration.migration_name || '.sql', migration.verify);

      -- A sqitch change line starts with the change name followed by
      -- whitespace, so the first token is enough to tell whether the plan
      -- already knows about this migration. Any whitespace: a plan that
      -- separates the name from the timestamp with a tab would otherwise look
      -- like a plan that does not have the change at all, and the migration
      -- would be appended a second time.
      if exists (
        select 1
        from unnest(string_to_array(plan_contents, E'\n')) as line
        where (regexp_match(line, '^(\S+)'))[1] = migration.migration_name
      ) then
        raise info 'updated existing migration: %', migration.migration_name;
      else
        plan_additions := plan_additions
          || migration.migration_name || ' ' || timestamp_str
          || ' pg_statecharts <pg_statecharts@postgres> # '
          || migration.migration_name || E'\n';

        raise info 'created new migration: %', migration.migration_name;
      end if;
    end loop;

    if plan_additions <> '' then
      -- Guard against a plan file that does not end in a newline, which would
      -- otherwise glue the first new change onto the last existing one.
      if plan_contents <> '' and right(plan_contents, 1) <> E'\n' then
        plan_contents := plan_contents || E'\n';
      end if;

      perform fsm.__write_file(sqitch_plan_file_path, plan_contents || plan_additions);
    end if;
  end;
$gen$ language plpgsql volatile;

comment on function fsm.gen_statechart_sqitch_migrations(text, text, boolean) is $comment$
    Generates a sqitch deploy/revert/verify migration for every .scxml file at
    source_path and registers it in the sqitch plan.

    Both paths are resolved on the database host rather than on the client, and
    the deploy/statechart, revert/statechart and verify/statechart directories
    have to already exist.

    Requires the pg_read_server_files and pg_write_server_files roles (or
    superuser). Note that the files are written by the postgres process, so run
    the database as your own user if you want to own the generated files:

        docker compose run --user "$(id -u):$(id -g)" ...
$comment$;