-- ── Disciplines lookup (Trade / Matrix / Structural / Architectural) ──
CREATE TABLE [dbo].[Disciplines] (
    [DisciplineId]   TINYINT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [DisciplineName] VARCHAR(100) NOT NULL
);

INSERT INTO [dbo].[Disciplines] ([DisciplineName]) VALUES
    ('Trade'),
    ('Matrix'),
    ('Structural'),
    ('Architectural');

-- ── RIS: Return Issuance Slip ─────────────────────────────────────
CREATE TABLE [dbo].[RIS] (
    [RISId]         BIGINT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [RISNo]         VARCHAR(50)  NOT NULL,          -- generated slip number
    [InventoryId]   INT          NOT NULL,
    [PRDetailId]    BIGINT       NOT NULL,
    [ProjectCodeId] INT NOT NULL,           -- UserId receiving items
    [DisciplineId]  TINYINT      NOT NULL,           -- Trade/Matrix/Structural/Architectural
    [QtyIssued]     DECIMAL(14,2)           NOT NULL,
    [Remarks]       VARCHAR(500) NULL,
    [Status]        SMALLINT     NOT NULL DEFAULT 0, -- 0=Draft,1=Approved,2=Cancelled
    [CreatedBy]     VARCHAR(450) NOT NULL,
    [CreatedDate]   DATETIME     NOT NULL DEFAULT GETDATE(),
    [ApprovedBy]    VARCHAR(450) NULL,
    [ApprovedDate]  DATETIME     NULL,
    CanceledBy      VARCHAR(50) NULL,
    CanceledDate    DATETIME NULL,
    Reason          VARCHAR(150) NULL,
    FOREIGN KEY ([InventoryId])  REFERENCES [Inventory]([InventoryId]),
    FOREIGN KEY ([PRDetailId])   REFERENCES [PRDetails]([PRDetailsId]),
    FOREIGN KEY ([DisciplineId]) REFERENCES [Disciplines]([DisciplineId]),
    FOREIGN KEY ([ProjectCodeId]) REFERENCES ProjectCodes([ProjectCodeId])
);

-- ── MRS: Material Return Slip ─────────────────────────────────────
CREATE TABLE [dbo].[MRS] (
    [MRSId]         BIGINT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [MRSNo]         VARCHAR(50)  NOT NULL,
    [RISId]         BIGINT       NOT NULL,           -- must reference an original issuance
    [InventoryId]   INT          NOT NULL,
    [ReturnedBy]    VARCHAR(450) NOT NULL,
    [QtyReturned]   DECIMAL(14,2)          NOT NULL,
    [Condition]     VARCHAR(100) NULL,               -- Good / Damaged / Partial
    [Remarks]       VARCHAR(500) NULL,
    [Status]        SMALLINT     NOT NULL DEFAULT 0,
    [CreatedBy]     VARCHAR(450) NOT NULL,
    [CreatedDate]   DATETIME     NOT NULL DEFAULT GETDATE(),
    [ApprovedBy]    VARCHAR(450) NULL,
    [ApprovedDate]  DATETIME     NULL,
    CanceledBy      VARCHAR(50) NULL,
    CanceledDate    DATETIME NULL,
    Reason          VARCHAR(150) NULL,
    FOREIGN KEY ([RISId])        REFERENCES [RIS]([RISId]),
    FOREIGN KEY ([InventoryId])  REFERENCES [Inventory]([InventoryId])
);

INSERT INTO [dbo].[TransTypes]
(TransTypeName)
VALUES
('RIS'),('MRS')