BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS channels (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  provider TEXT NOT NULL,
  external_id TEXT NOT NULL,
  title TEXT,
  handle TEXT,
  avatar_url TEXT,
  url TEXT,
  subs_count INTEGER,
  verified INTEGER DEFAULT 0,
  last_refreshed_at INTEGER,
  UNIQUE(provider, external_id)
);

CREATE TABLE IF NOT EXISTS subscriptions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL,
  UNIQUE(user_id, channel_id)
);

CREATE INDEX IF NOT EXISTS idx_subs_user ON subscriptions(user_id);

COMMIT;