/*-snippet
{
  "body": [
    "SELECT ax_define.define_multitenancy_on_table('${1:id}', '${2:table_name}', '${3:app_public}');"
  ],
  "description": [
    "Define multitenancy columns and logic.",
    "Must be called on all tables in a multitenant system.\n",
    "First parameter: a comma-separated string of column names that are part of the primary key (e.g. 'id' or 'entity_id,name', or '' to make tenant/env already unique).",
    "Fourth parameter: a unique name for the multicolumn unique constraint. If the value is NULL then a name will be generated from the table name."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_define.define_multitenancy_on_table(
  idColumnNames text,
  tableName text,
  schemaName text,
  constraintName text default NULL
  ) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
  SELECT COALESCE(constraintName, tableName || '_unique_id_tenant_environment') INTO constraintName;
  PERFORM ax_utils.validate_identifier_length(constraintName, 'If the auto-generated name is too long then a "constraintName" argument must be provided.');
  EXECUTE '
    DO $do$ BEGIN 
      BEGIN
          ALTER TABLE ' || schemaName || '.' || tableName || ' ADD COLUMN tenant_id UUID NOT NULL DEFAULT ''00000000-0000-0000-0000-000000000000'';
          ALTER TABLE ' || schemaName || '.' || tableName || ' ALTER COLUMN tenant_id SET DEFAULT ''ffffffff-ffff-ffff-ffff-ffffffffffff'';
      EXCEPTION
          WHEN duplicate_column THEN RAISE NOTICE ''The column tenant_id already exists in the ' || schemaName || '.' || tableName || ' table.'';
      END;
    END $do$;
    DO $do$ BEGIN 
      BEGIN
          ALTER TABLE ' || schemaName || '.' || tableName || ' ADD COLUMN environment_id UUID NOT NULL DEFAULT ''00000000-0000-0000-0000-000000000000'';
          ALTER TABLE ' || schemaName || '.' || tableName || ' ALTER COLUMN environment_id SET DEFAULT ''ffffffff-ffff-ffff-ffff-ffffffffffff'';
      EXCEPTION
          WHEN duplicate_column THEN RAISE NOTICE ''The column environment_id already exists in the ' || schemaName || '.' || tableName || ' table.'';
      END;
    END $do$;
    ALTER TABLE ' || schemaName || '.' || tableName || ' DROP CONSTRAINT IF EXISTS tenant_id_not_default;
    ALTER TABLE ' || schemaName || '.' || tableName || ' ADD CONSTRAINT tenant_id_not_default CHECK (ax_utils.constraint_not_default_uuid(tenant_id));
    ALTER TABLE ' || schemaName || '.' || tableName || ' DROP CONSTRAINT IF EXISTS environment_id_not_default;
    ALTER TABLE ' || schemaName || '.' || tableName || ' ADD CONSTRAINT environment_id_not_default CHECK (ax_utils.constraint_not_default_uuid(environment_id));
    SELECT ax_define.define_tenant_environment_trigger(''' || tableName || ''', ''' || schemaName || ''');
    ALTER TABLE ' || schemaName || '.' || tableName || ' DROP CONSTRAINT IF EXISTS ' || constraintName || ';
    ';
    IF idColumnNames = '' THEN
      EXECUTE 'ALTER TABLE ' || schemaName || '.' || tableName || ' ADD CONSTRAINT ' || constraintName || ' UNIQUE(tenant_id, environment_id);';
    ELSE
      EXECUTE 'ALTER TABLE ' || schemaName || '.' || tableName || ' ADD CONSTRAINT ' || constraintName || ' UNIQUE(' || idColumnNames || ', tenant_id, environment_id);';
    END IF;
END;
$$;

/*-snippet
{
  "body": [
    "SELECT ax_define.define_multitenancy_relation('${1:fk_column}', '${2:table_name}', '${3:app_public}', '${4:id}', '${5:parent_table}', '${6:app_public}');"
  ],
  "description": [
    "Define multitenancy relations.",
    "This must be called only after 'define_multitenancy_on_table' was called on both tables.\n",
    "First parameter: comma-separated list of column names from the \"table_name\" table that should be used as FK.",
    "Fourth parameter: comma-separated list of column names from the \"parent_table\" that should be used for the reference.",
    "Seventh (optional) parameter: defines cascade options. Default is 'ON DELETE CASCADE'.",
    "Eighth (optional) parameter: a unique name for this constraint. If the value is NULL then a name will be generated from the table names."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_define.define_multitenancy_relation(
  idColumnNames text,
  tableName text,
  schemaName text,
  foreignIdColumnNames text,
  foreignTableName text,
  foreignSchemaName text,
  cascadeOptions text default 'ON DELETE CASCADE',
  constraintName text default NULL
  ) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
  SELECT COALESCE(constraintName, 'multitenancy_relation_' || tableName || '_to_' || foreignTableName) INTO constraintName;
  PERFORM ax_utils.validate_identifier_length(constraintName, 'If the auto-generated name is too long then a "constraintName" argument must be provided.');
  EXECUTE 'ALTER TABLE ' || schemaName || '.' || tableName || ' DROP CONSTRAINT IF EXISTS ' || constraintName || ';';
  EXECUTE '
    ALTER TABLE ' || schemaName || '.' || tableName || ' ADD CONSTRAINT ' || constraintName || '
      FOREIGN KEY (' || idColumnNames || ', tenant_id, environment_id) REFERENCES ' || foreignSchemaName || '.' || foreignTableName || '(' || foreignIdColumnNames || ', tenant_id, environment_id) ' || cascadeOptions || ';
  ';
END;
$$;

/*-snippet
{
  "body": [
    "SELECT ax_define.define_multitenancy_unique_field_constraint('${1:column_name}', '${2:table_name}', '${3:app_public}');"
  ],
  "description": [
    "Defines a UNIQUE constraint on a single column in an idempotent way.\n",
    "Fourth (optional) parameter: a unique name for this constraint. If the value is NULL then a name will be generated from the table & field name."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_define.define_multitenancy_unique_field_constraint(fieldName text, tableName text, schemaName text, constraintName text default NULL) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
  SELECT COALESCE(constraintName, tableName || '_' || fieldName || '_is_unique') INTO constraintName;
  PERFORM ax_utils.validate_identifier_length(constraintName, 'If the auto-generated name is too long then a "constraintName" argument must be provided.');
  EXECUTE 'ALTER TABLE ' || schemaName || '.' || tableName || ' DROP CONSTRAINT IF EXISTS ' || constraintName || ';';
  EXECUTE 'ALTER TABLE ' || schemaName || '.' || tableName || ' ADD CONSTRAINT ' || constraintName || ' UNIQUE (' || fieldName || ', tenant_id, environment_id);';
END;
$$;

/*-snippet
{
  "body": [
    "SELECT ax_define.define_multitenancy_multi_unique_constraint('{\"${1:column_name_one}\", \"${2:column_name_two}\"}', '${3:table_name}', '${4:app_public}');"
  ],
  "description": [
    "Defines a UNIQUE constraint on multiple columns in an idempotent way.\n",
    "Fourth (optional) parameter: a unique name for this constraint. If the value is NULL then a name will be generated from the table & field names."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_define.define_multitenancy_multi_unique_constraint(fieldNames text[], tableName text, schemaName text, constraintName text default NULL) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
  fieldNamesConcat text = array_to_string(array_agg(fieldNames), '_');
  fieldList text = array_to_string(array_agg(fieldNames), ', ');
BEGIN
  SELECT COALESCE(constraintName, tableName || '_' || fieldNamesConcat || '_is_unique') INTO constraintName;
  PERFORM ax_utils.validate_identifier_length(constraintName, 'If the auto-generated name is too long then a "constraintName" argument must be provided.');
  EXECUTE 'ALTER TABLE ' || schemaName || '.' || tableName || ' DROP CONSTRAINT IF EXISTS ' || constraintName || ';';
  EXECUTE 'ALTER TABLE ' || schemaName || '.' || tableName || ' ADD CONSTRAINT ' || constraintName || ' UNIQUE (' || fieldList || ', tenant_id, environment_id);';
END;
$$;

/*-snippet
{
  "body": [
    "SELECT ax_define.define_multitenancy_unique_constraint('${2:table_name}', '${3:app_public}');"
  ],
  "description": [
    "Defines a UNIQUE constraint in an idempotent way for a single row per tenant/env combination.\n",
    "Third (optional) parameter: a unique name for this constraint. If the value is NULL then a name will be generated from the table name."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_define.define_multitenancy_unique_constraint(tableName text, schemaName text, constraintName text default NULL) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
  SELECT COALESCE(constraintName, tableName || '_multitenancy_entry_is_unique') INTO constraintName;
  PERFORM ax_utils.validate_identifier_length(constraintName, 'If the auto-generated name is too long then a "constraintName" argument must be provided.');
  EXECUTE 'ALTER TABLE ' || schemaName || '.' || tableName || ' DROP CONSTRAINT IF EXISTS ' || constraintName || ';';
  EXECUTE 'ALTER TABLE ' || schemaName || '.' || tableName || ' ADD CONSTRAINT ' || constraintName || ' UNIQUE (tenant_id, environment_id);';
END;
$$;

/*-snippet
{
  "body": [
    "SELECT ax_define.define_multitenancy_primary_key('${1:column_name}','${2:table_name}', '${3:app_public}');"
  ],
  "description": [
    "Defines a PRIMARY KEY which includes tenant/env.\n",
    "First parameter:  either a single column name or a comma-separated string with multiple column names.",
    "Fourth (optional) parameter: a unique name for this constraint. If the value is NULL then a name will be generated from the table and column names."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_define.define_multitenancy_primary_key(fieldNames text, tableName text, schemaName text, constraintName text default NULL) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
  SELECT COALESCE(constraintName, tableName || '_pkey') INTO constraintName;
  PERFORM ax_utils.validate_identifier_length(constraintName, 'If the auto-generated name is too long then a "constraintName" argument must be provided.');
  EXECUTE 'ALTER TABLE ' || schemaName || '.' || tableName || ' DROP CONSTRAINT IF EXISTS ' || constraintName || ';';
  EXECUTE 'ALTER TABLE ' || schemaName || '.' || tableName || ' ADD CONSTRAINT ' || constraintName || ' PRIMARY KEY (' || fieldNames || ',tenant_id, environment_id);';
END;
$$;

/*-snippet
{
  "body": [
    "SELECT ax_define.define_multitenancy_index('${1:column_name}','${2:table_name}', '${3:app_public}');"
  ],
  "description": [
    "Defines a regular index in an idempotent way for a single field on a multitenancy table.\n",
    "Fourth (optional) parameter: a unique name for this index. If the value is NULL then a name will be generated from the table & field names."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_define.define_multitenancy_index(fieldName text, tableName text, schemaName text, indexName text default NULL) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
  SELECT COALESCE(indexName, 'idx_' || tableName || '_' || fieldName || '_mt') INTO indexName;
  PERFORM ax_utils.validate_identifier_length(indexName, 'If the auto-generated name is too long then an "indexName" argument must be provided.');
  EXECUTE 'DROP INDEX IF EXISTS ' || indexName || ' cascade;';
  EXECUTE 'CREATE INDEX ' || indexName || ' ON ' || schemaName || '.' || tableName || ' (' || fieldName || ', tenant_id, environment_id);';
END;
$$;

/*-snippet
{
  "body": [
    "SELECT ax_define.define_multitenant_authentication('${1/(.*)/${1:/upcase}/}_VIEW,${1/(.*)/${1:/upcase}/}_EDIT,ADMIN', '${1/(.*)/${1:/upcase}/}_EDIT,ADMIN', '${1:table_name}', '${2:app_public}');"
  ],
  "description": [
    "Defines authentication for a table via row level security based on permissions and multitenant checks.\n",
    "First parameter: comma-separated list of permission names to grant read permissions.",
    "Second parameter: comma-separated list of permission names to grant write permissions.",
    " - If the second parameter is an empty string, only read permissions are granted.",
    "Third parameter: the name of the table to protect.",
    "Fourth parameter: the database schema name of the table to protect."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_define.define_multitenant_authentication(readPermissions text, modifyPermissions text, tableName text, schemaName text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
  perform ax_define.define_authentication(readPermissions, modifyPermissions, tableName, schemaName, '(tenant_id = (SELECT ax_utils.current_tenant_id()) AND environment_id = (SELECT ax_utils.current_environment_id()))');
END;
$$;

/*-snippet
{
  "body": [
    "SELECT ax_define.define_readonly_multitenant_authentication('${1/(.*)/${1:/upcase}/}_VIEW,${1/(.*)/${1:/upcase}/}_EDIT,ADMIN', '${1:table_name}', '${2:app_public}');"
  ],
  "description": [
    "Defines read-only authentication for a table via row level security based on permissions and multitenant checks."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_define.define_readonly_multitenant_authentication(readPermissions text, tableName text, schemaName text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
  perform ax_define.define_multitenant_authentication(readPermissions, '', tableName, schemaName);
END;
$$;

/*-snippet
{
  "body": [
    "SELECT ax_define.drop_multitenancy_timestamp_propagation('${1:fk_column}', '${2:table_name}', '${3:app_public}', '${4:id}', '${5:parent_table}', '${6:app_public}');"
  ],
  "description": [
    "Removes a function and trigger created by 'ax_define.define_multitenancy_timestamp_propagation' in an idempotent way.\n",
    "The call signature mirrors 'ax_define.define_multitenancy_timestamp_propagation' and should be applied with the same arguments.\n"
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_define.drop_multitenancy_timestamp_propagation(
  idColumnName text,
  tableName text,
  schemaName text,
  foreignIdColumnName text,
  foreignTableName text,
  foreignSchemaName text,
  functionName text default NULL
  ) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
  SELECT COALESCE(functionName, 'tg_' || tableName || '__' || foreignTableName || '_mt_ts_propagation') INTO functionName;
  PERFORM ax_utils.validate_identifier_length(functionName, 'If the auto-generated name is too long then a "functionName" argument must be provided.');
  EXECUTE  'DROP TRIGGER IF EXISTS _200_propogate_timestamps on ' || schemaName || '.' || tableName;
  EXECUTE  'DROP FUNCTION IF EXISTS ' || schemaName || '.' || functionName || '()';
END;
$$;

/*-snippet
{
  "body": [
    "SELECT ax_define.define_multitenancy_timestamp_propagation('${1:fk_column}', '${2:table_name}', '${3:app_public}', '${4:id}', '${5:parent_table}', '${6:app_public}');"
  ],
  "description": [
    "Defines a function and trigger to propagate 'updated_date' changes to related entities.\n",
    "Propagation can trigger 'UPDATED' triggers on the target entity including chained timestamp propagation.\n",
    "The call signature mirrors ax_define.define_multitenancy_relation and can generally be applied with the same arguments.\n",
    "First parameter: a column name from the \"table_name\" table that the trigger should be associated with.",
    "Fourth parameter: a column name from the \"parent_table\" that 'updated_date' should be propagated to.",
    "Seventh (optional) parameter: a unique name for the generated function. If the value is NULL then a name will be generated from the table names.\n",
    "NB!. This function uses SECURITY DEFINER permission. It will always be executed as DB_OWNER."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_define.define_multitenancy_timestamp_propagation(
  idColumnName text,
  tableName text,
  schemaName text,
  foreignIdColumnName text,
  foreignTableName text,
  foreignSchemaName text,
  functionName text default NULL
  ) RETURNS void
    LANGUAGE plpgsql
    AS $a$
BEGIN
  SELECT COALESCE(functionName, 'tg_' || tableName || '__' || foreignTableName || '_mt_ts_propagation') INTO functionName;
  PERFORM ax_utils.validate_identifier_length(functionName, 'If the auto-generated name is too long then a "functionName" argument must be provided.');
  -- Set updated_date=now() on the foreign table. This will propogate UPDATE triggers.
  --
  -- A new function is created for each table to do this.
  --     It *may* be possible to use a stock function with trigger arguments but its not easy as NEW and OLD cannot be accessed with dynamic column names. A possible 
  --     solution to that is described here: https://itectec.com/database/postgresql-assignment-of-a-column-with-dynamic-column-name/. But even there the advise is 
  --     to: "Just write a new trigger function for each table. Less hassle, better performance. Byte the bullet on code duplication:"
  --
  -- !! WARNING !!: This function uses "SECURITY DEFINER". This is required to ensure that update to the target table is allowed. This means that the function is 
  --                executed with role "DB_OWNER". Extra care must be taken to ensure multitenancy integrity is preserved. Any propogated trigger functions will also 
  --                execute with role "DB_OWNER".
  EXECUTE  '
            CREATE OR REPLACE FUNCTION ' || schemaName || '.' || functionName || '() RETURNS TRIGGER
            LANGUAGE plpgsql 
            SECURITY DEFINER
            SET search_path = pg_temp
            AS $b$
            BEGIN
                -- if updated_date exists on source and its not changed, then exit here
                -- this prevents multiple propogation from different children in one transaction
                if (to_jsonb(NEW) ? ''updated_date'') THEN
                    -- must be a seperate condition to prevent exception if column does not exist
                    IF (OLD.updated_date = NEW.updated_date) THEN
                        RETURN NULL;
                    END IF;
                END IF;

                -- UPDATE (where relationship is unchanged, or changed to another entity in which case a change is triggered on both the old and new relation)
                IF (OLD.' || idColumnName || ' IS NOT NULL AND NEW.' || idColumnName || ' IS NOT NULL) THEN
                    UPDATE ' || foreignSchemaName || '.' || foreignTableName || ' SET updated_date=now()
                        WHERE (' || foreignIdColumnName || ' = OLD.' || idColumnName || ' AND tenant_id = OLD.tenant_id AND environment_id = OLD.environment_id)
                        OR (' || foreignIdColumnName || ' = NEW.' || idColumnName || ' AND tenant_id = NEW.tenant_id AND environment_id = NEW.environment_id);

                -- INSERT (or UPDATE which sets nullable relationship)
                ELSIF (NEW.' || idColumnName || ' IS NOT NULL) THEN
                    UPDATE ' || foreignSchemaName || '.' || foreignTableName || ' SET updated_date=now()
                        WHERE ' || foreignIdColumnName || ' = NEW.' || idColumnName || ' AND tenant_id = NEW.tenant_id AND environment_id = NEW.environment_id;

                -- DELETE (or UPDATE which removes nullable relationship)
                ELSIF (OLD.' || idColumnName || ' IS NOT NULL) THEN
                    UPDATE ' || foreignSchemaName || '.' || foreignTableName || ' SET updated_date=now()
                        WHERE ' || foreignIdColumnName || ' = OLD.' || idColumnName || ' AND tenant_id = OLD.tenant_id AND environment_id = OLD.environment_id;
                    
                END IF;
                RETURN NULL;
            END $b$;
            REVOKE EXECUTE ON FUNCTION ' || schemaName || '.' || functionName || '() FROM public;
            ';
  
  -- Function runs *AFTER* INSERT, UPDATE, DELETE. Propogated queries can still raise error and rollback the transaction
  EXECUTE  'DROP TRIGGER IF EXISTS _200_propogate_timestamps on ' || schemaName || '.' || tableName;
  EXECUTE  'CREATE trigger _200_propogate_timestamps 
            AFTER INSERT OR UPDATE OR DELETE ON ' || schemaName || '.' || tableName || '
            FOR EACH ROW EXECUTE PROCEDURE ' || schemaName || '.' || functionName || '();';
END;
$a$;
