-- 2026-04-17
-- Expande wpjy_github_user_links para guardar a verificacao forte via OAuth do GitHub.

SET @db_name = DATABASE();

SET @sql = IF(
    EXISTS(
        SELECT 1
          FROM information_schema.columns
         WHERE table_schema = @db_name
           AND table_name = 'wpjy_github_user_links'
           AND column_name = 'github_user_id'
    ),
    'SELECT 1',
    'ALTER TABLE wpjy_github_user_links ADD COLUMN github_user_id BIGINT UNSIGNED NULL AFTER github_login'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
    EXISTS(
        SELECT 1
          FROM information_schema.columns
         WHERE table_schema = @db_name
           AND table_name = 'wpjy_github_user_links'
           AND column_name = 'github_name'
    ),
    'SELECT 1',
    'ALTER TABLE wpjy_github_user_links ADD COLUMN github_name VARCHAR(190) NULL AFTER github_user_id'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
    EXISTS(
        SELECT 1
          FROM information_schema.columns
         WHERE table_schema = @db_name
           AND table_name = 'wpjy_github_user_links'
           AND column_name = 'github_profile_url'
    ),
    'SELECT 1',
    'ALTER TABLE wpjy_github_user_links ADD COLUMN github_profile_url VARCHAR(255) NULL AFTER github_name'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
    EXISTS(
        SELECT 1
          FROM information_schema.columns
         WHERE table_schema = @db_name
           AND table_name = 'wpjy_github_user_links'
           AND column_name = 'github_avatar_url'
    ),
    'SELECT 1',
    'ALTER TABLE wpjy_github_user_links ADD COLUMN github_avatar_url VARCHAR(255) NULL AFTER github_profile_url'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
    EXISTS(
        SELECT 1
          FROM information_schema.columns
         WHERE table_schema = @db_name
           AND table_name = 'wpjy_github_user_links'
           AND column_name = 'verified_at'
    ),
    'SELECT 1',
    'ALTER TABLE wpjy_github_user_links ADD COLUMN verified_at DATETIME NULL AFTER github_avatar_url'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
    EXISTS(
        SELECT 1
          FROM information_schema.columns
         WHERE table_schema = @db_name
           AND table_name = 'wpjy_github_user_links'
           AND column_name = 'verification_source'
    ),
    'SELECT 1',
    'ALTER TABLE wpjy_github_user_links ADD COLUMN verification_source VARCHAR(30) NULL AFTER verified_at'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
    EXISTS(
        SELECT 1
          FROM information_schema.columns
         WHERE table_schema = @db_name
           AND table_name = 'wpjy_github_user_links'
           AND column_name = 'is_verified'
    ),
    'SELECT 1',
    'ALTER TABLE wpjy_github_user_links ADD COLUMN is_verified TINYINT(1) NOT NULL DEFAULT 0 AFTER verification_source'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_idx = (
    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_github_user_id'
);

SET @sql = IF(
    @has_idx = 0,
    'ALTER TABLE wpjy_github_user_links ADD UNIQUE INDEX uq_github_user_links_github_user_id (github_user_id)',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

SET @sql = IF(
    @has_idx = 0,
    'ALTER TABLE wpjy_github_user_links ADD INDEX idx_github_user_links_verified (is_verified, verified_at)',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;