<?php
namespace AccessNow\SimpleLMS;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// This class reads and writes the plugin's own {prefix}slms_* tables, so direct
// queries are required; the data is per-user and changes on every action, so it is
// not cached. Table names are built from $wpdb->prefix and fixed strings, and every
// value goes through $wpdb->prepare() or $wpdb->insert/update/delete.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange

class Activator {

	public static function activate() {
		// Migrations first: the older steps expect the older table layouts. maybe_upgrade() also
		// creates the tables on a fresh install.
		require_once ACCESSNOW_SLMS_PLUGIN_DIR . 'includes/class-settings.php';
		require_once ACCESSNOW_SLMS_PLUGIN_DIR . 'includes/class-upgrader.php';
		Upgrader::maybe_upgrade();

		// Some 4.x installs recorded 4.0.0 without the 3.0.0 step having run, so they lack the
		// points de-duplication index. Remove duplicate awards first, or the unique index can't be added.
		Upgrader::repair_unique_indexes();

		// Then bring every table in line with schema(): create missing tables, add missing columns and indexes.
		self::create_tables();

		require_once ACCESSNOW_SLMS_PLUGIN_DIR . 'includes/class-capabilities.php';
		Capabilities::create_roles();
		require_once ACCESSNOW_SLMS_PLUGIN_DIR . 'includes/class-post-types.php';
		Post_Types::register_post_types();
		require_once ACCESSNOW_SLMS_PLUGIN_DIR . 'includes/class-certificates.php';
		Certificates::register_rewrite();
		flush_rewrite_rules();
	}

	/**
	 * Create missing tables and add missing columns and indexes, so every table matches schema().
	 *
	 * dbDelta() never drops a column or an index. A schema change is made by editing schema()
	 * and adding an Upgrader step that calls this method.
	 *
	 * @return array Changes made, as reported by dbDelta().
	 */
	public static function create_tables() {
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		return dbDelta( self::schema() );
	}

	/**
	 * The plugin's tables, as CREATE TABLE statements in the form dbDelta() can compare with
	 * an existing table: no IF NOT EXISTS (dbDelta() would read "IF" as the table name and never
	 * alter an existing table), one column or index per line, two spaces after PRIMARY KEY, and
	 * KEY rather than INDEX.
	 *
	 * @return string[]
	 */
	public static function schema() {
		global $wpdb;
		$charset_collate = $wpdb->get_charset_collate();

		return array(
			"CREATE TABLE {$wpdb->prefix}slms_enrollments (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				user_id bigint(20) unsigned NOT NULL,
				course_id bigint(20) unsigned NOT NULL,
				enrolled_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				enrolled_by bigint(20) unsigned NOT NULL DEFAULT 0,
				status varchar(20) NOT NULL DEFAULT 'active',
				completed_at datetime DEFAULT NULL,
				expires_at datetime DEFAULT NULL,
				PRIMARY KEY  (id),
				KEY user_course (user_id,course_id),
				KEY idx_user (user_id),
				KEY idx_course (course_id),
				KEY idx_status (user_id,course_id,status)
			) $charset_collate;",

			"CREATE TABLE {$wpdb->prefix}slms_progress (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				user_id bigint(20) unsigned NOT NULL,
				course_id bigint(20) unsigned NOT NULL,
				lesson_id bigint(20) unsigned NOT NULL,
				enrollment_id bigint(20) unsigned NOT NULL DEFAULT 0,
				status varchar(20) NOT NULL DEFAULT 'incomplete',
				completed_at datetime DEFAULT NULL,
				content_hash varchar(32) DEFAULT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY enrollment_lesson (enrollment_id,lesson_id),
				KEY idx_user_course (user_id,course_id)
			) $charset_collate;",

			"CREATE TABLE {$wpdb->prefix}slms_quiz_attempts (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				user_id bigint(20) unsigned NOT NULL,
				quiz_id bigint(20) unsigned NOT NULL,
				course_id bigint(20) unsigned NOT NULL,
				enrollment_id bigint(20) unsigned NOT NULL DEFAULT 0,
				attempt_number smallint(5) unsigned NOT NULL DEFAULT 1,
				score decimal(5,2) NOT NULL DEFAULT 0.00,
				passed tinyint(1) NOT NULL DEFAULT 0,
				status varchar(20) NOT NULL DEFAULT 'graded',
				answers longtext NOT NULL,
				started_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				completed_at datetime DEFAULT NULL,
				PRIMARY KEY  (id),
				KEY idx_user_quiz (user_id,quiz_id),
				KEY idx_course (course_id),
				KEY idx_enrollment_quiz (enrollment_id,quiz_id)
			) $charset_collate;",

			"CREATE TABLE {$wpdb->prefix}slms_lesson_notes (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				user_id bigint(20) unsigned NOT NULL,
				lesson_id bigint(20) unsigned NOT NULL,
				note_content longtext NOT NULL,
				created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				UNIQUE KEY user_lesson (user_id,lesson_id)
			) $charset_collate;",

			// idx_cert_user was only ever added by the 3.0.0 upgrade step; it is part of the schema.
			"CREATE TABLE {$wpdb->prefix}slms_certificates (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				user_id bigint(20) unsigned NOT NULL,
				course_id bigint(20) unsigned NOT NULL,
				enrollment_id bigint(20) unsigned NOT NULL DEFAULT 0,
				certificate_code varchar(64) NOT NULL,
				issued_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				UNIQUE KEY cert_code (certificate_code),
				UNIQUE KEY enrollment_course (enrollment_id,course_id),
				KEY idx_cert_user (user_id)
			) $charset_collate;",

			"CREATE TABLE {$wpdb->prefix}slms_points (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				user_id bigint(20) unsigned NOT NULL,
				points int(11) NOT NULL DEFAULT 0,
				action varchar(50) NOT NULL,
				reference_id bigint(20) unsigned NOT NULL DEFAULT 0,
				enrollment_id bigint(20) unsigned NOT NULL DEFAULT 0,
				earned_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				KEY idx_user (user_id),
				UNIQUE KEY idx_points_dedup (user_id,action,reference_id,enrollment_id)
			) $charset_collate;",

			"CREATE TABLE {$wpdb->prefix}slms_badges (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				user_id bigint(20) unsigned NOT NULL,
				badge_type varchar(50) NOT NULL,
				badge_label varchar(100) NOT NULL,
				earned_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				UNIQUE KEY user_badge (user_id,badge_type)
			) $charset_collate;",
		);
	}
				}