-- sql/schema.sql
CREATE TABLE IF NOT EXISTS airdrops (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  slug VARCHAR(200) DEFAULT NULL,
  url  VARCHAR(255) DEFAULT NULL,
  image_url VARCHAR(255) DEFAULT NULL,
  chain VARCHAR(50) DEFAULT NULL,
  reward VARCHAR(100) DEFAULT NULL,
  deadline DATE NULL,
  description MEDIUMTEXT NULL,
  ai_score TINYINT UNSIGNED NULL,
  ai_summary TEXT NULL,
  ai_reason TEXT NULL,
  ai_last_analyzed DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_created_at (created_at),
  KEY idx_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS airdrop_posts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  airdrop_id INT UNSIGNED NOT NULL,
  channel_id VARCHAR(32) NOT NULL,
  message_id INT NULL,
  posted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status ENUM('ok','fail') NOT NULL DEFAULT 'ok',
  error VARCHAR(255) NULL,
  UNIQUE KEY uq_airdrop_channel (airdrop_id, channel_id),
  KEY idx_channel (channel_id),
  CONSTRAINT fk_airdrop_posts_airdrops FOREIGN KEY (airdrop_id) REFERENCES airdrops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
