{
  "Create schema if missing": {
    "scope": "sql",
    "prefix": "idem_schema",
    "body": [
      "IF SCHEMA_ID(N'${1:app}') IS NULL",
      "BEGIN",
      "    EXEC(N'CREATE SCHEMA [${1}] AUTHORIZATION [${2:dbo}];');",
      "END;",
      "GO",
      "$0"
    ],
    "description": "Existence guard; leaves an existing schema and its owner unchanged."
  },
  "Create table if missing": {
    "scope": "sql",
    "prefix": "idem_table",
    "body": [
      "IF SCHEMA_ID(N'${1:dbo}') IS NULL",
      "    THROW 51000, N'Required schema is missing.', 1;",
      "",
      "IF OBJECT_ID(N'[${1}].[${2:Example}]') IS NOT NULL",
      "   AND OBJECT_ID(N'[${1}].[${2}]', N'U') IS NULL",
      "    THROW 51000, N'The requested table name belongs to another object type.', 1;",
      "",
      "-- An existing table is retained; validate its definition separately.",
      "IF OBJECT_ID(N'[${1}].[${2}]', N'U') IS NULL",
      "BEGIN",
      "    CREATE TABLE [${1}].[${2}]",
      "    (",
      "        ${3:[Id] int NOT NULL PRIMARY KEY,",
      "        [Name] nvarchar(100) NULL}",
      "    );",
      "END;",
      "GO",
      "$0"
    ],
    "description": "Existence guard; rejects a name occupied by another object type. Does not reconcile existing columns."
  },
  "Add column if missing": {
    "scope": "sql",
    "prefix": "idem_column",
    "body": [
      "IF OBJECT_ID(N'[${1:dbo}].[${2:Example}]', N'U') IS NULL",
      "    THROW 51000, N'Required table is missing or is not visible to this principal.', 1;",
      "",
      "-- This does not validate the type, length, or nullability of an existing column.",
      "IF NOT EXISTS",
      "(",
      "    SELECT 1 FROM sys.columns",
      "    WHERE object_id = OBJECT_ID(N'[${1}].[${2}]', N'U')",
      "      AND name = N'${3:Description}'",
      ")",
      "BEGIN",
      "    ALTER TABLE [${1}].[${2}]",
      "        ADD [${3}] ${4:nvarchar(200) NULL};",
      "END;",
      "GO",
      "$0"
    ],
    "description": "Existence guard; retains any existing definition. Default is nullable for populated tables."
  },
  "Widen nvarchar without shrinking": {
    "scope": "sql",
    "prefix": "idem_widen_nvarchar",
    "body": [
      "IF OBJECT_ID(N'[${1:dbo}].[${2:Example}]', N'U') IS NULL",
      "    THROW 51000, N'Required table is missing or is not visible to this principal.', 1;",
      "",
      "DECLARE @TargetLength int = ${4:400};",
      "DECLARE @CurrentBytes smallint, @Nullable bit, @TypeId int, @Computed bit;",
      "",
      "IF @TargetLength IS NULL OR @TargetLength NOT BETWEEN 1 AND 4000",
      "    THROW 51000, N'Target nvarchar length must be between 1 and 4000.', 1;",
      "",
      "SELECT @CurrentBytes = max_length, @Nullable = is_nullable,",
      "       @TypeId = user_type_id, @Computed = is_computed",
      "FROM sys.columns",
      "WHERE object_id = OBJECT_ID(N'[${1}].[${2}]', N'U')",
      "  AND name = N'${3:Description}';",
      "",
      "IF @CurrentBytes IS NULL",
      "    THROW 51000, N'Required column is missing.', 1;",
      "IF @TypeId <> TYPE_ID(N'nvarchar') OR @Computed = 1",
      "    THROW 51000, N'Expected an ordinary built-in nvarchar column.', 1;",
      "",
      "-- max_length is bytes; -1 means MAX. Never narrow an existing column.",
      "IF @CurrentBytes <> -1 AND @CurrentBytes < @TargetLength * 2",
      "BEGIN",
      "    IF @Nullable = 1",
      "        ALTER TABLE [${1}].[${2}] ALTER COLUMN [${3}] nvarchar(${4}) NULL;",
      "    ELSE",
      "        ALTER TABLE [${1}].[${2}] ALTER COLUMN [${3}] nvarchar(${4}) NOT NULL;",
      "END;",
      "GO",
      "$0"
    ],
    "description": "Definition-aware: widens ordinary nvarchar columns to a minimum size, preserves nullability, and retains larger/MAX columns."
  },
  "Add default if column has none": {
    "scope": "sql",
    "prefix": "idem_default",
    "body": [
      "IF OBJECT_ID(N'[${1:dbo}].[${2:Example}]', N'U') IS NULL",
      "    THROW 51000, N'Required table is missing or is not visible to this principal.', 1;",
      "",
      "IF NOT EXISTS",
      "(",
      "    SELECT 1 FROM sys.columns",
      "    WHERE object_id = OBJECT_ID(N'[${1}].[${2}]', N'U') AND name = N'${3:Name}'",
      ")",
      "    THROW 51000, N'Required column is missing.', 1;",
      "",
      "-- default_object_id also detects legacy bound defaults.",
      "IF EXISTS",
      "(",
      "    SELECT 1 FROM sys.columns",
      "    WHERE object_id = OBJECT_ID(N'[${1}].[${2}]', N'U')",
      "      AND name = N'${3}' AND default_object_id = 0",
      ")",
      "BEGIN",
      "    ALTER TABLE [${1}].[${2}]",
      "        ADD CONSTRAINT [${4:DF_Example_Name}] DEFAULT (${5:N'Unknown'}) FOR [${3}];",
      "END;",
      "GO",
      "$0"
    ],
    "description": "Existence guard by column, including differently named defaults; does not replace an existing expression."
  },
  "Add and trust check constraint": {
    "scope": "sql",
    "prefix": "idem_check",
    "body": [
      "IF OBJECT_ID(N'[${1:dbo}].[${2:Example}]', N'U') IS NULL",
      "    THROW 51000, N'Required table is missing or is not visible to this principal.', 1;",
      "",
      "-- An existing constraint with this name must already have the intended expression.",
      "IF NOT EXISTS",
      "(",
      "    SELECT 1 FROM sys.check_constraints",
      "    WHERE parent_object_id = OBJECT_ID(N'[${1}].[${2}]', N'U')",
      "      AND name = N'${3:CK_Example_Id_Positive}'",
      ")",
      "BEGIN",
      "    ALTER TABLE [${1}].[${2}] WITH CHECK",
      "        ADD CONSTRAINT [${3}] CHECK (${4:[Id] > 0});",
      "END;",
      "",
      "IF EXISTS",
      "(",
      "    SELECT 1 FROM sys.check_constraints",
      "    WHERE parent_object_id = OBJECT_ID(N'[${1}].[${2}]', N'U')",
      "      AND name = N'${3}' AND (is_disabled = 1 OR is_not_trusted = 1)",
      ")",
      "    ALTER TABLE [${1}].[${2}] WITH CHECK CHECK CONSTRAINT [${3}];",
      "GO",
      "$0"
    ],
    "description": "Existence guard by name; validates existing rows and enables/trusts the constraint. Does not compare its expression."
  },
  "Add and trust foreign key": {
    "scope": "sql",
    "prefix": "idem_fk",
    "body": [
      "IF OBJECT_ID(N'[${1:dbo}].[${2:ExampleChild}]', N'U') IS NULL",
      "    THROW 51000, N'Required table is missing or is not visible to this principal.', 1;",
      "",
      "IF OBJECT_ID(N'[${5:dbo}].[${6:Example}]', N'U') IS NULL",
      "    THROW 51000, N'Referenced table is missing.', 1;",
      "",
      "-- An existing FK with this name must already have the intended mapping/actions.",
      "IF NOT EXISTS",
      "(",
      "    SELECT 1 FROM sys.foreign_keys",
      "    WHERE parent_object_id = OBJECT_ID(N'[${1}].[${2}]', N'U')",
      "      AND name = N'${3:FK_ExampleChild_Example}'",
      ")",
      "BEGIN",
      "    ALTER TABLE [${1}].[${2}] WITH CHECK",
      "        ADD CONSTRAINT [${3}] FOREIGN KEY ([${4:ExampleId}])",
      "        REFERENCES [${5}].[${6}] ([${7:Id}]);",
      "END;",
      "",
      "IF EXISTS",
      "(",
      "    SELECT 1 FROM sys.foreign_keys",
      "    WHERE parent_object_id = OBJECT_ID(N'[${1}].[${2}]', N'U')",
      "      AND name = N'${3}' AND (is_disabled = 1 OR is_not_trusted = 1)",
      ")",
      "    ALTER TABLE [${1}].[${2}] WITH CHECK CHECK CONSTRAINT [${3}];",
      "GO",
      "$0"
    ],
    "description": "Existence guard by name; checks existing rows and enables/trusts the FK. Does not compare an existing mapping."
  },
  "Create nonclustered index if missing": {
    "scope": "sql",
    "prefix": "idem_index",
    "body": [
      "IF OBJECT_ID(N'[${1:dbo}].[${2:Example}]', N'U') IS NULL",
      "    THROW 51000, N'Required table is missing or is not visible to this principal.', 1;",
      "",
      "-- An existing same-name index is retained, even if its definition differs.",
      "IF NOT EXISTS",
      "(",
      "    SELECT 1 FROM sys.indexes",
      "    WHERE object_id = OBJECT_ID(N'[${1}].[${2}]', N'U') AND name = N'${3:IX_Example_Name}'",
      ")",
      "BEGIN",
      "    CREATE NONCLUSTERED INDEX [${3}] ON [${1}].[${2}] (${4:[Name] ASC});",
      "END;",
      "GO",
      "$0"
    ],
    "description": "Existence guard scoped to a table; does not reconcile keys, filters, options, or disabled indexes."
  },
  "Create or alter procedure": {
    "scope": "sql",
    "prefix": "idem_proc",
    "body": [
      "GO",
      "SET ANSI_NULLS ON;",
      "SET QUOTED_IDENTIFIER ON;",
      "GO",
      "",
      "CREATE OR ALTER PROCEDURE [${1:dbo}].[${2:usp_Example}]",
      "AS",
      "BEGIN",
      "    SET NOCOUNT ON;",
      "    ${3:SELECT CAST(1 AS int) AS [Result];}",
      "END;",
      "GO",
      "$0"
    ],
    "description": "Converges a SQL procedure definition; requires SQL Server 2016 SP1 or later."
  },
  "Create or alter view": {
    "scope": "sql",
    "prefix": "idem_view",
    "body": [
      "GO",
      "SET ANSI_NULLS ON;",
      "SET QUOTED_IDENTIFIER ON;",
      "GO",
      "",
      "CREATE OR ALTER VIEW [${1:dbo}].[${2:vw_Example}]",
      "AS",
      "    ${3:SELECT CAST(1 AS int) AS [Id]};",
      "GO",
      "$0"
    ],
    "description": "Converges a view definition; requires SQL Server 2016 SP1 or later."
  },
  "Create or alter inline table-valued function": {
    "scope": "sql",
    "prefix": "idem_function",
    "body": [
      "GO",
      "SET ANSI_NULLS ON;",
      "SET QUOTED_IDENTIFIER ON;",
      "GO",
      "",
      "CREATE OR ALTER FUNCTION [${1:dbo}].[${2:ufn_Example}] (@${3:Id} ${4:int})",
      "RETURNS TABLE",
      "AS",
      "RETURN",
      "(",
      "    ${5:SELECT @Id AS [Id]}",
      ");",
      "GO",
      "$0"
    ],
    "description": "Converges an inline TVF definition; cannot convert an existing function to a different function kind."
  },
  "Insert seed row if missing": {
    "scope": "sql",
    "prefix": "idem_insert",
    "body": [
      "-- Requires a non-null key backed by a PRIMARY KEY or UNIQUE constraint/index.",
      "DECLARE @SeedKey ${4:int} = ${5:1};",
      "DECLARE @SeedValue ${7:nvarchar(100)} = ${8:N'Example'};",
      "IF @SeedKey IS NULL",
      "    THROW 51000, N'Seed key must not be NULL.', 1;",
      "",
      "INSERT INTO [${1:dbo}].[${2:Example}] ([${3:Id}], [${6:Name}])",
      "SELECT @SeedKey, @SeedValue",
      "WHERE NOT EXISTS",
      "(",
      "    SELECT 1 FROM [${1}].[${2}] WITH (UPDLOCK, HOLDLOCK)",
      "    WHERE [${3}] = @SeedKey",
      ");",
      "GO",
      "$0"
    ],
    "description": "Single-statement insert with key-range locking; retains values in existing rows. Requires a unique non-null key."
  },
  "Update seed row only when value differs": {
    "scope": "sql",
    "prefix": "idem_update",
    "body": [
      "-- Requires a non-null key backed by a PRIMARY KEY or UNIQUE constraint/index.",
      "DECLARE @SeedKey ${4:int} = ${5:1};",
      "DECLARE @SeedValue ${7:nvarchar(100)} = ${8:N'Example'};",
      "IF @SeedKey IS NULL",
      "    THROW 51000, N'Seed key must not be NULL.', 1;",
      "",
      "UPDATE [${1:dbo}].[${2:Example}]",
      "SET [${6:Name}] = @SeedValue",
      "WHERE [${3:Id}] = @SeedKey",
      "  AND ([${6}] <> @SeedValue",
      "       OR ([${6}] IS NULL AND @SeedValue IS NOT NULL)",
      "       OR ([${6}] IS NOT NULL AND @SeedValue IS NULL));",
      "GO",
      "$0"
    ],
    "description": "NULL-aware change check for comparable SQL types; does not insert a missing row."
  },
  "Insert or update seed row": {
    "scope": "sql",
    "prefix": "idem_upsert",
    "body": [
      "-- Standalone transaction: do not nest inside a migration transaction.",
      "IF @@TRANCOUNT <> 0 OR (@@OPTIONS & 2) = 2",
      "    THROW 51000, N'Run this snippet with no active transaction and IMPLICIT_TRANSACTIONS OFF.', 1;",
      "",
      "-- Requires a non-null key backed by a PRIMARY KEY or UNIQUE constraint/index.",
      "DECLARE @SeedKey ${4:int} = ${5:1};",
      "DECLARE @SeedValue ${7:nvarchar(100)} = ${8:N'Example'};",
      "IF @SeedKey IS NULL",
      "    THROW 51000, N'Seed key must not be NULL.', 1;",
      "",
      "BEGIN TRY",
      "    BEGIN TRANSACTION;",
      "    IF NOT EXISTS",
      "    (",
      "        SELECT 1 FROM [${1:dbo}].[${2:Example}] WITH (UPDLOCK, HOLDLOCK)",
      "        WHERE [${3:Id}] = @SeedKey",
      "    )",
      "    BEGIN",
      "        INSERT INTO [${1}].[${2}] ([${3}], [${6:Name}])",
      "        VALUES (@SeedKey, @SeedValue);",
      "    END",
      "    ELSE",
      "    BEGIN",
      "        UPDATE [${1}].[${2}]",
      "        SET [${6}] = @SeedValue",
      "        WHERE [${3}] = @SeedKey",
      "          AND ([${6}] <> @SeedValue",
      "               OR ([${6}] IS NULL AND @SeedValue IS NOT NULL)",
      "               OR ([${6}] IS NOT NULL AND @SeedValue IS NULL));",
      "    END;",
      "    COMMIT TRANSACTION;",
      "END TRY",
      "BEGIN CATCH",
      "    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;",
      "    THROW;",
      "END CATCH;",
      "GO",
      "$0"
    ],
    "description": "Owns a transaction, locks the key range, and skips unchanged values. Requires autocommit mode and a unique key."
  },
  "Drop standalone index if present": {
    "scope": "sql",
    "prefix": "idem_drop_index",
    "body": [
      "IF EXISTS",
      "(",
      "    SELECT 1 FROM sys.indexes",
      "    WHERE object_id = OBJECT_ID(N'[${1:dbo}].[${2:Example}]', N'U') AND name = N'${3:IX_Example_Name}'",
      "      AND (is_primary_key = 1 OR is_unique_constraint = 1)",
      ")",
      "    THROW 51000, N'This index backs a constraint; use an explicit constraint migration.', 1;",
      "",
      "IF EXISTS",
      "(",
      "    SELECT 1 FROM sys.indexes",
      "    WHERE object_id = OBJECT_ID(N'[${1}].[${2}]', N'U') AND name = N'${3}'",
      ")",
      "    DROP INDEX [${3}] ON [${1}].[${2}];",
      "GO",
      "$0"
    ],
    "description": "Removal: refuses to drop a primary-key or unique-constraint backing index. Unique standalone indexes can be dropped."
  },
  "Drop named constraint if present": {
    "scope": "sql",
    "prefix": "idem_drop_constraint",
    "body": [
      "-- Removing a constraint removes the corresponding integrity rule.",
      "IF EXISTS",
      "(",
      "    SELECT 1 FROM sys.objects",
      "    WHERE parent_object_id = OBJECT_ID(N'[${1:dbo}].[${2:Example}]', N'U')",
      "      AND name = N'${3:CK_Example_Id_Positive}' AND type IN (N'C', N'D', N'F', N'PK', N'UQ')",
      ")",
      "    ALTER TABLE [${1}].[${2}] DROP CONSTRAINT [${3}];",
      "GO",
      "$0"
    ],
    "description": "Removal: drops a named CHECK, DEFAULT, FK, PRIMARY KEY, or UNIQUE constraint on the specified table."
  },
  "Drop column if present": {
    "scope": "sql",
    "prefix": "idem_drop_column",
    "body": [
      "-- DATA LOSS: remove dependent constraints/indexes explicitly before running.",
      "IF EXISTS",
      "(",
      "    SELECT 1 FROM sys.columns",
      "    WHERE object_id = OBJECT_ID(N'[${1:dbo}].[${2:Example}]', N'U') AND name = N'${3:Description}'",
      ")",
      "    ALTER TABLE [${1}].[${2}] DROP COLUMN [${3}];",
      "GO",
      "$0"
    ],
    "description": "Removal: permanently deletes column data. Dependencies must be handled explicitly; nothing is automatically cascaded."
  },
  "Serialize a migration transaction": {
    "scope": "sql",
    "prefix": "idem_transaction",
    "body": [
      "-- Standalone transaction. All cooperating deployers must use the same lock name.",
      "IF @@TRANCOUNT <> 0 OR (@@OPTIONS & 2) = 2",
      "    THROW 51000, N'Run this snippet with no active transaction and IMPLICIT_TRANSACTIONS OFF.', 1;",
      "",
      "DECLARE @MigrationLockResult int;",
      "BEGIN TRY",
      "    BEGIN TRANSACTION;",
      "    EXEC @MigrationLockResult = sys.sp_getapplock",
      "        @Resource = N'${1:SchemaDeployment}',",
      "        @LockMode = N'Exclusive',",
      "        @LockOwner = N'Transaction',",
      "        @LockTimeout = 60000;",
      "    IF @MigrationLockResult < 0",
      "        THROW 51000, N'Could not acquire the migration application lock.', 1;",
      "",
      "    ${2:-- Insert idempotent statements here. No GO separators.}",
      "",
      "    COMMIT TRANSACTION;",
      "END TRY",
      "BEGIN CATCH",
      "    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;",
      "    THROW;",
      "END CATCH;",
      "GO",
      "$0"
    ],
    "description": "Wrapper only: serializes cooperating deployments using a shared application lock; inserted SQL must itself be idempotent."
  }
}