schema.sql

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

-- User preferences
CREATE TABLE IF NOT EXISTS user_preferences (
  user_id INTEGER PRIMARY KEY REFERENCES users(id),
  active_grid_id TEXT NOT NULL DEFAULT '24-hours',
  updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Custom grids created by users
CREATE TABLE IF NOT EXISTS saved_grids (
  id TEXT PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  name TEXT NOT NULL,
  rows INTEGER NOT NULL,
  columns INTEGER NOT NULL,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Grid state (blockColors + colors per preset/grid)
CREATE TABLE IF NOT EXISTS grid_states (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id),
  grid_id TEXT NOT NULL,
  block_colors TEXT NOT NULL,
  colors TEXT NOT NULL,
  updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id, grid_id)
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_grid_states_user ON grid_states(user_id);
CREATE INDEX IF NOT EXISTS idx_saved_grids_user ON saved_grids(user_id);