-- ============================================================================
-- EMAIL WARMUP SYSTEM DATABASE TABLES - 22 ACCOUNT VERSION
-- Updated to track provider info (gmail, yahoo, namecheap)
-- ============================================================================

-- Table to track which emails we've already replied to
CREATE TABLE IF NOT EXISTS `warmup_replies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message_id` varchar(255) NOT NULL,
  `recipient_email` varchar(255) NOT NULL,
  `status` enum('sent','skipped') NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_message` (`message_id`),
  KEY `idx_recipient` (`recipient_email`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table to queue replies (sent with delay to look natural)
CREATE TABLE IF NOT EXISTS `warmup_reply_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message_id` varchar(255) NOT NULL,
  `recipient_email` varchar(255) NOT NULL COMMENT 'The test email account',
  `recipient_name` varchar(255) NOT NULL,
  `reply_to_email` varchar(255) NOT NULL COMMENT 'HSLG sender email',
  `reply_to_name` varchar(255) NOT NULL,
  `original_subject` varchar(500) NOT NULL,
  `scheduled_time` datetime NOT NULL COMMENT 'When to send the reply',
  `provider` enum('gmail','yahoo','namecheap','other') NOT NULL DEFAULT 'other',
  `status` enum('pending','sending','sent','failed') NOT NULL DEFAULT 'pending',
  `attempts` int(11) NOT NULL DEFAULT 0,
  `sent_at` datetime DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status_scheduled` (`status`, `scheduled_time`),
  KEY `idx_recipient` (`recipient_email`),
  KEY `idx_provider` (`provider`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table to track warmup statistics by provider
CREATE TABLE IF NOT EXISTS `warmup_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `provider` enum('gmail','yahoo','namecheap','all') NOT NULL DEFAULT 'all',
  `test_account` varchar(255) DEFAULT NULL COMMENT 'Specific account or NULL for aggregate',
  `emails_received` int(11) NOT NULL DEFAULT 0,
  `emails_opened` int(11) NOT NULL DEFAULT 0,
  `links_clicked` int(11) NOT NULL DEFAULT 0,
  `replies_sent` int(11) 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`),
  UNIQUE KEY `unique_date_provider_account` (`date`, `provider`, `test_account`),
  KEY `idx_date` (`date`),
  KEY `idx_provider` (`provider`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- HELPER VIEWS FOR MONITORING
-- ============================================================================

-- View to see warmup performance by provider
CREATE OR REPLACE VIEW warmup_provider_performance AS
SELECT 
    provider,
    COUNT(*) as total_emails,
    SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END) as replies_sent,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as replies_failed,
    ROUND(SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as reply_rate_pct,
    DATE(created_at) as date
FROM warmup_reply_queue
GROUP BY provider, DATE(created_at)
ORDER BY date DESC, provider;

-- View to see pending replies by provider
CREATE OR REPLACE VIEW warmup_pending_by_provider AS
SELECT 
    provider,
    COUNT(*) as pending_count,
    MIN(scheduled_time) as next_scheduled,
    MAX(scheduled_time) as last_scheduled
FROM warmup_reply_queue
WHERE status = 'pending'
GROUP BY provider;

-- ============================================================================
-- SAMPLE QUERIES FOR MONITORING
-- ============================================================================

-- Check overall warmup activity
-- SELECT * FROM warmup_provider_performance ORDER BY date DESC LIMIT 7;

-- See what's pending
-- SELECT * FROM warmup_pending_by_provider;

-- Check Gmail performance specifically
-- SELECT * FROM warmup_reply_queue WHERE provider = 'gmail' ORDER BY created_at DESC LIMIT 10;

-- Daily summary
-- SELECT 
--     DATE(created_at) as date,
--     provider,
--     COUNT(*) as emails,
--     SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END) as sent
-- FROM warmup_reply_queue
-- GROUP BY DATE(created_at), provider
-- ORDER BY date DESC;

-- ============================================================================
-- VERIFY TABLES CREATED
-- ============================================================================

SELECT 'Warmup Tables Created Successfully!' as status;
SHOW TABLES LIKE 'warmup%';