-- 002: cadastros (categorias, marcas, fornecedores, clientes, produtos) e estoque
CREATE TABLE categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  parent_id BIGINT UNSIGNED NULL,
  name VARCHAR(120) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  version INT UNSIGNED NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_categories_uuid (uuid),
  UNIQUE KEY uq_categories_name (company_id, parent_id, name),
  KEY idx_categories_updated (company_id, updated_at),
  CONSTRAINT fk_categories_company FOREIGN KEY (company_id) REFERENCES companies (id),
  CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id) REFERENCES categories (id)
);
CREATE TABLE brands (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  version INT UNSIGNED NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_brands_uuid (uuid),
  UNIQUE KEY uq_brands_name (company_id, name),
  CONSTRAINT fk_brands_company FOREIGN KEY (company_id) REFERENCES companies (id)
);
CREATE TABLE suppliers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  legal_name VARCHAR(160) NOT NULL,
  trade_name VARCHAR(160) NULL,
  document VARCHAR(20) NULL,
  phone VARCHAR(30) NULL,
  email VARCHAR(160) NULL,
  address TEXT NULL,
  contact_name VARCHAR(120) NULL,
  notes TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  version INT UNSIGNED NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_suppliers_uuid (uuid),
  KEY idx_suppliers_company (company_id, legal_name),
  KEY idx_suppliers_document (company_id, document),
  CONSTRAINT fk_suppliers_company FOREIGN KEY (company_id) REFERENCES companies (id)
);
CREATE TABLE customers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  document VARCHAR(20) NULL,
  phone VARCHAR(30) NULL,
  whatsapp VARCHAR(30) NULL,
  email VARCHAR(160) NULL,
  address TEXT NULL,
  notes TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  version INT UNSIGNED NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_customers_uuid (uuid),
  KEY idx_customers_name (company_id, name),
  KEY idx_customers_document (company_id, document),
  KEY idx_customers_phone (company_id, phone),
  KEY idx_customers_updated (company_id, updated_at),
  CONSTRAINT fk_customers_company FOREIGN KEY (company_id) REFERENCES companies (id)
);
CREATE TABLE products (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  sku VARCHAR(60) NOT NULL,
  barcode VARCHAR(30) NULL,
  name VARCHAR(190) NOT NULL,
  description TEXT NULL,
  category_id BIGINT UNSIGNED NULL,
  brand_id BIGINT UNSIGNED NULL,
  supplier_id BIGINT UNSIGNED NULL,
  unit VARCHAR(10) NOT NULL DEFAULT 'UN',
  cost_price DECIMAL(14,2) NOT NULL DEFAULT 0,
  sale_price DECIMAL(14,2) NOT NULL DEFAULT 0,
  wholesale_price DECIMAL(14,2) NULL,
  wholesale_min_qty DECIMAL(14,3) NULL,
  promo_price DECIMAL(14,2) NULL,
  promo_starts_at DATETIME NULL,
  promo_ends_at DATETIME NULL,
  margin DECIMAL(8,2) NOT NULL DEFAULT 0,
  min_stock DECIMAL(14,3) NOT NULL DEFAULT 0,
  max_stock DECIMAL(14,3) NULL,
  image_path VARCHAR(255) NULL,
  ncm VARCHAR(10) NULL,
  cest VARCHAR(10) NULL,
  cfop VARCHAR(6) NULL,
  origin_code CHAR(1) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  version INT UNSIGNED NOT NULL DEFAULT 1,
  deleted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_products_uuid (uuid),
  UNIQUE KEY uq_products_sku (company_id, sku),
  KEY idx_products_barcode (company_id, barcode),
  KEY idx_products_name (company_id, name),
  KEY idx_products_category (category_id),
  KEY idx_products_updated (company_id, updated_at),
  FULLTEXT KEY ft_products_search (name, description),
  CONSTRAINT fk_products_company FOREIGN KEY (company_id) REFERENCES companies (id),
  CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories (id),
  CONSTRAINT fk_products_brand FOREIGN KEY (brand_id) REFERENCES brands (id),
  CONSTRAINT fk_products_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id)
);
CREATE TABLE product_variations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  sku VARCHAR(60) NOT NULL,
  barcode VARCHAR(30) NULL,
  name VARCHAR(120) NOT NULL,
  cost_price DECIMAL(14,2) NULL,
  sale_price DECIMAL(14,2) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_variations_uuid (uuid),
  UNIQUE KEY uq_variations_sku (product_id, sku),
  KEY idx_variations_barcode (barcode),
  CONSTRAINT fk_variations_product FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE
);
CREATE TABLE stock (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  variation_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
  quantity DECIMAL(14,3) NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_stock_item (store_id, product_id, variation_id),
  KEY idx_stock_product (product_id),
  CONSTRAINT fk_stock_company FOREIGN KEY (company_id) REFERENCES companies (id),
  CONSTRAINT fk_stock_store FOREIGN KEY (store_id) REFERENCES stores (id),
  CONSTRAINT fk_stock_product FOREIGN KEY (product_id) REFERENCES products (id)
);
CREATE TABLE stock_movements (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  variation_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
  type ENUM('ENTRY','EXIT','SALE','RETURN','ADJUSTMENT','INVENTORY','TRANSFER_IN','TRANSFER_OUT','LOSS','DAMAGE') NOT NULL,
  quantity DECIMAL(14,3) NOT NULL,
  balance_before DECIMAL(14,3) NOT NULL,
  balance_after DECIMAL(14,3) NOT NULL,
  reason VARCHAR(255) NULL,
  user_id BIGINT UNSIGNED NULL,
  origin ENUM('PDV','BACKOFFICE','SYNC','SYSTEM','WEBHOOK') NOT NULL DEFAULT 'BACKOFFICE',
  reference_type VARCHAR(40) NULL,
  reference_id VARCHAR(64) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_stock_mov_uuid (uuid),
  KEY idx_stock_mov_product (product_id, created_at),
  KEY idx_stock_mov_ref (reference_type, reference_id),
  KEY idx_stock_mov_store (store_id, created_at),
  CONSTRAINT fk_stock_mov_company FOREIGN KEY (company_id) REFERENCES companies (id),
  CONSTRAINT fk_stock_mov_store FOREIGN KEY (store_id) REFERENCES stores (id),
  CONSTRAINT fk_stock_mov_product FOREIGN KEY (product_id) REFERENCES products (id),
  CONSTRAINT fk_stock_mov_user FOREIGN KEY (user_id) REFERENCES users (id)
);