{
  "Add Optional Column (Ax Custom)": {
    "scope": "sql",
    "prefix": "ax-add-optional-column",
    "body": [
      "ALTER TABLE ${4:app_public}.${1:table_name} ADD COLUMN IF NOT EXISTS ${2:column_name} ${3|TEXT,INT,BOOLEAN,DATE,TIMESTAMPTZ,UUID|};",
      "",
      "GRANT INSERT (${2:column_name}) ON ${4:app_public}.${1:table_name} TO \":${5:DATABASE_GQL_ROLE}\";",
      "GRANT UPDATE (${2:column_name}) ON ${4:app_public}.${1:table_name} TO \":${5:DATABASE_GQL_ROLE}\";"
    ],
    "description": [
      "Adds an optional (NULL) column if it does not exist.",
      "The column is exposed in GraphQL API for insert and update operations."
    ]
  },
  "Add Required Column (Ax Custom)": {
    "scope": "sql",
    "prefix": "ax-add-required-column",
    "body": [
      "ALTER TABLE ${4:app_public}.${1:table_name} ADD COLUMN IF NOT EXISTS ${2:column_name} ${3|TEXT,INT,BOOLEAN,DATE,TIMESTAMPTZ|} NULL;",
      "UPDATE ${4:app_public}.${1:table_name} SET ${2:column_name} = ${6:existing_column_or_some_value_in_quotes} WHERE ${2:column_name} IS NULL;",
      "ALTER TABLE ${4:app_public}.${1:table_name} ALTER COLUMN ${2:column_name} SET NOT NULL;",
      "GRANT INSERT (${2:column_name}) ON ${4:app_public}.${1:table_name} TO \":${5:DATABASE_GQL_ROLE}\";",
      "GRANT UPDATE (${2:column_name}) ON ${4:app_public}.${1:table_name} TO \":${5:DATABASE_GQL_ROLE}\";"
    ],
    "description": [
      "Adds a required (NOT NULL) column if it does not exist.\n",
      "The column is exposed in GraphQL API for insert and update operations.",
      "The column is added as nullable at first, then default values must be populated, and after that column is set to be not nullable."
    ]
  },
  "Delete Column (Ax Custom)": {
    "scope": "sql",
    "prefix": "ax-drop-column",
    "body": [
      "ALTER TABLE ${3:app_public}.${1:table_name} DROP COLUMN IF EXISTS ${2:column_name};"
    ],
    "description": "Drops a column from a specific table if it exists."
  },
  "Rename Column (Ax Custom)": {
    "scope": "sql",
    "prefix": "ax-rename-column",
    "body": [
      "DO \\$\\$ BEGIN",
      "  IF ax_define.column_exists('${2:column_name}', '${1:table_name}', '${4:app_public}') THEN",
      "    ALTER TABLE ${4:app_public}.${1:table_name} RENAME COLUMN ${2:column_name} TO ${3:new_column_name};",
      "  END IF;",
      "END \\$\\$;"
    ],
    "description": "Renames a column if it exists."
  },
  "Change Column Type (Ax Custom)": {
    "scope": "sql",
    "prefix": "ax-change-column-type",
    "body": [
      "DO \\$do\\$ BEGIN",
      "  IF EXISTS (SELECT column_name FROM information_schema.columns",
      "    WHERE table_schema = '${5:app_public}' AND table_name = '${1:table_name}' AND column_name = '${2:column_name}' AND data_type = '${4|text,integer,boolean,date,timestamp with time zone|}')",
      "  THEN",
      "    ALTER TABLE ${5:app_public}.${1:table_name} ALTER COLUMN ${2:column_name} TYPE ${3|TEXT,INT,BOOLEAN,DATE,TIMESTAMPTZ|} USING ${2:column_name}::${3|TEXT,INT,BOOLEAN,DATE,TIMESTAMPTZ|};",
      "  END IF;",
      "END \\$do\\$;"
    ],
    "description": "Changes columns type. The expression after `USING` is used to cast/transform existing values."
  },
  "Set Column NOT NULL (Ax Custom)": {
    "scope": "sql",
    "prefix": "ax-set-column-not-null",
    "body": [
      "UPDATE ${4:app_public}.${1:table_name} SET ${2:column_name} = '${3:default value}' WHERE ${2:column_name} IS NULL;",
      "ALTER TABLE ${4:app_public}.${1:table_name} ALTER COLUMN ${2:column_name} SET DEFAULT '${3:default value}';",
      "ALTER TABLE ${4:app_public}.${1:table_name} ALTER COLUMN ${2:column_name} SET NOT NULL;"
    ],
    "description": [
      "Sets a default value for a non-unique column and makes it required.\n",
      "Custom code might be required to populate values before setting a column to be required."
    ]
  },
  "Drop Constraint (Ax Custom)": {
    "scope": "sql",
    "prefix": "ax-drop-constraint",
    "body": [
      "ALTER TABLE ${4:app_public}.${1:table_name} DROP CONSTRAINT IF EXISTS ${2:column_name}_${3|max_length,min_length,max_value,min_value,not_empty,is_trimmed,is_base64,starts_with,has_allowed_value,not_default_uuid,is_url,not_empty_array|};"
    ],
    "description": "Drops a constraint if it exists."
  },
  "Add Custom Domain (Ax Custom)": {
    "scope": "sql",
    "prefix": "ax-add-custom-domain",
    "body": [
      "ALTER TABLE ${5:app_public}.${1:table_name} ALTER COLUMN ${2:column_name} TYPE ${4|jsonb,text|};",
      "DROP DOMAIN IF EXISTS ${5:app_public}.${2:column_name}_${3:object};",
      "CREATE DOMAIN ${5:app_public}.${2:column_name}_${3:object} AS ${4|jsonb,text|};",
      "ALTER TABLE ${5:app_public}.${1:table_name} ALTER COLUMN ${2:column_name} TYPE ${5:app_public}.${2:column_name}_${3:object};",
      "",
      "-- $0TODO: Adjust auto-generated type in `generated/zapatos/custom/${2/([a-z]+)*(?:_)?([a-z]+)*/${1:/pascalcase}${2:/pascalcase}/g}${3/([a-z]+)*(?:_)?([a-z]+)*/${1:/pascalcase}${2:/pascalcase}/g}.d.ts`, e.g `export type ${2/([a-z]+)*(?:_)?([a-z]+)*/${1:/pascalcase}${2:/pascalcase}/g}${3/([a-z]+)*(?:_)?([a-z]+)*/${1:/pascalcase}${2:/pascalcase}/g} = { title: string };`"
    ],
    "description": [
      "Adds a custom domain when there is a need to represent a default type by a custom type.",
      "Helpful for cases when there is a need to add stronger typing to jsonb or text columns."
    ]
  },
  "Add Custom Trigger (Ax Custom)": {
    "scope": "sql",
    "prefix": "ax-add-custom-trigger",
    "body": [
      "CREATE OR REPLACE FUNCTION ${6:app_hidden}.tg__${1:table_name}_${2:task_name}() RETURNS trigger AS $$",
      "BEGIN",
      "  NEW.${8:column_name} = (${0:/*Write your update logic here, e.g. CASE-END expression: https://www.postgresql.org/docs/current/functions-conditional.html */});",
      "  RETURN NEW;",
      "END;",
      "$$ LANGUAGE plpgsql VOLATILE;",
      "",
      "DROP trigger IF EXISTS _${3:500}_${2:task_name} on ${7:app_public}.${1:table_name};",
      "CREATE trigger _${3:500}_${2:task_name}",
      "${4|BEFORE,AFTER,INSTEAD OF|} ${5|UPDATE,INSERT,INSERT OR UPDATE,DELETE|} ON ${7:app_public}.${1:table_name}",
      "for each ROW",
      "EXECUTE PROCEDURE ${6:app_hidden}.tg__${1:table_name}_${2:task_name}();"
    ],
    "description": [
      "Adds a custom trigger function and a trigger that uses said function.",
      "The number in the name of the trigger indicates the order it will take among other triggers.",
      "A lower number means faster execution, e.g. trigger which starts with _100 will start before _300."
    ]
  },
  "Add Table (Ax Custom)": {
    "scope": "sql",
    "prefix": "ax-add-table",
    "body": [
      "DROP TABLE IF EXISTS ${3:app_public}.${1:table_name} CASCADE;",
      "CREATE TABLE ${3:app_public}.${1:table_name} (",
      "  id INT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,",
      "  title TEXT NOT NULL,",
      "  unique_column TEXT NOT NULL,",
      "  boolean_column BOOLEAN NOT NULL DEFAULT FALSE,",
      "  optional_text_column TEXT,",
      "  optional_int_column INT,",
      "  optional_datetime_column TIMESTAMPTZ,",
      "  optional_date_column DATE,",
      "",
      "  CONSTRAINT title_max_length CHECK(ax_utils.constraint_max_length(title, 100, 'The title can only be %2\\$s characters long.')),",
      "  CONSTRAINT title_not_empty CHECK(ax_utils.constraint_not_empty(title, 'The title cannot be empty.'))",
      ");",
      "SELECT ax_define.define_audit_date_fields_on_table('${1:table_name}', '${3:app_public}');",
      "SELECT ax_define.define_audit_user_fields_on_table('${1:table_name}', '${3:app_public}', ':DEFAULT_USERNAME');",
      "",
      "SELECT ax_define.define_authentication('${1/(.*)/${1:/upcase}/}_VIEW,${1/(.*)/${1:/upcase}/}_EDIT,ADMIN', '${1/(.*)/${1:/upcase}/}_EDIT,ADMIN', '${1:table_name}', '${3:app_public}');",
      "",
      "SELECT ax_define.define_unique_constraint('unique_column', '${1:table_name}', '${3:app_public}');",
      "",
      "GRANT SELECT, DELETE ON ${3:app_public}.${1:table_name} TO \":${5:DATABASE_GQL_ROLE}\";",
      "GRANT INSERT (",
      "  title,",
      "  unique_column",
      ") ON ${3:app_public}.${1:table_name} TO \":${5:DATABASE_GQL_ROLE}\";",
      "GRANT UPDATE (",
      "  title,",
      "  boolean_column,",
      "  optional_text_column,",
      "  optional_int_column,",
      "  optional_datetime_column,",
      "  optional_date_column",
      ") ON ${3:app_public}.${1:table_name} TO \":${5:DATABASE_GQL_ROLE}\";",
      "",
      "SELECT ax_define.define_subscription_triggers('id', '${1:table_name}', '${3:app_public}', '${1:table_name}', '${6:SingularTableNameInPascalCase}');",
      "SELECT ax_define.define_indexes_with_id('title', '${1:table_name}', '${3:app_public}');",
      "SELECT ax_define.define_indexes_with_id('unique_column', '${1:table_name}', '${3:app_public}');",
      "SELECT ax_define.define_indexes_with_id('optional_text_column', '${1:table_name}', '${3:app_public}');",
      "SELECT ax_define.define_indexes_with_id('optional_datetime_column', '${1:table_name}', '${3:app_public}');",
      "SELECT ax_define.define_like_index('title', '${1:table_name}', '${3:app_public}');",
      "SELECT ax_define.define_index('boolean_column', '${1:table_name}', '${3:app_public}');",
      "",
      "-- $0TODO: After a new table is added and service is running, new GraphQL queries/mutations will become available based on GRANT statements.",
      "--       Newly added operations must be assigned to permissions in the PermissionDefinition to become accessible.",
      "-- TODO: Adjust SubscriptionsPlugin by adding a subscription GraphQL endpoint, e.g. `SubscriptionsPluginFactory('${1:table_name}', '${6:SingularTableNameInPascalCase}', 'Int')`"
    ],
    "description": "Creates a table with enabled authentication. Includes examples of audit properties, indexes, subscriptions trigger, grants, and constraints."
  },
  "Add Relation Table (Ax Custom)": {
    "scope": "sql",
    "prefix": "ax-add-relation-table",
    "body": [
      "DROP TABLE IF EXISTS ${8:app_public}.${1:table_name} CASCADE;",
      "CREATE TABLE ${8:app_public}.${1:table_name} (",
      "  ${2:fk_column} INT NOT NULL REFERENCES ${8:app_public}.${3:parent_table}(${4:id}) ON DELETE CASCADE,",
      "  ${5:name} text NOT NULL,",
      "",
      "  PRIMARY KEY(${2:fk_column}, ${5:name}),",
      "  CONSTRAINT ${5:name}_not_empty CHECK(ax_utils.constraint_not_empty(${5:name}, 'The ${5/[_]/ /g} cannot be empty.'))",
      ");",
      "",
      "SELECT ax_define.define_authentication('${6:ENTITIES}_VIEW,${6:ENTITIES}_EDIT,ADMIN', '${6:ENTITIES}_EDIT,ADMIN', '${1:table_name}', '${8:app_public}');",
      "",
      "GRANT SELECT, INSERT, DELETE ON ${8:app_public}.${1:table_name} TO \":DATABASE_GQL_ROLE\";",
      "GRANT UPDATE (${5:name}) ON ${8:app_public}.${1:table_name} TO \":DATABASE_GQL_ROLE\";",
      "SELECT ax_define.define_subscription_triggers('${2:fk_column}', '${1:table_name}', '${8:app_public}', '${3:parent_table}', '${7:RelationTableEntitySingular}');",
      "SELECT ax_define.define_index('${2:fk_column}', '${1:table_name}', '${8:app_public}');",
      "SELECT ax_define.define_like_index('${5:name}', '${1:table_name}', '${8:app_public}');",
      "SELECT ax_define.live_suggestions_endpoint('${5:name}', '${1:table_name}', '${8:app_public}');",
      "",
      "-- $0TODO: After a new table is added and service is running, new GraphQL queries/mutations will become available based on GRANT statements.",
      "--       Newly added operations must be assigned to permissions in the PermissionDefinition to become accessible."
    ],
    "description": "Creates a relation table with enabled authentication and composite primary key."
  },
  "Drop Table (Ax Custom)": {
    "scope": "sql",
    "prefix": "ax-drop-table",
    "body": ["DROP TABLE IF EXISTS ${8:app_public}.${1:table_name} CASCADE;"],
    "description": "Drops a table."
  },
  "Add Enum and Column (Ax Custom)": {
    "scope": "sql",
    "prefix": "ax-add-enum-and-column",
    "body": [
      "SELECT ax_define.create_enum_table(",
      "  '${1:enum_table_name}',",
      "  '${2:app_public}',",
      "  ':${9:DATABASE_LOGIN}',",
      "  '{\"${6:DEFAULT_VALUE}\",\"${7:OTHER_VALUE}\",\"${8:ANOTHER_VALUE}\"}',",
      "  '{\"${6/(?:([A-Z])([A-Z]+))*(_)?([A-Z]+)*/${1:/capitalize}${2:/downcase}${3:+ }${4:/downcase}/g}\",\"${7/(?:([A-Z])([A-Z]+))*(_)?([A-Z]+)*/${1:/capitalize}${2:/downcase}${3:+ }${4:/downcase}/g}\",\"${8/(?:([A-Z])([A-Z]+))*(_)?([A-Z]+)*/${1:/capitalize}${2:/downcase}${3:+ }${4:/downcase}/g}\"}');",
      "",
      "SELECT ax_define.set_enum_as_column_type('${3:column_name}', '${4:entity_table_name}', '${5:app_public}', '${1:enum_table_name}', '${2:app_public}', '${6:DEFAULT_VALUE}');",
      "SELECT ax_define.set_enum_domain('${3:column_name}', '${4:entity_table_name}', '${5:app_public}', '${1:enum_table_name}_enum', '${2:app_public}');",
      "GRANT INSERT (${3:column_name}) ON ${5:app_public}.${4:entity_table_name} TO \":${10:DATABASE_GQL_ROLE}\";",
      "GRANT UPDATE (${3:column_name}) ON ${5:app_public}.${4:entity_table_name} TO \":${10:DATABASE_GQL_ROLE}\";",
      "",
      "-- $0TODO: Adjust auto-generated ENUM type in `generated/zapatos/custom/${1/([a-z]+)*(?:_)?([a-z]+)*/${1:/pascalcase}${2:/pascalcase}/g}Enum.d.ts`, e.g `export type ${1/([a-z]+)*(?:_)?([a-z]+)*/${1:/pascalcase}${2:/pascalcase}/g}Enum = '${6:DEFAULT_VALUE}'|'${7:OTHER_VALUE}'|'${8:ANOTHER_VALUE}';`"
    ],
    "description": "Creates an enum table, adds a column to the table with the default value set, and adds a custom domain for the enum."
  },
  "Add Enum Value (Ax Custom)": {
    "scope": "sql",
    "prefix": "ax-add-enum-value",
    "body": [
      "INSERT INTO ${2:app_public}.${1:enum_table_name} (value, description)",
      "VALUES ('${3:NEW_VALUE}', '${3/(?:([A-Z])([A-Z]+))*(_)?([A-Z]+)*/${1:/capitalize}${2:/downcase}${3:+ }${4:/downcase}/g}')",
      "ON CONFLICT (value) DO NOTHING;"
    ],
    "description": "Adds a new value to an enum table."
  },
  "Drop Enum Value (Ax Custom)": {
    "scope": "sql",
    "prefix": "ax-drop-enum-value",
    "body": [
      "UPDATE ${5:app_public}.${4:entity_table_name} SET ${6:colum_name} = '${7:DEFAULT_VALUE}' WHERE ${6:colum_name} = '${3:ENUM_VALUE_TO_DROP}';",
      "DELETE FROM ${2:app_public}.${1:enum_table_name} WHERE value = '${3:ENUM_VALUE_TO_DROP}';"
    ],
    "description": [
      "Drops a value from the enum table.\n",
      "Make sure the dropped value is not used in other tables.\n",
      "This snippet has a simplified migration sample that might require adjustments."
    ]
  },
  "Create Transaction Inbox Outbox Tables and Functions (Ax Custom)": {
    "scope": "sql",
    "prefix": "ax-add-trx-inbox-outbox-tables-and-functions",
    "body": [
      "-- Create Transaction Inbox Table",
      "SELECT ax_define.create_trx_table(",
      "'${1:app_hidden}',",
      "'${2:inbox}',",
      "'{${3::DATABASE_ENV_OWNER},${4::DATABASE_GQL_ROLE}}',",
      "'${5|sequential,parallel|}');",
      "",
      "-- Create Transaction Outbox Table",
      "SELECT ax_define.create_trx_table(",
      "'${1:app_hidden}',",
      "'${6:outbox}',",
      "'{${3::DATABASE_ENV_OWNER},${4::DATABASE_GQL_ROLE}}',",
      "'${7|sequential,parallel|}');",
      "",
      "-- Create Transaction Inbox Next Message function",
      "SELECT ax_define.create_trx_next_messages_function(",
      "'${8:app_hidden}',",
      "'${1:app_hidden}',",
      "'${2:inbox}');",
      "",
      "-- Create Transaction Outbox Next Message function",
      "SELECT ax_define.create_trx_next_messages_function(",
      "'${8:app_hidden}',",
      "'${1:app_hidden}',",
      "'${6:outbox}');"
    ],
    "description": [
      "Drops a value from the enum table.\n",
      "Make sure the dropped value is not used in other tables.\n",
      "This snippet has a simplified migration sample that might require adjustments."
    ]
  }
}