CREATE TABLE IF NOT EXISTS customer_categories (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  description TEXT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_customer_category_name (name)
);

INSERT INTO customer_categories (name, description)
VALUES ('Online', 'WooCommerce and direct web sales')
ON DUPLICATE KEY UPDATE active = TRUE;

SET @add_customer_category = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'customers'
        AND column_name = 'category_id'
    ),
    'SELECT 1',
    'ALTER TABLE customers ADD COLUMN category_id BIGINT UNSIGNED NULL AFTER woo_customer_id'
  )
);
PREPARE add_customer_category_stmt FROM @add_customer_category;
EXECUTE add_customer_category_stmt;
DEALLOCATE PREPARE add_customer_category_stmt;

SET @add_order_category = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'orders'
        AND column_name = 'category_id'
    ),
    'SELECT 1',
    'ALTER TABLE orders ADD COLUMN category_id BIGINT UNSIGNED NULL AFTER customer_id'
  )
);
PREPARE add_order_category_stmt FROM @add_order_category;
EXECUTE add_order_category_stmt;
DEALLOCATE PREPARE add_order_category_stmt;

SET @add_customer_category_index = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.statistics
      WHERE table_schema = DATABASE()
        AND table_name = 'customers'
        AND index_name = 'idx_customers_category'
    ),
    'SELECT 1',
    'ALTER TABLE customers ADD INDEX idx_customers_category (category_id, active)'
  )
);
PREPARE add_customer_category_index_stmt FROM @add_customer_category_index;
EXECUTE add_customer_category_index_stmt;
DEALLOCATE PREPARE add_customer_category_index_stmt;

SET @add_order_category_index = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.statistics
      WHERE table_schema = DATABASE()
        AND table_name = 'orders'
        AND index_name = 'idx_orders_category'
    ),
    'SELECT 1',
    'ALTER TABLE orders ADD INDEX idx_orders_category (category_id, order_date)'
  )
);
PREPARE add_order_category_index_stmt FROM @add_order_category_index;
EXECUTE add_order_category_index_stmt;
DEALLOCATE PREPARE add_order_category_index_stmt;

UPDATE customers c
JOIN customer_categories cc ON cc.name = 'Online'
SET c.category_id = cc.id
WHERE c.woo_customer_id IS NOT NULL
  AND c.category_id IS NULL;

UPDATE orders o
JOIN customer_categories cc ON cc.name = 'Online'
SET o.category_id = cc.id
WHERE o.source = 'woocommerce'
  AND o.category_id IS NULL;

CREATE TABLE IF NOT EXISTS roast_logs (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  week_start DATE NOT NULL,
  roast_date DATE NULL,
  inventory_item_id BIGINT UNSIGNED NULL,
  planned_green_kg DECIMAL(12,3) NOT NULL DEFAULT 0,
  actual_green_kg DECIMAL(12,3) NULL,
  roasted_kg DECIMAL(12,3) NULL,
  roast_level ENUM('light', 'medium', 'dark') NULL,
  batch_code VARCHAR(100) NULL,
  status ENUM('planned', 'roasted', 'packed', 'completed') NOT NULL DEFAULT 'planned',
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_roast_log_inventory FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id) ON DELETE SET NULL,
  CONSTRAINT fk_roast_log_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_roast_logs_week (week_start, roast_date),
  INDEX idx_roast_logs_inventory (inventory_item_id, week_start)
);