-- IG Tracker v2 schema
CREATE TABLE IF NOT EXISTS ig_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  label VARCHAR(100) NULL,
  is_own TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ig_snapshots (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  snapshot_date DATE NOT NULL,
  followers_count INT NULL,
  media_count INT NULL,
  posts_30d INT NULL,
  likes_30d INT NULL,
  comments_30d INT NULL,
  avg_engagement_rate_30d DECIMAL(10,8) NULL,
  raw_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_account_day (account_id, snapshot_date),
  INDEX idx_day (snapshot_date),
  CONSTRAINT fk_snap_acc FOREIGN KEY (account_id) REFERENCES ig_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ig_settings (
  k VARCHAR(50) PRIMARY KEY,
  v TEXT NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ig_errors (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  occurred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  context VARCHAR(80) NOT NULL,
  account_username VARCHAR(50) NULL,
  message TEXT NOT NULL,
  details JSON NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Optional seed example:
-- INSERT INTO ig_accounts(username,label,is_own,is_active) VALUES ('yourusername','Mon compte',1,1);