-- Módulo Vendas — Comissões (runs, items, payments)
-- Portável para MySQL/SQLite; tolerante a reexecuções.

-- Execuções de apuração de comissões
CREATE TABLE IF NOT EXISTS commission_runs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  period_type VARCHAR(20) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  seller_emails_json TEXT,
  empresa_ids_json TEXT,
  total_revenue_cents BIGINT DEFAULT 0,
  total_commission_cents BIGINT DEFAULT 0
);

-- Itens por vendedor em cada execução
CREATE TABLE IF NOT EXISTS commission_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id INTEGER NOT NULL,
  seller_email VARCHAR(255),
  seller_name VARCHAR(255),
  revenue_cents BIGINT DEFAULT 0,
  commission_percent DECIMAL(7,4) DEFAULT 0,
  commission_cents BIGINT DEFAULT 0
);

-- Índices auxiliares
CREATE INDEX IF NOT EXISTS idx_comm_items_run ON commission_items(run_id);
CREATE INDEX IF NOT EXISTS idx_comm_items_seller ON commission_items(seller_email);

-- Pagamentos de comissões por execução/vendedor
CREATE TABLE IF NOT EXISTS commission_payments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id INTEGER NOT NULL,
  seller_email VARCHAR(255),
  amount_cents BIGINT NOT NULL,
  status VARCHAR(32) DEFAULT 'pendente',
  occurred_at DATETIME,
  provider VARCHAR(64),
  external_id VARCHAR(128),
  notes TEXT
);

CREATE INDEX IF NOT EXISTS idx_comm_payments_run ON commission_payments(run_id);
CREATE INDEX IF NOT EXISTS idx_comm_payments_seller ON commission_payments(seller_email);

-- Para MySQL, compatibilidade de AUTO_INCREMENT
-- (Ignorado em SQLite)
ALTER TABLE commission_runs AUTO_INCREMENT=1;
ALTER TABLE commission_items AUTO_INCREMENT=1;
ALTER TABLE commission_payments AUTO_INCREMENT=1;