-- =============================================================================
-- Medical ERP Database Configuration Script
-- For MySQL/MariaDB servers
-- =============================================================================

-- Create database if it doesn't exist
CREATE DATABASE IF NOT EXISTS medical_erp_production 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- Create user for the application (change username and password)
CREATE USER IF NOT EXISTS 'medical_erp_user'@'localhost' IDENTIFIED BY 'your_secure_password_here';

-- Grant necessary privileges
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP, REFERENCES 
ON medical_erp_production.* 
TO 'medical_erp_user'@'localhost';

-- Grant additional privileges for migrations and schema updates
GRANT CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE 
ON medical_erp_production.* 
TO 'medical_erp_user'@'localhost';

-- Flush privileges to apply changes
FLUSH PRIVILEGES;

-- Use the created database
USE medical_erp_production;

-- Set timezone (adjust according to your server location)
SET time_zone = '+00:00';

-- Configure MySQL settings for better performance
SET GLOBAL innodb_buffer_pool_size = 128M;
SET GLOBAL max_connections = 200;
SET GLOBAL wait_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;

-- Create initial admin user (will be created by the application)
-- This is just for reference - the application will handle user creation

-- Display configuration summary
SELECT 
    'Database Configuration Complete' as Status,
    'medical_erp_production' as Database_Name,
    'medical_erp_user' as Database_User,
    @@version as MySQL_Version,
    @@time_zone as Timezone;

-- =============================================================================
-- Instructions for manual setup:
-- =============================================================================
-- 1. Login to MySQL as root: mysql -u root -p
-- 2. Run this script: source database_config.sql
-- 3. Update server/.env.production with the correct database credentials
-- 4. Run the installation script: ./install_server.sh
-- =============================================================================