-- customer_voices: 店舗による「お客様の声」（店舗投稿型口コミ）
-- 冪等。phpMyAdmin で実行可。
-- IMPORTANT: 公開時は必ず「店舗による投稿（お客様アンケート転載）」バッジを表示する前提。
-- reviews とは完全に別テーブル。★平均は別集計。

CREATE TABLE IF NOT EXISTS `customer_voices` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id` INT UNSIGNED NOT NULL,
  `customer_label` VARCHAR(64) NOT NULL,
  `rating` TINYINT UNSIGNED NOT NULL,
  `title` VARCHAR(191) NOT NULL,
  `body` TEXT NOT NULL,
  `received_at` DATE NULL,
  `survey_note` TEXT NULL,
  `consent_confirmed` TINYINT(1) NOT NULL DEFAULT 0,
  `status` ENUM('pending','published','rejected') NOT NULL DEFAULT 'pending',
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_voices_shop_id_index` (`shop_id`),
  KEY `customer_voices_shop_id_status_created_at_index` (`shop_id`,`status`,`created_at`),
  KEY `customer_voices_status_created_at_index` (`status`,`created_at`),
  CONSTRAINT `customer_voices_shop_id_foreign` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- プラン特徴：standard=最大3件、premium=無制限（JSON null）
-- JSON_SET の値引数に SQL NULL を渡すと JSON null として格納される（MariaDB/MySQL共通）。
UPDATE `plans` SET `features` = JSON_SET(`features`, '$.customer_voice', true, '$.customer_voice_limit', 3) WHERE `code` = 'standard';
UPDATE `plans` SET `features` = JSON_SET(`features`, '$.customer_voice', true, '$.customer_voice_limit', NULL) WHERE `code` = 'premium';

-- migrations 記録（再ラン防止）
INSERT IGNORE INTO `migrations` (`migration`,`batch`)
SELECT '2026_05_21_000001_create_customer_voices_table', COALESCE(MAX(`batch`),0)+1 FROM `migrations`;