<?php
/**
 * Database table management.
 *
 * @package AarambhaCaseManagement
 */

namespace AarambhaCaseManagement\Database;

defined( 'ABSPATH' ) || exit;

/**
 * Handles creation and naming of the plugin's custom database tables.
 */
class Database {

	/**
	 * Plugin database version.
	 */
	public const VERSION = '1.0.0';

	/**
	 * Plugin table prefix.
	 */
	private const PREFIX = 'aarambha_case_management_';

	/**
	 * Cases table.
	 */
	public const TABLE_CASES = 'cases';
	/**
	 * Case files table.
	 */
	public const TABLE_CASE_FILES = 'case_files';

	/**
	 * Get a plugin table name.
	 *
	 * @param string $table Table identifier.
	 * @return string
	 */
	public static function get_table( string $table ): string {

		global $wpdb;
		return $wpdb->prefix . self::PREFIX . $table;
	}

	/**
	 * Create plugin database tables.
	 */
	public static function create_tables(): void {

		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset_collate = $wpdb->get_charset_collate();

		/**
		 * Create Table for cases
		 */

		$cases_table = self::get_table( self::TABLE_CASES );
		$cases_sql   = "CREATE TABLE {$cases_table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            case_number VARCHAR(50) NOT NULL,
            customer_name VARCHAR(255) NOT NULL,
            customer_email VARCHAR(255) NOT NULL,
            phone VARCHAR(50) DEFAULT '',
            subject VARCHAR(255) NOT NULL,
            description LONGTEXT NOT NULL,
            status VARCHAR(30) NOT NULL DEFAULT 'pending',
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            KEY case_number (case_number),
            KEY customer_email (customer_email),
            KEY status (status)
        ) {$charset_collate};";
		dbDelta( $cases_sql );

		/**
		 * Create Table for files
		 */

		$files_table = self::get_table( self::TABLE_CASE_FILES );
		$files_sql   = "CREATE TABLE {$files_table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            case_id BIGINT UNSIGNED NOT NULL,
            attachment_id BIGINT UNSIGNED NOT NULL,
            created_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            KEY case_id (case_id),
            KEY attachment_id (attachment_id)
        ) {$charset_collate};";
		dbDelta( $files_sql );
		update_option( 'aarambha_case_management_db_version', self::VERSION );
	}
}