-- Banco de Dados: fechamento_armazenagem
CREATE DATABASE IF NOT EXISTS `fechamento_armazenagem` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `fechamento_armazenagem`;

-- 1. Tabela de Usuários
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` VARCHAR(20) NOT NULL DEFAULT 'admin',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Tabela de Clientes
CREATE TABLE IF NOT EXISTS `clients` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `code` VARCHAR(50) NOT NULL UNIQUE,
  `volume_calc_type` ENUM('XML', 'PRODUCT_CONVERSION') NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Tabela de Produtos (Conversão Croda)
CREATE TABLE IF NOT EXISTS `products` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id` INT UNSIGNED NOT NULL,
  `product_code` VARCHAR(60) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `unit_of_measure` VARCHAR(10) NOT NULL DEFAULT 'UN',
  `units_per_volume` DECIMAL(12, 4) NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  INDEX `idx_client_product` (`client_id`, `product_code`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Tabela de Importações (Histórico de Uploads)
CREATE TABLE IF NOT EXISTS `imports` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id` INT UNSIGNED NOT NULL,
  `original_filename` VARCHAR(255) NOT NULL,
  `file_type` VARCHAR(10) NOT NULL, -- 'XML' ou 'ZIP'
  `xml_count` INT NOT NULL DEFAULT 0,
  `processed_count` INT NOT NULL DEFAULT 0,
  `pending_count` INT NOT NULL DEFAULT 0,
  `error_count` INT NOT NULL DEFAULT 0,
  `duplicate_count` INT NOT NULL DEFAULT 0,
  `user_id` INT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Tabela de Notas Fiscais (Invoices)
CREATE TABLE IF NOT EXISTS `invoices` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `import_id` INT UNSIGNED NULL,
  `client_id` INT UNSIGNED NOT NULL,
  `nfe_key` VARCHAR(44) NOT NULL,
  `number` VARCHAR(20) NOT NULL,
  `series` VARCHAR(10) NOT NULL,
  `emission_date` DATE NOT NULL,
  `issuer_cnpj` VARCHAR(20) NOT NULL,
  `issuer_name` VARCHAR(150) NOT NULL,
  `recipient_cnpj` VARCHAR(20) NOT NULL,
  `recipient_name` VARCHAR(150) NOT NULL,
  `total_amount` DECIMAL(15, 2) NOT NULL DEFAULT '0.00',
  `volume_quantity` DECIMAL(12, 4) NOT NULL DEFAULT '0.0000',
  `volume_origin` ENUM('XML', 'CALCULADO', 'MANUAL') NOT NULL,
  `status` ENUM('PROCESSADA', 'PENDENTE', 'ERRO') NOT NULL DEFAULT 'PROCESSADA',
  `pending_reason` TEXT NULL,
  `xml_filename` VARCHAR(255) NOT NULL,
  `file_hash` VARCHAR(64) NOT NULL,
  `recalculated_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`import_id`) REFERENCES `imports` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  UNIQUE KEY `uk_client_nfe_key` (`client_id`, `nfe_key`),
  INDEX `idx_emission_date` (`emission_date`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Tabela de Itens da Nota Fiscal
CREATE TABLE IF NOT EXISTS `invoice_items` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `invoice_id` INT UNSIGNED NOT NULL,
  `product_code` VARCHAR(60) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `com_qty` DECIMAL(15, 4) NOT NULL DEFAULT '0.0000',
  `com_unit` VARCHAR(10) NOT NULL,
  `trib_qty` DECIMAL(15, 4) NOT NULL DEFAULT '0.0000',
  `trib_unit` VARCHAR(10) NOT NULL,
  `units_per_volume_used` DECIMAL(12, 4) NULL,
  `calculated_volumes` DECIMAL(12, 4) NULL,
  `calculation_status` ENUM('OK', 'MISSING_PRODUCT') NOT NULL DEFAULT 'OK',
  FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Histórico Auditável de Cálculos e Recálculos
CREATE TABLE IF NOT EXISTS `invoice_volume_calculations` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `invoice_id` INT UNSIGNED NOT NULL,
  `calculation_type` VARCHAR(50) NOT NULL, -- 'INITIAL', 'RECALCULATION', 'MANUAL_ADJUSTMENT'
  `input_data` JSON NULL,
  `result_data` JSON NULL,
  `user_id` INT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Erros de Processamento
CREATE TABLE IF NOT EXISTS `processing_errors` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `import_id` INT UNSIGNED NULL,
  `filename` VARCHAR(255) NOT NULL,
  `error_message` TEXT NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`import_id`) REFERENCES `imports` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
