-- ========================================
-- ADDITIONAL MISSING TABLES
-- ========================================
-- These tables are used in code but missing from import.sql
-- ========================================

USE `whatscrm`;

-- ========================================
-- 1. beta_api_messages TABLE
-- ========================================
-- Used in: functions/apiMessages.js
-- Purpose: Track API message status
-- ========================================
CREATE TABLE IF NOT EXISTS `beta_api_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(999) DEFAULT NULL,
  `meta_msg_id` varchar(999) DEFAULT NULL,
  `status` varchar(999) DEFAULT 'pending',
  `recipient` varchar(999) DEFAULT NULL,
  `message_type` varchar(999) DEFAULT NULL,
  `request_data` longtext DEFAULT NULL,
  `response_data` longtext DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_meta_msg_id` (`meta_msg_id`(100)),
  KEY `idx_uid` (`uid`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 2. beta_api_analytics TABLE
-- ========================================
-- Used in: functions/apiMessages.js
-- Purpose: Daily API message analytics
-- ========================================
CREATE TABLE IF NOT EXISTS `beta_api_analytics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(999) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `messages_sent` int(11) DEFAULT 0,
  `messages_delivered` int(11) DEFAULT 0,
  `messages_read` int(11) DEFAULT 0,
  `messages_failed` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_uid_date` (`uid`(100),`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 3. plan_addons TABLE (Optional - if used)
-- ========================================
-- Purpose: Store addon features for plans
-- ========================================
-- Uncomment if your code uses this table
-- CREATE TABLE IF NOT EXISTS `plan_addons` (
--   `id` int(11) NOT NULL AUTO_INCREMENT,
--   `plan_id` int(11) DEFAULT NULL,
--   `addon_name` varchar(999) DEFAULT NULL,
--   `addon_features` longtext DEFAULT NULL,
--   `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
--   PRIMARY KEY (`id`),
--   KEY `idx_plan_id` (`plan_id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;