ALTER TABLE `user` CHANGE `telephone` `telephone` VARCHAR(255) NULL DEFAULT NULL, CHANGE `telephone_fixe` `telephone_fixe` VARCHAR(255) NULL DEFAULT NULL;
ALTER TABLE `prospection` ADD `responsable_id` INT NULL AFTER `formule_id`, ADD `demande_id` INT NULL AFTER `responsable_id`;
ALTER TABLE `message` ADD `status` VARCHAR(20) NULL AFTER `sent_at`;
CREATE TABLE `messagerie_conversation_hidden_users` ( `conversation_id` INT NOT NULL, `user_id` INT NOT NULL, PRIMARY KEY (`conversation_id`, `user_id`), INDEX `IDX_MCHU_CONVERSATION` (`conversation_id`), INDEX `IDX_MCHU_USER` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE contrepartie ADD COLUMN type_valeur VARCHAR(20) DEFAULT NULL, ADD COLUMN unite VARCHAR(50) DEFAULT NULL;
CREATE TABLE realisation ( id INT AUTO_INCREMENT NOT NULL, convention_id INT DEFAULT NULL, formule_id INT DEFAULT NULL, type_contrepartie_id INT DEFAULT NULL, realise_par_id INT DEFAULT NULL, idtoken VARCHAR(255) NOT NULL, statut VARCHAR(50) DEFAULT NULL, valeur_reelle VARCHAR(255) DEFAULT NULL, unite VARCHAR(50) DEFAULT NULL, commentaire LONGTEXT DEFAULT NULL, date_realisation DATETIME DEFAULT NULL, preuve VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id) ) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci`;
ALTER TABLE `realisation` ADD `contrepartie_id` INT NULL AFTER `type_contrepartie_id`;
ALTER TABLE `contrepartie` ADD `statut` VARCHAR(255) NULL AFTER `unite`;
ALTER TABLE `contrepartie` ADD `convention_id` INT NULL AFTER `type_id`;
ALTER TABLE `contrepartie` ADD `proposition_id` INT NULL AFTER `convention_id`;
CREATE TABLE `user_permission` (`user_id` INT NOT NULL, `permission_id` INT NOT NULL, PRIMARY KEY (`user_id`, `permission_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `parametre` CHANGE `valeur` `valeur` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;
CREATE TABLE `projet_presentation_formule` (`projet_presentation_id` INT NOT NULL, `formule_id` INT NOT NULL, PRIMARY KEY (`projet_presentation_id`, `formule_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `convention` ADD `niveau_id` INT DEFAULT NULL AFTER `formule_id`;
CREATE TABLE `user_connection` (
  `id` INT AUTO_INCREMENT NOT NULL,
  `user_id` INT NOT NULL,
  `ip` VARCHAR(45) NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `user_connection` ADD CONSTRAINT `FK_USER_CONNECTION_USER` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE;
CREATE TABLE `parametre_projet` (
  `id` INT AUTO_INCREMENT NOT NULL,
  `projet_id` INT NOT NULL,
  `idtoken` VARCHAR(255) NOT NULL,
  `cle` VARCHAR(100) NOT NULL,
  `valeur` LONGTEXT DEFAULT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `parametre_projet` ADD CONSTRAINT `FK_PARAMETRE_PROJET_PROJET` FOREIGN KEY (`projet_id`) REFERENCES `projet` (`id`) ON DELETE CASCADE;
-- Formules flexibles et options de contreparties
ALTER TABLE type_contrepartie
    ADD COLUMN valeur DOUBLE PRECISION DEFAULT 0 NOT NULL,
    ADD COLUMN nombre_activation INT DEFAULT 0 NOT NULL,
    ADD COLUMN nombre_max INT DEFAULT 0 NOT NULL;

ALTER TABLE formule
    ADD COLUMN flex TINYINT(1) DEFAULT 0 NOT NULL,
    ADD COLUMN valeur_contrepartie DOUBLE PRECISION DEFAULT 0 NOT NULL,
    ADD COLUMN valeur_utilisee DOUBLE PRECISION DEFAULT 0 NOT NULL,;

CREATE TABLE formule_contrepartie_option (
    id INT AUTO_INCREMENT NOT NULL,
    idtoken VARCHAR(255) NOT NULL,
    formule_id INT DEFAULT NULL,
    type_contrepartie_id INT DEFAULT NULL,
    obligatoire TINYINT(1) DEFAULT 0 NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX IDX_FCO_FORMULE (formule_id),
    INDEX IDX_FCO_TYPE (type_contrepartie_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE formule_contrepartie_selection (
    id INT AUTO_INCREMENT NOT NULL,
    idtoken VARCHAR(255) NOT NULL,
    formule_id INT DEFAULT NULL,
    proposition_id INT DEFAULT NULL,
    type_contrepartie_id INT DEFAULT NULL,
    quantite INT DEFAULT 0 NOT NULL,
    statut VARCHAR(50) NOT NULL,
    created_by_id INT DEFAULT NULL,
    created_at DATETIME NOT NULL,
    INDEX IDX_FCS_FORMULE (formule_id),
    INDEX IDX_FCS_PROPOSITION (proposition_id),
    INDEX IDX_FCS_TYPE (type_contrepartie_id),
    INDEX IDX_FCS_CREATED_BY (created_by_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

ALTER TABLE formule_contrepartie_option
    ADD CONSTRAINT FK_FCO_FORMULE FOREIGN KEY (formule_id) REFERENCES formule (id) ON DELETE CASCADE,
    ADD CONSTRAINT FK_FCO_TYPE FOREIGN KEY (type_contrepartie_id) REFERENCES type_contrepartie (id) ON DELETE SET NULL;

ALTER TABLE formule_contrepartie_selection
    ADD CONSTRAINT FK_FCS_FORMULE FOREIGN KEY (formule_id) REFERENCES formule (id) ON DELETE SET NULL,
    ADD CONSTRAINT FK_FCS_PROPOSITION FOREIGN KEY (proposition_id) REFERENCES proposition (id) ON DELETE SET NULL,
    ADD CONSTRAINT FK_FCS_TYPE FOREIGN KEY (type_contrepartie_id) REFERENCES type_contrepartie (id) ON DELETE SET NULL,
    ADD CONSTRAINT FK_FCS_CREATED_BY FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL;

ALTER TABLE facture_projet
    ADD COLUMN send_at DATETIME DEFAULT NULL;

ALTER TABLE recu_fiscal
    ADD COLUMN send_at DATETIME DEFAULT NULL;

CREATE TABLE activite (
    id INT AUTO_INCREMENT NOT NULL,
    idtoken VARCHAR(255) NOT NULL,
    type VARCHAR(20) NOT NULL,
    date DATE NOT NULL,
    heure TIME NOT NULL,
    lieu VARCHAR(255) DEFAULT NULL,
    compte_rendu LONGTEXT DEFAULT NULL,
    deleted TINYINT(1) NOT NULL,
    created_at DATETIME NOT NULL,
    created_by_id INT DEFAULT NULL,
    projet_id INT DEFAULT NULL,
    convention_id INT DEFAULT NULL,
    prospection_id INT DEFAULT NULL,
    INDEX IDX_ACTIVITE_CREATED_BY (created_by_id),
    INDEX IDX_ACTIVITE_PROJET (projet_id),
    INDEX IDX_ACTIVITE_CONVENTION (convention_id),
    INDEX IDX_ACTIVITE_PROSPECTION (prospection_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE activite_participant (
    activite_id INT NOT NULL,
    user_id INT NOT NULL,
    INDEX IDX_ACTIVITE_PARTICIPANT_ACTIVITE (activite_id),
    INDEX IDX_ACTIVITE_PARTICIPANT_USER (user_id),
    PRIMARY KEY(activite_id, user_id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

ALTER TABLE activite
    ADD CONSTRAINT FK_ACTIVITE_CREATED_BY FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL,
    ADD CONSTRAINT FK_ACTIVITE_PROJET FOREIGN KEY (projet_id) REFERENCES projet (id) ON DELETE SET NULL,
    ADD CONSTRAINT FK_ACTIVITE_CONVENTION FOREIGN KEY (convention_id) REFERENCES convention (id) ON DELETE SET NULL,
    ADD CONSTRAINT FK_ACTIVITE_PROSPECTION FOREIGN KEY (prospection_id) REFERENCES prospection (id) ON DELETE SET NULL;

ALTER TABLE activite_participant
    ADD CONSTRAINT FK_ACTIVITE_PARTICIPANT_ACTIVITE FOREIGN KEY (activite_id) REFERENCES activite (id) ON DELETE CASCADE,
    ADD CONSTRAINT FK_ACTIVITE_PARTICIPANT_USER FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE;

************************

ALTER TABLE mecene
    ADD COLUMN numero_tva VARCHAR(255) DEFAULT NULL,
    ADD COLUMN numero_rcs VARCHAR(255) DEFAULT NULL,
    ADD COLUMN registre_commerce VARCHAR(255) DEFAULT NULL;

ALTER TABLE proposition
    ADD COLUMN public_token VARCHAR(255) DEFAULT NULL,
    ADD COLUMN public_presentation_id INT DEFAULT NULL;

ALTER TABLE `prospection` CHANGE `created_by_id` `created_by_id` INT NULL;

-- Etape 3 prospection : message, signature, envoi programme et suivi
ALTER TABLE proposition
    ADD COLUMN message LONGTEXT DEFAULT NULL,
    ADD COLUMN user_signature_id INT DEFAULT NULL,
    ADD COLUMN send_mode VARCHAR(50) DEFAULT NULL,
    ADD COLUMN send_scheduled_at DATETIME DEFAULT NULL,
    ADD COLUMN send_scheduled_processed TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN sent_at DATETIME DEFAULT NULL,
    ADD COLUMN tracking_read_receipt TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN tracking_reminder TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN tracking_team_copy TINYINT(1) NOT NULL DEFAULT 0;

CREATE INDEX IDX_PROPOSITION_USER_SIGNATURE ON proposition (user_signature_id);

ALTER TABLE proposition
    ADD CONSTRAINT FK_PROPOSITION_USER_SIGNATURE
    FOREIGN KEY (user_signature_id) REFERENCES user (id);

-- Module sollicitation partenaires
CREATE TABLE sollicitation (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT DEFAULT NULL,
    projet_id INT DEFAULT NULL,
    created_by_id INT DEFAULT NULL,
    updated_by_id INT DEFAULT NULL,
    idtoken VARCHAR(255) NOT NULL,
    titre VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL,
    priorite VARCHAR(20) NOT NULL,
    statut VARCHAR(50) NOT NULL,
    instructions LONGTEXT DEFAULT NULL,
    deadline_at DATETIME DEFAULT NULL,
    send_at DATETIME DEFAULT NULL,
    last_reminder_at DATETIME DEFAULT NULL,
    auto_reminder_enabled TINYINT(1) NOT NULL DEFAULT 1,
    auto_reminder_days_before INT NOT NULL DEFAULT 3,
    notify_email TINYINT(1) NOT NULL DEFAULT 1,
    notify_in_app TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    deleted TINYINT(1) NOT NULL DEFAULT 0,
    INDEX IDX_SOLLICITATION_SOCIETE (societe_id),
    INDEX IDX_SOLLICITATION_PROJET (projet_id),
    INDEX IDX_SOLLICITATION_CREATED_BY (created_by_id),
    INDEX IDX_SOLLICITATION_UPDATED_BY (updated_by_id),
    INDEX IDX_SOLLICITATION_STATUT (statut),
    INDEX IDX_SOLLICITATION_DEADLINE (deadline_at),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE sollicitation_cible (
    id INT AUTO_INCREMENT NOT NULL,
    sollicitation_id INT DEFAULT NULL,
    mecene_id INT DEFAULT NULL,
    idtoken VARCHAR(255) NOT NULL,
    statut VARCHAR(50) NOT NULL,
    opened_at DATETIME DEFAULT NULL,
    started_at DATETIME DEFAULT NULL,
    submitted_at DATETIME DEFAULT NULL,
    validated_at DATETIME DEFAULT NULL,
    refused_at DATETIME DEFAULT NULL,
    last_reminder_at DATETIME DEFAULT NULL,
    reminder_count INT NOT NULL DEFAULT 0,
    internal_comment LONGTEXT DEFAULT NULL,
    created_at DATETIME NOT NULL,
    deleted TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE INDEX uniq_sollicitation_cible (sollicitation_id, mecene_id),
    INDEX IDX_SOLLICITATION_CIBLE_SOL (sollicitation_id),
    INDEX IDX_SOLLICITATION_CIBLE_MECENE (mecene_id),
    INDEX IDX_SOLLICITATION_CIBLE_STATUT (statut),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE sollicitation_piece_jointe (
    id INT AUTO_INCREMENT NOT NULL,
    sollicitation_id INT DEFAULT NULL,
    uploaded_by_id INT DEFAULT NULL,
    idtoken VARCHAR(255) NOT NULL,
    nom_original VARCHAR(255) NOT NULL,
    path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(150) DEFAULT NULL,
    size INT DEFAULT NULL,
    is_template TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    deleted TINYINT(1) NOT NULL DEFAULT 0,
    INDEX IDX_SOLLICITATION_PJ_SOL (sollicitation_id),
    INDEX IDX_SOLLICITATION_PJ_USER (uploaded_by_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE sollicitation_reponse (
    id INT AUTO_INCREMENT NOT NULL,
    sollicitation_id INT DEFAULT NULL,
    cible_id INT DEFAULT NULL,
    created_by_id INT DEFAULT NULL,
    reviewed_by_id INT DEFAULT NULL,
    idtoken VARCHAR(255) NOT NULL,
    statut VARCHAR(50) NOT NULL,
    commentaire_partenaire LONGTEXT DEFAULT NULL,
    commentaire_revision LONGTEXT DEFAULT NULL,
    submitted_at DATETIME DEFAULT NULL,
    reviewed_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    deleted TINYINT(1) NOT NULL DEFAULT 0,
    INDEX IDX_SOLLICITATION_REP_SOL (sollicitation_id),
    INDEX IDX_SOLLICITATION_REP_CIBLE (cible_id),
    INDEX IDX_SOLLICITATION_REP_CREATED_BY (created_by_id),
    INDEX IDX_SOLLICITATION_REP_REVIEWED_BY (reviewed_by_id),
    INDEX IDX_SOLLICITATION_REP_STATUT (statut),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE sollicitation_reponse_fichier (
    id INT AUTO_INCREMENT NOT NULL,
    reponse_id INT DEFAULT NULL,
    uploaded_by_id INT DEFAULT NULL,
    idtoken VARCHAR(255) NOT NULL,
    nom_original VARCHAR(255) NOT NULL,
    path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(150) DEFAULT NULL,
    size INT DEFAULT NULL,
    created_at DATETIME NOT NULL,
    deleted TINYINT(1) NOT NULL DEFAULT 0,
    INDEX IDX_SOLLICITATION_REP_FILE_REP (reponse_id),
    INDEX IDX_SOLLICITATION_REP_FILE_USER (uploaded_by_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE sollicitation_evenement (
    id INT AUTO_INCREMENT NOT NULL,
    sollicitation_id INT DEFAULT NULL,
    cible_id INT DEFAULT NULL,
    reponse_id INT DEFAULT NULL,
    created_by_id INT DEFAULT NULL,
    idtoken VARCHAR(255) NOT NULL,
    type VARCHAR(80) NOT NULL,
    message LONGTEXT NOT NULL,
    metadata LONGTEXT DEFAULT NULL,
    system_event TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    INDEX IDX_SOLLICITATION_EVT_SOL (sollicitation_id),
    INDEX IDX_SOLLICITATION_EVT_CIBLE (cible_id),
    INDEX IDX_SOLLICITATION_EVT_REP (reponse_id),
    INDEX IDX_SOLLICITATION_EVT_CREATED_BY (created_by_id),
    INDEX IDX_SOLLICITATION_EVT_TYPE (type),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

ALTER TABLE sollicitation
    ADD CONSTRAINT FK_SOLLICITATION_SOCIETE FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE SET NULL,
    ADD CONSTRAINT FK_SOLLICITATION_PROJET FOREIGN KEY (projet_id) REFERENCES projet (id) ON DELETE SET NULL,
    ADD CONSTRAINT FK_SOLLICITATION_CREATED_BY FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL,
    ADD CONSTRAINT FK_SOLLICITATION_UPDATED_BY FOREIGN KEY (updated_by_id) REFERENCES user (id) ON DELETE SET NULL;

ALTER TABLE sollicitation_cible
    ADD CONSTRAINT FK_SOLLICITATION_CIBLE_SOL FOREIGN KEY (sollicitation_id) REFERENCES sollicitation (id) ON DELETE CASCADE,
    ADD CONSTRAINT FK_SOLLICITATION_CIBLE_MECENE FOREIGN KEY (mecene_id) REFERENCES mecene (id) ON DELETE SET NULL;

ALTER TABLE sollicitation_piece_jointe
    ADD CONSTRAINT FK_SOLLICITATION_PJ_SOL FOREIGN KEY (sollicitation_id) REFERENCES sollicitation (id) ON DELETE CASCADE,
    ADD CONSTRAINT FK_SOLLICITATION_PJ_USER FOREIGN KEY (uploaded_by_id) REFERENCES user (id) ON DELETE SET NULL;

ALTER TABLE sollicitation_reponse
    ADD CONSTRAINT FK_SOLLICITATION_REP_SOL FOREIGN KEY (sollicitation_id) REFERENCES sollicitation (id) ON DELETE CASCADE,
    ADD CONSTRAINT FK_SOLLICITATION_REP_CIBLE FOREIGN KEY (cible_id) REFERENCES sollicitation_cible (id) ON DELETE SET NULL,
    ADD CONSTRAINT FK_SOLLICITATION_REP_CREATED_BY FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL,
    ADD CONSTRAINT FK_SOLLICITATION_REP_REVIEWED_BY FOREIGN KEY (reviewed_by_id) REFERENCES user (id) ON DELETE SET NULL;

ALTER TABLE sollicitation_reponse_fichier
    ADD CONSTRAINT FK_SOLLICITATION_REP_FILE_REP FOREIGN KEY (reponse_id) REFERENCES sollicitation_reponse (id) ON DELETE CASCADE,
    ADD CONSTRAINT FK_SOLLICITATION_REP_FILE_USER FOREIGN KEY (uploaded_by_id) REFERENCES user (id) ON DELETE SET NULL;

ALTER TABLE sollicitation_evenement
    ADD CONSTRAINT FK_SOLLICITATION_EVT_SOL FOREIGN KEY (sollicitation_id) REFERENCES sollicitation (id) ON DELETE CASCADE,
    ADD CONSTRAINT FK_SOLLICITATION_EVT_CIBLE FOREIGN KEY (cible_id) REFERENCES sollicitation_cible (id) ON DELETE SET NULL,
    ADD CONSTRAINT FK_SOLLICITATION_EVT_REP FOREIGN KEY (reponse_id) REFERENCES sollicitation_reponse (id) ON DELETE SET NULL,
    ADD CONSTRAINT FK_SOLLICITATION_EVT_CREATED_BY FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS messenger_messages (
  id BIGINT AUTO_INCREMENT NOT NULL,
  body LONGTEXT NOT NULL,
  headers LONGTEXT NOT NULL,
  queue_name VARCHAR(190) NOT NULL,
  created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
  available_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
  delivered_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
  INDEX IDX_75EA56E0FB7336F0 (queue_name),
  INDEX IDX_75EA56E0E3BD61CE (available_at),
  INDEX IDX_75EA56E016BA31DB (delivered_at),
  PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE=InnoDB;

ALTER TABLE error_report
  ADD COLUMN handled_at DATETIME DEFAULT NULL;

ALTER TABLE convention
  ADD COLUMN allow_partner_flex_edit_after_signature TINYINT(1) NOT NULL DEFAULT 0;

########################################################

ALTER TABLE apport
  ADD COLUMN date_previsionnelle DATE DEFAULT NULL,
  ADD COLUMN montant_previsionnel DECIMAL(10,2) DEFAULT NULL,
  ADD COLUMN statut VARCHAR(30) NOT NULL DEFAULT 'EN_ATTENTE',
  ADD COLUMN date_consignation DATE DEFAULT NULL,
  ADD COLUMN montant_consigne DECIMAL(10,2) DEFAULT NULL,
  ADD COLUMN updated_at DATETIME DEFAULT NULL;

CREATE INDEX IDX_APPORT_CONVENTION_STATUT ON apport (convention_id, statut);

ALTER TABLE mecene
  ADD COLUMN nom_commercial VARCHAR(255) DEFAULT NULL;

-- =========================
-- Equipes organisateur + visibilité projet
-- =========================
CREATE TABLE app_team (
  id INT AUTO_INCREMENT NOT NULL,
  societe_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX IDX_APP_TEAM_SOCIETE (societe_id),
  INDEX IDX_APP_TEAM_SYSTEM (societe_id, is_system),
  UNIQUE INDEX UNIQ_APP_TEAM_SOCIETE_SLUG (societe_id, slug),
  PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE app_team_member (
  id INT AUTO_INCREMENT NOT NULL,
  team_id INT NOT NULL,
  user_id INT NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX IDX_APP_TEAM_MEMBER_TEAM (team_id),
  INDEX IDX_APP_TEAM_MEMBER_USER (user_id),
  UNIQUE INDEX UNIQ_APP_TEAM_MEMBER_TEAM_USER (team_id, user_id),
  PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE app_team
  ADD CONSTRAINT FK_APP_TEAM_SOCIETE
  FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE;

ALTER TABLE app_team_member
  ADD CONSTRAINT FK_APP_TEAM_MEMBER_TEAM
  FOREIGN KEY (team_id) REFERENCES app_team (id) ON DELETE CASCADE,
  ADD CONSTRAINT FK_APP_TEAM_MEMBER_USER
  FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE;

ALTER TABLE projet
  ADD COLUMN visibility_type VARCHAR(20) NOT NULL DEFAULT 'ORGANIZATION',
  ADD COLUMN visible_team_id INT DEFAULT NULL,
  ADD COLUMN visible_user_id INT DEFAULT NULL;

CREATE INDEX IDX_PROJET_VISIBILITY_TYPE ON projet (visibility_type);
CREATE INDEX IDX_PROJET_VISIBLE_TEAM ON projet (visible_team_id);
CREATE INDEX IDX_PROJET_VISIBLE_USER ON projet (visible_user_id);

ALTER TABLE projet
  ADD CONSTRAINT FK_PROJET_VISIBLE_TEAM
  FOREIGN KEY (visible_team_id) REFERENCES app_team (id) ON DELETE SET NULL,
  ADD CONSTRAINT FK_PROJET_VISIBLE_USER
  FOREIGN KEY (visible_user_id) REFERENCES user (id) ON DELETE SET NULL;

UPDATE projet
SET visibility_type = 'ORGANIZATION'
WHERE visibility_type IS NULL OR visibility_type = '';

-- =========================
-- Entités Equipe et EquipeProjet
-- =========================
CREATE TABLE IF NOT EXISTS equipe (
  id INT AUTO_INCREMENT NOT NULL,
  societe_id INT NOT NULL,
  idtoken VARCHAR(255) NOT NULL,
  nom VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  deleted TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME DEFAULT NULL,
  UNIQUE KEY UNIQ_EQUIPE_TOKEN (idtoken),
  UNIQUE KEY UNIQ_EQUIPE_SOCIETE_SLUG (societe_id, slug),
  KEY IDX_EQUIPE_SOCIETE (societe_id),
  KEY IDX_EQUIPE_SYSTEM (societe_id, is_system),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS equipe_user (
  equipe_id INT NOT NULL,
  user_id INT NOT NULL,
  PRIMARY KEY (equipe_id, user_id),
  KEY IDX_EQUIPE_USER_EQUIPE (equipe_id),
  KEY IDX_EQUIPE_USER_USER (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS equipe_projet (
  id INT AUTO_INCREMENT NOT NULL,
  equipe_id INT NOT NULL,
  projet_id INT NOT NULL,
  idtoken VARCHAR(255) NOT NULL,
  can_edit TINYINT(1) NOT NULL DEFAULT 0,
  deleted TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  UNIQUE KEY UNIQ_EQUIPE_PROJET_TOKEN (idtoken),
  UNIQUE KEY UNIQ_EQUIPE_PROJET (equipe_id, projet_id),
  KEY IDX_EQUIPE_PROJET_EQUIPE (equipe_id),
  KEY IDX_EQUIPE_PROJET_PROJET (projet_id),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE equipe
  ADD CONSTRAINT FK_EQUIPE_SOCIETE FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE;

ALTER TABLE equipe_user
  ADD CONSTRAINT FK_EQUIPE_USER_EQUIPE FOREIGN KEY (equipe_id) REFERENCES equipe (id) ON DELETE CASCADE,
  ADD CONSTRAINT FK_EQUIPE_USER_USER FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE;

ALTER TABLE equipe_projet
  ADD CONSTRAINT FK_EQUIPE_PROJET_EQUIPE FOREIGN KEY (equipe_id) REFERENCES equipe (id) ON DELETE CASCADE,
  ADD CONSTRAINT FK_EQUIPE_PROJET_PROJET FOREIGN KEY (projet_id) REFERENCES projet (id) ON DELETE CASCADE;

-- Rapport PDF asynchrone projet
CREATE TABLE IF NOT EXISTS project_report_job (
  id INT AUTO_INCREMENT NOT NULL,
  project_id INT NOT NULL,
  created_by_id INT NOT NULL,
  idtoken VARCHAR(255) NOT NULL,
  type VARCHAR(50) NOT NULL,
  status VARCHAR(20) NOT NULL,
  progress SMALLINT NOT NULL DEFAULT 0,
  current_step VARCHAR(255) DEFAULT NULL,
  steps JSON NOT NULL,
  config JSON NOT NULL,
  message VARCHAR(255) DEFAULT NULL,
  pdf_path VARCHAR(255) DEFAULT NULL,
  pdf_size BIGINT DEFAULT NULL,
  error_message LONGTEXT DEFAULT NULL,
  created_at DATETIME NOT NULL,
  started_at DATETIME DEFAULT NULL,
  finished_at DATETIME DEFAULT NULL,
  expires_at DATETIME DEFAULT NULL,
  UNIQUE KEY UNIQ_PROJECT_REPORT_JOB_TOKEN (idtoken),
  KEY IDX_PROJECT_REPORT_JOB_PROJECT (project_id),
  KEY IDX_PROJECT_REPORT_JOB_CREATED_BY (created_by_id),
  KEY IDX_PROJECT_REPORT_JOB_PROJECT_STATUS (project_id, status),
  KEY IDX_PROJECT_REPORT_JOB_CREATED_AT (created_at),
  KEY IDX_PROJECT_REPORT_JOB_EXPIRES_AT (expires_at),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE project_report_job
  ADD CONSTRAINT FK_PROJECT_REPORT_JOB_PROJECT FOREIGN KEY (project_id) REFERENCES projet (id) ON DELETE CASCADE,
  ADD CONSTRAINT FK_PROJECT_REPORT_JOB_CREATED_BY FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE CASCADE;

-- Pack sélectionné lors de la création d'une société
ALTER TABLE societe
  ADD COLUMN pack_id INT DEFAULT NULL,
  ADD KEY IDX_SOCIETE_PACK (pack_id);

ALTER TABLE societe
  ADD CONSTRAINT FK_SOCIETE_PACK FOREIGN KEY (pack_id) REFERENCES pack (id) ON DELETE SET NULL;

-- =========================
-- Centre de ressources (DashboardBundle)
-- =========================
CREATE TABLE IF NOT EXISTS resource_content (
  id INT AUTO_INCREMENT NOT NULL,
  code VARCHAR(120) NOT NULL,
  type VARCHAR(30) NOT NULL,
  category VARCHAR(60) DEFAULT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT DEFAULT NULL,
  payload JSON DEFAULT NULL,
  tags JSON DEFAULT NULL,
  is_new TINYINT(1) NOT NULL DEFAULT 0,
  is_important TINYINT(1) NOT NULL DEFAULT 0,
  published_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME DEFAULT NULL,
  UNIQUE KEY UNIQ_RESOURCE_CONTENT_CODE (code),
  KEY IDX_RESOURCE_CONTENT_TYPE (type),
  KEY IDX_RESOURCE_CONTENT_CATEGORY (category),
  KEY IDX_RESOURCE_CONTENT_PUBLISHED_AT (published_at),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS resource_favorite (
  id INT AUTO_INCREMENT NOT NULL,
  user_id INT NOT NULL,
  resource_code VARCHAR(120) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY UNIQ_RESOURCE_FAVORITE_USER_CODE (user_id, resource_code),
  KEY IDX_RESOURCE_FAVORITE_USER (user_id),
  KEY IDX_RESOURCE_FAVORITE_CODE (resource_code),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS resource_history (
  id INT AUTO_INCREMENT NOT NULL,
  user_id INT NOT NULL,
  societe_id INT DEFAULT NULL,
  resource_code VARCHAR(120) NOT NULL,
  action_type VARCHAR(40) NOT NULL,
  created_at DATETIME NOT NULL,
  KEY IDX_RESOURCE_HISTORY_USER (user_id),
  KEY IDX_RESOURCE_HISTORY_SOCIETE (societe_id),
  KEY IDX_RESOURCE_HISTORY_CODE (resource_code),
  KEY IDX_RESOURCE_HISTORY_CREATED_AT (created_at),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE resource_favorite
  ADD CONSTRAINT FK_RESOURCE_FAVORITE_USER FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE;

ALTER TABLE resource_history
  ADD CONSTRAINT FK_RESOURCE_HISTORY_USER FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE,
  ADD CONSTRAINT FK_RESOURCE_HISTORY_SOCIETE FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE SET NULL;

INSERT IGNORE INTO resource_content (code, type, category, title, description, tags, is_new, is_important, published_at, created_at)
VALUES
('modele-convention-mecenat', 'modele', 'conventions', 'Convention de mécénat standard', 'Modèle complet avec clauses de base et champs personnalisables.', JSON_ARRAY('Mécénat','Conformité 2024'), 1, 1, NOW(), NOW()),
('modele-email-relance-j3', 'modele', 'emails', 'Relance signature J+3', 'Email court prêt à envoyer pour accélérer la signature.', JSON_ARRAY('Relance','Signature'), 0, 0, NOW(), NOW()),
('fiche-rdv-partenaire', 'fiche', 'prospection', 'Préparer un rendez-vous partenaire', 'Checklist concise avant, pendant, après rendez-vous.', JSON_ARRAY('Prospection','RDV'), 0, 1, NOW(), NOW()),
('actu-regles-fiscales', 'actualite', 'juridique', 'Nouvelles règles fiscales mécénat 2026', 'Points clés à vérifier sur l’éligibilité et les justificatifs.', JSON_ARRAY('Mécénat','Fiscalité'), 1, 1, NOW(), NOW()),
('kit-prospection-30j', 'kit', 'prospection', 'Kit prospection 30 jours', 'Scripts d’appels, séquences email et messages LinkedIn.', JSON_ARRAY('Prospection','Scripts'), 0, 1, NOW(), NOW()),
('parcours-signer-plus-vite', 'parcours', 'juridique', 'Signer plus vite', 'Séquence complète pour réduire les délais de signature.', JSON_ARRAY('Signature','Relance'), 0, 1, NOW(), NOW());

-- =========================
-- Admin ressources (CRUD + stats)
-- =========================
CREATE TABLE IF NOT EXISTS resource_item (
  id INT AUTO_INCREMENT NOT NULL,
  organizer_owner_id INT DEFAULT NULL,
  created_by_id INT DEFAULT NULL,
  idtoken VARCHAR(255) NOT NULL,
  type VARCHAR(20) NOT NULL,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  excerpt LONGTEXT DEFAULT NULL,
  content_html LONGTEXT DEFAULT NULL,
  status VARCHAR(20) NOT NULL,
  visibility VARCHAR(20) NOT NULL,
  document_type VARCHAR(30) DEFAULT NULL,
  legal_framework VARCHAR(30) DEFAULT NULL,
  access_level VARCHAR(30) NOT NULL DEFAULT 'all_users',
  tags JSON DEFAULT NULL,
  sectors JSON DEFAULT NULL,
  keywords JSON DEFAULT NULL,
  level VARCHAR(30) DEFAULT NULL,
  duration VARCHAR(30) DEFAULT NULL,
  cover_image_path VARCHAR(255) DEFAULT NULL,
  source_name VARCHAR(255) DEFAULT NULL,
  source_url VARCHAR(255) DEFAULT NULL,
  is_recommended TINYINT(1) NOT NULL DEFAULT 0,
  requires_legal_validation TINYINT(1) NOT NULL DEFAULT 0,
  version VARCHAR(20) NOT NULL DEFAULT '1.0',
  compliance_note LONGTEXT DEFAULT NULL,
  last_legal_update_at DATETIME DEFAULT NULL,
  published_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME DEFAULT NULL,
  views_count INT NOT NULL DEFAULT 0,
  favorites_count INT NOT NULL DEFAULT 0,
  downloads_count INT NOT NULL DEFAULT 0,
  usages_count INT NOT NULL DEFAULT 0,
  deleted TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY UNIQ_RESOURCE_ITEM_TOKEN (idtoken),
  UNIQUE KEY UNIQ_RESOURCE_ITEM_SLUG (slug),
  KEY IDX_RESOURCE_ITEM_TYPE (type),
  KEY IDX_RESOURCE_ITEM_ACCESS_LEVEL (access_level),
  KEY IDX_RESOURCE_ITEM_STATUS (status),
  KEY IDX_RESOURCE_ITEM_VISIBILITY (visibility),
  KEY IDX_RESOURCE_ITEM_OWNER (organizer_owner_id),
  KEY IDX_RESOURCE_ITEM_CREATED_BY (created_by_id),
  KEY IDX_RESOURCE_ITEM_DELETED (deleted),
  KEY IDX_RESOURCE_ITEM_PUBLISHED_AT (published_at),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS resource_attachment (
  id INT AUTO_INCREMENT NOT NULL,
  resource_item_id INT NOT NULL,
  storage_path VARCHAR(255) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  size INT NOT NULL,
  kind VARCHAR(50) NOT NULL,
  label VARCHAR(255) DEFAULT NULL,
  display_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  KEY IDX_RESOURCE_ATTACHMENT_ITEM (resource_item_id),
  KEY IDX_RESOURCE_ATTACHMENT_KIND (kind),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS resource_item_favorite (
  id INT AUTO_INCREMENT NOT NULL,
  user_id INT NOT NULL,
  organizer_id INT DEFAULT NULL,
  resource_item_id INT NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY UNIQ_RESOURCE_ITEM_FAVORITE (user_id, resource_item_id),
  KEY IDX_RESOURCE_ITEM_FAVORITE_USER (user_id),
  KEY IDX_RESOURCE_ITEM_FAVORITE_ORGA (organizer_id),
  KEY IDX_RESOURCE_ITEM_FAVORITE_ITEM (resource_item_id),
  KEY IDX_RESOURCE_ITEM_FAVORITE_CREATED_AT (created_at),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS resource_item_view (
  id INT AUTO_INCREMENT NOT NULL,
  user_id INT DEFAULT NULL,
  organizer_id INT DEFAULT NULL,
  resource_item_id INT NOT NULL,
  ip VARCHAR(45) DEFAULT NULL,
  viewed_at DATETIME NOT NULL,
  KEY IDX_RESOURCE_ITEM_VIEW_USER (user_id),
  KEY IDX_RESOURCE_ITEM_VIEW_ORGA (organizer_id),
  KEY IDX_RESOURCE_ITEM_VIEW_ITEM (resource_item_id),
  KEY IDX_RESOURCE_ITEM_VIEW_VIEWED_AT (viewed_at),
  KEY IDX_RESOURCE_ITEM_VIEW_ITEM_DATE (resource_item_id, viewed_at),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS resource_item_usage_log (
  id INT AUTO_INCREMENT NOT NULL,
  user_id INT DEFAULT NULL,
  organizer_id INT DEFAULT NULL,
  resource_item_id INT NOT NULL,
  attachment_id INT DEFAULT NULL,
  action_type VARCHAR(40) NOT NULL,
  meta JSON DEFAULT NULL,
  created_at DATETIME NOT NULL,
  KEY IDX_RESOURCE_ITEM_USAGE_USER (user_id),
  KEY IDX_RESOURCE_ITEM_USAGE_ORGA (organizer_id),
  KEY IDX_RESOURCE_ITEM_USAGE_ITEM (resource_item_id),
  KEY IDX_RESOURCE_ITEM_USAGE_ATTACHMENT (attachment_id),
  KEY IDX_RESOURCE_ITEM_USAGE_ACTION (action_type),
  KEY IDX_RESOURCE_ITEM_USAGE_CREATED_AT (created_at),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE resource_item
  ADD CONSTRAINT FK_RESOURCE_ITEM_OWNER FOREIGN KEY (organizer_owner_id) REFERENCES societe (id) ON DELETE SET NULL,
  ADD CONSTRAINT FK_RESOURCE_ITEM_CREATED_BY FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL;

ALTER TABLE resource_attachment
  ADD CONSTRAINT FK_RESOURCE_ATTACHMENT_ITEM FOREIGN KEY (resource_item_id) REFERENCES resource_item (id) ON DELETE CASCADE;

ALTER TABLE resource_item_favorite
  ADD CONSTRAINT FK_RESOURCE_ITEM_FAVORITE_USER FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE,
  ADD CONSTRAINT FK_RESOURCE_ITEM_FAVORITE_ORGA FOREIGN KEY (organizer_id) REFERENCES societe (id) ON DELETE SET NULL,
  ADD CONSTRAINT FK_RESOURCE_ITEM_FAVORITE_ITEM FOREIGN KEY (resource_item_id) REFERENCES resource_item (id) ON DELETE CASCADE;

ALTER TABLE resource_item_view
  ADD CONSTRAINT FK_RESOURCE_ITEM_VIEW_USER FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE SET NULL,
  ADD CONSTRAINT FK_RESOURCE_ITEM_VIEW_ORGA FOREIGN KEY (organizer_id) REFERENCES societe (id) ON DELETE SET NULL,
  ADD CONSTRAINT FK_RESOURCE_ITEM_VIEW_ITEM FOREIGN KEY (resource_item_id) REFERENCES resource_item (id) ON DELETE CASCADE;

ALTER TABLE resource_item_usage_log
  ADD CONSTRAINT FK_RESOURCE_ITEM_USAGE_USER FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE SET NULL,
  ADD CONSTRAINT FK_RESOURCE_ITEM_USAGE_ORGA FOREIGN KEY (organizer_id) REFERENCES societe (id) ON DELETE SET NULL,
  ADD CONSTRAINT FK_RESOURCE_ITEM_USAGE_ITEM FOREIGN KEY (resource_item_id) REFERENCES resource_item (id) ON DELETE CASCADE,
  ADD CONSTRAINT FK_RESOURCE_ITEM_USAGE_ATTACHMENT FOREIGN KEY (attachment_id) REFERENCES resource_attachment (id) ON DELETE SET NULL;

-- Billing / trial management on societe
ALTER TABLE societe
  ADD COLUMN is_trial TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN trial_ends_at DATETIME DEFAULT NULL,
  ADD COLUMN stripe_customer_id VARCHAR(255) DEFAULT NULL,
  ADD COLUMN stripe_subscription_id VARCHAR(255) DEFAULT NULL,
  ADD COLUMN subscription_status VARCHAR(50) DEFAULT NULL,
  ADD COLUMN current_period_ends_at DATETIME DEFAULT NULL,
  ADD COLUMN has_valid_subscription TINYINT(1) NOT NULL DEFAULT 0,
  ADD KEY IDX_SOCIETE_STRIPE_CUSTOMER (stripe_customer_id),
  ADD KEY IDX_SOCIETE_STRIPE_SUBSCRIPTION (stripe_subscription_id);

-- Billing offers from pack
ALTER TABLE pack
  ADD COLUMN prix_annuel DOUBLE PRECISION DEFAULT NULL,
  ADD COLUMN stripe_price_monthly_id VARCHAR(255) DEFAULT NULL,
  ADD COLUMN stripe_price_yearly_id VARCHAR(255) DEFAULT NULL,
  ADD KEY IDX_PACK_STRIPE_MONTHLY (stripe_price_monthly_id),
  ADD KEY IDX_PACK_STRIPE_YEARLY (stripe_price_yearly_id);

ALTER TABLE `societe` ADD `created_at` DATETIME NULL AFTER `has_valid_subscription`;

-- Stripe webhook / abonnement (sécurisation colonnes société si base incomplète)
--ALTER TABLE societe
--  ADD COLUMN IF NOT EXISTS stripe_customer_id VARCHAR(255) DEFAULT NULL,
--  ADD COLUMN IF NOT EXISTS stripe_subscription_id VARCHAR(255) DEFAULT NULL,
--  ADD COLUMN IF NOT EXISTS subscription_status VARCHAR(50) DEFAULT NULL,
--  ADD COLUMN IF NOT EXISTS has_valid_subscription TINYINT(1) NOT NULL DEFAULT 0,
--  ADD COLUMN IF NOT EXISTS current_period_ends_at DATETIME DEFAULT NULL;

--ALTER TABLE societe
--  ADD INDEX IF NOT EXISTS IDX_SOCIETE_STRIPE_CUSTOMER (stripe_customer_id),
--  ADD INDEX IF NOT EXISTS IDX_SOCIETE_STRIPE_SUBSCRIPTION (stripe_subscription_id);

CREATE TABLE IF NOT EXISTS forme_juridique (
  id INT AUTO_INCREMENT NOT NULL,
  idtoken VARCHAR(255) NOT NULL,
  code VARCHAR(100) NOT NULL,
  nom VARCHAR(255) NOT NULL,
  categorie VARCHAR(255) NOT NULL,
  deleted TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY UNIQ_FORME_JURIDIQUE_IDTOKEN (idtoken),
  UNIQUE KEY UNIQ_FORME_JURIDIQUE_CODE (code),
  KEY IDX_FORME_JURIDIQUE_DELETED_CATEGORY (deleted, categorie),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO forme_juridique (idtoken, code, nom, categorie, deleted) VALUES
  ('fj_association1901', 'association1901', 'Association loi 1901', 'Associations', 0),
  ('fj_sarl', 'SARL', 'SARL (Société à Responsabilité Limitée)', 'Sociétés commerciales', 0),
  ('fj_eurl', 'EURL', 'EURL (Entreprise Unipersonnelle à Responsabilité Limitée)', 'Sociétés commerciales', 0),
  ('fj_sas', 'SAS', 'SAS (Société par Actions Simplifiée)', 'Sociétés commerciales', 0),
  ('fj_sasu', 'SASU', 'SASU (Société par Actions Simplifiée Unipersonnelle)', 'Sociétés commerciales', 0),
  ('fj_sa', 'SA', 'SA (Société Anonyme)', 'Sociétés commerciales', 0),
  ('fj_snc', 'SNC', 'SNC (Société en Nom Collectif)', 'Sociétés commerciales', 0),
  ('fj_sci', 'SCI', 'SCI (Société Civile Immobilière)', 'Sociétés civiles', 0),
  ('fj_scp', 'SCP', 'SCP (Société Civile Professionnelle)', 'Sociétés civiles', 0),
  ('fj_scm', 'SCM', 'SCM (Société Civile de Moyens)', 'Sociétés civiles', 0),
  ('fj_sca', 'SCA', 'SCA (Société en Commandite par Actions)', 'Sociétés spécifiques', 0),
  ('fj_scs', 'SCS', 'SCS (Société en Commandite Simple)', 'Sociétés spécifiques', 0),
  ('fj_sel', 'SEL', 'SEL (Société d''Exercice Libéral)', 'Sociétés spécifiques', 0),
  ('fj_micro', 'Micro-entreprise', 'Micro-entreprise (auto-entrepreneur)', 'Formes alternatives', 0),
  ('fj_scop', 'SCOP', 'SCOP (Société Coopérative et Participative)', 'Formes alternatives', 0),
  ('fj_scic', 'SCIC', 'SCIC (Société Coopérative d''Intérêt Collectif)', 'Formes alternatives', 0),
  ('fj_geie', 'GEIE', 'GEIE (Groupement Européen d''Intérêt Économique)', 'Groupements', 0),
  ('fj_gie', 'GIE', 'GIE (Groupement d''Intérêt Économique)', 'Groupements', 0),
  ('fj_earl', 'EARL', 'EARL (Exploitation Agricole à Responsabilité Limitée)', 'Autres structures', 0);

ALTER TABLE societe
  ADD COLUMN forme_juridique_id INT DEFAULT NULL;

UPDATE societe s
LEFT JOIN forme_juridique fj ON fj.code = s.forme_juridique
SET s.forme_juridique_id = fj.id
WHERE s.forme_juridique IS NOT NULL;

ALTER TABLE societe
  ADD CONSTRAINT FK_SOCIETE_FORME_JURIDIQUE FOREIGN KEY (forme_juridique_id) REFERENCES forme_juridique (id) ON DELETE SET NULL;

ALTER TABLE societe
  DROP COLUMN forme_juridique;

ALTER TABLE mecene
  ADD COLUMN forme_juridique_id INT DEFAULT NULL;

UPDATE mecene m
LEFT JOIN forme_juridique fj ON fj.code = m.forme_juridique
SET m.forme_juridique_id = fj.id
WHERE m.forme_juridique IS NOT NULL;

ALTER TABLE mecene
  ADD CONSTRAINT FK_MECENE_FORME_JURIDIQUE FOREIGN KEY (forme_juridique_id) REFERENCES forme_juridique (id) ON DELETE SET NULL;

ALTER TABLE mecene
  DROP COLUMN forme_juridique;

ALTER TABLE mecene
  ADD COLUMN naf VARCHAR(255) DEFAULT NULL;

ALTER TABLE proposition
  ADD COLUMN decline_reason VARCHAR(255) DEFAULT NULL,
  ADD COLUMN decline_comment LONGTEXT DEFAULT NULL;

ALTER TABLE niveau_mecenat
  ADD COLUMN couleur VARCHAR(255) DEFAULT NULL;

ALTER TABLE projet
  ADD COLUMN visuel_principal VARCHAR(255) DEFAULT NULL;

ALTER TABLE formule
    ADD COLUMN compliance_status VARCHAR(50) DEFAULT NULL,
    ADD COLUMN compliance_summary LONGTEXT DEFAULT NULL,
    ADD COLUMN compliance_analyzed_at DATETIME DEFAULT NULL;

CREATE TABLE app_setting (
    id INT AUTO_INCREMENT NOT NULL,
    setting_key VARCHAR(100) NOT NULL,
    setting_value LONGTEXT DEFAULT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE INDEX uniq_app_setting_key (setting_key),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE analyse_formule (
    id INT AUTO_INCREMENT NOT NULL,
    formule_id INT NOT NULL,
    final_status VARCHAR(50) NOT NULL,
    automatic_status VARCHAR(50) NOT NULL,
    formula_type VARCHAR(50) NOT NULL,
    formula_value DOUBLE PRECISION NOT NULL,
    total_counterparties_value DOUBLE PRECISION NOT NULL,
    counterparties_ratio DOUBLE PRECISION NOT NULL,
    automatic_issues JSON NOT NULL,
    ai_status VARCHAR(50) DEFAULT NULL,
    ai_summary LONGTEXT DEFAULT NULL,
    ai_risk_level VARCHAR(50) DEFAULT NULL,
    ai_issues JSON NOT NULL,
    ai_suggestions JSON NOT NULL,
    disclaimer LONGTEXT NOT NULL,
    raw_ai_response JSON DEFAULT NULL,
    error LONGTEXT DEFAULT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    INDEX IDX_ANALYSE_FORMULE_FORMULE (formule_id),
    PRIMARY KEY(id),
    CONSTRAINT FK_ANALYSE_FORMULE_FORMULE FOREIGN KEY (formule_id) REFERENCES formule (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

ALTER TABLE convention
    ADD COLUMN compliance_status VARCHAR(50) DEFAULT NULL,
    ADD COLUMN compliance_summary LONGTEXT DEFAULT NULL,
    ADD COLUMN compliance_analyzed_at DATETIME DEFAULT NULL;
    
CREATE TABLE analyse_convention (
    id INT AUTO_INCREMENT NOT NULL,
    convention_id INT NOT NULL,
    final_status VARCHAR(50) NOT NULL,
    ai_status VARCHAR(50) DEFAULT NULL,
    ai_summary LONGTEXT DEFAULT NULL,
    ai_risk_level VARCHAR(50) DEFAULT NULL,
    ai_issues JSON NOT NULL,
    ai_suggestions JSON NOT NULL,
    disclaimer LONGTEXT NOT NULL,
    extracted_text_preview LONGTEXT DEFAULT NULL,
    source_type VARCHAR(50) DEFAULT NULL,
    raw_ai_response JSON DEFAULT NULL,
    error LONGTEXT DEFAULT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    INDEX IDX_ANALYSE_CONVENTION_CONVENTION (convention_id),
    PRIMARY KEY(id),
    CONSTRAINT FK_ANALYSE_CONVENTION_CONVENTION FOREIGN KEY (convention_id) REFERENCES convention (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE project_onboarding (
    id INT AUTO_INCREMENT NOT NULL,
    project_id INT NOT NULL,
    is_enabled TINYINT(1) NOT NULL DEFAULT 0,
    welcome_message LONGTEXT DEFAULT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_PROJECT_ONBOARDING_PROJECT (project_id),
    PRIMARY KEY(id),
    CONSTRAINT FK_PROJECT_ONBOARDING_PROJECT FOREIGN KEY (project_id) REFERENCES projet (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE project_onboarding_document (
    id INT AUTO_INCREMENT NOT NULL,
    onboarding_id INT NOT NULL,
    label VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    original_file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(255) NOT NULL,
    file_size BIGINT NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    INDEX IDX_PROJECT_ONBOARDING_DOCUMENT_ONBOARDING (onboarding_id),
    PRIMARY KEY(id),
    CONSTRAINT FK_PROJECT_ONBOARDING_DOCUMENT_ONBOARDING FOREIGN KEY (onboarding_id) REFERENCES project_onboarding (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE project_onboarding_dispatch (
    id INT AUTO_INCREMENT NOT NULL,
    onboarding_id INT NOT NULL,
    partner_id INT NOT NULL,
    convention_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    sent_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    opened_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    downloaded_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX uniq_project_onboarding_dispatch_convention (convention_id),
    INDEX IDX_PROJECT_ONBOARDING_DISPATCH_ONBOARDING (onboarding_id),
    INDEX IDX_PROJECT_ONBOARDING_DISPATCH_PARTNER (partner_id),
    PRIMARY KEY(id),
    CONSTRAINT FK_PROJECT_ONBOARDING_DISPATCH_ONBOARDING FOREIGN KEY (onboarding_id) REFERENCES project_onboarding (id) ON DELETE CASCADE,
    CONSTRAINT FK_PROJECT_ONBOARDING_DISPATCH_PARTNER FOREIGN KEY (partner_id) REFERENCES mecene (id) ON DELETE CASCADE,
    CONSTRAINT FK_PROJECT_ONBOARDING_DISPATCH_CONVENTION FOREIGN KEY (convention_id) REFERENCES convention (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE error_report (
    id INT AUTO_INCREMENT NOT NULL,
    status_code INT NOT NULL,
    exception_class VARCHAR(255) NOT NULL,
    exception_message LONGTEXT NOT NULL,
    url LONGTEXT DEFAULT NULL,
    path VARCHAR(255) DEFAULT NULL,
    method VARCHAR(20) DEFAULT NULL,
    route_name VARCHAR(255) DEFAULT NULL,
    query_params JSON DEFAULT NULL,
    request_params JSON DEFAULT NULL,
    request_attributes JSON DEFAULT NULL,
    headers JSON DEFAULT NULL,
    body_preview LONGTEXT DEFAULT NULL,
    response_preview LONGTEXT DEFAULT NULL,
    referrer VARCHAR(1000) DEFAULT NULL,
    user_agent VARCHAR(1000) DEFAULT NULL,
    ip VARCHAR(64) DEFAULT NULL,
    user_id INT DEFAULT NULL,
    user_email VARCHAR(255) DEFAULT NULL,
    user_display_name VARCHAR(255) DEFAULT NULL,
    environment VARCHAR(50) NOT NULL,
    file VARCHAR(1000) DEFAULT NULL,
    line_number INT DEFAULT NULL,
    trace_preview LONGTEXT DEFAULT NULL,
    is_email_sent TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    INDEX idx_error_report_created_at (created_at),
    INDEX idx_error_report_status_code (status_code),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE email_template (
    id INT AUTO_INCREMENT NOT NULL,
    type VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    subject_template VARCHAR(255) NOT NULL,
    body_html_template LONGTEXT NOT NULL,
    body_text_template LONGTEXT DEFAULT NULL,
    is_enabled TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE INDEX uniq_email_template_type (type),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE email_message (
    id INT AUTO_INCREMENT NOT NULL,
    type VARCHAR(100) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    from_email VARCHAR(255) DEFAULT NULL,
    to_recipients JSON NOT NULL,
    cc_recipients JSON DEFAULT NULL,
    bcc_recipients JSON DEFAULT NULL,
    reply_to JSON DEFAULT NULL,
    body_html LONGTEXT DEFAULT NULL,
    body_text LONGTEXT DEFAULT NULL,
    variables JSON DEFAULT NULL,
    status VARCHAR(20) NOT NULL,
    sent_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL,
    error_message LONGTEXT DEFAULT NULL,
    provider_message_id VARCHAR(255) DEFAULT NULL,
    INDEX idx_email_message_type (type),
    INDEX idx_email_message_status (status),
    INDEX idx_email_message_sent_at (sent_at),
    INDEX idx_email_message_created_at (created_at),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE email_attachment (
    id INT AUTO_INCREMENT NOT NULL,
    email_message_id INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    mime_type VARCHAR(255) DEFAULT NULL,
    size INT DEFAULT NULL,
    storage_path VARCHAR(500) NOT NULL,
    checksum VARCHAR(128) DEFAULT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_email_attachment_email_message (email_message_id),
    PRIMARY KEY(id),
    CONSTRAINT fk_email_attachment_email_message FOREIGN KEY (email_message_id) REFERENCES email_message (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

ALTER TABLE error_report
  ADD COLUMN handled_at DATETIME DEFAULT NULL;