JezK
Edit File: setup_db.php
<?php $host = '127.0.0.1'; $user = 'root'; $pass = ''; try { $pdo = new PDO("mysql:host=$host", $user, $pass); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Create Database $pdo->exec("CREATE DATABASE IF NOT EXISTS humara_notes"); echo "Database created successfully.<br>"; // Select Database $pdo->exec("USE humara_notes"); // Create Users Table $sqlUsers = "CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP )"; $pdo->exec($sqlUsers); // Create Notes Table // Added public_access column: 'none', 'view', 'edit' $sqlNotes = "CREATE TABLE IF NOT EXISTS notes ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, title VARCHAR(255) NOT NULL, content TEXT, share_token VARCHAR(255) UNIQUE DEFAULT NULL, public_access ENUM('none', 'view', 'edit') DEFAULT 'none', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE )"; $pdo->exec($sqlNotes); // Create checklist_items table $pdo->exec("CREATE TABLE IF NOT EXISTS checklist_items ( id INT AUTO_INCREMENT PRIMARY KEY, note_id INT NOT NULL, content TEXT, is_completed TINYINT(1) DEFAULT 0, status ENUM('pending', 'in_progress', 'completed') DEFAULT 'pending', deadline DATETIME NULL, FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE )"); // Migration for existing tables: Check if columns exist, if not add them try { $pdo->query("SELECT status FROM checklist_items LIMIT 1"); } catch (Exception $e) { $pdo->exec("ALTER TABLE checklist_items ADD COLUMN status ENUM('pending', 'in_progress', 'completed') DEFAULT 'pending'"); } try { $pdo->query("SELECT deadline FROM checklist_items LIMIT 1"); } catch (Exception $e) { $pdo->exec("ALTER TABLE checklist_items ADD COLUMN deadline DATETIME NULL"); } // Create Note Shares Table (for direct user collaboration) $sqlShares = "CREATE TABLE IF NOT EXISTS note_shares ( id INT AUTO_INCREMENT PRIMARY KEY, note_id INT NOT NULL, email VARCHAR(100) NOT NULL, access_level ENUM('view', 'edit') DEFAULT 'view', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE, UNIQUE KEY unique_share (note_id, email) )"; $pdo->exec($sqlShares); // Check if columns exist (for updates on existing DB) try { $pdo->exec("ALTER TABLE notes ADD COLUMN public_access ENUM('none', 'view', 'edit') DEFAULT 'none'"); } catch (Exception $e) { /* Column likely exists */ } echo "Tables updated/created successfully.<br>"; } catch (PDOException $e) { die("DB ERROR: " . $e->getMessage()); } ?>