<?php

class Database {
    private $pdo;

    public function connect() {
        if ($this->pdo == null) {
            try {
                $host = getenv('DB_HOST') ?: "127.0.0.1";
                $db_name = getenv('DB_NAME') ?: "eliazar225_test_db"; // HelioHost often prefixes username
                $username = getenv('DB_USER') ?: "eliazar225_user";
                $password = getenv('DB_PASS') ?: '215da59ab';
                
                // For HelioHost, sometimes localhost is not enough, but usually it is.
                
                $dsn = "mysql:host=$host;dbname=$db_name;charset=utf8mb4";
                
                $this->pdo = new PDO($dsn, $username, $password);
                $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
                
                // Note: On shared hosting (HelioHost), you often create tables via phpMyAdmin.
                // But we can try to create them if the user has permissions.
                $this->initTables();
            } catch (PDOException $e) {
                // Return generic error in production, but specific for debugging now
                echo "Connection error: " . $e->getMessage();
                exit;
            }
        }
        return $this->pdo;
    }

    private function initTables() {
        $queries = [
            "CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(255) UNIQUE NOT NULL,
                email VARCHAR(255) UNIQUE NOT NULL,
                name VARCHAR(255),
                profile_picture VARCHAR(500),
                email_verified BOOLEAN DEFAULT FALSE,
                otp_code VARCHAR(6),
                otp_expiry DATETIME,
                posts_today INT DEFAULT 0,
                last_post_date DATE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                password_hash VARCHAR(255) NOT NULL,
                public_key TEXT,
                backup_blob TEXT
            ) ENGINE=InnoDB",
            
            "CREATE TABLE IF NOT EXISTS friendships (
                user_id INT,
                friend_id INT,
                status VARCHAR(50) DEFAULT 'accepted',
                PRIMARY KEY (user_id, friend_id),
                FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
                FOREIGN KEY(friend_id) REFERENCES users(id) ON DELETE CASCADE
            ) ENGINE=InnoDB",
            
            "CREATE TABLE IF NOT EXISTS posts (
                id INT AUTO_INCREMENT PRIMARY KEY,
                sender_id INT NOT NULL,
                image_url VARCHAR(500) NOT NULL,
                message TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY(sender_id) REFERENCES users(id) ON DELETE CASCADE
            ) ENGINE=InnoDB",
            
            "CREATE TABLE IF NOT EXISTS post_recipients (
                post_id INT,
                recipient_id INT,
				post_backup_blob TEXT NOT NULL,
                PRIMARY KEY (post_id, recipient_id),
                FOREIGN KEY(post_id) REFERENCES posts(id) ON DELETE CASCADE,
                FOREIGN KEY(recipient_id) REFERENCES users(id) ON DELETE CASCADE
            ) ENGINE=InnoDB",
            
            "CREATE TABLE IF NOT EXISTS comments (
                id INT AUTO_INCREMENT PRIMARY KEY,
                post_id INT NOT NULL,
                user_id INT NOT NULL,
                text TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY(post_id) REFERENCES posts(id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
            ) ENGINE=InnoDB",
            
            "CREATE TABLE IF NOT EXISTS likes (
                post_id INT NOT NULL,
                user_id INT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (post_id, user_id),
                FOREIGN KEY(post_id) REFERENCES posts(id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
            ) ENGINE=InnoDB"
        ];

        foreach ($queries as $query) {
            $this->pdo->exec($query);
        }
    }
}
