CREATE DATABASE IF NOT EXISTS `vex_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `vex_db`;

CREATE TABLE IF NOT EXISTS `items` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `sku` VARCHAR(255) UNIQUE NOT NULL,
  `nome` VARCHAR(255) NOT NULL,
  `categoria` VARCHAR(255) DEFAULT NULL,
  `foto_url` VARCHAR(255) DEFAULT NULL,
  `ativo` BOOLEAN DEFAULT TRUE,
  `custo_unit` DECIMAL(10, 2) DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `batches` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `item_id` INT NOT NULL,
  `fornecedor` VARCHAR(255) DEFAULT NULL,
  `lote` VARCHAR(255) DEFAULT NULL,
  `qtd_in` INT NOT NULL,
  `unit_cost` DECIMAL(10, 2) DEFAULT NULL,
  `expires_at` DATETIME DEFAULT NULL,
  `unidade_id` INT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`item_id`) REFERENCES `items`(`id`)
);

CREATE TABLE IF NOT EXISTS `stock_moves` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `item_id` INT NOT NULL,
  `move_type` VARCHAR(10) NOT NULL,
  `qty` INT NOT NULL,
  `unidade_id` INT DEFAULT NULL,
  `ref_table` VARCHAR(255) DEFAULT NULL,
  `ref_id` INT DEFAULT NULL,
  `created_by` INT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`item_id`) REFERENCES `items`(`id`)
);

CREATE TABLE IF NOT EXISTS `campaigns` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `nome` VARCHAR(255) NOT NULL,
  `inicio_em` DATETIME DEFAULT NULL,
  `fim_em` DATETIME DEFAULT NULL,
  `observacao` TEXT DEFAULT NULL,
  `ativo` BOOLEAN DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS `rules` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `tipo` VARCHAR(255) NOT NULL,
  `item_id` INT DEFAULT NULL,
  `campaign_id` INT DEFAULT NULL,
  `pessoa_id` INT DEFAULT NULL,
  `janela` VARCHAR(255) DEFAULT NULL,
  `limite_qtd` INT NOT NULL,
  `excecao_permitida` BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
  FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`)
);

CREATE TABLE IF NOT EXISTS `requests` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `pessoa_id` INT NOT NULL,
  `item_id` INT NOT NULL,
  `campaign_id` INT DEFAULT NULL,
  `qtd` INT NOT NULL,
  `status` VARCHAR(255) DEFAULT 'pendente',
  `motivo_negativa` TEXT DEFAULT NULL,
  `created_by` INT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
  FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`)
);

CREATE TABLE IF NOT EXISTS `giveaways` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `request_id` INT DEFAULT NULL,
  `pessoa_id` INT NOT NULL,
  `item_id` INT NOT NULL,
  `campaign_id` INT DEFAULT NULL,
  `qtd` INT NOT NULL,
  `unidade_id` INT DEFAULT NULL,
  `entregue_por` INT DEFAULT NULL,
  `aceite_digital` BOOLEAN DEFAULT FALSE,
  `aceite_ip` VARCHAR(45) DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`request_id`) REFERENCES `requests`(`id`),
  FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
  FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`)
);

CREATE TABLE IF NOT EXISTS `people` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `nome` VARCHAR(255) NOT NULL,
  `cpf` VARCHAR(14) UNIQUE DEFAULT NULL,
  `unidade_id` INT DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `actor_id` INT NOT NULL,
  `entity` VARCHAR(255) NOT NULL,
  `entity_id` INT NOT NULL,
  `action` VARCHAR(255) NOT NULL,
  `before_json` TEXT DEFAULT NULL,
  `after_json` TEXT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
);


