-- Migration 13: Pedidos, vendas e itens
-- Source: backend/db/dump/19052026_nextbar.sql

-- -------------------------------------------------------- 13. Pedidos, vendas e itens
--
-- Estrutura para tabela `tipos_entrada_evento`
--

CREATE TABLE `tipos_entrada_evento` (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  event_id INT NOT NULL,
  name VARCHAR(200) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  status TINYINT(1) NOT NULL DEFAULT 1,
  trash TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  FOREIGN KEY (event_id) REFERENCES eventos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
--
-- Estrutura para tabela `clusters_similaridade`
--

CREATE TABLE `clusters_similaridade` (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  event_id INT NULL,
  name VARCHAR(200) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  status TINYINT(1) NOT NULL DEFAULT 1,
  trash TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  FOREIGN KEY (event_id) REFERENCES eventos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
--
-- Estrutura para tabela `pedidos`
--

CREATE TABLE `pedidos` (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  event_id INT NOT NULL,
  sector_id INT NOT NULL,
  device_id INT NOT NULL,
  user_id INT NOT NULL,
  final_client_id INT,
  origin VARCHAR(200),
  subtotal DECIMAL(10,2) NOT NULL,
  discount DECIMAL(10,2) NOT NULL,
  fees DECIMAL(10,2) NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  status TINYINT(2) NOT NULL,
  trash TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  FOREIGN KEY (event_id) REFERENCES eventos(id),
  FOREIGN KEY (device_id) REFERENCES dispositivos(id),
  FOREIGN KEY (user_id) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
--
-- Estrutura para tabela `itens_pedidos`
--

CREATE TABLE `itens_pedidos` (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_tenant_id INT NOT NULL,
  product_concept_id INT NOT NULL,
  product_variant_id INT NOT NULL,
  event_entry_type_id INT NOT NULL,
  qtd INT(11) NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  discount DECIMAL(10,2) NOT NULL,
  subtotal DECIMAL(10,2) NOT NULL,
  total_price DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  status TINYINT(2) NOT NULL,
  trash TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (order_id) REFERENCES pedidos(id),
  FOREIGN KEY (product_tenant_id) REFERENCES produtos_tenant(id),
  FOREIGN KEY (event_entry_type_id) REFERENCES tipos_entrada_evento(id),
  FOREIGN KEY (product_concept_id) REFERENCES conceitos_produtos(id),
  FOREIGN KEY (product_variant_id) REFERENCES variantes_produtos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
--
-- Estrutura para tabela `vendas`
--

CREATE TABLE `vendas` (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  tenant_id INT NOT NULL,
  event_id INT NOT NULL,
  sector_id INT NOT NULL,
  device_id INT NOT NULL,
  user_id INT NOT NULL,
  operator_session_id INT,
  catalog_version_id VARCHAR(200),
  sale_code VARCHAR(200) NOT NULL,
  idempotency_key VARCHAR(200) NOT NULL,
  origin VARCHAR(200),
  subtotal DECIMAL(10,2) NOT NULL,
  discount DECIMAL(10,2) NOT NULL,
  fees DECIMAL(10,2) NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  sale_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  synchronized_on TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  status TINYINT(2) NOT NULL,
  trash TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  FOREIGN KEY (event_id) REFERENCES eventos(id),
  FOREIGN KEY (device_id) REFERENCES dispositivos(id),
  FOREIGN KEY (user_id) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
--
-- Estrutura para tabela `itens_venda`
--

CREATE TABLE `itens_venda` (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sale_id INT NOT NULL,
  product_tenant_id INT NOT NULL,
  product_concept_id INT NOT NULL,
  product_variant_id INT NOT NULL,
  similarity_cluster_id INT NOT NULL,
  snapshot_product_name VARCHAR(200),
  qtd INT(11) NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  discount DECIMAL(10,2) NOT NULL,
  subtotal DECIMAL(10,2) NOT NULL,
  total_price DECIMAL(10,2) NOT NULL,
  print_mode_form INT(11) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  status TINYINT(2) NOT NULL,
  trash TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (sale_id) REFERENCES vendas(id),
  FOREIGN KEY (product_tenant_id) REFERENCES produtos_tenant(id),
  FOREIGN KEY (product_concept_id) REFERENCES conceitos_produtos(id),
  FOREIGN KEY (product_variant_id) REFERENCES variantes_produtos(id),
  FOREIGN KEY (similarity_cluster_id) REFERENCES clusters_similaridade(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
--
-- Estrutura para tabela `historico_status_vendas`
--

CREATE TABLE `historico_status_vendas` (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sale_id INT NOT NULL,
  previous_status TINYINT(1),
  new_status TINYINT(1),
  motivation VARCHAR(200),
  changed_by INT(11),
  modified_on TIMESTAMP NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status TINYINT(2) NOT NULL DEFAULT 1,
  trash TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (sale_id) REFERENCES vendas(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;