-- Migration 002: discovery cache and interactions
ALTER TABLE pages
  ADD COLUMN IF NOT EXISTS canonical_url VARCHAR(2048) NULL,
  ADD COLUMN IF NOT EXISTS canonical_url_hash CHAR(64) NULL,
  ADD COLUMN IF NOT EXISTS page_type VARCHAR(32) NOT NULL DEFAULT 'url',
  ADD COLUMN IF NOT EXISTS parent_canonical_url_hash CHAR(64) NULL,
  ADD COLUMN IF NOT EXISTS action_key CHAR(64) NULL,
  ADD COLUMN IF NOT EXISTS action_label VARCHAR(512) NULL,
  ADD COLUMN IF NOT EXISTS cache_policy VARCHAR(32) NULL,
  ADD COLUMN IF NOT EXISTS first_seen_at DOUBLE NULL,
  ADD COLUMN IF NOT EXISTS last_seen_at DOUBLE NULL,
  ADD COLUMN IF NOT EXISTS etag VARCHAR(512) NULL,
  ADD COLUMN IF NOT EXISTS last_modified VARCHAR(512) NULL;

CREATE UNIQUE INDEX IF NOT EXISTS idx_pages_canonical_type_action
  ON pages (canonical_url_hash, page_type, action_key);

CREATE INDEX IF NOT EXISTS idx_pages_content_digest ON pages (content_digest);
CREATE INDEX IF NOT EXISTS idx_pages_parent_hash ON pages (parent_canonical_url_hash);

CREATE TABLE IF NOT EXISTS discovered_targets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  discovery_key CHAR(71) NOT NULL,
  target_type VARCHAR(32) NOT NULL DEFAULT 'url',
  raw_url VARCHAR(2048) NULL,
  canonical_url VARCHAR(2048) NULL,
  canonical_url_hash CHAR(64) NULL,
  parent_canonical_url_hash CHAR(64) NULL,
  source_url VARCHAR(2048) NULL,
  link_text VARCHAR(1024) NOT NULL DEFAULT '',
  action_signature VARCHAR(2048) NULL,
  first_seen_at DOUBLE NOT NULL,
  last_seen_at DOUBLE NOT NULL,
  seen_count INT NOT NULL DEFAULT 1,
  crawl_status VARCHAR(32) NOT NULL DEFAULT 'discovered',
  reserved_at DOUBLE NULL,
  crawled_at DOUBLE NULL,
  error_text TEXT NULL,
  UNIQUE INDEX idx_discovered_key (discovery_key),
  INDEX idx_discovered_canonical_hash (canonical_url_hash),
  INDEX idx_discovered_parent_hash (parent_canonical_url_hash),
  INDEX idx_discovered_status (crawl_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS page_actions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  parent_canonical_url_hash CHAR(64) NOT NULL,
  action_key CHAR(64) NOT NULL,
  action_type VARCHAR(32) NOT NULL,
  selector VARCHAR(2048) NOT NULL DEFAULT '',
  label VARCHAR(1024) NOT NULL DEFAULT '',
  href VARCHAR(2048) NULL,
  before_url VARCHAR(2048) NULL,
  after_url VARCHAR(2048) NULL,
  result_type VARCHAR(32) NOT NULL DEFAULT 'unknown',
  virtual_url VARCHAR(2048) NULL,
  content_digest CHAR(64) NULL,
  created_at DOUBLE NOT NULL,
  UNIQUE INDEX idx_page_actions_key (action_key),
  INDEX idx_page_actions_parent (parent_canonical_url_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
