-- RaseedNow upgrade: category images and encrypted account inventory
-- Import this file into the existing selected database in phpMyAdmin.
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE `categories`
  ADD COLUMN `image_path` VARCHAR(255) NULL AFTER `icon`;

CREATE TABLE `account_credentials` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_variant_id` BIGINT UNSIGNED NOT NULL,
  `encrypted_username` TEXT NOT NULL,
  `encrypted_password` TEXT NOT NULL,
  `username_fingerprint` VARCHAR(255) NOT NULL,
  `status` VARCHAR(255) NOT NULL DEFAULT 'available',
  `reserved_order_id` BIGINT UNSIGNED NULL,
  `sold_order_item_id` BIGINT UNSIGNED NULL,
  `imported_by` BIGINT UNSIGNED NULL,
  `batch_reference` VARCHAR(255) NULL,
  `note` TEXT NULL,
  `reserved_at` DATETIME NULL,
  `sold_at` DATETIME NULL,
  `voided_at` DATETIME NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `account_credentials_product_username_unique` (`product_variant_id`,`username_fingerprint`),
  KEY `account_credentials_status_index` (`status`),
  KEY `account_credentials_batch_reference_index` (`batch_reference`),
  KEY `account_credentials_reserved_order_id_index` (`reserved_order_id`),
  KEY `account_credentials_sold_order_item_id_index` (`sold_order_item_id`),
  KEY `account_credentials_imported_by_index` (`imported_by`),
  CONSTRAINT `account_credentials_product_variant_id_foreign` FOREIGN KEY (`product_variant_id`) REFERENCES `product_variants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `account_credentials_reserved_order_id_foreign` FOREIGN KEY (`reserved_order_id`) REFERENCES `orders` (`id`) ON DELETE SET NULL,
  CONSTRAINT `account_credentials_sold_order_item_id_foreign` FOREIGN KEY (`sold_order_item_id`) REFERENCES `order_items` (`id`) ON DELETE SET NULL,
  CONSTRAINT `account_credentials_imported_by_foreign` FOREIGN KEY (`imported_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `migrations` (`id`,`migration`,`batch`) VALUES
(50,'2026_08_01_000003_add_category_images_and_account_inventory',7);

SET FOREIGN_KEY_CHECKS=1;