-- ============================================================
-- LIMS Database Migration: Returned Samples Tracking
-- ============================================================

-- Add columns to track returned samples
ALTER TABLE sample_accessioning 
ADD COLUMN is_returned BOOLEAN DEFAULT FALSE AFTER accession_status,
ADD COLUMN return_count INT DEFAULT 0 AFTER is_returned,
ADD COLUMN last_returned_date DATETIME NULL AFTER return_count,
ADD COLUMN last_returned_reason TEXT NULL AFTER last_returned_date,
ADD COLUMN last_returned_by VARCHAR(100) NULL AFTER last_returned_reason;

-- Add indexes for performance
CREATE INDEX idx_is_returned ON sample_accessioning(is_returned);
CREATE INDEX idx_return_count ON sample_accessioning(return_count);

-- Update existing returned samples (if any exist in test_reassignments)
UPDATE sample_accessioning sa
INNER JOIN (
    SELECT barcode, 
           COUNT(*) as count,
           MAX(reassigned_at) as last_date,
           MAX(reason) as last_reason,
           MAX(from_analyst) as last_analyst
    FROM test_reassignments
    GROUP BY barcode
) tr ON sa.barcode = tr.barcode
SET sa.is_returned = TRUE,
    sa.return_count = tr.count,
    sa.last_returned_date = tr.last_date,
    sa.last_returned_reason = tr.last_reason,
    sa.last_returned_by = tr.last_analyst
WHERE sa.accession_status = 'Unassigned' AND sa.assigned_to IS NULL;

-- Verify migration
SELECT 
    COUNT(*) as total_samples,
    SUM(CASE WHEN is_returned = TRUE THEN 1 ELSE 0 END) as returned_samples,
    SUM(return_count) as total_returns
FROM sample_accessioning;
