1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
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()
|
