CREATE TABLE IF NOT EXISTS bot_users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  chat_id BIGINT NOT NULL UNIQUE,
  username VARCHAR(190) NULL,
  first_name VARCHAR(190) NULL,
  last_name VARCHAR(190) NULL,
  language_code VARCHAR(20) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS instagram_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  chat_id BIGINT NOT NULL,
  message_id BIGINT NULL,
  original_url TEXT NOT NULL,
  canonical_url TEXT NULL,
  shortcode VARCHAR(100) NULL,
  media_type VARCHAR(50) NULL,
  media_count INT DEFAULT 0,
  author VARCHAR(190) NULL,
  title TEXT NULL,
  caption LONGTEXT NULL,
  status ENUM('pending','success','failed') NOT NULL DEFAULT 'pending',
  error_message TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_chat_created (chat_id, created_at),
  INDEX idx_shortcode (shortcode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS instagram_media (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_id BIGINT UNSIGNED NOT NULL,
  media_type ENUM('photo','video','unknown') NOT NULL DEFAULT 'unknown',
  media_url LONGTEXT NOT NULL,
  thumbnail_url LONGTEXT NULL,
  width INT NULL,
  height INT NULL,
  byte_size BIGINT NULL,
  telegram_file_id VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_media_request FOREIGN KEY (request_id) REFERENCES instagram_requests(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;