-- "Notify me when available" (back in stock) - the same table as migration
-- 2026_10_12_000002_create_product_stock_alerts_table. Run one or the other, not both.
-- Safe to re-run: IF NOT EXISTS.

CREATE TABLE IF NOT EXISTS `product_stock_alerts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint unsigned NOT NULL,
  `variant` varchar(255) NOT NULL DEFAULT '',
  `user_id` bigint unsigned NOT NULL,
  `phone` varchar(32) DEFAULT NULL,
  `message` text,
  `source` varchar(10) NOT NULL DEFAULT 'web',
  `status` varchar(12) NOT NULL DEFAULT 'waiting',
  `attempts` tinyint unsigned NOT NULL DEFAULT '0',
  `last_error` varchar(255) DEFAULT NULL,
  `bulk_group_id` varchar(64) DEFAULT NULL,
  `queued_at` timestamp NULL DEFAULT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  `push_sent_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product_stock_alerts_product_status_index` (`product_id`,`status`),
  KEY `product_stock_alerts_user_status_index` (`user_id`,`status`),
  KEY `product_stock_alerts_status_queued_index` (`status`,`queued_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Record it as run, so `php artisan migrate` later skips the migration:
-- INSERT INTO migrations (migration, batch) SELECT '2026_10_12_000002_create_product_stock_alerts_table', COALESCE(MAX(batch), 0) + 1 FROM migrations;