-- sql/sources.sql
CREATE TABLE IF NOT EXISTS airdrop_sources (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  type ENUM('rss','json','html_airdropsio','html_cryptorank') NOT NULL DEFAULT 'rss',
  url TEXT NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  map_json JSON NULL,
  last_fetch DATETIME NULL,
  last_status VARCHAR(40) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_enabled (enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS airdrop_raw (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  source_id INT UNSIGNED NOT NULL,
  foreign_id VARCHAR(255) NOT NULL,
  title VARCHAR(255) NULL,
  url TEXT NULL,
  content MEDIUMTEXT NULL,
  image_url TEXT NULL,
  published_at DATETIME NULL,
  raw_json LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_source_foreign (source_id, foreign_id),
  KEY idx_source (source_id),
  CONSTRAINT fk_raw_source FOREIGN KEY (source_id) REFERENCES airdrop_sources(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
