using Dapper;
using Microsoft.Data.SqlClient;
using Respawn;
using Xunit;

namespace SIGCM2.TestSupport;

/// <summary>
/// Manages a real SQL Server test database.
/// Resets state between test runs using Respawn.
/// Seeds the admin user after each reset.
/// </summary>
public sealed class SqlTestFixture : IAsyncLifetime
{
    private readonly string _connectionString;
    private SqlConnection _connection = null!;
    private Respawner _respawner = null!;

    /// <summary>Parameterless ctor for xUnit ICollectionFixture — uses SIGCM2_Test_App.</summary>
    public SqlTestFixture() : this(TestConnectionStrings.AppTestDb) { }

    /// <summary>
    /// Explicit connection string ctor — used by TestWebAppFactory (same assembly).
    /// Internal to satisfy xUnit's "single public constructor" rule for ICollectionFixture.
    /// </summary>
    internal SqlTestFixture(string connectionString)
    {
        _connectionString = connectionString;
    }

    public async Task InitializeAsync()
    {
        _connection = new SqlConnection(_connectionString);
        await _connection.OpenAsync();

        // V008: ensure MustChangePassword column and IX_Usuario_Activo_Rol exist in test DB
        await EnsureV008SchemaAsync();

        // V009: update PermisosJson DEFAULT constraint and migrate legacy rows
        await EnsureV009SchemaAsync();

        // V010 (UDT-010): verify audit infrastructure + temporal tables are active.
        // Applied manually via: sqlcmd ... -i database/migrations/V010__audit_infrastructure.sql
        await EnsureV010SchemaAsync();

        // V011 (ADM-001): ensure dbo.Medio, dbo.Seccion + temporal tables + permiso 'administracion:secciones:gestionar'.
        await EnsureV011SchemaAsync();

        // V013 (ADM-008): ensure dbo.PuntoDeVenta + temporal + permiso. Drops idempotentes
        // de SecuenciaComprobante + SP usp_ReservarNumeroComprobante (cirugía post-smoke:
        // IMAC asigna numeros AFIP, no nosotros — ver memoria architecture/facturacion-imac-numeracion).
        await EnsureV013SchemaAsync();

        // V014 (ADM-009): ensure dbo.TipoDeIva + dbo.IngresosBrutos + temporal + seed + permiso fiscal.
        await EnsureV014SchemaAsync();

        // V015 (UDT-011): ensure dbo.v_AuditEvent_Local + dbo.v_SecurityEvent_Local views exist.
        await EnsureV015SchemaAsync();

        // V016 (CAT-001): ensure dbo.Rubro + temporal + permiso 'catalogo:rubros:gestionar'.
        await EnsureV016SchemaAsync();

        // V017 (PRD-001): ensure dbo.ProductType + temporal + permiso 'catalogo:tipos:gestionar'.
        await EnsureV017SchemaAsync();

        _respawner = await Respawner.CreateAsync(_connection, new RespawnerOptions
        {
            DbAdapter = DbAdapter.SqlServer,
            // Rol is a lookup table seeded by migration V003 — never wipe or Usuario FK breaks.
            // Permiso and RolPermiso are seeded by V005/V006 — never wipe or integration tests lose the permission catalog.
            // *_History tables: UDT-010/ADM-001 system-versioned — Respawn cannot DELETE them directly (engine rejects).
            TablesToIgnore =
            [
                new Respawn.Graph.Table("dbo", "Rol"),
                new Respawn.Graph.Table("dbo", "Permiso"),
                new Respawn.Graph.Table("dbo", "RolPermiso"),
                new Respawn.Graph.Table("dbo", "Usuario_History"),
                new Respawn.Graph.Table("dbo", "Rol_History"),
                new Respawn.Graph.Table("dbo", "Permiso_History"),
                new Respawn.Graph.Table("dbo", "RolPermiso_History"),
                new Respawn.Graph.Table("dbo", "Medio_History"),
                new Respawn.Graph.Table("dbo", "Seccion_History"),
                new Respawn.Graph.Table("dbo", "PuntoDeVenta_History"),
                // ADM-009 (V014): TipoDeIva + IngresosBrutos son temporales.
                new Respawn.Graph.Table("dbo", "TipoDeIva_History"),
                new Respawn.Graph.Table("dbo", "IngresosBrutos_History"),
                // Seed de TipoDeIva e IngresosBrutos son datos de referencia — no limpiar con Respawn.
                new Respawn.Graph.Table("dbo", "TipoDeIva"),
                new Respawn.Graph.Table("dbo", "IngresosBrutos"),
                // CAT-001 (V016): Rubro es temporal — history no puede deletearse directo.
                new Respawn.Graph.Table("dbo", "Rubro_History"),
                // PRD-001 (V017): ProductType es temporal — history no puede deletearse directo.
                new Respawn.Graph.Table("dbo", "ProductType_History"),
            ]
        });

        await ResetAndSeedAsync();
    }

    /// <summary>
    /// Exposes the open SqlConnection for tests that need to run ad-hoc queries
    /// (e.g. seed extra rows, assert history tables). Connection is opened during
    /// InitializeAsync and closed in DisposeAsync.
    /// </summary>
    public SqlConnection Connection => _connection;

    public async Task ResetAndSeedAsync()
    {
        await _respawner.ResetAsync(_connection);
        await SeedRolCanonicalAsync();
        await SeedPermisosCanonicalAsync();
        await SeedRolPermisosCanonicalAsync();
        await SeedAdminAsync();
        await SeedMediosCanonicalAsync();
    }

    private async Task SeedRolCanonicalAsync()
    {
        const string sql = """
            SET QUOTED_IDENTIFIER ON;
            MERGE dbo.Rol AS t
            USING (VALUES
                ('admin', N'Administrador', N'Supervisor total'),
                ('cajero', N'Cajero', N'Mostrador contado'),
                ('operador_ctacte', N'Operador Cta Cte', N'Cuenta corriente'),
                ('picadora', N'Picadora/Correctora', N'Edición de textos'),
                ('jefe_publicidad', N'Jefe de Publicidad', N'Supervisión de pauta'),
                ('productor', N'Productor', N'Carga restringida'),
                ('diagramacion', N'Diagramación/Taller', N'Solo lectura pauta'),
                ('reportes', N'Reportes', N'Solo lectura reportes')
            ) AS s (Codigo, Nombre, Descripcion)
            ON t.Codigo = s.Codigo
            WHEN NOT MATCHED BY TARGET THEN
                INSERT (Codigo, Nombre, Descripcion, Activo)
                VALUES (s.Codigo, s.Nombre, s.Descripcion, 1);
            """;
        await _connection.ExecuteAsync(sql);
    }

    public async Task DisposeAsync()
    {
        if (_connection is not null)
        {
            await _connection.CloseAsync();
            await _connection.DisposeAsync();
        }
    }

    /// <summary>
    /// Applies V008 schema changes idempotently to the test database.
    /// Mirrors V008__add_mustchangepassword_and_indexes.sql.
    /// </summary>
    private async Task EnsureV008SchemaAsync()
    {
        const string addColumn = """
            IF COL_LENGTH('dbo.Usuario', 'MustChangePassword') IS NULL
            BEGIN
                ALTER TABLE dbo.Usuario
                    ADD MustChangePassword BIT NOT NULL
                    CONSTRAINT DF_Usuario_MustChangePassword DEFAULT(0);
            END
            """;

        const string addIndex = """
            IF NOT EXISTS (
                SELECT 1 FROM sys.indexes
                WHERE name = 'IX_Usuario_Activo_Rol'
                  AND object_id = OBJECT_ID('dbo.Usuario')
            )
            BEGIN
                CREATE INDEX IX_Usuario_Activo_Rol
                    ON dbo.Usuario(Activo, Rol)
                    INCLUDE (Id, Username, Email, UltimoLogin, FechaModificacion);
            END
            """;

        await _connection.ExecuteAsync(addColumn);
        await _connection.ExecuteAsync(addIndex);
    }

    private async Task SeedPermisosCanonicalAsync()
    {
        const string sql = """
            SET QUOTED_IDENTIFIER ON;
            MERGE dbo.Permiso AS t
            USING (VALUES
                ('ventas:contado:crear',                N'Cargar orden contado',                NULL,                                           'ventas'),
                ('ventas:contado:modificar',            N'Modificar orden contado',             NULL,                                           'ventas'),
                ('ventas:contado:cobrar',               N'Cobrar orden contado',                NULL,                                           'ventas'),
                ('ventas:contado:facturar',             N'Facturar orden contado',              NULL,                                           'ventas'),
                ('ventas:ctacte:crear',                 N'Cargar orden cuenta corriente',       NULL,                                           'ventas'),
                ('ventas:ctacte:facturar',              N'Facturar lote cuenta corriente',      NULL,                                           'ventas'),
                ('textos:editar',                       N'Editar textos',                       NULL,                                           'textos'),
                ('textos:reclamos:ver',                 N'Ver reclamos de textos',              NULL,                                           'textos'),
                ('pauta:azanu:ver',                     N'Ver AZANU en pauta',                  NULL,                                           'pauta'),
                ('pauta:limpiar',                       N'Limpieza de pauta',                   NULL,                                           'pauta'),
                ('pauta:recursos:fueradehora',          N'Recursos fuera de hora',              NULL,                                           'pauta'),
                ('productores:deuda:ver',               N'Ver deuda propia de productores',     NULL,                                           'productores'),
                ('productores:pendientes:crear',        N'Cargar pendientes de productores',    NULL,                                           'productores'),
                ('productores:deuda:bypass',            N'Bypass de deuda de productores',      NULL,                                           'productores'),
                ('administracion:usuarios:gestionar',          N'Gestionar usuarios del sistema',       N'Crear, editar y desactivar usuarios',          'administracion'),
                ('administracion:tarifarios:gestionar',        N'Gestionar tarifarios',                 N'Crear y modificar tarifarios de publicidad',   'administracion'),
                ('administracion:medios:gestionar',            N'Gestionar medios publicitarios',        N'Alta y configuracion de medios',               'administracion'),
                ('administracion:auditoria:ver',               N'Ver logs de auditoria',                N'Acceso al dashboard de auditoria',             'administracion'),
                -- V007 (UDT-006): permisos administrativos RBAC
                ('administracion:roles:gestionar',             N'Gestionar roles del sistema',          N'Crear, editar y desactivar roles RBAC',         'administracion'),
                ('administracion:roles_permisos:gestionar',    N'Gestionar asignacion de permisos',     N'Asignar y revocar permisos por rol',            'administracion'),
                ('administracion:permisos:ver',                N'Ver catalogo de permisos',             N'Consultar el listado de permisos del sistema',  'administracion'),
                -- V011 (ADM-001): permiso para CRUD de Secciones
                ('administracion:secciones:gestionar',         N'Gestionar secciones por medio',        N'Crear, editar y desactivar secciones de un medio','administracion'),
                -- V013 (ADM-008): permiso para CRUD de Puntos de Venta
                ('administracion:puntos_de_venta:gestionar',   N'Gestionar puntos de venta',            N'Crear, editar y desactivar puntos de venta AFIP','administracion'),
                -- V014 (ADM-009): permiso para tablas fiscales
                ('administracion:fiscal:gestionar',            N'Gestionar tablas fiscales',            N'Gestionar tablas fiscales (IVA, IIBB)',           'administracion'),
                -- V016 (CAT-001): permiso para gestionar árbol de rubros
                ('catalogo:rubros:gestionar',                  N'Gestionar rubros del catálogo',        N'Crear, editar, mover y desactivar rubros del árbol de catálogo comercial', 'catalogo')
            ) AS s (Codigo, Nombre, Descripcion, Modulo)
            ON t.Codigo = s.Codigo
            WHEN NOT MATCHED BY TARGET THEN
                INSERT (Codigo, Nombre, Descripcion, Modulo)
                VALUES (s.Codigo, s.Nombre, s.Descripcion, s.Modulo);
            """;
        await _connection.ExecuteAsync(sql);
    }

    private async Task SeedRolPermisosCanonicalAsync()
    {
        const string sql = """
            SET QUOTED_IDENTIFIER ON;
            MERGE dbo.RolPermiso AS t
            USING (
                SELECT r.Id AS RolId, p.Id AS PermisoId
                FROM (VALUES
                    ('admin', 'ventas:contado:crear'),
                    ('admin', 'ventas:contado:modificar'),
                    ('admin', 'ventas:contado:cobrar'),
                    ('admin', 'ventas:contado:facturar'),
                    ('admin', 'ventas:ctacte:crear'),
                    ('admin', 'ventas:ctacte:facturar'),
                    ('admin', 'textos:editar'),
                    ('admin', 'textos:reclamos:ver'),
                    ('admin', 'pauta:azanu:ver'),
                    ('admin', 'pauta:limpiar'),
                    ('admin', 'pauta:recursos:fueradehora'),
                    ('admin', 'productores:deuda:ver'),
                    ('admin', 'productores:pendientes:crear'),
                    ('admin', 'productores:deuda:bypass'),
                    ('admin', 'administracion:usuarios:gestionar'),
                    ('admin', 'administracion:tarifarios:gestionar'),
                    ('admin', 'administracion:medios:gestionar'),
                    ('admin', 'administracion:auditoria:ver'),
                    -- V007 (UDT-006): permisos administrativos RBAC para admin
                    ('admin', 'administracion:roles:gestionar'),
                    ('admin', 'administracion:roles_permisos:gestionar'),
                    ('admin', 'administracion:permisos:ver'),
                    -- V011 (ADM-001)
                    ('admin', 'administracion:secciones:gestionar'),
                    -- V013 (ADM-008)
                    ('admin', 'administracion:puntos_de_venta:gestionar'),
                    -- V014 (ADM-009)
                    ('admin', 'administracion:fiscal:gestionar'),
                    -- V016 (CAT-001)
                    ('admin', 'catalogo:rubros:gestionar'),
                    ('cajero', 'ventas:contado:crear'),
                    ('cajero', 'ventas:contado:modificar'),
                    ('cajero', 'ventas:contado:cobrar'),
                    ('cajero', 'ventas:contado:facturar'),
                    ('operador_ctacte', 'ventas:ctacte:crear'),
                    ('operador_ctacte', 'ventas:ctacte:facturar'),
                    ('picadora', 'textos:editar'),
                    ('picadora', 'textos:reclamos:ver'),
                    ('jefe_publicidad', 'textos:editar'),
                    ('jefe_publicidad', 'textos:reclamos:ver'),
                    ('jefe_publicidad', 'pauta:azanu:ver'),
                    ('jefe_publicidad', 'pauta:limpiar'),
                    ('jefe_publicidad', 'pauta:recursos:fueradehora'),
                    ('jefe_publicidad', 'productores:deuda:ver'),
                    ('jefe_publicidad', 'productores:deuda:bypass'),
                    ('productor', 'productores:deuda:ver'),
                    ('productor', 'productores:pendientes:crear'),
                    ('diagramacion', 'pauta:azanu:ver')
                ) AS x (RolCodigo, PermisoCodigo)
                JOIN dbo.Rol     r ON r.Codigo = x.RolCodigo
                JOIN dbo.Permiso p ON p.Codigo = x.PermisoCodigo
            ) AS s ON t.RolId = s.RolId AND t.PermisoId = s.PermisoId
            WHEN NOT MATCHED BY TARGET THEN
                INSERT (RolId, PermisoId) VALUES (s.RolId, s.PermisoId);
            """;
        await _connection.ExecuteAsync(sql);
    }

    private async Task SeedAdminAsync()
    {
        // V009: PermisosJson uses new canonical shape {"grant":[],"deny":[]} — NOT legacy '["*"]'
        const string sql = """
            SET QUOTED_IDENTIFIER ON;
            IF NOT EXISTS (SELECT 1 FROM dbo.Usuario WHERE Username = 'admin')
            INSERT INTO dbo.Usuario (Username, PasswordHash, Nombre, Apellido, Rol, PermisosJson, Activo, MustChangePassword)
            VALUES (
                'admin',
                '$2a$12$rmq6tlSAQ8WXhR2CwLCSeuwCJKz/.8Eab95UQCUNfwe4dokeOqMcW',
                'Administrador', 'Sistema', 'admin', '{"grant":[],"deny":[]}', 1, 0
            );
            """;
        await _connection.ExecuteAsync(sql);
    }

    /// <summary>
    /// ADM-001 (V011): applies Medio/Seccion schema + temporal + permiso 'administracion:secciones:gestionar'
    /// idempotently to the test database. Mirrors V011__create_medio_seccion.sql.
    /// Canonical seed (ELDIA, ELPLATA) vive en SeedMediosCanonicalAsync — se reaplica tras cada Respawn.
    /// </summary>
    private async Task EnsureV011SchemaAsync()
    {
        const string createMedio = """
            IF OBJECT_ID(N'dbo.Medio', N'U') IS NULL
            BEGIN
                CREATE TABLE dbo.Medio (
                    Id                   INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Medio PRIMARY KEY,
                    Codigo               VARCHAR(30)       NOT NULL,
                    Nombre               NVARCHAR(100)     NOT NULL,
                    Tipo                 TINYINT           NOT NULL,
                    PlataformaEmpresaId  INT               NULL,
                    Activo               BIT               NOT NULL CONSTRAINT DF_Medio_Activo         DEFAULT(1),
                    FechaCreacion        DATETIME2(3)      NOT NULL CONSTRAINT DF_Medio_FechaCreacion  DEFAULT(SYSUTCDATETIME()),
                    FechaModificacion    DATETIME2(3)      NULL,
                    CONSTRAINT UQ_Medio_Codigo UNIQUE (Codigo),
                    CONSTRAINT CK_Medio_Tipo   CHECK (Tipo BETWEEN 1 AND 4)
                );
            END
            """;

        const string createMedioIndex = """
            IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_Medio_Activo_Tipo' AND object_id = OBJECT_ID('dbo.Medio'))
            BEGIN
                CREATE INDEX IX_Medio_Activo_Tipo
                    ON dbo.Medio(Activo, Tipo)
                    INCLUDE (Codigo, Nombre, PlataformaEmpresaId);
            END
            """;

        const string createSeccion = """
            IF OBJECT_ID(N'dbo.Seccion', N'U') IS NULL
            BEGIN
                CREATE TABLE dbo.Seccion (
                    Id                INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Seccion PRIMARY KEY,
                    MedioId           INT               NOT NULL,
                    Codigo            VARCHAR(30)       NOT NULL,
                    Nombre            NVARCHAR(100)     NOT NULL,
                    Tipo              VARCHAR(20)       NOT NULL,
                    Activo            BIT               NOT NULL CONSTRAINT DF_Seccion_Activo         DEFAULT(1),
                    FechaCreacion     DATETIME2(3)      NOT NULL CONSTRAINT DF_Seccion_FechaCreacion  DEFAULT(SYSUTCDATETIME()),
                    FechaModificacion DATETIME2(3)      NULL,
                    CONSTRAINT FK_Seccion_Medio FOREIGN KEY (MedioId) REFERENCES dbo.Medio(Id) ON DELETE NO ACTION,
                    CONSTRAINT UQ_Seccion_MedioId_Codigo UNIQUE (MedioId, Codigo),
                    CONSTRAINT CK_Seccion_Tipo CHECK (Tipo IN ('clasificados','notables','suplementos'))
                );
            END
            """;

        const string createSeccionIndex = """
            IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_Seccion_MedioId_Activo' AND object_id = OBJECT_ID('dbo.Seccion'))
            BEGIN
                CREATE INDEX IX_Seccion_MedioId_Activo
                    ON dbo.Seccion(MedioId, Activo)
                    INCLUDE (Codigo, Nombre, Tipo);
            END
            """;

        const string addMedioPeriod = """
            IF COL_LENGTH('dbo.Medio', 'ValidFrom') IS NULL
            BEGIN
                ALTER TABLE dbo.Medio
                ADD
                    ValidFrom DATETIME2(3) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
                        CONSTRAINT DF_Medio_ValidFrom DEFAULT(SYSUTCDATETIME()),
                    ValidTo   DATETIME2(3) GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL
                        CONSTRAINT DF_Medio_ValidTo   DEFAULT(CONVERT(DATETIME2(3), '9999-12-31 23:59:59.999')),
                    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
            END
            """;

        const string setMedioVersioning = """
            IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('dbo.Medio') AND temporal_type = 2)
            BEGIN
                ALTER TABLE dbo.Medio
                SET (SYSTEM_VERSIONING = ON (
                    HISTORY_TABLE = dbo.Medio_History,
                    HISTORY_RETENTION_PERIOD = 10 YEARS
                ));
            END
            """;

        const string addSeccionPeriod = """
            IF COL_LENGTH('dbo.Seccion', 'ValidFrom') IS NULL
            BEGIN
                ALTER TABLE dbo.Seccion
                ADD
                    ValidFrom DATETIME2(3) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
                        CONSTRAINT DF_Seccion_ValidFrom DEFAULT(SYSUTCDATETIME()),
                    ValidTo   DATETIME2(3) GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL
                        CONSTRAINT DF_Seccion_ValidTo   DEFAULT(CONVERT(DATETIME2(3), '9999-12-31 23:59:59.999')),
                    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
            END
            """;

        const string setSeccionVersioning = """
            IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('dbo.Seccion') AND temporal_type = 2)
            BEGIN
                ALTER TABLE dbo.Seccion
                SET (SYSTEM_VERSIONING = ON (
                    HISTORY_TABLE = dbo.Seccion_History,
                    HISTORY_RETENTION_PERIOD = 10 YEARS
                ));
            END
            """;

        await _connection.ExecuteAsync(createMedio);
        await _connection.ExecuteAsync(createMedioIndex);
        await _connection.ExecuteAsync(createSeccion);
        await _connection.ExecuteAsync(createSeccionIndex);
        await _connection.ExecuteAsync(addMedioPeriod);
        await _connection.ExecuteAsync(setMedioVersioning);
        await _connection.ExecuteAsync(addSeccionPeriod);
        await _connection.ExecuteAsync(setSeccionVersioning);
        // Permiso 'administracion:secciones:gestionar' + asignación a admin se siembran
        // desde SeedPermisosCanonicalAsync / SeedRolPermisosCanonicalAsync (post-respawn).
    }

    /// <summary>
    /// ADM-008 (V013): applies dbo.PuntoDeVenta schema + temporal table.
    /// NOTE: SecuenciaComprobante y SP usp_ReservarNumeroComprobante fueron eliminados
    /// post-smoke (Batch 9) — IMAC/Infogestión asigna los números AFIP externamente.
    /// Este método también hace DROP idempotente de esos artefactos en caso de que
    /// SIGCM2_Test los tuviera de una versión previa de la migración V013.
    /// Permiso y asignación se siembran desde SeedPermisosCanonicalAsync / SeedRolPermisosCanonicalAsync.
    /// </summary>
    private async Task EnsureV013SchemaAsync()
    {
        // ── Drops idempotentes de artefactos eliminados (cirugía post-smoke) ──
        // Si SIGCM2_Test tiene SecuenciaComprobante o el SP de una versión previa, se limpian.
        const string dropSp = """
            IF OBJECT_ID(N'dbo.usp_ReservarNumeroComprobante', N'P') IS NOT NULL
                DROP PROCEDURE dbo.usp_ReservarNumeroComprobante;
            """;

        const string disableSecuenciaVersioning = """
            IF EXISTS (SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('dbo.SecuenciaComprobante') AND temporal_type = 2)
                ALTER TABLE dbo.SecuenciaComprobante SET (SYSTEM_VERSIONING = OFF);
            """;

        const string dropSecuenciaHistory = """
            IF OBJECT_ID(N'dbo.SecuenciaComprobante_History', N'U') IS NOT NULL
                DROP TABLE dbo.SecuenciaComprobante_History;
            """;

        const string dropSecuencia = """
            IF OBJECT_ID(N'dbo.SecuenciaComprobante', N'U') IS NOT NULL
                DROP TABLE dbo.SecuenciaComprobante;
            """;

        // ── PuntoDeVenta: crear si no existe ──────────────────────────────────
        const string createPdv = """
            IF OBJECT_ID(N'dbo.PuntoDeVenta', N'U') IS NULL
            BEGIN
                CREATE TABLE dbo.PuntoDeVenta (
                    Id                INT IDENTITY(1,1)  NOT NULL CONSTRAINT PK_PuntoDeVenta PRIMARY KEY,
                    MedioId           INT                NOT NULL,
                    NumeroAFIP        SMALLINT           NOT NULL,
                    Nombre            NVARCHAR(100)      NOT NULL,
                    Descripcion       NVARCHAR(255)      NULL,
                    Activo            BIT                NOT NULL CONSTRAINT DF_PuntoDeVenta_Activo          DEFAULT(1),
                    FechaCreacion     DATETIME2(3)       NOT NULL CONSTRAINT DF_PuntoDeVenta_FechaCreacion   DEFAULT(SYSUTCDATETIME()),
                    FechaModificacion DATETIME2(3)       NULL,
                    CONSTRAINT FK_PuntoDeVenta_Medio     FOREIGN KEY (MedioId) REFERENCES dbo.Medio(Id) ON DELETE NO ACTION,
                    CONSTRAINT UQ_PuntoDeVenta_Medio_AFIP UNIQUE (MedioId, NumeroAFIP),
                    CONSTRAINT CK_PuntoDeVenta_NumeroAFIP CHECK (NumeroAFIP >= 1)
                );
            END
            """;

        const string createPdvIndex = """
            IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_PuntoDeVenta_MedioId_Activo' AND object_id = OBJECT_ID('dbo.PuntoDeVenta'))
            BEGIN
                CREATE INDEX IX_PuntoDeVenta_MedioId_Activo
                    ON dbo.PuntoDeVenta(MedioId, Activo)
                    INCLUDE (NumeroAFIP, Nombre);
            END
            """;

        const string addPdvPeriod = """
            IF COL_LENGTH('dbo.PuntoDeVenta', 'ValidFrom') IS NULL
            BEGIN
                ALTER TABLE dbo.PuntoDeVenta
                ADD
                    ValidFrom DATETIME2(3) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
                        CONSTRAINT DF_PuntoDeVenta_ValidFrom DEFAULT(SYSUTCDATETIME()),
                    ValidTo   DATETIME2(3) GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL
                        CONSTRAINT DF_PuntoDeVenta_ValidTo   DEFAULT(CONVERT(DATETIME2(3), '9999-12-31 23:59:59.999')),
                    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
            END
            """;

        const string setPdvVersioning = """
            IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('dbo.PuntoDeVenta') AND temporal_type = 2)
            BEGIN
                ALTER TABLE dbo.PuntoDeVenta
                SET (SYSTEM_VERSIONING = ON (
                    HISTORY_TABLE = dbo.PuntoDeVenta_History,
                    HISTORY_RETENTION_PERIOD = 10 YEARS
                ));
            END
            """;

        // Drops primero (limpieza de versión previa)
        await _connection.ExecuteAsync(dropSp);
        await _connection.ExecuteAsync(disableSecuenciaVersioning);
        await _connection.ExecuteAsync(dropSecuenciaHistory);
        await _connection.ExecuteAsync(dropSecuencia);

        // Luego crear PuntoDeVenta + Temporal Table
        await _connection.ExecuteAsync(createPdv);
        await _connection.ExecuteAsync(createPdvIndex);
        await _connection.ExecuteAsync(addPdvPeriod);
        await _connection.ExecuteAsync(setPdvVersioning);
    }

    /// <summary>
    /// ADM-001 (V012): MERGE seed ELDIA + ELPLATA. Re-seeded on every Respawn reset.
    /// </summary>
    private async Task SeedMediosCanonicalAsync()
    {
        const string sql = """
            SET QUOTED_IDENTIFIER ON;
            MERGE dbo.Medio AS t
            USING (VALUES
                ('ELDIA',   N'El Día',   1),
                ('ELPLATA', N'El Plata', 1)
            ) AS s (Codigo, Nombre, Tipo)
            ON t.Codigo = s.Codigo
            WHEN NOT MATCHED BY TARGET THEN
                INSERT (Codigo, Nombre, Tipo, PlataformaEmpresaId, Activo)
                VALUES (s.Codigo, s.Nombre, s.Tipo, NULL, 1);
            """;
        await _connection.ExecuteAsync(sql);
    }

    /// <summary>
    /// UDT-010 (V010): verifies that the audit infrastructure is present.
    /// Does NOT re-apply the migration (the ALTER DATABASE ADD FILEGROUP/FILE + partition
    /// function/scheme creation requires the full script). If missing, fails with a clear
    /// message pointing to the migration script.
    /// </summary>
    private async Task EnsureV010SchemaAsync()
    {
        const string check = """
            SELECT
                CAST(CASE WHEN OBJECT_ID('dbo.AuditEvent','U')   IS NULL THEN 0 ELSE 1 END AS BIT) AS HasAuditEvent,
                CAST(CASE WHEN OBJECT_ID('dbo.SecurityEvent','U') IS NULL THEN 0 ELSE 1 END AS BIT) AS HasSecurityEvent,
                CAST(CASE WHEN EXISTS (SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('dbo.Usuario') AND temporal_type = 2) THEN 1 ELSE 0 END AS BIT) AS UsuarioVersioned
            """;
        var result = await _connection.QuerySingleAsync<(bool HasAuditEvent, bool HasSecurityEvent, bool UsuarioVersioned)>(check);
        if (!result.HasAuditEvent || !result.HasSecurityEvent || !result.UsuarioVersioned)
        {
            throw new InvalidOperationException(
                "V010 audit infrastructure is not applied in the test database. " +
                "Run: sqlcmd -S <server> -d SIGCM2_Test -U <user> -P <pass> -i database/migrations/V010__audit_infrastructure.sql");
        }
    }

    /// <summary>
    /// Applies V009 schema changes idempotently to the test database.
    /// Mirrors V009__activate_permisos_overrides.sql.
    /// Drops and re-adds DF_Usuario_Permisos with the new shape, then migrates legacy rows.
    /// </summary>
    private async Task EnsureV009SchemaAsync()
    {
        const string dropConstraint = """
            IF EXISTS (
                SELECT 1 FROM sys.default_constraints
                WHERE name = 'DF_Usuario_Permisos'
                  AND parent_object_id = OBJECT_ID('dbo.Usuario')
            )
            BEGIN
                ALTER TABLE dbo.Usuario DROP CONSTRAINT DF_Usuario_Permisos;
            END
            """;

        const string addConstraint = """
            IF NOT EXISTS (
                SELECT 1 FROM sys.default_constraints
                WHERE name = 'DF_Usuario_Permisos'
                  AND parent_object_id = OBJECT_ID('dbo.Usuario')
            )
            BEGIN
                ALTER TABLE dbo.Usuario
                    ADD CONSTRAINT DF_Usuario_Permisos
                    DEFAULT('{"grant":[],"deny":[]}') FOR PermisosJson;
            END
            """;

        const string migrateRows = """
            UPDATE dbo.Usuario
            SET PermisosJson = '{"grant":[],"deny":[]}'
            WHERE PermisosJson IN ('[]', '["*"]', '')
               OR PermisosJson IS NULL
               OR LTRIM(RTRIM(PermisosJson)) = ''
            """;

        await _connection.ExecuteAsync(dropConstraint);
        await _connection.ExecuteAsync(addConstraint);
        await _connection.ExecuteAsync(migrateRows);
    }

    /// <summary>
    /// ADM-009 (V014): applies dbo.TipoDeIva + dbo.IngresosBrutos schema + temporal tables + seed + permiso fiscal.
    /// Mirrors V014__create_tablas_fiscales.sql (idempotente).
    /// Permiso y asignacion se siembran desde SeedPermisosCanonicalAsync / SeedRolPermisosCanonicalAsync.
    /// Seed de TipoDeIva e IngresosBrutos se aplica aqui (datos de referencia estables).
    /// </summary>
    private async Task EnsureV014SchemaAsync()
    {
        // ── 1. dbo.TipoDeIva ─────────────────────────────────────────────────
        const string createTipoDeIva = """
            IF OBJECT_ID(N'dbo.TipoDeIva', N'U') IS NULL
            BEGIN
                CREATE TABLE dbo.TipoDeIva (
                    Id                INT IDENTITY(1,1)  NOT NULL CONSTRAINT PK_TipoDeIva PRIMARY KEY,
                    Codigo            VARCHAR(32)        NOT NULL,
                    Descripcion       NVARCHAR(100)      NOT NULL,
                    Porcentaje        DECIMAL(5,2)       NOT NULL,
                    AplicaIVA         BIT                NOT NULL,
                    Activo            BIT                NOT NULL CONSTRAINT DF_TipoDeIva_Activo            DEFAULT(1),
                    VigenciaDesde     DATE               NOT NULL,
                    VigenciaHasta     DATE               NULL,
                    PredecesorId      INT                NULL,
                    FechaCreacion     DATETIME2(3)       NOT NULL CONSTRAINT DF_TipoDeIva_FechaCreacion     DEFAULT(SYSUTCDATETIME()),
                    FechaModificacion DATETIME2(3)       NULL,
                    CONSTRAINT CK_TipoDeIva_Porcentaje  CHECK (Porcentaje >= 0 AND Porcentaje <= 100),
                    CONSTRAINT CK_TipoDeIva_Vigencia    CHECK (VigenciaHasta IS NULL OR VigenciaHasta >= VigenciaDesde),
                    CONSTRAINT UQ_TipoDeIva_Codigo_Vigencia UNIQUE (Codigo, VigenciaDesde),
                    CONSTRAINT FK_TipoDeIva_Predecesor  FOREIGN KEY (PredecesorId) REFERENCES dbo.TipoDeIva(Id)
                );
            END
            """;

        const string createTipoDeIvaIdx1 = """
            IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_TipoDeIva_Codigo_VigenciaDesde' AND object_id = OBJECT_ID('dbo.TipoDeIva'))
                CREATE INDEX IX_TipoDeIva_Codigo_VigenciaDesde ON dbo.TipoDeIva(Codigo, VigenciaDesde DESC);
            """;

        const string createTipoDeIvaIdx2 = """
            IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_TipoDeIva_PredecesorId' AND object_id = OBJECT_ID('dbo.TipoDeIva'))
                CREATE INDEX IX_TipoDeIva_PredecesorId ON dbo.TipoDeIva(PredecesorId) WHERE PredecesorId IS NOT NULL;
            """;

        const string addTipoDeIvaPeriod = """
            IF COL_LENGTH('dbo.TipoDeIva', 'ValidFrom') IS NULL
            BEGIN
                ALTER TABLE dbo.TipoDeIva
                ADD
                    ValidFrom DATETIME2(3) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
                        CONSTRAINT DF_TipoDeIva_ValidFrom DEFAULT(SYSUTCDATETIME()),
                    ValidTo   DATETIME2(3) GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL
                        CONSTRAINT DF_TipoDeIva_ValidTo   DEFAULT(CONVERT(DATETIME2(3), '9999-12-31 23:59:59.999')),
                    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
            END
            """;

        const string setTipoDeIvaVersioning = """
            IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('dbo.TipoDeIva') AND temporal_type = 2)
            BEGIN
                ALTER TABLE dbo.TipoDeIva
                SET (SYSTEM_VERSIONING = ON (
                    HISTORY_TABLE = dbo.TipoDeIva_History,
                    HISTORY_RETENTION_PERIOD = 10 YEARS
                ));
            END
            """;

        // ── 2. dbo.IngresosBrutos ────────────────────────────────────────────
        const string createIIBB = """
            IF OBJECT_ID(N'dbo.IngresosBrutos', N'U') IS NULL
            BEGIN
                CREATE TABLE dbo.IngresosBrutos (
                    Id                INT IDENTITY(1,1)  NOT NULL CONSTRAINT PK_IngresosBrutos PRIMARY KEY,
                    Provincia         VARCHAR(50)        NOT NULL,
                    Descripcion       NVARCHAR(100)      NOT NULL,
                    Alicuota          DECIMAL(5,2)       NOT NULL,
                    Activo            BIT                NOT NULL CONSTRAINT DF_IIBB_Activo            DEFAULT(1),
                    VigenciaDesde     DATE               NOT NULL,
                    VigenciaHasta     DATE               NULL,
                    PredecesorId      INT                NULL,
                    FechaCreacion     DATETIME2(3)       NOT NULL CONSTRAINT DF_IIBB_FechaCreacion     DEFAULT(SYSUTCDATETIME()),
                    FechaModificacion DATETIME2(3)       NULL,
                    CONSTRAINT CK_IIBB_Alicuota         CHECK (Alicuota >= 0 AND Alicuota <= 100),
                    CONSTRAINT CK_IIBB_Vigencia         CHECK (VigenciaHasta IS NULL OR VigenciaHasta >= VigenciaDesde),
                    CONSTRAINT UQ_IIBB_Provincia_Vigencia UNIQUE (Provincia, VigenciaDesde),
                    CONSTRAINT FK_IIBB_Predecesor       FOREIGN KEY (PredecesorId) REFERENCES dbo.IngresosBrutos(Id)
                );
            END
            """;

        const string createIIBBIdx1 = """
            IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_IIBB_Provincia_VigenciaDesde' AND object_id = OBJECT_ID('dbo.IngresosBrutos'))
                CREATE INDEX IX_IIBB_Provincia_VigenciaDesde ON dbo.IngresosBrutos(Provincia, VigenciaDesde DESC);
            """;

        const string createIIBBIdx2 = """
            IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_IIBB_PredecesorId' AND object_id = OBJECT_ID('dbo.IngresosBrutos'))
                CREATE INDEX IX_IIBB_PredecesorId ON dbo.IngresosBrutos(PredecesorId) WHERE PredecesorId IS NOT NULL;
            """;

        const string addIIBBPeriod = """
            IF COL_LENGTH('dbo.IngresosBrutos', 'ValidFrom') IS NULL
            BEGIN
                ALTER TABLE dbo.IngresosBrutos
                ADD
                    ValidFrom DATETIME2(3) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
                        CONSTRAINT DF_IIBB_ValidFrom DEFAULT(SYSUTCDATETIME()),
                    ValidTo   DATETIME2(3) GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL
                        CONSTRAINT DF_IIBB_ValidTo   DEFAULT(CONVERT(DATETIME2(3), '9999-12-31 23:59:59.999')),
                    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
            END
            """;

        const string setIIBBVersioning = """
            IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('dbo.IngresosBrutos') AND temporal_type = 2)
            BEGIN
                ALTER TABLE dbo.IngresosBrutos
                SET (SYSTEM_VERSIONING = ON (
                    HISTORY_TABLE = dbo.IngresosBrutos_History,
                    HISTORY_RETENTION_PERIOD = 10 YEARS
                ));
            END
            """;

        // ── 3. Seed TipoDeIva ────────────────────────────────────────────────
        const string seedTipoDeIva = """
            SET QUOTED_IDENTIFIER ON;
            MERGE dbo.TipoDeIva AS t
            USING (VALUES
                ('EXENTO',     N'Exento de IVA',                    CAST(0    AS DECIMAL(5,2)), CAST(0 AS BIT), CAST('2020-01-01' AS DATE)),
                ('NO_GRAVADO', N'No gravado',                        CAST(0    AS DECIMAL(5,2)), CAST(0 AS BIT), CAST('2020-01-01' AS DATE)),
                ('IVA_105',    N'IVA alicuota diferencial 10.5%',   CAST(10.5 AS DECIMAL(5,2)), CAST(1 AS BIT), CAST('2020-01-01' AS DATE)),
                ('IVA_21',     N'IVA alicuota general 21%',         CAST(21   AS DECIMAL(5,2)), CAST(1 AS BIT), CAST('2020-01-01' AS DATE))
            ) AS s (Codigo, Descripcion, Porcentaje, AplicaIVA, VigenciaDesde)
            ON t.Codigo = s.Codigo AND t.PredecesorId IS NULL
            WHEN NOT MATCHED BY TARGET THEN
                INSERT (Codigo, Descripcion, Porcentaje, AplicaIVA, Activo, VigenciaDesde, VigenciaHasta, PredecesorId)
                VALUES (s.Codigo, s.Descripcion, s.Porcentaje, s.AplicaIVA, 1, s.VigenciaDesde, NULL, NULL);
            """;

        // ── 4. Seed IngresosBrutos ────────────────────────────────────────────
        // 24 filas: 23 provincias INDEC + CABA. Alicuota=0 placeholder.
        // T700 cleanup: values in PascalCase matching ProvinciaArgentina enum.ToString().
        const string seedIIBB = """
            SET QUOTED_IDENTIFIER ON;
            MERGE dbo.IngresosBrutos AS t
            USING (VALUES
                ('BuenosAires',                 N'Ingresos Brutos - Buenos Aires'),
                ('CiudadAutonomaDeBuenosAires',  N'Ingresos Brutos - Ciudad Autonoma de Buenos Aires'),
                ('Catamarca',                    N'Ingresos Brutos - Catamarca'),
                ('Chaco',                        N'Ingresos Brutos - Chaco'),
                ('Chubut',                       N'Ingresos Brutos - Chubut'),
                ('Cordoba',                      N'Ingresos Brutos - Cordoba'),
                ('Corrientes',                   N'Ingresos Brutos - Corrientes'),
                ('EntreRios',                    N'Ingresos Brutos - Entre Rios'),
                ('Formosa',                      N'Ingresos Brutos - Formosa'),
                ('Jujuy',                        N'Ingresos Brutos - Jujuy'),
                ('LaPampa',                      N'Ingresos Brutos - La Pampa'),
                ('LaRioja',                      N'Ingresos Brutos - La Rioja'),
                ('Mendoza',                      N'Ingresos Brutos - Mendoza'),
                ('Misiones',                     N'Ingresos Brutos - Misiones'),
                ('Neuquen',                      N'Ingresos Brutos - Neuquen'),
                ('RioNegro',                     N'Ingresos Brutos - Rio Negro'),
                ('Salta',                        N'Ingresos Brutos - Salta'),
                ('SanJuan',                      N'Ingresos Brutos - San Juan'),
                ('SanLuis',                      N'Ingresos Brutos - San Luis'),
                ('SantaCruz',                    N'Ingresos Brutos - Santa Cruz'),
                ('SantaFe',                      N'Ingresos Brutos - Santa Fe'),
                ('SantiagoDelEstero',             N'Ingresos Brutos - Santiago del Estero'),
                ('TierraDelFuego',               N'Ingresos Brutos - Tierra del Fuego'),
                ('Tucuman',                      N'Ingresos Brutos - Tucuman')
            ) AS s (Provincia, Descripcion)
            ON t.Provincia = s.Provincia AND t.PredecesorId IS NULL
            WHEN NOT MATCHED BY TARGET THEN
                INSERT (Provincia, Descripcion, Alicuota, Activo, VigenciaDesde, VigenciaHasta, PredecesorId)
                VALUES (s.Provincia, s.Descripcion, CAST(0 AS DECIMAL(5,2)), 1, CAST('2020-01-01' AS DATE), NULL, NULL);
            """;

        // Apply in order
        await _connection.ExecuteAsync(createTipoDeIva);
        await _connection.ExecuteAsync(createTipoDeIvaIdx1);
        await _connection.ExecuteAsync(createTipoDeIvaIdx2);
        await _connection.ExecuteAsync(addTipoDeIvaPeriod);
        await _connection.ExecuteAsync(setTipoDeIvaVersioning);
        await _connection.ExecuteAsync(createIIBB);
        await _connection.ExecuteAsync(createIIBBIdx1);
        await _connection.ExecuteAsync(createIIBBIdx2);
        await _connection.ExecuteAsync(addIIBBPeriod);
        await _connection.ExecuteAsync(setIIBBVersioning);
        await _connection.ExecuteAsync(seedTipoDeIva);
        await _connection.ExecuteAsync(seedIIBB);
        // Permiso 'administracion:fiscal:gestionar' y asignacion a admin se siembran
        // desde SeedPermisosCanonicalAsync / SeedRolPermisosCanonicalAsync (post-respawn).
    }

    /// <summary>
    /// UDT-011 (V015): applies dbo.v_AuditEvent_Local + dbo.v_SecurityEvent_Local views
    /// idempotently to the test database. Mirrors V015__create_local_timezone_views.sql.
    /// Views expose OccurredAtLocal (DateTimeOffset, offset -03:00 Argentina Standard Time).
    /// Note: CREATE VIEW cannot be inside IF...BEGIN...END directly — uses EXEC('CREATE VIEW ...').
    /// </summary>
    private async Task EnsureV015SchemaAsync()
    {
        const string createAuditEventLocal = """
            IF OBJECT_ID('dbo.v_AuditEvent_Local', 'V') IS NULL
            BEGIN
                EXEC('
                    CREATE VIEW dbo.v_AuditEvent_Local AS
                    SELECT
                        Id,
                        OccurredAt,
                        OccurredAt AT TIME ZONE ''UTC'' AT TIME ZONE ''Argentina Standard Time'' AS OccurredAtLocal,
                        ActorUserId,
                        ActorRoleId,
                        Action,
                        TargetType,
                        TargetId,
                        CorrelationId,
                        IpAddress,
                        UserAgent,
                        Metadata
                    FROM dbo.AuditEvent;
                ');
            END
            """;

        const string createSecurityEventLocal = """
            IF OBJECT_ID('dbo.v_SecurityEvent_Local', 'V') IS NULL
            BEGIN
                EXEC('
                    CREATE VIEW dbo.v_SecurityEvent_Local AS
                    SELECT
                        Id,
                        OccurredAt,
                        OccurredAt AT TIME ZONE ''UTC'' AT TIME ZONE ''Argentina Standard Time'' AS OccurredAtLocal,
                        ActorUserId,
                        AttemptedUsername,
                        SessionId,
                        Action,
                        Result,
                        FailureReason,
                        IpAddress,
                        UserAgent,
                        Metadata
                    FROM dbo.SecurityEvent;
                ');
            END
            """;

        await _connection.ExecuteAsync(createAuditEventLocal);
        await _connection.ExecuteAsync(createSecurityEventLocal);
    }

    /// <summary>
    /// CAT-001 (V016): applies dbo.Rubro schema + temporal + filtered unique index + covering index
    /// idempotentemente. Mirrors V016__create_rubro.sql.
    /// Nota: COLLATE debe ir ANTES de NOT NULL — parser de SQL Server 2019 es estricto con ese orden.
    /// Permiso 'catalogo:rubros:gestionar' y asignación a admin se siembran
    /// desde SeedPermisosCanonicalAsync / SeedRolPermisosCanonicalAsync (post-respawn).
    /// </summary>
    private async Task EnsureV016SchemaAsync()
    {
        const string createRubro = """
            IF OBJECT_ID(N'dbo.Rubro', N'U') IS NULL
            BEGIN
                CREATE TABLE dbo.Rubro (
                    Id                INT IDENTITY(1,1)  NOT NULL CONSTRAINT PK_Rubro PRIMARY KEY,
                    ParentId          INT                NULL,
                    Nombre            NVARCHAR(200)      COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
                    Orden             INT                NOT NULL CONSTRAINT DF_Rubro_Orden            DEFAULT(0),
                    Activo            BIT                NOT NULL CONSTRAINT DF_Rubro_Activo           DEFAULT(1),
                    TarifarioBaseId   INT                NULL,
                    FechaCreacion     DATETIME2(3)       NOT NULL CONSTRAINT DF_Rubro_FechaCreacion    DEFAULT(SYSUTCDATETIME()),
                    FechaModificacion DATETIME2(3)       NULL,
                    CONSTRAINT FK_Rubro_Parent FOREIGN KEY (ParentId) REFERENCES dbo.Rubro(Id) ON DELETE NO ACTION
                );
            END
            """;

        const string addRubroPeriod = """
            IF COL_LENGTH('dbo.Rubro', 'ValidFrom') IS NULL
            BEGIN
                ALTER TABLE dbo.Rubro
                ADD
                    ValidFrom DATETIME2(3) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
                        CONSTRAINT DF_Rubro_ValidFrom DEFAULT(SYSUTCDATETIME()),
                    ValidTo   DATETIME2(3) GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL
                        CONSTRAINT DF_Rubro_ValidTo   DEFAULT(CONVERT(DATETIME2(3), '9999-12-31 23:59:59.999')),
                    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
            END
            """;

        const string setRubroVersioning = """
            IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('dbo.Rubro') AND temporal_type = 2)
            BEGIN
                ALTER TABLE dbo.Rubro
                SET (SYSTEM_VERSIONING = ON (
                    HISTORY_TABLE = dbo.Rubro_History,
                    HISTORY_RETENTION_PERIOD = 10 YEARS
                ));
            END
            """;

        const string createUqIndex = """
            IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'UQ_Rubro_ParentId_Nombre_Activo' AND object_id = OBJECT_ID('dbo.Rubro'))
            BEGIN
                CREATE UNIQUE INDEX UQ_Rubro_ParentId_Nombre_Activo
                    ON dbo.Rubro(ParentId, Nombre)
                    WHERE Activo = 1 AND ParentId IS NOT NULL;
            END
            """;

        const string createCoveringIndex = """
            IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_Rubro_ParentId_Activo' AND object_id = OBJECT_ID('dbo.Rubro'))
            BEGIN
                CREATE INDEX IX_Rubro_ParentId_Activo
                    ON dbo.Rubro(ParentId, Activo)
                    INCLUDE (Nombre, Orden);
            END
            """;

        await _connection.ExecuteAsync(createRubro);
        await _connection.ExecuteAsync(addRubroPeriod);
        await _connection.ExecuteAsync(setRubroVersioning);
        await _connection.ExecuteAsync(createUqIndex);
        await _connection.ExecuteAsync(createCoveringIndex);
    }

    private async Task EnsureV017SchemaAsync()
    {
        const string createProductType = """
            IF OBJECT_ID(N'dbo.ProductType', N'U') IS NULL
            BEGIN
                CREATE TABLE dbo.ProductType (
                    Id                INT IDENTITY(1,1)     NOT NULL CONSTRAINT PK_ProductType PRIMARY KEY,
                    Nombre            NVARCHAR(200)         COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
                    HasDuration       BIT                   NOT NULL CONSTRAINT DF_ProductType_HasDuration       DEFAULT(0),
                    RequiresText      BIT                   NOT NULL CONSTRAINT DF_ProductType_RequiresText      DEFAULT(0),
                    RequiresCategory  BIT                   NOT NULL CONSTRAINT DF_ProductType_RequiresCategory  DEFAULT(0),
                    IsBundle          BIT                   NOT NULL CONSTRAINT DF_ProductType_IsBundle          DEFAULT(0),
                    AllowImages       BIT                   NOT NULL CONSTRAINT DF_ProductType_AllowImages       DEFAULT(0),
                    MaxImages         INT                   NULL,
                    MaxImageSizeMB    DECIMAL(10,2)         NULL,
                    MaxImageWidth     INT                   NULL,
                    MaxImageHeight    INT                   NULL,
                    IsActive          BIT                   NOT NULL CONSTRAINT DF_ProductType_IsActive          DEFAULT(1),
                    FechaCreacion     DATETIME2(3)          NOT NULL CONSTRAINT DF_ProductType_FechaCreacion     DEFAULT(SYSUTCDATETIME()),
                    FechaModificacion DATETIME2(3)          NULL
                );
            END
            """;

        const string addProductTypePeriod = """
            IF COL_LENGTH('dbo.ProductType', 'ValidFrom') IS NULL
            BEGIN
                ALTER TABLE dbo.ProductType
                ADD
                    ValidFrom DATETIME2(3) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
                        CONSTRAINT DF_ProductType_ValidFrom DEFAULT(SYSUTCDATETIME()),
                    ValidTo   DATETIME2(3) GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL
                        CONSTRAINT DF_ProductType_ValidTo   DEFAULT(CONVERT(DATETIME2(3), '9999-12-31 23:59:59.999')),
                    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
            END
            """;

        const string setProductTypeVersioning = """
            IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('dbo.ProductType') AND temporal_type = 2)
            BEGIN
                ALTER TABLE dbo.ProductType
                SET (SYSTEM_VERSIONING = ON (
                    HISTORY_TABLE = dbo.ProductType_History,
                    HISTORY_RETENTION_PERIOD = 10 YEARS
                ));
            END
            """;

        const string createUqIndex = """
            IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'UQ_ProductType_Nombre_Activo' AND object_id = OBJECT_ID('dbo.ProductType'))
            BEGIN
                CREATE UNIQUE INDEX UQ_ProductType_Nombre_Activo
                    ON dbo.ProductType(Nombre)
                    WHERE IsActive = 1;
            END
            """;

        const string createCoveringIndex = """
            IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ProductType_IsActive_Cover' AND object_id = OBJECT_ID('dbo.ProductType'))
            BEGIN
                CREATE INDEX IX_ProductType_IsActive_Cover
                    ON dbo.ProductType(IsActive)
                    INCLUDE (Nombre, HasDuration, RequiresText, RequiresCategory, IsBundle, AllowImages);
            END
            """;

        await _connection.ExecuteAsync(createProductType);
        await _connection.ExecuteAsync(addProductTypePeriod);
        await _connection.ExecuteAsync(setProductTypeVersioning);
        await _connection.ExecuteAsync(createUqIndex);
        await _connection.ExecuteAsync(createCoveringIndex);
    }
}
