import openpyxl
import random

wb = openpyxl.load_workbook('kuesioner.xlsx', data_only=True)
sheet = wb.active

respondents = []
current_resp = None

for row in sheet.iter_rows(min_row=2, values_only=True):
    no = row[0]
    name_part = row[1]
    
    # Handle empty rows
    if name_part is None or str(name_part).strip() == '':
        continue
        
    if no is not None and str(no).strip() != '':
        # New respondent
        answers = {
            'H': list(row[2:12]),
            'P': list(row[12:22])
        }
        current_resp = {
            'name': str(name_part).strip(),
            'answers': answers
        }
        respondents.append(current_resp)
    else:
        # Continuation of previous name
        if current_resp is not None:
            current_resp['name'] += ' ' + str(name_part).strip()

php_code = "<?php\nrequire 'config.php';\n"
php_code += "mysqli_query($conn, 'SET FOREIGN_KEY_CHECKS = 0');\n"
php_code += "mysqli_query($conn, 'TRUNCATE TABLE responden');\n"
php_code += "mysqli_query($conn, 'TRUNCATE TABLE hasil_kuesioner');\n"

umurs = ['<19 Tahun', '20 - 25 Tahun', '26 - 30 Tahun', '31 - 40 Tahun', '41 Tahun Keatas']
pendapatans = ['Rp. 1.000.000 - 2.000.000', 'Rp. 2.500.000 - 4.000.000', 'Rp. 3.500.000 - 4.000.000', 'Rp. 4.500.000 - 5.000.000', '> Rp. 5.000.0000']

def guess_gender(name):
    name = name.lower()
    if any(name.endswith(suffix) for suffix in ['a', 'i', 'y', 'wati', 'putri', 'tuti']):
        if "putra" in name or "budi" in name or "dika" in name or "raja" in name or "arya" in name or "ezaz" in name or "feliks" in name:
            return "Laki-laki"
        return "Perempuan"
    return "Laki-laki"

php_code += "$data = [\n"
for r in respondents:
    name = r['name'].replace("'", "\\'")
    gender = guess_gender(name)
    umur = random.choice(umurs)
    pend = random.choice(pendapatans)
    ans = r['answers']
    
    ans_str = "["
    for i in range(10):
        # Handle possible None values in answers by defaulting to 0
        p_val = ans['P'][i] if ans['P'][i] is not None else 0
        h_val = ans['H'][i] if ans['H'][i] is not None else 0
        ans_str += f"[{i+1}, {p_val}, {h_val}],"
    ans_str += "]"
    
    php_code += f"    ['name' => '{name}', 'jk' => '{gender}', 'umur' => '{umur}', 'pend' => '{pend}', 'ans' => {ans_str}],\n"

php_code += "];\n"

php_code += """
$success = 0;
foreach ($data as $r) {
    $sql = "INSERT INTO responden (nama, jk, umur, pendapatan) VALUES ('{$r['name']}', '{$r['jk']}', '{$r['umur']}', '{$r['pend']}')";
    mysqli_query($conn, $sql);
    $id_resp = mysqli_insert_id($conn);
    foreach ($r['ans'] as $a) {
        $id_p = $a[0];
        $p = $a[1];
        $h = $a[2];
        if ($p > 0 && $h > 0) {
            $sql2 = "INSERT INTO hasil_kuesioner (id_responden, id_pernyataan, skor_persepsi, skor_harapan) VALUES ($id_resp, $id_p, $p, $h)";
            mysqli_query($conn, $sql2);
        }
    }
    $success++;
}
mysqli_query($conn, 'SET FOREIGN_KEY_CHECKS = 1');
echo "Seeded $success respondents.";
?>
"""

with open('api/run_seed.php', 'w') as f:
    f.write(php_code)
print(f"run_seed.php generated for {len(respondents)} respondents.")