-- Tabelas de Cadastros
CREATE TABLE IF NOT EXISTS empresas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(200) NOT NULL,
  razao_social VARCHAR(200) NULL,
  cnpj VARCHAR(20) NULL,
  ie VARCHAR(30) NULL,
  im VARCHAR(30) NULL,
  email VARCHAR(200) NULL,
  telefone VARCHAR(50) NULL,
  site_url VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fornecedores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  empresa_id INT NULL,
  nome VARCHAR(200) NOT NULL,
  tipo_pessoa ENUM('F','J') NULL,
  cpf VARCHAR(20) NULL,
  cnpj VARCHAR(20) NULL,
  ie VARCHAR(30) NULL,
  email VARCHAR(200) NULL,
  telefone VARCHAR(50) NULL,
  contato VARCHAR(200) NULL,
  site_url VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX (empresa_id),
  CONSTRAINT fk_fornecedores_empresas FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS transportadoras (
  id INT AUTO_INCREMENT PRIMARY KEY,
  empresa_id INT NULL,
  nome VARCHAR(200) NOT NULL,
  cnpj VARCHAR(20) NULL,
  ie VARCHAR(30) NULL,
  email VARCHAR(200) NULL,
  telefone VARCHAR(50) NULL,
  capacidade VARCHAR(100) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX (empresa_id),
  CONSTRAINT fk_transportadoras_empresas FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS vendedores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  empresa_id INT NULL,
  nome VARCHAR(200) NOT NULL,
  email VARCHAR(200) NULL,
  telefone VARCHAR(50) NULL,
  comissao_percent DECIMAL(5,2) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX (empresa_id),
  CONSTRAINT fk_vendedores_empresas FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS tecnicos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  empresa_id INT NULL,
  nome VARCHAR(200) NOT NULL,
  email VARCHAR(200) NULL,
  telefone VARCHAR(50) NULL,
  especialidade VARCHAR(150) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX (empresa_id),
  CONSTRAINT fk_tecnicos_empresas FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;