-- 2026-04-17
-- GitHub centralizado em wpjy_github_user_links como fonte principal
-- Objetivos:
-- 1) eliminar duplicidade operacional entre usermeta e github_user_links
-- 2) backfill/normalizar github_login por user_id a partir de meta legado
-- 3) reforcar integridade com indice unico por user_id
-- 4) religar linked_user_id em wpjy_github_commits pelo login normalizado
-- 5) remover o legado em usermeta depois do backfill

START TRANSACTION;

DELETE dup
FROM wpjy_github_user_links dup
JOIN wpjy_github_user_links base
  ON dup.user_id = base.user_id
 AND dup.id > base.id;

SET @db_name = DATABASE();
SET @has_user_unique = (
    SELECT COUNT(*)
      FROM information_schema.statistics
     WHERE table_schema = @db_name
       AND table_name = 'wpjy_github_user_links'
       AND index_name = 'uq_github_user_links_user'
);

SET @sql_add_user_unique = IF(
    @has_user_unique = 0,
    'ALTER TABLE wpjy_github_user_links ADD UNIQUE INDEX uq_github_user_links_user (user_id)',
    'SELECT 1'
);
PREPARE stmt_add_user_unique FROM @sql_add_user_unique;
EXECUTE stmt_add_user_unique;
DEALLOCATE PREPARE stmt_add_user_unique;

DROP TEMPORARY TABLE IF EXISTS tmp_github_links_source;
CREATE TEMPORARY TABLE tmp_github_links_source AS
SELECT
    u.ID AS user_id,
    COALESCE(
        NULLIF(TRIM(COALESCE(um_username.meta_value, '')), ''),
        NULLIF(
            TRIM(
                CASE
                    WHEN COALESCE(um_profile.meta_value, '') REGEXP '^https?://(www\\.)?github\\.com/' THEN
                        SUBSTRING_INDEX(
                            TRIM(BOTH '/' FROM
                                REPLACE(
                                    REPLACE(
                                        REPLACE(
                                            REPLACE(TRIM(COALESCE(um_profile.meta_value, '')), 'https://www.github.com/', ''),
                                            'https://github.com/', ''
                                        ),
                                        'http://www.github.com/', ''
                                    ),
                                    'http://github.com/', ''
                                )
                            ),
                            '/',
                            1
                        )
                    ELSE ''
                END
            ),
            ''
        ),
        NULLIF(TRIM(COALESCE(gul.github_login, '')), '')
    ) AS github_login_raw,
    COALESCE(
        NULLIF(TRIM(COALESCE(gul.nome_ref, '')), ''),
        NULLIF(TRIM(COALESCE(u.display_name, '')), ''),
        NULLIF(TRIM(CONCAT(COALESCE(um_first.meta_value, ''), ' ', COALESCE(um_last.meta_value, ''))), ''),
        u.user_login
    ) AS nome_ref,
    COALESCE(
        NULLIF(TRIM(COALESCE(gul.email_ref, '')), ''),
        NULLIF(TRIM(COALESCE(u.user_email, '')), '')
    ) AS email_ref
FROM wpjy_users u
LEFT JOIN wpjy_github_user_links gul
  ON gul.user_id = u.ID
LEFT JOIN wpjy_usermeta um_username
  ON um_username.user_id = u.ID
 AND um_username.meta_key = 'github_username'
LEFT JOIN wpjy_usermeta um_profile
  ON um_profile.user_id = u.ID
 AND um_profile.meta_key = 'github_profile'
LEFT JOIN wpjy_usermeta um_first
  ON um_first.user_id = u.ID
 AND um_first.meta_key = 'first_name'
LEFT JOIN wpjy_usermeta um_last
  ON um_last.user_id = u.ID
 AND um_last.meta_key = 'last_name';

DELETE FROM tmp_github_links_source
WHERE github_login_raw IS NULL
   OR TRIM(github_login_raw) = '';

ALTER TABLE tmp_github_links_source ADD COLUMN github_login VARCHAR(120) NULL;

UPDATE tmp_github_links_source
SET github_login = LOWER(TRIM(github_login_raw));

DELETE FROM tmp_github_links_source
WHERE github_login IS NULL
   OR github_login = '';

UPDATE wpjy_github_user_links gul
JOIN tmp_github_links_source src
  ON src.user_id = gul.user_id
LEFT JOIN wpjy_github_user_links conflict
  ON LOWER(CONVERT(conflict.github_login USING utf8mb4)) COLLATE utf8mb4_unicode_ci =
     LOWER(CONVERT(src.github_login USING utf8mb4)) COLLATE utf8mb4_unicode_ci
 AND conflict.user_id <> src.user_id
SET
    gul.github_login = src.github_login,
    gul.nome_ref = src.nome_ref,
    gul.email_ref = src.email_ref,
    gul.updated_at = NOW()
WHERE conflict.id IS NULL;

INSERT INTO wpjy_github_user_links (user_id, github_login, nome_ref, email_ref, created_at, updated_at)
SELECT
    src.user_id,
    src.github_login,
    src.nome_ref,
    src.email_ref,
    NOW(),
    NOW()
FROM tmp_github_links_source src
LEFT JOIN wpjy_github_user_links gul_user
  ON gul_user.user_id = src.user_id
LEFT JOIN wpjy_github_user_links gul_login
  ON LOWER(CONVERT(gul_login.github_login USING utf8mb4)) COLLATE utf8mb4_unicode_ci =
     LOWER(CONVERT(src.github_login USING utf8mb4)) COLLATE utf8mb4_unicode_ci
WHERE gul_user.id IS NULL
  AND gul_login.id IS NULL;

UPDATE wpjy_github_commits c
JOIN tmp_github_links_source src
  ON c.linked_user_id = src.user_id
SET c.linked_user_id = NULL
WHERE LOWER(CONVERT(COALESCE(c.sender_login, '') USING utf8mb4)) COLLATE utf8mb4_unicode_ci <>
      LOWER(CONVERT(src.github_login USING utf8mb4)) COLLATE utf8mb4_unicode_ci
  AND LOWER(CONVERT(COALESCE(c.author_username, '') USING utf8mb4)) COLLATE utf8mb4_unicode_ci <>
      LOWER(CONVERT(src.github_login USING utf8mb4)) COLLATE utf8mb4_unicode_ci;

UPDATE wpjy_github_commits c
JOIN tmp_github_links_source src
  ON LOWER(CONVERT(COALESCE(c.sender_login, '') USING utf8mb4)) COLLATE utf8mb4_unicode_ci =
     LOWER(CONVERT(src.github_login USING utf8mb4)) COLLATE utf8mb4_unicode_ci
   OR LOWER(CONVERT(COALESCE(c.author_username, '') USING utf8mb4)) COLLATE utf8mb4_unicode_ci =
      LOWER(CONVERT(src.github_login USING utf8mb4)) COLLATE utf8mb4_unicode_ci
SET c.linked_user_id = src.user_id;

SELECT
    src.user_id,
    src.github_login,
    src.nome_ref,
    src.email_ref,
    COUNT(c.id) AS commits_vinculados
FROM tmp_github_links_source src
LEFT JOIN wpjy_github_commits c
  ON c.linked_user_id = src.user_id
GROUP BY src.user_id, src.github_login, src.nome_ref, src.email_ref
ORDER BY src.nome_ref ASC;

DELETE FROM wpjy_usermeta
WHERE meta_key IN ('github_profile', 'github_username');

COMMIT;