BEGIN TRANSACTION;

-- Groupes d'abonnements (façon PocketTube) : dossiers thématiques par utilisateur.
CREATE TABLE IF NOT EXISTS subscription_groups (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  color TEXT NOT NULL DEFAULT '#ef4444',
  icon TEXT NOT NULL DEFAULT '📁',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  UNIQUE(user_id, name)
);

CREATE INDEX IF NOT EXISTS idx_sub_groups_user ON subscription_groups(user_id);

-- Relation plusieurs-à-plusieurs : une chaîne (abonnement) dans plusieurs groupes.
CREATE TABLE IF NOT EXISTS subscription_group_members (
  group_id TEXT NOT NULL REFERENCES subscription_groups(id) ON DELETE CASCADE,
  subscription_id INTEGER NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
  added_at INTEGER NOT NULL,
  PRIMARY KEY (group_id, subscription_id)
);

CREATE INDEX IF NOT EXISTS idx_sub_group_members_sub ON subscription_group_members(subscription_id);
CREATE INDEX IF NOT EXISTS idx_sub_group_members_group ON subscription_group_members(group_id);

COMMIT;