<?php

class Database {
	private static $instance = null;
	private $connection;

	private function __construct() {
		$this->initializeConnection();
		$this->checkAndCreateDatabase();
		$this->checkAndCreateTables();
	}

	/**
	 * Get the singleton instance of the Database
	 * @return Database
	 */
	public static function getInstance() {
		if (self::$instance === null) {
			self::$instance = new Database();
		}

		return self::$instance;
	}

	/**
	 * Get the active database connection
	 * @return mysqli
	 */
	public function getConnection() {
		return $this->connection;
	}

	/**
	 * Initialize the connection to the database
	 */
	private function initializeConnection() {
		$this->connection = new mysqli(DB_HOST, DB_USER, DB_PASS);

		if ($this->connection->connect_error) {
			die("Database connection failed: " . $this->connection->connect_error);
		}
	}

	/**
	 * Check and create the database if it doesn't exist
	 */
	private function checkAndCreateDatabase() {
		$query = "CREATE DATABASE IF NOT EXISTS `" . DB_NAME . "`";
		if (!$this->connection->query($query)) {
			die("Failed to create or access database: " . $this->connection->error);
		}

		$this->connection->select_db(DB_NAME);
	}

	/**
	 * Check and create required tables if they don't exist
	 */
	private function checkAndCreateTables() {
		// Create tables
		$usersTableQuery = "CREATE TABLE IF NOT EXISTS users (
			id INT AUTO_INCREMENT PRIMARY KEY,
			username VARCHAR(50) NOT NULL,
			email VARCHAR(100) NOT NULL UNIQUE,
			password VARCHAR(255) NOT NULL,
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		) ENGINE=InnoDB;";

		if (!$this->connection->query($usersTableQuery)) {
			die("Failed to create users table: " . $this->connection->error);
		}

		$journalTableQuery = "CREATE TABLE IF NOT EXISTS journal (
			id INT AUTO_INCREMENT PRIMARY KEY,
			year INT NOT NULL,
			user_id INT NOT NULL,
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
		) ENGINE=InnoDB;";

		if (!$this->connection->query($usersTableQuery)) {
			die("Failed to create journal table: " . $this->connection->error);
		}

		$challengesTableQuery = "CREATE TABLE IF NOT EXISTS challenges (
			id INT AUTO_INCREMENT PRIMARY KEY,
			journal_id INT NOT NULL,
			booksgoal INT NOT NULL,
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (journal_id) REFERENCES journal(id) ON DELETE CASCADE
		) ENGINE=InnoDB;";

		if (!$this->connection->query($usersTableQuery)) {
			die("Failed to create challenges table: " . $this->connection->error);
		}

		$bookrecordsTableQuery = "CREATE TABLE IF NOT EXISTS bookrecords (
			id INT AUTO_INCREMENT PRIMARY KEY,
			journal_id INT NOT NULL,
			author VARCHAR(128) NOT NULL,
			name VARCHAR(128) NOT NULL,
			page_count INT NOT NULL,
			status ENUM('tbr', 'reading', 'read'),
			favorite BOOLEAN DEFAULT FALSE NOT NULL,
			finished TIMESTAMP NULL,
			genre varchar(32) NOT NULL,
			placement TINYINT NULL CHECK (placement BETWEEN 1 AND 12)
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (journal_id) REFERENCES journal(id) ON DELETE CASCADE
		) ENGINE=InnoDB;";

		if (!$this->connection->query($usersTableQuery)) {
			die("Failed to create bookrecords table: " . $this->connection->error);
		}
	}

	/**
	 * Prevent cloning of the singleton instance
	 */
	private function __clone() {}

	/**
	 * Prevent unserialization of the singleton instance
	 */
	public function __wakeup() {}

}