-- 006: Mercado Pago, webhooks, sincronização, notificações, backups
CREATE TABLE mercadopago_payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  payment_id BIGINT UNSIGNED NOT NULL,
  sale_id BIGINT UNSIGNED NULL,
  payment_uuid CHAR(36) NULL,
  idempotency_key CHAR(36) NOT NULL,
  external_reference VARCHAR(80) NULL,
  status VARCHAR(30) NOT NULL,
  status_detail VARCHAR(80) NULL,
  amount DECIMAL(14,2) NOT NULL,
  qr_code TEXT NULL,
  qr_code_base64 MEDIUMTEXT NULL,
  expires_at DATETIME NULL,
  environment ENUM('TEST','PRODUCTION') NOT NULL,
  raw_response JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_mp_uuid (uuid),
  UNIQUE KEY uq_mp_payment (company_id, payment_id),
  UNIQUE KEY uq_mp_idem (idempotency_key),
  KEY idx_mp_sale (sale_id),
  KEY idx_mp_status (status),
  CONSTRAINT fk_mp_company FOREIGN KEY (company_id) REFERENCES companies (id),
  CONSTRAINT fk_mp_sale FOREIGN KEY (sale_id) REFERENCES sales (id)
);
CREATE TABLE webhook_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  provider VARCHAR(30) NOT NULL,
  event_key VARCHAR(120) NOT NULL,
  event_type VARCHAR(60) NULL,
  resource_id VARCHAR(60) NULL,
  status ENUM('RECEIVED','PROCESSED','IGNORED','FAILED') NOT NULL DEFAULT 'RECEIVED',
  signature_valid TINYINT(1) NULL,
  payload JSON NULL,
  error VARCHAR(255) NULL,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at DATETIME NULL,
  UNIQUE KEY uq_webhook_key (provider, event_key),
  KEY idx_webhook_resource (provider, resource_id),
  KEY idx_webhook_status (status)
);
CREATE TABLE sync_queue (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  device_id VARCHAR(64) NULL,
  user_id BIGINT UNSIGNED NULL,
  entity VARCHAR(40) NOT NULL,
  entity_id CHAR(36) NULL,
  operation VARCHAR(20) NOT NULL,
  payload JSON NULL,
  status ENUM('PENDING','PROCESSING','SYNCED','FAILED','CONFLICT') NOT NULL DEFAULT 'PENDING',
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  last_error VARCHAR(500) NULL,
  result JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  synced_at DATETIME NULL,
  UNIQUE KEY uq_sync_queue_uuid (uuid),
  KEY idx_sync_status (company_id, status),
  KEY idx_sync_entity (entity, entity_id),
  CONSTRAINT fk_sq_company FOREIGN KEY (company_id) REFERENCES companies (id)
);
CREATE TABLE sync_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  device_id VARCHAR(64) NULL,
  user_id BIGINT UNSIGNED NULL,
  direction ENUM('PUSH','PULL') NOT NULL,
  items_count INT UNSIGNED NOT NULL DEFAULT 0,
  errors_count INT UNSIGNED NOT NULL DEFAULT 0,
  duration_ms INT UNSIGNED NULL,
  details JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_sync_logs_date (company_id, created_at),
  CONSTRAINT fk_sl_company FOREIGN KEY (company_id) REFERENCES companies (id)
);
CREATE TABLE notifications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  type VARCHAR(40) NOT NULL,
  title VARCHAR(160) NOT NULL,
  message VARCHAR(500) NULL,
  data JSON NULL,
  read_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_notif_user (company_id, user_id, read_at),
  CONSTRAINT fk_notif_company FOREIGN KEY (company_id) REFERENCES companies (id)
);
CREATE TABLE backups (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NULL,
  file_name VARCHAR(190) NOT NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  sha256 CHAR(64) NULL,
  type ENUM('AUTO','MANUAL') NOT NULL,
  status ENUM('RUNNING','OK','FAILED','VALIDATED','INVALID') NOT NULL,
  error VARCHAR(500) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NULL,
  KEY idx_backups_date (created_at)
);