-- =============================================================================
-- Migration: Remove demandeur / email from demand table & meta_json
-- Date:      2026-04-15
-- Context:   Requester identity is now resolved exclusively via
--            created_by_user_id → user_profile (display_name, email).
--            The legacy demandeur/email fields (stored in meta_json) are
--            no longer written by the application and can be cleaned up.
--
-- IMPORTANT: Back up the `demand` table before running this script.
--            Verify that created_by_user_id IS NOT NULL for all rows
--            before running Step 3 (ALTER TABLE).
-- =============================================================================

-- ---------------------------------------------------------------------------
-- Step 0: Safety check — count rows where created_by_user_id IS NULL
--         (run this before anything else; result should be 0)
-- ---------------------------------------------------------------------------
SELECT COUNT(*) AS rows_without_user_id
FROM demand
WHERE created_by_user_id IS NULL;

-- ---------------------------------------------------------------------------
-- Step 1: Clean up meta_json — remove demandeur & email keys from JSON
--         (safe to run on a live system, leaves other JSON keys intact)
-- ---------------------------------------------------------------------------
UPDATE demand
SET meta_json = JSON_REMOVE(
                    JSON_REMOVE(meta_json, '$.demandeur'),
                    '$.email'
                )
WHERE meta_json IS NOT NULL
  AND (
      JSON_CONTAINS_PATH(meta_json, 'one', '$.demandeur')
      OR JSON_CONTAINS_PATH(meta_json, 'one', '$.email')
  );

-- Optional: null out meta_json where it became an empty object after cleanup
UPDATE demand
SET meta_json = NULL
WHERE meta_json = '{}';

-- ---------------------------------------------------------------------------
-- Step 2 (OPTIONAL – only if the physical columns exist in your schema)
--
--   The canonical DDL (api/documentation/demands_ddl.sql) does NOT define
--   demandeur or email as physical columns — they were only stored in
--   meta_json.  If an earlier schema version added them as real columns,
--   drop them with the statements below.  Otherwise, skip Step 2.
-- ---------------------------------------------------------------------------

-- ALTER TABLE demand DROP COLUMN IF EXISTS demandeur;
-- ALTER TABLE demand DROP COLUMN IF EXISTS email;

-- ---------------------------------------------------------------------------
-- Step 3 (RECOMMENDED – enforce created_by_user_id is NOT NULL)
--
--   The DDL already declares this column as NOT NULL, but existing rows
--   created before enforcement may have NULL.  Run Step 0 first and fix
--   any NULL rows manually (assign a default user or delete orphans),
--   then run the ALTER below.
-- ---------------------------------------------------------------------------

-- ALTER TABLE demand
--     MODIFY COLUMN created_by_user_id INT UNSIGNED NOT NULL
--     COMMENT 'auth_user.id of the creator';

-- ---------------------------------------------------------------------------
-- Step 4: Verify — spot-check a few rows after migration
-- ---------------------------------------------------------------------------
SELECT
    d.id,
    d.ref,
    d.created_by_user_id,
    up.display_name AS requester_name,
    up.email        AS requester_email,
    d.meta_json
FROM demand d
LEFT JOIN user_profile up ON up.auth_user_id = d.created_by_user_id
ORDER BY d.created_at DESC
LIMIT 20;

