ALTER TABLE tasks ADD COLUMN estimated_minutes INTEGER CHECK(estimated_minutes IS NULL OR estimated_minutes BETWEEN 1 AND 1440);
ALTER TABLE tasks ADD COLUMN requires_link INTEGER NOT NULL DEFAULT 0 CHECK(requires_link IN (0,1));
ALTER TABLE settings ADD COLUMN review_target_days INTEGER CHECK(review_target_days IS NULL OR review_target_days BETWEEN 1 AND 30);
ALTER TABLE users ADD COLUMN email_verified_at INTEGER;
ALTER TABLE users ADD COLUMN signup_fingerprint TEXT;
UPDATE users SET email_verified_at=created_at WHERE email_verified_at IS NULL;
CREATE TABLE auth_tokens (
  token_hash TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  purpose TEXT NOT NULL CHECK(purpose IN ('verify_email','reset_password')),
  expires_at INTEGER NOT NULL,
  used_at INTEGER
);
CREATE INDEX idx_auth_tokens_user ON auth_tokens(user_id,purpose);
CREATE TABLE security_attempts (
  action TEXT NOT NULL,
  fingerprint TEXT NOT NULL,
  window_start INTEGER NOT NULL,
  attempts INTEGER NOT NULL,
  PRIMARY KEY(action,fingerprint,window_start)
);
CREATE TABLE landing_daily (
  day TEXT PRIMARY KEY,
  views INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE task_opens (
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  task_id TEXT NOT NULL REFERENCES tasks(id),
  first_at INTEGER NOT NULL,
  PRIMARY KEY(user_id,task_id)
);
CREATE INDEX idx_task_opens_user_first ON task_opens(user_id,first_at);
CREATE TABLE referral_reviews (
  submission_id TEXT PRIMARY KEY REFERENCES submissions(id),
  referrer_id TEXT NOT NULL REFERENCES users(id),
  referred_user_id TEXT NOT NULL REFERENCES users(id),
  amount_cents INTEGER NOT NULL CHECK(amount_cents > 0),
  status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','approved','rejected')),
  reason TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  reviewed_at INTEGER
);
CREATE INDEX idx_referral_reviews_status ON referral_reviews(status,created_at);