// Webhook para receber dados
const { name, description } = $input().data;

// Conexão com SQLite
const db = $sqlite('database.db');

// Inserir no banco de dados
const result = db.run(`
INSERT INTO sectors (name, description)
VALUES (?, ?)
`, [name, description]);

// Retornar resposta
$return({
  success: true,
  message: "Setor criado com sucesso",
  id: result.lastID
});

// Receber setor do webhook
const { sectorId, subject, message } = $input().data;

// Buscar e-mails dos usuários do setor
const db = $sqlite('database.db');
const users = db.query(`
SELECT email FROM users WHERE sector_id = ?
`, [sectorId]);

// Preparar lista de destinatários
const recipients = users.map(user => user.email).join(';');

// Enviar e-mail com Microsoft Outlook
const outlook = $node('n8n-nodes-base.microsoftOutlook');
const emailResult = outlook.execute('sendEmail', {
  to: recipients,
  subject: subject,
  body: message,
  contentType: 'HTML'
});

$return({
  success: true,
  message: "E-mail enviado com sucesso",
  recipients: recipients
});

// 1. Webhook para receber dados do formulário
const { title, questions, sectors } = $input().data;

// 2. Salvar formulário no SQLite
const db = $sqlite('database.db');
const formResult = db.run(`
INSERT INTO forms (title, questions_json)
VALUES (?, ?)
`, [title, JSON.stringify(questions)]);

// 3. Para cada setor selecionado
for (const sectorId of sectors) {
  // Buscar e-mails dos usuários do setor
  const users = db.query(`
SELECT email FROM users WHERE sector_id = ?
`, [sectorId]);

  // Enviar e-mail para cada usuário
  const recipients = users.map(user => user.email).join(';');
  const outlook = $node('n8n-nodes-base.microsoftOutlook');
  outlook.execute('sendEmail', {
    to: recipients,
    subject: `Novo formulário: ${title}`,
    body: generateEmailBody(title, questions),
    contentType: 'HTML'
  });
}

$return({
  success: true,
  message: "Formulário processado e e-mails enviados"
});

-- Tabela de setores
CREATE TABLE sectors (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  description TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Tabela de usuários
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  sector_id INTEGER NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (sector_id) REFERENCES sectors(id)
);

-- Tabela de formulários
CREATE TABLE forms (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  questions_json TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Tabela de relacionamento formulários-setores
CREATE TABLE form_sectors (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  form_id INTEGER NOT NULL,
  sector_id INTEGER NOT NULL,
  FOREIGN KEY (form_id) REFERENCES forms(id),
  FOREIGN KEY (sector_id) REFERENCES sectors(id)
);

-- Buscar todos os usuários de um setor
SELECT u.name, u.email, s.name AS sector_name
FROM users u
JOIN sectors s ON u.sector_id = s.id
WHERE s.id = ?;

-- Contar usuários por setor
SELECT s.name, COUNT(u.id) AS user_count
FROM sectors s
LEFT JOIN users u ON s.id = u.sector_id
GROUP BY s.id;

-- Buscar formulários e seus setores destinatários
SELECT f.title, f.created_at, GROUP_CONCAT(s.name, ', ') AS sectors
FROM forms f
JOIN form_sectors fs ON f.id = fs.form_id
JOIN sectors s ON fs.sector_id = s.id
GROUP BY f.id;