-- Description: Creates the quotes table for storing Add to Quote requests

CREATE TABLE IF NOT EXISTS quotes (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  shop_domain     VARCHAR(255)  NOT NULL DEFAULT '',
  product_id      VARCHAR(100)  NOT NULL DEFAULT '',
  product_title   VARCHAR(500)  DEFAULT '',
  quantity        INT           DEFAULT 1,
  customer_name   VARCHAR(255)  NOT NULL,
  customer_email  VARCHAR(255)  NOT NULL,
  customer_phone  VARCHAR(50)   DEFAULT '',
  customer_message TEXT,
  configurations  JSON,
  status          ENUM('new','reviewed','quoted','closed') DEFAULT 'new',
  created_at      DATETIME      DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_shop_domain  (shop_domain),
  INDEX idx_product_id   (product_id),
  INDEX idx_customer_email (customer_email),
  INDEX idx_status       (status),
  INDEX idx_created_at   (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
