CREATE TABLE IF NOT EXISTS `wp_tmapp_subscription_orders` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` VARCHAR(64) NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `plan_id` VARCHAR(32) NOT NULL,
  `plan_title` VARCHAR(255) DEFAULT NULL,
  `plan_days` INT UNSIGNED NOT NULL DEFAULT 0,
  `amount` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `currency` VARCHAR(10) NOT NULL DEFAULT 'IRT',
  `status` VARCHAR(32) NOT NULL DEFAULT 'created',
  `payment_provider` VARCHAR(32) DEFAULT 'zarinpal',
  `authority` VARCHAR(128) DEFAULT NULL,
  `ref_id` VARCHAR(128) DEFAULT NULL,
  `payment_url` TEXT DEFAULT NULL,
  `return_url` TEXT DEFAULT NULL,
  `site_b_start_url` TEXT DEFAULT NULL,
  `last_error` TEXT DEFAULT NULL,
  `attempts` INT UNSIGNED NOT NULL DEFAULT 0,
  `confirmed_at` DATETIME DEFAULT NULL,
  `activated_at` DATETIME DEFAULT NULL,
  `expires_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_id` (`order_id`),
  UNIQUE KEY `ref_id` (`ref_id`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`),
  KEY `authority` (`authority`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE IF NOT EXISTS `wp_tmapp_payment_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` VARCHAR(64) NOT NULL,
  `event_type` VARCHAR(64) NOT NULL,
  `message` TEXT DEFAULT NULL,
  `payload` LONGTEXT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `event_type` (`event_type`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
