-- Projetos podem pertencer a mais de uma empresa (N:N)
CREATE TABLE IF NOT EXISTS ptk_project_company (
  id INT NOT NULL AUTO_INCREMENT,
  project_id INT NOT NULL,
  company_id INT NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_ptk_project_company (project_id, company_id),
  KEY idx_ptk_project_company_company (company_id),
  CONSTRAINT fk_ptk_project_company_project
    FOREIGN KEY (project_id) REFERENCES ptk_project (id) ON DELETE CASCADE,
  CONSTRAINT fk_ptk_project_company_person
    FOREIGN KEY (company_id) REFERENCES amb_person (id) ON DELETE CASCADE
);

-- Vínculo principal (company_id legado) de cada projeto
INSERT IGNORE INTO ptk_project_company (project_id, company_id)
SELECT id, company_id FROM ptk_project WHERE company_id IS NOT NULL;

-- Telhas Paraná → InfoERP Suporte (projeto compartilhado)
INSERT IGNORE INTO ptk_project_company (project_id, company_id)
SELECT p.id, 13
FROM ptk_project p
WHERE LOWER(p.description) LIKE '%infoerp%suporte%'
ORDER BY p.id ASC
LIMIT 1;

-- Unificar tickets do projeto duplicado Telhas (35) no InfoERP Suporte (19)
UPDATE ptk_ticket t
INNER JOIN ptk_project p ON p.id = 19
SET t.project_id = 19
WHERE t.project_id = 35
  AND EXISTS (SELECT 1 FROM ptk_project WHERE id = 35);

-- Remover projeto duplicado Telhas, se existir
DELETE FROM ptk_project WHERE id = 35 AND company_id = 13;