<?php
/**
 * Database installer / migrator using dbDelta.
 *
 * @package SuiteMkt3k
 */

namespace SuiteMkt3k;

defined( 'ABSPATH' ) || exit;

final class Installer {

	public static function install(): void {
		global $wpdb;

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

		$charset_collate = $wpdb->get_charset_collate();
		$prefix          = $wpdb->prefix . 'wmk_';

		$queries = array();

		$queries[] = "CREATE TABLE {$prefix}coupons (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			wc_coupon_id BIGINT(20) UNSIGNED NULL,
			name VARCHAR(190) NOT NULL,
			marketing_text TEXT NULL,
			format_type VARCHAR(20) NOT NULL DEFAULT 'classic',
			discount_type VARCHAR(20) NOT NULL DEFAULT 'percent',
			discount_value DECIMAL(20,6) NOT NULL DEFAULT 0,
			usage_limit INT(11) NULL,
			usage_count INT(11) NOT NULL DEFAULT 0,
			status VARCHAR(20) NOT NULL DEFAULT 'draft',
			starts_at DATETIME NULL,
			ends_at DATETIME NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY idx_status (status),
			KEY idx_dates (starts_at, ends_at),
			KEY idx_wc_coupon (wc_coupon_id)
		) {$charset_collate};";

		$queries[] = "CREATE TABLE {$prefix}coupon_designs (
			coupon_id BIGINT(20) UNSIGNED NOT NULL,
			design_json LONGTEXT NOT NULL,
			updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY  (coupon_id)
		) {$charset_collate};";

		$queries[] = "CREATE TABLE {$prefix}subscribers (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			name VARCHAR(190) NOT NULL DEFAULT '',
			email VARCHAR(190) NOT NULL,
			birth_date DATE NULL,
			source_type VARCHAR(40) NOT NULL DEFAULT 'manual',
			source_campaign_id BIGINT(20) UNSIGNED NULL,
			source_campaign_name VARCHAR(190) NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'active',
			unsubscribe_token CHAR(64) NOT NULL,
			ip_hash CHAR(64) NULL,
			locale VARCHAR(10) NULL,
			consent_at DATETIME NULL,
			subscribed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			unsubscribed_at DATETIME NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY uniq_email (email),
			UNIQUE KEY uniq_token (unsubscribe_token),
			KEY idx_status (status),
			KEY idx_source (source_type, source_campaign_id)
		) {$charset_collate};";

		$queries[] = "CREATE TABLE {$prefix}newsletters (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			name VARCHAR(190) NOT NULL,
			subject VARCHAR(255) NOT NULL DEFAULT '',
			preheader VARCHAR(255) NOT NULL DEFAULT '',
			template_key VARCHAR(60) NOT NULL DEFAULT 'classic',
			content_json LONGTEXT NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'draft',
			scheduled_at DATETIME NULL,
			sent_at DATETIME NULL,
			recipients_count INT(11) NOT NULL DEFAULT 0,
			opens_count INT(11) NOT NULL DEFAULT 0,
			clicks_count INT(11) NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY idx_status (status),
			KEY idx_scheduled (scheduled_at)
		) {$charset_collate};";

		$queries[] = "CREATE TABLE {$prefix}newsletter_recipients (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			newsletter_id BIGINT(20) UNSIGNED NOT NULL,
			subscriber_id BIGINT(20) UNSIGNED NOT NULL,
			email VARCHAR(190) NOT NULL,
			tracking_hash CHAR(64) NOT NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'queued',
			sent_at DATETIME NULL,
			opened_at DATETIME NULL,
			clicked_at DATETIME NULL,
			unsubscribed_at DATETIME NULL,
			error_message VARCHAR(255) NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY uniq_hash (tracking_hash),
			KEY idx_newsletter (newsletter_id),
			KEY idx_subscriber (subscriber_id),
			KEY idx_status (status)
		) {$charset_collate};";

		$queries[] = "CREATE TABLE {$prefix}newsletter_forms (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			name VARCHAR(190) NOT NULL,
			design_json 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)
		) {$charset_collate};";

		$queries[] = "CREATE TABLE {$prefix}abandoned_carts (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			cart_hash CHAR(64) NULL,
			wc_session_key VARCHAR(190) NULL,
			wc_order_id BIGINT(20) UNSIGNED NULL,
			user_id BIGINT(20) UNSIGNED NULL,
			email VARCHAR(190) NOT NULL,
			name VARCHAR(190) NULL,
			phone VARCHAR(60) NULL,
			cart_items_json LONGTEXT NOT NULL,
			billing_json LONGTEXT NULL,
			total DECIMAL(20,6) NOT NULL DEFAULT 0,
			currency CHAR(3) NULL,
			recovery_url TEXT NULL,
			recovery_token CHAR(64) NULL,
			recovery_coupon_code VARCHAR(60) NULL,
			last_activity_at DATETIME NOT NULL,
			recovery_email_sent_at DATETIME NULL,
			recovered_at DATETIME NULL,
			recovered_order_id BIGINT(20) UNSIGNED NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'pending',
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			UNIQUE KEY uniq_cart_hash (cart_hash),
			KEY idx_email (email),
			KEY idx_wc_order (wc_order_id),
			KEY idx_status (status),
			KEY idx_activity (last_activity_at)
		) {$charset_collate};";

		$queries[] = "CREATE TABLE {$prefix}cart_email_templates (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			name VARCHAR(190) NOT NULL,
			subject VARCHAR(255) NOT NULL DEFAULT '',
			design_json LONGTEXT NOT NULL,
			discount_percent DECIMAL(6,2) NULL,
			discount_validity_hours INT(11) NULL,
			is_default TINYINT(1) NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY  (id)
		) {$charset_collate};";

		$queries[] = "CREATE TABLE {$prefix}events (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			event_type VARCHAR(30) NOT NULL,
			entity_type VARCHAR(30) NOT NULL,
			entity_id BIGINT(20) UNSIGNED NULL,
			subscriber_id BIGINT(20) UNSIGNED NULL,
			visitor_hash CHAR(64) NULL,
			meta_json LONGTEXT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY idx_event (event_type, entity_type, entity_id),
			KEY idx_created (created_at),
			KEY idx_subscriber (subscriber_id)
		) {$charset_collate};";

		$queries[] = "CREATE TABLE {$prefix}stats_daily (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			day DATE NOT NULL,
			entity_type VARCHAR(30) NOT NULL,
			entity_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			impressions INT(11) NOT NULL DEFAULT 0,
			clicks INT(11) NOT NULL DEFAULT 0,
			conversions INT(11) NOT NULL DEFAULT 0,
			opens INT(11) NOT NULL DEFAULT 0,
			revenue DECIMAL(20,6) NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			UNIQUE KEY uniq_day_entity (day, entity_type, entity_id)
		) {$charset_collate};";

		$queries[] = "CREATE TABLE {$prefix}rate_limits (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			scope VARCHAR(60) NOT NULL,
			identifier CHAR(64) NOT NULL,
			counter INT(11) NOT NULL DEFAULT 0,
			window_start DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY uniq_scope_id (scope, identifier),
			KEY idx_window (window_start)
		) {$charset_collate};";

		$queries[] = "CREATE TABLE {$prefix}logs (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			level VARCHAR(10) NOT NULL DEFAULT 'info',
			context VARCHAR(40) NOT NULL,
			message TEXT NOT NULL,
			meta_json LONGTEXT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY idx_level (level),
			KEY idx_context (context),
			KEY idx_created (created_at)
		) {$charset_collate};";

		foreach ( $queries as $sql ) {
			dbDelta( $sql );
		}
	}

	public static function table( string $name ): string {
		global $wpdb;
		return $wpdb->prefix . 'wmk_' . $name;
	}
}
