CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','financeiro','operacional','consulta','auditoria') NOT NULL DEFAULT 'admin',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS imports (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  filename VARCHAR(255) NOT NULL,
  file_hash CHAR(64) NULL,
  status ENUM('running','done','error') NOT NULL DEFAULT 'running',
  stats_json JSON NULL,
  error_message TEXT NULL,
  started_at DATETIME NOT NULL,
  finished_at DATETIME NULL,
  INDEX idx_imports_status (status),
  INDEX idx_imports_started (started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  woo_order_id BIGINT UNSIGNED NOT NULL,
  order_number VARCHAR(80) NULL,
  order_number_formatted VARCHAR(80) NULL,
  order_date DATETIME NULL,
  status VARCHAR(60) NULL,
  currency VARCHAR(12) NULL,
  billing_name VARCHAR(220) NULL,
  billing_email VARCHAR(220) NULL,
  billing_phone VARCHAR(80) NULL,
  billing_city VARCHAR(120) NULL,
  billing_state VARCHAR(80) NULL,
  billing_postcode VARCHAR(40) NULL,
  billing_country VARCHAR(40) NULL,
  shipping_name VARCHAR(220) NULL,
  shipping_city VARCHAR(120) NULL,
  shipping_state VARCHAR(80) NULL,
  shipping_postcode VARCHAR(40) NULL,
  shipping_method_id VARCHAR(160) NULL,
  shipping_method VARCHAR(255) NULL,
  payment_method_id VARCHAR(120) NULL,
  payment_method VARCHAR(255) NULL,
  discount_total DECIMAL(12,2) NOT NULL DEFAULT 0,
  shipping_total DECIMAL(12,2) NOT NULL DEFAULT 0,
  order_total DECIMAL(12,2) NOT NULL DEFAULT 0,
  refunded_total DECIMAL(12,2) NOT NULL DEFAULT 0,
  customer_note TEXT NULL,
  customer_id BIGINT UNSIGNED NULL,
  completed_date DATETIME NULL,
  raw_xml LONGTEXT NULL,
  raw_json LONGTEXT NULL,
  import_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_orders_woo (woo_order_id),
  INDEX idx_orders_date (order_date),
  INDEX idx_orders_status (status),
  INDEX idx_orders_total (order_total),
  INDEX idx_orders_billing_name (billing_name),
  INDEX idx_orders_email (billing_email),
  INDEX idx_orders_shipping_method (shipping_method),
  CONSTRAINT fk_orders_import FOREIGN KEY (import_id) REFERENCES imports(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  woo_item_id BIGINT UNSIGNED NULL,
  product_id BIGINT UNSIGNED NULL,
  sku VARCHAR(120) NULL,
  name VARCHAR(255) NULL,
  quantity DECIMAL(12,2) NOT NULL DEFAULT 1,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  refunded DECIMAL(12,2) NOT NULL DEFAULT 0,
  refunded_quantity INT NOT NULL DEFAULT 0,
  meta_raw LONGTEXT NULL,
  INDEX idx_items_order (order_id),
  INDEX idx_items_product (product_id),
  INDEX idx_items_name (name),
  CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_item_meta (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_item_id BIGINT UNSIGNED NOT NULL,
  meta_key VARCHAR(255) NOT NULL,
  normalized_key VARCHAR(255) NOT NULL,
  meta_value LONGTEXT NULL,
  INDEX idx_meta_item (order_item_id),
  INDEX idx_meta_key (normalized_key),
  FULLTEXT KEY ft_meta_value (meta_value),
  CONSTRAINT fk_meta_item FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_notes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  note_date DATETIME NULL,
  author VARCHAR(160) NULL,
  content TEXT NULL,
  INDEX idx_notes_order (order_id),
  CONSTRAINT fk_notes_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS shipping_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  woo_shipping_id BIGINT UNSIGNED NULL,
  method_id VARCHAR(160) NULL,
  method_name VARCHAR(255) NULL,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  INDEX idx_shipping_order (order_id),
  INDEX idx_shipping_method (method_name),
  CONSTRAINT fk_shipping_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS report_presets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  description TEXT NULL,
  config_json JSON NOT NULL,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS export_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  export_type VARCHAR(40) NOT NULL,
  filters_json JSON NULL,
  rows_count INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sync_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source VARCHAR(80) NOT NULL,
  status VARCHAR(40) NOT NULL,
  message TEXT NULL,
  payload LONGTEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
