-- ============================================================
-- Story #55 - Sales Document Coverage incremental update
-- ============================================================
-- Objetivo:
--   Ajustar el modelo de documentos de venta ya creado para soportar
--   facturacion por periodo/capita y trazabilidad de presupuestos cubiertos.
--
-- Contexto:
--   El scaffold actualizado ya incluye:
--     - PhS_SalesDocuments
--     - PhS_SalesDocumentDetails
--     - PhS_SalesFiscalDocuments
--     - PhS_SalesFiscalDocumentAssociations
--
-- Decisiones:
--   - No se recrean tablas existentes.
--   - No se modifican modelos EF manualmente.
--   - Se agrega periodo a la cabecera comercial.
--   - Se agrega PhS_SalesDocumentCoverage como verdad de cobertura.
--   - Coverage se usa tanto para facturacion 1 a 1 como para capita.
--   - quote_id en PhS_SalesDocuments queda como referencia principal/rapida,
--     no como verdad completa de facturacion.
-- ============================================================

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    -- ============================================================
    -- PhS_SalesDocuments: periodo comercial opcional
    -- ============================================================

    IF COL_LENGTH('dbo.PhS_SalesDocuments', 'period_from') IS NULL
    BEGIN
        ALTER TABLE dbo.PhS_SalesDocuments
            ADD period_from DATETIME NULL;
    END;

    IF COL_LENGTH('dbo.PhS_SalesDocuments', 'period_to') IS NULL
    BEGIN
        ALTER TABLE dbo.PhS_SalesDocuments
            ADD period_to DATETIME NULL;
    END;

    IF OBJECT_ID('dbo.CK_PhS_SalesDocuments_Period', 'C') IS NULL
    BEGIN
        EXEC sys.sp_executesql N'
            ALTER TABLE dbo.PhS_SalesDocuments
                ADD CONSTRAINT CK_PhS_SalesDocuments_Period
                CHECK (period_from IS NULL OR period_to IS NULL OR period_to >= period_from);
        ';
    END;

    -- ============================================================
    -- PhS_SalesDocumentCoverage
    -- ============================================================
    -- Representa que presupuestos/casos quedan cubiertos por un documento
    -- de venta. Es la fuente real para determinar si un presupuesto queda
    -- pendiente de facturacion o ya fue cubierto/facturado.
    -- ============================================================

    IF OBJECT_ID('dbo.PhS_SalesDocumentCoverage', 'U') IS NULL
    BEGIN
        CREATE TABLE dbo.PhS_SalesDocumentCoverage
        (
            id INT IDENTITY(1,1) NOT NULL,

            salesdocument_id INT NOT NULL,
            salesdocumentdetail_id INT NULL,

            quote_id INT NOT NULL,
            quote_detail_id INT NULL,

            -- Tipo de cobertura.
            -- Valores esperados en Domain:
            -- 1 = Direct
            -- 2 = Capita
            -- 3 = Adjustment
            coverage_type INT NOT NULL CONSTRAINT DF_PhS_SalesDocumentCoverage_coverage_type DEFAULT (1),

            -- Porcentaje/importe cubierto respecto del presupuesto/caso.
            -- En facturacion 1 a 1 normalmente sera 100.
            -- En obra social / particular puede ser 60/40.
            -- En capita puede ser 100 aunque la linea facturada sea agregada.
            coverage_percentage DECIMAL(9,4) NULL,
            coverage_amount DECIMAL(18,2) NULL,

            period_from DATETIME NULL,
            period_to DATETIME NULL,

            notes NVARCHAR(MAX) NULL,

            createdat DATETIME NOT NULL CONSTRAINT DF_PhS_SalesDocumentCoverage_createdat DEFAULT (GETDATE()),
            modifiedat DATETIME NULL,

            CONSTRAINT PK_PhS_SalesDocumentCoverage PRIMARY KEY (id),

            CONSTRAINT FK_PhS_SalesDocumentCoverage_SalesDocuments
                FOREIGN KEY (salesdocument_id)
                REFERENCES dbo.PhS_SalesDocuments(id),

            CONSTRAINT FK_PhS_SalesDocumentCoverage_SalesDocumentDetails
                FOREIGN KEY (salesdocumentdetail_id)
                REFERENCES dbo.PhS_SalesDocumentDetails(id),

            CONSTRAINT FK_PhS_SalesDocumentCoverage_QuoteHeaders
                FOREIGN KEY (quote_id)
                REFERENCES dbo.PhS_QuoteHeaders(id),

            CONSTRAINT FK_PhS_SalesDocumentCoverage_QuoteDetails
                FOREIGN KEY (quote_detail_id)
                REFERENCES dbo.PhS_QuoteDetails(id),

            CONSTRAINT CK_PhS_SalesDocumentCoverage_Period
                CHECK (period_from IS NULL OR period_to IS NULL OR period_to >= period_from),

            CONSTRAINT CK_PhS_SalesDocumentCoverage_Percentage
                CHECK (coverage_percentage IS NULL OR (coverage_percentage > 0 AND coverage_percentage <= 100))
        );
    END;

    -- ============================================================
    -- Indices
    -- ============================================================

    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_PhS_SalesDocumentCoverage_Document' AND object_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'))
    BEGIN
        CREATE INDEX IX_PhS_SalesDocumentCoverage_Document
            ON dbo.PhS_SalesDocumentCoverage(salesdocument_id);
    END;

    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_PhS_SalesDocumentCoverage_DocumentDetail' AND object_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'))
    BEGIN
        CREATE INDEX IX_PhS_SalesDocumentCoverage_DocumentDetail
            ON dbo.PhS_SalesDocumentCoverage(salesdocumentdetail_id);
    END;

    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_PhS_SalesDocumentCoverage_Quote' AND object_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'))
    BEGIN
        CREATE INDEX IX_PhS_SalesDocumentCoverage_Quote
            ON dbo.PhS_SalesDocumentCoverage(quote_id);
    END;

    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_PhS_SalesDocumentCoverage_QuoteDetail' AND object_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'))
    BEGIN
        CREATE INDEX IX_PhS_SalesDocumentCoverage_QuoteDetail
            ON dbo.PhS_SalesDocumentCoverage(quote_detail_id);
    END;

    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_PhS_SalesDocumentCoverage_Period' AND object_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'))
    BEGIN
        CREATE INDEX IX_PhS_SalesDocumentCoverage_Period
            ON dbo.PhS_SalesDocumentCoverage(period_from, period_to);
    END;

    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_PhS_SalesDocuments_Period' AND object_id = OBJECT_ID(N'dbo.PhS_SalesDocuments'))
    BEGIN
        EXEC sys.sp_executesql N'
            CREATE INDEX IX_PhS_SalesDocuments_Period
                ON dbo.PhS_SalesDocuments(period_from, period_to);
        ';
    END;

    -- ============================================================
    -- Extended properties / MS_Description
    -- ============================================================

    IF NOT EXISTS (
        SELECT 1
        FROM sys.extended_properties
        WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocuments')
          AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocuments'), N'period_from', 'ColumnId')
          AND name = N'MS_Description'
    )
    BEGIN
        EXEC sys.sp_addextendedproperty
            @name = N'MS_Description',
            @value = N'Fecha inicial del periodo comercial facturado. Aplica especialmente a facturacion por capita o periodos mensuales.',
            @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PhS_SalesDocuments',
            @level2type = N'COLUMN', @level2name = N'period_from';
    END;

    IF NOT EXISTS (
        SELECT 1
        FROM sys.extended_properties
        WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocuments')
          AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocuments'), N'period_to', 'ColumnId')
          AND name = N'MS_Description'
    )
    BEGIN
        EXEC sys.sp_addextendedproperty
            @name = N'MS_Description',
            @value = N'Fecha final del periodo comercial facturado. Aplica especialmente a facturacion por capita o periodos mensuales.',
            @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PhS_SalesDocuments',
            @level2type = N'COLUMN', @level2name = N'period_to';
    END;

    IF NOT EXISTS (
        SELECT 1
        FROM sys.extended_properties
        WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage')
          AND minor_id = 0
          AND name = N'MS_Description'
    )
    BEGIN
        EXEC sys.sp_addextendedproperty
            @name = N'MS_Description',
            @value = N'Presupuestos/casos cubiertos por un documento de venta. Es la fuente real para determinar si un presupuesto queda pendiente de facturacion o ya fue cubierto, incluyendo facturacion 1 a 1 y capita.',
            @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PhS_SalesDocumentCoverage';
    END;

    IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'id', 'ColumnId') AND name = N'MS_Description')
    BEGIN
        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador interno de la cobertura.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'id';
    END;

    IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'salesdocument_id', 'ColumnId') AND name = N'MS_Description')
    BEGIN
        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Documento de venta que cubre el presupuesto/caso.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'salesdocument_id';
    END;

    IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'salesdocumentdetail_id', 'ColumnId') AND name = N'MS_Description')
    BEGIN
        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Detalle del documento de venta asociado a esta cobertura, cuando aplique. En capita puede apuntar a la linea agregada mensual.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'salesdocumentdetail_id';
    END;

    IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'quote_id', 'ColumnId') AND name = N'MS_Description')
    BEGIN
        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Presupuesto/caso cubierto por el documento de venta. Se usa tanto para facturacion directa como para capita.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'quote_id';
    END;

    IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'quote_detail_id', 'ColumnId') AND name = N'MS_Description')
    BEGIN
        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Detalle de presupuesto cubierto, cuando se requiera trazabilidad granular por item.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'quote_detail_id';
    END;

    IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'coverage_type', 'ColumnId') AND name = N'MS_Description')
    BEGIN
        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tipo de cobertura. Valores esperados en Domain: Direct, Capita, Adjustment.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'coverage_type';
    END;

    IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'coverage_percentage', 'ColumnId') AND name = N'MS_Description')
    BEGIN
        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Porcentaje del presupuesto/caso cubierto por el documento. Permite 100% en facturacion directa o particiones 60/40.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'coverage_percentage';
    END;

    IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'coverage_amount', 'ColumnId') AND name = N'MS_Description')
    BEGIN
        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Importe de referencia cubierto por el documento, cuando aplique.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'coverage_amount';
    END;

    IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'period_from', 'ColumnId') AND name = N'MS_Description')
    BEGIN
        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Fecha inicial del periodo de cobertura.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'period_from';
    END;

    IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'period_to', 'ColumnId') AND name = N'MS_Description')
    BEGIN
        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Fecha final del periodo de cobertura.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'period_to';
    END;

    IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'notes', 'ColumnId') AND name = N'MS_Description')
    BEGIN
        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Notas internas de cobertura.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'notes';
    END;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;

