-- CiviCRM Contact image_file_id column
-- Implements: https://github.com/civicrm/civicrm-core/pull/32853
-- Adds image_file_id column to civicrm_contact referencing civicrm_file.id
-- Migrates existing image_URL data to civicrm_file + civicrm_entity_file records
--
-- Usage: mysql -u USER -p DATABASE < civicrm-contact-image-file-id.sql
--   or:  wp db query < civicrm-contact-image-file-id.sql
--
-- DDL (ALTER TABLE) cannot be rolled back in MySQL, so the schema change runs
-- first with an idempotent guard. The data migration runs as a transaction
-- with validation checks — it rolls back on any failure.

-- ============================================================================
-- Phase 1: Schema change (DDL — implicit commit, not transactional)
-- ============================================================================

-- Add image_file_id column if it doesn't already exist
SET @col_exists = (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'civicrm_contact'
    AND COLUMN_NAME = 'image_file_id'
);

SET @ddl = IF(@col_exists = 0,
  'ALTER TABLE `civicrm_contact` ADD COLUMN `image_file_id` int unsigned NULL DEFAULT NULL COMMENT \'FK to civicrm_file\' AFTER `image_URL`',
  'SELECT "image_file_id column already exists — skipping ALTER" AS status'
);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================================
-- Phase 2: Data migration (DML — transactional with validation and rollback)
-- ============================================================================

DROP PROCEDURE IF EXISTS `_migrate_contact_images`;

DELIMITER //

CREATE PROCEDURE `_migrate_contact_images`()
BEGIN
  DECLARE _contacts_with_url INT DEFAULT 0;
  DECLARE _files_created INT DEFAULT 0;
  DECLARE _contacts_updated INT DEFAULT 0;
  DECLARE _entity_files_created INT DEFAULT 0;
  DECLARE _contacts_with_file_id INT DEFAULT 0;
  DECLARE _contacts_missing_entity_file INT DEFAULT 0;
  DECLARE _exit_code INT DEFAULT 0;

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    GET DIAGNOSTICS CONDITION 1 @err_msg = MESSAGE_TEXT;
    ROLLBACK;
    SELECT CONCAT('ROLLBACK — SQL error: ', @err_msg) AS migration_status;
  END;

  START TRANSACTION;

  -- Count contacts with image_URL containing photo= parameter
  SELECT COUNT(*) INTO _contacts_with_url
  FROM `civicrm_contact`
  WHERE image_URL IS NOT NULL
    AND image_URL != ''
    AND image_URL LIKE '%photo=%';

  IF _contacts_with_url = 0 THEN
    SELECT 'No contacts with image_URL photo= parameter — nothing to migrate' AS migration_status;
    COMMIT;
  ELSE
    -- Step 1: Create civicrm_file records from image_URL photo filenames
    INSERT INTO `civicrm_file` (`mime_type`, `uri`, `upload_date`)
    SELECT DISTINCT
      'image/jpeg',
      SUBSTRING_INDEX(c.image_URL, 'photo=', -1),
      NOW()
    FROM `civicrm_contact` c
    WHERE c.image_URL IS NOT NULL
      AND c.image_URL != ''
      AND c.image_URL LIKE '%photo=%'
      AND NOT EXISTS (
        SELECT 1 FROM `civicrm_file` f
        WHERE f.uri = SUBSTRING_INDEX(c.image_URL, 'photo=', -1)
      );

    SET _files_created = ROW_COUNT();

    -- Step 2: Set image_file_id on civicrm_contact from civicrm_file
    UPDATE `civicrm_contact` c
      JOIN `civicrm_file` f ON f.uri = SUBSTRING_INDEX(c.image_URL, 'photo=', -1)
    SET c.image_file_id = f.id
    WHERE c.image_URL IS NOT NULL
      AND c.image_URL != ''
      AND c.image_URL LIKE '%photo=%';

    SET _contacts_updated = ROW_COUNT();

    -- Step 3: Create civicrm_entity_file records (required by /civicrm/file endpoint)
    INSERT INTO `civicrm_entity_file` (`entity_table`, `entity_id`, `file_id`)
    SELECT
      'civicrm_contact',
      c.id,
      c.image_file_id
    FROM `civicrm_contact` c
    WHERE c.image_file_id IS NOT NULL
      AND NOT EXISTS (
        SELECT 1 FROM `civicrm_entity_file` ef
        WHERE ef.entity_table = 'civicrm_contact'
          AND ef.entity_id = c.id
          AND ef.file_id = c.image_file_id
      );

    SET _entity_files_created = ROW_COUNT();

    -- ========================================================================
    -- Validation: Verify migration integrity before committing
    -- ========================================================================

    -- Check 1: Every contact with image_URL should now have image_file_id
    SELECT COUNT(*) INTO _contacts_with_file_id
    FROM `civicrm_contact`
    WHERE image_URL IS NOT NULL
      AND image_URL != ''
      AND image_URL LIKE '%photo=%'
      AND image_file_id IS NOT NULL;

    IF _contacts_with_file_id != _contacts_with_url THEN
      SET _exit_code = 1;
      SELECT CONCAT(
        'ROLLBACK — Validation failed: ',
        _contacts_with_file_id, ' of ', _contacts_with_url,
        ' contacts have image_file_id set'
      ) AS migration_status;
      ROLLBACK;
    END IF;

    -- Check 2: Every contact with image_file_id should have a civicrm_entity_file record
    IF _exit_code = 0 THEN
      SELECT COUNT(*) INTO _contacts_missing_entity_file
      FROM `civicrm_contact` c
      WHERE c.image_file_id IS NOT NULL
        AND NOT EXISTS (
          SELECT 1 FROM `civicrm_entity_file` ef
          WHERE ef.entity_table = 'civicrm_contact'
            AND ef.entity_id = c.id
            AND ef.file_id = c.image_file_id
        );

      IF _contacts_missing_entity_file > 0 THEN
        SET _exit_code = 1;
        SELECT CONCAT(
          'ROLLBACK — Validation failed: ',
          _contacts_missing_entity_file,
          ' contacts missing civicrm_entity_file records'
        ) AS migration_status;
        ROLLBACK;
      END IF;
    END IF;

    -- All checks passed — commit
    IF _exit_code = 0 THEN
      COMMIT;
      SELECT 'COMMITTED' AS migration_status,
        _contacts_with_url AS contacts_with_image_url,
        _files_created AS file_records_created,
        _contacts_updated AS contacts_updated,
        _entity_files_created AS entity_file_records_created;
    END IF;

  END IF;
END //

DELIMITER ;

CALL `_migrate_contact_images`();
DROP PROCEDURE IF EXISTS `_migrate_contact_images`;

-- ============================================================================
-- Phase 3: API verification (run after SQL completes successfully)
-- ============================================================================
-- Verify the CiviCRM API can return image_file_id for contacts:
--   cv api4 Contact.get +s id +s image_file_id +w 'image_file_id IS NOT NULL' +l 1
--
-- Verify the /civicrm/file endpoint resolves for a migrated contact:
--   cv ev 'return CRM_Core_BAO_File::generateFileHash(FILE_ID, CONTACT_ID);'
--
-- If API verification fails, reverse the data migration with:
--   START TRANSACTION;
--   DELETE ef FROM civicrm_entity_file ef
--     JOIN civicrm_contact c ON ef.entity_id = c.id AND ef.file_id = c.image_file_id
--     WHERE ef.entity_table = 'civicrm_contact';
--   UPDATE civicrm_contact SET image_file_id = NULL WHERE image_file_id IS NOT NULL;
--   -- Optionally remove created file records (only if no other references exist):
--   -- DELETE FROM civicrm_file WHERE id NOT IN (SELECT file_id FROM civicrm_entity_file);
--   COMMIT;
