-- SUPERFAX HELPDESK ENTERPRISE FULL V1
-- Execute no banco suporte pelo phpMyAdmin.
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS stock_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(80) DEFAULT NULL,
  name VARCHAR(180) NOT NULL,
  category VARCHAR(80) DEFAULT 'Peça',
  compatible_models TEXT DEFAULT NULL,
  quantity INT NOT NULL DEFAULT 0,
  min_quantity INT NOT NULL DEFAULT 0,
  unit_cost DECIMAL(10,2) DEFAULT 0.00,
  location VARCHAR(120) DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS stock_movements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  item_id INT DEFAULT NULL,
  type ENUM('entrada','saida','ajuste') DEFAULT 'entrada',
  quantity INT NOT NULL DEFAULT 1,
  ticket_id INT DEFAULT NULL,
  equipment_id INT DEFAULT NULL,
  technician_id INT DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_item (item_id), INDEX idx_ticket (ticket_id), INDEX idx_equipment (equipment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT DEFAULT NULL,
  contract_id INT DEFAULT NULL,
  description VARCHAR(220) DEFAULT NULL,
  amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  due_date DATE DEFAULT NULL,
  paid_at DATE DEFAULT NULL,
  status ENUM('Aberta','Paga','Vencida','Cancelada') DEFAULT 'Aberta',
  payment_method VARCHAR(60) DEFAULT NULL,
  pix_code TEXT DEFAULT NULL,
  boleto_url VARCHAR(255) DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_customer (customer_id), INDEX idx_contract (contract_id), INDEX idx_due (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS technician_visits (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT DEFAULT NULL,
  technician_id INT DEFAULT NULL,
  equipment_id INT DEFAULT NULL,
  checkin_at DATETIME DEFAULT NULL,
  checkout_at DATETIME DEFAULT NULL,
  latitude VARCHAR(60) DEFAULT NULL,
  longitude VARCHAR(60) DEFAULT NULL,
  checklist TEXT DEFAULT NULL,
  signature_path VARCHAR(255) DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ticket (ticket_id), INDEX idx_tech (technician_id), INDEX idx_equipment (equipment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS message_templates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  channel VARCHAR(40) DEFAULT 'WhatsApp',
  subject VARCHAR(180) DEFAULT NULL,
  body TEXT DEFAULT NULL,
  active TINYINT DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS whatsapp_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_phone VARCHAR(60) DEFAULT NULL,
  customer_name VARCHAR(160) DEFAULT NULL,
  direction ENUM('in','out') DEFAULT 'in',
  message TEXT DEFAULT NULL,
  ticket_id INT DEFAULT NULL,
  status VARCHAR(60) DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ticket (ticket_id), INDEX idx_phone (customer_phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS approvals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT DEFAULT NULL,
  customer_id INT DEFAULT NULL,
  type VARCHAR(80) DEFAULT 'Orçamento',
  description TEXT DEFAULT NULL,
  amount DECIMAL(10,2) DEFAULT 0.00,
  status ENUM('Pendente','Aprovado','Recusado') DEFAULT 'Pendente',
  approved_at DATETIME DEFAULT NULL,
  signature_path VARCHAR(255) DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ticket (ticket_id), INDEX idx_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Campos adicionais seguros
ALTER TABLE tickets ADD COLUMN IF NOT EXISTS approval_status VARCHAR(40) DEFAULT NULL;
ALTER TABLE tickets ADD COLUMN IF NOT EXISTS source_message_id VARCHAR(120) DEFAULT NULL;
ALTER TABLE tickets ADD COLUMN IF NOT EXISTS customer_signature VARCHAR(255) DEFAULT NULL;
ALTER TABLE tickets ADD COLUMN IF NOT EXISTS downtime_minutes INT DEFAULT 0;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS franchise_bw INT DEFAULT 0;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS franchise_color INT DEFAULT 0;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS excess_bw_value DECIMAL(10,4) DEFAULT 0.0000;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS excess_color_value DECIMAL(10,4) DEFAULT 0.0000;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS billing_day INT DEFAULT 10;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS contact_name VARCHAR(160) DEFAULT NULL;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS city VARCHAR(120) DEFAULT NULL;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS state VARCHAR(40) DEFAULT NULL;

INSERT INTO stock_items (sku,name,category,compatible_models,quantity,min_quantity,unit_cost,location)
SELECT 'TN-DEMO-001','Toner Compatível Demonstração','Toner','Brother, HP, Ricoh',10,3,85.00,'Almoxarifado'
WHERE NOT EXISTS (SELECT 1 FROM stock_items WHERE sku='TN-DEMO-001');
INSERT INTO stock_items (sku,name,category,compatible_models,quantity,min_quantity,unit_cost,location)
SELECT 'FUS-DEMO-001','Fusor Demonstração','Peça','Multifuncionais laser',2,1,450.00,'Almoxarifado'
WHERE NOT EXISTS (SELECT 1 FROM stock_items WHERE sku='FUS-DEMO-001');

INSERT INTO message_templates (name,channel,subject,body)
SELECT 'Chamado aberto','WhatsApp','Chamado recebido','Olá, recebemos seu chamado #{protocol}. Nossa equipe irá acompanhar.'
WHERE NOT EXISTS (SELECT 1 FROM message_templates WHERE name='Chamado aberto');
INSERT INTO message_templates (name,channel,subject,body)
SELECT 'Técnico a caminho','WhatsApp','Técnico deslocado','O técnico foi acionado e está a caminho para o atendimento.'
WHERE NOT EXISTS (SELECT 1 FROM message_templates WHERE name='Técnico a caminho');

INSERT INTO invoices (customer_id, contract_id, description, amount, due_date, status, payment_method)
SELECT 1, 1, 'Mensalidade contrato demonstração', 450.00, DATE_ADD(CURDATE(), INTERVAL 10 DAY), 'Aberta', 'PIX'
WHERE NOT EXISTS (SELECT 1 FROM invoices WHERE description='Mensalidade contrato demonstração');

INSERT INTO settings (`key`,`value`) VALUES
('enterprise_version','FULL_V1'),
('whatsapp_webhook_enabled','0'),
('smtp_enabled','0'),
('pwa_enabled','1'),
('default_sla_hours','8')
ON DUPLICATE KEY UPDATE value=VALUES(value);