-- Journal d’audit transversal multi-société - 2026-07-28.
-- Schéma additif : aucune donnée métier existante n’est modifiée.
CREATE TABLE IF NOT EXISTS platform_audit_log (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT DEFAULT NULL,
    actor_id INT DEFAULT NULL,
    actor_email VARCHAR(180) DEFAULT NULL,
    category VARCHAR(40) NOT NULL,
    action VARCHAR(80) NOT NULL,
    entity_type VARCHAR(180) DEFAULT NULL,
    entity_id VARCHAR(64) DEFAULT NULL,
    entity_token VARCHAR(80) DEFAULT NULL,
    entity_label VARCHAR(255) DEFAULT NULL,
    changes JSON NOT NULL,
    context JSON NOT NULL,
    route VARCHAR(180) DEFAULT NULL,
    http_method VARCHAR(10) DEFAULT NULL,
    ip_address VARCHAR(64) DEFAULT NULL,
    user_agent VARCHAR(500) DEFAULT NULL,
    occurred_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    INDEX IDX_PLATFORM_AUDIT_COMPANY_DATE (societe_id, occurred_at),
    INDEX IDX_PLATFORM_AUDIT_ACTOR_DATE (actor_id, occurred_at),
    INDEX IDX_PLATFORM_AUDIT_ACTION_DATE (action, occurred_at),
    INDEX IDX_PLATFORM_AUDIT_ENTITY (entity_type, entity_token),
    PRIMARY KEY (id),
    CONSTRAINT FK_PLATFORM_AUDIT_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE SET NULL,
    CONSTRAINT FK_PLATFORM_AUDIT_ACTOR
        FOREIGN KEY (actor_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

-- #################################################################################################
-- Calendrier central — lot 1 (migration additive 2026-08-05)
CREATE TABLE IF NOT EXISTS calendar_internal (id INT AUTO_INCREMENT NOT NULL, societe_id INT NOT NULL, owner_id INT DEFAULT NULL, created_by_id INT DEFAULT NULL, public_id VARCHAR(48) NOT NULL, type VARCHAR(20) NOT NULL, scope_key VARCHAR(80) NOT NULL, label VARCHAR(180) NOT NULL, color VARCHAR(20) NOT NULL, active TINYINT(1) NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, archived_at DATETIME DEFAULT NULL, UNIQUE INDEX UNIQ_E913D6BB5B48B91 (public_id), UNIQUE INDEX uniq_calendar_internal_scope (societe_id, scope_key), INDEX IDX_E913D6BFCF77503 (societe_id), INDEX IDX_E913D6B7E3C61F9 (owner_id), INDEX IDX_E913D6BB03A8386 (created_by_id), INDEX idx_calendar_internal_tenant (societe_id, type, active), PRIMARY KEY(id), CONSTRAINT FK_CALENDAR_INTERNAL_COMPANY FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE, CONSTRAINT FK_CALENDAR_INTERNAL_OWNER FOREIGN KEY (owner_id) REFERENCES user (id) ON DELETE SET NULL, CONSTRAINT FK_CALENDAR_INTERNAL_CREATED_BY FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS calendar_permission (id INT AUTO_INCREMENT NOT NULL, societe_id INT NOT NULL, calendar_id INT NOT NULL, granted_to_user_id INT NOT NULL, created_by_id INT DEFAULT NULL, public_id VARCHAR(48) NOT NULL, visibility_level VARCHAR(20) NOT NULL, booking_level VARCHAR(20) NOT NULL, can_view_tasks TINYINT(1) NOT NULL, can_edit_own_created_events TINYINT(1) NOT NULL, active_slot VARCHAR(10) DEFAULT NULL, starts_at DATETIME DEFAULT NULL, ends_at DATETIME DEFAULT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, revoked_at DATETIME DEFAULT NULL, UNIQUE INDEX UNIQ_760356AFB5B48B91 (public_id), UNIQUE INDEX uniq_calendar_permission_active (calendar_id, granted_to_user_id, active_slot), INDEX idx_calendar_permission_tenant_user (societe_id, granted_to_user_id), INDEX IDX_760356AF363BFDD (granted_to_user_id), INDEX IDX_760356AFB03A8386 (created_by_id), PRIMARY KEY(id), CONSTRAINT FK_CALENDAR_PERMISSION_COMPANY FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE, CONSTRAINT FK_CALENDAR_PERMISSION_CALENDAR FOREIGN KEY (calendar_id) REFERENCES calendar_internal (id) ON DELETE CASCADE, CONSTRAINT FK_CALENDAR_PERMISSION_USER FOREIGN KEY (granted_to_user_id) REFERENCES user (id) ON DELETE CASCADE, CONSTRAINT FK_CALENDAR_PERMISSION_CREATED_BY FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS calendar_booking_request (id INT AUTO_INCREMENT NOT NULL, societe_id INT NOT NULL, target_calendar_id INT NOT NULL, requested_for_id INT NOT NULL, requested_by_id INT NOT NULL, decision_by_id INT DEFAULT NULL, created_event_id INT DEFAULT NULL, public_id VARCHAR(48) NOT NULL, title VARCHAR(255) NOT NULL, description LONGTEXT DEFAULT NULL, starts_at DATETIME NOT NULL, ends_at DATETIME NOT NULL, time_zone VARCHAR(80) NOT NULL, location VARCHAR(255) DEFAULT NULL, visibility_level VARCHAR(20) NOT NULL, blocks_availability TINYINT(1) NOT NULL, status VARCHAR(20) NOT NULL, decision_comment LONGTEXT DEFAULT NULL, decision_at DATETIME DEFAULT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, UNIQUE INDEX UNIQ_CALENDAR_BOOKING_PUBLIC (public_id), UNIQUE INDEX UNIQ_CALENDAR_BOOKING_EVENT (created_event_id), INDEX idx_calendar_booking_tenant_status (societe_id, status, starts_at), INDEX IDX_CALENDAR_BOOKING_CALENDAR (target_calendar_id), INDEX IDX_CALENDAR_BOOKING_FOR (requested_for_id), INDEX IDX_CALENDAR_BOOKING_BY (requested_by_id), INDEX IDX_CALENDAR_BOOKING_DECISION (decision_by_id), PRIMARY KEY(id), CONSTRAINT FK_CALENDAR_BOOKING_COMPANY FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE, CONSTRAINT FK_CALENDAR_BOOKING_CALENDAR FOREIGN KEY (target_calendar_id) REFERENCES calendar_internal (id) ON DELETE CASCADE, CONSTRAINT FK_CALENDAR_BOOKING_FOR FOREIGN KEY (requested_for_id) REFERENCES user (id) ON DELETE CASCADE, CONSTRAINT FK_CALENDAR_BOOKING_BY FOREIGN KEY (requested_by_id) REFERENCES user (id) ON DELETE CASCADE, CONSTRAINT FK_CALENDAR_BOOKING_DECISION FOREIGN KEY (decision_by_id) REFERENCES user (id) ON DELETE SET NULL, CONSTRAINT FK_CALENDAR_BOOKING_EVENT FOREIGN KEY (created_event_id) REFERENCES calendar_event (id) ON DELETE SET NULL) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS calendar_view_preference (id INT AUTO_INCREMENT NOT NULL, societe_id INT NOT NULL, user_id INT NOT NULL, visible_layers JSON NOT NULL, visible_colleague_calendars JSON NOT NULL, default_view VARCHAR(20) NOT NULL, default_date_range VARCHAR(30) NOT NULL, show_completed_tasks TINYINT(1) NOT NULL, show_weekends TINYINT(1) NOT NULL, display_time_zone VARCHAR(80) NOT NULL, color_preferences JSON NOT NULL, updated_at DATETIME NOT NULL, UNIQUE INDEX uniq_calendar_view_preference (societe_id, user_id), INDEX IDX_AC8C5E8AA76ED395 (user_id), PRIMARY KEY(id), CONSTRAINT FK_CALENDAR_PREF_COMPANY FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE, CONSTRAINT FK_CALENDAR_PREF_USER FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS calendar_event_history (id INT AUTO_INCREMENT NOT NULL, societe_id INT NOT NULL, event_id INT NOT NULL, actor_id INT DEFAULT NULL, action VARCHAR(60) NOT NULL, changes JSON NOT NULL, created_at DATETIME NOT NULL, INDEX idx_calendar_event_history_tenant (societe_id, created_at), INDEX IDX_9DB9A15D71F7E88B (event_id), INDEX IDX_9DB9A15D10DAF24A (actor_id), PRIMARY KEY(id), CONSTRAINT FK_CALENDAR_HISTORY_COMPANY FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE, CONSTRAINT FK_CALENDAR_HISTORY_EVENT FOREIGN KEY (event_id) REFERENCES calendar_event (id) ON DELETE CASCADE, CONSTRAINT FK_CALENDAR_HISTORY_ACTOR FOREIGN KEY (actor_id) REFERENCES user (id) ON DELETE SET NULL) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

ALTER TABLE calendar_event ADD internal_calendar_id INT DEFAULT NULL, ADD organizer_id INT DEFAULT NULL, ADD client_id INT DEFAULT NULL, ADD prospect_id INT DEFAULT NULL, ADD project_id INT DEFAULT NULL, ADD event_type VARCHAR(30) NOT NULL DEFAULT 'appointment', ADD visibility_level VARCHAR(20) NOT NULL DEFAULT 'details', ADD blocks_availability TINYINT(1) NOT NULL DEFAULT 1, ADD origin VARCHAR(20) NOT NULL DEFAULT 'capsule', ADD source_task_type VARCHAR(120) DEFAULT NULL, ADD source_task_token VARCHAR(120) DEFAULT NULL, ADD recurrence_rule LONGTEXT DEFAULT NULL, ADD archived_at DATETIME DEFAULT NULL, ADD INDEX IDX_CALENDAR_EVENT_INTERNAL (internal_calendar_id), ADD INDEX IDX_CALENDAR_EVENT_ORGANIZER (organizer_id), ADD INDEX IDX_CALENDAR_EVENT_CLIENT (client_id), ADD INDEX IDX_CALENDAR_EVENT_PROSPECT (prospect_id), ADD INDEX IDX_CALENDAR_EVENT_PROJECT (project_id), ADD INDEX idx_calendar_event_availability (societe_id, internal_calendar_id, starts_at, ends_at, blocks_availability), ADD CONSTRAINT FK_CALENDAR_EVENT_INTERNAL FOREIGN KEY (internal_calendar_id) REFERENCES calendar_internal (id) ON DELETE SET NULL, ADD CONSTRAINT FK_CALENDAR_EVENT_ORGANIZER FOREIGN KEY (organizer_id) REFERENCES user (id) ON DELETE SET NULL, ADD CONSTRAINT FK_CALENDAR_EVENT_CLIENT FOREIGN KEY (client_id) REFERENCES client (id) ON DELETE SET NULL, ADD CONSTRAINT FK_CALENDAR_EVENT_PROSPECT FOREIGN KEY (prospect_id) REFERENCES prospection_prospect (id) ON DELETE SET NULL, ADD CONSTRAINT FK_CALENDAR_EVENT_PROJECT FOREIGN KEY (project_id) REFERENCES projet (id) ON DELETE SET NULL;
ALTER TABLE calendar_source ADD local_calendar_id INT DEFAULT NULL, ADD INDEX IDX_CALENDAR_SOURCE_LOCAL (local_calendar_id), ADD CONSTRAINT FK_CALENDAR_SOURCE_LOCAL FOREIGN KEY (local_calendar_id) REFERENCES calendar_internal (id) ON DELETE SET NULL;
ALTER TABLE calendar_webhook_channel ADD last_message_number INT DEFAULT NULL, ADD renewed_at DATETIME DEFAULT NULL;

-- Après application : php bin/console app:calendar:ensure-employee-calendars
-- Puis : php bin/console app:calendar:backfill-event-calendars

-- EmailBundle : modèles, personnalisations par société et journal des envois - 2026-08-01
-- Équivalent SQL de DoctrineMigrations\Version20260801120000.
-- Les ALTER additifs permettent aussi de compléter les anciennes tables EmailBundle.

CREATE TABLE IF NOT EXISTS email_template (
    id INT AUTO_INCREMENT NOT NULL,
    type VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description LONGTEXT DEFAULT NULL,
    category VARCHAR(100) DEFAULT NULL,
    subject_template VARCHAR(255) NOT NULL,
    body_html_template LONGTEXT NOT NULL,
    body_text_template LONGTEXT DEFAULT NULL,
    is_enabled TINYINT(1) DEFAULT 1 NOT NULL,
    available_variables JSON DEFAULT NULL,
    required_variables JSON DEFAULT NULL,
    is_customizable TINYINT(1) DEFAULT 1 NOT NULL,
    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;

ALTER TABLE email_template
    ADD COLUMN  description LONGTEXT DEFAULT NULL,
    ADD COLUMN  category VARCHAR(100) DEFAULT NULL,
    ADD COLUMN available_variables JSON DEFAULT NULL,
    ADD COLUMN required_variables JSON DEFAULT NULL,
    ADD COLUMN is_customizable TINYINT(1) DEFAULT 1 NOT NULL;

CREATE TABLE IF NOT EXISTS email_message (
    id INT AUTO_INCREMENT NOT NULL,
    type VARCHAR(100) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    from_email VARCHAR(255) DEFAULT NULL,
    from_name VARCHAR(255) DEFAULT NULL,
    template_key VARCHAR(100) DEFAULT NULL,
    template_source VARCHAR(20) 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,
    context_type VARCHAR(50) DEFAULT NULL,
    context_id INT DEFAULT NULL,
    company_id INT DEFAULT NULL,
    client_id INT 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),
    INDEX idx_email_message_context (context_type, context_id),
    PRIMARY KEY (id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

ALTER TABLE email_message
    ADD COLUMN from_name VARCHAR(255) DEFAULT NULL,
    ADD COLUMN template_key VARCHAR(100) DEFAULT NULL,
    ADD COLUMN template_source VARCHAR(20) DEFAULT NULL,
    ADD COLUMN context_type VARCHAR(50) DEFAULT NULL,
    ADD COLUMN context_id INT DEFAULT NULL,
    ADD COLUMN company_id INT DEFAULT NULL,
    ADD COLUMN client_id INT DEFAULT NULL;

CREATE TABLE IF NOT EXISTS 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_MESSAGE (email_message_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_EMAIL_ATTACHMENT_MESSAGE
        FOREIGN KEY (email_message_id) REFERENCES email_message (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS email_template_override (
    id INT AUTO_INCREMENT NOT NULL,
    company_id INT NOT NULL,
    template_id INT NOT NULL,
    subject_template VARCHAR(255) NOT NULL,
    body_html_template LONGTEXT NOT NULL,
    body_text_template LONGTEXT DEFAULT NULL,
    is_enabled TINYINT(1) DEFAULT 1 NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX IDX_EMAIL_TEMPLATE_OVERRIDE_COMPANY (company_id),
    INDEX IDX_EMAIL_TEMPLATE_OVERRIDE_TEMPLATE (template_id),
    UNIQUE INDEX uniq_email_template_override_company_template (company_id, template_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_EMAIL_TEMPLATE_OVERRIDE_COMPANY
        FOREIGN KEY (company_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_EMAIL_TEMPLATE_OVERRIDE_TEMPLATE
        FOREIGN KEY (template_id) REFERENCES email_template (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS email_template_history (
    id INT AUTO_INCREMENT NOT NULL,
    template_override_id INT NOT NULL,
    company_id INT NOT NULL,
    updated_by_id INT DEFAULT NULL,
    previous_values JSON DEFAULT NULL,
    new_values JSON NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX IDX_EMAIL_TEMPLATE_HISTORY_OVERRIDE (template_override_id),
    INDEX IDX_EMAIL_TEMPLATE_HISTORY_COMPANY (company_id),
    INDEX IDX_EMAIL_TEMPLATE_HISTORY_USER (updated_by_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_EMAIL_TEMPLATE_HISTORY_OVERRIDE
        FOREIGN KEY (template_override_id) REFERENCES email_template_override (id) ON DELETE CASCADE,
    CONSTRAINT FK_EMAIL_TEMPLATE_HISTORY_COMPANY
        FOREIGN KEY (company_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_EMAIL_TEMPLATE_HISTORY_USER
        FOREIGN KEY (updated_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

-- #################################################################################################

-- ProspectionBundle : rattachement des tâches générées au planning - 2026-07-29
-- Équivalent SQL de DoctrineMigrations\Version20260729120000.
-- Rejouable : seules les tâches ChatGPT qui ne possèdent pas encore de journée sont traitées.

INSERT INTO commercial_prospection_routine_day
    (
        routine_id,
        date,
        title,
        objective,
        summary,
        status,
        planned_tasks_count,
        completed_tasks_count,
        progress_percent,
        created_at,
        updated_at
    )
SELECT pending.routine_id,
       pending.task_date,
       CONCAT('Routine du ', DATE_FORMAT(pending.task_date, '%d/%m/%Y')),
       NULL,
       NULL,
       'planned',
       0,
       0,
       0,
       NOW(),
       NOW()
FROM (
    SELECT MIN(r.id) AS routine_id,
           DATE(t.scheduled_at) AS task_date
    FROM commercial_prospection_task t
    INNER JOIN commercial_prospection_routine r
        ON r.societe_id = t.societe_id
       AND r.status = 'active'
    WHERE t.source = 'chatgpt_file'
      AND t.archived_at IS NULL
      AND t.routine_day_id IS NULL
      AND t.scheduled_at IS NOT NULL
    GROUP BY t.societe_id, DATE(t.scheduled_at)
) pending
LEFT JOIN commercial_prospection_routine_day existing_day
    ON existing_day.routine_id = pending.routine_id
   AND existing_day.date = pending.task_date
WHERE existing_day.id IS NULL;

UPDATE commercial_prospection_task t
INNER JOIN commercial_prospection_routine r
    ON r.societe_id = t.societe_id
   AND r.status = 'active'
LEFT JOIN commercial_prospection_routine earlier_routine
    ON earlier_routine.societe_id = r.societe_id
   AND earlier_routine.status = 'active'
   AND earlier_routine.id < r.id
INNER JOIN commercial_prospection_routine_day d
    ON d.routine_id = r.id
   AND d.date = DATE(t.scheduled_at)
SET t.routine_id = r.id,
    t.routine_day_id = d.id,
    t.updated_at = NOW()
WHERE t.source = 'chatgpt_file'
  AND t.archived_at IS NULL
  AND t.routine_day_id IS NULL
  AND t.scheduled_at IS NOT NULL
  AND earlier_routine.id IS NULL;

UPDATE commercial_prospection_routine_day d
INNER JOIN (
    SELECT routine_day_id,
           SUM(status <> 'cancelled') AS planned_count,
           SUM(status = 'completed') AS completed_count
    FROM commercial_prospection_task
    WHERE archived_at IS NULL
      AND routine_day_id IS NOT NULL
    GROUP BY routine_day_id
) task_counts
    ON task_counts.routine_day_id = d.id
INNER JOIN commercial_prospection_task imported_task
    ON imported_task.routine_day_id = d.id
   AND imported_task.source = 'chatgpt_file'
   AND imported_task.archived_at IS NULL
SET d.planned_tasks_count = task_counts.planned_count,
    d.completed_tasks_count = task_counts.completed_count,
    d.progress_percent = CASE
        WHEN task_counts.planned_count = 0 THEN 0
        ELSE ROUND(task_counts.completed_count * 100 / task_counts.planned_count)
    END,
    d.updated_at = NOW();

-- #################################################################################################

-- #################################################################################################
-- GenerationBundle : pont manuel de génération externe (schéma strictement additif)
-- À valider humainement avant exécution en production.
-- #################################################################################################

CREATE TABLE IF NOT EXISTS generation_batch (
    id INT AUTO_INCREMENT NOT NULL,
    company_id INT NOT NULL,
    created_by_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    definition_code VARCHAR(100) NOT NULL,
    request_schema_version VARCHAR(80) NOT NULL,
    output_schema_version VARCHAR(80) NOT NULL,
    status VARCHAR(30) NOT NULL,
    title VARCHAR(200) NOT NULL,
    request_payload JSON NOT NULL,
    request_checksum VARCHAR(64) DEFAULT NULL,
    expected_filename VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    downloaded_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    gpt_open_triggered_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    gpt_open_triggered_by_id INT DEFAULT NULL,
    result_uploaded_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    imported_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    archived_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_GENERATION_BATCH_TOKEN (idtoken),
    INDEX IDX_GENERATION_BATCH_COMPANY_STATUS (company_id, status),
    INDEX IDX_GENERATION_BATCH_COMPANY_CREATED (company_id, created_at),
    INDEX IDX_GENERATION_BATCH_CREATOR (created_by_id),
    INDEX IDX_GENERATION_BATCH_GPT_OPEN_ACTOR (gpt_open_triggered_by_id),
    INDEX IDX_4ADAA8E1979B1AD6 (company_id),
    INDEX IDX_4ADAA8E1B03A8386 (created_by_id),
    PRIMARY KEY(id),
    CONSTRAINT FK_GENERATION_BATCH_COMPANY FOREIGN KEY (company_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_GENERATION_BATCH_CREATOR FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_GENERATION_BATCH_GPT_OPEN_ACTOR FOREIGN KEY (gpt_open_triggered_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS generation_result (
    id INT AUTO_INCREMENT NOT NULL,
    batch_id INT NOT NULL,
    company_id INT NOT NULL,
    uploaded_by_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    version INT NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    stored_filename VARCHAR(255) NOT NULL,
    relative_path VARCHAR(500) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    file_size INT NOT NULL,
    checksum VARCHAR(64) NOT NULL,
    status VARCHAR(30) NOT NULL,
    total_rows INT NOT NULL,
    valid_rows INT NOT NULL,
    warning_rows INT NOT NULL,
    invalid_rows INT NOT NULL,
    duplicate_rows INT NOT NULL,
    imported_rows INT NOT NULL,
    uploaded_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    validated_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    imported_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    error_message LONGTEXT DEFAULT NULL,
    UNIQUE INDEX UNIQ_GENERATION_RESULT_TOKEN (idtoken),
    UNIQUE INDEX UNIQ_GENERATION_RESULT_VERSION (batch_id, version),
    INDEX IDX_GENERATION_RESULT_COMPANY_STATUS (company_id, status),
    INDEX IDX_GENERATION_RESULT_UPLOADER (uploaded_by_id),
    INDEX IDX_456BD4CAF39EBE7A (batch_id),
    INDEX IDX_456BD4CA979B1AD6 (company_id),
    INDEX IDX_456BD4CAA2B28FE8 (uploaded_by_id),
    PRIMARY KEY(id),
    CONSTRAINT FK_GENERATION_RESULT_BATCH FOREIGN KEY (batch_id) REFERENCES generation_batch (id) ON DELETE CASCADE,
    CONSTRAINT FK_GENERATION_RESULT_COMPANY FOREIGN KEY (company_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_GENERATION_RESULT_UPLOADER FOREIGN KEY (uploaded_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS generation_result_row (
    id INT AUTO_INCREMENT NOT NULL,
    result_id INT NOT NULL,
    company_id INT NOT NULL,
    `row_number` INT NOT NULL,
    external_key VARCHAR(160) NOT NULL,
    output_schema_version VARCHAR(80) NOT NULL,
    status VARCHAR(30) NOT NULL,
    raw_payload JSON NOT NULL,
    normalized_payload JSON NOT NULL,
    errors JSON NOT NULL,
    warnings JSON NOT NULL,
    selected_for_import TINYINT(1) NOT NULL,
    target_entity_type VARCHAR(120) DEFAULT NULL,
    target_entity_id INT DEFAULT NULL,
    target_entity_idtoken VARCHAR(80) DEFAULT NULL,
    imported_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)',
    INDEX IDX_GENERATION_ROW_RESULT_STATUS (result_id, status),
    INDEX IDX_GENERATION_ROW_IDEMPOTENCE (company_id, output_schema_version, external_key),
    INDEX IDX_DA947BDC7A7B643 (result_id),
    INDEX IDX_DA947BDC979B1AD6 (company_id),
    PRIMARY KEY(id),
    CONSTRAINT FK_GENERATION_ROW_RESULT FOREIGN KEY (result_id) REFERENCES generation_result (id) ON DELETE CASCADE,
    CONSTRAINT FK_GENERATION_ROW_COMPANY FOREIGN KEY (company_id) REFERENCES societe (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS generation_settings (
    id INT AUTO_INCREMENT NOT NULL,
    company_id INT NOT NULL,
    gpt_url VARCHAR(500) DEFAULT NULL,
    gpt_display_name VARCHAR(120) NOT NULL DEFAULT 'GPT Capsule',
    default_instruction VARCHAR(500) NOT NULL DEFAULT 'Traite le fichier Capsule joint et génère le CSV demandé.',
    auto_open_gpt TINYINT(1) NOT NULL DEFAULT 1,
    auto_download_json TINYINT(1) NOT NULL DEFAULT 1,
    allow_csv_upload_from_modal TINYINT(1) NOT NULL DEFAULT 1,
    retention_days INT NOT NULL,
    max_file_size INT NOT NULL,
    max_rows INT NOT NULL,
    max_cell_size INT NOT NULL,
    unknown_prospect_is_error TINYINT(1) NOT NULL,
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_GENERATION_SETTINGS_COMPANY (company_id),
    PRIMARY KEY(id),
    CONSTRAINT FK_GENERATION_SETTINGS_COMPANY FOREIGN KEY (company_id) REFERENCES societe (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

-- Mise à niveau additive d’une installation où les tables GenerationBundle existent déjà.
ALTER TABLE generation_batch
    ADD COLUMN IF NOT EXISTS gpt_open_triggered_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    ADD COLUMN IF NOT EXISTS gpt_open_triggered_by_id INT DEFAULT NULL;

SET @generation_gpt_actor_index_exists := (
    SELECT COUNT(*) FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'generation_batch'
      AND index_name = 'IDX_GENERATION_BATCH_GPT_OPEN_ACTOR'
);
SET @generation_gpt_actor_index_sql := IF(
    @generation_gpt_actor_index_exists = 0,
    'ALTER TABLE generation_batch ADD INDEX IDX_GENERATION_BATCH_GPT_OPEN_ACTOR (gpt_open_triggered_by_id)',
    'SELECT 1'
);
PREPARE generation_gpt_actor_index_statement FROM @generation_gpt_actor_index_sql;
EXECUTE generation_gpt_actor_index_statement;
DEALLOCATE PREPARE generation_gpt_actor_index_statement;

SET @generation_gpt_actor_fk_exists := (
    SELECT COUNT(*) FROM information_schema.table_constraints
    WHERE constraint_schema = DATABASE()
      AND table_name = 'generation_batch'
      AND constraint_name = 'FK_GENERATION_BATCH_GPT_OPEN_ACTOR'
      AND constraint_type = 'FOREIGN KEY'
);
SET @generation_gpt_actor_fk_sql := IF(
    @generation_gpt_actor_fk_exists = 0,
    'ALTER TABLE generation_batch ADD CONSTRAINT FK_GENERATION_BATCH_GPT_OPEN_ACTOR FOREIGN KEY (gpt_open_triggered_by_id) REFERENCES user (id) ON DELETE SET NULL',
    'SELECT 1'
);
PREPARE generation_gpt_actor_fk_statement FROM @generation_gpt_actor_fk_sql;
EXECUTE generation_gpt_actor_fk_statement;
DEALLOCATE PREPARE generation_gpt_actor_fk_statement;

ALTER TABLE generation_settings
    ADD COLUMN IF NOT EXISTS gpt_display_name VARCHAR(120) NOT NULL DEFAULT 'GPT Capsule',
    ADD COLUMN IF NOT EXISTS default_instruction VARCHAR(500) NOT NULL DEFAULT 'Traite le fichier Capsule joint et génère le CSV demandé.',
    ADD COLUMN IF NOT EXISTS auto_open_gpt TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS auto_download_json TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS allow_csv_upload_from_modal TINYINT(1) NOT NULL DEFAULT 1;

ALTER TABLE prospection_generated_email
    ADD COLUMN IF NOT EXISTS source VARCHAR(30) NOT NULL DEFAULT 'manual';

-- #################################################################################################

-- #################################################################################################


-- CommunicationBundle : socle éditorial multi-société - 2026-07-28
-- Schéma additif validé. Ce bloc ne modifie et ne supprime aucune table existante.
-- Les publications externes, OAuth LinkedIn et métriques restent désactivés dans le lot 1.

CREATE TABLE IF NOT EXISTS communication_category (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    idtoken VARCHAR(48) NOT NULL,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(140) NOT NULL,
    color VARCHAR(16) DEFAULT NULL,
    description LONGTEXT DEFAULT NULL,
    active TINYINT(1) NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    archived_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_COMM_CATEGORY_TOKEN (idtoken),
    UNIQUE INDEX UNIQ_COMM_CATEGORY_COMPANY_SLUG (societe_id, slug),
    INDEX IDX_COMM_CATEGORY_COMPANY_ACTIVE (societe_id, active),
    PRIMARY KEY (id),
    CONSTRAINT FK_COMM_CATEGORY_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_tag (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    idtoken VARCHAR(48) NOT NULL,
    name VARCHAR(80) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    archived_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_COMM_TAG_TOKEN (idtoken),
    UNIQUE INDEX UNIQ_COMM_TAG_COMPANY_SLUG (societe_id, slug),
    INDEX IDX_COMM_TAG_COMPANY (societe_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_COMM_TAG_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_campaign (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    owner_id INT DEFAULT NULL,
    created_by_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    name VARCHAR(180) NOT NULL,
    description LONGTEXT DEFAULT NULL,
    objective VARCHAR(40) NOT NULL,
    status VARCHAR(30) NOT NULL,
    start_date DATE DEFAULT NULL COMMENT '(DC2Type:date_immutable)',
    end_date DATE DEFAULT NULL COMMENT '(DC2Type:date_immutable)',
    budget NUMERIC(14, 2) DEFAULT NULL,
    target_audience LONGTEXT DEFAULT NULL,
    platforms JSON NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    archived_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_COMM_CAMPAIGN_TOKEN (idtoken),
    INDEX IDX_COMM_CAMPAIGN_COMPANY_STATUS (societe_id, status),
    INDEX IDX_COMM_CAMPAIGN_COMPANY_DATES (societe_id, start_date, end_date),
    INDEX IDX_COMM_CAMPAIGN_OWNER (owner_id),
    INDEX IDX_COMM_CAMPAIGN_CREATED_BY (created_by_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_COMM_CAMPAIGN_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_CAMPAIGN_OWNER
        FOREIGN KEY (owner_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_CAMPAIGN_CREATED_BY
        FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_post (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    category_id INT DEFAULT NULL,
    campaign_id INT DEFAULT NULL,
    assigned_to_id INT DEFAULT NULL,
    created_by_id INT DEFAULT NULL,
    updated_by_id INT DEFAULT NULL,
    approved_by_id INT DEFAULT NULL,
    rejected_by_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    internal_title VARCHAR(200) NOT NULL,
    brief LONGTEXT DEFAULT NULL,
    master_content LONGTEXT DEFAULT NULL,
    objective VARCHAR(40) NOT NULL,
    tone VARCHAR(80) DEFAULT NULL,
    source_type VARCHAR(40) DEFAULT NULL,
    source_token VARCHAR(80) DEFAULT NULL,
    status VARCHAR(30) NOT NULL,
    priority VARCHAR(20) NOT NULL,
    approved_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    rejected_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    rejection_reason LONGTEXT DEFAULT NULL,
    content_version INT NOT NULL,
    current_content_hash VARCHAR(64) NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    archived_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_COMM_POST_TOKEN (idtoken),
    INDEX IDX_COMM_POST_COMPANY_STATUS (societe_id, status),
    INDEX IDX_COMM_POST_COMPANY_CAMPAIGN (societe_id, campaign_id),
    INDEX IDX_COMM_POST_COMPANY_ASSIGNEE (societe_id, assigned_to_id),
    INDEX IDX_COMM_POST_COMPANY_UPDATED (societe_id, updated_at),
    INDEX IDX_COMM_POST_CATEGORY (category_id),
    INDEX IDX_COMM_POST_CREATED_BY (created_by_id),
    INDEX IDX_COMM_POST_UPDATED_BY (updated_by_id),
    INDEX IDX_COMM_POST_APPROVED_BY (approved_by_id),
    INDEX IDX_COMM_POST_REJECTED_BY (rejected_by_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_COMM_POST_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_POST_CATEGORY
        FOREIGN KEY (category_id) REFERENCES communication_category (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_POST_CAMPAIGN
        FOREIGN KEY (campaign_id) REFERENCES communication_campaign (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_POST_ASSIGNEE
        FOREIGN KEY (assigned_to_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_POST_CREATED_BY
        FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_POST_UPDATED_BY
        FOREIGN KEY (updated_by_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_POST_APPROVED_BY
        FOREIGN KEY (approved_by_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_POST_REJECTED_BY
        FOREIGN KEY (rejected_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_post_tag (
    post_id INT NOT NULL,
    tag_id INT NOT NULL,
    INDEX IDX_COMM_POST_TAG_TAG (tag_id),
    PRIMARY KEY (post_id, tag_id),
    CONSTRAINT FK_COMM_POST_TAG_POST
        FOREIGN KEY (post_id) REFERENCES communication_post (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_POST_TAG_TAG
        FOREIGN KEY (tag_id) REFERENCES communication_tag (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_idea (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    category_id INT DEFAULT NULL,
    assigned_to_id INT DEFAULT NULL,
    created_by_id INT DEFAULT NULL,
    converted_post_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    title VARCHAR(200) NOT NULL,
    description LONGTEXT DEFAULT NULL,
    objective VARCHAR(40) DEFAULT NULL,
    source_type VARCHAR(40) DEFAULT NULL,
    source_token VARCHAR(80) DEFAULT NULL,
    status VARCHAR(30) NOT NULL,
    priority VARCHAR(20) NOT NULL,
    suggested_platform VARCHAR(30) DEFAULT NULL,
    suggested_publish_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    converted_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)',
    archived_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_COMM_IDEA_TOKEN (idtoken),
    UNIQUE INDEX UNIQ_COMM_IDEA_CONVERTED_POST (converted_post_id),
    INDEX IDX_COMM_IDEA_COMPANY_STATUS (societe_id, status),
    INDEX IDX_COMM_IDEA_COMPANY_SUGGESTED (societe_id, suggested_publish_at),
    INDEX IDX_COMM_IDEA_COMPANY_SOURCE (societe_id, source_type, source_token),
    INDEX IDX_COMM_IDEA_CATEGORY (category_id),
    INDEX IDX_COMM_IDEA_ASSIGNEE (assigned_to_id),
    INDEX IDX_COMM_IDEA_CREATED_BY (created_by_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_COMM_IDEA_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_IDEA_CATEGORY
        FOREIGN KEY (category_id) REFERENCES communication_category (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_IDEA_ASSIGNEE
        FOREIGN KEY (assigned_to_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_IDEA_CREATED_BY
        FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_IDEA_CONVERTED_POST
        FOREIGN KEY (converted_post_id) REFERENCES communication_post (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_idea_tag (
    idea_id INT NOT NULL,
    tag_id INT NOT NULL,
    INDEX IDX_COMM_IDEA_TAG_TAG (tag_id),
    PRIMARY KEY (idea_id, tag_id),
    CONSTRAINT FK_COMM_IDEA_TAG_IDEA
        FOREIGN KEY (idea_id) REFERENCES communication_idea (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_IDEA_TAG_TAG
        FOREIGN KEY (tag_id) REFERENCES communication_tag (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_post_variant (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    post_id INT NOT NULL,
    created_by_id INT DEFAULT NULL,
    updated_by_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    platform VARCHAR(30) NOT NULL,
    name VARCHAR(140) NOT NULL,
    title VARCHAR(255) DEFAULT NULL,
    content LONGTEXT NOT NULL,
    link_url LONGTEXT DEFAULT NULL,
    call_to_action VARCHAR(255) DEFAULT NULL,
    hashtags JSON NOT NULL,
    mentions JSON NOT NULL,
    first_comment LONGTEXT DEFAULT NULL,
    alt_text LONGTEXT DEFAULT NULL,
    locale VARCHAR(16) NOT NULL,
    status VARCHAR(30) NOT NULL,
    is_primary TINYINT(1) NOT NULL,
    content_version INT NOT NULL,
    content_hash VARCHAR(64) NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    locked_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    archived_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_COMM_VARIANT_TOKEN (idtoken),
    INDEX IDX_COMM_VARIANT_COMPANY_POST (societe_id, post_id),
    INDEX IDX_COMM_VARIANT_COMPANY_PLATFORM_STATUS (societe_id, platform, status),
    INDEX IDX_COMM_VARIANT_CREATED_BY (created_by_id),
    INDEX IDX_COMM_VARIANT_UPDATED_BY (updated_by_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_COMM_VARIANT_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_VARIANT_POST
        FOREIGN KEY (post_id) REFERENCES communication_post (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_VARIANT_CREATED_BY
        FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_VARIANT_UPDATED_BY
        FOREIGN KEY (updated_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_media_folder (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    parent_id INT DEFAULT NULL,
    created_by_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    name VARCHAR(140) NOT NULL,
    slug VARCHAR(160) NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    archived_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_COMM_MEDIA_FOLDER_TOKEN (idtoken),
    UNIQUE INDEX UNIQ_COMM_MEDIA_FOLDER_SCOPE (societe_id, parent_id, slug),
    INDEX IDX_COMM_MEDIA_FOLDER_PARENT (parent_id),
    INDEX IDX_COMM_MEDIA_FOLDER_CREATED_BY (created_by_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_COMM_MEDIA_FOLDER_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_MEDIA_FOLDER_PARENT
        FOREIGN KEY (parent_id) REFERENCES communication_media_folder (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_MEDIA_FOLDER_CREATED_BY
        FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_media (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    folder_id INT DEFAULT NULL,
    uploaded_by_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    stored_filename VARCHAR(255) NOT NULL,
    relative_path VARCHAR(500) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    media_type VARCHAR(30) NOT NULL,
    file_size BIGINT NOT NULL,
    width INT DEFAULT NULL,
    height INT DEFAULT NULL,
    duration_seconds INT DEFAULT NULL,
    checksum VARCHAR(64) NOT NULL,
    title VARCHAR(180) DEFAULT NULL,
    description LONGTEXT DEFAULT NULL,
    alt_text LONGTEXT DEFAULT NULL,
    copyright_owner VARCHAR(180) DEFAULT NULL,
    usage_rights LONGTEXT DEFAULT NULL,
    usage_expires_at DATE DEFAULT NULL COMMENT '(DC2Type:date_immutable)',
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    archived_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_COMM_MEDIA_TOKEN (idtoken),
    INDEX IDX_COMM_MEDIA_COMPANY_TYPE (societe_id, media_type),
    INDEX IDX_COMM_MEDIA_COMPANY_FOLDER (societe_id, folder_id),
    INDEX IDX_COMM_MEDIA_COMPANY_CHECKSUM (societe_id, checksum),
    INDEX IDX_COMM_MEDIA_COMPANY_RIGHTS (societe_id, usage_expires_at),
    INDEX IDX_COMM_MEDIA_UPLOADED_BY (uploaded_by_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_COMM_MEDIA_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_MEDIA_FOLDER
        FOREIGN KEY (folder_id) REFERENCES communication_media_folder (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_MEDIA_UPLOADED_BY
        FOREIGN KEY (uploaded_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_media_tag (
    media_id INT NOT NULL,
    tag_id INT NOT NULL,
    INDEX IDX_COMM_MEDIA_TAG_TAG (tag_id),
    PRIMARY KEY (media_id, tag_id),
    CONSTRAINT FK_COMM_MEDIA_TAG_MEDIA
        FOREIGN KEY (media_id) REFERENCES communication_media (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_MEDIA_TAG_TAG
        FOREIGN KEY (tag_id) REFERENCES communication_tag (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_post_media (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    post_id INT NOT NULL,
    variant_id INT DEFAULT NULL,
    media_id INT NOT NULL,
    position INT NOT NULL,
    role VARCHAR(30) NOT NULL,
    caption LONGTEXT DEFAULT NULL,
    alt_text LONGTEXT DEFAULT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    INDEX IDX_COMM_POST_MEDIA_POST_POSITION (societe_id, post_id, position),
    INDEX IDX_COMM_POST_MEDIA_VARIANT_POSITION (societe_id, variant_id, position),
    INDEX IDX_COMM_POST_MEDIA_MEDIA (media_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_COMM_POST_MEDIA_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_POST_MEDIA_POST
        FOREIGN KEY (post_id) REFERENCES communication_post (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_POST_MEDIA_VARIANT
        FOREIGN KEY (variant_id) REFERENCES communication_post_variant (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_POST_MEDIA_MEDIA
        FOREIGN KEY (media_id) REFERENCES communication_media (id) ON DELETE RESTRICT
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_template (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    category_id INT DEFAULT NULL,
    created_by_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    name VARCHAR(180) NOT NULL,
    description LONGTEXT DEFAULT NULL,
    platform VARCHAR(30) NOT NULL,
    objective VARCHAR(40) DEFAULT NULL,
    tone VARCHAR(80) DEFAULT NULL,
    content_template LONGTEXT NOT NULL,
    title_template LONGTEXT DEFAULT NULL,
    default_hashtags JSON NOT NULL,
    available_variables JSON NOT NULL,
    allow_empty_variables TINYINT(1) NOT NULL,
    active TINYINT(1) NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    archived_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_COMM_TEMPLATE_TOKEN (idtoken),
    INDEX IDX_COMM_TEMPLATE_SCOPE (societe_id, active, platform),
    INDEX IDX_COMM_TEMPLATE_CATEGORY (category_id),
    INDEX IDX_COMM_TEMPLATE_CREATED_BY (created_by_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_COMM_TEMPLATE_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_TEMPLATE_CATEGORY
        FOREIGN KEY (category_id) REFERENCES communication_category (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_TEMPLATE_CREATED_BY
        FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_approval (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    post_id INT NOT NULL,
    requested_by_id INT DEFAULT NULL,
    reviewer_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    requested_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    status VARCHAR(30) NOT NULL,
    comment LONGTEXT DEFAULT NULL,
    reviewed_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    content_version INT NOT NULL,
    content_hash VARCHAR(64) NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_COMM_APPROVAL_TOKEN (idtoken),
    INDEX IDX_COMM_APPROVAL_COMPANY_STATUS (societe_id, status, requested_at),
    INDEX IDX_COMM_APPROVAL_REVIEWER_STATUS (societe_id, reviewer_id, status),
    INDEX IDX_COMM_APPROVAL_POST_CREATED (post_id, created_at),
    INDEX IDX_COMM_APPROVAL_REQUESTED_BY (requested_by_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_COMM_APPROVAL_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_APPROVAL_POST
        FOREIGN KEY (post_id) REFERENCES communication_post (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_APPROVAL_REQUESTED_BY
        FOREIGN KEY (requested_by_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_APPROVAL_REVIEWER
        FOREIGN KEY (reviewer_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_social_account (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    connected_by_id INT DEFAULT NULL,
    linked_user_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    platform VARCHAR(30) NOT NULL,
    account_type VARCHAR(30) NOT NULL,
    external_account_id VARCHAR(255) DEFAULT NULL,
    external_account_urn VARCHAR(500) DEFAULT NULL,
    display_name VARCHAR(180) NOT NULL,
    username VARCHAR(180) DEFAULT NULL,
    avatar_url LONGTEXT DEFAULT NULL,
    access_token_encrypted LONGTEXT DEFAULT NULL,
    refresh_token_encrypted LONGTEXT DEFAULT NULL,
    token_expires_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    scopes JSON NOT NULL,
    capabilities JSON NOT NULL,
    status VARCHAR(30) NOT NULL,
    last_checked_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    last_successful_call_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    last_error_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    last_error_message LONGTEXT DEFAULT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    disconnected_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_COMM_SOCIAL_ACCOUNT_TOKEN (idtoken),
    INDEX IDX_COMM_SOCIAL_ACCOUNT_SCOPE (societe_id, platform, status),
    INDEX IDX_COMM_SOCIAL_ACCOUNT_EXTERNAL (societe_id, external_account_urn),
    INDEX IDX_COMM_SOCIAL_ACCOUNT_CONNECTED_BY (connected_by_id),
    INDEX IDX_COMM_SOCIAL_ACCOUNT_LINKED_USER (linked_user_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_COMM_SOCIAL_ACCOUNT_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_SOCIAL_ACCOUNT_CONNECTED_BY
        FOREIGN KEY (connected_by_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_SOCIAL_ACCOUNT_LINKED_USER
        FOREIGN KEY (linked_user_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_publication (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    post_id INT NOT NULL,
    variant_id INT NOT NULL,
    social_account_id INT DEFAULT NULL,
    manually_published_by_id INT DEFAULT NULL,
    created_by_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    platform VARCHAR(30) NOT NULL,
    publication_mode VARCHAR(20) NOT NULL,
    status VARCHAR(30) NOT NULL,
    scheduled_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    timezone VARCHAR(64) NOT NULL,
    queued_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    processing_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    published_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    failed_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    cancelled_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    external_post_id VARCHAR(255) DEFAULT NULL,
    external_post_urn VARCHAR(500) DEFAULT NULL,
    external_post_url LONGTEXT DEFAULT NULL,
    idempotency_key VARCHAR(64) NOT NULL,
    retry_count INT NOT NULL,
    max_retry_count INT NOT NULL,
    next_retry_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    last_error_type VARCHAR(40) DEFAULT NULL,
    last_error_code VARCHAR(100) DEFAULT NULL,
    last_error_message LONGTEXT DEFAULT NULL,
    payload_snapshot JSON DEFAULT NULL,
    content_snapshot JSON DEFAULT NULL,
    media_snapshot JSON DEFAULT NULL,
    provider_response_snapshot JSON DEFAULT NULL,
    manual_comment LONGTEXT DEFAULT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_COMM_PUBLICATION_TOKEN (idtoken),
    UNIQUE INDEX UNIQ_COMM_PUBLICATION_IDEMPOTENCY (idempotency_key),
    INDEX IDX_COMM_PUBLICATION_SCHEDULE (societe_id, status, scheduled_at),
    INDEX IDX_COMM_PUBLICATION_ACCOUNT_STATUS (societe_id, social_account_id, status),
    INDEX IDX_COMM_PUBLICATION_COMPANY_POST (societe_id, post_id),
    INDEX IDX_COMM_PUBLICATION_RETRY (societe_id, next_retry_at),
    INDEX IDX_COMM_PUBLICATION_VARIANT (variant_id),
    INDEX IDX_COMM_PUBLICATION_MANUAL_USER (manually_published_by_id),
    INDEX IDX_COMM_PUBLICATION_CREATED_BY (created_by_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_COMM_PUBLICATION_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_PUBLICATION_POST
        FOREIGN KEY (post_id) REFERENCES communication_post (id) ON DELETE RESTRICT,
    CONSTRAINT FK_COMM_PUBLICATION_VARIANT
        FOREIGN KEY (variant_id) REFERENCES communication_post_variant (id) ON DELETE RESTRICT,
    CONSTRAINT FK_COMM_PUBLICATION_ACCOUNT
        FOREIGN KEY (social_account_id) REFERENCES communication_social_account (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_PUBLICATION_MANUAL_USER
        FOREIGN KEY (manually_published_by_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_COMM_PUBLICATION_CREATED_BY
        FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_publication_log (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    publication_id INT NOT NULL,
    user_id INT DEFAULT NULL,
    event_type VARCHAR(50) NOT NULL,
    status_before VARCHAR(30) DEFAULT NULL,
    status_after VARCHAR(30) DEFAULT NULL,
    message LONGTEXT DEFAULT NULL,
    error_code VARCHAR(100) DEFAULT NULL,
    metadata JSON NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    INDEX IDX_COMM_PUBLICATION_LOG_TARGET (societe_id, publication_id, created_at),
    INDEX IDX_COMM_PUBLICATION_LOG_EVENT (societe_id, event_type, created_at),
    INDEX IDX_COMM_PUBLICATION_LOG_USER (user_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_COMM_PUBLICATION_LOG_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_PUBLICATION_LOG_PUBLICATION
        FOREIGN KEY (publication_id) REFERENCES communication_publication (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_PUBLICATION_LOG_USER
        FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_audit_log (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    user_id INT DEFAULT NULL,
    target_type VARCHAR(50) NOT NULL,
    target_idtoken VARCHAR(48) NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    message LONGTEXT DEFAULT NULL,
    metadata JSON NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    INDEX IDX_COMM_AUDIT_TARGET (societe_id, target_type, target_idtoken, created_at),
    INDEX IDX_COMM_AUDIT_EVENT (societe_id, event_type, created_at),
    INDEX IDX_COMM_AUDIT_USER (user_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_COMM_AUDIT_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_AUDIT_USER
        FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS communication_settings (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    updated_by_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    approval_required TINYINT(1) NOT NULL,
    direct_publication_allowed TINYINT(1) NOT NULL,
    author_self_approval_allowed TINYINT(1) NOT NULL,
    edit_after_approval_allowed TINYINT(1) NOT NULL,
    minimum_days_before_publication INT NOT NULL,
    timezone VARCHAR(64) NOT NULL,
    publication_hours JSON NOT NULL,
    enabled_platforms JSON NOT NULL,
    ai_enabled TINYINT(1) NOT NULL,
    automatic_publication_enabled TINYINT(1) NOT NULL,
    retries_enabled TINYINT(1) NOT NULL,
    max_retry_count INT NOT NULL,
    sync_frequency_minutes INT NOT NULL,
    notifications_enabled TINYINT(1) NOT NULL,
    hashtag_library JSON NOT NULL,
    default_signature LONGTEXT DEFAULT NULL,
    default_cta LONGTEXT DEFAULT NULL,
    default_tone VARCHAR(80) DEFAULT NULL,
    default_locale VARCHAR(16) NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_COMM_SETTINGS_TOKEN (idtoken),
    UNIQUE INDEX UNIQ_COMM_SETTINGS_COMPANY (societe_id),
    INDEX IDX_COMM_SETTINGS_UPDATED_BY (updated_by_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_COMM_SETTINGS_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_COMM_SETTINGS_UPDATED_BY
        FOREIGN KEY (updated_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

INSERT INTO communication_settings (
    societe_id,
    updated_by_id,
    idtoken,
    approval_required,
    direct_publication_allowed,
    author_self_approval_allowed,
    edit_after_approval_allowed,
    minimum_days_before_publication,
    timezone,
    publication_hours,
    enabled_platforms,
    ai_enabled,
    automatic_publication_enabled,
    retries_enabled,
    max_retry_count,
    sync_frequency_minutes,
    notifications_enabled,
    hashtag_library,
    default_signature,
    default_cta,
    default_tone,
    default_locale,
    created_at,
    updated_at
)
SELECT
    s.id,
    NULL,
    LEFT(SHA2(CONCAT(UUID(), '-communication-settings-', s.id), 256), 48),
    1,
    0,
    0,
    0,
    0,
    'Europe/Paris',
    JSON_ARRAY(),
    JSON_ARRAY('generic', 'linkedin'),
    0,
    0,
    1,
    3,
    60,
    1,
    JSON_ARRAY(),
    NULL,
    NULL,
    NULL,
    'fr_FR',
    NOW(),
    NOW()
FROM societe s
WHERE NOT EXISTS (
    SELECT 1
    FROM communication_settings cs
    WHERE cs.societe_id = s.id
);

INSERT INTO subscription (societe_id, bundle_name, active)
SELECT s.id, 'CommunicationBundle', 1
FROM societe s
WHERE NOT EXISTS (
    SELECT 1
    FROM subscription sub
    WHERE sub.societe_id = s.id
      AND LOWER(REPLACE(REPLACE(sub.bundle_name, '_', ''), '-', '')) = 'communicationbundle'
);

-- #################################################################################################

-- Catégories commerciales communes aux prospects et aux clients.
INSERT INTO categorie_client (idtoken, nom, created_at, deleted, societe_id)
SELECT SHA2(CONCAT(UUID(), '-rexane-', s.id), 256), 'Rexane', NOW(), 0, s.id
FROM societe s
WHERE NOT EXISTS (
    SELECT 1 FROM categorie_client c WHERE c.societe_id = s.id AND LOWER(c.nom) = 'rexane'
);

INSERT INTO categorie_client (idtoken, nom, created_at, deleted, societe_id)
SELECT SHA2(CONCAT(UUID(), '-fundup-', s.id), 256), 'FundUp', NOW(), 0, s.id
FROM societe s
WHERE NOT EXISTS (
    SELECT 1 FROM categorie_client c WHERE c.societe_id = s.id AND LOWER(c.nom) = 'fundup'
);


-- Prospects commerciaux : valeur estimée - 2026-07-28
-- Équivalent SQL de DoctrineMigrations\Version20260728143000.
ALTER TABLE client
    ADD prospect_estimated_value NUMERIC(12, 2) DEFAULT NULL;

-- #################################################################################################


-- ProspectionBundle : CRM prospects, activités enrichies et planning de contenu - 2026-07-28
-- Équivalent SQL de DoctrineMigrations\Version20260728120000.
-- Les CREATE IF NOT EXISTS rendent ce bloc compatible avec une base déjà migrée par Doctrine.

CREATE TABLE IF NOT EXISTS prospection_crm_activity (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    prospect_id INT NOT NULL,
    owner_id INT DEFAULT NULL,
    type VARCHAR(30) NOT NULL,
    status VARCHAR(30) NOT NULL,
    title VARCHAR(255) NOT NULL,
    scheduled_at DATETIME NOT NULL,
    completed_at DATETIME DEFAULT NULL,
    location VARCHAR(255) DEFAULT NULL,
    report LONGTEXT DEFAULT NULL,
    email_subject VARCHAR(255) DEFAULT NULL,
    email_content LONGTEXT DEFAULT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX IDX_PCA_COMPANY (societe_id),
    INDEX IDX_PCA_PROSPECT (prospect_id),
    INDEX IDX_PCA_OWNER (owner_id),
    INDEX idx_prospection_crm_activity_scheduled (scheduled_at),
    INDEX idx_prospection_crm_activity_status (status),
    PRIMARY KEY (id),
    CONSTRAINT FK_PCA_COMPANY FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_PCA_PROSPECT FOREIGN KEY (prospect_id) REFERENCES client (id) ON DELETE CASCADE,
    CONSTRAINT FK_PCA_OWNER FOREIGN KEY (owner_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS prospection_content_item (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    author_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    channel VARCHAR(30) NOT NULL,
    status VARCHAR(30) NOT NULL,
    title VARCHAR(255) NOT NULL,
    topic VARCHAR(255) DEFAULT NULL,
    tone VARCHAR(30) DEFAULT NULL,
    content LONGTEXT NOT NULL,
    scheduled_at DATETIME DEFAULT NULL,
    published_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE INDEX UNIQ_PROSPECTION_CONTENT_TOKEN (idtoken),
    INDEX IDX_PCI_COMPANY (societe_id),
    INDEX IDX_PCI_AUTHOR (author_id),
    INDEX idx_prospection_content_scheduled (scheduled_at),
    INDEX idx_prospection_content_status (status),
    PRIMARY KEY (id),
    CONSTRAINT FK_PCI_COMPANY FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_PCI_AUTHOR FOREIGN KEY (author_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

-- #################################################################################################

-- CommercialBundle : calendrier commercial et synchronisation Google Calendar - 2026-07-23
-- Équivalent SQL de DoctrineMigrations\Version20260723153000.
-- Ne stocker les identifiants OAuth que dans les colonnes *_encrypted.

CREATE TABLE IF NOT EXISTS calendar_integration_account (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    user_id INT NOT NULL,
    provider VARCHAR(30) NOT NULL,
    google_account_email VARCHAR(180) NOT NULL,
    google_account_id VARCHAR(255) DEFAULT NULL,
    access_token_encrypted LONGTEXT DEFAULT NULL,
    refresh_token_encrypted LONGTEXT DEFAULT NULL,
    token_expires_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    scopes JSON NOT NULL,
    status VARCHAR(30) NOT NULL,
    last_connected_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    last_sync_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    last_error_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    last_error_message LONGTEXT DEFAULT NULL,
    disconnected_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_calendar_account_user_provider (societe_id, user_id, provider),
    INDEX IDX_8F253F16FCF77503 (societe_id),
    INDEX IDX_8F253F16A76ED395 (user_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_8F253F16FCF77503 FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_8F253F16A76ED395 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS calendar_source (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    user_id INT NOT NULL,
    integration_account_id INT NOT NULL,
    provider VARCHAR(30) NOT NULL,
    external_calendar_id VARCHAR(255) NOT NULL,
    name VARCHAR(180) NOT NULL,
    description LONGTEXT DEFAULT NULL,
    is_primary TINYINT(1) NOT NULL,
    access_role VARCHAR(30) NOT NULL,
    color VARCHAR(20) NOT NULL,
    time_zone VARCHAR(80) NOT NULL,
    sync_enabled TINYINT(1) NOT NULL,
    sync_direction VARCHAR(30) NOT NULL,
    import_enabled TINYINT(1) NOT NULL,
    export_enabled TINYINT(1) NOT NULL,
    default_for_appointments TINYINT(1) NOT NULL,
    default_for_tasks TINYINT(1) NOT NULL,
    last_sync_token LONGTEXT DEFAULT NULL,
    pending_sync TINYINT(1) NOT NULL,
    last_full_sync_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    last_incremental_sync_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_calendar_source_external (integration_account_id, external_calendar_id),
    INDEX IDX_F5EC672FFCF77503 (societe_id),
    INDEX IDX_F5EC672FA76ED395 (user_id),
    INDEX IDX_F5EC672FD4F46039 (integration_account_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_F5EC672FFCF77503 FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_F5EC672FA76ED395 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE,
    CONSTRAINT FK_F5EC672FD4F46039 FOREIGN KEY (integration_account_id) REFERENCES calendar_integration_account (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

-- Compatibilité avec les premières installations Calendar : `primary` est un mot réservé MySQL.
SET @calendar_source_primary_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'calendar_source' AND COLUMN_NAME = 'primary'
);
SET @calendar_source_is_primary_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'calendar_source' AND COLUMN_NAME = 'is_primary'
);
SET @calendar_source_primary_sql = IF(
    @calendar_source_primary_exists > 0 AND @calendar_source_is_primary_exists = 0,
    'ALTER TABLE calendar_source CHANGE `primary` is_primary TINYINT(1) NOT NULL',
    'SELECT 1'
);
PREPARE calendar_source_primary_statement FROM @calendar_source_primary_sql;
EXECUTE calendar_source_primary_statement;
DEALLOCATE PREPARE calendar_source_primary_statement;

CREATE TABLE IF NOT EXISTS calendar_event (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    calendar_source_id INT DEFAULT NULL,
    owner_id INT NOT NULL,
    assigned_to_id INT DEFAULT NULL,
    created_by_id INT NOT NULL,
    updated_by_id INT DEFAULT NULL,
    public_id VARCHAR(36) NOT NULL,
    source_type VARCHAR(40) NOT NULL,
    source_entity_type VARCHAR(120) DEFAULT NULL,
    source_entity_id INT DEFAULT NULL,
    title VARCHAR(255) NOT NULL,
    description LONGTEXT DEFAULT NULL,
    location VARCHAR(255) DEFAULT NULL,
    meeting_url VARCHAR(500) DEFAULT NULL,
    starts_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    ends_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    all_day TINYINT(1) NOT NULL,
    time_zone VARCHAR(80) NOT NULL,
    status VARCHAR(30) NOT NULL,
    visibility VARCHAR(30) NOT NULL,
    transparency VARCHAR(20) NOT NULL,
    priority VARCHAR(20) NOT NULL,
    color_key VARCHAR(30) DEFAULT NULL,
    reminder_policy VARCHAR(40) NOT NULL,
    sync_enabled TINYINT(1) NOT NULL,
    sync_direction VARCHAR(30) NOT NULL,
    sync_status VARCHAR(30) NOT NULL,
    google_calendar_id VARCHAR(255) DEFAULT NULL,
    google_event_id VARCHAR(255) DEFAULT NULL,
    google_ical_uid VARCHAR(255) DEFAULT NULL,
    google_etag VARCHAR(255) DEFAULT NULL,
    google_html_link VARCHAR(500) DEFAULT NULL,
    google_updated_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    last_synced_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    last_local_change_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    last_remote_change_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    metadata JSON NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    cancelled_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    deleted_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_57FA09C9B5B48B91 (public_id),
    UNIQUE INDEX uniq_calendar_google_event (calendar_source_id, google_event_id),
    INDEX IDX_57FA09C9FCF77503 (societe_id),
    INDEX IDX_57FA09C9D650972B (calendar_source_id),
    INDEX IDX_57FA09C97E3C61F9 (owner_id),
    INDEX IDX_57FA09C9F4BD7827 (assigned_to_id),
    INDEX IDX_57FA09C9B03A8386 (created_by_id),
    INDEX IDX_57FA09C9896DBBDE (updated_by_id),
    INDEX idx_calendar_event_tenant_range (societe_id, starts_at, ends_at),
    INDEX idx_calendar_event_source_entity (source_entity_type, source_entity_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_57FA09C9FCF77503 FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_57FA09C9D650972B FOREIGN KEY (calendar_source_id) REFERENCES calendar_source (id) ON DELETE SET NULL,
    CONSTRAINT FK_57FA09C97E3C61F9 FOREIGN KEY (owner_id) REFERENCES user (id) ON DELETE CASCADE,
    CONSTRAINT FK_57FA09C9F4BD7827 FOREIGN KEY (assigned_to_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_57FA09C9B03A8386 FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE CASCADE,
    CONSTRAINT FK_57FA09C9896DBBDE FOREIGN KEY (updated_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS calendar_event_attendee (
    id INT AUTO_INCREMENT NOT NULL,
    event_id INT NOT NULL,
    user_id INT DEFAULT NULL,
    email VARCHAR(180) NOT NULL,
    display_name VARCHAR(180) DEFAULT NULL,
    response_status VARCHAR(30) NOT NULL,
    optional TINYINT(1) NOT NULL,
    INDEX IDX_E53E63E771F7E88B (event_id),
    INDEX IDX_E53E63E7A76ED395 (user_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_E53E63E771F7E88B FOREIGN KEY (event_id) REFERENCES calendar_event (id) ON DELETE CASCADE,
    CONSTRAINT FK_E53E63E7A76ED395 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS calendar_event_reminder (
    id INT AUTO_INCREMENT NOT NULL,
    event_id INT NOT NULL,
    method VARCHAR(20) NOT NULL,
    minutes_before INT NOT NULL,
    sent_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    INDEX IDX_B459F9C071F7E88B (event_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_B459F9C071F7E88B FOREIGN KEY (event_id) REFERENCES calendar_event (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS calendar_sync_job (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    integration_account_id INT DEFAULT NULL,
    calendar_source_id INT DEFAULT NULL,
    event_id INT DEFAULT NULL,
    type VARCHAR(40) NOT NULL,
    status VARCHAR(30) NOT NULL,
    dedupe_key VARCHAR(180) NOT NULL,
    payload JSON NOT NULL,
    attempts INT NOT NULL,
    available_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    started_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    finished_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    error_message LONGTEXT DEFAULT NULL,
    stats JSON NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX uniq_calendar_sync_job_dedupe (dedupe_key),
    INDEX IDX_40F4B923FCF77503 (societe_id),
    INDEX IDX_40F4B923D4F46039 (integration_account_id),
    INDEX IDX_40F4B923D650972B (calendar_source_id),
    INDEX IDX_40F4B92371F7E88B (event_id),
    INDEX idx_calendar_sync_job_queue (status, available_at),
    PRIMARY KEY (id),
    CONSTRAINT FK_40F4B923FCF77503 FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_40F4B923D4F46039 FOREIGN KEY (integration_account_id) REFERENCES calendar_integration_account (id) ON DELETE SET NULL,
    CONSTRAINT FK_40F4B923D650972B FOREIGN KEY (calendar_source_id) REFERENCES calendar_source (id) ON DELETE SET NULL,
    CONSTRAINT FK_40F4B92371F7E88B FOREIGN KEY (event_id) REFERENCES calendar_event (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS calendar_sync_log (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    integration_account_id INT DEFAULT NULL,
    calendar_source_id INT DEFAULT NULL,
    job_id INT DEFAULT NULL,
    level VARCHAR(20) NOT NULL,
    action VARCHAR(80) NOT NULL,
    message LONGTEXT NOT NULL,
    context JSON NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    INDEX IDX_3413311EFCF77503 (societe_id),
    INDEX IDX_3413311ED4F46039 (integration_account_id),
    INDEX IDX_3413311ED650972B (calendar_source_id),
    INDEX IDX_3413311EBE04EA9 (job_id),
    INDEX idx_calendar_sync_log_tenant (societe_id, created_at),
    PRIMARY KEY (id),
    CONSTRAINT FK_3413311EFCF77503 FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_3413311ED4F46039 FOREIGN KEY (integration_account_id) REFERENCES calendar_integration_account (id) ON DELETE SET NULL,
    CONSTRAINT FK_3413311ED650972B FOREIGN KEY (calendar_source_id) REFERENCES calendar_source (id) ON DELETE SET NULL,
    CONSTRAINT FK_3413311EBE04EA9 FOREIGN KEY (job_id) REFERENCES calendar_sync_job (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS calendar_webhook_channel (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    integration_account_id INT NOT NULL,
    calendar_source_id INT NOT NULL,
    channel_id VARCHAR(180) NOT NULL,
    resource_id VARCHAR(180) NOT NULL,
    resource_uri VARCHAR(500) NOT NULL,
    token_hash VARCHAR(64) NOT NULL,
    address VARCHAR(500) NOT NULL,
    expires_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    status VARCHAR(30) NOT NULL,
    last_notification_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    stopped_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_calendar_webhook_channel (channel_id),
    INDEX IDX_E7BD0374FCF77503 (societe_id),
    INDEX IDX_E7BD0374D4F46039 (integration_account_id),
    INDEX IDX_E7BD0374D650972B (calendar_source_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_E7BD0374FCF77503 FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_E7BD0374D4F46039 FOREIGN KEY (integration_account_id) REFERENCES calendar_integration_account (id) ON DELETE CASCADE,
    CONSTRAINT FK_E7BD0374D650972B FOREIGN KEY (calendar_source_id) REFERENCES calendar_source (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS calendar_conflict (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    calendar_event_id INT NOT NULL,
    calendar_source_id INT DEFAULT NULL,
    resolved_by_id INT DEFAULT NULL,
    conflict_type VARCHAR(80) NOT NULL,
    local_snapshot JSON NOT NULL,
    remote_snapshot JSON NOT NULL,
    status VARCHAR(30) NOT NULL,
    resolution_strategy VARCHAR(40) DEFAULT NULL,
    resolved_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)',
    INDEX IDX_4818BDC7FCF77503 (societe_id),
    INDEX IDX_4818BDC77495C8E3 (calendar_event_id),
    INDEX IDX_4818BDC7D650972B (calendar_source_id),
    INDEX IDX_4818BDC76713A32B (resolved_by_id),
    INDEX idx_calendar_conflict_tenant_status (societe_id, status),
    PRIMARY KEY (id),
    CONSTRAINT FK_4818BDC7FCF77503 FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_4818BDC77495C8E3 FOREIGN KEY (calendar_event_id) REFERENCES calendar_event (id) ON DELETE CASCADE,
    CONSTRAINT FK_4818BDC7D650972B FOREIGN KEY (calendar_source_id) REFERENCES calendar_source (id) ON DELETE SET NULL,
    CONSTRAINT FK_4818BDC76713A32B FOREIGN KEY (resolved_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

-- #################################################################################################

-- 001/002 - Création client entreprise et référentiel des formes juridiques

ALTER TABLE client
    ADD siret VARCHAR(14) DEFAULT NULL,
    ADD siren VARCHAR(9) DEFAULT NULL,
    ADD forme_juridique VARCHAR(255) DEFAULT NULL,
    ADD code_naf VARCHAR(16) DEFAULT NULL,
    ADD departement VARCHAR(128) DEFAULT NULL,
    ADD notes_commerciales LONGTEXT DEFAULT NULL;

ALTER TABLE user
    ADD contact_principal TINYINT(1) DEFAULT 0 NOT NULL;

CREATE INDEX IDX_CLIENT_SIRET ON client (siret);
CREATE INDEX IDX_CLIENT_SIREN ON client (siren);

CREATE TABLE forme_juridique (
    id INT AUTO_INCREMENT NOT NULL,
    code VARCHAR(10) NOT NULL,
    nom VARCHAR(255) NOT NULL,
    categorie VARCHAR(100) NOT NULL,
    actif TINYINT(1) DEFAULT 1 NOT NULL,
    UNIQUE INDEX UNIQ_FORME_JURIDIQUE_CODE (code),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

INSERT INTO forme_juridique (code, nom, categorie, actif) VALUES
    ('1000', 'Micro-entreprise', 'Entreprises individuelles', 1),
    ('2110', 'Société créée de fait', 'Sociétés de personnes', 1),
    ('2310', 'Société en nom collectif (SNC)', 'Sociétés de personnes', 1),
    ('2385', 'Société en commandite simple (SCS)', 'Sociétés de personnes', 1),
    ('2400', 'Société à responsabilité limitée (SARL)', 'Sociétés commerciales', 1),
    ('2410', 'Entreprise unipersonnelle à responsabilité limitée (EURL)', 'Sociétés commerciales', 1),
    ('2710', 'Société anonyme à directoire', 'Sociétés commerciales', 1),
    ('2800', 'Société en commandite par actions (SCA)', 'Sociétés commerciales', 1),
    ('2900', 'Société par actions simplifiée (SAS)', 'Sociétés commerciales', 1),
    ('2910', 'Société par actions simplifiée unipersonnelle (SASU)', 'Sociétés commerciales', 1),
    ('3205', 'Société civile immobilière (SCI)', 'Sociétés civiles', 1),
    ('3210', 'Société civile professionnelle (SCP)', 'Sociétés civiles', 1),
    ('3290', 'Autre société civile', 'Sociétés civiles', 1),
    ('4110', 'Groupement d’intérêt économique (GIE)', 'Groupements', 1),
    ('4300', 'Groupement agricole d’exploitation en commun (GAEC)', 'Groupements', 1),
    ('4400', 'Société coopérative agricole', 'Coopératives', 1),
    ('5498', 'Société à responsabilité limitée (SARL)', 'Sociétés commerciales', 1),
    ('5505', 'Société anonyme (SA)', 'Sociétés commerciales', 1),
    ('5710', 'Société par actions simplifiée (SAS)', 'Sociétés commerciales', 1),
    ('6110', 'Société d’exercice libéral (SEL)', 'Professions libérales', 1),
    ('6220', 'Société civile de moyens (SCM)', 'Sociétés civiles', 1),
    ('6310', 'Société coopérative de production (SCOP)', 'Coopératives', 1),
    ('6400', 'Société coopérative d’intérêt collectif (SCIC)', 'Coopératives', 1),
    ('6538', 'Société d’économie mixte (SEM)', 'Secteur public', 1),
    ('6545', 'Société publique locale (SPL)', 'Secteur public', 1),
    ('7700', 'Association loi 1901', 'Associations et fondations', 1),
    ('7710', 'Association non déclarée', 'Associations et fondations', 1),
    ('7750', 'Fondation', 'Associations et fondations', 1),
    ('9000', 'Forme juridique non précisée', 'Autres', 1);

ALTER TABLE client ADD forme_juridique_id INT DEFAULT NULL;
UPDATE client c
INNER JOIN forme_juridique f ON LOWER(f.nom) = LOWER(c.forme_juridique)
SET c.forme_juridique_id = f.id
WHERE c.forme_juridique IS NOT NULL;
ALTER TABLE client DROP forme_juridique;
ALTER TABLE client
    ADD CONSTRAINT FK_CLIENT_FORME_JURIDIQUE
    FOREIGN KEY (forme_juridique_id) REFERENCES forme_juridique (id) ON DELETE SET NULL;
CREATE INDEX IDX_CLIENT_FORME_JURIDIQUE ON client (forme_juridique_id);

-- #################################################################################################

-- Correction MySQL 9 : last_value est un mot réservé et bloquait la validation des factures

ALTER TABLE billing_numbering_sequence
    CHANGE `last_value` last_number INT NOT NULL;

-- #################################################################################################

-- CommercialBundle : banques, catalogue produits, prospects et snapshots bancaires - 2026-07-18
-- Équivalent SQL de DoctrineMigrations\Version20260718103000.
-- Ajouts uniquement : aucune suppression de table ou de colonne.

CREATE TABLE product_category (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    idtoken VARCHAR(64) NOT NULL,
    name VARCHAR(120) NOT NULL,
    code VARCHAR(30) NOT NULL,
    description LONGTEXT DEFAULT NULL,
    color VARCHAR(20) NOT NULL,
    position INT NOT NULL,
    deleted TINYINT(1) NOT NULL,
    created_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    updated_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    UNIQUE INDEX UNIQ_PRODUCT_CATEGORY_TOKEN (idtoken),
    INDEX IDX_PRODUCT_CATEGORY_COMPANY (societe_id),
    INDEX idx_product_category_scope (societe_id, deleted, position),
    PRIMARY KEY(id),
    CONSTRAINT FK_PRODUCT_CATEGORY_COMPANY
        FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

ALTER TABLE produit
    ADD idtoken VARCHAR(64) DEFAULT NULL,
    ADD category_id INT DEFAULT NULL,
    ADD reference VARCHAR(80) DEFAULT NULL,
    ADD unit VARCHAR(30) DEFAULT 'unite' NOT NULL,
    ADD product_type VARCHAR(30) DEFAULT 'service' NOT NULL,
    ADD internal_cost NUMERIC(12, 2) DEFAULT NULL,
    ADD target_margin_rate NUMERIC(5, 2) DEFAULT NULL,
    ADD notes LONGTEXT DEFAULT NULL,
    ADD created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    ADD updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '(DC2Type:datetime_immutable)';

UPDATE produit
SET idtoken = MD5(CONCAT('product-', id, '-', UUID()))
WHERE idtoken IS NULL;

ALTER TABLE produit
    MODIFY idtoken VARCHAR(64) NOT NULL,
    ADD UNIQUE INDEX UNIQ_PRODUCT_TOKEN (idtoken),
    ADD INDEX IDX_PRODUCT_CATEGORY (category_id),
    ADD CONSTRAINT FK_PRODUCT_CATEGORY
        FOREIGN KEY (category_id) REFERENCES product_category (id) ON DELETE SET NULL;

ALTER TABLE banque
    ADD account_holder VARCHAR(255) DEFAULT NULL,
    ADD currency VARCHAR(3) DEFAULT 'EUR' NOT NULL,
    ADD is_default TINYINT(1) DEFAULT 0 NOT NULL,
    ADD notes LONGTEXT DEFAULT NULL,
    ADD created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    ADD updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    ADD INDEX idx_bank_company_default (societe_id, deleted, is_default);

ALTER TABLE client
    ADD prospect_status VARCHAR(32) DEFAULT NULL,
    ADD prospect_source VARCHAR(100) DEFAULT NULL,
    ADD prospect_owner_id INT DEFAULT NULL,
    ADD converted_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    ADD converted_by_id INT DEFAULT NULL,
    ADD INDEX IDX_CLIENT_PROSPECT_OWNER (prospect_owner_id),
    ADD INDEX IDX_CLIENT_CONVERTED_BY (converted_by_id),
    ADD INDEX idx_client_prospect_scope (societe_id, entite, isdelete, archive);

ALTER TABLE client
    ADD CONSTRAINT FK_CLIENT_PROSPECT_OWNER
        FOREIGN KEY (prospect_owner_id) REFERENCES user (id) ON DELETE SET NULL,
    ADD CONSTRAINT FK_CLIENT_CONVERTED_BY
        FOREIGN KEY (converted_by_id) REFERENCES user (id) ON DELETE SET NULL;

ALTER TABLE billing_document
    ADD bank_account_id INT DEFAULT NULL,
    ADD bank_label_snapshot VARCHAR(255) DEFAULT NULL,
    ADD bank_holder_snapshot VARCHAR(255) DEFAULT NULL,
    ADD bank_iban_snapshot VARCHAR(255) DEFAULT NULL,
    ADD bank_bic_snapshot VARCHAR(255) DEFAULT NULL,
    ADD bank_currency_snapshot VARCHAR(3) DEFAULT NULL,
    ADD INDEX IDX_BILLING_DOCUMENT_BANK (bank_account_id),
    ADD CONSTRAINT FK_BILLING_DOCUMENT_BANK
        FOREIGN KEY (bank_account_id) REFERENCES banque (id) ON DELETE SET NULL;

-- #################################################################################################


-- RoutineBundle : routines internes, tâches, checklists et historique - 2026-07-22
-- Équivalent SQL de DoctrineMigrations\Version20260722150000.
-- Sept nouvelles tables uniquement. Les CREATE IF NOT EXISTS permettent l'exécution après la migration Doctrine.

CREATE TABLE IF NOT EXISTS routine_category (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    idtoken VARCHAR(48) NOT NULL,
    name VARCHAR(120) NOT NULL,
    code VARCHAR(80) NOT NULL,
    description LONGTEXT DEFAULT NULL,
    color VARCHAR(30) NOT NULL,
    position INT NOT NULL,
    active TINYINT(1) NOT NULL,
    archived_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE INDEX UNIQ_F858FCF78932CD22 (idtoken),
    UNIQUE INDEX uniq_routine_category_company_code (societe_id, code),
    INDEX IDX_F858FCF7FCF77503 (societe_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_F858FCF7FCF77503 FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS routine_template (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    category_id INT DEFAULT NULL,
    assigned_to_id INT DEFAULT NULL,
    validator_id INT DEFAULT NULL,
    created_by_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description LONGTEXT DEFAULT NULL,
    priority VARCHAR(20) NOT NULL,
    status VARCHAR(20) NOT NULL,
    recurrence_type VARCHAR(20) NOT NULL,
    interval_value INT NOT NULL,
    weekdays JSON NOT NULL,
    month_day INT DEFAULT NULL,
    monthly_rule VARCHAR(30) DEFAULT NULL,
    `year_month` INT DEFAULT NULL,
    year_day INT DEFAULT NULL,
    start_date DATE NOT NULL,
    end_date DATE DEFAULT NULL,
    workday_policy VARCHAR(30) NOT NULL,
    scheduled_time TIME DEFAULT NULL,
    estimated_minutes INT DEFAULT NULL,
    requires_validation TINYINT(1) NOT NULL,
    action_code VARCHAR(100) DEFAULT NULL,
    action_parameters JSON NOT NULL,
    paused_from DATE DEFAULT NULL,
    paused_until DATE DEFAULT NULL,
    active TINYINT(1) NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    archived_at DATETIME DEFAULT NULL,
    UNIQUE INDEX UNIQ_6974FAB58932CD22 (idtoken),
    INDEX IDX_6974FAB5FCF77503 (societe_id),
    INDEX IDX_6974FAB512469DE2 (category_id),
    INDEX IDX_6974FAB5F4BD7827 (assigned_to_id),
    INDEX IDX_6974FAB5B0644AEC (validator_id),
    INDEX IDX_6974FAB5B03A8386 (created_by_id),
    INDEX idx_routine_template_status (status),
    PRIMARY KEY (id),
    CONSTRAINT FK_6974FAB5FCF77503 FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_6974FAB512469DE2 FOREIGN KEY (category_id) REFERENCES routine_category (id) ON DELETE SET NULL,
    CONSTRAINT FK_6974FAB5F4BD7827 FOREIGN KEY (assigned_to_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_6974FAB5B0644AEC FOREIGN KEY (validator_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_6974FAB5B03A8386 FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS routine_checklist_template_item (
    id INT AUTO_INCREMENT NOT NULL,
    template_id INT NOT NULL,
    label VARCHAR(255) NOT NULL,
    description LONGTEXT DEFAULT NULL,
    position INT NOT NULL,
    required TINYINT(1) NOT NULL,
    INDEX IDX_CCA7AAEA5DA0FB8 (template_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_CCA7AAEA5DA0FB8 FOREIGN KEY (template_id) REFERENCES routine_template (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS routine_task (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    routine_template_id INT DEFAULT NULL,
    category_id INT DEFAULT NULL,
    assigned_to_id INT DEFAULT NULL,
    created_by_id INT DEFAULT NULL,
    validator_id INT DEFAULT NULL,
    completed_by_id INT DEFAULT NULL,
    validated_by_id INT DEFAULT NULL,
    postponed_by_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    original_occurrence_date DATE DEFAULT NULL,
    occurrence_key VARCHAR(120) DEFAULT NULL,
    scheduled_date DATE NOT NULL,
    scheduled_time TIME DEFAULT NULL,
    due_at DATETIME DEFAULT NULL,
    title_snapshot VARCHAR(255) NOT NULL,
    description_snapshot LONGTEXT DEFAULT NULL,
    category_name_snapshot VARCHAR(120) DEFAULT NULL,
    category_code_snapshot VARCHAR(80) DEFAULT NULL,
    priority_snapshot VARCHAR(20) NOT NULL,
    action_code_snapshot VARCHAR(100) DEFAULT NULL,
    action_parameters_snapshot JSON NOT NULL,
    status VARCHAR(30) NOT NULL,
    estimated_minutes INT DEFAULT NULL,
    actual_minutes INT DEFAULT NULL,
    requires_validation TINYINT(1) NOT NULL,
    completed_at DATETIME DEFAULT NULL,
    validated_at DATETIME DEFAULT NULL,
    postponed_at DATETIME DEFAULT NULL,
    postpone_reason LONGTEXT DEFAULT NULL,
    postpone_count INT NOT NULL,
    skip_reason LONGTEXT DEFAULT NULL,
    cancel_reason LONGTEXT DEFAULT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE INDEX UNIQ_48CA291D8932CD22 (idtoken),
    UNIQUE INDEX uniq_routine_task_occurrence (societe_id, routine_template_id, occurrence_key),
    INDEX IDX_48CA291DFCF77503 (societe_id),
    INDEX IDX_48CA291D1CEF45B0 (routine_template_id),
    INDEX IDX_48CA291D12469DE2 (category_id),
    INDEX IDX_48CA291DF4BD7827 (assigned_to_id),
    INDEX IDX_48CA291DB03A8386 (created_by_id),
    INDEX IDX_48CA291DB0644AEC (validator_id),
    INDEX IDX_48CA291D85ECDE76 (completed_by_id),
    INDEX IDX_48CA291DC69DE5E5 (validated_by_id),
    INDEX IDX_48CA291D1734B47F (postponed_by_id),
    INDEX idx_routine_task_date_status (scheduled_date, status),
    PRIMARY KEY (id),
    CONSTRAINT FK_48CA291DFCF77503 FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_48CA291D1CEF45B0 FOREIGN KEY (routine_template_id) REFERENCES routine_template (id) ON DELETE RESTRICT,
    CONSTRAINT FK_48CA291D12469DE2 FOREIGN KEY (category_id) REFERENCES routine_category (id) ON DELETE SET NULL,
    CONSTRAINT FK_48CA291DF4BD7827 FOREIGN KEY (assigned_to_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_48CA291DB03A8386 FOREIGN KEY (created_by_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_48CA291DB0644AEC FOREIGN KEY (validator_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_48CA291D85ECDE76 FOREIGN KEY (completed_by_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_48CA291DC69DE5E5 FOREIGN KEY (validated_by_id) REFERENCES user (id) ON DELETE SET NULL,
    CONSTRAINT FK_48CA291D1734B47F FOREIGN KEY (postponed_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS routine_template_task_selection (
    routine_template_id INT NOT NULL,
    routine_task_id INT NOT NULL,
    INDEX IDX_F0CCB55F1CEF45B0 (routine_template_id),
    INDEX IDX_F0CCB55F4D48411E (routine_task_id),
    PRIMARY KEY (routine_template_id, routine_task_id),
    CONSTRAINT FK_F0CCB55F1CEF45B0 FOREIGN KEY (routine_template_id) REFERENCES routine_template (id) ON DELETE CASCADE,
    CONSTRAINT FK_F0CCB55F4D48411E FOREIGN KEY (routine_task_id) REFERENCES routine_task (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS routine_task_checklist_item (
    id INT AUTO_INCREMENT NOT NULL,
    task_id INT NOT NULL,
    completed_by_id INT DEFAULT NULL,
    label_snapshot VARCHAR(255) NOT NULL,
    description_snapshot LONGTEXT DEFAULT NULL,
    position INT NOT NULL,
    required TINYINT(1) NOT NULL,
    completed TINYINT(1) NOT NULL,
    completed_at DATETIME DEFAULT NULL,
    INDEX IDX_8F1B04398DB60186 (task_id),
    INDEX IDX_8F1B043985ECDE76 (completed_by_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_8F1B04398DB60186 FOREIGN KEY (task_id) REFERENCES routine_task (id) ON DELETE CASCADE,
    CONSTRAINT FK_8F1B043985ECDE76 FOREIGN KEY (completed_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS routine_task_event (
    id INT AUTO_INCREMENT NOT NULL,
    task_id INT NOT NULL,
    actor_id INT DEFAULT NULL,
    event_type VARCHAR(50) NOT NULL,
    note LONGTEXT DEFAULT NULL,
    metadata JSON NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX IDX_A57668118DB60186 (task_id),
    INDEX IDX_A576681110DAF24A (actor_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_A57668118DB60186 FOREIGN KEY (task_id) REFERENCES routine_task (id) ON DELETE CASCADE,
    CONSTRAINT FK_A576681110DAF24A FOREIGN KEY (actor_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS routine_generation_log (
    id INT AUTO_INCREMENT NOT NULL,
    societe_id INT NOT NULL,
    generated_by_id INT DEFAULT NULL,
    idtoken VARCHAR(48) NOT NULL,
    period_from DATE NOT NULL,
    period_to DATE NOT NULL,
    dry_run TINYINT(1) NOT NULL,
    routines_analyzed INT NOT NULL,
    tasks_created INT NOT NULL,
    duplicates_ignored INT NOT NULL,
    error_count INT NOT NULL,
    errors JSON NOT NULL,
    created_at DATETIME NOT NULL,
    UNIQUE INDEX UNIQ_2A810E9C8932CD22 (idtoken),
    INDEX IDX_2A810E9CFCF77503 (societe_id),
    INDEX IDX_2A810E9C1BDD81B (generated_by_id),
    PRIMARY KEY (id),
    CONSTRAINT FK_2A810E9CFCF77503 FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE,
    CONSTRAINT FK_2A810E9C1BDD81B FOREIGN KEY (generated_by_id) REFERENCES user (id) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

-- Mise à niveau d'une installation ayant déjà exécuté la première version de RoutineBundle.
ALTER TABLE routine_template
    DROP FOREIGN KEY FK_6974FAB512469DE2,
    MODIFY category_id INT DEFAULT NULL,
    ADD CONSTRAINT FK_6974FAB512469DE2
        FOREIGN KEY (category_id) REFERENCES routine_category (id) ON DELETE SET NULL;

-- #################################################################################################
-- Notifications internes et connexion Google Calendar société (2026-08-05).
CREATE TABLE IF NOT EXISTS notification (id INT AUTO_INCREMENT NOT NULL, destinataire_id INT NOT NULL, societe_id INT DEFAULT NULL, projet_id INT DEFAULT NULL, type VARCHAR(80) NOT NULL, titre VARCHAR(180) NOT NULL, message LONGTEXT NOT NULL, action VARCHAR(100) DEFAULT NULL, target_url VARCHAR(500) DEFAULT NULL, icon VARCHAR(100) DEFAULT NULL, color VARCHAR(30) DEFAULT NULL, target_entity VARCHAR(120) DEFAULT NULL, target_entity_id INT DEFAULT NULL, priority VARCHAR(30) DEFAULT NULL, metadata JSON DEFAULT NULL, lu TINYINT(1) NOT NULL, read_at DATETIME DEFAULT NULL, expires_at DATETIME DEFAULT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX IDX_BF5476CAA4F84F6E (destinataire_id), INDEX IDX_BF5476CAFCF77503 (societe_id), INDEX IDX_BF5476CAC18272 (projet_id), INDEX idx_notification_user_read (destinataire_id, lu, created_at), INDEX idx_notification_company_date (societe_id, created_at), PRIMARY KEY(id), CONSTRAINT FK_NOTIFICATION_USER FOREIGN KEY (destinataire_id) REFERENCES user (id) ON DELETE CASCADE, CONSTRAINT FK_NOTIFICATION_COMPANY FOREIGN KEY (societe_id) REFERENCES societe (id) ON DELETE CASCADE, CONSTRAINT FK_NOTIFICATION_PROJECT FOREIGN KEY (projet_id) REFERENCES projet (id) ON DELETE SET NULL) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;
ALTER TABLE calendar_integration_account ADD authorized_by_id INT DEFAULT NULL, ADD connection_type VARCHAR(20) NOT NULL DEFAULT 'employee', ADD scope_key VARCHAR(80) NOT NULL DEFAULT '', ADD display_name VARCHAR(180) DEFAULT NULL, CHANGE user_id user_id INT DEFAULT NULL, ADD INDEX IDX_8F253F162B62D3A1 (authorized_by_id), ADD CONSTRAINT FK_CALENDAR_ACCOUNT_AUTHORIZED_BY FOREIGN KEY (authorized_by_id) REFERENCES user (id) ON DELETE SET NULL;
UPDATE calendar_integration_account SET authorized_by_id=user_id, scope_key=CONCAT('employee:', user_id) WHERE scope_key='' AND user_id IS NOT NULL;
ALTER TABLE calendar_integration_account DROP INDEX uniq_calendar_account_user_provider, ADD UNIQUE INDEX uniq_calendar_account_scope_provider (societe_id, scope_key, provider);
ALTER TABLE calendar_source CHANGE user_id user_id INT DEFAULT NULL;

-- Normalisation DBAL non destructive des dates Calendar historiques.
ALTER TABLE calendar_sync_job CHANGE available_at available_at DATETIME NOT NULL, CHANGE started_at started_at DATETIME DEFAULT NULL, CHANGE finished_at finished_at DATETIME DEFAULT NULL, CHANGE created_at created_at DATETIME NOT NULL;
ALTER TABLE calendar_sync_log CHANGE created_at created_at DATETIME NOT NULL;
ALTER TABLE calendar_conflict CHANGE resolved_at resolved_at DATETIME DEFAULT NULL, CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL;
ALTER TABLE calendar_event_reminder CHANGE sent_at sent_at DATETIME DEFAULT NULL, CHANGE created_at created_at DATETIME NOT NULL;
ALTER TABLE calendar_event CHANGE starts_at starts_at DATETIME NOT NULL, CHANGE ends_at ends_at DATETIME NOT NULL, CHANGE google_updated_at google_updated_at DATETIME DEFAULT NULL, CHANGE last_synced_at last_synced_at DATETIME DEFAULT NULL, CHANGE last_local_change_at last_local_change_at DATETIME DEFAULT NULL, CHANGE last_remote_change_at last_remote_change_at DATETIME DEFAULT NULL, CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL, CHANGE cancelled_at cancelled_at DATETIME DEFAULT NULL, CHANGE deleted_at deleted_at DATETIME DEFAULT NULL;
