CREATE DATABASE IF NOT EXISTS pretwatch_v2 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE pretwatch_v2;

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS monitor_groups (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  product_code VARCHAR(120) NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_monitor_groups_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_monitor_groups_user (user_id),
  INDEX idx_monitor_groups_code (product_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS monitors (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  group_id INT UNSIGNED NOT NULL,
  store_key VARCHAR(100) NOT NULL,
  store_name VARCHAR(120) NOT NULL,
  product_url TEXT NOT NULL,
  css_price VARCHAR(255) NOT NULL,
  price_regex VARCHAR(255) NULL,
  css_stock VARCHAR(255) NULL,
  stock_regex VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_price DECIMAL(12,2) NULL,
  last_stock VARCHAR(40) NULL,
  last_checked_at DATETIME NULL,
  last_status VARCHAR(40) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_monitors_group FOREIGN KEY (group_id) REFERENCES monitor_groups(id) ON DELETE CASCADE,
  INDEX idx_monitors_group (group_id),
  INDEX idx_monitors_store (store_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS monitor_checks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  monitor_id INT UNSIGNED NOT NULL,
  checked_at DATETIME NOT NULL,
  price DECIMAL(12,2) NULL,
  stock VARCHAR(40) NULL,
  raw_price_text TEXT NULL,
  raw_stock_text TEXT NULL,
  status VARCHAR(40) NOT NULL,
  error_message TEXT NULL,
  CONSTRAINT fk_monitor_checks_monitor FOREIGN KEY (monitor_id) REFERENCES monitors(id) ON DELETE CASCADE,
  INDEX idx_monitor_checks_monitor (monitor_id),
  INDEX idx_monitor_checks_date (checked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;