-- Lifecycle push flows, flow A (App\Services\PushFlows) - the same four tables as migrations
-- 2026_10_26_000001 .. 2026_10_26_000004. Run this file OR `php artisan migrate`, not both.
-- Safe to re-run: IF NOT EXISTS.
--
-- Nothing starts by itself: the master switch push_flows_enabled is OFF until it is switched on
-- (php artisan push-flows:settings push_flows_enabled 1). After running this file:
--   php artisan push-flows:seed-templates      (writes the wording of the marketing sheet, never overwrites an edit)
--   php artisan push-flows:import-legacy --dry-run   (what the old FCM phones would become), then without --dry-run
--   php artisan config:clear                   (new config/push_flows.php; a cached config would read it empty)
--
-- A TIMESTAMP column that is NOT NULL without a default gets ON UPDATE CURRENT_TIMESTAMP on MySQL/MariaDB
-- when explicit_defaults_for_timestamp is off. push_flow_messages.due_at is therefore NULLable on purpose:
-- otherwise every UPDATE of a message would move its due time to "now".

CREATE TABLE IF NOT EXISTS `push_devices` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `temp_user_id` varchar(36) DEFAULT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `channel` varchar(12) NOT NULL DEFAULT 'fcm',
  `legacy_guest_device_token_id` bigint unsigned DEFAULT NULL,
  `legacy_user_id` bigint unsigned DEFAULT NULL,
  `onesignal_id` varchar(64) DEFAULT NULL,
  `subscription_id` varchar(64) DEFAULT NULL,
  `push_token` varchar(512) DEFAULT NULL,
  `platform` varchar(10) DEFAULT NULL,
  `lang` varchar(5) NOT NULL DEFAULT 'ar',
  `app_version` varchar(32) DEFAULT NULL,
  `timezone` varchar(64) DEFAULT NULL,
  `push_enabled` tinyint(1) DEFAULT NULL,
  `cohort` varchar(10) NOT NULL DEFAULT 'new',
  `first_opened_at` timestamp NULL DEFAULT NULL,
  `last_seen_at` timestamp NULL DEFAULT NULL,
  `lifecycle_done_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `push_devices_temp_unique` (`temp_user_id`),
  UNIQUE KEY `push_devices_legacy_unique` (`legacy_guest_device_token_id`),
  KEY `push_devices_user_index` (`user_id`),
  KEY `push_devices_legacy_user_index` (`legacy_user_id`),
  KEY `push_devices_subscription_index` (`subscription_id`),
  KEY `push_devices_cohort_user_index` (`cohort`,`user_id`),
  KEY `push_devices_channel_index` (`channel`,`push_enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `push_flow_runs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `flow` varchar(1) NOT NULL,
  `subject_key` varchar(64) NOT NULL,
  `cycle` smallint unsigned NOT NULL DEFAULT 0,
  `device_id` bigint unsigned DEFAULT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `status` varchar(12) NOT NULL DEFAULT 'active',
  `exit_reason` varchar(40) DEFAULT NULL,
  `cohort` varchar(10) NOT NULL DEFAULT 'new',
  `context` json DEFAULT NULL,
  `entered_at` timestamp NULL DEFAULT NULL,
  `ended_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `push_runs_unique` (`flow`,`subject_key`,`cycle`),
  KEY `push_runs_status_flow_index` (`status`,`flow`),
  KEY `push_runs_device_index` (`device_id`),
  KEY `push_runs_user_index` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `push_flow_messages` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `run_id` bigint unsigned NOT NULL,
  `flow` varchar(1) NOT NULL,
  `step` varchar(2) NOT NULL,
  `subject_key` varchar(64) NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `device_id` bigint unsigned DEFAULT NULL,
  `status` varchar(12) NOT NULL DEFAULT 'scheduled',
  `skip_reason` varchar(60) DEFAULT NULL,
  `channel` varchar(12) DEFAULT NULL,
  `due_at` timestamp NULL DEFAULT NULL,
  `attempts` tinyint unsigned NOT NULL DEFAULT 0,
  `next_attempt_at` timestamp NULL DEFAULT NULL,
  `locked_until` timestamp NULL DEFAULT NULL,
  `claim` char(32) DEFAULT NULL,
  `idempotency_key` char(36) NOT NULL,
  `provider_id` varchar(64) DEFAULT NULL,
  `last_error` varchar(255) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `body` text DEFAULT NULL,
  `link` varchar(500) DEFAULT NULL,
  `analytics_tag` varchar(32) DEFAULT NULL,
  `rendered_lang` char(2) DEFAULT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  `opened_at` timestamp NULL DEFAULT NULL,
  `converted_at` timestamp NULL DEFAULT NULL,
  `converted_order_id` bigint unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `push_msgs_key_unique` (`idempotency_key`),
  UNIQUE KEY `push_msgs_run_step_unique` (`run_id`,`step`),
  KEY `push_msgs_due_index` (`status`,`due_at`),
  KEY `push_msgs_subject_sent_index` (`subject_key`,`sent_at`),
  KEY `push_msgs_user_sent_index` (`user_id`,`sent_at`),
  KEY `push_msgs_device_sent_index` (`device_id`,`sent_at`),
  KEY `push_msgs_flow_step_status_index` (`flow`,`step`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `push_flow_templates` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `flow` varchar(1) NOT NULL,
  `step` varchar(2) NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `title_ar` varchar(255) NOT NULL,
  `body_ar` text NOT NULL,
  `title_en` varchar(255) DEFAULT NULL,
  `body_en` text DEFAULT NULL,
  `screen` varchar(30) NOT NULL,
  `link` varchar(500) NOT NULL,
  `analytics_tag` varchar(32) NOT NULL,
  `image_source` varchar(20) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `edited_by` bigint unsigned DEFAULT NULL,
  `edited_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `push_templates_unique` (`flow`,`step`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Record the migrations as run, so `php artisan migrate` later skips them:
-- INSERT INTO migrations (migration, batch) SELECT m.migration, COALESCE((SELECT MAX(batch) FROM migrations), 0) + 1
--   FROM (SELECT '2026_10_26_000001_create_push_devices_table' AS migration
--         UNION ALL SELECT '2026_10_26_000002_create_push_flow_runs_table'
--         UNION ALL SELECT '2026_10_26_000003_create_push_flow_messages_table'
--         UNION ALL SELECT '2026_10_26_000004_create_push_flow_templates_table') m
--   WHERE m.migration NOT IN (SELECT migration FROM migrations);