
--Migration_RCCP_20251112_164908.txt
CREATE TABLE [dbo].[OrderItems] (
    [Id] [bigint] NOT NULL IDENTITY,
    [Type] [nvarchar](30),
    [Category] [nvarchar](30),
    [ItemCode] [nvarchar](30),
    [ItemName] [nvarchar](50),
    [Details] [nvarchar](max),
    [CreatedUtcDateTime] [datetime] NOT NULL,
    CONSTRAINT [PK_dbo.OrderItems] PRIMARY KEY ([Id])
)
CREATE TABLE [dbo].[OrderSessionItems] (
    [Id] [bigint] NOT NULL IDENTITY,
    [SessionType] [nvarchar](30),
    [OrderSessionId] [bigint] NOT NULL,
    [CreatedDateTime] [datetime] NOT NULL,
    [ItemId] [bigint] NOT NULL,
    [Quantity] [int] NOT NULL,
    [ConfidenceScore] [float],
    CONSTRAINT [PK_dbo.OrderSessionItems] PRIMARY KEY ([Id])
)
CREATE TABLE [dbo].[OrderSessions] (
    [Id] [bigint] NOT NULL IDENTITY,
    [OrderId] [nvarchar](50),
    [OrderNumber] [nvarchar](50),
    [OrderType] [nvarchar](30),
    [DeliveryMethod] [nvarchar](30),
    [CreatedDateTime] [datetime] NOT NULL,
    [StartTime] [datetime],
    [EndTime] [datetime],
    [Status] [nvarchar](30),
    [CompanyId] [bigint] NOT NULL,
    [BranchId] [bigint] NOT NULL,
    [DeviceSerial] [nvarchar](30),
    [ReceiptOCR] [nvarchar](500),
    CONSTRAINT [PK_dbo.OrderSessions] PRIMARY KEY ([Id])
)
CREATE INDEX [IX_OrderSessionId] ON [dbo].[OrderSessionItems]([OrderSessionId])
CREATE INDEX [IX_ItemId] ON [dbo].[OrderSessionItems]([ItemId])
ALTER TABLE [dbo].[OrderSessionItems] ADD CONSTRAINT [FK_dbo.OrderSessionItems_dbo.OrderSessions_OrderSessionId] FOREIGN KEY ([OrderSessionId]) REFERENCES [dbo].[OrderSessions] ([Id]) ON DELETE CASCADE
ALTER TABLE [dbo].[OrderSessionItems] ADD CONSTRAINT [FK_dbo.OrderSessionItems_dbo.OrderItems_ItemId] FOREIGN KEY ([ItemId]) REFERENCES [dbo].[OrderItems] ([Id]) ON DELETE CASCADE
