-- Add consolidation support to reports table
-- This allows tracking which reports have been consolidated into circuit-level reports

-- Add consolidated_into field to track which consolidated report this report belongs to
ALTER TABLE reports 
ADD COLUMN consolidated_into INT(11) NULL DEFAULT NULL COMMENT 'ID of consolidated report this report was merged into',
ADD INDEX idx_consolidated_into (consolidated_into);

-- Add foreign key constraint (optional, depends on your preference)
-- ALTER TABLE reports 
-- ADD CONSTRAINT fk_reports_consolidated_into 
-- FOREIGN KEY (consolidated_into) REFERENCES reports(id) ON DELETE SET NULL;

-- Update existing reports to ensure they have proper default values
UPDATE reports SET consolidated_into = NULL WHERE consolidated_into = 0;

-- Add 'consolidated' status to the status enum if it doesn't exist
-- First check current enum values
SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'reports' 
AND COLUMN_NAME = 'status';

-- If 'consolidated' is not in the enum, add it
-- Note: This will need to be run manually if the enum doesn't include 'consolidated'
-- ALTER TABLE reports MODIFY COLUMN status ENUM('draft','submitted','pending','approved','rejected','completed','consolidated') DEFAULT 'draft';
