CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(190) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  url VARCHAR(600) NOT NULL,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  url VARCHAR(700) NOT NULL,
  title TEXT,
  description TEXT,
  final_price_irr INT NULL,
  old_price_irr INT NULL,
  discount_percent INT NULL,
  availability ENUM('available','unavailable','unknown') NOT NULL DEFAULT 'unknown',
  first_seen DATETIME NOT NULL,
  last_seen DATETIME NOT NULL,
  last_changed DATETIME NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uniq_cat_url (category_id, url),
  INDEX (category_id),
  INDEX (url),
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS changes_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  product_url VARCHAR(700) NOT NULL,
  change_type VARCHAR(50) NOT NULL, -- new, price, price_compare, availability
  old_value TEXT NULL,
  new_value TEXT NULL,
  detected_at DATETIME NOT NULL,
  INDEX (product_url),
  INDEX (detected_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sent_events (
  event_hash VARCHAR(64) PRIMARY KEY,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;