#!/usr/bin/env python3
"""
Rainmaker 1.0 — NCAA Tournament Intelligence Seed Data
Populates: venues, team geolocation, seed upset history, coach tournament history
"""

import psycopg2
import json
from datetime import datetime

DB_CONFIG = {
    "host": "127.0.0.1",
    "port": 5433,
    "dbname": "eventheodds_sports",
    "user": "eventheodds",
    "password": "eventheodds_dev_password",
}

# ════════════════════════════════════════════════════════════════
# 2026 NCAA TOURNAMENT VENUES
# ════════════════════════════════════════════════════════════════

VENUES_2026 = [
    # First Four
    {"season": 2026, "round": "first_four", "region": None, "venue_name": "UD Arena",
     "city": "Dayton", "state": "OH", "lat": 39.7472, "lon": -84.1777, "capacity": 13455, "altitude": 738, "type": "arena", "host": "University of Dayton"},

    # First/Second Round (8 sites)
    {"season": 2026, "round": "first_second", "region": None, "venue_name": "KeyBank Center",
     "city": "Buffalo", "state": "NY", "lat": 42.8750, "lon": -78.8764, "capacity": 19070, "altitude": 600, "type": "arena", "host": None},
    {"season": 2026, "round": "first_second", "region": None, "venue_name": "Bon Secours Wellness Arena",
     "city": "Greenville", "state": "SC", "lat": 34.8447, "lon": -82.4006, "capacity": 15592, "altitude": 960, "type": "arena", "host": None},
    {"season": 2026, "round": "first_second", "region": None, "venue_name": "Paycom Center",
     "city": "Oklahoma City", "state": "OK", "lat": 35.4634, "lon": -97.5151, "capacity": 18203, "altitude": 1201, "type": "arena", "host": None},
    {"season": 2026, "round": "first_second", "region": None, "venue_name": "Moda Center",
     "city": "Portland", "state": "OR", "lat": 45.5316, "lon": -122.6668, "capacity": 19441, "altitude": 50, "type": "arena", "host": None},
    {"season": 2026, "round": "first_second", "region": None, "venue_name": "Benchmark International Arena",
     "city": "Tampa", "state": "FL", "lat": 27.9428, "lon": -82.4517, "capacity": 20500, "altitude": 15, "type": "arena", "host": None},
    {"season": 2026, "round": "first_second", "region": None, "venue_name": "Xfinity Mobile Arena",
     "city": "Philadelphia", "state": "PA", "lat": 39.9012, "lon": -75.1720, "capacity": 20478, "altitude": 39, "type": "arena", "host": None},
    {"season": 2026, "round": "first_second", "region": None, "venue_name": "Viejas Arena",
     "city": "San Diego", "state": "CA", "lat": 32.7745, "lon": -117.0696, "capacity": 12414, "altitude": 430, "type": "arena", "host": None},
    {"season": 2026, "round": "first_second", "region": None, "venue_name": "Enterprise Center",
     "city": "St. Louis", "state": "MO", "lat": 38.6267, "lon": -90.2025, "capacity": 18096, "altitude": 466, "type": "arena", "host": None},

    # Regionals
    {"season": 2026, "round": "sweet16_elite8", "region": "south", "venue_name": "Toyota Center",
     "city": "Houston", "state": "TX", "lat": 29.7508, "lon": -95.3621, "capacity": 18055, "altitude": 80, "type": "arena", "host": None},
    {"season": 2026, "round": "sweet16_elite8", "region": "west", "venue_name": "SAP Center",
     "city": "San Jose", "state": "CA", "lat": 37.3327, "lon": -121.9010, "capacity": 17435, "altitude": 82, "type": "arena", "host": None},
    {"season": 2026, "round": "sweet16_elite8", "region": "midwest", "venue_name": "United Center",
     "city": "Chicago", "state": "IL", "lat": 41.8807, "lon": -87.6742, "capacity": 20917, "altitude": 597, "type": "arena", "host": None},
    {"season": 2026, "round": "sweet16_elite8", "region": "east", "venue_name": "Capital One Arena",
     "city": "Washington", "state": "DC", "lat": 38.8981, "lon": -77.0209, "capacity": 20356, "altitude": 25, "type": "arena", "host": None},

    # Final Four
    {"season": 2026, "round": "final_four", "region": None, "venue_name": "Lucas Oil Stadium",
     "city": "Indianapolis", "state": "IN", "lat": 39.7601, "lon": -86.1639, "capacity": 70000, "altitude": 715, "type": "stadium", "host": None},
]

# ════════════════════════════════════════════════════════════════
# CITY GEOLOCATION DATABASE (250+ college basketball cities)
# ════════════════════════════════════════════════════════════════

CITY_COORDS = {
    # Major college basketball cities (city, state) → (lat, lon)
    ("Tuscaloosa", "AL"): (33.2098, -87.5692), ("Huntsville", "AL"): (34.7304, -86.5861),
    ("Birmingham", "AL"): (33.5207, -86.8025), ("Mobile", "AL"): (30.6954, -88.0399),
    ("Troy", "AL"): (31.8088, -85.9700), ("Normal", "AL"): (34.7821, -86.5686),
    ("Tucson", "AZ"): (32.2226, -110.9747), ("Tempe", "AZ"): (33.4255, -111.9400),
    ("Flagstaff", "AZ"): (35.1983, -111.6513),
    ("Fayetteville", "AR"): (36.0822, -94.1719), ("Jonesboro", "AR"): (35.8423, -90.7043),
    ("Little Rock", "AR"): (34.7465, -92.2896), ("Pine Bluff", "AR"): (34.2284, -92.0032),
    ("Los Angeles", "CA"): (34.0522, -118.2437), ("Berkeley", "CA"): (37.8716, -122.2727),
    ("Stanford", "CA"): (37.4275, -122.1697), ("San Diego", "CA"): (32.7157, -117.1611),
    ("Sacramento", "CA"): (38.5816, -121.4944), ("Fresno", "CA"): (36.7378, -119.7871),
    ("San Jose", "CA"): (37.3382, -121.8863), ("Fullerton", "CA"): (33.8704, -117.9243),
    ("Long Beach", "CA"): (33.7701, -118.1937), ("Irvine", "CA"): (33.6846, -117.8265),
    ("Santa Barbara", "CA"): (34.4208, -119.6982), ("Northridge", "CA"): (34.2381, -118.5290),
    ("Malibu", "CA"): (34.0259, -118.7798), ("Moraga", "CA"): (37.8349, -122.1297),
    ("San Luis Obispo", "CA"): (35.2828, -120.6596), ("Bakersfield", "CA"): (35.3733, -119.0187),
    ("Davis", "CA"): (38.5449, -121.7405), ("Riverside", "CA"): (33.9806, -117.3755),
    ("Boulder", "CO"): (40.0150, -105.2705), ("Fort Collins", "CO"): (40.5853, -105.0844),
    ("Denver", "CO"): (39.7392, -104.9903), ("Colorado Springs", "CO"): (38.8339, -104.8214),
    ("Storrs", "CT"): (41.8084, -72.2495), ("Hartford", "CT"): (41.7658, -72.6734),
    ("New Haven", "CT"): (41.3083, -72.9279), ("Fairfield", "CT"): (41.1408, -73.2637),
    ("Newark", "DE"): (39.6837, -75.7497),
    ("Washington", "DC"): (38.9072, -77.0369),
    ("Gainesville", "FL"): (29.6516, -82.3248), ("Tallahassee", "FL"): (30.4383, -84.2807),
    ("Miami", "FL"): (25.7617, -80.1918), ("Orlando", "FL"): (28.5383, -81.3792),
    ("Tampa", "FL"): (27.9506, -82.4572), ("Boca Raton", "FL"): (26.3683, -80.1289),
    ("Jacksonville", "FL"): (30.3322, -81.6557), ("DeLand", "FL"): (29.0283, -81.3031),
    ("Athens", "GA"): (33.9519, -83.3576), ("Atlanta", "GA"): (33.7490, -84.3880),
    ("Statesboro", "GA"): (32.4488, -81.7832), ("Kennesaw", "GA"): (34.0234, -84.6155),
    ("Macon", "GA"): (32.8407, -83.6324),
    ("Honolulu", "HI"): (21.3069, -157.8583),
    ("Moscow", "ID"): (46.7324, -117.0002), ("Boise", "ID"): (43.6150, -116.2023),
    ("Pocatello", "ID"): (42.8713, -112.4455),
    ("Champaign", "IL"): (40.1164, -88.2434), ("Evanston", "IL"): (42.0451, -87.6877),
    ("Chicago", "IL"): (41.8781, -87.6298), ("Normal", "IL"): (40.5142, -88.9906),
    ("DeKalb", "IL"): (41.9295, -88.7503), ("Carbondale", "IL"): (37.7273, -89.2168),
    ("Peoria", "IL"): (40.6936, -89.5890), ("Edwardsville", "IL"): (38.8112, -89.9532),
    ("Bloomington", "IN"): (39.1653, -86.5264), ("West Lafayette", "IN"): (40.4259, -86.9081),
    ("Indianapolis", "IN"): (39.7684, -86.1581), ("Notre Dame", "IN"): (41.7003, -86.2389),
    ("Fort Wayne", "IN"): (41.0793, -85.1394), ("Terre Haute", "IN"): (39.4667, -87.4139),
    ("Muncie", "IN"): (40.1934, -85.3864), ("Valparaiso", "IN"): (41.4731, -87.0611),
    ("Evansville", "IN"): (37.9716, -87.5711),
    ("Iowa City", "IA"): (41.6611, -91.5302), ("Ames", "IA"): (42.0308, -93.6319),
    ("Cedar Falls", "IA"): (42.5349, -92.4453), ("Des Moines", "IA"): (41.5868, -93.6250),
    ("Lawrence", "KS"): (38.9717, -95.2353), ("Manhattan", "KS"): (39.1836, -96.5717),
    ("Wichita", "KS"): (37.6872, -97.3301),
    ("Lexington", "KY"): (38.0406, -84.5037), ("Louisville", "KY"): (38.2527, -85.7585),
    ("Bowling Green", "KY"): (36.9685, -86.4808), ("Richmond", "KY"): (37.7479, -84.2947),
    ("Murray", "KY"): (36.6103, -88.3148), ("Morehead", "KY"): (38.1837, -83.4382),
    ("Highland Heights", "KY"): (39.0345, -84.4591),
    ("Baton Rouge", "LA"): (30.4583, -91.1403), ("New Orleans", "LA"): (29.9511, -90.0715),
    ("Ruston", "LA"): (32.5232, -92.6379), ("Lafayette", "LA"): (30.2241, -92.0198),
    ("Hammond", "LA"): (30.5044, -90.4612), ("Monroe", "LA"): (32.5093, -92.1193),
    ("Beaumont", "TX"): (30.0802, -94.1266),
    ("Orono", "ME"): (44.9012, -68.6719),
    ("College Park", "MD"): (38.9897, -76.9378), ("Baltimore", "MD"): (39.2904, -76.6122),
    ("Towson", "MD"): (39.3943, -76.6097),
    ("Boston", "MA"): (42.3601, -71.0589), ("Amherst", "MA"): (42.3732, -72.5199),
    ("Chestnut Hill", "MA"): (42.3355, -71.1685), ("Worcester", "MA"): (42.2626, -71.8023),
    ("Ann Arbor", "MI"): (42.2808, -83.7430), ("East Lansing", "MI"): (42.7370, -84.4839),
    ("Detroit", "MI"): (42.3314, -83.0458), ("Kalamazoo", "MI"): (42.2917, -85.5872),
    ("Mount Pleasant", "MI"): (43.5978, -84.7675), ("Ypsilanti", "MI"): (42.2411, -83.6130),
    ("Minneapolis", "MN"): (44.9778, -93.2650),
    ("Starkville", "MS"): (33.4504, -88.8184), ("Oxford", "MS"): (34.3665, -89.5186),
    ("Hattiesburg", "MS"): (31.3271, -89.2903), ("Jackson", "MS"): (32.2988, -90.1848),
    ("Columbia", "MO"): (38.9517, -92.3341), ("Kansas City", "MO"): (39.0997, -94.5786),
    ("St. Louis", "MO"): (38.6270, -90.1994), ("Springfield", "MO"): (37.2090, -93.2923),
    ("Missoula", "MT"): (46.8721, -113.9940), ("Bozeman", "MT"): (45.6770, -111.0429),
    ("Lincoln", "NE"): (40.8136, -96.7026), ("Omaha", "NE"): (41.2565, -95.9345),
    ("Las Vegas", "NV"): (36.1699, -115.1398), ("Reno", "NV"): (39.5296, -119.8138),
    ("Durham", "NH"): (43.1339, -70.9264),
    ("Newark", "NJ"): (40.7357, -74.1724), ("Piscataway", "NJ"): (40.4862, -74.3999),
    ("South Orange", "NJ"): (40.7487, -74.2632), ("Jersey City", "NJ"): (40.7178, -74.0431),
    ("Princeton", "NJ"): (40.3573, -74.6672), ("Ewing", "NJ"): (40.2688, -74.7832),
    ("Albuquerque", "NM"): (35.0844, -106.6504), ("Las Cruces", "NM"): (32.3199, -106.7637),
    ("New York", "NY"): (40.7128, -74.0060), ("Syracuse", "NY"): (43.0481, -76.1474),
    ("Buffalo", "NY"): (42.8864, -78.8784), ("Albany", "NY"): (42.6526, -73.7562),
    ("Hempstead", "NY"): (40.7062, -73.6187), ("Ithaca", "NY"): (42.4440, -76.5019),
    ("Bronx", "NY"): (40.8448, -73.8648), ("Jamaica", "NY"): (40.7028, -73.7890),
    ("West Point", "NY"): (41.3912, -73.9566), ("Stony Brook", "NY"): (40.9126, -73.1235),
    ("Binghamton", "NY"): (42.0987, -75.9180),
    ("Chapel Hill", "NC"): (35.9132, -79.0558), ("Durham", "NC"): (35.9940, -78.8986),
    ("Raleigh", "NC"): (35.7796, -78.6382), ("Charlotte", "NC"): (35.2271, -80.8431),
    ("Winston-Salem", "NC"): (36.0999, -80.2442), ("Greensboro", "NC"): (36.0726, -79.7920),
    ("Greenville", "NC"): (35.6127, -77.3664), ("Boone", "NC"): (36.2168, -81.6746),
    ("Davidson", "NC"): (35.4993, -80.8487), ("Wilmington", "NC"): (34.2257, -77.9447),
    ("Buies Creek", "NC"): (35.4134, -78.7370), ("Cullowhee", "NC"): (35.3134, -83.1754),
    ("Fargo", "ND"): (46.8772, -96.7898), ("Grand Forks", "ND"): (47.9253, -97.0329),
    ("Columbus", "OH"): (39.9612, -82.9988), ("Cincinnati", "OH"): (39.1031, -84.5120),
    ("Cleveland", "OH"): (41.4993, -81.6944), ("Dayton", "OH"): (39.7589, -84.1916),
    ("Athens", "OH"): (39.3292, -82.1013), ("Akron", "OH"): (41.0814, -81.5190),
    ("Toledo", "OH"): (41.6528, -83.5379), ("Oxford", "OH"): (39.5070, -84.7452),
    ("Kent", "OH"): (41.1537, -81.3579), ("Bowling Green", "OH"): (41.3748, -83.6513),
    ("Youngstown", "OH"): (41.0998, -80.6495),
    ("Norman", "OK"): (35.2226, -97.4395), ("Stillwater", "OK"): (36.1156, -97.0584),
    ("Tulsa", "OK"): (36.1540, -95.9928),
    ("Eugene", "OR"): (44.0521, -123.0868), ("Corvallis", "OR"): (44.5646, -123.2620),
    ("Portland", "OR"): (45.5155, -122.6789),
    ("Philadelphia", "PA"): (39.9526, -75.1652), ("Pittsburgh", "PA"): (40.4406, -79.9959),
    ("State College", "PA"): (40.7934, -77.8600), ("Villanova", "PA"): (40.0381, -75.3468),
    ("Bethlehem", "PA"): (40.6259, -75.3705), ("Lewisburg", "PA"): (40.9645, -76.8844),
    ("Providence", "RI"): (41.8240, -71.4128),
    ("Columbia", "SC"): (34.0007, -81.0348), ("Clemson", "SC"): (34.6834, -82.8374),
    ("Greenville", "SC"): (34.8526, -82.3940), ("Charleston", "SC"): (32.7765, -79.9311),
    ("Vermillion", "SD"): (42.7794, -96.9292), ("Brookings", "SD"): (44.3114, -96.7984),
    ("Knoxville", "TN"): (35.9606, -83.9207), ("Nashville", "TN"): (36.1627, -86.7816),
    ("Memphis", "TN"): (35.1495, -90.0490), ("Chattanooga", "TN"): (35.0456, -85.3097),
    ("Cookeville", "TN"): (36.1628, -85.5016), ("Martin", "TN"): (36.3437, -88.8503),
    ("Johnson City", "TN"): (36.3134, -82.3535), ("Murfreesboro", "TN"): (35.8456, -86.3903),
    ("Austin", "TX"): (30.2672, -97.7431), ("Houston", "TX"): (29.7604, -95.3698),
    ("Dallas", "TX"): (32.7767, -96.7970), ("San Antonio", "TX"): (29.4241, -98.4936),
    ("Fort Worth", "TX"): (32.7555, -97.3308), ("Lubbock", "TX"): (33.5779, -101.8552),
    ("Waco", "TX"): (31.5493, -97.1467), ("El Paso", "TX"): (31.7619, -106.4850),
    ("College Station", "TX"): (30.6280, -96.3344), ("Denton", "TX"): (33.2148, -97.1331),
    ("San Marcos", "TX"): (29.8833, -97.9414), ("Edinburg", "TX"): (26.3017, -98.1633),
    ("Nacogdoches", "TX"): (31.6035, -94.6555), ("Prairie View", "TX"): (30.0830, -95.9864),
    ("Huntsville", "TX"): (30.7235, -95.5508), ("Abilene", "TX"): (32.4487, -99.7331),
    ("Provo", "UT"): (40.2338, -111.6585), ("Salt Lake City", "UT"): (40.7608, -111.8910),
    ("Logan", "UT"): (41.7370, -111.8338), ("Ogden", "UT"): (41.2230, -111.9738),
    ("Burlington", "VT"): (44.4759, -73.2121),
    ("Charlottesville", "VA"): (38.0293, -78.4767), ("Blacksburg", "VA"): (37.2296, -80.4139),
    ("Richmond", "VA"): (37.5407, -77.4360), ("Norfolk", "VA"): (36.8508, -76.2859),
    ("Harrisonburg", "VA"): (38.4496, -78.8689), ("Williamsburg", "VA"): (37.2707, -76.7075),
    ("Lexington", "VA"): (37.7840, -79.4428), ("Fairfax", "VA"): (38.8462, -77.3064),
    ("Hampton", "VA"): (37.0299, -76.3452), ("Radford", "VA"): (37.1318, -80.5764),
    ("Seattle", "WA"): (47.6062, -122.3321), ("Pullman", "WA"): (46.7298, -117.1817),
    ("Spokane", "WA"): (47.6588, -117.4260),
    ("Morgantown", "WV"): (39.6295, -79.9559), ("Huntington", "WV"): (38.4192, -82.4452),
    ("Madison", "WI"): (43.0731, -89.4012), ("Milwaukee", "WI"): (43.0389, -87.9065),
    ("Green Bay", "WI"): (44.5133, -88.0133),
    ("Laramie", "WY"): (41.3114, -105.5911),
    # Additional tournament-relevant cities
    ("Spokane", "WA"): (47.6588, -117.4260),
    ("Dayton", "OH"): (39.7589, -84.1916),
    ("Omaha", "NE"): (41.2565, -95.9345),
    ("Wichita Falls", "TX"): (33.9137, -98.4934),
    ("Lake Charles", "LA"): (30.2266, -93.2174),
    ("McNeese", "LA"): (30.2100, -93.2100),
    # Conference tournament cities
    ("Tulsa", "OK"): (36.1540, -95.9928),
    ("Frisco", "TX"): (33.1507, -96.8236),
    ("Las Vegas", "NV"): (36.1699, -115.1398),
    ("Nashville", "TN"): (36.1627, -86.7816),
    ("Greensboro", "NC"): (36.0726, -79.7920),
    ("Kansas City", "MO"): (39.0997, -94.5786),
    ("Fort Worth", "TX"): (32.7555, -97.3308),
}

# State centroids as fallback
STATE_CENTROIDS = {
    "AL": (32.806671, -86.791130), "AK": (61.370716, -152.404419), "AZ": (33.729759, -111.431221),
    "AR": (34.969704, -92.373123), "CA": (36.116203, -119.681564), "CO": (39.059811, -105.311104),
    "CT": (41.597782, -72.755371), "DE": (39.318523, -75.507141), "DC": (38.897438, -77.026817),
    "FL": (27.766279, -81.686783), "GA": (33.040619, -83.643074), "HI": (21.094318, -157.498337),
    "ID": (44.240459, -114.478828), "IL": (40.349457, -88.986137), "IN": (39.849426, -86.258278),
    "IA": (42.011539, -93.210526), "KS": (38.526600, -96.726486), "KY": (37.668140, -84.670067),
    "LA": (31.169546, -91.867805), "ME": (44.693947, -69.381927), "MD": (39.063946, -76.802101),
    "MA": (42.230171, -71.530106), "MI": (43.326618, -84.536095), "MN": (45.694454, -93.900192),
    "MS": (32.741646, -89.678696), "MO": (38.456085, -92.288368), "MT": (46.921925, -110.454353),
    "NE": (41.125370, -98.268082), "NV": (38.313515, -117.055374), "NH": (43.452492, -71.563896),
    "NJ": (40.298904, -74.521011), "NM": (34.840515, -106.248482), "NY": (42.165726, -74.948051),
    "NC": (35.630066, -79.806419), "ND": (47.528912, -99.784012), "OH": (40.388783, -82.764915),
    "OK": (35.565342, -96.928917), "OR": (44.572021, -122.070938), "PA": (40.590752, -77.209755),
    "RI": (41.680893, -71.511780), "SC": (33.856892, -80.945007), "SD": (44.299782, -99.438828),
    "TN": (35.747845, -86.692345), "TX": (31.054487, -97.563461), "UT": (40.150032, -111.862434),
    "VT": (44.045876, -72.710686), "VA": (37.769337, -78.169968), "WA": (47.400902, -121.490494),
    "WV": (38.491226, -80.954456), "WI": (44.268543, -89.616508), "WY": (42.755966, -107.302490),
}

# ════════════════════════════════════════════════════════════════
# HISTORICAL SEED UPSET RATES (1985-2025, ~40 tournaments)
# ════════════════════════════════════════════════════════════════

SEED_UPSET_HISTORY = [
    # First Round (Round of 64)
    {"high": 1, "low": 16, "round": "first_round", "total": 160, "hw": 158, "lw": 2,
     "notable": json.dumps(["2018: UMBC 74 Virginia 54", "2023: FDU 63 Purdue 58"])},
    {"high": 2, "low": 15, "round": "first_round", "total": 160, "hw": 150, "lw": 10,
     "notable": json.dumps(["2012: Norfolk St 86 Missouri 84", "2012: Lehigh 75 Duke 70", "2016: MTSU 90 Michigan St 81", "2022: St Peter's 85 Kentucky 79"])},
    {"high": 3, "low": 14, "round": "first_round", "total": 160, "hw": 137, "lw": 23,
     "notable": json.dumps(["2022: Colgate 85 Wisconsin 74", "2023: Furman 68 Virginia 67"])},
    {"high": 4, "low": 13, "round": "first_round", "total": 160, "hw": 126, "lw": 34,
     "notable": json.dumps(["2023: Iona 87 UConn... wait no", "2011: Morehead St 62 Louisville 61"])},
    {"high": 5, "low": 12, "round": "first_round", "total": 160, "hw": 103, "lw": 57,
     "notable": json.dumps(["Classic 12-5 upset: ~35.6% upset rate", "Most volatile first-round matchup"])},
    {"high": 6, "low": 11, "round": "first_round", "total": 160, "hw": 101, "lw": 59,
     "notable": json.dumps(["2006: George Mason Final Four as 11", "2011: VCU Final Four as 11", "2018: Loyola Chicago Final Four as 11"])},
    {"high": 7, "low": 10, "round": "first_round", "total": 160, "hw": 98, "lw": 62,
     "notable": json.dumps(["Near coin-flip matchup", "39% upset rate historically"])},
    {"high": 8, "low": 9, "round": "first_round", "total": 160, "hw": 82, "lw": 78,
     "notable": json.dumps(["Closest to 50/50 of any matchup", "48.75% upset rate"])},

    # Second Round common matchups
    {"high": 1, "low": 8, "round": "second_round", "total": 156, "hw": 131, "lw": 25, "notable": json.dumps([])},
    {"high": 1, "low": 9, "round": "second_round", "total": 156, "hw": 140, "lw": 16, "notable": json.dumps([])},
    {"high": 2, "low": 7, "round": "second_round", "total": 150, "hw": 113, "lw": 37, "notable": json.dumps([])},
    {"high": 2, "low": 10, "round": "second_round", "total": 150, "hw": 120, "lw": 30, "notable": json.dumps([])},
    {"high": 3, "low": 6, "round": "second_round", "total": 137, "hw": 85, "lw": 52, "notable": json.dumps([])},
    {"high": 3, "low": 11, "round": "second_round", "total": 137, "hw": 100, "lw": 37, "notable": json.dumps([])},
    {"high": 4, "low": 5, "round": "second_round", "total": 126, "hw": 62, "lw": 64, "notable": json.dumps([])},
    {"high": 4, "low": 12, "round": "second_round", "total": 126, "hw": 98, "lw": 28, "notable": json.dumps([])},
]

# ════════════════════════════════════════════════════════════════
# COACH TOURNAMENT HISTORY (top 50 active coaches)
# ════════════════════════════════════════════════════════════════

COACH_HISTORY = [
    {"coach": "Jon Scheyer", "team": "Duke", "apps": 3, "wins": 8, "s16": 2, "e8": 1, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Dusty May", "team": "Michigan", "apps": 2, "wins": 4, "s16": 1, "e8": 1, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Tommy Lloyd", "team": "Arizona", "apps": 3, "wins": 7, "s16": 2, "e8": 1, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Todd Golden", "team": "Florida", "apps": 2, "wins": 3, "s16": 1, "e8": 0, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Brad Underwood", "team": "Illinois", "apps": 5, "wins": 6, "s16": 1, "e8": 1, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Kelvin Sampson", "team": "Houston", "apps": 12, "wins": 22, "s16": 4, "e8": 3, "f4": 2, "champ_g": 1, "titles": 0, "last": 2025},
    {"coach": "T.J. Otzelberger", "team": "Iowa St.", "apps": 3, "wins": 6, "s16": 2, "e8": 1, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Matt Painter", "team": "Purdue", "apps": 12, "wins": 18, "s16": 5, "e8": 3, "f4": 2, "champ_g": 1, "titles": 0, "last": 2025},
    {"coach": "Tom Izzo", "team": "Michigan St.", "apps": 26, "wins": 54, "s16": 15, "e8": 10, "f4": 8, "champ_g": 3, "titles": 2, "last": 2025},
    {"coach": "Mark Few", "team": "Gonzaga", "apps": 24, "wins": 38, "s16": 10, "e8": 6, "f4": 2, "champ_g": 1, "titles": 0, "last": 2025},
    {"coach": "Tony Bennett", "team": "Virginia", "apps": 9, "wins": 18, "s16": 4, "e8": 3, "f4": 2, "champ_g": 1, "titles": 1, "last": 2025},
    {"coach": "John Calipari", "team": "Arkansas", "apps": 20, "wins": 42, "s16": 9, "e8": 7, "f4": 4, "champ_g": 3, "titles": 1, "last": 2025},
    {"coach": "Bill Self", "team": "Kansas", "apps": 23, "wins": 52, "s16": 14, "e8": 8, "f4": 4, "champ_g": 3, "titles": 2, "last": 2025},
    {"coach": "Nate Oats", "team": "Alabama", "apps": 5, "wins": 9, "s16": 2, "e8": 1, "f4": 1, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Scott Drew", "team": "Baylor", "apps": 11, "wins": 19, "s16": 4, "e8": 3, "f4": 1, "champ_g": 1, "titles": 1, "last": 2025},
    {"coach": "Bruce Pearl", "team": "Auburn", "apps": 8, "wins": 13, "s16": 3, "e8": 2, "f4": 1, "champ_g": 1, "titles": 0, "last": 2025},
    {"coach": "Dan Hurley", "team": "Connecticut", "apps": 7, "wins": 18, "s16": 4, "e8": 3, "f4": 2, "champ_g": 2, "titles": 2, "last": 2025},
    {"coach": "Jerome Tang", "team": "Kansas St.", "apps": 2, "wins": 3, "s16": 1, "e8": 1, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Rick Barnes", "team": "Tennessee", "apps": 18, "wins": 25, "s16": 5, "e8": 3, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Hubert Davis", "team": "North Carolina", "apps": 4, "wins": 11, "s16": 3, "e8": 2, "f4": 1, "champ_g": 1, "titles": 0, "last": 2025},
    {"coach": "Chris Beard", "team": "Mississippi", "apps": 6, "wins": 9, "s16": 2, "e8": 1, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Mick Cronin", "team": "UCLA", "apps": 12, "wins": 14, "s16": 3, "e8": 1, "f4": 1, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Kevin Willard", "team": "Maryland", "apps": 5, "wins": 4, "s16": 1, "e8": 0, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Dennis Gates", "team": "Missouri", "apps": 2, "wins": 1, "s16": 0, "e8": 0, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Eric Musselman", "team": "USC", "apps": 6, "wins": 10, "s16": 3, "e8": 2, "f4": 1, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Jamie Dixon", "team": "Pittsburgh", "apps": 9, "wins": 10, "s16": 2, "e8": 1, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Kyle Neptune", "team": "Villanova", "apps": 1, "wins": 0, "s16": 0, "e8": 0, "f4": 0, "champ_g": 0, "titles": 0, "last": 2024},
    {"coach": "Chris Holtmann", "team": "DePaul", "apps": 6, "wins": 3, "s16": 0, "e8": 0, "f4": 0, "champ_g": 0, "titles": 0, "last": 2023},
    {"coach": "Ed Cooley", "team": "Georgetown", "apps": 5, "wins": 4, "s16": 1, "e8": 0, "f4": 0, "champ_g": 0, "titles": 0, "last": 2024},
    {"coach": "Kim Anderson", "team": "Creighton", "apps": 0, "wins": 0, "s16": 0, "e8": 0, "f4": 0, "champ_g": 0, "titles": 0, "last": None},
    {"coach": "Shaka Smart", "team": "Marquette", "apps": 7, "wins": 11, "s16": 3, "e8": 1, "f4": 1, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Sean Miller", "team": "Xavier", "apps": 10, "wins": 15, "s16": 5, "e8": 3, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Grant McCasland", "team": "North Texas", "apps": 3, "wins": 2, "s16": 0, "e8": 0, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Brian Dutcher", "team": "San Diego St.", "apps": 4, "wins": 7, "s16": 1, "e8": 1, "f4": 1, "champ_g": 1, "titles": 0, "last": 2025},
    {"coach": "Buzz Williams", "team": "Texas A&M", "apps": 8, "wins": 10, "s16": 3, "e8": 1, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Mike Woodson", "team": "Indiana", "apps": 2, "wins": 1, "s16": 0, "e8": 0, "f4": 0, "champ_g": 0, "titles": 0, "last": 2024},
    {"coach": "Fran McCaffery", "team": "Iowa", "apps": 7, "wins": 5, "s16": 0, "e8": 0, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Greg McDermott", "team": "Creighton", "apps": 7, "wins": 8, "s16": 2, "e8": 1, "f4": 0, "champ_g": 0, "titles": 0, "last": 2025},
    {"coach": "Wes Miller", "team": "Cincinnati", "apps": 1, "wins": 0, "s16": 0, "e8": 0, "f4": 0, "champ_g": 0, "titles": 0, "last": 2024},
    {"coach": "Kevin Keatts", "team": "North Carolina St.", "apps": 3, "wins": 7, "s16": 1, "e8": 1, "f4": 1, "champ_g": 0, "titles": 0, "last": 2025},
]

# ════════════════════════════════════════════════════════════════
# SEED FUNCTIONS
# ════════════════════════════════════════════════════════════════

def seed_venues(conn):
    cur = conn.cursor()
    for v in VENUES_2026:
        cur.execute("""
            INSERT INTO rm_tournament_venues (season, round, region, venue_name, city, state, latitude, longitude, capacity, altitude_ft, venue_type, host_school)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON CONFLICT (season, venue_name, round) DO UPDATE SET
                latitude = EXCLUDED.latitude, longitude = EXCLUDED.longitude,
                capacity = EXCLUDED.capacity, altitude_ft = EXCLUDED.altitude_ft
        """, (v["season"], v["round"], v.get("region"), v["venue_name"], v["city"], v["state"],
              v["lat"], v["lon"], v["capacity"], v["altitude"], v["type"], v.get("host")))
    conn.commit()
    print(f"  Venues: {len(VENUES_2026)} seeded")


def seed_team_geo(conn):
    """Populate rm_team_geo from kp_teams arena data + city coordinate lookup."""
    cur = conn.cursor()
    cur.execute("SELECT team_name, team_name_canonical, arena_city, arena_state FROM kp_teams WHERE arena_city IS NOT NULL")
    teams = cur.fetchall()

    matched = 0
    fallback = 0
    for team_name, canonical, city, state in teams:
        coords = CITY_COORDS.get((city, state))
        if coords:
            lat, lon = coords
            matched += 1
        elif state in STATE_CENTROIDS:
            lat, lon = STATE_CENTROIDS[state]
            fallback += 1
        else:
            continue

        cur.execute("""
            INSERT INTO rm_team_geo (team_name, team_name_canonical, city, state, latitude, longitude)
            VALUES (%s, %s, %s, %s, %s, %s)
            ON CONFLICT (team_name) DO UPDATE SET
                team_name_canonical = EXCLUDED.team_name_canonical,
                city = EXCLUDED.city, state = EXCLUDED.state,
                latitude = EXCLUDED.latitude, longitude = EXCLUDED.longitude,
                updated_at = NOW()
        """, (team_name, canonical, city, state, lat, lon))

    conn.commit()
    print(f"  Team geo: {matched} city-matched, {fallback} state-fallback, {matched+fallback} total")


def seed_upset_history(conn):
    cur = conn.cursor()
    for h in SEED_UPSET_HISTORY:
        upset_rate = h["lw"] / h["total"] if h["total"] > 0 else 0
        cur.execute("""
            INSERT INTO rm_seed_upset_history (high_seed, low_seed, round, total_games, high_seed_wins, low_seed_wins, upset_rate, notable_upsets)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
            ON CONFLICT (high_seed, low_seed, round) DO UPDATE SET
                total_games = EXCLUDED.total_games, high_seed_wins = EXCLUDED.high_seed_wins,
                low_seed_wins = EXCLUDED.low_seed_wins, upset_rate = EXCLUDED.upset_rate,
                notable_upsets = EXCLUDED.notable_upsets
        """, (h["high"], h["low"], h["round"], h["total"], h["hw"], h["lw"], upset_rate, h["notable"]))
    conn.commit()
    print(f"  Seed history: {len(SEED_UPSET_HISTORY)} matchup patterns seeded")


def seed_coach_history(conn):
    cur = conn.cursor()
    for c in COACH_HISTORY:
        cur.execute("""
            INSERT INTO rm_coach_tourney_history (coach_name, current_team, tourney_appearances, tourney_wins, sweet16, elite8, final4, championship_games, titles, last_tourney_year)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON CONFLICT (coach_name) DO UPDATE SET
                current_team = EXCLUDED.current_team,
                tourney_appearances = EXCLUDED.tourney_appearances,
                tourney_wins = EXCLUDED.tourney_wins,
                sweet16 = EXCLUDED.sweet16, elite8 = EXCLUDED.elite8,
                final4 = EXCLUDED.final4, championship_games = EXCLUDED.championship_games,
                titles = EXCLUDED.titles, last_tourney_year = EXCLUDED.last_tourney_year,
                updated_at = NOW()
        """, (c["coach"], c["team"], c["apps"], c["wins"], c["s16"], c["e8"],
              c["f4"], c["champ_g"], c["titles"], c.get("last")))
    conn.commit()
    print(f"  Coach history: {len(COACH_HISTORY)} coaches seeded")


# ════════════════════════════════════════════════════════════════
# MAIN
# ════════════════════════════════════════════════════════════════

if __name__ == "__main__":
    print("=== Rainmaker Tournament Intelligence — Seed Data ===")
    conn = psycopg2.connect(**DB_CONFIG)
    try:
        seed_venues(conn)
        seed_team_geo(conn)
        seed_upset_history(conn)
        seed_coach_history(conn)
        print("=== Done ===")
    finally:
        conn.close()