-- BuyerKiosk Database Initialization
-- Creates the multi-database structure required by the application

-- Create central databases
CREATE DATABASE IF NOT EXISTS `kiosk_buykiosk` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS `kiosk_users` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS `kiosk_buys` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS `contactNumbers` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create test database
CREATE DATABASE IF NOT EXISTS `kiosk_test` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create sample store databases (add more as needed)
CREATE DATABASE IF NOT EXISTS `kiosk_ou00` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS `kiosk_pa00` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Grant privileges to the application user
GRANT ALL PRIVILEGES ON `kiosk_buykiosk`.* TO 'kiosk_db'@'%';
GRANT ALL PRIVILEGES ON `kiosk_users`.* TO 'kiosk_db'@'%';
GRANT ALL PRIVILEGES ON `kiosk_buys`.* TO 'kiosk_db'@'%';
GRANT ALL PRIVILEGES ON `contactNumbers`.* TO 'kiosk_db'@'%';
GRANT ALL PRIVILEGES ON `kiosk_test`.* TO 'kiosk_db'@'%';
GRANT ALL PRIVILEGES ON `kiosk_ou00`.* TO 'kiosk_db'@'%';
GRANT ALL PRIVILEGES ON `kiosk_pa00`.* TO 'kiosk_db'@'%';

-- Allow creating new databases (for store provisioning)
GRANT CREATE ON *.* TO 'kiosk_db'@'%';

FLUSH PRIVILEGES;

-- Note: Actual table migrations will be run via:
--   docker-compose exec web php userfrosting/conductor run
