-- =============================================================================
-- WI.TLINEPRODUCTIONPLANALLOCATION — new table + supporting fixes
-- Migration: 20260818
--
-- Bridges the real MM production plan (TPRODUCTIONPLANDETAIL: Item + Quantity +
-- coarse date range, no Line/Shift/Workstation/time-window dimension at all —
-- confirmed live) down to a specific line/shift/date, optionally a specific
-- workstation and intra-shift time window. Many rows per (Line,Date,Shift) is
-- normal — that's how multi-product/partial-shift allocation is expressed.
--
-- Also fixes a pre-existing gap found while building this: MPRODUCTIONLINE's
-- own bring-up script defines a -1 "NONE" sentinel row but it was never applied
-- on this environment (table confirmed live at 0 rows) — TLINESHIFTROSTER's own
-- sentinel insert has silently been failing for the same reason. Seeding it here
-- since this migration's own sentinel row depends on it.
--
-- Also seeds the MAUTONUMBER row for the new "LINEPRODUCTIONPLANALLOCATION"
-- entity code (confirmed free live: no MAUTONUMBER row with this ENTITYCODE,
-- and ENTITYID -1389999997 confirmed free, adjacent to the existing WI cluster).
-- =============================================================================

IF NOT EXISTS (SELECT 1 FROM DBO.MPRODUCTIONLINE WHERE LINEID = -1)
BEGIN
    INSERT INTO DBO.MPRODUCTIONLINE
        (LINEID, LINECODE, LINENAME, DESCRIPTION, VERSION, STATUS, SORTORDER,
         CREATEDBYID, MODIFIEDBYID, SOURCETYPE, TENANTID)
    VALUES
        (-1, 'NONE', 'NONE', 'NONE', 0, 1, 9999, -1, -1, 5, -1)
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id
               WHERE s.name = 'WI' AND t.name = 'TLINEPRODUCTIONPLANALLOCATION')
BEGIN
    CREATE TABLE WI.TLINEPRODUCTIONPLANALLOCATION
    (
        LINEALLOCATIONID        INT             NOT NULL,
        PRODUCTIONPLANDETAILID  INT             NOT NULL,
        PRODUCTIONLINEID        INT             NOT NULL,
        SHIFTID                 INT             NOT NULL,
        ALLOCATIONDATE          DATE            NOT NULL,
        WORKSTATIONID           INT             CONSTRAINT DF_TLINEPRODUCTIONPLANALLOCATION_WORKSTATIONID DEFAULT NULL,
        STARTMINUTES            SMALLINT        CONSTRAINT DF_TLINEPRODUCTIONPLANALLOCATION_STARTMINUTES  DEFAULT NULL,
        ENDMINUTES              SMALLINT        CONSTRAINT DF_TLINEPRODUCTIONPLANALLOCATION_ENDMINUTES    DEFAULT NULL,
        ALLOCATEDQUANTITY       INT             CONSTRAINT DF_TLINEPRODUCTIONPLANALLOCATION_ALLOCATEDQTY  DEFAULT NULL,
        REMARKS                 NVARCHAR(1000),

        STATUS                  TINYINT         NOT NULL  CONSTRAINT DF_TLINEPRODUCTIONPLANALLOCATION_STATUS     DEFAULT 1,
        CREATEDBYID             INT             NOT NULL,
        CREATEDON               DATETIME        NOT NULL  CONSTRAINT DF_TLINEPRODUCTIONPLANALLOCATION_CREATEDON  DEFAULT GETDATE(),
        MODIFIEDBYID            INT             NOT NULL,
        MODIFIEDON              DATETIME        NOT NULL  CONSTRAINT DF_TLINEPRODUCTIONPLANALLOCATION_MODIFIEDON DEFAULT GETDATE(),
        SOURCETYPE              TINYINT         NOT NULL  CONSTRAINT DF_TLINEPRODUCTIONPLANALLOCATION_SOURCETYPE DEFAULT 5,
        TENANTID                INT             NOT NULL  CONSTRAINT DF_TLINEPRODUCTIONPLANALLOCATION_TENANTID   DEFAULT -1,

        CONSTRAINT PK_TLINEPRODUCTIONPLANALLOCATION_LINEALLOCATIONID       PRIMARY KEY (LINEALLOCATIONID),
        CONSTRAINT FK_TLINEPRODUCTIONPLANALLOCATION_PRODUCTIONPLANDETAILID FOREIGN KEY (PRODUCTIONPLANDETAILID) REFERENCES DBO.TPRODUCTIONPLANDETAIL(PRODUCTIONPLANDETAILID),
        CONSTRAINT FK_TLINEPRODUCTIONPLANALLOCATION_PRODUCTIONLINEID       FOREIGN KEY (PRODUCTIONLINEID)        REFERENCES DBO.MPRODUCTIONLINE(LINEID),
        CONSTRAINT FK_TLINEPRODUCTIONPLANALLOCATION_SHIFTID                FOREIGN KEY (SHIFTID)                 REFERENCES DBO.MSHIFT(SHIFTID),
        CONSTRAINT FK_TLINEPRODUCTIONPLANALLOCATION_WORKSTATIONID          FOREIGN KEY (WORKSTATIONID)           REFERENCES DBO.MWORKSTATION(WORKSTATIONID),
        CONSTRAINT FK_TLINEPRODUCTIONPLANALLOCATION_CREATEDBYID            FOREIGN KEY (CREATEDBYID)             REFERENCES MUSER(USERID),
        CONSTRAINT FK_TLINEPRODUCTIONPLANALLOCATION_MODIFIEDBYID           FOREIGN KEY (MODIFIEDBYID)            REFERENCES MUSER(USERID),
        CONSTRAINT FK_TLINEPRODUCTIONPLANALLOCATION_TENANTID               FOREIGN KEY (TENANTID)                REFERENCES MCLIENT(CLIENTID),
        CONSTRAINT CK_TLINEPRODUCTIONPLANALLOCATION_STATUS                 CHECK (STATUS     IN (0,1,2,3,4,5)),
        CONSTRAINT CK_TLINEPRODUCTIONPLANALLOCATION_SOURCETYPE             CHECK (SOURCETYPE IN (1,2,3,4,5))
    )

    CREATE NONCLUSTERED INDEX IX_TLINEPRODUCTIONPLANALLOCATION_LINE_DATE_SHIFT
        ON WI.TLINEPRODUCTIONPLANALLOCATION (PRODUCTIONLINEID, ALLOCATIONDATE, SHIFTID, TENANTID)
        INCLUDE (PRODUCTIONPLANDETAILID, WORKSTATIONID, STARTMINUTES, ENDMINUTES)

    CREATE NONCLUSTERED INDEX IX_TLINEPRODUCTIONPLANALLOCATION_PLANDETAIL
        ON WI.TLINEPRODUCTIONPLANALLOCATION (PRODUCTIONPLANDETAILID)

    INSERT INTO WI.TLINEPRODUCTIONPLANALLOCATION
        (LINEALLOCATIONID, PRODUCTIONPLANDETAILID, PRODUCTIONLINEID, SHIFTID, ALLOCATIONDATE,
         WORKSTATIONID, STARTMINUTES, ENDMINUTES, ALLOCATEDQUANTITY, REMARKS,
         STATUS, CREATEDBYID, MODIFIEDBYID, SOURCETYPE, TENANTID)
    VALUES
        (-1, -1, -1, -1, '2026-01-01', NULL, NULL, NULL, NULL, 'NONE', 1, -1, -1, 5, -1)
END
GO

IF NOT EXISTS (SELECT 1 FROM MAUTONUMBER WHERE ENTITYCODE = 'LINEPRODUCTIONPLANALLOCATION')
BEGIN
    INSERT INTO MAUTONUMBER (ENTITYID, ENTITYCODE, AUTOID)
    VALUES (-1389999997, 'LINEPRODUCTIONPLANALLOCATION', -1500000000);
END
GO