<?php
/**
 * Newsletter Database Setup
 * Creates all necessary tables for newsletter functionality
 */

session_start();

// Check if user is admin
if (!isset($_SESSION['user_id']) || $_SESSION['user_role'] !== 'admin') {
    die('Access denied. Admin only.');
}

require_once 'config/db.php';

$conn = $connection;

if ($conn->connect_error) {
    die('Connection failed: ' . $conn->connect_error);
}

// Create newsletter subscribers table
$create_subscribers_table = "CREATE TABLE IF NOT EXISTS newsletter_subscribers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    full_name VARCHAR(100),
    phone VARCHAR(20),
    status ENUM('pending', 'confirmed', 'unsubscribed', 'blocked') DEFAULT 'pending',
    confirmation_token VARCHAR(255),
    confirmation_token_expiry DATETIME,
    confirmed_at DATETIME,
    unsubscribed_at DATETIME,
    subscription_source VARCHAR(100),
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_email (email),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_confirmation_token (confirmation_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

// Create newsletter campaigns table
$create_campaigns_table = "CREATE TABLE IF NOT EXISTS newsletter_campaigns (
    id INT PRIMARY KEY AUTO_INCREMENT,
    admin_id INT NOT NULL,
    campaign_name VARCHAR(255) NOT NULL,
    subject_line VARCHAR(255) NOT NULL,
    preview_text VARCHAR(255),
    email_content LONGTEXT NOT NULL,
    template_type ENUM('service_announcement', 'project_showcase', 'promotion', 'update', 'custom') DEFAULT 'custom',
    status ENUM('draft', 'scheduled', 'sending', 'sent', 'paused', 'cancelled') DEFAULT 'draft',
    scheduled_at DATETIME,
    sent_at DATETIME,
    recipient_count INT DEFAULT 0,
    sent_count INT DEFAULT 0,
    open_count INT DEFAULT 0,
    click_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_admin_id (admin_id),
    INDEX idx_status (status),
    INDEX idx_scheduled_at (scheduled_at),
    INDEX idx_sent_at (sent_at),
    FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

// Create newsletter campaign recipients table
$create_campaign_recipients_table = "CREATE TABLE IF NOT EXISTS newsletter_campaign_recipients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    campaign_id INT NOT NULL,
    subscriber_id INT NOT NULL,
    status ENUM('pending', 'sent', 'failed', 'bounced', 'opened', 'clicked') DEFAULT 'pending',
    sent_at DATETIME,
    opened_at DATETIME,
    clicked_at DATETIME,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_campaign_id (campaign_id),
    INDEX idx_subscriber_id (subscriber_id),
    INDEX idx_status (status),
    UNIQUE KEY unique_campaign_subscriber (campaign_id, subscriber_id),
    FOREIGN KEY (campaign_id) REFERENCES newsletter_campaigns(id) ON DELETE CASCADE,
    FOREIGN KEY (subscriber_id) REFERENCES newsletter_subscribers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

// Create newsletter approval logs table
$create_approval_logs_table = "CREATE TABLE IF NOT EXISTS newsletter_approval_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subscriber_id INT NOT NULL,
    action ENUM('pending', 'confirmed', 'approved', 'rejected', 'unsubscribed') DEFAULT 'pending',
    admin_id INT,
    reason TEXT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_subscriber_id (subscriber_id),
    INDEX idx_admin_id (admin_id),
    INDEX idx_action (action),
    FOREIGN KEY (subscriber_id) REFERENCES newsletter_subscribers(id) ON DELETE CASCADE,
    FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

// Create email queue table
$create_email_queue_table = "CREATE TABLE IF NOT EXISTS newsletter_email_queue (
    id INT PRIMARY KEY AUTO_INCREMENT,
    campaign_id INT,
    subscriber_email VARCHAR(255) NOT NULL,
    subscriber_id INT,
    subject_line VARCHAR(255) NOT NULL,
    email_content LONGTEXT NOT NULL,
    status ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
    retry_count INT DEFAULT 0,
    last_retry_at DATETIME,
    error_message TEXT,
    sent_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_campaign_id (campaign_id),
    INDEX idx_created_at (created_at),
    INDEX idx_subscriber_id (subscriber_id),
    FOREIGN KEY (campaign_id) REFERENCES newsletter_campaigns(id) ON DELETE CASCADE,
    FOREIGN KEY (subscriber_id) REFERENCES newsletter_subscribers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

// Execute table creations
$tables = [
    'newsletter_subscribers' => $create_subscribers_table,
    'newsletter_campaigns' => $create_campaigns_table,
    'newsletter_campaign_recipients' => $create_campaign_recipients_table,
    'newsletter_approval_logs' => $create_approval_logs_table,
    'newsletter_email_queue' => $create_email_queue_table
];

$results = [];
foreach ($tables as $table_name => $sql) {
    if ($conn->query($sql) === TRUE) {
        $results[$table_name] = 'Success';
    } else {
        $results[$table_name] = 'Error: ' . $conn->error;
    }
}

// Return JSON response
header('Content-Type: application/json');
echo json_encode([
    'status' => 'success',
    'message' => 'Newsletter tables setup completed',
    'results' => $results
]);

$conn->close();
?>
