-- ============================================================================
-- Newsletter / Email Subscription Module — Gmind Institute
-- ============================================================================
-- Run this ENTIRE SQL in phpMyAdmin (on database advaysin_gmind or gmind_institute)
-- ============================================================================

-- 1. NEWSLETTER SUBSCRIBERS TABLE
CREATE TABLE IF NOT EXISTS `newsletter_subscribers` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NOT NULL,
    `name` VARCHAR(150) DEFAULT NULL,
    `student_id` INT UNSIGNED DEFAULT NULL,
    `status` ENUM('active', 'unsubscribed', 'bounced') NOT NULL DEFAULT 'active',
    `verification_token` VARCHAR(64) DEFAULT NULL,
    `is_verified` TINYINT(1) NOT NULL DEFAULT 1,
    `source` VARCHAR(50) DEFAULT 'homepage',
    `subscribed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `unsubscribed_at` TIMESTAMP NULL DEFAULT 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 `uk_newsletter_email` (`email`),
    INDEX `idx_newsletter_status` (`status`, `subscribed_at`),
    INDEX `idx_student_id` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. NEWSLETTER EMAIL LOG TABLE (tracks sent emails)
CREATE TABLE IF NOT EXISTS `newsletter_emails` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `subject` VARCHAR(300) NOT NULL,
    `body_html` MEDIUMTEXT DEFAULT NULL,
    `body_text` MEDIUMTEXT DEFAULT NULL,
    `recipient_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `sent_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `failed_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` ENUM('draft', 'sending', 'sent', 'failed') NOT NULL DEFAULT 'draft',
    `sent_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_newsletter_email_status` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. NEWSLETTER SETTINGS (insert into settings table)
-- Uses BOTH column naming conventions (setting_key/setting_value AND key_name/value)
INSERT INTO `settings` (`setting_key`, `setting_value`, `key_name`, `value`) VALUES
('newsletter_enabled', '1', 'newsletter_enabled', '1'),
('newsletter_title', 'Stay Updated with Gmind Institute', 'newsletter_title', 'Stay Updated with Gmind Institute'),
('newsletter_subtitle', 'Subscribe to get latest updates on new courses, batch schedules, and exclusive offers directly in your inbox.', 'newsletter_subtitle', 'Subscribe to get latest updates on new courses, batch schedules, and exclusive offers directly in your inbox.'),
('newsletter_button_text', 'Subscribe Now', 'newsletter_button_text', 'Subscribe Now'),
('newsletter_success_message', 'Thank you for subscribing! You will receive updates from Gmind Institute.', 'newsletter_success_message', 'Thank you for subscribing! You will receive updates from Gmind Institute.')
ON DUPLICATE KEY UPDATE `setting_value` = VALUES(`setting_value`), `value` = VALUES(`value`);
