database/schema.sql

-- Teams table
CREATE TABLE IF NOT EXISTS teams (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Submissions table
CREATE TABLE IF NOT EXISTS submissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    team_id INTEGER NOT NULL,
    file_path TEXT NOT NULL,
    submit_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    score REAL,
    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'processing', 'completed', 'error', 'overlimit')),
    error_message TEXT,
    FOREIGN KEY (team_id) REFERENCES teams(id)
);

-- Daily limits tracking
CREATE TABLE IF NOT EXISTS daily_limits (
    team_id INTEGER NOT NULL,
    date DATE NOT NULL,
    count INTEGER DEFAULT 0,
    PRIMARY KEY (team_id, date),
    FOREIGN KEY (team_id) REFERENCES teams(id)
);

-- Indexes for faster queries
CREATE INDEX IF NOT EXISTS idx_submissions_team_id ON submissions(team_id);
CREATE INDEX IF NOT EXISTS idx_submissions_status ON submissions(status);
CREATE INDEX IF NOT EXISTS idx_submissions_submit_time ON submissions(submit_time);
CREATE INDEX IF NOT EXISTS idx_daily_limits_team_date ON daily_limits(team_id, date);