import sqlite3
import pandas as pd
import sys
import os

db_path = os.path.join(os.path.dirname(__file__), 'database.sqlite')
excel_path = sys.argv[1]

print(f"Importing from {excel_path} to {db_path}...")

conn = sqlite3.connect(db_path)
cursor = conn.cursor()

cursor.execute("DELETE FROM exam_records")

try:
    xl = pd.ExcelFile(excel_path)
    records = []
    
    # --- Process DS thi_final ---
    if "DS thi_final" in xl.sheet_names:
        df1 = pd.read_excel(excel_path, sheet_name="DS thi_final", header=None)
        # Data starts from row 7 (index 7)
        for idx, row in df1.iloc[7:].iterrows():
            ma_sv = str(row[2]).strip()
            if ma_sv == 'nan' or not ma_sv: continue
            
            ho_dem = str(row[3]).strip()
            ten = str(row[4]).strip()
            lop_chuyen_nganh = str(row[14]).strip() if pd.notna(row[14]) else ''
            dieu_kien_thi_raw = str(row[15]).strip().upper() if pd.notna(row[15]) else ''
            if 'HT' in dieu_kien_thi_raw:
                dieu_kien_thi = 'Hoãn thi'
            elif 'KĐT' in dieu_kien_thi_raw or 'KDT' in dieu_kien_thi_raw:
                dieu_kien_thi = 'Không được thi'
            else:
                dieu_kien_thi = 'Đủ điều kiện'
                
            phong_thi = str(row[20]).strip() if pd.notna(row[20]) else ''
            ma_lhp = str(row[22]).strip() if pd.notna(row[22]) else ''
            ngay_thi = str(row[23]).strip() if pd.notna(row[23]) else ''
            ca_thi = str(row[24]).strip() if pd.notna(row[24]) else ''
            gio_thi = str(row[25]).strip() if pd.notna(row[25]) else ''
            ten_mon = str(row[27]).strip() if pd.notna(row[27]) else ''
            dia_diem_thi = str(row[29]).strip() if pd.notna(row[29]) else ''
            ghi_chu = str(row[33]).strip() if pd.notna(row[33]) else ''
            
            records.append((ma_sv, ho_dem, ten, lop_chuyen_nganh, dieu_kien_thi, ma_lhp, ten_mon, ngay_thi, ca_thi, gio_thi, phong_thi, dia_diem_thi, ghi_chu))



    # Clean 'nan' strings
    cleaned_records = []
    for r in records:
        cleaned_records.append(tuple(['' if val == 'nan' else val for val in r]))

    cursor.executemany("""
        INSERT INTO exam_records (
            ma_sv, ho_dem, ten, lop_chuyen_nganh, dieu_kien_thi, 
            ma_lhp, ten_mon, ngay_thi, ca_thi, gio_thi, 
            phong_thi, dia_diem_thi, ghi_chu, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'), datetime('now'))
    """, cleaned_records)
    
    conn.commit()
    print(f"Successfully imported {len(cleaned_records)} records!")

except Exception as e:
    print(f"An error occurred: {e}")
finally:
    conn.close()