<?php
defined( 'ABSPATH' ) || exit;

/**
 * Certificate Verification database
 *
 * @package ACCEVE_Certificate_Verification_Database
 * @since 1.0.0
 */
class ACCEVE_Certificate_Verification_Database {

    /**
     * Singleton instance.
     *
     * @var ACCEVE_Certificate_Verification_Database|null
     */
    private static $instance = null;

    /**
     * Database table name for certificate verification records.
     * table prefix (e.g. wp_acceve_certificate_verification).
     *
     * @var string
     */
    private $table_name;

    /**
     * Get singleton instance.
     *
     * @return ACCEVE_Certificate_Verification_Database
     */
    public static function get_instance() {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * Constructor.
     */
    private function __construct() {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'acceve_certificate_verification';

        add_action('admin_init', array($this, 'database_table_for_certificate_verification'));
        $this->maybe_add_certificate_course_id_column();
        $this->maybe_add_certificate_code_columns();
        $this->maybe_update_varchar_columns();
    }

    /**
     * Update all varchar columns in certificate verification table to varchar(255) for existing installs.
     *
     * @return void
     */
    public function maybe_update_varchar_columns() {
        global $wpdb;
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->table_name)) !== $this->table_name) {
            return;
        }

        $columns = $wpdb->get_results("SHOW COLUMNS FROM `{$this->table_name}`", ARRAY_A);
        if (empty($columns)) {
            return;
        }

        foreach ($columns as $column) {
            $field = $column['Field'];
            $type  = strtolower($column['Type']);
            $null  = strtoupper($column['Null']) === 'NO' ? 'NOT NULL' : 'DEFAULT NULL';

            if (strpos($type, 'varchar(') === 0 && $type !== 'varchar(255)') {
                $wpdb->query("ALTER TABLE `{$this->table_name}` MODIFY `$field` varchar(255) $null");
            }
        }
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }

    /**
     * Add qr_content / barcode_content columns on existing installs.
     *
     * Stores the per-certificate content encoded into the QR code and barcode
     * (Pro feature). Runs when the DB layer first loads.
     *
     * @return void
     */
    public function maybe_add_certificate_code_columns() {
        global $wpdb;
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->table_name)) !== $this->table_name) {
            return;
        }

        $qr_found = $wpdb->get_results($wpdb->prepare("SHOW COLUMNS FROM `{$this->table_name}` LIKE %s", 'qr_content'));
        if (empty($qr_found)) {
            $wpdb->query("ALTER TABLE `{$this->table_name}` ADD COLUMN qr_content text DEFAULT NULL AFTER custom_fields");
        }

        $barcode_found = $wpdb->get_results($wpdb->prepare("SHOW COLUMNS FROM `{$this->table_name}` LIKE %s", 'barcode_content'));
        if (empty($barcode_found)) {
            $wpdb->query("ALTER TABLE `{$this->table_name}` ADD COLUMN barcode_content varchar(255) DEFAULT NULL AFTER qr_content");
        }
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
    }

    /**
     * Add course_id column on existing installs (runs when DB layer first loads, including frontend).
     *
     * @return void
     */
    public function maybe_add_certificate_course_id_column() {
        global $wpdb;
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->table_name)) !== $this->table_name) {
            return;
        }
        $found = $wpdb->get_results($wpdb->prepare("SHOW COLUMNS FROM `{$this->table_name}` LIKE %s", 'course_id'));
        if (!empty($found)) {
            return;
        }
        $wpdb->query("ALTER TABLE `{$this->table_name}` ADD COLUMN course_id varchar(255) DEFAULT NULL AFTER course_name");
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
    }

    /**
     * Certificate verification database table create.
     *
     * @return void
     * @since 1.0.0
     */
    public function database_table_for_certificate_verification() {
        global $wpdb;

        // Check if table exists
        // phpcs:ignore
        if ($wpdb->get_var("SHOW TABLES LIKE '$this->table_name'") != $this->table_name) {
            $charset_collate = $wpdb->get_charset_collate();

            $sql = "CREATE TABLE $this->table_name (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            certificate_id varchar(255) NOT NULL,
            student_name varchar(255) NOT NULL,
            birth_date date NOT NULL,
            course_type varchar(255) NOT NULL,
            course_name varchar(255) NOT NULL,
            course_id varchar(255) DEFAULT NULL,
            batch_name varchar(255) DEFAULT NULL,
            institution varchar(255) NOT NULL,
            issue_date date NOT NULL,
            expiry_date date DEFAULT NULL,
            verification_code varchar(255) NOT NULL,
            additional_data text DEFAULT NULL,
            custom_fields longtext DEFAULT NULL,
            qr_content text DEFAULT NULL,
            barcode_content varchar(255) DEFAULT NULL,
            is_active tinyint(1) DEFAULT 1,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY certificate_id (certificate_id),
            KEY verification_code (verification_code)
        ) $charset_collate;";

            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            dbDelta($sql);

            self::add_sample_data();
        }

        // Create table if not exists
        $this->create_custom_fields_table();

        // Create table if not exists
        $this->create_certificate_templates_table();
    }

    /**
     * Helper function for inserting two sample data on database, when db table create.
     *
     * @return void
     * @since 1.0.0
     */
    private static function add_sample_data() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'acceve_certificate_verification';

        $sample_data = array(
            array(
                'certificate_id' => 'DIP-' . wp_rand(1000, 9999),
                'student_name' => 'John Doe',
                'birth_date' => gmdate('Y-m-d', strtotime('+15 years')),
                'course_type' => 'Online',
                'course_name' => 'Advanced Web Development',
                'batch_name' => 'Batch 2024-A',
                'institution' => 'Tech University',
                'issue_date' => gmdate('Y-m-d', strtotime('-1 month')),
                'expiry_date' => gmdate('Y-m-d', strtotime('+2 years')),
                'verification_code' => wp_generate_password(8, false),
                'is_active' => 1
            ),
            array(
                'certificate_id' => 'UNI-' . wp_rand(1000, 9999),
                'student_name' => 'Jane Smith',
                'birth_date' => gmdate('Y-m-d', strtotime('+15 years')),
                'course_type' => 'Offline',
                'course_name' => 'Computer Science',
                'batch_name' => 'Batch 2024-B',
                'institution' => 'Global University',
                'issue_date' => gmdate('Y-m-d', strtotime('-6 months')),
                'expiry_date' => gmdate('Y-m-d', strtotime('+4 years')),
                'verification_code' => wp_generate_password(8, false),
                'is_active' => 1
            )
        );

        foreach ($sample_data as $data) {
            // phpcs:ignore
            $wpdb->insert($table_name, $data);
        }
    }

    /**
     * Create custom fields table.
     *
     * @return void
     * @since 1.0.2
     */
    private function create_custom_fields_table() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'acceve_custom_fields';
        $charset_collate = $wpdb->get_charset_collate();

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) != $table_name) {
            $sql = "CREATE TABLE $table_name (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            field_name varchar(100) NOT NULL,
            field_label varchar(255) NOT NULL,
            field_type varchar(50) NOT NULL,
            field_options text DEFAULT NULL,
            is_required tinyint(1) DEFAULT 0,
            field_order int(11) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY field_name (field_name)
        ) $charset_collate;";

            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            dbDelta($sql);
        }
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
    }

    /**
     * Create certificate templates table.
     *
     * @return void
     * @since 1.0.0
     */
    private function create_certificate_templates_table() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'acceve_certificate_templates';
        $charset_collate = $wpdb->get_charset_collate();

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) != $table_name) {
            $sql = "CREATE TABLE $table_name (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            template_name varchar(255) NOT NULL,
            background_image text DEFAULT NULL,
            elements longtext DEFAULT NULL,
            canvas_width int(11) DEFAULT 1123,
            canvas_height int(11) DEFAULT 794,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id)
        ) $charset_collate;";

            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            dbDelta($sql);
        }
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
    }

    /**
     * Helper function for getting certificate information from db table.
     *
     * @return array | null | object
     * @since 1.0.0
     */
    public function get_certificate_with_date($certificate_id, $birth_date) {
        global $wpdb;

        // phpcs:ignore
        return $wpdb->get_row(
        // phpcs:ignore
            $wpdb->prepare("SELECT * FROM {$this->table_name} WHERE certificate_id = %s AND birth_date = %s", $certificate_id, $birth_date)
        );
    }

    /**
     * Helper function for getting certificate information with custom verification filters (DOB, Issue Date).
     *
     * @param string      $certificate_id Certificate ID.
     * @param string|null $birth_date     Date of birth filter (optional).
     * @param string|null $issue_date     Issue date filter (optional).
     *
     * @return array | null | object
     * @since 1.0.6
     */
    public function get_certificate_with_verification_details($certificate_id, $birth_date = null, $issue_date = null) {
        global $wpdb;

        $sql = "SELECT * FROM {$this->table_name} WHERE certificate_id = %s";
        $args = array($certificate_id);

        if (!empty($birth_date)) {
            $sql .= " AND birth_date = %s";
            $args[] = $birth_date;
        }

        if (!empty($issue_date)) {
            $sql .= " AND issue_date = %s";
            $args[] = $issue_date;
        }

        // phpcs:ignore
        return $wpdb->get_row(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->prepare($sql, ...$args)
        );
    }

    /**
     * Helper function for getting certificate information from db table.
     *
     * @return array | null | object
     * @since 1.0.0
     */
    public function get_certificate($certificate_id) {
        global $wpdb;

        // phpcs:ignore
        return $wpdb->get_row(
        // phpcs:ignore
            $wpdb->prepare("SELECT * FROM {$this->table_name} WHERE certificate_id = %s", $certificate_id)
        );
    }

    /**
     * Helper function for adding certificate information into db table.
     *
     * @return bool | int | null
     * @since 1.0.0
     */
    public function add_certificate($data) {
        global $wpdb;

        $defaults = array(
            'verification_code' => wp_generate_password(8, false),
            'created_at'        => current_time('mysql'),
            'is_active'         => 1
        );

        $data = wp_parse_args($data, $defaults);
        // phpcs:ignore
        return $wpdb->insert($this->table_name, $data);
    }

    /**
     * Helper function for updating certificate information into db table.
     *
     * @return bool | int | null
     * @since 1.0.0
     */

    public function update_certificate($original_id, $data) {
        global $wpdb;

        // phpcs:ignore
        return $wpdb->update(
            $this->table_name,
            $data,
            array('certificate_id' => $original_id),
            null,
            array('%s')
        );
    }

    /**
     * Helper function for deleting certificate information from db table.
     *
     * @return bool | int | null
     * @since 1.0.0
     */
    public function delete_certificate($certificate_id) {
        global $wpdb;

        // phpcs:ignore
        return $wpdb->delete(
            $this->table_name,
            array('certificate_id' => $certificate_id),
            array('%s')
        );
    }

    /**
     * Helper function for getting all certificate from db table for all certificate show on table.
     *
     * @return array | null | object
     * @since 1.0.0
     */
    public function get_all_certificates($per_page = 20, $page_number = 1, $search = '') {
        global $wpdb;

        $offset = ($page_number - 1) * $per_page;
        $search = trim($search);

        $where_sql = '';
        $params    = array();

        if (!empty($search)) {
            $like      = '%' . $wpdb->esc_like($search) . '%';
            $where_sql = " WHERE (
                certificate_id LIKE %s OR 
                student_name LIKE %s OR 
                course_type LIKE %s OR 
                course_name LIKE %s OR 
                course_id LIKE %s OR 
                batch_name LIKE %s OR 
                institution LIKE %s OR 
                verification_code LIKE %s OR 
                custom_fields LIKE %s
            )";
            for ($i = 0; $i < 9; $i++) {
                $params[] = $like;
            }
        }

        $allowed_orderby = array('certificate_id', 'student_name', 'issue_date', 'course_type', 'course_name', 'institution', 'created_at');
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- Reading request parameters for list table sorting.
        $orderby         = !empty($_REQUEST['orderby']) ? sanitize_text_field(wp_unslash($_REQUEST['orderby'])) : '';

        if ($orderby && in_array($orderby, $allowed_orderby, true)) {
            // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- Reading request parameters for list table sorting.
            $raw_order    = !empty($_REQUEST['order']) ? sanitize_text_field(wp_unslash($_REQUEST['order'])) : '';
            $order        = (strtoupper($raw_order) === 'DESC') ? 'DESC' : 'ASC';
            $order_clause = " ORDER BY {$orderby} {$order}";
        } else {
            $order_clause = " ORDER BY created_at DESC";
        }

        $sql      = "SELECT * FROM {$this->table_name}{$where_sql}{$order_clause} LIMIT %d OFFSET %d";
        $params[] = $per_page;
        $params[] = $offset;

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is dynamically built with pre-whitelisted orderby/order clauses and %s placeholders.
        // phpcs:ignore
        return $wpdb->get_results($wpdb->prepare($sql, ...$params),
            ARRAY_A
        );
    }

    /**
     * Helper function for getting certificate count number from db table.
     *
     * @param string $search Search string.
     *
     * @return int
     * @since 1.0.0
     */
    public function count_certificates($search = '') {
        global $wpdb;

        $search = trim($search);

        if (!empty($search)) {
            $like = '%' . $wpdb->esc_like($search) . '%';
            $sql  = "SELECT COUNT(*) FROM {$this->table_name} WHERE (
                certificate_id LIKE %s OR 
                student_name LIKE %s OR 
                course_type LIKE %s OR 
                course_name LIKE %s OR 
                course_id LIKE %s OR 
                batch_name LIKE %s OR 
                institution LIKE %s OR 
                verification_code LIKE %s OR 
                custom_fields LIKE %s
            )";
            $params = array_fill(0, 9, $like);
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Query built with placeholders and executed via $wpdb->prepare.
            return (int) $wpdb->get_var($wpdb->prepare($sql, ...$params));
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table name is safe plugin table.
        return (int) $wpdb->get_var("SELECT COUNT(*) FROM {$this->table_name}");
    }
}