243 lines
10 KiB
JavaScript
Executable File
243 lines
10 KiB
JavaScript
Executable File
// Database Initialization Script
|
|
// Run this to automatically create all tables in MySQL
|
|
import mysql from 'mysql2/promise';
|
|
import dotenv from 'dotenv';
|
|
import { fileURLToPath } from 'url';
|
|
import path from 'path';
|
|
|
|
const __filename = fileURLToPath(import.meta.url);
|
|
const __dirname = path.dirname(__filename);
|
|
|
|
dotenv.config({ path: path.join(__dirname, '../.env') });
|
|
|
|
const schema = `
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
identifier VARCHAR(50) UNIQUE,
|
|
password VARCHAR(255) NOT NULL,
|
|
role ENUM('ADMIN', 'GURU', 'SEKRETARIS') NOT NULL DEFAULT 'GURU',
|
|
class_name VARCHAR(20) NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_identifier (identifier),
|
|
INDEX idx_role (role)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS teachers (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
nip VARCHAR(30) UNIQUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_nip (nip),
|
|
INDEX idx_name (name)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS students (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
nis VARCHAR(20) UNIQUE,
|
|
class_name VARCHAR(20) NOT NULL,
|
|
gender ENUM('L', 'P') NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_nis (nis),
|
|
INDEX idx_class (class_name),
|
|
INDEX idx_name (name)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS subjects (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL UNIQUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_name (name)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS journal_entries (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
journal_id VARCHAR(50) UNIQUE NOT NULL,
|
|
date DATE NOT NULL,
|
|
teacher_name VARCHAR(100) NOT NULL,
|
|
nip VARCHAR(30),
|
|
class_name VARCHAR(20) NOT NULL,
|
|
subject VARCHAR(100) NOT NULL,
|
|
topic TEXT,
|
|
start_time VARCHAR(10),
|
|
end_time VARCHAR(10),
|
|
students_present INT DEFAULT 0,
|
|
students_absent INT DEFAULT 0,
|
|
sakit INT DEFAULT 0,
|
|
izin INT DEFAULT 0,
|
|
alpha INT DEFAULT 0,
|
|
dispen INT DEFAULT 0,
|
|
notes TEXT,
|
|
photo_url VARCHAR(500),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_journal_id (journal_id),
|
|
INDEX idx_date (date),
|
|
INDEX idx_teacher (teacher_name),
|
|
INDEX idx_class (class_name)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS class_journal_entries (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
journal_id VARCHAR(50) UNIQUE NOT NULL,
|
|
date DATE NOT NULL,
|
|
teacher_name VARCHAR(100) NOT NULL,
|
|
subject VARCHAR(100) NOT NULL,
|
|
class_name VARCHAR(20) NOT NULL,
|
|
teacher_presence ENUM('Hadir', 'Tugas', 'Tidak Hadir') DEFAULT 'Hadir',
|
|
start_time VARCHAR(10),
|
|
end_time VARCHAR(10),
|
|
students_present INT DEFAULT 0,
|
|
students_absent INT DEFAULT 0,
|
|
sakit INT DEFAULT 0,
|
|
izin INT DEFAULT 0,
|
|
alpha INT DEFAULT 0,
|
|
dispen INT DEFAULT 0,
|
|
notes TEXT,
|
|
class_secretary VARCHAR(100),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_journal_id (journal_id),
|
|
INDEX idx_date (date),
|
|
INDEX idx_class (class_name)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS attendance_details (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
journal_type ENUM('teacher', 'class') NOT NULL,
|
|
journal_id VARCHAR(50) NOT NULL,
|
|
student_name VARCHAR(100) NOT NULL,
|
|
status ENUM('H', 'S', 'I', 'A', 'D') NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_journal (journal_type, journal_id),
|
|
INDEX idx_student (student_name)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS school_settings (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
setting_key VARCHAR(50) UNIQUE NOT NULL,
|
|
setting_value TEXT,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB;
|
|
`;
|
|
|
|
async function initDatabase() {
|
|
console.log('╔═══════════════════════════════════════════════════════════╗');
|
|
console.log('║ 🗄️ Database Initialization Script ║');
|
|
console.log('╠═══════════════════════════════════════════════════════════╣');
|
|
|
|
const dbHost = process.env.DB_HOST || 'localhost';
|
|
const dbUser = process.env.DB_USER || 'smanabid';
|
|
const dbPass = process.env.DB_PASSWORD || '';
|
|
const dbPort = process.env.DB_PORT || 3306;
|
|
const dbName = process.env.DB_NAME || 'smanabid_db_jurnal';
|
|
|
|
console.log(`║ Host: ${dbHost}:${dbPort}`);
|
|
console.log(`║ User: ${dbUser}`);
|
|
console.log(`║ Database: ${dbName}`);
|
|
console.log('╠═══════════════════════════════════════════════════════════╣');
|
|
|
|
let connection;
|
|
|
|
try {
|
|
console.log('║ Connecting to MySQL server... ║');
|
|
connection = await mysql.createConnection({
|
|
host: dbHost,
|
|
user: dbUser,
|
|
password: dbPass,
|
|
port: dbPort,
|
|
multipleStatements: true
|
|
});
|
|
console.log('║ ✅ Connected to MySQL server ║');
|
|
|
|
console.log(`║ Creating database: ${dbName}...`);
|
|
await connection.query(`CREATE DATABASE IF NOT EXISTS \`${dbName}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
|
|
console.log('║ ✅ Database created/verified ║');
|
|
|
|
await connection.query(`USE \`${dbName}\``);
|
|
console.log('║ ✅ Using database ║');
|
|
|
|
console.log('║ Creating tables... ║');
|
|
await connection.query(schema);
|
|
console.log('║ ✅ All tables created successfully ║');
|
|
|
|
// Insert seed data
|
|
console.log('║ Inserting seed data... ║');
|
|
|
|
// Default settings
|
|
await connection.query(`
|
|
INSERT IGNORE INTO school_settings (setting_key, setting_value) VALUES
|
|
('semester', 'Ganjil'), ('academic_year', '2025/2026'),
|
|
('headmaster_name', ''), ('headmaster_nip', ''),
|
|
('logo_url', ''), ('kop_url', '')
|
|
`);
|
|
|
|
// Admin user
|
|
await connection.query(`
|
|
INSERT IGNORE INTO users (name, identifier, password, role) VALUES
|
|
('Administrator', 'admin', 'admin123', 'ADMIN'),
|
|
('Smanab Admin', 'AdminJurnal#1', 'Smanab100%', 'ADMIN')
|
|
`);
|
|
|
|
// Sample Teachers
|
|
await connection.query(`
|
|
INSERT IGNORE INTO teachers (name, nip) VALUES
|
|
('I Ketut Suardika, S.Pd', '196512311990031234'),
|
|
('Ni Made Dewi Lestari, S.Pd', '198001152006042001'),
|
|
('I Wayan Dharma Putra, M.Pd', '197503222000121004')
|
|
`);
|
|
|
|
// Sample Subjects
|
|
await connection.query(`
|
|
INSERT IGNORE INTO subjects (name) VALUES
|
|
('Matematika'), ('Bahasa Indonesia'), ('Bahasa Inggris'),
|
|
('Fisika'), ('Kimia'), ('Biologi'), ('Sejarah'),
|
|
('Geografi'), ('Ekonomi'), ('Pendidikan Agama'),
|
|
('PJOK'), ('Seni Budaya'), ('Informatika')
|
|
`);
|
|
|
|
// Note: Sample students removed - Admin manages student data via import/manual entry
|
|
|
|
console.log('║ ✅ Seed data inserted ║');
|
|
console.log('╠═══════════════════════════════════════════════════════════╣');
|
|
console.log('║ 🎉 Database initialization completed successfully! ║');
|
|
console.log('║ ║');
|
|
console.log('║ Default admin login: ║');
|
|
console.log('║ - Username: admin ║');
|
|
console.log('║ - Password: admin123 ║');
|
|
console.log('╚═══════════════════════════════════════════════════════════╝');
|
|
|
|
return true;
|
|
} catch (error) {
|
|
console.log('╠═══════════════════════════════════════════════════════════╣');
|
|
console.error('║ ❌ Error:', error.message);
|
|
console.log('║');
|
|
console.log('║ Kemungkinan penyebab:');
|
|
console.log('║ 1. MySQL server belum berjalan');
|
|
console.log('║ 2. User/password salah');
|
|
console.log('║ 3. User tidak memiliki permission');
|
|
console.log('║');
|
|
console.log('║ Solusi:');
|
|
console.log('║ - Pastikan MySQL sudah running');
|
|
console.log('║ - Cek credential di file .env');
|
|
console.log('║ - Jalankan: mysql -u root -p');
|
|
console.log('║ CREATE USER smanabid@localhost IDENTIFIED BY password;');
|
|
console.log('║ GRANT ALL ON smanabid_db_jurnal.* TO smanabid@localhost;');
|
|
console.log('╚═══════════════════════════════════════════════════════════╝');
|
|
return false;
|
|
} finally {
|
|
if (connection) {
|
|
await connection.end();
|
|
}
|
|
}
|
|
}
|
|
|
|
// Run
|
|
initDatabase().then(success => {
|
|
process.exit(success ? 0 : 1);
|
|
});
|