-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    github_id INTEGER UNIQUE,
    username TEXT NOT NULL,
    email TEXT,
    avatar_url TEXT,
    last_repo TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Sessions/Tokens (encrypted access tokens)
CREATE TABLE IF NOT EXISTS auth_tokens (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    provider TEXT NOT NULL,
    access_token TEXT NOT NULL,
    refresh_token TEXT,
    expires_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, provider),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Branch state tracking
CREATE TABLE IF NOT EXISTS branch_states (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    repo_full_name TEXT NOT NULL,
    branch_name TEXT NOT NULL,
    base_branch TEXT DEFAULT 'main',
    last_push_at DATETIME NOT NULL,
    has_uncommitted_changes BOOLEAN DEFAULT FALSE,
    file_paths TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, repo_full_name),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Work-in-progress content (auto-save buffer)
CREATE TABLE IF NOT EXISTS draft_content (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    repo_full_name TEXT NOT NULL,
    file_path TEXT NOT NULL,
    content TEXT NOT NULL,
    last_saved_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, repo_full_name, file_path),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_auth_tokens_user_provider ON auth_tokens(user_id, provider);
CREATE INDEX IF NOT EXISTS idx_branch_states_user_repo ON branch_states(user_id, repo_full_name);
CREATE INDEX IF NOT EXISTS idx_draft_content_user_repo_file ON draft_content(user_id, repo_full_name, file_path);