-- ConnectWise Sync – MySQL schema (all sync data in dedicated tables)
-- Run this in phpMyAdmin or mysql CLI after creating the database.

-- ---------------------------------------------------------------------------
-- Auth
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(255) DEFAULT NULL,
  `created_at` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tokens` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `token` VARCHAR(64) NOT NULL,
  `expires_at` INT UNSIGNED NOT NULL,
  `created_at` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `user_id` (`user_id`),
  KEY `expires_at` (`expires_at`),
  CONSTRAINT `tokens_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Configuration (friend request, messaging, scheduling, safety, settings)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sync_config` (
  `user_id` INT UNSIGNED NOT NULL,
  `friend_request` JSON DEFAULT NULL,
  `messaging` JSON DEFAULT NULL,
  `scheduling` JSON DEFAULT NULL,
  `safety` JSON DEFAULT NULL,
  `settings` JSON DEFAULT NULL,
  `settings_initialized` TINYINT(1) NOT NULL DEFAULT 0,
  `synced_at` INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `sync_config_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Message templates – segments and groups
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sync_segments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `segment_id` VARCHAR(64) NOT NULL,
  `title` VARCHAR(255) DEFAULT NULL,
  `message` TEXT,
  `sort_order` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `sync_segments_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sync_groups` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `group_id` VARCHAR(64) NOT NULL,
  `title` VARCHAR(255) DEFAULT NULL,
  `items` JSON DEFAULT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `sync_groups_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Automation state and counters
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sync_automation_state` (
  `user_id` INT UNSIGNED NOT NULL,
  `status` VARCHAR(32) NOT NULL DEFAULT 'stopped',
  `friend_requests_sent` INT UNSIGNED NOT NULL DEFAULT 0,
  `messages_sent` INT UNSIGNED NOT NULL DEFAULT 0,
  `session_friend_requests` INT UNSIGNED NOT NULL DEFAULT 0,
  `session_messages` INT UNSIGNED NOT NULL DEFAULT 0,
  `errors` INT UNSIGNED NOT NULL DEFAULT 0,
  `last_reset_date` VARCHAR(64) DEFAULT NULL,
  `session_start_time` INT UNSIGNED DEFAULT NULL,
  `synced_at` INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `sync_automation_state_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Activity logs
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sync_activity_log` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `timestamp` VARCHAR(32) NOT NULL,
  `type` VARCHAR(32) DEFAULT NULL,
  `message` TEXT,
  `data` JSON DEFAULT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `sync_activity_log_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Pending friend requests with message tracking
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sync_pending_friend_requests` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `url` VARCHAR(512) DEFAULT NULL,
  `name` VARCHAR(255) DEFAULT NULL,
  `profile_id` VARCHAR(64) DEFAULT NULL,
  `sent_at` INT UNSIGNED NOT NULL DEFAULT 0,
  `message_sent` TINYINT(1) NOT NULL DEFAULT 0,
  `message_sent_at` INT UNSIGNED DEFAULT NULL,
  `stored_message` TEXT,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `sync_pending_friend_requests_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Position / resume data for continuing scans
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sync_position_resume` (
  `user_id` INT UNSIGNED NOT NULL,
  `last_search_position` INT UNSIGNED NOT NULL DEFAULT 0,
  `last_search_url` VARCHAR(512) DEFAULT NULL,
  `synced_at` INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `sync_position_resume_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Pending message retry (temporary message queues)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sync_pending_message_retry` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `profile_id` VARCHAR(64) DEFAULT NULL,
  `profile_name` VARCHAR(255) DEFAULT NULL,
  `profile_url` VARCHAR(512) DEFAULT NULL,
  `trigger` VARCHAR(64) DEFAULT NULL,
  `stored_message` TEXT,
  `attempts` INT UNSIGNED NOT NULL DEFAULT 0,
  `last_attempt` INT UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `sync_pending_message_retry_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Tracking sets (prevent duplicate actions)
-- acceptedNotificationsMessageSent, incomingRequestsTracked, acceptedRequestsTracked, declinedRequestsTracked, etc.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sync_tracking_sets` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `set_key` VARCHAR(64) NOT NULL,
  `set_value` JSON DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_set` (`user_id`, `set_key`),
  CONSTRAINT `sync_tracking_sets_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Misc key-value (tab management IDs, lastMessageSentTo, migrations, etc.)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sync_misc` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `misc_key` VARCHAR(64) NOT NULL,
  `misc_value` JSON DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_key` (`user_id`, `misc_key`),
  CONSTRAINT `sync_misc_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Full payload backup (optional; used if you want one-row restore)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sync_data` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `payload` LONGTEXT NOT NULL,
  `synced_at` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  CONSTRAINT `sync_data_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
