-- =====================================================================
--  Gabungkan catatan siswa yang tercatat dua kali
--
--  Sebagian siswa muncul dua kali karena ejaan namanya berbeda antara data
--  lama dan daftar terbaru: "M. Azwan Yunus Arridho" dan "MUHAMAD AZWAN YUNUS
--  ARRIDHO" adalah orang yang sama.
--
--  Akibatnya dua hal menempel pada catatan yang salah:
--    - kartu RFID, sehingga tidak berfungsi di gerbang karena pemiliknya
--      tidak terdaftar di kelas mana pun
--    - hubungan dengan orang tua, sehingga wali murid dapat masuk ke aplikasi
--      tetapi tidak melihat anaknya sama sekali
--
--  Keduanya dipindahkan ke catatan yang punya kelas. Catatan lama TIDAK
--  dihapus, hanya ditandai pindah — absensi dan riwayat nilainya masih
--  menunjuk ke sana.
--
--  Urutannya penting: kolom rfid_uid bersifat unik, jadi catatan lama harus
--  melepas nomornya lebih dulu sebelum catatan baru boleh memakainya.
-- =====================================================================

SET NAMES utf8mb4;


-- SITI AISYAH  ->  SITI AISYAH (9H, 100%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 709'
 WHERE id = 710;
UPDATE rfid_kartu SET pemilik_id = 709
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 710;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 709, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 710;
DELETE FROM siswa_ortu WHERE siswa_id = 710;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 709 ORDER BY id LIMIT 1)
 WHERE id = 709;

-- MUHAMAD ADITYA RAMADHAN  ->  MUHAMAD ADITYA RAMADHAN (7D, 100%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 932'
 WHERE id = 933;
UPDATE rfid_kartu SET pemilik_id = 932
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 933;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 932, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 933;
DELETE FROM siswa_ortu WHERE siswa_id = 933;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 932 ORDER BY id LIMIT 1)
 WHERE id = 932;

-- NADHIFA Asyairunnisa Mauliyani  ->  NADHIFA ASSYAIRUNNISA MAULIYANI (7C, 98%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 1031'
 WHERE id = 880;
UPDATE rfid_kartu SET pemilik_id = 1031
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 880;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 1031, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 880;
DELETE FROM siswa_ortu WHERE siswa_id = 880;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 1031 ORDER BY id LIMIT 1)
 WHERE id = 1031;

-- MUHAMMAD NIZZAM AL FARISHI  ->  MUHAMAD NIZZAM AL FARISHI (7B, 98%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 1000'
 WHERE id = 928;
UPDATE rfid_kartu SET pemilik_id = 1000
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 928;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 1000, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 928;
DELETE FROM siswa_ortu WHERE siswa_id = 928;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 1000 ORDER BY id LIMIT 1)
 WHERE id = 1000;

-- Muhamad Al Fatih Rizqi Trimansyah  ->  MUHAMAD ALFATIH RIZQI TRISMANSYAH (7D, 97%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 897'
 WHERE id = 869;
UPDATE rfid_kartu SET pemilik_id = 897
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 869;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 897, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 869;
DELETE FROM siswa_ortu WHERE siswa_id = 869;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 897 ORDER BY id LIMIT 1)
 WHERE id = 897;

-- MUHAMAD FADIL  ->  MUHAMAD PADIL (9A, 92%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 1102'
 WHERE id = 473;
UPDATE rfid_kartu SET pemilik_id = 1102
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 473;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 1102, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 473;
DELETE FROM siswa_ortu WHERE siswa_id = 473;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 1102 ORDER BY id LIMIT 1)
 WHERE id = 1102;

-- SITI NAZWA SALSABILA  ->  Siti Nayla Salsabila (7E, 90%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 921'
 WHERE id = 751;
UPDATE rfid_kartu SET pemilik_id = 921
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 751;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 921, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 751;
DELETE FROM siswa_ortu WHERE siswa_id = 751;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 921 ORDER BY id LIMIT 1)
 WHERE id = 921;

-- MEIZHIRRA NAZWALIA AZAHRA  ->  MEIZHIRRA NAZWALIA A. (9A, 89%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 1097'
 WHERE id = 444;
UPDATE rfid_kartu SET pemilik_id = 1097
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 444;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 1097, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 444;
DELETE FROM siswa_ortu WHERE siswa_id = 444;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 1097 ORDER BY id LIMIT 1)
 WHERE id = 1097;

-- MUHAMMAD ALGIPARI  ->  MUHAMAD ALGHIFARI (9G, 88%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 1091'
 WHERE id = 565;
UPDATE rfid_kartu SET pemilik_id = 1091
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 565;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 1091, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 565;
DELETE FROM siswa_ortu WHERE siswa_id = 565;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 1091 ORDER BY id LIMIT 1)
 WHERE id = 1091;

-- M. Azwan Yunus Arridho  ->  MUHAMAD AZWAN YUNUS ARRIDHO (8C, 88%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 1068'
 WHERE id = 65;
UPDATE rfid_kartu SET pemilik_id = 1068
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 65;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 1068, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 65;
DELETE FROM siswa_ortu WHERE siswa_id = 65;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 1068 ORDER BY id LIMIT 1)
 WHERE id = 1068;

-- SITI APINDA SANATUN HASANAH  ->  SITI APINDA SANATUN H. (9H, 88%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 1115'
 WHERE id = 718;
UPDATE rfid_kartu SET pemilik_id = 1115
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 718;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 1115, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 718;
DELETE FROM siswa_ortu WHERE siswa_id = 718;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 1115 ORDER BY id LIMIT 1)
 WHERE id = 1115;

-- MUHAMMAD AZKA HILMANI JUNIARTO  ->  M. AZKA HILMANI JUNIARTO (9G, 87%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 1083'
 WHERE id = 570;
UPDATE rfid_kartu SET pemilik_id = 1083
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 570;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 1083, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 570;
DELETE FROM siswa_ortu WHERE siswa_id = 570;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 1083 ORDER BY id LIMIT 1)
 WHERE id = 1083;

-- Sahlma Tantita Zivana  ->  SAHLMA TANTITA Z (9B, 86%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 1111'
 WHERE id = 683;
UPDATE rfid_kartu SET pemilik_id = 1111
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 683;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 1111, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 683;
DELETE FROM siswa_ortu WHERE siswa_id = 683;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 1111 ORDER BY id LIMIT 1)
 WHERE id = 1111;

-- M ABDA APRILIANSYAH  ->  MUHAMAD ABDA APRILIANSYAH (9I, 86%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 1096'
 WHERE id = 398;
UPDATE rfid_kartu SET pemilik_id = 1096
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 398;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 1096, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 398;
DELETE FROM siswa_ortu WHERE siswa_id = 398;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 1096 ORDER BY id LIMIT 1)
 WHERE id = 1096;

-- MUHAMAD ABDURAHMAN SOLEH  ->  M ABDURAHMAN SOLEH (8B, 86%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 1067'
 WHERE id = 71;
UPDATE rfid_kartu SET pemilik_id = 1067
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 71;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 1067, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 71;
DELETE FROM siswa_ortu WHERE siswa_id = 71;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 1067 ORDER BY id LIMIT 1)
 WHERE id = 1067;

-- CHALISTA RAMADHANI PUTRI  ->  CHALISTA RAMADHANI (9J, 86%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 1077'
 WHERE id = 314;
UPDATE rfid_kartu SET pemilik_id = 1077
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 314;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 1077, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 314;
DELETE FROM siswa_ortu WHERE siswa_id = 314;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 1077 ORDER BY id LIMIT 1)
 WHERE id = 1077;

-- HIKMAH AULIA RIZKI LUBIS  ->  HIKMAH AULIA LUBIS (9E, 86%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 1078'
 WHERE id = 366;
UPDATE rfid_kartu SET pemilik_id = 1078
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 366;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 1078, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 366;
DELETE FROM siswa_ortu WHERE siswa_id = 366;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 1078 ORDER BY id LIMIT 1)
 WHERE id = 1078;

-- M. DIO SATRYA ANDRIANI  ->  MUHAMMAD DIO SATRYA ANDRIANI (9J, 86%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 1107'
 WHERE id = 416;
UPDATE rfid_kartu SET pemilik_id = 1107
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 416;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 1107, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 416;
DELETE FROM siswa_ortu WHERE siswa_id = 416;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 1107 ORDER BY id LIMIT 1)
 WHERE id = 1107;

-- MUHAMAD RAZIB JANUAR  ->  MUHAMAD RAZIB J (9D, 86%)
UPDATE siswa SET rfid_uid = NULL, status = 'PINDAH',
       alasan_keluar = 'Catatan ganda, digabung ke id 1101'
 WHERE id = 534;
UPDATE rfid_kartu SET pemilik_id = 1101
 WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 534;
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
     SELECT 1101, ortu_id, is_utama FROM siswa_ortu WHERE siswa_id = 534;
DELETE FROM siswa_ortu WHERE siswa_id = 534;
UPDATE siswa SET rfid_uid = (SELECT uid FROM rfid_kartu
        WHERE pemilik_tipe = 'SISWA' AND pemilik_id = 1101 ORDER BY id LIMIT 1)
 WHERE id = 1101;

SELECT CONCAT(COUNT(*), ' kartu menempel pada siswa berkelas') AS hasil
  FROM rfid_kartu k JOIN siswa s ON s.id = k.pemilik_id
  JOIN kelas_siswa ks ON ks.siswa_id = s.id AND ks.status = 'AKTIF'
 WHERE k.pemilik_tipe = 'SISWA';