bmc_hub/migrations/194_time_entry_billing_extensions.sql

-- Migration 194: Time entry billing extensions
-- Adds per-entry controls for extra billed time and extended support pricing.

ALTER TABLE tticket_worklog
    ADD COLUMN IF NOT EXISTS extra_billed_hours DECIMAL(6,2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS extended_support_flag BOOLEAN DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS rate_multiplier DECIMAL(6,3) DEFAULT 1.000,
    ADD COLUMN IF NOT EXISTS manual_hourly_rate DECIMAL(10,2),
    ADD COLUMN IF NOT EXISTS manual_rounded_hours DECIMAL(6,2),
    ADD COLUMN IF NOT EXISTS rounding_override_reason TEXT;

ALTER TABLE tmodule_times
    ADD COLUMN IF NOT EXISTS extra_billed_hours DECIMAL(6,2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS extended_support_flag BOOLEAN DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS rate_multiplier DECIMAL(6,3) DEFAULT 1.000,
    ADD COLUMN IF NOT EXISTS manual_hourly_rate DECIMAL(10,2),
    ADD COLUMN IF NOT EXISTS manual_rounded_hours DECIMAL(6,2),
    ADD COLUMN IF NOT EXISTS rounding_override_reason TEXT;

UPDATE tticket_worklog
SET
    extra_billed_hours = COALESCE(extra_billed_hours, 0),
    extended_support_flag = COALESCE(extended_support_flag, FALSE),
    rate_multiplier = COALESCE(rate_multiplier, 1.000);

UPDATE tmodule_times
SET
    extra_billed_hours = COALESCE(extra_billed_hours, 0),
    extended_support_flag = COALESCE(extended_support_flag, FALSE),
    rate_multiplier = COALESCE(rate_multiplier, 1.000);

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'tticket_worklog_extra_billed_hours_nonnegative'
          AND conrelid = 'tticket_worklog'::regclass
    ) THEN
        ALTER TABLE tticket_worklog
            ADD CONSTRAINT tticket_worklog_extra_billed_hours_nonnegative
            CHECK (extra_billed_hours IS NULL OR extra_billed_hours >= 0);
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'tticket_worklog_rate_multiplier_positive'
          AND conrelid = 'tticket_worklog'::regclass
    ) THEN
        ALTER TABLE tticket_worklog
            ADD CONSTRAINT tticket_worklog_rate_multiplier_positive
            CHECK (rate_multiplier IS NULL OR rate_multiplier > 0);
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'tticket_worklog_manual_hourly_rate_positive'
          AND conrelid = 'tticket_worklog'::regclass
    ) THEN
        ALTER TABLE tticket_worklog
            ADD CONSTRAINT tticket_worklog_manual_hourly_rate_positive
            CHECK (manual_hourly_rate IS NULL OR manual_hourly_rate > 0);
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'tticket_worklog_manual_rounded_hours_positive'
          AND conrelid = 'tticket_worklog'::regclass
    ) THEN
        ALTER TABLE tticket_worklog
            ADD CONSTRAINT tticket_worklog_manual_rounded_hours_positive
            CHECK (manual_rounded_hours IS NULL OR manual_rounded_hours > 0);
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'tmodule_times_extra_billed_hours_nonnegative'
          AND conrelid = 'tmodule_times'::regclass
    ) THEN
        ALTER TABLE tmodule_times
            ADD CONSTRAINT tmodule_times_extra_billed_hours_nonnegative
            CHECK (extra_billed_hours IS NULL OR extra_billed_hours >= 0);
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'tmodule_times_rate_multiplier_positive'
          AND conrelid = 'tmodule_times'::regclass
    ) THEN
        ALTER TABLE tmodule_times
            ADD CONSTRAINT tmodule_times_rate_multiplier_positive
            CHECK (rate_multiplier IS NULL OR rate_multiplier > 0);
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'tmodule_times_manual_hourly_rate_positive'
          AND conrelid = 'tmodule_times'::regclass
    ) THEN
        ALTER TABLE tmodule_times
            ADD CONSTRAINT tmodule_times_manual_hourly_rate_positive
            CHECK (manual_hourly_rate IS NULL OR manual_hourly_rate > 0);
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'tmodule_times_manual_rounded_hours_positive'
          AND conrelid = 'tmodule_times'::regclass
    ) THEN
        ALTER TABLE tmodule_times
            ADD CONSTRAINT tmodule_times_manual_rounded_hours_positive
            CHECK (manual_rounded_hours IS NULL OR manual_rounded_hours > 0);
    END IF;
END $$;