-- Schéma de base de données pour l'application de gestion d'e-mails de secrétariat
-- Préfixe: email_ (obligatoire pour toutes les tables)
-- Compatible MySQL 5.7+


-- Table des utilisateurs
CREATE TABLE `email_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `guilmaut_uid` varchar(100) NOT NULL UNIQUE,
  `email` varchar(255) NOT NULL,
  `nom` varchar(100) NOT NULL,
  `prenom` varchar(100) NOT NULL,
  `actif` tinyint(1) NOT NULL DEFAULT 1,
  `dernier_login` datetime NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email_users_guilmaut_uid` (`guilmaut_uid`),
  KEY `idx_email_users_email` (`email`),
  KEY `idx_email_users_actif` (`actif`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des rôles
CREATE TABLE `email_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL UNIQUE,
  `libelle` varchar(100) NOT NULL,
  `permissions_json` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email_roles_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table de liaison utilisateurs-rôles
CREATE TABLE `email_user_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email_user_roles_unique` (`user_id`, `role_id`),
  KEY `idx_email_user_roles_user` (`user_id`),
  KEY `idx_email_user_roles_role` (`role_id`),
  CONSTRAINT `fk_email_user_roles_user` FOREIGN KEY (`user_id`) REFERENCES `email_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_email_user_roles_role` FOREIGN KEY (`role_id`) REFERENCES `email_roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des clients/dossiers
CREATE TABLE `email_clients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code_dossier` varchar(50) NOT NULL UNIQUE,
  `nom` varchar(100) NOT NULL,
  `prenom` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  `tel` varchar(20) NULL,
  `owner_user_id` int(11) NOT NULL,
  `statut_global` enum('nouveau', 'en_cours', 'termine', 'suspendu') NOT NULL DEFAULT 'nouveau',
  `meta_json` text NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email_clients_code` (`code_dossier`),
  KEY `idx_email_clients_owner` (`owner_user_id`),
  KEY `idx_email_clients_statut` (`statut_global`),
  KEY `idx_email_clients_email` (`email`),
  CONSTRAINT `fk_email_clients_owner` FOREIGN KEY (`owner_user_id`) REFERENCES `email_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des formulaires
CREATE TABLE `email_forms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(100) NOT NULL,
  `form_key` varchar(50) NOT NULL UNIQUE,
  `form_order` int(11) NOT NULL UNIQUE,
  `version` int(11) NOT NULL DEFAULT 1,
  `statut` enum('brouillon', 'publie', 'archive') NOT NULL DEFAULT 'brouillon',
  `schema_json` longtext NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email_forms_key` (`form_key`),
  UNIQUE KEY `idx_email_forms_order` (`form_order`),
  KEY `idx_email_forms_statut` (`statut`),
  KEY `idx_email_forms_created_by` (`created_by`),
  CONSTRAINT `fk_email_forms_created_by` FOREIGN KEY (`created_by`) REFERENCES `email_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des champs de formulaires
CREATE TABLE `email_form_fields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_id` int(11) NOT NULL,
  `field_key` varchar(50) NOT NULL,
  `type` enum('text', 'email', 'tel', 'number', 'date', 'textarea', 'select', 'radio', 'checkbox', 'file') NOT NULL,
  `label` varchar(200) NOT NULL,
  `required` tinyint(1) NOT NULL DEFAULT 0,
  `validation_regex` varchar(500) NULL,
  `position` int(11) NOT NULL DEFAULT 0,
  `meta_json` text NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email_form_fields_unique` (`form_id`, `field_key`),
  KEY `idx_email_form_fields_form` (`form_id`),
  KEY `idx_email_form_fields_position` (`position`),
  CONSTRAINT `fk_email_form_fields_form` FOREIGN KEY (`form_id`) REFERENCES `email_forms` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des réponses aux formulaires
CREATE TABLE `email_form_responses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `form_id` int(11) NOT NULL,
  `version` int(11) NOT NULL DEFAULT 1,
  `submitted_by` int(11) NOT NULL,
  `submitted_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('brouillon', 'soumis', 'valide', 'rejete') NOT NULL DEFAULT 'brouillon',
  `mail_sent` tinyint(1) NOT NULL DEFAULT 0,
  `meta_json` text NULL,
  PRIMARY KEY (`id`),
  KEY `idx_email_form_responses_client` (`client_id`),
  KEY `idx_email_form_responses_form` (`form_id`),
  KEY `idx_email_form_responses_submitted_by` (`submitted_by`),
  KEY `idx_email_form_responses_status` (`status`),
  KEY `idx_email_form_responses_mail_sent` (`mail_sent`),
  CONSTRAINT `fk_email_form_responses_client` FOREIGN KEY (`client_id`) REFERENCES `email_clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_email_form_responses_form` FOREIGN KEY (`form_id`) REFERENCES `email_forms` (`id`),
  CONSTRAINT `fk_email_form_responses_submitted_by` FOREIGN KEY (`submitted_by`) REFERENCES `email_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des valeurs des réponses
CREATE TABLE `email_response_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `response_id` int(11) NOT NULL,
  `field_key` varchar(50) NOT NULL,
  `value_text` text NULL,
  `value_number` decimal(15,2) NULL,
  `value_date` date NULL,
  `value_json` text NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email_response_values_unique` (`response_id`, `field_key`),
  KEY `idx_email_response_values_response` (`response_id`),
  KEY `idx_email_response_values_field` (`field_key`),
  CONSTRAINT `fk_email_response_values_response` FOREIGN KEY (`response_id`) REFERENCES `email_form_responses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des templates d'e-mail
CREATE TABLE `email_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `form_order` int(11) NOT NULL UNIQUE,
  `subject` varchar(500) NOT NULL,
  `html` longtext NOT NULL,
  `assets_json` text NULL,
  `version` int(11) NOT NULL DEFAULT 1,
  `status` enum('brouillon', 'publie', 'archive') NOT NULL DEFAULT 'brouillon',
  `created_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email_templates_form_order` (`form_order`),
  KEY `idx_email_templates_status` (`status`),
  KEY `idx_email_templates_created_by` (`created_by`),
  CONSTRAINT `fk_email_templates_created_by` FOREIGN KEY (`created_by`) REFERENCES `email_users` (`id`),
  CONSTRAINT `fk_email_templates_form_order` FOREIGN KEY (`form_order`) REFERENCES `email_forms` (`form_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table de la file d'attente des mails
CREATE TABLE `email_mail_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `to_email` varchar(255) NOT NULL,
  `cc_email` varchar(500) NULL,
  `bcc_email` varchar(500) NULL,
  `subject` varchar(500) NOT NULL,
  `html` longtext NOT NULL,
  `attachments_json` text NULL,
  `status` enum('queued', 'sending', 'sent', 'failed', 'cancelled') NOT NULL DEFAULT 'queued',
  `try_count` int(11) NOT NULL DEFAULT 0,
  `last_error` text NULL,
  `scheduled_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sent_at` datetime NULL,
  `template_id` int(11) NULL,
  `response_id` int(11) NULL,
  `priority` tinyint(4) NOT NULL DEFAULT 5,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_email_mail_queue_client` (`client_id`),
  KEY `idx_email_mail_queue_status` (`status`),
  KEY `idx_email_mail_queue_scheduled` (`scheduled_at`),
  KEY `idx_email_mail_queue_template` (`template_id`),
  KEY `idx_email_mail_queue_response` (`response_id`),
  KEY `idx_email_mail_queue_priority` (`priority`),
  CONSTRAINT `fk_email_mail_queue_client` FOREIGN KEY (`client_id`) REFERENCES `email_clients` (`id`),
  CONSTRAINT `fk_email_mail_queue_template` FOREIGN KEY (`template_id`) REFERENCES `email_templates` (`id`),
  CONSTRAINT `fk_email_mail_queue_response` FOREIGN KEY (`response_id`) REFERENCES `email_form_responses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des étapes par client
CREATE TABLE `email_client_stage` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `form_order` int(11) NOT NULL,
  `status` enum('non_demarre', 'en_cours', 'complete', 'mail_envoye', 'echec_envoi') NOT NULL DEFAULT 'non_demarre',
  `mail_status` enum('non_requis', 'en_attente', 'envoye', 'echec') NOT NULL DEFAULT 'non_requis',
  `completed_at` datetime NULL,
  `mail_sent_at` datetime NULL,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email_client_stage_unique` (`client_id`, `form_order`),
  KEY `idx_email_client_stage_client` (`client_id`),
  KEY `idx_email_client_stage_form_order` (`form_order`),
  KEY `idx_email_client_stage_status` (`status`),
  KEY `idx_email_client_stage_mail_status` (`mail_status`),
  CONSTRAINT `fk_email_client_stage_client` FOREIGN KEY (`client_id`) REFERENCES `email_clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_email_client_stage_form_order` FOREIGN KEY (`form_order`) REFERENCES `email_forms` (`form_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des logs d'audit
CREATE TABLE `email_audit_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL,
  `action` varchar(100) NOT NULL,
  `entity` varchar(100) NOT NULL,
  `entity_id` int(11) NULL,
  `payload_json` text NULL,
  `ip` varchar(45) NULL,
  `user_agent` varchar(500) NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_email_audit_log_user` (`user_id`),
  KEY `idx_email_audit_log_action` (`action`),
  KEY `idx_email_audit_log_entity` (`entity`, `entity_id`),
  KEY `idx_email_audit_log_created` (`created_at`),
  CONSTRAINT `fk_email_audit_log_user` FOREIGN KEY (`user_id`) REFERENCES `email_users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Données de base
INSERT INTO `email_roles` (`code`, `libelle`, `permissions_json`) VALUES
('admin', 'Administrateur', '["all"]'),
('manager', 'Manager', '["read_all", "edit_clients", "send_mail", "reports"]'),
('secretaire', 'Secrétaire', '["read_own", "edit_own_clients", "fill_forms", "send_mail"]'),
('readonly', 'Lecture seule', '["read_own"]');
