-- Life Analytics - one-time production data conversion for MySQL 8 / OVH.
-- Import after 2026_07_24_update_production_schema.sql.
--
-- The conversion is guarded by manual_database_updates. Reimporting this file
-- does not multiply or normalize weights a second time.
--
-- Weight rules based on the legacy production dump:
-- - activity types: legacy 0.10..1.00 becomes 1.00..10.00 (x10);
-- - goals: each user's highest legacy weight becomes 10.00, with all ratios
--   between their goals preserved and a minimum accepted value of 1.00;
-- - goal progress snapshots use the same per-user conversion as their goals.

START TRANSACTION;

SET @life_analytics_update_key := '2026_07_24_adapt_production_data_v1';
SET @life_analytics_should_apply := (
    SELECT NOT EXISTS (
        SELECT 1
        FROM manual_database_updates
        WHERE update_key = @life_analytics_update_key
    )
);

CREATE TEMPORARY TABLE legacy_goal_weight_scales (
    user_id BIGINT UNSIGNED NOT NULL,
    maximum_weight DECIMAL(12, 4) NOT NULL,
    PRIMARY KEY (user_id)
);

INSERT INTO legacy_goal_weight_scales (user_id, maximum_weight)
SELECT user_id, MAX(score_weight)
FROM goals
WHERE @life_analytics_should_apply = 1
  AND score_weight > 0
GROUP BY user_id;

-- Existing production accounts are French and use the Europe/Paris timezone.
-- New accounts continue to choose their locale and detect their timezone.
UPDATE users
SET locale = 'fr',
    timezone = 'Europe/Paris'
WHERE @life_analytics_should_apply = 1;

-- Convert the former activity coefficient scale without flattening its ranking.
UPDATE activity_types
SET score_weight = LEAST(10.00, GREATEST(1.00, ROUND(score_weight * 10, 2)))
WHERE @life_analytics_should_apply = 1;

-- Convert historical snapshots before changing the source goal weights.
UPDATE goal_progress AS progress
INNER JOIN legacy_goal_weight_scales AS scale
    ON scale.user_id = progress.user_id
SET progress.score_weight_snapshot = LEAST(
    10.00,
    GREATEST(1.00, ROUND((progress.score_weight_snapshot / scale.maximum_weight) * 10, 2))
)
WHERE @life_analytics_should_apply = 1
  AND progress.score_weight_snapshot IS NOT NULL
  AND scale.maximum_weight > 0;

UPDATE goals AS goal
INNER JOIN legacy_goal_weight_scales AS scale
    ON scale.user_id = goal.user_id
SET goal.score_weight = LEAST(
    10.00,
    GREATEST(1.00, ROUND((goal.score_weight / scale.maximum_weight) * 10, 2))
)
WHERE @life_analytics_should_apply = 1
  AND scale.maximum_weight > 0;

-- Preserve legacy friend/family sessions with a neutral context. Their primary
-- activity weight remains authoritative; the context can be tuned later.
INSERT INTO activity_social_contexts (
    user_id,
    name,
    slug,
    multiplier,
    is_active,
    created_at,
    updated_at
)
SELECT DISTINCT
    activity_sessions.user_id,
    'Ami / famille',
    'ami-famille',
    1.00,
    1,
    NOW(),
    NOW()
FROM activity_sessions
WHERE @life_analytics_should_apply = 1
  AND activity_sessions.with_friend_or_family = 1
  AND activity_sessions.activity_social_context_id IS NULL
ON DUPLICATE KEY UPDATE updated_at = VALUES(updated_at);

UPDATE activity_sessions
INNER JOIN activity_social_contexts
    ON activity_social_contexts.user_id = activity_sessions.user_id
    AND activity_social_contexts.slug = 'ami-famille'
SET activity_sessions.activity_social_context_id = activity_social_contexts.id
WHERE @life_analytics_should_apply = 1
  AND activity_sessions.with_friend_or_family = 1
  AND activity_sessions.activity_social_context_id IS NULL;

-- Convert the former implicit weekly/monthly split into explicit day targets.
INSERT IGNORE INTO goal_schedule_days (
    goal_id,
    period_day,
    expected_progress,
    created_at,
    updated_at
)
SELECT
    goals.id,
    days.period_day,
    GREATEST(
        0.01,
        ROUND(goals.target_value / IF(goals.period = 'weekly', 7, 31), 2)
    ),
    NOW(),
    NOW()
FROM goals
INNER JOIN (
    SELECT 1 AS period_day UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
    UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
    UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16
    UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
    UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
    UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28
    UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31
) AS days
    ON days.period_day <= IF(goals.period = 'weekly', 7, 31)
WHERE @life_analytics_should_apply = 1
  AND goals.period IN ('weekly', 'monthly')
  AND goals.target_value > 0;

-- Keep current scores as historical records. Opened or edited days will be
-- recalculated by the application with the new formula.
INSERT IGNORE INTO manual_database_updates (update_key)
SELECT @life_analytics_update_key
WHERE @life_analytics_should_apply = 1;

DROP TEMPORARY TABLE legacy_goal_weight_scales;

COMMIT;

-- Verification queries: expected ranges are 1.00..10.00 and applied_count = 1.
SELECT MIN(score_weight) AS activity_weight_min,
       MAX(score_weight) AS activity_weight_max
FROM activity_types;

SELECT user_id,
       MIN(score_weight) AS goal_weight_min,
       MAX(score_weight) AS goal_weight_max
FROM goals
GROUP BY user_id;

SELECT COUNT(*) AS applied_count
FROM manual_database_updates
WHERE update_key = @life_analytics_update_key;
