-- Database Migration Script: Add Contract Rate Effective Date and End Date
-- This script adds two new columns to the pRevenue_Code table to support
-- effective date and end date functionality for contract rates.

-- Add Contract_Rate_Effective_Date column
ALTER TABLE pRevenue_Code 
ADD COLUMN Contract_Rate_Effective_Date DATE NULL 
COMMENT 'Effective date for the contract rate';

-- Add Contract_Rate_End_Date column  
ALTER TABLE pRevenue_Code 
ADD COLUMN Contract_Rate_End_Date DATE NULL 
COMMENT 'End date for the contract rate (NULL means no end date)';

-- Create index for better performance on date-based queries
CREATE INDEX idx_contract_rate_dates ON pRevenue_Code (Contract_Rate_Effective_Date, Contract_Rate_End_Date);

-- Optional: Update existing records to have a default effective date
-- Uncomment the following lines if you want to set a default effective date for existing records
-- UPDATE pRevenue_Code 
-- SET Contract_Rate_Effective_Date = '2024-01-01' 
-- WHERE Contract_Rate_Effective_Date IS NULL AND Contract_Rate > 0;
