USE comptaai_maroc;

CREATE TABLE IF NOT EXISTS cron_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  job_key VARCHAR(120) NOT NULL UNIQUE,
  name VARCHAR(180) NOT NULL,
  description TEXT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  schedule_label VARCHAR(120) NULL,
  last_run_at DATETIME NULL,
  next_run_placeholder VARCHAR(120) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY cron_jobs_status_index (status),
  KEY cron_jobs_last_run_index (last_run_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cron_job_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cron_job_id BIGINT UNSIGNED NULL,
  job_key VARCHAR(120) NOT NULL,
  status ENUM('success','failed','skipped') NOT NULL DEFAULT 'success',
  message TEXT NULL,
  started_at DATETIME NOT NULL,
  finished_at DATETIME NULL,
  duration_ms INT UNSIGNED NOT NULL DEFAULT 0,
  triggered_by ENUM('manual','cron','system') NOT NULL DEFAULT 'cron',
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY cron_job_logs_job_index (cron_job_id),
  KEY cron_job_logs_key_index (job_key),
  KEY cron_job_logs_started_index (started_at),
  CONSTRAINT cron_job_logs_job_fk FOREIGN KEY (cron_job_id) REFERENCES cron_jobs(id) ON DELETE SET NULL,
  CONSTRAINT cron_job_logs_user_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT INTO cron_jobs (job_key, name, description, status, schedule_label, next_run_placeholder) VALUES
('check_subscriptions', 'Verifier les abonnements expires', 'Passe les abonnements termines en statut expire.', 'active', 'Quotidien', 'Configure plus tard via cPanel'),
('overdue_invoices', 'Marquer les factures SaaS en retard', 'Passe les factures impayees arrivees a echeance en retard.', 'active', 'Quotidien', 'Configure plus tard via cPanel'),
('document_reminders', 'Rappels documents en attente', 'Cree des rappels pour les documents non traites.', 'active', 'Quotidien', 'Configure plus tard via cPanel'),
('tva_reminders', 'Rappels validation TVA', 'Cree des rappels pour les rapports TVA brouillon.', 'active', 'Hebdomadaire', 'Configure plus tard via cPanel'),
('cleanup_temp', 'Nettoyage fichiers temporaires', 'Supprime les fichiers temporaires anciens.', 'active', 'Quotidien', 'Configure plus tard via cPanel'),
('monthly_usage', 'Statistiques usage mensuel', 'Actualise les statistiques mensuelles des abonnements.', 'active', 'Mensuel', 'Configure plus tard via cPanel'),
('database_backup_placeholder', 'Backup base de donnees placeholder', 'Journalise le backup automatique futur sans creer de fichier.', 'inactive', 'Mensuel', 'Configure plus tard via cPanel')
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  description = VALUES(description),
  schedule_label = VALUES(schedule_label),
  next_run_placeholder = VALUES(next_run_placeholder);

INSERT INTO system_settings (setting_key, setting_value)
VALUES ('cron.secret_token', SHA2(UUID(), 256))
ON DUPLICATE KEY UPDATE setting_value = setting_value;