import pymysql

conn = pymysql.connect(host='localhost', user='root', password='', database='tracerstudy_db', port=3306, charset='utf8mb4')
cursor = conn.cursor()

# 1. Update Database Schema for tracer_responses
print("=== UPDATING DATABASE SCHEMA ===")

# Check if 'nim' column exists in tracer_responses
cursor.execute("SHOW COLUMNS FROM tracer_responses LIKE 'nim'")
if not cursor.fetchone():
    cursor.execute("ALTER TABLE tracer_responses ADD COLUMN nim VARCHAR(50) NULL AFTER alumni_id, ADD INDEX (nim);")
    print("Added 'nim' column to tracer_responses.")
else:
    print("'nim' column already exists in tracer_responses.")

# Widen columns that can overflow
alter_cols = [
    ("jumlah_perusahaan", "TEXT NULL"),
    ("dapet_kerja_6_bulan", "VARCHAR(255) NULL"),
    ("tingkatan_tempat_kerja", "VARCHAR(255) NULL"),
    ("telepon", "VARCHAR(50) NULL"),
    ("nik", "VARCHAR(50) NULL"),
    ("npwp", "VARCHAR(50) NULL"),
    ("status_saat_ini", "VARCHAR(255) NULL"),
    ("kerja_tidak_sesuai_1", "TEXT NULL"),
    ("kerja_tidak_sesuai_2", "TEXT NULL"),
] + [(f"alasan_tidak_sesuai_{i}", "TEXT NULL") for i in range(1, 12)]

for col, col_type in alter_cols:
    try:
        cursor.execute(f"ALTER TABLE tracer_responses MODIFY COLUMN {col} {col_type};")
        print(f"Modified column '{col}' to {col_type}")
    except Exception as e:
        print(f"Failed to modify '{col}': {e}")

# 2. Truncate all tables
print("\n=== TRUNCATING DATA TABLES ===")
cursor.execute('SET FOREIGN_KEY_CHECKS = 0')

tables = ['clustering_result', 'tracer_responses', 'kompetensi', 'pekerjaan', 'alumni']
for table in tables:
    cursor.execute(f'TRUNCATE TABLE {table}')
    print(f"Truncated: {table}")

cursor.execute('SET FOREIGN_KEY_CHECKS = 1')
conn.commit()

# 3. Verification
print("\n=== VERIFIKASI SETELAH TRUNCATE ===")
for table in tables:
    row = cursor.fetchone()
    count = row[0] if row else 0
    print(f"  {table}: {count} rows")

cursor.close()
conn.close()
print("\nSemua data berhasil dibersihkan dan skema database berhasil diperbarui!")