import pandas as pd
import numpy as np

import re
import sqlite3
from sqlalchemy import create_engine

# Years we want to process
years = [2018, 2021, 2022, 2023]
# Make a list of our numeric columns
num_cols = ['regular_pay', 'overtime_pay', 'other_pay', 'total_pay', 'benefits', 'total_pay_and_benefits']

# There are far too many job titles, so we'll add a column called job_class
# It will be Administrator, Teacher, or Support Staff
# I put the list of more than 60,000 job titles into Gemini and asked it to help me sort through them. 
# It came up with this function.
# I then (as we'll see below) vectorized the function and ran it across the job titles to sort into Teacher, Administrator,
# Support Staff, or Uncategorized.
def categorize_job_v4(title):
    # If title is null, make it uncategorized.
    if pd.isna(title):
        return "Uncategorized"

    # make it lowercase for regex
    t = str(title).lower()

    # if the lowercase title is in this list, it's uncategorized.
    if t in ['#ref!', '(blank)', 'nan', 'none', '#n/a', '***not found***'] or len(t.strip()) < 2:
        return "Uncategorized"

    # List of administrator words
    admin_kw = ['principal', 'superintendent', 'director', 'admin', 'manager', 
                'supervisor', 'coordinator', 'chief', 'dean', 'president', 
                'executive', 'board', 'chancellor', 'overseer', 'supt', 'vp']

    # List of teacher words
    teacher_kw = ['teacher', 'instructor', 'substitute', 'faculty', 'professor', 
                  'lecturer', 'educator', 'coach', 'certificated', 'teach', 
                  'tchr', 'rs/sdc', 'mild/mod', 'mild/moderate', 'mild moderate',
                  'mod/severe', 'mod severe', 'mod/sev', 'mod sev', 'rsp', 'sdc', 'special ed', 'special education', 'sped']

    # 1. Administrator Check
    if any(re.search(r'\b' + k + r'\b', t) for k in admin_kw) or 'administrator' in t:
        return "Administrator"
        
    # Check if it's an aide explicitly to prevent them from becoming teachers just because they have 'sped'
    is_aide = any(re.search(r'\b' + x + r'\b', t) for x in ['aide', 'assistant', 'para', 'paraeducator', 'para-educator', 'clerk', 'secretary', 'technician'])
    has_teacher = any(x in t for x in ['teacher', 'tchr', 'instructor'])
    
    if is_aide and not has_teacher:
        return "Support Staff"

    # 2. Teacher Check (Includes the user's new keywords and special education)
    if any(k in t for k in teacher_kw):
        return "Teacher"
        
    return "Support Staff"
# Use numpy's vectorize to minimize resources needed to apply the operation across the column
vectorized_categorize = np.vectorize(categorize_job_v4)

for year in years:
    print(year)
    # Create connection to our SQLite db
    con = sqlite3.connect(f'Data/sqlite_dbs/{year}.db')
    # Use pandas to read all the data from the salaries table using the connection
    this_data = pd.read_sql('SELECT * FROM salaries', con)
    this_data
    # Close the connection
    con.close()
    # Loop through our numeric columns and make them numeric in pandas dtypes
    for col in num_cols:
        this_data[col] = pd.to_numeric(this_data[col], errors='coerce')
        
    # We'll cut the data off at 800,000 for the upper limit and 30,000 for the lower limit since we care more about data being
    # accurate than we do about it being statistically outliers or not.
    this_data = this_data[(this_data['total_pay_and_benefits'] <= 800_000) & (this_data['total_pay_and_benefits'] >= 30_000)]
    this_data['job_class'] = vectorized_categorize(this_data['job_title'])

    # Update None values to Alameda
    this_data.loc[this_data['county'] == 'None', 'county'] = 'Alameda'
    this_data = this_data.rename(columns={'entity': 'district'})

    # We'll make a dictionary of suffix as key and dataframe as value. Then we'll use them to write to different tables
    # in the PostgreSQL database
    df_dict = {}
    # Split the data into the various groups we want to aggregate.
    # Get rid of any uncategorized (they are few; 1 in 2021 for example)
    this_data = this_data[this_data['job_class'] != 'Uncategorized']
    # This is also our Teachers, Admins, and Support Staff group
    df_dict['tas'] = this_data.copy()
    # Teachers and administrators
    df_dict['ta'] = this_data[this_data['job_class'] != 'Support Staff']
    # Just Teachers
    df_dict['t'] = this_data[this_data['job_class'] == 'Teacher']
    # Just Administrators
    df_dict['a'] = this_data[this_data['job_class'] == 'Administrator']
    # Just support staff
    df_dict['s'] = this_data[this_data['job_class'] == 'Support Staff']
    # Create database connection engine:
    engine = create_engine('postgresql+psycopg2://USER:PASS@localhost:5432/caled')

    for pre, df in df_dict.items():
        print(f'Aggregating county for {pre}')
        this_county_grouped = df.groupby(['county'])[num_cols].agg(['min', 'max', 'mean', 'sum'])
        this_county_grouped.columns = ['_'.join(col) for col in this_county_grouped.columns]
        this_county_agg = this_county_grouped.reset_index()
        this_county_agg['year'] = year

        print(f'Aggregating district for {pre}')
        this_district_grouped = df.groupby(['county', 'district'])[num_cols].agg(['min', 'max', 'mean', 'sum'])
        this_district_grouped.columns = ['_'.join(col) for col in this_district_grouped.columns]
        this_district_agg = this_district_grouped.reset_index()
        this_district_agg['year'] = year

        print(f'Writing county for {pre}')
        this_county_agg.to_sql(f'CalEd_aggbycounty{pre}', con=engine, if_exists='append', index=False)
        print(f'Writing district for {pre}')
        this_district_agg.to_sql(f'CalEd_aggbydistrict{pre}', con=engine, if_exists='append', index=False)

2018
Aggregating county for tas
Aggregating district for {pre}
Writing county for tas
Writing district for tas
Aggregating county for ta
Aggregating district for {pre}
Writing county for ta
Writing district for ta
Aggregating county for t
Aggregating district for {pre}
Writing county for t
Writing district for t
Aggregating county for a
Aggregating district for {pre}
Writing county for a
Writing district for a
Aggregating county for s
Aggregating district for {pre}
Writing county for s
Writing district for s
2021
Aggregating county for tas
Aggregating district for {pre}
Writing county for tas
Writing district for tas
Aggregating county for ta
Aggregating district for {pre}
Writing county for ta
Writing district for ta
Aggregating county for t
Aggregating district for {pre}
Writing county for t
Writing district for t
Aggregating county for a
Aggregating district for {pre}
Writing county for a
Writing district for a
Aggregating county for s
Aggregating district for {pre}
Writing county for s
Writing district for s
2022
Aggregating county for tas
Aggregating district for {pre}
Writing county for tas
Writing district for tas
Aggregating county for ta
Aggregating district for {pre}
Writing county for ta
Writing district for ta
Aggregating county for t
Aggregating district for {pre}
Writing county for t
Writing district for t
Aggregating county for a
Aggregating district for {pre}
Writing county for a
Writing district for a
Aggregating county for s
Aggregating district for {pre}
Writing county for s
Writing district for s
2023
Aggregating county for tas
Aggregating district for {pre}
Writing county for tas
Writing district for tas
Aggregating county for ta
Aggregating district for {pre}
Writing county for ta
Writing district for ta
Aggregating county for t
Aggregating district for {pre}
Writing county for t
Writing district for t
Aggregating county for a
Aggregating district for {pre}
Writing county for a
Writing district for a
Aggregating county for s
Aggregating district for {pre}
Writing county for s
Writing district for s