-- ================================================================
-- Monitor de Competencia — Schema
-- 5 tablas para tracking de vendedores competidores
-- ================================================================

CREATE TABLE IF NOT EXISTS meli_monitor_sellers (
  seller_id    BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  nickname     VARCHAR(80)  NOT NULL DEFAULT '',
  level_id     VARCHAR(30)  DEFAULT NULL,
  power_status VARCHAR(20)  DEFAULT NULL,
  total_listings INT UNSIGNED DEFAULT 0,
  city         VARCHAR(100) DEFAULT '',
  state_name   VARCHAR(100) DEFAULT '',
  permalink    VARCHAR(300) DEFAULT '',
  registration_date VARCHAR(30) DEFAULT '',
  active       TINYINT(1)   NOT NULL DEFAULT 1,
  notes        VARCHAR(255) DEFAULT '',
  added_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_synced_at DATETIME   DEFAULT NULL,
  KEY idx_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS meli_monitor_items (
  item_id      VARCHAR(20)  NOT NULL PRIMARY KEY,
  seller_id    BIGINT UNSIGNED NOT NULL,
  title        VARCHAR(255) NOT NULL DEFAULT '',
  price        DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency_id  VARCHAR(5)   DEFAULT 'ARS',
  category_id  VARCHAR(20)  DEFAULT '',
  listing_type_id VARCHAR(30) DEFAULT '',
  thumbnail    VARCHAR(300) DEFAULT '',
  permalink    VARCHAR(300) DEFAULT '',
  free_shipping TINYINT(1)  DEFAULT 0,
  fulfillment  TINYINT(1)   DEFAULT 0,
  item_condition VARCHAR(10) DEFAULT '',
  active       TINYINT(1)   NOT NULL DEFAULT 1,
  first_seen_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_updated_at DATETIME  DEFAULT NULL,
  KEY idx_seller (seller_id),
  KEY idx_category (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS meli_monitor_snapshots (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  item_id      VARCHAR(20) NOT NULL,
  sold_quantity INT UNSIGNED NOT NULL DEFAULT 0,
  available_quantity INT UNSIGNED NOT NULL DEFAULT 0,
  price        DECIMAL(12,2) NOT NULL DEFAULT 0,
  snapshot_date DATE NOT NULL,
  snapshot_hour TINYINT UNSIGNED NOT NULL,
  taken_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_item_date_hour (item_id, snapshot_date, snapshot_hour),
  KEY idx_date (snapshot_date),
  KEY idx_item_date (item_id, snapshot_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS meli_monitor_daily (
  item_id      VARCHAR(20) NOT NULL,
  snapshot_date DATE NOT NULL,
  sold_start   INT UNSIGNED NOT NULL DEFAULT 0,
  sold_end     INT UNSIGNED NOT NULL DEFAULT 0,
  sold_day     INT UNSIGNED NOT NULL DEFAULT 0,
  price_start  DECIMAL(12,2) DEFAULT 0,
  price_end    DECIMAL(12,2) DEFAULT 0,
  stock_start  INT UNSIGNED DEFAULT 0,
  stock_end    INT UNSIGNED DEFAULT 0,
  PRIMARY KEY (item_id, snapshot_date),
  KEY idx_date (snapshot_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS meli_monitor_changes (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  item_id      VARCHAR(20) NOT NULL,
  seller_id    BIGINT UNSIGNED NOT NULL,
  field_changed VARCHAR(20) NOT NULL,
  old_value    VARCHAR(500) DEFAULT '',
  new_value    VARCHAR(500) DEFAULT '',
  detected_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  notified     TINYINT(1) NOT NULL DEFAULT 0,
  KEY idx_seller (seller_id),
  KEY idx_item (item_id),
  KEY idx_date (detected_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;