-- Moodle Database Setup SQL
-- Run this as MySQL root user

-- Drop existing database if it exists
DROP DATABASE IF EXISTS moodle;

-- Create fresh database with proper charset
CREATE DATABASE moodle DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create/update database user
DROP USER IF EXISTS 'lms'@'localhost';
CREATE USER 'lms'@'localhost' IDENTIFIED BY 'student_staff';

-- Grant all privileges to the user
GRANT ALL PRIVILEGES ON moodle.* TO 'lms'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

-- Show databases to confirm
SHOW DATABASES;

-- Show user privileges
SHOW GRANTS FOR 'lms'@'localhost';