-- 003: caixa, vendas, pagamentos, devoluções
CREATE TABLE payment_methods (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(30) NOT NULL,
  name VARCHAR(60) NOT NULL,
  type ENUM('CASH','PIX','DEBIT','CREDIT','GATEWAY','OTHER') NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_payment_methods_code (company_id, code),
  CONSTRAINT fk_pm_company FOREIGN KEY (company_id) REFERENCES companies (id)
);
CREATE TABLE cash_registers (
  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,
  name VARCHAR(60) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_cash_registers_uuid (uuid),
  UNIQUE KEY uq_cash_registers_name (store_id, name),
  CONSTRAINT fk_cr_company FOREIGN KEY (company_id) REFERENCES companies (id),
  CONSTRAINT fk_cr_store FOREIGN KEY (store_id) REFERENCES stores (id)
);
CREATE TABLE cash_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  cash_register_id BIGINT UNSIGNED NOT NULL,
  operator_id BIGINT UNSIGNED NOT NULL,
  opened_at DATETIME NOT NULL,
  initial_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  closed_at DATETIME NULL,
  expected_amount DECIMAL(14,2) NULL,
  informed_amount DECIMAL(14,2) NULL,
  difference DECIMAL(14,2) NULL,
  status ENUM('OPEN','CLOSED') NOT NULL DEFAULT 'OPEN',
  closing_report JSON NULL,
  open_guard TINYINT(1) GENERATED ALWAYS AS (CASE WHEN status = 'OPEN' THEN 1 ELSE NULL END) VIRTUAL,
  UNIQUE KEY uq_cash_sessions_uuid (uuid),
  UNIQUE KEY uq_cash_one_open_per_register (cash_register_id, open_guard),
  KEY idx_cash_sessions_operator (operator_id, status),
  KEY idx_cash_sessions_date (company_id, opened_at),
  CONSTRAINT fk_cs_company FOREIGN KEY (company_id) REFERENCES companies (id),
  CONSTRAINT fk_cs_register FOREIGN KEY (cash_register_id) REFERENCES cash_registers (id),
  CONSTRAINT fk_cs_operator FOREIGN KEY (operator_id) REFERENCES users (id)
);
CREATE TABLE cash_movements (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  cash_session_id BIGINT UNSIGNED NOT NULL,
  type ENUM('SALE','SUPPLY','WITHDRAWAL','EXPENSE','REMOVAL','RETURN') NOT NULL,
  method_code VARCHAR(30) NOT NULL DEFAULT 'CASH',
  amount DECIMAL(14,2) NOT NULL,
  description VARCHAR(255) NULL,
  reference_type VARCHAR(40) NULL,
  reference_id VARCHAR(64) NULL,
  user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_cash_mov_uuid (uuid),
  KEY idx_cash_mov_session (cash_session_id, type),
  CONSTRAINT fk_cm_session FOREIGN KEY (cash_session_id) REFERENCES cash_sessions (id),
  CONSTRAINT fk_cm_user FOREIGN KEY (user_id) REFERENCES users (id)
);
CREATE TABLE sales (
  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,
  number INT UNSIGNED NULL,
  local_number VARCHAR(40) NULL,
  cash_session_id BIGINT UNSIGNED NULL,
  cash_register_id BIGINT UNSIGNED NULL,
  operator_id BIGINT UNSIGNED NOT NULL,
  seller_id BIGINT UNSIGNED NULL,
  customer_id BIGINT UNSIGNED NULL,
  subtotal DECIMAL(14,2) NOT NULL DEFAULT 0,
  discount DECIMAL(14,2) NOT NULL DEFAULT 0,
  surcharge DECIMAL(14,2) NOT NULL DEFAULT 0,
  total DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('AWAITING_PAYMENT','AWAITING_SYNC','COMPLETED','CANCELED','PARTIALLY_RETURNED','RETURNED') NOT NULL,
  origin ENUM('PDV_ONLINE','PDV_OFFLINE','BACKOFFICE') NOT NULL DEFAULT 'PDV_ONLINE',
  cancel_reason VARCHAR(255) NULL,
  canceled_at DATETIME NULL,
  canceled_by BIGINT UNSIGNED NULL,
  sold_at DATETIME NOT NULL,
  synced_at DATETIME NULL,
  notes VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_sales_uuid (uuid),
  UNIQUE KEY uq_sales_number (store_id, number),
  KEY idx_sales_date (company_id, sold_at),
  KEY idx_sales_status (company_id, status),
  KEY idx_sales_customer (customer_id),
  KEY idx_sales_operator (operator_id, sold_at),
  CONSTRAINT fk_sales_company FOREIGN KEY (company_id) REFERENCES companies (id),
  CONSTRAINT fk_sales_store FOREIGN KEY (store_id) REFERENCES stores (id),
  CONSTRAINT fk_sales_session FOREIGN KEY (cash_session_id) REFERENCES cash_sessions (id),
  CONSTRAINT fk_sales_operator FOREIGN KEY (operator_id) REFERENCES users (id),
  CONSTRAINT fk_sales_seller FOREIGN KEY (seller_id) REFERENCES users (id),
  CONSTRAINT fk_sales_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
);
CREATE TABLE sale_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  sale_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  variation_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
  description VARCHAR(190) NOT NULL,
  quantity DECIMAL(14,3) NOT NULL,
  unit_price DECIMAL(14,2) NOT NULL,
  cost_price DECIMAL(14,2) NOT NULL DEFAULT 0,
  discount DECIMAL(14,2) NOT NULL DEFAULT 0,
  total DECIMAL(14,2) NOT NULL,
  returned_quantity DECIMAL(14,3) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_sale_items_uuid (uuid),
  KEY idx_sale_items_sale (sale_id),
  KEY idx_sale_items_product (product_id),
  CONSTRAINT fk_si_sale FOREIGN KEY (sale_id) REFERENCES sales (id) ON DELETE CASCADE,
  CONSTRAINT fk_si_product FOREIGN KEY (product_id) REFERENCES products (id)
);
CREATE TABLE payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  sale_id BIGINT UNSIGNED NOT NULL,
  method_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  change_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  installments TINYINT UNSIGNED NOT NULL DEFAULT 1,
  status ENUM('PENDING','APPROVED','REJECTED','CANCELED','REFUNDED') NOT NULL DEFAULT 'PENDING',
  external_reference VARCHAR(80) NULL,
  paid_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_payments_uuid (uuid),
  KEY idx_payments_sale (sale_id),
  KEY idx_payments_status (status),
  KEY idx_payments_external (external_reference),
  CONSTRAINT fk_pay_sale FOREIGN KEY (sale_id) REFERENCES sales (id) ON DELETE CASCADE,
  CONSTRAINT fk_pay_method FOREIGN KEY (method_id) REFERENCES payment_methods (id)
);
CREATE TABLE sale_returns (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  sale_id BIGINT UNSIGNED NOT NULL,
  type ENUM('TOTAL','PARTIAL') NOT NULL,
  reason VARCHAR(255) NOT NULL,
  total DECIMAL(14,2) NOT NULL,
  refund_method_code VARCHAR(30) NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_sale_returns_uuid (uuid),
  KEY idx_sale_returns_sale (sale_id),
  CONSTRAINT fk_sr_company FOREIGN KEY (company_id) REFERENCES companies (id),
  CONSTRAINT fk_sr_sale FOREIGN KEY (sale_id) REFERENCES sales (id),
  CONSTRAINT fk_sr_user FOREIGN KEY (user_id) REFERENCES users (id)
);
CREATE TABLE sale_return_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  return_id BIGINT UNSIGNED NOT NULL,
  sale_item_id BIGINT UNSIGNED NOT NULL,
  quantity DECIMAL(14,3) NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  CONSTRAINT fk_sri_return FOREIGN KEY (return_id) REFERENCES sale_returns (id) ON DELETE CASCADE,
  CONSTRAINT fk_sri_item FOREIGN KEY (sale_item_id) REFERENCES sale_items (id)
);