CREATE TABLE IF NOT EXISTS opening_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  trade_date DATE NOT NULL,
  captured_at_utc DATETIME(3) NOT NULL,
  captured_at_brasilia DATETIME(3) NOT NULL,
  capture_type ENUM('scheduled','manual','recovered') NOT NULL DEFAULT 'scheduled',
  news_3star TINYINT(1) NOT NULL DEFAULT 0,
  mode ENUM('external','adrs') NOT NULL DEFAULT 'external',
  result_pct DECIMAL(10,4) NULL,
  classification VARCHAR(40) NULL,
  direction ENUM('negative','lateral','positive') NULL,
  notes VARCHAR(500) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_opening_snapshots_trade_date (trade_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS opening_snapshot_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  snapshot_id BIGINT UNSIGNED NOT NULL,
  asset_key VARCHAR(20) NOT NULL,
  tv_symbol VARCHAR(80) NOT NULL,
  price DECIMAL(20,8) NULL,
  reference_close DECIMAL(20,8) NULL,
  change_pct DECIMAL(10,4) NULL,
  contribution_pct DECIMAL(10,4) NULL,
  source_timestamp_utc DATETIME(3) NULL,
  status ENUM('ok','missing','error','manual') NOT NULL DEFAULT 'ok',
  raw_json JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_snapshot_asset (snapshot_id, asset_key),
  CONSTRAINT fk_snapshot_items_snapshot FOREIGN KEY (snapshot_id)
    REFERENCES opening_snapshots(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;