CREATE TABLE users (
  id TEXT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  salt TEXT NOT NULL,
  role TEXT NOT NULL CHECK(role IN ('user','admin')),
  balance_cents INTEGER NOT NULL DEFAULT 0 CHECK(balance_cents >= 0),
  created_at INTEGER NOT NULL
);
CREATE TABLE sessions (
  token_hash TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  expires_at INTEGER NOT NULL
);
CREATE TABLE tasks (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL DEFAULT '',
  description TEXT NOT NULL,
  instructions TEXT NOT NULL DEFAULT '',
  reward_cents INTEGER NOT NULL CHECK(reward_cents > 0),
  active INTEGER NOT NULL DEFAULT 1 CHECK(active IN (0,1)),
  created_at INTEGER NOT NULL
);
CREATE TABLE submissions (
  id TEXT PRIMARY KEY,
  task_id TEXT NOT NULL REFERENCES tasks(id),
  user_id TEXT NOT NULL REFERENCES users(id),
  proof TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','approved','rejected')),
  review_note TEXT,
  created_at INTEGER NOT NULL,
  reviewed_at INTEGER,
  UNIQUE(task_id,user_id)
);
CREATE TABLE settings (
  id INTEGER PRIMARY KEY CHECK(id=1),
  min_payout_cents INTEGER NOT NULL CHECK(min_payout_cents > 0)
);
INSERT INTO settings(id,min_payout_cents) VALUES(1,5000);
CREATE TABLE payout_methods (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  active INTEGER NOT NULL DEFAULT 1 CHECK(active IN (0,1))
);
CREATE TABLE payouts (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id),
  method_id TEXT NOT NULL REFERENCES payout_methods(id),
  details TEXT NOT NULL,
  amount_cents INTEGER NOT NULL CHECK(amount_cents > 0),
  status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','paid','rejected')),
  created_at INTEGER NOT NULL,
  reviewed_at INTEGER
);
CREATE TABLE ledger (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id),
  amount_cents INTEGER NOT NULL,
  description TEXT NOT NULL,
  source_type TEXT NOT NULL,
  source_id TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  UNIQUE(source_type,source_id)
);
CREATE INDEX idx_sessions_user ON sessions(user_id);
CREATE INDEX idx_tasks_active_created ON tasks(active,created_at);
CREATE INDEX idx_submissions_status_created ON submissions(status,created_at);
CREATE INDEX idx_payouts_status_created ON payouts(status,created_at);
CREATE INDEX idx_ledger_user_created ON ledger(user_id,created_at);