CREATE TABLE IF NOT EXISTS users (id int auto_increment primary key,name varchar(120),email varchar(160) unique,password varchar(255),role enum('admin','manager','agent','technician','customer') default 'customer',phone varchar(30),company varchar(160),active tinyint default 1,created_at datetime default current_timestamp);
CREATE TABLE IF NOT EXISTS customers (id int auto_increment primary key,name varchar(160),email varchar(160),phone varchar(40),document varchar(40),address varchar(255),created_at datetime default current_timestamp);
CREATE TABLE IF NOT EXISTS tickets (id int auto_increment primary key,protocol varchar(30),customer_id int,user_id int,technician_id int,title varchar(200),description text,channel varchar(40) default 'Portal',priority enum('Baixa','Média','Alta','Urgente') default 'Média',status enum('Aberto','Em atendimento','Aguardando cliente','Concluído','Cancelado') default 'Aberto',sla_due datetime,created_at datetime default current_timestamp,updated_at datetime null);
CREATE TABLE IF NOT EXISTS ticket_messages (id int auto_increment primary key,ticket_id int,user_id int,message text,internal tinyint default 0,created_at datetime default current_timestamp);
CREATE TABLE IF NOT EXISTS equipment (id int auto_increment primary key,customer_id int,type varchar(80),brand varchar(80),model varchar(120),serial varchar(120),asset_tag varchar(120),location varchar(160),notes text,created_at datetime default current_timestamp);
CREATE TABLE IF NOT EXISTS contracts (id int auto_increment primary key,customer_id int,number varchar(80),type varchar(80),description text,start_date date,end_date date,value decimal(10,2) default 0,status varchar(40) default 'Ativo',created_at datetime default current_timestamp);
CREATE TABLE IF NOT EXISTS toner_history (id int auto_increment primary key,equipment_id int,toner_model varchar(80),quantity int default 1,counter int,notes text,created_at datetime default current_timestamp);
CREATE TABLE IF NOT EXISTS maintenance_history (id int auto_increment primary key,equipment_id int,ticket_id int,description text,technician_id int,created_at datetime default current_timestamp);
CREATE TABLE IF NOT EXISTS knowledge_articles (id int auto_increment primary key,title varchar(200),category varchar(120),content text,published tinyint default 1,created_at datetime default current_timestamp);
CREATE TABLE IF NOT EXISTS settings (`key` varchar(120) primary key,`value` text);
CREATE TABLE IF NOT EXISTS audit_logs (id int auto_increment primary key,user_id int,action varchar(200),ip varchar(60),created_at datetime default current_timestamp);
INSERT IGNORE INTO users (name,email,password,role,company,active) VALUES ('Administrador Superfax','admin@superfaxsolucoes.com.br','$2y$10$qwZt3j0eqU9RK4PmmPpS2.F4B/0cnwUCrSYuNqZURueWPxHZbxP7y','admin','Superfax Soluções',1),('Técnico Superfax','tecnico@superfaxsolucoes.com.br','$2y$10$qwZt3j0eqU9RK4PmmPpS2.F4B/0cnwUCrSYuNqZURueWPxHZbxP7y','technician','Superfax Soluções',1),('Cliente Demonstração','cliente@demo.com.br','$2y$10$qwZt3j0eqU9RK4PmmPpS2.F4B/0cnwUCrSYuNqZURueWPxHZbxP7y','customer','Empresa Exemplo Ltda',1);
INSERT IGNORE INTO customers (id,name,email,phone,document,address) VALUES (1,'Empresa Exemplo Ltda','cliente@demo.com.br','(00) 90000-0000','00.000.000/0001-00','Endereço de demonstração');
INSERT IGNORE INTO equipment (customer_id,type,brand,model,serial,asset_tag,location) VALUES (1,'Impressora','Brother','DCP-L5652DN','BR123456','EQ-001','Recepção'),(1,'Impressora','HP','LaserJet Pro','HP98765','EQ-002','Financeiro');
INSERT IGNORE INTO contracts (customer_id,number,type,description,start_date,end_date,value,status) VALUES (1,'CT-2026-000125','Locação','Contrato de impressoras multifuncionais','2026-01-01','2027-01-15',450.00,'Ativo');
INSERT IGNORE INTO tickets (protocol,customer_id,user_id,title,description,channel,priority,status,sla_due) VALUES ('2487',1,3,'Impressora não liga','Cliente informa que a impressora da recepção não liga.','WhatsApp','Alta','Aberto',DATE_ADD(NOW(),INTERVAL 4 HOUR)),('2481',1,3,'Equipamento com falhas','Falhas recorrentes ao imprimir.','Portal','Média','Em atendimento',DATE_ADD(NOW(),INTERVAL 8 HOUR));
INSERT IGNORE INTO knowledge_articles (title,category,content) VALUES ('Como abrir um chamado','Portal','Acesse Novo Chamado, descreva o problema e envie fotos quando necessário.'),('Como solicitar toner','Suprimentos','Abra um chamado na categoria Toner e informe o modelo do equipamento.');
INSERT IGNORE INTO settings (`key`,`value`) VALUES ('company_name','Superfax Soluções'),('smtp_host',''),('smtp_user',''),('smtp_pass',''),('whatsapp_token',''),('whatsapp_phone_id',''),('pdf_footer','Documento gerado pelo Superfax HelpDesk Enterprise');