-- Banco: oferta_crm (crie no cPanel e importe este SQL)
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','affiliate') NOT NULL DEFAULT 'affiliate',
  display_name VARCHAR(190) NOT NULL,
  affiliate_code VARCHAR(50) UNIQUE,
  default_commission_pct DECIMAL(5,2) NOT NULL DEFAULT 3.00,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  affiliate_id INT NOT NULL,
  full_name VARCHAR(190) NOT NULL,
  whatsapp VARCHAR(40),
  city VARCHAR(100),
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (affiliate_id),
  CONSTRAINT fk_clients_aff FOREIGN KEY (affiliate_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  affiliate_id INT NOT NULL,
  client_id INT NOT NULL,
  status ENUM('PROPOSTA','FECHADO','CANCELADO') NOT NULL DEFAULT 'PROPOSTA',
  payment_method VARCHAR(50),
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
  discount_value DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  commission_pct DECIMAL(5,2) NOT NULL DEFAULT 3.00,
  commission_value DECIMAL(12,2) NOT NULL DEFAULT 0,
  commission_status ENUM('PENDENTE','A_PAGAR','PAGO') NOT NULL DEFAULT 'PENDENTE',
  utm_source VARCHAR(100),
  utm_medium VARCHAR(100),
  utm_campaign VARCHAR(100),
  utm_term VARCHAR(100),
  utm_content VARCHAR(100),
  accepted_at DATETIME NULL,
  closed_at DATETIME NULL,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (affiliate_id), INDEX (client_id), INDEX (status), INDEX (created_at),
  CONSTRAINT fk_orders_aff FOREIGN KEY (affiliate_id) REFERENCES users(id),
  CONSTRAINT fk_orders_cli FOREIGN KEY (client_id) REFERENCES clients(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_name VARCHAR(190) NOT NULL,
  sku VARCHAR(100),
  quantity INT NOT NULL DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(12,2) NOT NULL DEFAULT 0,
  INDEX (order_id),
  CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
