first_year query

from collections import Counter

# Input values
sem = 1
sub_type = 'c'.strip().upper()
session_sa = 'Autumn'.strip().upper()
floated_year = 2025

# Step 1: Read, clean, and sort the rolls (case-insensitive)
with open('roll1.txt', 'r') as file:
    lines = [line.strip().upper() for line in file if line.strip()]

# Subject codes with credits
SUBJECTS = {
    'HS1101': 3,  #Core for all stud together
    'MA1101': 3,  #Core for all stud together
    'CS1101': 3,  #Core for all stud together
    'ME1201': 3,  #50% stud
    'ME1101': 3,  #50% stud
    'CH1101': 3,  #50% stud

}

# SUBJECTS_REMAINING_HALF = {
    # 'HS1101': 3,  #Core for all stud together
    # 'MA1101': 3,  #Core for all stud together
    # 'CS1101': 3,  #Core for all stud together
#     'PH1201': 3,  #50% stud
#     'EE1101': 3,  #50% stud
#     'CE1101': 3,  #50% stud

# }


# Sort rolls alphabetically (case-insensitive)
sorted_rolls = sorted(lines, key=str.casefold)

# Overwrite roll1.txt with sorted data
with open('roll1.txt', 'w') as file:
    for roll in sorted_rolls:
        file.write(roll + '\n')

# Step 2: Load sorted and cleaned rolls
raw_rolls = sorted_rolls

# Step 3: Identify duplicates
roll_counts = Counter(raw_rolls)
duplicates = [roll for roll, count in roll_counts.items() if count > 1]

# Display duplicates
if duplicates:
    print("Duplicate roll numbers found in roll1.txt:")
    for dup in duplicates:
        print(f" - {dup} (count: {roll_counts[dup]})")
    print(f"Total duplicate entries: {sum(roll_counts[dup] - 1 for dup in duplicates)}")
else:
    print("No duplicate roll numbers found.")

# Step 4: Remove duplicates
roll_list = list(dict.fromkeys(raw_rolls))  # Preserves order


# Normalize subject codes
normalized_subjects = {k.strip().upper(): v for k, v in SUBJECTS.items()}

# Build SQL query
query = "INSERT INTO `reg_table` (`roll`, `sem`, `sub_code`, `c`, `grade1`, `grade2`, `type`, `session_sa`, `floated_year`, `added_on`) VALUES\n"

values = []
for roll in roll_list:
    for sub_code, c_val in normalized_subjects.items():
        values.append(f"('{roll}', {sem}, '{sub_code}', {c_val}, NULL, NULL, '{sub_type}', '{session_sa}', {floated_year}, NOW())")

query += ",\n".join(values) + ";"
from datetime import datetime

# Generate timestamp in format YYYY-MM-DD-HH-MM
timestamp = datetime.now().strftime('%Y-%m-%d-%H-%M-%S')

# Create filename with sem and timestamp & Save to SQL file
file_name = f"reg_table_insert_sem_{sem}_{timestamp}.sql"

with open(file_name, "w") as f:
    f.write(query)

print(f"SQL file '{file_name}' has been created with {len(values)} entries.")