CREATE DATABASE IF NOT EXISTS `whatsappqr`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `whatsappqr`;

CREATE TABLE IF NOT EXISTS `wa_campaigns` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NOT NULL,
  `message_template` TEXT NOT NULL,
  `status` ENUM('draft', 'queued', 'running', 'paused', 'completed', 'cancelled', 'failed') NOT NULL DEFAULT 'draft',
  `started_at` DATETIME NULL,
  `finished_at` DATETIME NULL,
  `contact_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `sent_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `error_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_wa_campaigns_status` (`status`),
  KEY `idx_wa_campaigns_status_created_at` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wa_contacts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `phone_raw` VARCHAR(50) NOT NULL,
  `phone_normalized` VARCHAR(30) NOT NULL,
  `name` VARCHAR(120) NULL,
  `document_id` VARCHAR(60) NULL,
  `custom_fields` JSON NULL,
  `opt_out` TINYINT(1) NOT NULL DEFAULT 0,
  `last_contacted_at` DATETIME NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_wa_contacts_phone_normalized` (`phone_normalized`),
  KEY `idx_wa_contacts_opt_out` (`opt_out`),
  KEY `idx_wa_contacts_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wa_queue` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `campaign_id` BIGINT UNSIGNED NOT NULL,
  `contact_id` BIGINT UNSIGNED NOT NULL,
  `phone_normalized` VARCHAR(30) NOT NULL,
  `message_rendered` TEXT NOT NULL,
  `status` ENUM('pending', 'reserved', 'processing', 'sent', 'delivered', 'read', 'error', 'cancelled') NOT NULL DEFAULT 'pending',
  `attempt_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `max_attempts` INT UNSIGNED NOT NULL DEFAULT 3,
  `wa_message_id` VARCHAR(255) NULL,
  `worker_id` VARCHAR(100) NULL,
  `reserved_at` DATETIME NULL,
  `processing_at` DATETIME NULL,
  `sent_at` DATETIME NULL,
  `delivered_at` DATETIME NULL,
  `read_at` DATETIME NULL,
  `next_attempt_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_error` TEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_wa_queue_campaign_contact` (`campaign_id`, `contact_id`),
  KEY `idx_wa_queue_status_next_attempt` (`status`, `next_attempt_at`),
  KEY `idx_wa_queue_worker_id` (`worker_id`),
  KEY `idx_wa_queue_wa_message_id` (`wa_message_id`),
  KEY `idx_wa_queue_campaign_id` (`campaign_id`),
  CONSTRAINT `fk_wa_queue_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `wa_campaigns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_wa_queue_contact` FOREIGN KEY (`contact_id`) REFERENCES `wa_contacts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wa_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `level` ENUM('debug', 'info', 'warn', 'error') NOT NULL DEFAULT 'info',
  `event_type` VARCHAR(80) NOT NULL,
  `campaign_id` BIGINT UNSIGNED NULL,
  `queue_id` BIGINT UNSIGNED NULL,
  `worker_id` VARCHAR(100) NULL,
  `message` TEXT NOT NULL,
  `context_json` JSON NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_wa_logs_created_at` (`created_at`),
  KEY `idx_wa_logs_event_type` (`event_type`),
  KEY `idx_wa_logs_campaign_id` (`campaign_id`),
  CONSTRAINT `fk_wa_logs_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `wa_campaigns` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_wa_logs_queue` FOREIGN KEY (`queue_id`) REFERENCES `wa_queue` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wa_worker_status` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `worker_id` VARCHAR(100) NOT NULL,
  `status` ENUM('starting', 'qr', 'authenticated', 'ready', 'disconnected', 'auth_failure', 'stopped', 'error') NOT NULL DEFAULT 'starting',
  `is_ready` TINYINT(1) NOT NULL DEFAULT 0,
  `last_heartbeat_at` DATETIME NULL,
  `last_error` TEXT NULL,
  `qr_updated_at` DATETIME NULL,
  `meta_json` JSON NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_wa_worker_status_worker_id` (`worker_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wa_dispatch_config` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `batch_max` INT UNSIGNED NOT NULL DEFAULT 20,
  `message_pause_min_sec` INT UNSIGNED NOT NULL DEFAULT 8,
  `message_pause_max_sec` INT UNSIGNED NOT NULL DEFAULT 35,
  `batch_pause_min_sec` INT UNSIGNED NOT NULL DEFAULT 120,
  `batch_pause_max_sec` INT UNSIGNED NOT NULL DEFAULT 240,
  `max_retries` INT UNSIGNED NOT NULL DEFAULT 3,
  `retry_backoff_json` JSON NOT NULL,
  `allowed_start_time` TIME NOT NULL DEFAULT '08:00:00',
  `allowed_end_time` TIME NOT NULL DEFAULT '19:00:00',
  `stale_reservation_minutes` INT UNSIGNED NOT NULL DEFAULT 20,
  `timezone` VARCHAR(64) NOT NULL DEFAULT 'America/Asuncion',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `wa_dispatch_config`
  (`id`, `is_enabled`, `batch_max`, `message_pause_min_sec`, `message_pause_max_sec`, `batch_pause_min_sec`, `batch_pause_max_sec`, `max_retries`, `retry_backoff_json`, `allowed_start_time`, `allowed_end_time`, `stale_reservation_minutes`, `timezone`)
VALUES
  (1, 1, 20, 8, 35, 120, 240, 3, JSON_ARRAY(300, 900, 3600), '08:00:00', '19:00:00', 20, 'America/Asuncion')
ON DUPLICATE KEY UPDATE
  `is_enabled` = VALUES(`is_enabled`),
  `batch_max` = VALUES(`batch_max`),
  `message_pause_min_sec` = VALUES(`message_pause_min_sec`),
  `message_pause_max_sec` = VALUES(`message_pause_max_sec`),
  `batch_pause_min_sec` = VALUES(`batch_pause_min_sec`),
  `batch_pause_max_sec` = VALUES(`batch_pause_max_sec`),
  `max_retries` = VALUES(`max_retries`),
  `retry_backoff_json` = VALUES(`retry_backoff_json`),
  `allowed_start_time` = VALUES(`allowed_start_time`),
  `allowed_end_time` = VALUES(`allowed_end_time`),
  `stale_reservation_minutes` = VALUES(`stale_reservation_minutes`),
  `timezone` = VALUES(`timezone`);

INSERT INTO `wa_worker_status` (`worker_id`, `status`, `is_ready`, `last_heartbeat_at`, `meta_json`)
VALUES ('worker-main', 'starting', 0, NOW(), JSON_OBJECT())
ON DUPLICATE KEY UPDATE
  `status` = VALUES(`status`),
  `is_ready` = VALUES(`is_ready`),
  `last_heartbeat_at` = VALUES(`last_heartbeat_at`);