<?php
/**
 * Database Schema Definitions
 *
 * @package Airy_Frontend_Forms
 */

// Exit if accessed directly.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Database schema definitions
 *
 * @package Airy_Frontend_Forms
 */
class AFFCF_Schema {

	/**
	 * Get table names
	 *
	 * @return array Table names.
	 */
	public static function get_table_names() {
		global $wpdb;

		return array(
			'forms'       => $wpdb->prefix . 'affcf_forms',
			'drafts'      => $wpdb->prefix . 'affcf_drafts',
			'submissions' => $wpdb->prefix . 'affcf_submissions',
			'rejections'  => $wpdb->prefix . 'affcf_rejections',
		);
	}

	/**
	 * Get forms table schema
	 *
	 * @return string SQL CREATE TABLE statement.
	 */
	public static function get_forms_table_schema() {
		global $wpdb;
		$table_name      = self::get_table_names()['forms'];
		$charset_collate = $wpdb->get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS `{$table_name}` (
			`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			`form_name` VARCHAR(255) NOT NULL COMMENT 'User-friendly form name',
			`form_slug` VARCHAR(255) NOT NULL COMMENT 'URL-friendly slug',
			`shortcode` VARCHAR(50) NOT NULL COMMENT 'Auto-generated shortcode',
			`post_type` VARCHAR(50) NOT NULL COMMENT 'Associated CPT',
			`status` ENUM('active', 'inactive') DEFAULT 'active' COMMENT 'Form status',
			`field_groups` LONGTEXT DEFAULT NULL COMMENT 'JSON field groups config',
			`default_fields` LONGTEXT DEFAULT NULL COMMENT 'JSON default fields config',
			`taxonomies` LONGTEXT DEFAULT NULL COMMENT 'JSON taxonomies config',
			`settings` LONGTEXT DEFAULT NULL COMMENT 'JSON form settings',
			`created_by` BIGINT(20) UNSIGNED DEFAULT 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 `shortcode` (`shortcode`),
			UNIQUE KEY `form_slug` (`form_slug`),
			KEY `idx_post_type` (`post_type`),
			KEY `idx_status` (`status`),
			KEY `idx_created_by` (`created_by`)
		) {$charset_collate};";
	}

	/**
	 * Get drafts table schema
	 *
	 * @return string SQL CREATE TABLE statement.
	 */
	public static function get_drafts_table_schema() {
		global $wpdb;
		$table_name      = self::get_table_names()['drafts'];
		$forms_table     = self::get_table_names()['forms'];
		$charset_collate = $wpdb->get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS `{$table_name}` (
			`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			`user_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'User who created draft',
			`form_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'Associated form ID',
			`post_id` BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'NULL for new, ID for edit',
			`draft_data` LONGTEXT NOT NULL COMMENT 'JSON form data',
			`files_data` LONGTEXT DEFAULT NULL COMMENT 'JSON file paths',
			`last_saved` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (`id`),
			UNIQUE KEY `unique_user_form_post` (`user_id`, `form_id`, `post_id`),
			KEY `idx_user_form` (`user_id`, `form_id`),
			KEY `idx_post_id` (`post_id`),
			KEY `idx_last_saved` (`last_saved`),
			FOREIGN KEY (`form_id`) REFERENCES `{$forms_table}`(`id`) ON DELETE CASCADE
		) {$charset_collate};";
	}

	/**
	 * Get submissions table schema
	 *
	 * @return string SQL CREATE TABLE statement.
	 */
	public static function get_submissions_table_schema() {
		global $wpdb;
		$table_name      = self::get_table_names()['submissions'];
		$forms_table     = self::get_table_names()['forms'];
		$charset_collate = $wpdb->get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS `{$table_name}` (
			`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			`user_id` BIGINT(20) UNSIGNED NOT NULL,
			`form_id` BIGINT(20) UNSIGNED NOT NULL,
			`post_id` BIGINT(20) UNSIGNED NOT NULL,
			`submission_type` ENUM('new', 'edit') DEFAULT 'new',
			`submission_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			`ip_address` VARCHAR(45) DEFAULT NULL,
			`user_agent` TEXT DEFAULT NULL,
			`captcha_score` DECIMAL(3,2) DEFAULT NULL,
			PRIMARY KEY (`id`),
			KEY `idx_user_id` (`user_id`),
			KEY `idx_form_id` (`form_id`),
			KEY `idx_post_id` (`post_id`),
			KEY `idx_submission_date` (`submission_date`),
			KEY `idx_submission_type` (`submission_type`),
			FOREIGN KEY (`form_id`) REFERENCES `{$forms_table}`(`id`) ON DELETE CASCADE
		) {$charset_collate};";
	}

	/**
	 * Get rejections table schema
	 *
	 * @return string SQL CREATE TABLE statement.
	 */
	public static function get_rejections_table_schema() {
		global $wpdb;
		$table_name      = self::get_table_names()['rejections'];
		$charset_collate = $wpdb->get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS `{$table_name}` (
			`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			`post_id` BIGINT(20) UNSIGNED NOT NULL,
			`rejected_by` BIGINT(20) UNSIGNED NOT NULL,
			`rejection_reason` TEXT DEFAULT NULL,
			`rejected_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			`notified` TINYINT(1) DEFAULT 0,
			PRIMARY KEY (`id`),
			KEY `idx_post_id` (`post_id`),
			KEY `idx_rejected_by` (`rejected_by`),
			KEY `idx_rejected_at` (`rejected_at`)
		) {$charset_collate};";
	}
}