-- Migration script to add existing income documents to InternalScannedDocuments table
-- This handles income records that have supporting_document but no corresponding InternalScannedDocuments entry

-- First, update any existing records that don't have income_number set
UPDATE finance_chapter_income
SET income_number = CONCAT('INCOME/', chapter_id, '/', YEAR(created_at), '/', LPAD(id, 4, '0'))
WHERE income_number IS NULL OR income_number = '';

-- Then insert missing document records into InternalScannedDocuments
INSERT INTO InternalScannedDocuments (requisition_id, uploader_user_id, chapter_id, document_type, document_link, notes, created_at)
SELECT
    fci.income_number as requisition_id,
    fci.created_by as uploader_user_id,
    fci.chapter_id,
    'Income' as document_type,
    fci.supporting_document as document_link,
    CONCAT('Income document for ', fci.income_category,
           CASE WHEN fci.description IS NOT NULL AND fci.description != ''
                THEN CONCAT(' - ', fci.description)
                ELSE ''
           END) as notes,
    fci.created_at
FROM finance_chapter_income fci
WHERE fci.supporting_document IS NOT NULL
  AND fci.supporting_document != ''
  AND fci.income_number IS NOT NULL
  AND fci.income_number != ''
  AND NOT EXISTS (
    SELECT 1 FROM InternalScannedDocuments isd
    WHERE isd.requisition_id = fci.income_number
    AND isd.document_type = 'Income'
  );
