CREATE TABLE IF NOT EXISTS integrations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  provider VARCHAR(50) NOT NULL,
  name VARCHAR(120) NOT NULL,
  base_url VARCHAR(255) NULL,
  api_token TEXT NULL,
  api_secret TEXT NULL,
  username VARCHAR(120) NULL,
  password_enc TEXT NULL,
  extra_config JSON NULL,
  active TINYINT(1) NOT NULL DEFAULT 0,
  last_status VARCHAR(50) NULL,
  last_message TEXT NULL,
  last_tested_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_integrations_provider (provider)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS integration_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  provider VARCHAR(50) NOT NULL,
  action VARCHAR(80) NOT NULL,
  direction VARCHAR(20) NOT NULL DEFAULT 'outbound',
  related_type VARCHAR(50) NULL,
  related_id INT NULL,
  http_code INT NULL,
  success TINYINT(1) NOT NULL DEFAULT 0,
  message TEXT NULL,
  request_payload JSON NULL,
  response_payload JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_integration_logs_provider (provider),
  INDEX idx_integration_logs_related (related_type, related_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE clients ADD COLUMN sgp_client_id VARCHAR(80) NULL;
ALTER TABLE clients ADD COLUMN perfex_customer_id VARCHAR(80) NULL;
ALTER TABLE payments ADD COLUMN sgp_invoice_id VARCHAR(80) NULL;
ALTER TABLE payments ADD COLUMN perfex_invoice_id VARCHAR(80) NULL;

INSERT INTO integrations (provider, name, base_url, active, extra_config)
VALUES
('sgp', 'ERP SGP', '', 0, JSON_OBJECT('auth_type','bearer','customer_search_path','/api/cliente','invoice_path','/api/fatura')),
('perfex', 'Perfex CRM', '', 0, JSON_OBJECT('auth_header','X-API-KEY','customers_path','/api/v1/customers','invoices_path','/api/v1/invoices'))
ON DUPLICATE KEY UPDATE name=VALUES(name);