import re

with open('sman1_barumun.sql', 'r', encoding='utf-8') as f:
    lines = f.readlines()

valid_nis = set()
in_siswa = False
for line in lines:
    if line.startswith("INSERT INTO `siswa`"):
        in_siswa = True
        continue
    
    if in_siswa:
        m = re.search(r"^\('([^']+)'", line)
        if m:
            valid_nis.add(m.group(1))
        if line.strip().endswith(";"):
            in_siswa = False

print(f"Found {len(valid_nis)} valid NIS in siswa table.")

out_lines = []
in_riwayat = False
riwayat_buffer = []

for line in lines:
    if line.startswith("INSERT INTO `riwayat_diagnosa`"):
        in_riwayat = True
        out_lines.append(line)
        continue
        
    if in_riwayat:
        m = re.search(r"^\(\d+,\s*'([^']+)'", line)
        if m:
            nis = m.group(1)
            if nis in valid_nis:
                riwayat_buffer.append(line)
                
        if line.strip().endswith(";"):
            in_riwayat = False
            if riwayat_buffer:
                last_line = riwayat_buffer[-1].rstrip()
                if last_line.endswith(','):
                    riwayat_buffer[-1] = last_line[:-1] + ";\n"
                elif last_line.endswith(';'):
                    pass # already ends with ;
            out_lines.extend(riwayat_buffer)
            riwayat_buffer = []
    else:
        out_lines.append(line)

with open('sman1_barumun.sql', 'w', encoding='utf-8') as f:
    f.writelines(out_lines)

print("Cleaned up riwayat_diagnosa table.")
