USE comptaai_maroc;

CREATE TABLE IF NOT EXISTS email_templates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  template_code VARCHAR(120) NOT NULL UNIQUE,
  name VARCHAR(180) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  body LONGTEXT NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY email_templates_active_index (is_active)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS email_queue (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  recipient_email VARCHAR(180) NOT NULL,
  recipient_name VARCHAR(180) NULL,
  subject VARCHAR(255) NOT NULL,
  body LONGTEXT NOT NULL,
  template_code VARCHAR(120) NULL,
  variables_json JSON NULL,
  status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
  retry_count TINYINT UNSIGNED NOT NULL DEFAULT 0,
  error_message TEXT NULL,
  queued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  KEY email_queue_status_index (status, queued_at),
  KEY email_queue_template_index (template_code)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS email_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email_queue_id BIGINT UNSIGNED NULL,
  recipient_email VARCHAR(180) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  status ENUM('sent','failed','skipped') NOT NULL,
  message TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY email_logs_queue_index (email_queue_id),
  KEY email_logs_status_index (status),
  KEY email_logs_created_index (created_at),
  CONSTRAINT email_logs_queue_fk FOREIGN KEY (email_queue_id) REFERENCES email_queue(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT INTO email_templates (template_code, name, subject, body, is_active) VALUES
('welcome_user', 'Bienvenue utilisateur', 'Bienvenue sur {{platform_name}}', 'Bonjour {{user_name}},\n\nVotre compte {{platform_name}} est pret.\nEmail: {{user_email}}\n\nCordialement,\n{{sender_name}}', 1),
('password_reset', 'Reinitialisation mot de passe', 'Reinitialisation de votre mot de passe', 'Bonjour {{user_name}},\n\nUtilisez ce lien pour reinitialiser votre mot de passe:\n{{reset_link}}\n\nSi vous n etes pas a l origine de cette demande, ignorez cet email.', 1),
('new_document_uploaded', 'Nouveau document depose', 'Nouveau document depose pour {{client_name}}', 'Bonjour,\n\nUn nouveau document a ete depose: {{document_name}}.\nClient: {{client_name}}\nStatut: {{status}}\n\nMerci de le traiter.', 1),
('document_needs_review', 'Document a reviser', 'Document a reviser - {{client_name}}', 'Bonjour,\n\nLe document {{document_name}} necessite une revision.\nMotif: {{reason}}\n\nMerci.', 1),
('ocr_completed', 'OCR termine', 'OCR termine pour {{document_name}}', 'Bonjour,\n\nLe traitement OCR est termine pour {{document_name}}.\nVous pouvez verifier les champs extraits.', 1),
('accounting_entry_validated', 'Ecriture comptable validee', 'Ecriture comptable validee', 'Bonjour,\n\nL ecriture {{entry_label}} a ete validee pour {{client_name}}.', 1),
('tva_report_ready', 'Rapport TVA pret', 'Rapport TVA pret - {{client_name}}', 'Bonjour,\n\nLe rapport TVA {{period}} est pret pour validation.', 1),
('subscription_expiring', 'Abonnement bientot expire', 'Votre abonnement expire bientot', 'Bonjour,\n\nVotre abonnement {{plan_name}} expire le {{end_date}}.\nMerci de prevoir le renouvellement.', 1),
('saas_invoice_unpaid', 'Facture SaaS impayee', 'Facture SaaS impayee {{invoice_number}}', 'Bonjour,\n\nLa facture {{invoice_number}} d un montant de {{amount_ttc}} MAD est en attente de paiement.\nEcheance: {{due_date}}.', 1),
('task_assigned', 'Tache assignee', 'Nouvelle tache: {{task_title}}', 'Bonjour {{user_name}},\n\nUne tache vous a ete assignee: {{task_title}}\nPriorite: {{priority}}\nEcheance: {{due_date}}\n\n{{task_description}}', 1)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  subject = VALUES(subject),
  body = VALUES(body);

INSERT INTO app_settings (setting_key, setting_value, is_secret) VALUES
('email.test_mode', '1', 0)
ON DUPLICATE KEY UPDATE setting_value = setting_value;

INSERT INTO cron_jobs (job_key, name, description, status, schedule_label, next_run_placeholder) VALUES
('send_emails', 'Envoyer les emails en file', 'Traite les emails pending si SMTP est configure et test mode desactive.', 'active', 'Toutes les 5 minutes', '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);