<?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();
    }

    /**
     * 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
        if ($wpdb->get_var("SHOW TABLES LIKE '{$this->table_name}'") !== $this->table_name) {
            return;
        }
        $found = $wpdb->get_results("SHOW COLUMNS FROM `{$this->table_name}` LIKE 'course_id'");
        if (!empty($found)) {
            return;
        }
        $wpdb->query("ALTER TABLE `{$this->table_name}` ADD COLUMN course_id varchar(100) DEFAULT NULL AFTER course_name");
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    }

    /**
     * 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(20) NOT NULL,
            student_name varchar(100) NOT NULL,
            birth_date date NOT NULL,
            course_type varchar(50) NOT NULL,
            course_name varchar(100) NOT NULL,
            course_id varchar(100) DEFAULT NULL,
            batch_name varchar(100) DEFAULT NULL,
            institution varchar(100) NOT NULL,
            issue_date date NOT NULL,
            expiry_date date DEFAULT NULL,
            verification_code varchar(32) NOT NULL,
            additional_data text DEFAULT NULL,
            custom_fields longtext 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();

        if ($wpdb->get_var("SHOW TABLES LIKE '$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);
        }
    }

    /**
     * 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();

        if ($wpdb->get_var("SHOW TABLES LIKE '$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 1200,
            canvas_height int(11) DEFAULT 800,
            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);
        }
    }

    /**
     * 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 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) {
        global $wpdb;

        $offset = ($page_number - 1) * $per_page;
        // phpcs:ignore
        $sql = "SELECT * FROM {$this->table_name}";

        // phpcs:ignore
        if (!empty($_REQUEST['orderby'])) {
            // phpcs:ignore
            $sql .= ' ORDER BY ' . esc_sql(sanitize_text_field(wp_unslash($_REQUEST['orderby'])));
            // phpcs:ignore
            $sql .= !empty($_REQUEST['order']) ? ' ' . esc_sql(sanitize_text_field(wp_unslash($_REQUEST['order']))) : ' ASC';
        } else {
            // phpcs:ignore
            $sql .= ' ORDER BY created_at DESC';
        }
        // phpcs:ignore
        $sql .= " LIMIT %d OFFSET %d";

        // phpcs:ignore
        return $wpdb->get_results(
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Already prepared above
            $wpdb->prepare($sql, $per_page, $offset),
            ARRAY_A
        );
    }

    /**
     * Helper function for getting certificate count number from db table.
     *
     * @return null | string
     * @since 1.0.0
     */
    public function count_certificates() {
        global $wpdb;
        // phpcs:ignore
        return $wpdb->get_var("SELECT COUNT(*) FROM {$this->table_name}");
    }
}