<?php

/**
 * SchoolSlots Pro Database Setup and Automatic Migration.
 *
 * @package SchoolSlotsPro
 */
if ( !defined( 'ABSPATH' ) ) {
    exit;
}
// phpcs:disable WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedFunctionFound, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
function schoolslots_create_database() {
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();
    $t_teachers = $wpdb->prefix . 'school_teachers';
    $t_events = $wpdb->prefix . 'school_events';
    $t_bookings = $wpdb->prefix . 'school_bookings';
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    // 1. Teachers core data and weekly consultation slots
    $sql_teachers = "CREATE TABLE {$t_teachers} ( \t\tid bigint(20) NOT NULL AUTO_INCREMENT, \t\tuser_id bigint(20) DEFAULT 0, \t\tname varchar(100) NOT NULL, \t\temail varchar(100) NOT NULL, \t\troom_number varchar(50) DEFAULT '', \t\tsubjects varchar(255) DEFAULT '', \t\tconsultation_day varchar(20) DEFAULT '', \t\tconsultation_period varchar(20) DEFAULT '', \t\tconsultation_start time DEFAULT NULL, \t\tconsultation_end time DEFAULT NULL, \t\tbreak_start time DEFAULT NULL, \t\tbreak_end time DEFAULT NULL, \t\tbuffer_minutes int(11) DEFAULT 0, \t\tPRIMARY KEY  (id), \t\tKEY email (email) \t) {$charset_collate};";
    // 2. Events definitions (including absent_teachers and is_active)
    $sql_events = "CREATE TABLE {$t_events} ( \t\tid bigint(20) NOT NULL AUTO_INCREMENT, \t\tevent_type varchar(50) NOT NULL, \t\tteacher_id bigint(20) DEFAULT 0, \t\tevent_title varchar(150) NOT NULL, \t\tlocation_room varchar(100) DEFAULT '', \t\tevent_date date DEFAULT NULL, \t\tend_date date DEFAULT NULL, \t\tstart_time time DEFAULT NULL, \t\tend_time time DEFAULT NULL, \t\tslot_duration int(11) DEFAULT 15, \t\tbuffer_minutes int(11) DEFAULT 0, \t\tbreak_start time DEFAULT NULL, \t\tbreak_end time DEFAULT NULL, \t\tmax_capacity int(11) DEFAULT 180, \t\tconsultation_day varchar(20) DEFAULT '', \t\tconsultation_period varchar(20) DEFAULT '', \t\tdeadline_type varchar(20) DEFAULT 'default', \t\tdeadline_time time DEFAULT '17:00:00', \t\treport_sent int(1) DEFAULT 0, \t\tdaily_schedule longtext DEFAULT NULL, \t\tabsent_teachers longtext DEFAULT NULL, \t\tis_active tinyint(1) DEFAULT 1, \t\tPRIMARY KEY  (id), \t\tKEY event_date (event_date), \t\tKEY is_active (is_active) \t) {$charset_collate};";
    // 3. Bookings records
    $sql_bookings = "CREATE TABLE {$t_bookings} ( \t\tid bigint(20) NOT NULL AUTO_INCREMENT, \t\tevent_type varchar(50) NOT NULL, \t\tteacher_id bigint(20) DEFAULT 0, \t\tevent_id bigint(20) DEFAULT 0, \t\tevent_date date DEFAULT NULL, \t\ttime_slot time DEFAULT NULL, \t\tparent_name varchar(100) DEFAULT '', \t\tstudent_name varchar(100) NOT NULL, \t\tstudent_class varchar(20) DEFAULT '', \t\tcontact_email varchar(100) NOT NULL, \t\tcancel_token varchar(64) DEFAULT '', \t\tstatus varchar(20) DEFAULT 'confirmed', \t\treminder_sent int(1) DEFAULT 0, \t\tchecked_in tinyint(1) DEFAULT 0, \t\tchecked_in_at datetime DEFAULT NULL, \t\tanonymized tinyint(1) DEFAULT 0, \t\tcreated_at datetime DEFAULT CURRENT_TIMESTAMP, \t\tPRIMARY KEY  (id), \t\tKEY event_id (event_id), \t\tKEY teacher_slot (teacher_id, event_date, time_slot), \t\tKEY cancel_token (cancel_token) \t) {$charset_collate};";
    dbDelta( $sql_teachers );
    dbDelta( $sql_events );
    dbDelta( $sql_bookings );
    // Default system settings with schoolslots_ prefix
    if ( false === get_option( 'schoolslots_default_school_address' ) ) {
        add_option( 'schoolslots_default_school_address', "Central High School / Main Building\n1000 Main Street 1." );
    }
    if ( false === get_option( 'schoolslots_secretary_email' ) ) {
        add_option( 'schoolslots_secretary_email', get_option( 'admin_email' ) );
    }
    if ( false === get_option( 'schoolslots_director_email' ) ) {
        add_option( 'schoolslots_director_email', get_option( 'admin_email' ) );
    }
    if ( false === get_option( 'schoolslots_deputy_email' ) ) {
        add_option( 'schoolslots_deputy_email', '' );
    }
    if ( false === get_option( 'schoolslots_enable_secretary_bcc' ) ) {
        add_option( 'schoolslots_enable_secretary_bcc', '1' );
    }
    if ( false === get_option( 'schoolslots_cancel_redirect_url' ) ) {
        add_option( 'schoolslots_cancel_redirect_url', home_url( '/' ) );
    }
    if ( false === get_option( 'schoolslots_gdpr_retention_days' ) ) {
        add_option( 'schoolslots_gdpr_retention_days', '30' );
    }
    if ( false === get_option( 'schoolslots_reminder_send_time' ) ) {
        add_option( 'schoolslots_reminder_send_time', '07:00' );
    }
    // Update DB version identifier
    update_option( 'schoolslots_db_version', '9.8.5' );
    // Scheduled cron tasks registration
    if ( !wp_next_scheduled( 'schoolslots_daily_morning_reminder' ) ) {
        wp_schedule_event( time(), 'hourly', 'schoolslots_daily_morning_reminder' );
    }
}

// Runtime schema check for safe column existence
add_action( 'admin_init', 'schoolslots_check_database_schema' );
function schoolslots_check_database_schema() {
    global $wpdb;
    $t_bookings = $wpdb->prefix . 'school_bookings';
    $t_events = $wpdb->prefix . 'school_events';
    $t_teachers = $wpdb->prefix . 'school_teachers';
    $col_subj = $wpdb->get_results( $wpdb->prepare( "SHOW COLUMNS FROM `{$t_teachers}` LIKE %s", 'subjects' ) );
    if ( empty( $col_subj ) ) {
        $wpdb->query( "ALTER TABLE `{$t_teachers}` ADD `subjects` varchar(255) DEFAULT '' AFTER `room_number`" );
    }
    $col1 = $wpdb->get_results( $wpdb->prepare( "SHOW COLUMNS FROM `{$t_bookings}` LIKE %s", 'checked_in' ) );
    if ( empty( $col1 ) ) {
        $wpdb->query( "ALTER TABLE `{$t_bookings}` ADD `checked_in` tinyint(1) DEFAULT 0, ADD `checked_in_at` datetime DEFAULT NULL" );
    }
    $col2 = $wpdb->get_results( $wpdb->prepare( "SHOW COLUMNS FROM `{$t_bookings}` LIKE %s", 'anonymized' ) );
    if ( empty( $col2 ) ) {
        $wpdb->query( "ALTER TABLE `{$t_bookings}` ADD `anonymized` tinyint(1) DEFAULT 0" );
    }
    $col3 = $wpdb->get_results( $wpdb->prepare( "SHOW COLUMNS FROM `{$t_events}` LIKE %s", 'daily_schedule' ) );
    if ( empty( $col3 ) ) {
        $wpdb->query( "ALTER TABLE `{$t_events}` ADD `daily_schedule` longtext DEFAULT NULL" );
    }
    $col4 = $wpdb->get_results( $wpdb->prepare( "SHOW COLUMNS FROM `{$t_events}` LIKE %s", 'absent_teachers' ) );
    if ( empty( $col4 ) ) {
        $wpdb->query( "ALTER TABLE `{$t_events}` ADD `absent_teachers` longtext DEFAULT NULL" );
    }
    $col5 = $wpdb->get_results( $wpdb->prepare( "SHOW COLUMNS FROM `{$t_bookings}` LIKE %s", 'status' ) );
    if ( empty( $col5 ) ) {
        $wpdb->query( "ALTER TABLE `{$t_bookings}` ADD `status` varchar(20) DEFAULT 'confirmed'" );
    }
    // Ensure morning reminder cron runs hourly instead of legacy daily schedule
    $sched = wp_get_schedule( 'schoolslots_daily_morning_reminder' );
    if ( 'hourly' !== $sched ) {
        wp_clear_scheduled_hook( 'schoolslots_daily_morning_reminder' );
        wp_schedule_event( time(), 'hourly', 'schoolslots_daily_morning_reminder' );
    }
}