<?php
/**
* Clase para gestión de base de datos del Working English System
*/
if (!defined('ABSPATH')) {
exit;
}
class WES_Database {
/**
* Crear todas las tablas necesarias
*/
public static function create_tables() {
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
// Tabla de idiomas
$table_languages = $wpdb->prefix . 'wes_languages';
$sql_languages = "CREATE TABLE $table_languages (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
code varchar(10) NOT NULL,
status enum('active','inactive') DEFAULT 'active',
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY code (code)
) $charset_collate;";
// Tabla de programas
$table_programs = $wpdb->prefix . 'wes_programs';
$sql_programs = "CREATE TABLE $table_programs (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
code varchar(10) NOT NULL,
description text,
duration_months int(11) DEFAULT 12,
status enum('active','inactive') DEFAULT 'active',
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY code (code)
) $charset_collate;";
// Tabla de niveles
$table_levels = $wpdb->prefix . 'wes_levels';
$sql_levels = "CREATE TABLE $table_levels (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
code varchar(10) NOT NULL,
order_number int(11) NOT NULL,
language_id int(11) NOT NULL,
status enum('active','inactive') DEFAULT 'active',
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY language_id (language_id),
FOREIGN KEY (language_id) REFERENCES $table_languages(id) ON DELETE CASCADE
) $charset_collate;";
// Tabla de sedes
$table_branches = $wpdb->prefix . 'wes_branches';
$sql_branches = "CREATE TABLE $table_branches (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
code varchar(10) NOT NULL,
address text,
phone varchar(20),
email varchar(100),
manager_name varchar(100),
status enum('active','inactive') DEFAULT 'active',
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY code (code)
) $charset_collate;";
// Tabla de maestros
$table_teachers = $wpdb->prefix . 'wes_teachers';
$sql_teachers = "CREATE TABLE $table_teachers (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
employee_code varchar(20) NOT NULL,
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
email varchar(100) NOT NULL,
phone varchar(20),
address text,
hire_date date,
birth_date date,
emergency_contact varchar(100),
emergency_phone varchar(20),
specializations text,
status enum('active','inactive','suspended') DEFAULT 'active',
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY employee_code (employee_code),
UNIQUE KEY user_id (user_id),
KEY email (email)
) $charset_collate;";
// Tabla de grupos
$table_groups = $wpdb->prefix . 'wes_groups';
$sql_groups = "CREATE TABLE $table_groups (
id int(11) NOT NULL AUTO_INCREMENT,
code varchar(20) NOT NULL,
name varchar(100) NOT NULL,
language_id int(11) NOT NULL,
program_id int(11) NOT NULL,
level_id int(11) NOT NULL,
teacher_id int(11) NOT NULL,
branch_id int(11) NOT NULL,
modality enum('presencial','virtual','hibrida') NOT NULL,
schedule varchar(200),
start_date date NOT NULL,
end_date date,
max_students int(11) DEFAULT 20,
current_students int(11) DEFAULT 0,
status enum('active','inactive','completed','cancelled') DEFAULT 'active',
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY code (code),
KEY language_id (language_id),
KEY program_id (program_id),
KEY level_id (level_id),
KEY teacher_id (teacher_id),
KEY branch_id (branch_id),
FOREIGN KEY (language_id) REFERENCES $table_languages(id),
FOREIGN KEY (program_id) REFERENCES $table_programs(id),
FOREIGN KEY (level_id) REFERENCES $table_levels(id),
FOREIGN KEY (teacher_id) REFERENCES $table_teachers(id),
FOREIGN KEY (branch_id) REFERENCES $table_branches(id)
) $charset_collate;";
// Tabla de estudiantes
$table_students = $wpdb->prefix . 'wes_students';
$sql_students = "CREATE TABLE $table_students (
id int(11) NOT NULL AUTO_INCREMENT,
student_code varchar(20) NOT NULL,
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
email varchar(100) NOT NULL,
phone varchar(20),
address text,
birth_date date,
gender enum('M','F','O'),
emergency_contact varchar(100),
emergency_phone varchar(20),
registration_date date NOT NULL,
branch_id int(11) NOT NULL,
current_group_id int(11),
status enum('active','inactive','graduated','suspended') DEFAULT 'active',
notes text,
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY student_code (student_code),
KEY email (email),
KEY branch_id (branch_id),
KEY current_group_id (current_group_id),
FOREIGN KEY (branch_id) REFERENCES $table_branches(id),
FOREIGN KEY (current_group_id) REFERENCES $table_groups(id) ON DELETE SET NULL
) $charset_collate;";
// Tabla de inscripciones (historial de grupos por estudiante)
$table_enrollments = $wpdb->prefix . 'wes_enrollments';
$sql_enrollments = "CREATE TABLE $table_enrollments (
id int(11) NOT NULL AUTO_INCREMENT,
student_id int(11) NOT NULL,
group_id int(11) NOT NULL,
enrollment_date date NOT NULL,
completion_date date,
final_grade decimal(5,2),
status enum('active','completed','dropped','transferred') DEFAULT 'active',
notes text,
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY student_id (student_id),
KEY group_id (group_id),
FOREIGN KEY (student_id) REFERENCES $table_students(id) ON DELETE CASCADE,
FOREIGN KEY (group_id) REFERENCES $table_groups(id) ON DELETE CASCADE
) $charset_collate;";
// Tabla de cargos/facturas
$table_charges = $wpdb->prefix . 'wes_student_charges';
$sql_charges = "CREATE TABLE $table_charges (
id int(11) NOT NULL AUTO_INCREMENT,
student_id int(11) NOT NULL,
group_id int(11),
charge_type enum('enrollment','monthly','material','exam','other') NOT NULL,
description varchar(200) NOT NULL,
amount decimal(10,2) NOT NULL,
discount_amount decimal(10,2) DEFAULT 0.00,
final_amount decimal(10,2) NOT NULL,
due_date date NOT NULL,
installments int(11) DEFAULT 1,
paid_amount decimal(10,2) DEFAULT 0.00,
status enum('pending','partial','paid','overdue','cancelled') DEFAULT 'pending',
created_by int(11) NOT NULL,
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY student_id (student_id),
KEY group_id (group_id),
KEY status (status),
KEY due_date (due_date),
FOREIGN KEY (student_id) REFERENCES $table_students(id) ON DELETE CASCADE,
FOREIGN KEY (group_id) REFERENCES $table_groups(id) ON DELETE SET NULL
) $charset_collate;";
// Tabla de pagos
$table_payments = $wpdb->prefix . 'wes_student_payments';
$sql_payments = "CREATE TABLE $table_payments (
id int(11) NOT NULL AUTO_INCREMENT,
charge_id int(11) NOT NULL,
student_id int(11) NOT NULL,
receipt_number varchar(20) NOT NULL,
amount decimal(10,2) NOT NULL,
payment_method enum('cash','card','transfer','check','other') NOT NULL,
payment_date date NOT NULL,
reference_number varchar(50),
notes text,
processed_by int(11) NOT NULL,
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY receipt_number (receipt_number),
KEY charge_id (charge_id),
KEY student_id (student_id),
KEY payment_date (payment_date),
FOREIGN KEY (charge_id) REFERENCES $table_charges(id) ON DELETE CASCADE,
FOREIGN KEY (student_id) REFERENCES $table_students(id) ON DELETE CASCADE
) $charset_collate;";
// Tabla de configuraciones
$table_settings = $wpdb->prefix . 'wes_settings';
$sql_settings = "CREATE TABLE $table_settings (
id int(11) NOT NULL AUTO_INCREMENT,
setting_key varchar(100) NOT NULL,
setting_value longtext,
setting_type enum('string','number','boolean','json','array') DEFAULT 'string',
description text,
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY setting_key (setting_key)
) $charset_collate;";
// Ejecutar consultas
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
$tables = array(
$sql_languages,
$sql_programs,
$sql_levels,
$sql_branches,
$sql_teachers,
$sql_groups,
$sql_students,
$sql_enrollments,
$sql_charges,
$sql_payments,
$sql_settings
);
foreach ($tables as $sql) {
dbDelta($sql);
}
// Insertar datos iniciales
self::insert_initial_data();
// Actualizar versión de BD
update_option('wes_db_version', WES_VERSION);
}
/**
* Insertar datos iniciales
*/
private static function insert_initial_data() {
global $wpdb;
// Idiomas
$languages = array(
array('English', 'EN'),
array('Español', 'ES'),
array('Français', 'FR'),
array('Deutsch', 'DE')
);
foreach ($languages as $lang) {
$wpdb->insert(
$wpdb->prefix . 'wes_languages',
array('name' => $lang[0], 'code' => $lang[1]),
array('%s', '%s')
);
}
// Programas
$programs = array(
array('General English', 'GE', 'Programa general de inglés'),
array('Business English', 'BE', 'Inglés para negocios'),
array('TOEFL Preparation', 'TOEFL', 'Preparación para examen TOEFL'),
array('IELTS Preparation', 'IELTS', 'Preparación para examen IELTS')
);
foreach ($programs as $program) {
$wpdb->insert(
$wpdb->prefix . 'wes_programs',
array('name' => $program[0], 'code' => $program[1], 'description' => $program[2]),
array('%s', '%s', '%s')
);
}
// Niveles para inglés
$levels = array(
array('Beginner', 'A1', 1),
array('Elementary', 'A2', 2),
array('Pre-Intermediate', 'B1', 3),
array('Intermediate', 'B2', 4),
array('Upper-Intermediate', 'C1', 5),
array('Advanced', 'C2', 6)
);
$english_id = $wpdb->get_var("SELECT id FROM {$wpdb->prefix}wes_languages WHERE code = 'EN'");
foreach ($levels as $level) {
$wpdb->insert(
$wpdb->prefix . 'wes_levels',
array(
'name' => $level[0],
'code' => $level[1],
'order_number' => $level[2],
'language_id' => $english_id
),
array('%s', '%s', '%d', '%d')
);
}
// Sede principal
$wpdb->insert(
$wpdb->prefix . 'wes_branches',
array(
'name' => 'Sede Principal',
'code' => 'MAIN',
'address' => 'Dirección de la sede principal',
'phone' => '555-0123',
'email' => 'info@workingenglish.com',
'manager_name' => 'Director General'
),
array('%s', '%s', '%s', '%s', '%s', '%s')
);
// Configuraciones iniciales
$settings = array(
array('academic_year', date('Y'), 'number'),
array('currency_symbol', '$', 'string'),
array('date_format', 'Y-m-d', 'string'),
array('enrollment_fee', '50.00', 'number'),
array('monthly_fee', '120.00', 'number')
);
foreach ($settings as $setting) {
$wpdb->insert(
$wpdb->prefix . 'wes_settings',
array(
'setting_key' => $setting[0],
'setting_value' => $setting[1],
'setting_type' => $setting[2]
),
array('%s', '%s', '%s')
);
}
}
/**
* Verificar si las tablas existen
*/
public static function tables_exist() {
global $wpdb;
$tables = array(
'wes_languages',
'wes_programs',
'wes_levels',
'wes_branches',
'wes_teachers',
'wes_groups',
'wes_students',
'wes_enrollments',
'wes_student_charges',
'wes_student_payments',
'wes_settings'
);
foreach ($tables as $table) {
$table_name = $wpdb->prefix . $table;
if ($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {
return false;
}
}
return true;
}
/**
* Obtener configuración
*/
public static function get_setting($key, $default = null) {
global $wpdb;
$value = $wpdb->get_var($wpdb->prepare(
"SELECT setting_value FROM {$wpdb->prefix}wes_settings WHERE setting_key = %s",
$key
));
return $value !== null ? $value : $default;
}
/**
* Guardar configuración
*/
public static function set_setting($key, $value, $type = 'string') {
global $wpdb;
$wpdb->replace(
$wpdb->prefix . 'wes_settings',
array(
'setting_key' => $key,
'setting_value' => $value,
'setting_type' => $type
),
array('%s', '%s', '%s')
);
}
}