-- schema.sql — Sistema de Ordens de Produção (OP)
-- Compatível com MySQL 5.7+ / MariaDB 10.4+

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

-- Produtos/Itens fabricados
CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(60) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  unit VARCHAR(20) NOT NULL DEFAULT 'UN',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Ordens de Produção
CREATE TABLE IF NOT EXISTS work_orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  product_id INT NOT NULL,
  qty DECIMAL(12,3) NOT NULL,
  status ENUM('ABERTA','EM_PRODUCAO','PAUSADA','CANCELADA','FINALIZADA') NOT NULL DEFAULT 'ABERTA',
  due_date DATE NULL,
  created_by INT NOT NULL,
  assigned_to INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  notes TEXT NULL,
  CONSTRAINT fk_wo_product FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_wo_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_wo_assigned_to FOREIGN KEY (assigned_to) REFERENCES users(id)
) ENGINE=InnoDB;

-- Itens (BOM) planejados para a OP
CREATE TABLE IF NOT EXISTS wo_materials_plan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  work_order_id INT NOT NULL,
  material_code VARCHAR(60) NOT NULL,
  description VARCHAR(255) NOT NULL,
  qty DECIMAL(12,3) NOT NULL,
  unit VARCHAR(20) NOT NULL,
  CONSTRAINT fk_womp_wo FOREIGN KEY (work_order_id) REFERENCES work_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Materiais consumidos reportados
CREATE TABLE IF NOT EXISTS wo_materials_used (
  id INT AUTO_INCREMENT PRIMARY KEY,
  work_order_id INT NOT NULL,
  material_code VARCHAR(60) NOT NULL,
  description VARCHAR(255) NOT NULL,
  qty DECIMAL(12,3) NOT NULL,
  unit VARCHAR(20) NOT NULL,
  used_by INT NULL,
  used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_womu_wo FOREIGN KEY (work_order_id) REFERENCES work_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_womu_user FOREIGN KEY (used_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- Etapas de produção (opcional simples)
CREATE TABLE IF NOT EXISTS wo_steps (
  id INT AUTO_INCREMENT PRIMARY KEY,
  work_order_id INT NOT NULL,
  step_name VARCHAR(120) NOT NULL,
  status ENUM('PENDENTE','EM_ANDAMENTO','CONCLUIDO') NOT NULL DEFAULT 'PENDENTE',
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  CONSTRAINT fk_wostep_wo FOREIGN KEY (work_order_id) REFERENCES work_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Apontamentos de tempo
CREATE TABLE IF NOT EXISTS time_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  work_order_id INT NOT NULL,
  user_id INT NOT NULL,
  started_at DATETIME NOT NULL,
  ended_at DATETIME NULL,
  notes VARCHAR(255) NULL,
  CONSTRAINT fk_te_wo FOREIGN KEY (work_order_id) REFERENCES work_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_te_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- Produtos exemplo
INSERT INTO products (sku, name, unit) VALUES
('PROD-001','Produto Padrão A','UN'),
('PROD-002','Produto Padrão B','UN')
ON DUPLICATE KEY UPDATE sku=sku;
-- Extensões para importação de Pedido (PDF) e geração de OP

USE opsdb;

CREATE TABLE IF NOT EXISTS customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) UNIQUE,
  name VARCHAR(200) NOT NULL,
  document VARCHAR(40) NULL,
  email VARCHAR(160) NULL,
  phone VARCHAR(60) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sales_orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  so_number VARCHAR(60) NOT NULL,
  customer_id INT NOT NULL,
  order_date DATE NULL,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_so_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sales_order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sales_order_id INT NOT NULL,
  sku VARCHAR(60) NULL,
  product_name VARCHAR(200) NOT NULL,
  qty DECIMAL(12,3) NOT NULL,
  unit VARCHAR(20) NOT NULL DEFAULT 'UN',
  CONSTRAINT fk_soi_so FOREIGN KEY (sales_order_id) REFERENCES sales_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;
