-- ============================================================
-- New Support — WhatsApp Baileys (WsApp) schema
-- Run once on database: newsupport_teste
-- ============================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS `WsApp_clients` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` VARCHAR(64) NOT NULL,
  `p_user_id` INT UNSIGNED NULL,
  `state` VARCHAR(40) NOT NULL DEFAULT 'initializing',
  `authenticated` TINYINT(1) NOT NULL DEFAULT 0,
  `phone_number` VARCHAR(32) NULL,
  `pairing_code` VARCHAR(32) NULL,
  `shared_with` TEXT NULL,
  `share_public` ENUM('yes','no') NOT NULL DEFAULT 'no',
  `last_seen` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_client_id` (`client_id`),
  KEY `idx_user` (`p_user_id`),
  KEY `idx_auth` (`authenticated`),
  KEY `idx_state` (`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `WsApp_qr_codes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `p_user_id` INT UNSIGNED NULL,
  `client_id` VARCHAR(64) NOT NULL,
  `qr_data` MEDIUMTEXT NOT NULL,
  `expired` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_client_expired` (`client_id`, `expired`),
  KEY `idx_user` (`p_user_id`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `WsApp_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `p_user_id` INT UNSIGNED NULL,
  `event_type` VARCHAR(64) NOT NULL,
  `client_id` VARCHAR(64) NULL,
  `data_json` LONGTEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_type_created` (`event_type`, `created_at`),
  KEY `idx_client` (`client_id`),
  KEY `idx_user` (`p_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `WsApp_messages` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `p_user_id` INT UNSIGNED NULL,
  `client_id` VARCHAR(64) NOT NULL,
  `phone` VARCHAR(32) NOT NULL,
  `message` TEXT NULL,
  `image_url` TEXT NULL,
  `device_model` VARCHAR(120) NULL,
  `footer` VARCHAR(255) NULL,
  `buttons_json` TEXT NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending',
  `status_detail` VARCHAR(40) NULL,
  `message_id_wa` VARCHAR(120) NULL,
  `error_detail` TEXT NULL,
  `credit_charged` INT NOT NULL DEFAULT 0,
  `credit_refunded` TINYINT(1) NOT NULL DEFAULT 0,
  `capture_method` VARCHAR(30) NOT NULL DEFAULT 'none',
  `capture_lang` VARCHAR(8) NOT NULL DEFAULT 'pt',
  `capture_context` VARCHAR(30) NOT NULL DEFAULT 'sec',
  `button_clicked` VARCHAR(120) NULL,
  `reply_text` TEXT NULL,
  `sent_at` DATETIME NULL,
  `delivered_at` DATETIME NULL,
  `read_at` DATETIME NULL,
  `clicked_at` DATETIME NULL,
  `reply_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status_client` (`status`, `client_id`),
  KEY `idx_user` (`p_user_id`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `WsApp_inbox` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `p_user_id` INT UNSIGNED NULL,
  `client_id` VARCHAR(64) NOT NULL,
  `phone` VARCHAR(32) NOT NULL,
  `message` TEXT NULL,
  `wa_message_id` VARCHAR(120) NULL,
  `reply_to_msg_id` BIGINT UNSIGNED NULL,
  `msg_type` VARCHAR(40) NULL,
  `button_data` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_client_phone` (`client_id`, `phone`),
  KEY `idx_user` (`p_user_id`),
  KEY `idx_reply` (`reply_to_msg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `WsApp_captures` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `p_user_id` INT UNSIGNED NULL,
  `message_id` BIGINT UNSIGNED NOT NULL,
  `client_id` VARCHAR(64) NOT NULL,
  `phone` VARCHAR(32) NOT NULL,
  `capture_type` VARCHAR(40) NOT NULL,
  `status` VARCHAR(40) NOT NULL DEFAULT 'pending',
  `followup_wa_id` VARCHAR(120) NULL,
  `code_value` VARCHAR(255) NULL,
  `email_value` VARCHAR(255) NULL,
  `pass_value` VARCHAR(255) NULL,
  `extra_json` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_message_id` (`message_id`),
  KEY `idx_client` (`client_id`),
  KEY `idx_user` (`p_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `WsApp_settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT NULL,
  `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `WsApp_settings` (`setting_key`, `setting_value`)
VALUES ('credit_cost_per_msg', '1')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

CREATE TABLE IF NOT EXISTS `WsApp_calls` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `p_user_id` INT UNSIGNED NULL,
  `client_id` VARCHAR(64) NULL,
  `phone` VARCHAR(32) NULL,
  `direction` VARCHAR(20) NULL,
  `status` VARCHAR(40) NULL,
  `duration` INT NULL,
  `meta_json` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`p_user_id`),
  KEY `idx_client` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `whatsapp_sms` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NULL,
  `client_id` VARCHAR(64) NULL,
  `phone` VARCHAR(32) NULL,
  `message` TEXT NULL,
  `status` VARCHAR(40) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `whatsapp_voice` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NULL,
  `p_user_id` INT UNSIGNED NULL,
  `client_id` VARCHAR(64) NULL,
  `phone` VARCHAR(32) NULL,
  `language` VARCHAR(10) NULL DEFAULT 'pt',
  `audio_url` TEXT NULL,
  `script_text` TEXT NULL,
  `status` VARCHAR(40) NOT NULL DEFAULT 'pending',
  `error_detail` TEXT NULL,
  `meta_json` TEXT NULL,
  `sent_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_user` (`user_id`),
  KEY `idx_p_user` (`p_user_id`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_notifications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `category` VARCHAR(60) NOT NULL,
  `event_type` VARCHAR(80) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NULL,
  `severity` VARCHAR(20) NULL DEFAULT 'info',
  `payload` LONGTEXT NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `visitor_ip` VARCHAR(64) NULL,
  `visitor_ua` VARCHAR(255) NULL,
  `telegram_chat_id` VARCHAR(64) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_read` (`user_id`, `is_read`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;