From 5249744b01849b7158ff9cf796c550924f452320 Mon Sep 17 00:00:00 2001
From: Anson Bridges <bridges.anson@gmail.com>
Date: Fri, 3 Apr 2026 16:02:56 -0700
Subject: start er up

---
 db_builder.py | 124 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 124 insertions(+)
 create mode 100644 db_builder.py

(limited to 'db_builder.py')

diff --git a/db_builder.py b/db_builder.py
new file mode 100644
index 0000000..ed4934b
--- /dev/null
+++ b/db_builder.py
@@ -0,0 +1,124 @@
+import sqlite3
+import json
+from werkzeug.security import generate_password_hash
+
+DB_FILE = 'orchard_league.db'
+
+def get_db():
+    conn = sqlite3.connect(DB_FILE)
+    conn.row_factory = sqlite3.Row
+    return conn
+
+def init_db():
+    conn = get_db()
+    c = conn.cursor()
+
+    # Drop existing tables
+    c.execute('DROP TABLE IF EXISTS GameSubmissions')
+    c.execute('DROP TABLE IF EXISTS Games')
+    c.execute('DROP TABLE IF EXISTS SeasonTeams')
+    c.execute('DROP TABLE IF EXISTS Seasons')
+    c.execute('DROP TABLE IF EXISTS Users')
+
+    # Users Table
+    c.execute('''
+        CREATE TABLE Users (
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            username TEXT UNIQUE NOT NULL,
+            password_hash TEXT NOT NULL,
+            is_admin BOOLEAN NOT NULL DEFAULT 0,
+            team_name TEXT,
+            team_icon TEXT
+        )
+    ''')
+
+    # Seasons Table
+    c.execute('''
+        CREATE TABLE Seasons (
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            name TEXT NOT NULL,
+            status TEXT NOT NULL DEFAULT 'Scheduled', -- 'Scheduled', 'Active', 'Completed'
+            total_games INTEGER,
+            games_per_week INTEGER,
+            playoff_teams INTEGER,
+            playoff_series_length INTEGER
+        )
+    ''')
+
+    # SeasonTeams Junction Table
+    c.execute('''
+        CREATE TABLE SeasonTeams (
+            season_id INTEGER,
+            user_id INTEGER,
+            status TEXT NOT NULL DEFAULT 'Pending', -- 'Pending', 'Approved'
+            PRIMARY KEY (season_id, user_id),
+            FOREIGN KEY (season_id) REFERENCES Seasons(id),
+            FOREIGN KEY (user_id) REFERENCES Users(id)
+        )
+    ''')
+
+    # Games Table
+    c.execute('''
+        CREATE TABLE Games (
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            season_id INTEGER,
+            away_team_id INTEGER,
+            home_team_id INTEGER,
+            scheduled_date TEXT,
+            status TEXT NOT NULL DEFAULT 'Scheduled', -- 'Scheduled', 'Final', 'TBD'
+            away_score INTEGER,
+            home_score INTEGER,
+            box_score_json TEXT,
+            is_playoff BOOLEAN NOT NULL DEFAULT 0,
+            series_id INTEGER,
+            playoff_round INTEGER,
+            playoff_game_num INTEGER,
+            is_conditional BOOLEAN NOT NULL DEFAULT 0,
+            FOREIGN KEY (season_id) REFERENCES Seasons(id),
+            FOREIGN KEY (away_team_id) REFERENCES Users(id),
+            FOREIGN KEY (home_team_id) REFERENCES Users(id)
+        )
+    ''')
+
+    # GameSubmissions Table
+    c.execute('''
+        CREATE TABLE GameSubmissions (
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            game_id INTEGER,
+            submitted_by_id INTEGER,
+            away_score INTEGER,
+            home_score INTEGER,
+            box_score_json TEXT,
+            proposed_date TEXT,
+            FOREIGN KEY (game_id) REFERENCES Games(id),
+            FOREIGN KEY (submitted_by_id) REFERENCES Users(id)
+        )
+    ''')
+
+    # Create Initial Admin User
+    admin_password = generate_password_hash('admin')
+    c.execute('''
+        INSERT INTO Users (username, password_hash, is_admin, team_name)
+        VALUES (?, ?, 1, ?)
+    ''', ('admin', admin_password, 'The Adminators'))
+
+    # Create 8 Placeholder Teams
+    teams = [
+        "Yankees", "Giants", "Dodgers", "Cubs", 
+        "Red Sox", "Cardinals", "White Sox", "Braves"
+    ]
+    test_password = generate_password_hash('test')
+    for team in teams:
+        username = f"test_{team.lower().replace(' ', '_')}"
+        team_name = f"Test {team}"
+        c.execute('''
+            INSERT INTO Users (username, password_hash, is_admin, team_name)
+            VALUES (?, ?, 0, ?)
+        ''', (username, test_password, team_name))
+
+    conn.commit()
+    conn.close()
+    print("Database initialized successfully.")
+
+if __name__ == '__main__':
+    init_db()
\ No newline at end of file
-- 
cgit v1.2.3