CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin', 'sales', 'production', 'warehouse', 'accounts') NOT NULL DEFAULT 'admin',
  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
);

CREATE TABLE IF NOT EXISTS customers (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  woo_customer_id BIGINT UNSIGNED NULL UNIQUE,
  company_name VARCHAR(190) NULL,
  contact_name VARCHAR(190) NOT NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(60) NULL,
  tax_number VARCHAR(100) NULL,
  payment_terms_days INT NOT NULL DEFAULT 0,
  billing_address JSON NULL,
  shipping_address JSON NULL,
  notes 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,
  INDEX idx_customers_name (company_name, contact_name)
);

CREATE TABLE IF NOT EXISTS suppliers (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(190) NOT NULL,
  contact_name VARCHAR(190) NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(60) NULL,
  tax_number VARCHAR(100) NULL,
  payment_terms_days INT NOT NULL DEFAULT 0,
  address JSON NULL,
  notes 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
);

CREATE TABLE IF NOT EXISTS inventory_items (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  sku VARCHAR(100) NOT NULL UNIQUE,
  name VARCHAR(190) NOT NULL,
  category ENUM('green_bean', 'packaging', 'shipping', 'consumable', 'other') NOT NULL,
  unit ENUM('kg', 'g', 'each', 'box', 'roll', 'litre') NOT NULL,
  quantity_on_hand DECIMAL(14,3) NOT NULL DEFAULT 0,
  reorder_level DECIMAL(14,3) NOT NULL DEFAULT 0,
  average_unit_cost DECIMAL(14,4) NOT NULL DEFAULT 0,
  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,
  INDEX idx_inventory_reorder (active, quantity_on_hand, reorder_level)
);

CREATE TABLE IF NOT EXISTS inventory_lots (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  inventory_item_id BIGINT UNSIGNED NOT NULL,
  supplier_id BIGINT UNSIGNED NULL,
  lot_code VARCHAR(120) NOT NULL,
  origin_country VARCHAR(120) NULL,
  region VARCHAR(120) NULL,
  producer VARCHAR(190) NULL,
  crop_year VARCHAR(20) NULL,
  received_date DATE NULL,
  best_before_date DATE NULL,
  quantity_received DECIMAL(14,3) NOT NULL DEFAULT 0,
  quantity_remaining DECIMAL(14,3) NOT NULL DEFAULT 0,
  unit_cost DECIMAL(14,4) NOT NULL DEFAULT 0,
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_inventory_lot (inventory_item_id, lot_code),
  CONSTRAINT fk_lot_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id),
  CONSTRAINT fk_lot_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

CREATE TABLE IF NOT EXISTS products (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  woo_product_id BIGINT UNSIGNED NULL UNIQUE,
  woo_variation_id BIGINT UNSIGNED NULL UNIQUE,
  sku VARCHAR(100) NOT NULL UNIQUE,
  name VARCHAR(190) NOT NULL,
  description TEXT NULL,
  roast_profile VARCHAR(120) NULL,
  pack_size_grams INT NOT NULL,
  wholesale_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  retail_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  tax_rate DECIMAL(6,4) NOT NULL DEFAULT 0.1000,
  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
);

CREATE TABLE IF NOT EXISTS product_components (
  product_id BIGINT UNSIGNED NOT NULL,
  inventory_item_id BIGINT UNSIGNED NOT NULL,
  quantity_per_unit DECIMAL(14,3) NOT NULL,
  PRIMARY KEY (product_id, inventory_item_id),
  CONSTRAINT fk_component_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_component_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id)
);

CREATE TABLE IF NOT EXISTS recurring_order_templates (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  frequency ENUM('weekly', 'fortnightly', 'monthly', 'quarterly') NOT NULL,
  next_order_date DATE NOT NULL,
  shipping_method VARCHAR(120) NULL,
  customer_reference VARCHAR(120) NULL,
  notes TEXT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  last_generated_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_recurring_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
  INDEX idx_recurring_due (active, next_order_date)
);

CREATE TABLE IF NOT EXISTS recurring_order_items (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  template_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL,
  CONSTRAINT fk_recurring_item_template FOREIGN KEY (template_id) REFERENCES recurring_order_templates(id) ON DELETE CASCADE,
  CONSTRAINT fk_recurring_item_product FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE IF NOT EXISTS orders (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  order_number VARCHAR(40) NOT NULL UNIQUE,
  source ENUM('manual', 'woocommerce', 'recurring') NOT NULL DEFAULT 'manual',
  woo_order_id BIGINT UNSIGNED NULL UNIQUE,
  recurring_template_id BIGINT UNSIGNED NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  status ENUM('draft', 'confirmed', 'scheduled', 'in_production', 'ready', 'shipped', 'completed', 'cancelled') NOT NULL DEFAULT 'draft',
  payment_status ENUM('unpaid', 'partial', 'paid', 'refunded') NOT NULL DEFAULT 'unpaid',
  order_date DATE NOT NULL,
  required_date DATE NULL,
  roast_date DATE NULL,
  currency CHAR(3) NOT NULL DEFAULT 'AUD',
  customer_reference VARCHAR(120) NULL,
  shipping_method VARCHAR(120) NULL,
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
  tax_total DECIMAL(12,2) NOT NULL DEFAULT 0,
  shipping_total DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  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_order_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT fk_order_template FOREIGN KEY (recurring_template_id) REFERENCES recurring_order_templates(id),
  CONSTRAINT fk_order_user FOREIGN KEY (created_by) REFERENCES users(id),
  INDEX idx_orders_schedule (status, roast_date, required_date),
  INDEX idx_orders_customer (customer_id, order_date)
);

CREATE TABLE IF NOT EXISTS order_items (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  description VARCHAR(255) NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL,
  tax_rate DECIMAL(6,4) NOT NULL DEFAULT 0.1000,
  line_subtotal DECIMAL(12,2) NOT NULL,
  line_tax DECIMAL(12,2) NOT NULL,
  line_total DECIMAL(12,2) NOT NULL,
  CONSTRAINT fk_order_item_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_order_item_product FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE IF NOT EXISTS invoices (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  invoice_number VARCHAR(40) NOT NULL UNIQUE,
  order_id BIGINT UNSIGNED NOT NULL UNIQUE,
  issue_date DATE NOT NULL,
  due_date DATE NOT NULL,
  status ENUM('draft', 'issued', 'part_paid', 'paid', 'overdue', 'void') NOT NULL DEFAULT 'draft',
  subtotal DECIMAL(12,2) NOT NULL,
  tax_total DECIMAL(12,2) NOT NULL,
  shipping_total DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL,
  amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0,
  commercial_invoice BOOLEAN NOT NULL DEFAULT TRUE,
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_invoice_order FOREIGN KEY (order_id) REFERENCES orders(id),
  INDEX idx_invoices_due (status, due_date)
);

CREATE TABLE IF NOT EXISTS reminders (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  type ENUM('recurring_order', 'invoice_due', 'invoice_overdue', 'low_stock', 'purchase_due', 'shipping') NOT NULL,
  entity_type VARCHAR(60) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(190) NOT NULL,
  message TEXT NULL,
  due_at DATETIME NOT NULL,
  status ENUM('pending', 'sent', 'dismissed', 'completed') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_reminder_entity_due (type, entity_type, entity_id, due_at),
  INDEX idx_reminders_due (status, due_at)
);

CREATE TABLE IF NOT EXISTS roast_days (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  roast_date DATE NOT NULL UNIQUE,
  status ENUM('planned', 'in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'planned',
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS production_batches (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  roast_day_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  planned_roasted_kg DECIMAL(12,3) NOT NULL,
  actual_green_kg DECIMAL(12,3) NULL,
  actual_roasted_kg DECIMAL(12,3) NULL,
  batch_code VARCHAR(100) NULL,
  status ENUM('planned', 'roasting', 'resting', 'packed', 'completed') NOT NULL DEFAULT 'planned',
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_batch_roast_day FOREIGN KEY (roast_day_id) REFERENCES roast_days(id) ON DELETE CASCADE,
  CONSTRAINT fk_batch_product FOREIGN KEY (product_id) REFERENCES products(id),
  UNIQUE KEY uq_roast_product (roast_day_id, product_id)
);

CREATE TABLE IF NOT EXISTS purchase_orders (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  po_number VARCHAR(40) NOT NULL UNIQUE,
  supplier_id BIGINT UNSIGNED NOT NULL,
  status ENUM('draft', 'ordered', 'part_received', 'received', 'cancelled') NOT NULL DEFAULT 'draft',
  order_date DATE NOT NULL,
  expected_date DATE NULL,
  currency CHAR(3) NOT NULL DEFAULT 'AUD',
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
  tax_total DECIMAL(12,2) NOT NULL DEFAULT 0,
  shipping_total DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  supplier_reference VARCHAR(120) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_po_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  INDEX idx_po_expected (status, expected_date)
);

CREATE TABLE IF NOT EXISTS purchase_order_items (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  purchase_order_id BIGINT UNSIGNED NOT NULL,
  inventory_item_id BIGINT UNSIGNED NOT NULL,
  description VARCHAR(255) NOT NULL,
  quantity DECIMAL(14,3) NOT NULL,
  quantity_received DECIMAL(14,3) NOT NULL DEFAULT 0,
  unit_cost DECIMAL(14,4) NOT NULL,
  tax_rate DECIMAL(6,4) NOT NULL DEFAULT 0.1000,
  line_total DECIMAL(12,2) NOT NULL,
  CONSTRAINT fk_po_item_order FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_po_item_inventory FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id)
);

CREATE TABLE IF NOT EXISTS inventory_movements (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  inventory_item_id BIGINT UNSIGNED NOT NULL,
  inventory_lot_id BIGINT UNSIGNED NULL,
  movement_type ENUM('purchase', 'production_use', 'adjustment', 'waste', 'return') NOT NULL,
  quantity DECIMAL(14,3) NOT NULL,
  reference_type VARCHAR(60) NULL,
  reference_id BIGINT UNSIGNED NULL,
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_movement_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id),
  CONSTRAINT fk_movement_lot FOREIGN KEY (inventory_lot_id) REFERENCES inventory_lots(id),
  CONSTRAINT fk_movement_user FOREIGN KEY (created_by) REFERENCES users(id),
  INDEX idx_movement_reference (reference_type, reference_id)
);

CREATE TABLE IF NOT EXISTS shipments (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  carrier VARCHAR(120) NULL,
  service VARCHAR(120) NULL,
  tracking_number VARCHAR(190) NULL,
  status ENUM('pending', 'booked', 'in_transit', 'delivered', 'exception', 'returned') NOT NULL DEFAULT 'pending',
  ship_date DATE NULL,
  delivered_date DATE NULL,
  shipping_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
  label_url TEXT NULL,
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_shipment_order FOREIGN KEY (order_id) REFERENCES orders(id),
  INDEX idx_shipments_status (status, ship_date)
);

CREATE TABLE IF NOT EXISTS sync_events (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  integration VARCHAR(60) NOT NULL,
  external_id VARCHAR(190) NULL,
  event_type VARCHAR(100) NOT NULL,
  direction ENUM('inbound', 'outbound') NOT NULL,
  status ENUM('pending', 'processed', 'failed', 'ignored') NOT NULL DEFAULT 'pending',
  payload JSON NULL,
  error_message TEXT NULL,
  processed_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_sync_pending (integration, status, created_at),
  INDEX idx_sync_external (integration, external_id)
);