-- 创建 ConsumptionReports 表
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConsumptionReports]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[ConsumptionReports](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [AllocationDistributionId] [int] NOT NULL,
        [ReportedAmount] [decimal](18, 2) NOT NULL,
        [CumulativeAmount] [decimal](18, 2) NOT NULL,
        [Remarks] [nvarchar](500) NULL,
        [ReportedByUserId] [int] NOT NULL,
        [ReportedAt] [datetime2](7) NOT NULL,
        CONSTRAINT [PK_ConsumptionReports] PRIMARY KEY CLUSTERED ([Id] ASC)
    )

    CREATE NONCLUSTERED INDEX [IX_ConsumptionReports_AllocationDistributionId] ON [dbo].[ConsumptionReports]
    (
        [AllocationDistributionId] ASC
    )

    CREATE NONCLUSTERED INDEX [IX_ConsumptionReports_ReportedAt] ON [dbo].[ConsumptionReports]
    (
        [ReportedAt] ASC
    )

    CREATE NONCLUSTERED INDEX [IX_ConsumptionReports_ReportedByUserId] ON [dbo].[ConsumptionReports]
    (
        [ReportedByUserId] ASC
    )

    ALTER TABLE [dbo].[ConsumptionReports]  WITH CHECK ADD  CONSTRAINT [FK_ConsumptionReports_AllocationDistributions_AllocationDistributionId] FOREIGN KEY([AllocationDistributionId])
    REFERENCES [dbo].[AllocationDistributions] ([Id])
    ON DELETE CASCADE

    ALTER TABLE [dbo].[ConsumptionReports] CHECK CONSTRAINT [FK_ConsumptionReports_AllocationDistributions_AllocationDistributionId]

    ALTER TABLE [dbo].[ConsumptionReports]  WITH CHECK ADD  CONSTRAINT [FK_ConsumptionReports_UserAccounts_ReportedByUserId] FOREIGN KEY([ReportedByUserId])
    REFERENCES [dbo].[UserAccounts] ([Id])

    ALTER TABLE [dbo].[ConsumptionReports] CHECK CONSTRAINT [FK_ConsumptionReports_UserAccounts_ReportedByUserId]

    PRINT 'ConsumptionReports table created successfully'
END
ELSE
BEGIN
    PRINT 'ConsumptionReports table already exists'
END
GO
