root
337 lines
11 KiB
PHP
337 lines
11 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace App\Database\Migrations;
|
|
|
|
use CodeIgniter\Database\Migration;
|
|
use RuntimeException;
|
|
|
|
final class AlignSchemaToScoolViewDump extends Migration
|
|
{
|
|
/**
|
|
* Columns present in school_prod.sql.zip but absent from scool_view.sql.zip.
|
|
*
|
|
* This migration intentionally makes the live database match the scool_view
|
|
* dump captured on 2026-07-17.
|
|
*/
|
|
private array $columnsToDrop = [
|
|
'classSection' => ['semester'],
|
|
'contactus' => ['semester'],
|
|
'discount_vouchers' => ['semester'],
|
|
'emergency_contacts' => ['semester', 'school_year'],
|
|
'ip_attempts' => ['semester'],
|
|
'login_activity' => ['semester'],
|
|
'notification_recipients' => ['semester'],
|
|
'notifications' => ['semester'],
|
|
'parents' => ['semester', 'school_year'],
|
|
'payment_error' => ['semester'],
|
|
'payment_transactions' => ['semester'],
|
|
'payments' => ['semester'],
|
|
'paypal_transactions' => ['semester'],
|
|
'preferences' => ['school_year'],
|
|
'refunds' => ['semester'],
|
|
'reimbursement_batch_items' => ['semester'],
|
|
'reimbursement_batches' => ['semester'],
|
|
'reimbursements' => ['semester'],
|
|
'students' => ['semester', 'school_year'],
|
|
'support_requests' => ['semester'],
|
|
'user_notifications' => ['semester'],
|
|
'users' => ['semester', 'school_year'],
|
|
'whatsapp_group_links' => ['semester'],
|
|
'whatsapp_group_memberships' => ['semester'],
|
|
];
|
|
|
|
/**
|
|
* Tables that should own a required school_year value after aligning the schema.
|
|
*
|
|
* @var list<string>
|
|
*/
|
|
private array $schoolYearTables = [
|
|
'admin_notification_subjects',
|
|
'attendance_comment_template',
|
|
'class_progress_attachments',
|
|
'competition_class_winners',
|
|
'competition_scores',
|
|
'competition_winners',
|
|
'inventory_categories',
|
|
'paypal_payments',
|
|
'placement_scores',
|
|
'promotion_queue',
|
|
'qcmquestions',
|
|
'school_year_closing_batches',
|
|
'school_year_closing_items',
|
|
'school_year_transition_logs',
|
|
'staff',
|
|
'whatsapp_group_links',
|
|
'whatsapp_invites_log',
|
|
];
|
|
|
|
public function up(): void
|
|
{
|
|
if ($this->db->DBDriver !== 'MySQLi') {
|
|
throw new RuntimeException('This migration requires MySQL/MariaDB through the MySQLi driver.');
|
|
}
|
|
|
|
$this->dropIndexIfExists('competition_winners', 'competition_id_class_section_id_rank');
|
|
$this->dropIndexIfExists('refunds', 'idx_refunds_parent_year_semester_status');
|
|
$this->dropIndexIfExists('teacher_class', 'idx_sy_teacher_class_school_year_223511c0');
|
|
$this->dropIndexIfExists('whatsapp_group_links', 'uq_section_term');
|
|
$this->dropIndexIfExists('whatsapp_group_memberships', 'uniq_whatsapp_membership');
|
|
$this->dropIndexIfExists('whatsapp_group_memberships', 'class_section_id_school_year_semester');
|
|
|
|
foreach ($this->columnsToDrop as $table => $columns) {
|
|
foreach ($columns as $column) {
|
|
$this->dropColumnIfExists($table, $column);
|
|
}
|
|
}
|
|
|
|
$this->db->resetDataCache();
|
|
|
|
foreach ($this->schoolYearTables as $table) {
|
|
$this->ensureRequiredSchoolYearColumn($table);
|
|
}
|
|
|
|
$this->db->resetDataCache();
|
|
|
|
$this->ensureIndex(
|
|
'refunds',
|
|
'idx_refunds_parent_year_semester_status',
|
|
['parent_id', 'school_year', 'status']
|
|
);
|
|
|
|
$this->ensureIndex(
|
|
'teacher_class',
|
|
'unique_teacher_assignment',
|
|
['teacher_id', 'class_section_id', 'school_year'],
|
|
true
|
|
);
|
|
|
|
$this->ensureIndex(
|
|
'whatsapp_group_links',
|
|
'uq_section_term',
|
|
['class_section_id', 'school_year'],
|
|
true
|
|
);
|
|
|
|
$this->ensureIndex(
|
|
'whatsapp_group_memberships',
|
|
'uniq_whatsapp_membership',
|
|
['class_section_id', 'school_year', 'subject_type', 'subject_id'],
|
|
true
|
|
);
|
|
|
|
$this->ensureIndex(
|
|
'whatsapp_group_memberships',
|
|
'class_section_id_school_year_semester',
|
|
['class_section_id', 'school_year']
|
|
);
|
|
|
|
if ($this->db->tableExists('whatsapp_group_links')) {
|
|
$this->db->query(
|
|
'ALTER TABLE `whatsapp_group_links` ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci'
|
|
);
|
|
}
|
|
}
|
|
|
|
public function down(): void
|
|
{
|
|
throw new RuntimeException(
|
|
'This migration is intentionally irreversible because matching scool_view drops columns and their data.'
|
|
);
|
|
}
|
|
|
|
private function ensureRequiredSchoolYearColumn(string $table): void
|
|
{
|
|
if (! $this->db->tableExists($table)) {
|
|
return;
|
|
}
|
|
|
|
if (! $this->db->fieldExists('school_year', $table)) {
|
|
$this->forge->addColumn($table, [
|
|
'school_year' => [
|
|
'type' => 'VARCHAR',
|
|
'constraint' => 9,
|
|
'null' => true,
|
|
],
|
|
]);
|
|
}
|
|
|
|
$year = $this->currentSchoolYear();
|
|
$this->db->query(
|
|
sprintf(
|
|
'UPDATE %s SET `school_year` = ? WHERE `school_year` IS NULL OR TRIM(`school_year`) = \'\'',
|
|
$this->quoteIdentifier($table)
|
|
),
|
|
[$year]
|
|
);
|
|
|
|
$this->db->query(sprintf(
|
|
'ALTER TABLE %s MODIFY `school_year` VARCHAR(9) NOT NULL',
|
|
$this->quoteIdentifier($table)
|
|
));
|
|
}
|
|
|
|
private function currentSchoolYear(): string
|
|
{
|
|
if ($this->db->tableExists('configuration')) {
|
|
$row = $this->db->table('configuration')
|
|
->select('config_value')
|
|
->whereIn('config_key', ['school_year', 'current_school_year'])
|
|
->where('config_value IS NOT NULL', null, false)
|
|
->where('TRIM(config_value) <>', '')
|
|
->orderBy("FIELD(config_key, 'school_year', 'current_school_year')", '', false)
|
|
->get(1)
|
|
->getRowArray();
|
|
|
|
if (isset($row['config_value']) && preg_match('/^\d{4}-\d{4}$/', (string) $row['config_value'])) {
|
|
return (string) $row['config_value'];
|
|
}
|
|
}
|
|
|
|
$year = (int) date('Y');
|
|
|
|
return $year . '-' . ($year + 1);
|
|
}
|
|
|
|
private function dropColumnIfExists(string $table, string $column): void
|
|
{
|
|
if (! $this->db->tableExists($table) || ! $this->db->fieldExists($column, $table)) {
|
|
return;
|
|
}
|
|
|
|
$this->dropForeignKeysContainingColumn($table, $column);
|
|
$this->dropIndexesContainingColumn($table, $column);
|
|
$this->dropChecksContainingColumn($table, $column);
|
|
|
|
$this->forge->dropColumn($table, $column);
|
|
}
|
|
|
|
private function ensureIndex(string $table, string $index, array $columns, bool $unique = false): void
|
|
{
|
|
if (! $this->db->tableExists($table) || ! $this->hasColumns($table, $columns)) {
|
|
return;
|
|
}
|
|
|
|
if ($this->indexExists($table, $index)) {
|
|
return;
|
|
}
|
|
|
|
$keyword = $unique ? 'UNIQUE INDEX' : 'INDEX';
|
|
$columnList = implode(', ', array_map([$this, 'quoteIdentifier'], $columns));
|
|
|
|
$this->db->query(sprintf(
|
|
'ALTER TABLE %s ADD %s %s (%s)',
|
|
$this->quoteIdentifier($table),
|
|
$keyword,
|
|
$this->quoteIdentifier($index),
|
|
$columnList
|
|
));
|
|
}
|
|
|
|
private function dropIndexIfExists(string $table, string $index): void
|
|
{
|
|
if (! $this->db->tableExists($table) || ! $this->indexExists($table, $index)) {
|
|
return;
|
|
}
|
|
|
|
$this->db->query(sprintf(
|
|
'ALTER TABLE %s DROP INDEX %s',
|
|
$this->quoteIdentifier($table),
|
|
$this->quoteIdentifier($index)
|
|
));
|
|
}
|
|
|
|
private function dropIndexesContainingColumn(string $table, string $column): void
|
|
{
|
|
$indexes = $this->db->query(
|
|
'SELECT DISTINCT INDEX_NAME
|
|
FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = ?
|
|
AND COLUMN_NAME = ?
|
|
AND INDEX_NAME <> \'PRIMARY\'',
|
|
[$table, $column]
|
|
)->getResult();
|
|
|
|
foreach ($indexes as $index) {
|
|
$this->dropIndexIfExists($table, $index->INDEX_NAME);
|
|
}
|
|
}
|
|
|
|
private function dropForeignKeysContainingColumn(string $table, string $column): void
|
|
{
|
|
$constraints = $this->db->query(
|
|
'SELECT DISTINCT CONSTRAINT_NAME
|
|
FROM information_schema.KEY_COLUMN_USAGE
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = ?
|
|
AND COLUMN_NAME = ?
|
|
AND REFERENCED_TABLE_NAME IS NOT NULL',
|
|
[$table, $column]
|
|
)->getResult();
|
|
|
|
foreach ($constraints as $constraint) {
|
|
$this->db->query(sprintf(
|
|
'ALTER TABLE %s DROP FOREIGN KEY %s',
|
|
$this->quoteIdentifier($table),
|
|
$this->quoteIdentifier($constraint->CONSTRAINT_NAME)
|
|
));
|
|
}
|
|
}
|
|
|
|
private function dropChecksContainingColumn(string $table, string $column): void
|
|
{
|
|
$checks = $this->db->query(
|
|
'SELECT tc.CONSTRAINT_NAME
|
|
FROM information_schema.TABLE_CONSTRAINTS tc
|
|
INNER JOIN information_schema.CHECK_CONSTRAINTS cc
|
|
ON cc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
|
|
AND cc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
|
|
WHERE tc.CONSTRAINT_SCHEMA = DATABASE()
|
|
AND tc.TABLE_NAME = ?
|
|
AND tc.CONSTRAINT_TYPE = \'CHECK\'
|
|
AND cc.CHECK_CLAUSE LIKE ?',
|
|
[$table, '%' . $column . '%']
|
|
)->getResult();
|
|
|
|
foreach ($checks as $check) {
|
|
$this->db->query(sprintf(
|
|
'ALTER TABLE %s DROP CHECK %s',
|
|
$this->quoteIdentifier($table),
|
|
$this->quoteIdentifier($check->CONSTRAINT_NAME)
|
|
));
|
|
}
|
|
}
|
|
|
|
private function indexExists(string $table, string $index): bool
|
|
{
|
|
$row = $this->db->query(
|
|
'SELECT COUNT(*) AS aggregate
|
|
FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = ?
|
|
AND INDEX_NAME = ?',
|
|
[$table, $index]
|
|
)->getRow();
|
|
|
|
return (int) ($row->aggregate ?? 0) > 0;
|
|
}
|
|
|
|
private function hasColumns(string $table, array $columns): bool
|
|
{
|
|
foreach ($columns as $column) {
|
|
if (! $this->db->fieldExists($column, $table)) {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
private function quoteIdentifier(string $identifier): string
|
|
{
|
|
return '`' . str_replace('`', '``', $identifier) . '`';
|
|
}
|
|
}
|