<?php
require_once 'config.php';

class Database {
    private $connection;
    
    public function __construct() {
        try {
            $this->connection = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASS);
            $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->createTables();
        } catch(PDOException $e) {
            die("Connection failed: " . $e->getMessage());
        }
    }
	
	private function createDefaultCategories() {
    $checkCategories = $this->connection->query("SELECT COUNT(*) FROM categories")->fetchColumn();
    
    if ($checkCategories == 0) {
        $default_categories = [
            ['name' => 'Mental Wellness', 'slug' => 'mental-wellness', 'description' => 'Articles about mental health, mindfulness, and emotional well-being'],
            ['name' => 'Nutrition', 'slug' => 'nutrition', 'description' => 'Healthy eating, diets, and nutritional science'],
            ['name' => 'Physical Health', 'slug' => 'physical-health', 'description' => 'Exercise, fitness, and physical well-being'],
            ['name' => 'Sleep Science', 'slug' => 'sleep-science', 'description' => 'Understanding sleep and improving sleep quality'],
            ['name' => 'Preventive Care', 'slug' => 'preventive-care', 'description' => 'Strategies for preventing illness and maintaining health'],
            ['name' => 'Holistic Living', 'slug' => 'holistic-living', 'description' => 'Integrative approaches to health and wellness']
        ];
        
        foreach ($default_categories as $cat) {
            try {
                $stmt = $this->connection->prepare("INSERT INTO categories (name, slug, description) VALUES (?, ?, ?)");
                $stmt->execute([$cat['name'], $cat['slug'], $cat['description']]);
            } catch (PDOException $e) {
                error_log("Category insertion error: " . $e->getMessage());
            }
        }
    }
}
    
    private function createTables() {
		//
		
		// In the createTables() method, update the categories table creation:
$categoriesTable = "CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

// Update the posts table to include category relationship
$postsTable = "CREATE TABLE IF NOT EXISTS posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    content TEXT NOT NULL,
    excerpt TEXT,
    featured_image VARCHAR(255),
    author_id INT,
    status ENUM('published', 'draft') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL
)";

// Post-categories relationship table
$postCategoriesTable = "CREATE TABLE IF NOT EXISTS post_categories (
    post_id INT,
    category_id INT,
    PRIMARY KEY (post_id, category_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
)";
		//
		
		
    // Users table
    $usersTable = "CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        email VARCHAR(100),
        full_name VARCHAR(100),
        role ENUM('admin', 'author') DEFAULT 'author',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )";
    
    // Posts table
	/*
    $postsTable = "CREATE TABLE IF NOT EXISTS posts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        slug VARCHAR(255) UNIQUE NOT NULL,
        content TEXT NOT NULL,
        excerpt TEXT,
        featured_image VARCHAR(255),
        author_id INT,
        status ENUM('published', 'draft') DEFAULT 'draft',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL
    )";
    
    // Categories table
	
    $categoriesTable = "CREATE TABLE IF NOT EXISTS categories (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        slug VARCHAR(100) UNIQUE NOT NULL,
        description TEXT
    )";
   
    // Post categories relationship table
    $postCategoriesTable = "CREATE TABLE IF NOT EXISTS post_categories (
        post_id INT,
        category_id INT,
        PRIMARY KEY (post_id, category_id),
        FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
        FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
    )";
    */ 
    // Subscribers table - NEW TABLE
    $subscribersTable = "CREATE TABLE IF NOT EXISTS subscribers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        email VARCHAR(255) UNIQUE NOT NULL,
        name VARCHAR(100),
        status ENUM('active', 'inactive', 'unsubscribed') DEFAULT 'active',
        subscription_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        unsubscribe_token VARCHAR(32) UNIQUE,
        confirmed BOOLEAN DEFAULT FALSE,
        confirmation_token VARCHAR(32),
        last_notified TIMESTAMP NULL,
        subscription_source VARCHAR(50) DEFAULT 'website',
        INDEX idx_email (email),
        INDEX idx_status (status),
        INDEX idx_subscription_date (subscription_date)
    )";
    
    $tables = [$usersTable, $postsTable, $categoriesTable, $postCategoriesTable, $subscribersTable];
    
    foreach ($tables as $table) {
        try {
            $this->connection->exec($table);
        } catch (PDOException $e) {
            error_log("Table creation error: " . $e->getMessage());
        }
    }
    
    // Create default admin user if not exists
    $this->createDefaultAdmin();
    
    // Insert default categories if none exist
    $this->createDefaultCategories();
}
    
    private function createDefaultAdmin() {
        $checkAdmin = $this->connection->prepare("SELECT id FROM users WHERE username = ?");
        $checkAdmin->execute([ADMIN_USERNAME]);
        
        if ($checkAdmin->rowCount() == 0) {
            $hashedPassword = password_hash(ADMIN_PASSWORD, PASSWORD_DEFAULT);
            $insertAdmin = $this->connection->prepare("INSERT INTO users (username, password, email, full_name, role) VALUES (?, ?, ?, ?, 'admin')");
            $insertAdmin->execute([ADMIN_USERNAME, $hashedPassword, 'admin@penielhome.com', 'Administrator']);
        }
    }
    
    public function getConnection() {
        return $this->connection;
    }
	
	
	
	
	
	
}

$database = new Database();
$pdo = $database->getConnection();
?>