-- ====================================================================
-- ARMONY CORTE PRO - BANCO DE DADOS PARA MYSQL / MARIADB / PHPMYADMIN
-- Sistema Industrial de Otimização e Plano de Corte de Vidro
-- Compatível com: MySQL 5.7+, MySQL 8.0+, MariaDB 10.3+, phpMyAdmin
-- Collation: utf8mb4_unicode_ci
-- ====================================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
SET NAMES utf8mb4;

-- --------------------------------------------------------------------
-- 1. SELEÇÃO DO BANCO DE DADOS
-- Na Hostinger / cPanel: selecione o banco de dados criado no menu lateral do phpMyAdmin antes de importar.
-- No XAMPP / Local: descomente as 2 linhas abaixo se o banco não existir:
-- CREATE DATABASE IF NOT EXISTS `armony_corte_pro` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE `armony_corte_pro`;
-- --------------------------------------------------------------------

-- --------------------------------------------------------------------
-- 2. TABELA: configuracoes (Parâmetros globais do sistema)
-- --------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `configuracoes` (
  `chave` VARCHAR(64) NOT NULL,
  `valor` TEXT NOT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`chave`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------
-- 3. TABELA: usuarios (Operadores, Engenheiros e Administradores)
-- --------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `usuarios` (
  `id` VARCHAR(64) NOT NULL,
  `username` VARCHAR(64) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `role` ENUM('admin', 'operador', 'engenheiro') NOT NULL DEFAULT 'operador',
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `avatar` VARCHAR(255) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------
-- 4. TABELA: clientes (Cadastro de Clientes e Obras)
-- --------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `clientes` (
  `id` VARCHAR(64) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `document` VARCHAR(30) DEFAULT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `email` VARCHAR(150) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_cliente_nome` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------
-- 5. TABELA: chapas_estoque (Catálogo de Tipos e Espessuras de Vidro)
-- --------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `chapas_estoque` (
  `id` VARCHAR(64) NOT NULL,
  `code` VARCHAR(50) NOT NULL,
  `description` VARCHAR(150) NOT NULL,
  `thickness` DECIMAL(5,2) NOT NULL DEFAULT 8.00,
  `density` DECIMAL(5,2) NOT NULL DEFAULT 2.50,
  `category` VARCHAR(50) NOT NULL DEFAULT 'float',
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `notes` TEXT DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_chapa_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------
-- 6. TABELA: chapas_dimensoes (Dimensões e Quantidades em Estoque)
-- --------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `chapas_dimensoes` (
  `id` VARCHAR(64) NOT NULL,
  `glass_stock_id` VARCHAR(64) NOT NULL,
  `label` VARCHAR(64) NOT NULL,
  `width` INT NOT NULL,
  `height` INT NOT NULL,
  `stock_quantity` INT NOT NULL DEFAULT 0,
  `weight_kg` DECIMAL(8,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_dim_stock_id` (`glass_stock_id`),
  CONSTRAINT `fk_chapa_dimensao` FOREIGN KEY (`glass_stock_id`) REFERENCES `chapas_estoque` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------
-- 7. TABELA: planos_corte (Ordens de Produção e Planos de Corte)
-- --------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `planos_corte` (
  `id` VARCHAR(64) NOT NULL,
  `code` VARCHAR(50) NOT NULL,
  `description` VARCHAR(200) NOT NULL,
  `date` DATE NOT NULL,
  `client_project` VARCHAR(150) DEFAULT NULL,
  `customer_id` VARCHAR(64) DEFAULT NULL,
  `customer_name` VARCHAR(150) DEFAULT NULL,
  `operator` VARCHAR(100) NOT NULL DEFAULT 'Administrador Master',
  `glass_stock_id` VARCHAR(64) DEFAULT NULL,
  `selected_dimension_id` VARCHAR(64) DEFAULT NULL,
  `sheet_width` INT NOT NULL DEFAULT 3210,
  `sheet_height` INT NOT NULL DEFAULT 2400,
  `sheet_thickness` DECIMAL(5,2) NOT NULL DEFAULT 8.00,
  `kerf` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `trim_margin` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `tolerance` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('draft', 'optimized', 'in_production', 'completed') NOT NULL DEFAULT 'draft',
  `optimization_result_json` LONGTEXT DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_plano_code` (`code`),
  KEY `idx_plano_customer` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------
-- 8. TABELA: itens_pecas (Peças do Plano de Corte)
-- --------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `itens_pecas` (
  `id` VARCHAR(64) NOT NULL,
  `plan_id` VARCHAR(64) NOT NULL,
  `auto_code` VARCHAR(20) NOT NULL,
  `tag` VARCHAR(50) NOT NULL,
  `description` VARCHAR(200) NOT NULL,
  `glass_stock_id` VARCHAR(64) DEFAULT NULL,
  `glass_description` VARCHAR(150) DEFAULT NULL,
  `thickness` DECIMAL(5,2) DEFAULT NULL,
  `customer_id` VARCHAR(64) DEFAULT NULL,
  `customer_name` VARCHAR(150) DEFAULT NULL,
  `width` INT NOT NULL,
  `height` INT NOT NULL,
  `quantity` INT NOT NULL DEFAULT 1,
  `allow_rotation` TINYINT(1) NOT NULL DEFAULT 1,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `idx_item_plan_id` (`plan_id`),
  CONSTRAINT `fk_item_plano` FOREIGN KEY (`plan_id`) REFERENCES `planos_corte` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================================================
-- CARGA INICIAL DE DADOS PADRÃO
-- ====================================================================

-- 1. Usuário Admin Padrão
INSERT INTO `usuarios` (`id`, `username`, `name`, `email`, `role`, `active`, `avatar`, `created_at`)
VALUES ('u-admin', 'admin', 'Administrador Master', 'admin@armony.com.br', 'admin', 1, NULL, CURRENT_TIMESTAMP)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- 2. Configurações Iniciais
INSERT INTO `configuracoes` (`chave`, `valor`) VALUES
('app_name', 'Armony Corte Pro'),
('version', '4.2.0'),
('default_kerf', '0'),
('default_trim_margin', '0'),
('default_tolerance', '0'),
('last_sync', CURRENT_TIMESTAMP)
ON DUPLICATE KEY UPDATE `valor` = VALUES(`valor`);

-- 3. Plano Inicial Padrão
INSERT INTO `planos_corte` (
  `id`, `code`, `description`, `date`, `client_project`, `customer_id`, `customer_name`,
  `operator`, `glass_stock_id`, `selected_dimension_id`, `sheet_width`, `sheet_height`,
  `sheet_thickness`, `kerf`, `trim_margin`, `tolerance`, `status`, `optimization_result_json`,
  `created_at`, `updated_at`
) VALUES (
  'plan-1', 'PLC-0001', 'Novo Plano de Corte 01', CURRENT_DATE, '', NULL, NULL,
  'Administrador Master', NULL, NULL, 3210, 2400,
  8.00, 0.00, 0.00, 0.00, 'draft', NULL,
  CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
)
ON DUPLICATE KEY UPDATE `code` = VALUES(`code`);

COMMIT;
-- FIM DO SCRIPT PHPMYADMIN / MYSQL