"""Seed the city_stats SQLite database with US city population data."""
import sqlite3
import os

DB_PATH = os.path.join(os.path.dirname(__file__), "city_database.sqlite")

CITIES = [
    ("New York", 8336817, "New York"),
    ("Los Angeles", 3979576, "California"),
    ("Chicago", 2693976, "Illinois"),
    ("Houston", 2320268, "Texas"),
    ("Phoenix", 1680992, "Arizona"),
    ("Philadelphia", 1584064, "Pennsylvania"),
    ("San Antonio", 1547253, "Texas"),
    ("San Diego", 1423851, "California"),
    ("Dallas", 1343573, "Texas"),
    ("San Jose", 1021795, "California"),
    ("Austin", 978908, "Texas"),
    ("Jacksonville", 911507, "Florida"),
    ("Fort Worth", 909585, "Texas"),
    ("Columbus", 898553, "Ohio"),
    ("Charlotte", 885708, "North Carolina"),
    ("San Francisco", 881549, "California"),
    ("Indianapolis", 876384, "Indiana"),
    ("Seattle", 753675, "Washington"),
    ("Denver", 727211, "Colorado"),
    ("Washington", 705749, "District of Columbia"),
    ("Boston", 692600, "Massachusetts"),
    ("El Paso", 681728, "Texas"),
    ("Nashville", 670820, "Tennessee"),
    ("Detroit", 670031, "Michigan"),
    ("Oklahoma City", 655057, "Oklahoma"),
    ("Portland", 654741, "Oregon"),
    ("Las Vegas", 651319, "Nevada"),
    ("Memphis", 651073, "Tennessee"),
    ("Louisville", 617638, "Kentucky"),
    ("Baltimore", 593490, "Maryland"),
    ("Milwaukee", 590157, "Wisconsin"),
    ("Albuquerque", 560513, "New Mexico"),
    ("Tucson", 548073, "Arizona"),
    ("Fresno", 531576, "California"),
    ("Sacramento", 513624, "California"),
    ("Kansas City", 495327, "Missouri"),
    ("Mesa", 518012, "Arizona"),
    ("Atlanta", 506811, "Georgia"),
    ("Omaha", 478192, "Nebraska"),
    ("Colorado Springs", 478221, "Colorado"),
    ("Raleigh", 474069, "North Carolina"),
    ("Long Beach", 462628, "California"),
    ("Virginia Beach", 449974, "Virginia"),
    ("Miami", 467963, "Florida"),
    ("Oakland", 433031, "California"),
    ("Minneapolis", 429606, "Minnesota"),
    ("Tulsa", 401190, "Oklahoma"),
    ("Arlington", 398854, "Texas"),
    ("Tampa", 399700, "Florida"),
    ("New Orleans", 390144, "Louisiana"),
    ("Wichita", 389938, "Kansas"),
    ("Cleveland", 381009, "Ohio"),
    ("Bakersfield", 384145, "California"),
    ("Aurora", 379289, "Colorado"),
    ("Anaheim", 350365, "California"),
    ("Honolulu", 345064, "Hawaii"),
    ("Santa Ana", 332318, "California"),
    ("Riverside", 328101, "California"),
    ("Corpus Christi", 326586, "Texas"),
    ("Lexington", 323780, "Kentucky"),
    ("Stockton", 312697, "California"),
    ("St. Louis", 300576, "Missouri"),
    ("Saint Paul", 308096, "Minnesota"),
    ("Henderson", 320189, "Nevada"),
    ("Pittsburgh", 300286, "Pennsylvania"),
    ("Cincinnati", 303940, "Ohio"),
    ("Anchorage", 288000, "Alaska"),
    ("Greensboro", 296710, "North Carolina"),
    ("Plano", 287677, "Texas"),
    ("Newark", 282011, "New Jersey"),
    ("Lincoln", 289102, "Nebraska"),
    ("Orlando", 287442, "Florida"),
    ("Buffalo", 255284, "New York"),
    ("Jersey City", 262075, "New Jersey"),
    ("Chula Vista", 274492, "California"),
    ("Fort Wayne", 270402, "Indiana"),
    ("St. Petersburg", 265351, "Florida"),
    ("Laredo", 262491, "Texas"),
    ("Madison", 259680, "Wisconsin"),
    ("Chandler", 261165, "Arizona"),
    ("Lubbock", 258862, "Texas"),
    ("Scottsdale", 258069, "Arizona"),
    ("Reno", 255601, "Nevada"),
    ("Glendale", 252381, "Arizona"),
    ("Norfolk", 242803, "Virginia"),
    ("Winston-Salem", 247945, "North Carolina"),
    ("North Las Vegas", 251974, "Nevada"),
    ("Gilbert", 254114, "Arizona"),
    ("Chesapeake", 244835, "Virginia"),
    ("Irving", 239798, "Texas"),
    ("Hialeah", 233339, "Florida"),
    ("Garland", 239928, "Texas"),
    ("Fremont", 241110, "California"),
    ("Richmond", 230436, "Virginia"),
    ("Boise", 228959, "Idaho"),
    ("Baton Rouge", 220236, "Louisiana"),
    ("Spokane", 222081, "Washington"),
    ("Des Moines", 214237, "Iowa"),
    ("Tacoma", 217827, "Washington"),
    ("San Bernardino", 215784, "California"),
    ("Modesto", 215030, "California"),
    ("Fontana", 214547, "California"),
    ("Santa Clarita", 212979, "California"),
    ("Birmingham", 209880, "Alabama"),
    ("Oxnard", 208881, "California"),
    ("Fayetteville", 211657, "North Carolina"),
    ("Moreno Valley", 208634, "California"),
    ("Rochester", 205695, "New York"),
    ("Glendale", 204167, "California"),
    ("Huntington Beach", 199223, "California"),
    ("Salt Lake City", 200133, "Utah"),
    ("Grand Rapids", 200217, "Michigan"),
    ("Amarillo", 200393, "Texas"),
    ("Yonkers", 200807, "New York"),
    ("Aurora", 200456, "Illinois"),
    ("Montgomery", 198525, "Alabama"),
    ("Akron", 197597, "Ohio"),
    ("Little Rock", 197312, "Arkansas"),
    ("Huntsville", 197318, "Alabama"),
    ("Augusta", 197081, "Georgia"),
    ("Port St. Lucie", 201846, "Florida"),
    ("Grand Prairie", 194543, "Texas"),
    ("Columbus", 194058, "Georgia"),
    ("Tallahassee", 194500, "Florida"),
    ("Overland Park", 195494, "Kansas"),
    ("Tempe", 195805, "Arizona"),
    ("McKinney", 199177, "Texas"),
    ("Mobile", 188720, "Alabama"),
    ("Cape Coral", 194016, "Florida"),
    ("Shreveport", 187593, "Louisiana"),
    ("Frisco", 200490, "Texas"),
    ("Knoxville", 187603, "Tennessee"),
    ("Worcester", 185428, "Massachusetts"),
    ("Brownsville", 182781, "Texas"),
    ("Vancouver", 184463, "Washington"),
    ("Fort Lauderdale", 182437, "Florida"),
    ("Sioux Falls", 192517, "South Dakota"),
    ("Ontario", 175265, "California"),
    ("Chattanooga", 182799, "Tennessee"),
    ("Providence", 179883, "Rhode Island"),
    ("Newport News", 179225, "Virginia"),
    ("Rancho Cucamonga", 177603, "California"),
    ("Santa Rosa", 178127, "California"),
    ("Peoria", 190985, "Arizona"),
    ("Oceanside", 175742, "California"),
    ("Elk Grove", 174775, "California"),
    ("Salem", 174365, "Oregon"),
    ("Pembroke Pines", 171178, "Florida"),
    ("Eugene", 172622, "Oregon"),
    ("Garden Grove", 171644, "California"),
    ("Cary", 170282, "North Carolina"),
    ("Fort Collins", 170243, "Colorado"),
    ("Corona", 169868, "California"),
    ("Springfield", 169176, "Missouri"),
    ("Jackson", 160628, "Mississippi"),
    ("Alexandria", 159428, "Virginia"),
    ("Hayward", 159203, "California"),
    ("Clarksville", 158146, "Tennessee"),
    ("Lakewood", 157935, "Colorado"),
    ("Lancaster", 159523, "California"),
    ("Salinas", 155465, "California"),
    ("Palmdale", 156904, "California"),
    ("Hollywood", 154817, "Florida"),
    ("Springfield", 153606, "Massachusetts"),
    ("Macon", 153159, "Georgia"),
    ("Kansas City", 152938, "Kansas"),
    ("Sunnyvale", 152703, "California"),
    ("Pomona", 151348, "California"),
    ("Killeen", 153095, "Texas"),
    ("Escondido", 151625, "California"),
    ("Pasadena", 153784, "Texas"),
    ("Naperville", 148449, "Illinois"),
    ("Bellevue", 148164, "Washington"),
    ("Joliet", 147344, "Illinois"),
    ("Murfreesboro", 152769, "Tennessee"),
    ("Midland", 146038, "Texas"),
    ("Rockford", 145609, "Illinois"),
    ("Paterson", 145233, "New Jersey"),
    ("Savannah", 144464, "Georgia"),
    ("Bridgeport", 144399, "Connecticut"),
    ("Torrance", 142899, "California"),
    ("McAllen", 143268, "Texas"),
    ("Syracuse", 141903, "New York"),
    ("Surprise", 141664, "Arizona"),
    ("Denton", 141541, "Texas"),
    ("Roseville", 141500, "California"),
    ("Thornton", 141464, "Colorado"),
    ("Miramar", 140823, "Florida"),
    ("Pasadena", 141029, "California"),
    ("Mesquite", 140937, "Texas"),
    ("Olathe", 140545, "Kansas"),
    ("Dayton", 140407, "Ohio"),
]


def seed():
    if os.path.exists(DB_PATH):
        os.remove(DB_PATH)

    conn = sqlite3.connect(DB_PATH)
    cur = conn.cursor()
    cur.execute("""
        CREATE TABLE city_stats (
            city_name TEXT NOT NULL,
            population INTEGER NOT NULL,
            state TEXT NOT NULL,
            PRIMARY KEY (city_name, state)
        )
    """)

    cur.executemany(
        "INSERT OR IGNORE INTO city_stats (city_name, population, state) VALUES (?, ?, ?)",
        CITIES
    )
    conn.commit()

    cur.execute("SELECT COUNT(*) FROM city_stats")
    count = cur.fetchone()[0]
    cur.execute("SELECT COUNT(DISTINCT state) FROM city_stats")
    states = cur.fetchone()[0]

    conn.close()
    print(f"Seeded {count} cities across {states} states into {DB_PATH}")


if __name__ == "__main__":
    seed()
