CREATE TABLE IF NOT EXISTS instagram_accounts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  instagram_user_id VARCHAR(80) NOT NULL UNIQUE,
  username VARCHAR(120) NOT NULL,
  account_type ENUM('business','creator','unknown') NOT NULL DEFAULT 'unknown',
  profile_url TEXT NULL,
  followers_count INT UNSIGNED NULL,
  media_count INT UNSIGNED NULL,
  status ENUM('connected','paused','candidate') NOT NULL DEFAULT 'candidate',
  projects_json JSON NOT NULL,
  last_collected_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS instagram_media_observations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  instagram_account_id BIGINT UNSIGNED NOT NULL,
  instagram_media_id VARCHAR(100) NOT NULL,
  media_type VARCHAR(30) NOT NULL,
  permalink TEXT NULL,
  caption MEDIUMTEXT NULL,
  published_at DATETIME NULL,
  like_count INT UNSIGNED NOT NULL DEFAULT 0,
  comments_count INT UNSIGNED NOT NULL DEFAULT 0,
  reach_count INT UNSIGNED NULL,
  views_count INT UNSIGNED NULL,
  shares_count INT UNSIGNED NULL,
  saved_count INT UNSIGNED NULL,
  engagement_score DECIMAL(10,2) NOT NULL DEFAULT 0,
  collected_at DATETIME NOT NULL,
  UNIQUE KEY instagram_media_unique(instagram_account_id,instagram_media_id),
  INDEX(engagement_score), INDEX(published_at),
  FOREIGN KEY(instagram_account_id) REFERENCES instagram_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
