-- =====================================================================
-- Enhancement: Compressed Work Hours Per Week - Approval Settings
-- Table : TA_Tbl_ApprovalSettings
-- Date  : 2026-03-13
-- =====================================================================

-- STEP 1: Add new columns to TA_Tbl_ApprovalSettings
-- =====================================================================
IF NOT EXISTS (
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'TA_Tbl_ApprovalSettings'
      AND COLUMN_NAME = 'CompressedWorkHours'
)
BEGIN
    ALTER TABLE [dbo].[TA_Tbl_ApprovalSettings]
    ADD [CompressedWorkHours] BIT NOT NULL DEFAULT (0)
END
GO

IF NOT EXISTS (
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'TA_Tbl_ApprovalSettings'
      AND COLUMN_NAME = 'MinWeeklyCompressedHours'
)
BEGIN
    ALTER TABLE [dbo].[TA_Tbl_ApprovalSettings]
    ADD [MinWeeklyCompressedHours] DECIMAL(5, 2) NOT NULL DEFAULT (0)
END
GO

-- =====================================================================
-- STEP 2: Update Proc_TA_SelectApprovalSettings
--         to return the two new columns
-- =====================================================================
IF EXISTS (
    SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[Proc_TA_SelectApprovalSettings]')
      AND type IN (N'P', N'PC')
)
    DROP PROCEDURE [dbo].[Proc_TA_SelectApprovalSettings]
GO

CREATE PROCEDURE [dbo].[Proc_TA_SelectApprovalSettings]
    @CompanyId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        [CompanyId],
        [Pattern],
        [Roster],
        [OTApproval],
        [LeaveSanction],
        [PermissionSanction],
        [ManualEntry],
        [ODEntry],
        [Approval],
        [webClockInOut],
        [CompressedWorkHours],
        [MinWeeklyCompressedHours]
    FROM [dbo].[TA_Tbl_ApprovalSettings]
    WHERE [CompanyId] = @CompanyId
END
GO

-- =====================================================================
-- STEP 3: Update Proc_TA_InsertUpdateApprovalSettings
--         to accept and save the two new parameters
-- =====================================================================
IF EXISTS (
    SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[Proc_TA_InsertUpdateApprovalSettings]')
      AND type IN (N'P', N'PC')
)
    DROP PROCEDURE [dbo].[Proc_TA_InsertUpdateApprovalSettings]
GO

CREATE PROCEDURE [dbo].[Proc_TA_InsertUpdateApprovalSettings]
    @CompanyId              INT,
    @Pattern                INT,
    @Roster                 INT,
    @OTApproval             INT,
    @LeaveSanction          INT,
    @PermissionSanction     INT,
    @ManualEntry            INT,
    @OnDutyEntry            INT,
    @Approval               INT,
    @Emp_Id                 INT,
    @webClockInOut          BIT,
    @CompressedWorkHours    INT,
    @MinWeeklyCompressedHours DECIMAL(5,2)
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM [dbo].[TA_Tbl_ApprovalSettings] WHERE [CompanyId] = @CompanyId)
    BEGIN
        UPDATE [dbo].[TA_Tbl_ApprovalSettings]
        SET
            [Pattern]                  = @Pattern,
            [Roster]                   = @Roster,
            [OTApproval]               = @OTApproval,
            [LeaveSanction]            = @LeaveSanction,
            [PermissionSanction]       = @PermissionSanction,
            [ManualEntry]              = @ManualEntry,
            [ODEntry]                  = @OnDutyEntry,
            [Approval]                 = @Approval,
            [Emp_Id]                   = @Emp_Id,
            [webClockInOut]            = @webClockInOut,
            [CompressedWorkHours]      = @CompressedWorkHours,
            [MinWeeklyCompressedHours] = @MinWeeklyCompressedHours
        WHERE [CompanyId] = @CompanyId
    END
    ELSE
    BEGIN
        INSERT INTO [dbo].[TA_Tbl_ApprovalSettings]
        (
            [CompanyId], [Pattern], [Roster], [OTApproval], [LeaveSanction],
            [PermissionSanction], [ManualEntry], [ODEntry], [Approval],
            [Emp_Id], [webClockInOut], [CompressedWorkHours], [MinWeeklyCompressedHours]
        )
        VALUES
        (
            @CompanyId, @Pattern, @Roster, @OTApproval, @LeaveSanction,
            @PermissionSanction, @ManualEntry, @OnDutyEntry, @Approval,
            @Emp_Id, @webClockInOut, @CompressedWorkHours, @MinWeeklyCompressedHours
        )
    END
END
GO
