-- Notifications (in-app) + per-user settings
-- Run this after the base schema is created.

CREATE TABLE IF NOT EXISTS `notification_settings` (
  `user_id` int(10) UNSIGNED NOT NULL,

  -- Delivery channels (future-proof)
  `in_app_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `email_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `push_enabled` tinyint(1) NOT NULL DEFAULT 0,

  -- Task reminders
  `task_due_today` tinyint(1) NOT NULL DEFAULT 1,
  `task_due_tomorrow` tinyint(1) NOT NULL DEFAULT 1,
  `task_overdue` tinyint(1) NOT NULL DEFAULT 1,

  -- Warranty reminders
  `warranty_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `warranty_days_before` smallint(6) NOT NULL DEFAULT 30,

  -- Maintenance reminders
  `annual_maintenance_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `annual_maintenance_month` tinyint(4) NOT NULL DEFAULT 1,
  `annual_maintenance_day` tinyint(4) NOT NULL DEFAULT 15,

  `missed_recurring_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `missed_grace_days` smallint(6) NOT NULL DEFAULT 7,

  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),

  PRIMARY KEY (`user_id`),
  CONSTRAINT `fk_notif_settings_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `notifications` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED NOT NULL,

  `type` varchar(32) NOT NULL,
  `title` varchar(140) NOT NULL,
  `body` varchar(500) DEFAULT NULL,

  `target_kind` enum('task','system','settings','none') NOT NULL DEFAULT 'none',
  `target_id` bigint(20) UNSIGNED DEFAULT NULL,

  `dedupe_key` varchar(120) NOT NULL,

  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `read_at` timestamp NULL DEFAULT NULL,
  `archived_at` timestamp NULL DEFAULT NULL,

  PRIMARY KEY (`id`),
  KEY `idx_user_created` (`user_id`, `created_at`),
  KEY `idx_user_unread` (`user_id`, `read_at`, `archived_at`),
  UNIQUE KEY `uq_user_dedupe` (`user_id`, `dedupe_key`),

  CONSTRAINT `fk_notifications_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
