-- ═══════════════════════════════════════════════════════════════ -- ULUANA — Schéma MySQL -- Base : uluana — encodage utf8mb4_unicode_ci -- ═══════════════════════════════════════════════════════════════ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ─────────────────────────────────────────────────────────────── -- USERS -- ─────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS users ( id VARCHAR(40) NOT NULL PRIMARY KEY, email VARCHAR(190) NOT NULL UNIQUE, firstname VARCHAR(80) NOT NULL DEFAULT '', lastname VARCHAR(80) NOT NULL DEFAULT '', craft VARCHAR(160) NOT NULL DEFAULT '', -- ex: "Céramiste & tournage grès" tagline TEXT NULL, -- phrase d'accroche du profil city VARCHAR(120) NOT NULL DEFAULT '', region VARCHAR(120) NOT NULL DEFAULT '', activity_start_year SMALLINT NULL, -- pour "Depuis 2016" stage ENUM('createur','artisan','artiste') NULL, avatar_url VARCHAR(255) NULL, role ENUM('admin','responsable','client_essentiel','client_premium','prospect') NOT NULL DEFAULT 'prospect', offre ENUM('essentiel','premium') NULL, offre_montant INT UNSIGNED NULL, is_test TINYINT(1) NOT NULL DEFAULT 0, active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_users_role (role), INDEX idx_users_active (active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─────────────────────────────────────────────────────────────── -- PROFIL ARTISAN — données enrichies de la page profil_artisan -- ─────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS artisan_history ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id VARCHAR(40) NOT NULL, year SMALLINT NOT NULL, title VARCHAR(200) NOT NULL, body TEXT NULL, highlight TINYINT(1) NOT NULL DEFAULT 0, sort_order SMALLINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_history_user (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS artisan_specialties ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id VARCHAR(40) NOT NULL, icon VARCHAR(10) NULL, name VARCHAR(160) NOT NULL, detail TEXT NULL, sort_order SMALLINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_specialties_user (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS artisan_gallery ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id VARCHAR(40) NOT NULL, image_url VARCHAR(255) NOT NULL, caption VARCHAR(200) NULL, sort_order SMALLINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_gallery_user (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS artisan_values ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id VARCHAR(40) NOT NULL, name VARCHAR(160) NOT NULL, description TEXT NULL, sort_order SMALLINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_values_user (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─────────────────────────────────────────────────────────────── -- AUDITS — basé sur le référentiel uluana_audit.json -- ─────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS audits ( id VARCHAR(40) NOT NULL PRIMARY KEY, user_id VARCHAR(40) NOT NULL, status ENUM('en_cours','finalise') NOT NULL DEFAULT 'en_cours', note_responsable TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, created_by VARCHAR(40) NULL, -- id du responsable qui a rempli updated_by VARCHAR(40) NULL, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_audits_user (user_id), INDEX idx_audits_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Une ligne par question répondue — permet les allers-retours par step/catégorie CREATE TABLE IF NOT EXISTS audit_answers ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, audit_id VARCHAR(40) NOT NULL, step_id VARCHAR(60) NOT NULL, -- ex: step-fin-1 axis VARCHAR(40) NOT NULL, -- financier | offre | clients | acquisition | marketing | operationnel question_id VARCHAR(80) NOT NULL, -- ex: fin_ca_mensuel value TEXT NULL, -- valeur brute (number/text/choice en texte ; product_table/repartition en JSON) updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (audit_id) REFERENCES audits(id) ON DELETE CASCADE, UNIQUE KEY uniq_audit_question (audit_id, question_id), INDEX idx_answers_audit (audit_id), INDEX idx_answers_axis (axis) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Scores calculés par axe une fois l'audit finalisé CREATE TABLE IF NOT EXISTS audit_scores ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, audit_id VARCHAR(40) NOT NULL, axis VARCHAR(40) NOT NULL, score DECIMAL(5,2) NOT NULL, -- 0 à 100 computed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (audit_id) REFERENCES audits(id) ON DELETE CASCADE, UNIQUE KEY uniq_audit_axis (audit_id, axis), INDEX idx_scores_audit (audit_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─────────────────────────────────────────────────────────────── -- ACTIONS — actions prises par l'artisan (depuis catalogue de cards) -- ─────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS actions ( id VARCHAR(40) NOT NULL PRIMARY KEY, user_id VARCHAR(40) NOT NULL, card_id VARCHAR(20) NOT NULL, -- ex: C-061 (référence au catalogue JSON, pas en base) status ENUM('en_cours','validee','abandonnee') NOT NULL DEFAULT 'en_cours', comment TEXT NULL, -- "comment je vais m'y prendre" rappel_date DATE NULL, rappel_sent TINYINT(1) NOT NULL DEFAULT 0, added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, UNIQUE KEY uniq_user_card (user_id, card_id), INDEX idx_actions_user (user_id), INDEX idx_actions_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─────────────────────────────────────────────────────────────── -- SUIVIS — saisies hebdomadaires -- ─────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS suivis ( id VARCHAR(40) NOT NULL PRIMARY KEY, user_id VARCHAR(40) NOT NULL, date DATE NOT NULL, prospects INT UNSIGNED NULL, devis INT UNSIGNED NULL, signes INT UNSIGNED NULL, ca DECIMAL(10,2) NULL, commandes INT UNSIGNED NULL, clients_actifs INT UNSIGNED NULL, clients_recurrents INT UNSIGNED NULL, churn INT UNSIGNED NULL, taux_conversion DECIMAL(5,2) NULL, -- calculé : signes/devis panier_moyen DECIMAL(10,2) NULL, -- calculé : ca/commandes ressenti TINYINT NULL, -- 1 à 5 besoin_aide TINYINT(1) NOT NULL DEFAULT 0, note_libre TEXT NULL, saved_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, UNIQUE KEY uniq_user_date (user_id, date), INDEX idx_suivis_user (user_id), INDEX idx_suivis_date (date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─────────────────────────────────────────────────────────────── -- RAPPELS — contrôle admin/responsable des relances mail -- ─────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS rappels ( id VARCHAR(40) NOT NULL PRIMARY KEY, user_id VARCHAR(40) NOT NULL, action_id VARCHAR(40) NOT NULL, date_envoi DATE NOT NULL, status ENUM('planifie','envoye','annule') NOT NULL DEFAULT 'planifie', modifie_par VARCHAR(40) NULL, note_admin TEXT NULL, sent_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (action_id) REFERENCES actions(id) ON DELETE CASCADE, INDEX idx_rappels_user (user_id), INDEX idx_rappels_status (status), INDEX idx_rappels_date (date_envoi) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET FOREIGN_KEY_CHECKS = 1; -- ═══════════════════════════════════════════════════════════════ -- CATALOGUE DE CARDS — remplace les fichiers JSON statiques -- ═══════════════════════════════════════════════════════════════ -- ─────────────────────────────────────────────────────────────── -- CATEGORIES -- ─────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS categories ( id VARCHAR(20) NOT NULL PRIMARY KEY, -- ex: CAT-01 slug VARCHAR(80) NOT NULL, label VARCHAR(160) NOT NULL, sort_order SMALLINT NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─────────────────────────────────────────────────────────────── -- CARDS — fiche racine (uluana_catalog.json) -- ─────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS cards ( id VARCHAR(20) NOT NULL PRIMARY KEY, -- ex: C-001 category_id VARCHAR(20) NOT NULL, title VARCHAR(200) NOT NULL, slug VARCHAR(200) NOT NULL, objective TEXT NULL, description TEXT NULL, difficulty TINYINT NOT NULL DEFAULT 1, -- 1 à 5 is_derived TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (category_id) REFERENCES categories(id), INDEX idx_cards_category (category_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tags (relation many-to-many) CREATE TABLE IF NOT EXISTS card_tags ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, card_id VARCHAR(20) NOT NULL, tag VARCHAR(60) NOT NULL, FOREIGN KEY (card_id) REFERENCES cards(id) ON DELETE CASCADE, INDEX idx_tags_card (card_id), INDEX idx_tags_tag (tag) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Cartes associées (relation many-to-many auto-référencée) CREATE TABLE IF NOT EXISTS card_associations ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, card_id VARCHAR(20) NOT NULL, associated_id VARCHAR(20) NOT NULL, sort_order SMALLINT NOT NULL DEFAULT 0, FOREIGN KEY (card_id) REFERENCES cards(id) ON DELETE CASCADE, FOREIGN KEY (associated_id) REFERENCES cards(id) ON DELETE CASCADE, UNIQUE KEY uniq_assoc (card_id, associated_id), INDEX idx_assoc_card (card_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─────────────────────────────────────────────────────────────── -- CARD DETAILS — contenu détaillé (card_details/*.json) -- ─────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS card_detail ( card_id VARCHAR(20) NOT NULL PRIMARY KEY, uluana_label VARCHAR(200) NULL, intro TEXT NULL, updated_at DATE NULL, author VARCHAR(100) NULL, FOREIGN KEY (card_id) REFERENCES cards(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Sections de detail : type method | tips | warning CREATE TABLE IF NOT EXISTS card_detail_sections ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, card_id VARCHAR(20) NOT NULL, type ENUM('method','tips','warning') NOT NULL, title VARCHAR(200) NULL, sort_order SMALLINT NOT NULL DEFAULT 0, FOREIGN KEY (card_id) REFERENCES cards(id) ON DELETE CASCADE, INDEX idx_dsections_card (card_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Étapes (pour type=method) CREATE TABLE IF NOT EXISTS card_detail_steps ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, section_id INT UNSIGNED NOT NULL, step_num SMALLINT NOT NULL, title VARCHAR(200) NOT NULL, content TEXT NOT NULL, FOREIGN KEY (section_id) REFERENCES card_detail_sections(id) ON DELETE CASCADE, INDEX idx_dsteps_section (section_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Items (pour type=tips ou warning) CREATE TABLE IF NOT EXISTS card_detail_items ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, section_id INT UNSIGNED NOT NULL, content TEXT NOT NULL, sort_order SMALLINT NOT NULL DEFAULT 0, FOREIGN KEY (section_id) REFERENCES card_detail_sections(id) ON DELETE CASCADE, INDEX idx_ditems_section (section_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─────────────────────────────────────────────────────────────── -- CARD GUIDES — guides de mise en place (card_guides/*.json) -- ─────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS card_guide ( card_id VARCHAR(20) NOT NULL PRIMARY KEY, titre VARCHAR(200) NOT NULL, -- contexte_uluana situation TEXT NULL, -- parties artisan_apporte TEXT NULL, artisan_gagne TEXT NULL, prospect_profil TEXT NULL, prospect_probleme TEXT NULL, prospect_agir TEXT NULL, ce_qui_les_relie TEXT NULL, -- premier_pas premier_pas_action TEXT NULL, premier_pas_temps VARCHAR(80) NULL, premier_pas_delivrable TEXT NULL, FOREIGN KEY (card_id) REFERENCES cards(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- contexte_uluana.prerequis[] et ce_que_ca_prepare[] CREATE TABLE IF NOT EXISTS card_guide_context_items ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, card_id VARCHAR(20) NOT NULL, type ENUM('prerequis','ce_que_ca_prepare') NOT NULL, content VARCHAR(255) NOT NULL, sort_order SMALLINT NOT NULL DEFAULT 0, FOREIGN KEY (card_id) REFERENCES card_guide(card_id) ON DELETE CASCADE, INDEX idx_gctx_card (card_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- cadrage.questions[] CREATE TABLE IF NOT EXISTS card_guide_questions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, card_id VARCHAR(20) NOT NULL, question_ref VARCHAR(10) NOT NULL, -- ex: Q1 question TEXT NOT NULL, pourquoi TEXT NULL, exemple_flou TEXT NULL, exemple_precis TEXT NULL, options_json TEXT NULL, -- liste d'options si présentes (JSON) sort_order SMALLINT NOT NULL DEFAULT 0, FOREIGN KEY (card_id) REFERENCES card_guide(card_id) ON DELETE CASCADE, INDEX idx_gq_card (card_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- etapes[] CREATE TABLE IF NOT EXISTS card_guide_etapes ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, card_id VARCHAR(20) NOT NULL, numero SMALLINT NOT NULL, titre VARCHAR(200) NOT NULL, description TEXT NULL, exemple TEXT NULL, outil TEXT NULL, FOREIGN KEY (card_id) REFERENCES card_guide(card_id) ON DELETE CASCADE, INDEX idx_getapes_card (card_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- mesure.indicateurs[] CREATE TABLE IF NOT EXISTS card_guide_indicateurs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, card_id VARCHAR(20) NOT NULL, indicateur_ref VARCHAR(10) NOT NULL, -- ex: M1 label VARCHAR(160) NOT NULL, objectif TEXT NULL, comment_mesurer TEXT NULL, FOREIGN KEY (card_id) REFERENCES card_guide(card_id) ON DELETE CASCADE, INDEX idx_gind_card (card_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- liens_cards : avant[] / apres[] / incompatible_si_absent[] CREATE TABLE IF NOT EXISTS card_guide_liens ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, card_id VARCHAR(20) NOT NULL, type ENUM('avant','apres','incompatible_si_absent') NOT NULL, content VARCHAR(255) NOT NULL, -- ex: "C-003 — Construire sa liste email..." sort_order SMALLINT NOT NULL DEFAULT 0, FOREIGN KEY (card_id) REFERENCES card_guide(card_id) ON DELETE CASCADE, INDEX idx_gliens_card (card_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- type_fonctionnel[] (liste de tags fonctionnels, ex: apport_lead) CREATE TABLE IF NOT EXISTS card_guide_types ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, card_id VARCHAR(20) NOT NULL, type_value VARCHAR(60) NOT NULL, FOREIGN KEY (card_id) REFERENCES card_guide(card_id) ON DELETE CASCADE, INDEX idx_gtypes_card (card_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─────────────────────────────────────────────────────────────── -- ARTISAN EXAMPLES — preuves terrain (artisan_examples/*.json) -- ─────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS artisan_examples ( id VARCHAR(20) NOT NULL PRIMARY KEY, -- ex: EX-C001-01 card_id VARCHAR(20) NOT NULL, artisan_first_name VARCHAR(80) NOT NULL, artisan_craft VARCHAR(120) NOT NULL, artisan_city VARCHAR(120) NOT NULL, member_since VARCHAR(10) NULL, -- ex: 2025-01 action_taken TEXT NOT NULL, result TEXT NOT NULL, difficulty_felt TINYINT NOT NULL, -- 1 à 5 quote TEXT NULL, followable TINYINT(1) NOT NULL DEFAULT 0, last_update DATE NULL, status VARCHAR(40) NOT NULL DEFAULT 'action_validee', FOREIGN KEY (card_id) REFERENCES cards(id) ON DELETE CASCADE, INDEX idx_examples_card (card_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ═══════════════════════════════════════════════════════════════ -- QUIZ D'IMPLÉMENTATION — mesure de l'efficacité des actions -- (uluana_quizzes.json + uluana_questions.json) -- ═══════════════════════════════════════════════════════════════ -- ─────────────────────────────────────────────────────────────── -- QUIZ QUESTIONS — référentiel global, partagé entre quizzes -- ─────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS quiz_questions ( id VARCHAR(20) NOT NULL PRIMARY KEY, -- ex: Q-L01 category VARCHAR(60) NOT NULL, -- ex: lancement, exposition, ressenti type ENUM('date','number','text','boolean','single_choice','multi_choice','scale') NOT NULL, label TEXT NOT NULL, hint TEXT NULL, unit VARCHAR(40) NULL, -- ex: "personnes" (pour type=number) min_value INT NULL, -- pour type=number ou scale max_value INT NULL, aggregatable TINYINT(1) NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Options (pour single_choice / multi_choice) CREATE TABLE IF NOT EXISTS quiz_question_options ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, question_id VARCHAR(20) NOT NULL, label VARCHAR(200) NOT NULL, sort_order SMALLINT NOT NULL DEFAULT 0, FOREIGN KEY (question_id) REFERENCES quiz_questions(id) ON DELETE CASCADE, INDEX idx_qopts_question (question_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─────────────────────────────────────────────────────────────── -- QUIZZES — un quiz par carte (card_id), avec ses phases -- ─────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS quizzes ( card_id VARCHAR(20) NOT NULL PRIMARY KEY, min_exposure INT NULL, min_occurrences INT NULL, FOREIGN KEY (card_id) REFERENCES cards(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Phases d'un quiz (1, 2, 3...) avec condition de déverrouillage CREATE TABLE IF NOT EXISTS quiz_phases ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, card_id VARCHAR(20) NOT NULL, phase_num SMALLINT NOT NULL, label VARCHAR(120) NOT NULL, description TEXT NULL, unlock_condition_type VARCHAR(40) NOT NULL, -- immediate | threshold_exposure | combined | after_phase unlock_message TEXT NULL, -- message_waiting affiché si verrouillé unlock_after_phase SMALLINT NULL, -- pour type=after_phase FOREIGN KEY (card_id) REFERENCES quizzes(card_id) ON DELETE CASCADE, UNIQUE KEY uniq_card_phase (card_id, phase_num), INDEX idx_phases_card (card_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Questions rattachées à chaque phase (relation many-to-many ordonnée) CREATE TABLE IF NOT EXISTS quiz_phase_questions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, phase_id INT UNSIGNED NOT NULL, question_id VARCHAR(20) NOT NULL, sort_order SMALLINT NOT NULL DEFAULT 0, FOREIGN KEY (phase_id) REFERENCES quiz_phases(id) ON DELETE CASCADE, FOREIGN KEY (question_id) REFERENCES quiz_questions(id) ON DELETE CASCADE, UNIQUE KEY uniq_phase_question (phase_id, question_id), INDEX idx_pq_phase (phase_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─────────────────────────────────────────────────────────────── -- RÉPONSES DES ARTISANS — remplace le localStorage uluana_quiz_{cardId} -- ─────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS quiz_responses ( id VARCHAR(40) NOT NULL PRIMARY KEY, user_id VARCHAR(40) NOT NULL, card_id VARCHAR(20) NOT NULL, current_phase SMALLINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (card_id) REFERENCES cards(id) ON DELETE CASCADE, UNIQUE KEY uniq_user_card_quiz (user_id, card_id), INDEX idx_qresp_user (user_id), INDEX idx_qresp_card (card_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Une ligne par réponse — permet l'agrégation multi-artisans (aggregatable=1) CREATE TABLE IF NOT EXISTS quiz_answers ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, response_id VARCHAR(40) NOT NULL, question_id VARCHAR(20) NOT NULL, value TEXT NULL, -- texte brut ; multi_choice stocké en JSON updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (response_id) REFERENCES quiz_responses(id) ON DELETE CASCADE, FOREIGN KEY (question_id) REFERENCES quiz_questions(id) ON DELETE CASCADE, UNIQUE KEY uniq_response_question (response_id, question_id), INDEX idx_qans_response (response_id), INDEX idx_qans_question (question_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Marque les phases complétées (équivalent de qAnswers['_phaseN_done'] côté front) CREATE TABLE IF NOT EXISTS quiz_response_phases_done ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, response_id VARCHAR(40) NOT NULL, phase_num SMALLINT NOT NULL, done_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (response_id) REFERENCES quiz_responses(id) ON DELETE CASCADE, UNIQUE KEY uniq_response_phase (response_id, phase_num) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;