-- マイグレーション: schema.sql から ddl_orders.sql への差分適用
-- 既存のschema.sqlが実行済みの場合、このファイルを実行してください

-- ============================================
-- 1. usersテーブルの変更
-- ============================================
-- emailをNULL可に変更、UNIQUE制約を削除
ALTER TABLE `users` 
  MODIFY COLUMN `email` VARCHAR(255) NULL DEFAULT NULL COMMENT '任意。未入力OK。入力があっても認証しない';

-- UNIQUE制約を削除（既存のUNIQUE KEY idx_emailを削除して、通常のKEYに変更）
ALTER TABLE `users` 
  DROP INDEX `idx_email`,
  ADD KEY `idx_email` (`email`);

-- ============================================
-- 2. ordersテーブルの大幅な構造変更
-- ============================================
-- 注意: 既存データがある場合は、事前にバックアップを取ってください

-- 既存のカラムを削除（外部キー制約がある場合は先に削除）
ALTER TABLE `orders`
  DROP COLUMN `order_number`,
  DROP COLUMN `order_status`,
  DROP COLUMN `order_items`,
  DROP COLUMN `total_amount`,
  DROP COLUMN `payment_address_eth`,
  DROP COLUMN `payment_address_usdt`,
  DROP COLUMN `admin_memo`;

-- 新しいカラムを追加
ALTER TABLE `orders`
  ADD COLUMN `order_no` VARCHAR(50) NOT NULL COMMENT '表示用注文番号' AFTER `id`,
  ADD COLUMN `product_type` ENUM('VPN','LV4','LV5') NOT NULL AFTER `user_id`,
  ADD COLUMN `vpn_hops` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1/2/3' AFTER `product_type`,
  ADD COLUMN `vpn_country_1` VARCHAR(10) NULL DEFAULT NULL COMMENT 'VPN国コード1' AFTER `vpn_hops`,
  ADD COLUMN `vpn_country_2` VARCHAR(10) NULL DEFAULT NULL COMMENT 'VPN国コード2' AFTER `vpn_country_1`,
  ADD COLUMN `vpn_country_3` VARCHAR(10) NULL DEFAULT NULL COMMENT 'VPN国コード3' AFTER `vpn_country_2`,
  ADD COLUMN `rdp_country` VARCHAR(10) NULL DEFAULT NULL COMMENT 'RDP国コード' AFTER `vpn_country_3`,
  ADD COLUMN `rd_spec` ENUM('NanoRD','MiniRD','StandardRD','MasterRD','ProRD','UltraRD') NULL DEFAULT NULL AFTER `rdp_country`,
  ADD COLUMN `amount_usd` DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER `rd_spec`,
  ADD COLUMN `status` ENUM('RECEIVED','AWAITING_PAYMENT','PAYMENT_CONFIRMED','IN_PROGRESS','DELIVERED','COMPLETED') NOT NULL DEFAULT 'RECEIVED' AFTER `amount_usd`,
  ADD COLUMN `admin_note` TEXT NULL DEFAULT NULL AFTER `status`;

-- インデックスの変更
ALTER TABLE `orders`
  DROP INDEX `idx_order_number`,
  ADD UNIQUE KEY `idx_order_no` (`order_no`),
  DROP INDEX `idx_order_status`,
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_created_at` (`created_at`);

-- order_noに一時的な値を設定（既存データがある場合）
-- UPDATE orders SET order_no = CONCAT('ORD', LPAD(id, 8, '0')) WHERE order_no = '' OR order_no IS NULL;

-- ============================================
-- 3. order_paymentsテーブル（新規作成）
-- ============================================
CREATE TABLE IF NOT EXISTS `order_payments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT UNSIGNED NOT NULL,
  `currency` ENUM('ETH','USDT_ERC20','USDT_TRC20','BTC','TRX') NOT NULL,
  `deposit_address` VARCHAR(128) NOT NULL,
  `expected_amount` DECIMAL(18,8) NOT NULL COMMENT 'crypto数量',
  `received_amount` DECIMAL(18,8) NOT NULL DEFAULT 0.00000000,
  `txid` VARCHAR(128) NULL DEFAULT NULL COMMENT 'トランザクションID',
  `status` ENUM('UNPAID','CHECKING','PAID','OVERPAID','UNDERPAID') NOT NULL DEFAULT 'UNPAID',
  `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_order_id` (`order_id`),
  KEY `idx_currency` (`currency`),
  KEY `idx_status` (`status`),
  KEY `idx_deposit_address` (`deposit_address`),
  CONSTRAINT `fk_order_payments_order_id` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 4. order_filesテーブルの構造変更
-- ============================================
-- 既存のカラムを削除
ALTER TABLE `order_files`
  DROP COLUMN `file_name`,
  DROP COLUMN `file_path`,
  DROP COLUMN `file_size`,
  DROP COLUMN `download_count`,
  DROP COLUMN `uploaded_at`,
  DROP COLUMN `updated_at`;

-- 新しいカラムを追加
ALTER TABLE `order_files`
  ADD COLUMN `original_name` VARCHAR(255) NOT NULL COMMENT '元のファイル名' AFTER `order_id`,
  ADD COLUMN `stored_name` VARCHAR(255) NOT NULL COMMENT '保存時のファイル名' AFTER `original_name`,
  ADD COLUMN `mime_type` VARCHAR(100) NULL DEFAULT NULL AFTER `stored_name`,
  ADD COLUMN `size_bytes` INT UNSIGNED NOT NULL AFTER `mime_type`,
  ADD COLUMN `uploaded_by_admin_id` INT UNSIGNED NULL DEFAULT NULL AFTER `size_bytes`,
  ADD COLUMN `is_active` TINYINT(1) NOT NULL DEFAULT 1 AFTER `uploaded_by_admin_id`;

-- インデックスの追加
ALTER TABLE `order_files`
  ADD KEY `idx_is_active` (`is_active`);

-- 外部キー制約の追加（後で実行）
-- ALTER TABLE `order_files` 
--   ADD CONSTRAINT `fk_order_files_uploaded_by_admin_id` 
--   FOREIGN KEY (`uploaded_by_admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL;

-- ============================================
-- 5. admin_order_logsテーブルの構造変更
-- ============================================
-- 既存のカラムを削除
ALTER TABLE `admin_order_logs`
  DROP COLUMN `admin_id`,
  DROP COLUMN `old_status`,
  DROP COLUMN `new_status`,
  DROP COLUMN `notes`;

-- 新しいカラムを追加
ALTER TABLE `admin_order_logs`
  ADD COLUMN `detail` TEXT NULL DEFAULT NULL AFTER `action`,
  ADD COLUMN `created_by_admin_id` INT UNSIGNED NULL DEFAULT NULL AFTER `created_at`;

-- インデックスの変更
ALTER TABLE `admin_order_logs`
  DROP INDEX `idx_admin_id`,
  ADD KEY `idx_created_by_admin_id` (`created_by_admin_id`);

-- 外部キー制約の変更（既存のfk_admin_order_logs_admin_idを削除して新規追加）
ALTER TABLE `admin_order_logs`
  DROP FOREIGN KEY `fk_admin_order_logs_admin_id`;

ALTER TABLE `admin_order_logs`
  ADD CONSTRAINT `fk_admin_order_logs_created_by_admin_id` 
  FOREIGN KEY (`created_by_admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL;

-- ============================================
-- 6. master_vpn_countriesテーブルの変更
-- ============================================
-- カラム名と型の変更
ALTER TABLE `master_vpn_countries`
  CHANGE COLUMN `country_code` `code` VARCHAR(20) NOT NULL COMMENT '例: jp, au, us_atlantaなど',
  CHANGE COLUMN `country_name` `name_ja` VARCHAR(100) NOT NULL,
  CHANGE COLUMN `display_order` `sort_order` INT UNSIGNED NOT NULL DEFAULT 0;

-- インデックスの変更
ALTER TABLE `master_vpn_countries`
  DROP INDEX `idx_country_code`,
  ADD UNIQUE KEY `idx_code` (`code`),
  DROP INDEX `idx_is_active`,
  ADD KEY `idx_is_active` (`is_active`),
  ADD KEY `idx_sort_order` (`sort_order`);

-- ============================================
-- 7. master_rdp_countriesテーブルの変更
-- ============================================
-- カラム名と型の変更
ALTER TABLE `master_rdp_countries`
  CHANGE COLUMN `country_code` `code` VARCHAR(20) NOT NULL,
  CHANGE COLUMN `country_name` `name_ja` VARCHAR(100) NOT NULL,
  CHANGE COLUMN `display_order` `sort_order` INT UNSIGNED NOT NULL DEFAULT 0;

-- インデックスの変更
ALTER TABLE `master_rdp_countries`
  DROP INDEX `idx_country_code`,
  ADD UNIQUE KEY `idx_code` (`code`),
  DROP INDEX `idx_is_active`,
  ADD KEY `idx_is_active` (`is_active`),
  ADD KEY `idx_sort_order` (`sort_order`);

-- ============================================
-- 8. master_rd_specsテーブルの完全な再構築
-- ============================================
-- 注意: 既存データがある場合は、事前にバックアップを取ってください

-- 既存の外部キー制約を削除（master_prices_lv4から参照されている場合）
ALTER TABLE `master_prices_lv4`
  DROP FOREIGN KEY `fk_prices_lv4_rd_spec`;

-- テーブルを削除して再作成

CREATE TABLE `master_rd_specs` (
  `spec_key` ENUM('NanoRD','MiniRD','StandardRD','MasterRD','ProRD','UltraRD') NOT NULL,
  `cpu_cores` INT UNSIGNED NOT NULL,
  `ram_gb` INT UNSIGNED NOT NULL,
  `storage_gb` INT UNSIGNED NOT NULL,
  `port_mbps` INT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`spec_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 9. master_prices_vpnテーブルの完全な再構築
-- ============================================
-- 注意: 既存データがある場合は、事前にバックアップを取ってください

-- 既存の外部キー制約を削除
ALTER TABLE `master_prices_vpn`
  DROP FOREIGN KEY `fk_prices_vpn_country`;

-- テーブルを削除して再作成

CREATE TABLE `master_prices_vpn` (
  `hops` INT UNSIGNED NOT NULL COMMENT '1/2/3',
  `price_usd` DECIMAL(10,2) NOT NULL COMMENT '30/45/60',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`hops`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 10. master_prices_lv4テーブルの完全な再構築
-- ============================================
-- 注意: 既存データがある場合は、事前にバックアップを取ってください

-- 既存の外部キー制約を削除
ALTER TABLE `master_prices_lv4`
  DROP FOREIGN KEY `fk_prices_lv4_vpn_country`,
  DROP FOREIGN KEY `fk_prices_lv4_rdp_country`;

-- テーブルを削除して再作成

CREATE TABLE `master_prices_lv4` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `rd_spec` ENUM('NanoRD','MiniRD','StandardRD','MasterRD','ProRD','UltraRD') NOT NULL,
  `hops` INT UNSIGNED NOT NULL COMMENT '1/2/3',
  `price_usd` DECIMAL(10,2) 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`),
  UNIQUE KEY `idx_rd_spec_hops` (`rd_spec`, `hops`),
  KEY `idx_rd_spec` (`rd_spec`),
  KEY `idx_hops` (`hops`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 11. master_lv5_addonテーブル（新規作成）
-- ============================================
CREATE TABLE IF NOT EXISTS `master_lv5_addon` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `addon_usd` DECIMAL(10,2) NOT NULL DEFAULT 50.00,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 12. 外部キー制約の追加（order_files.uploaded_by_admin_id）
-- ============================================
ALTER TABLE `order_files` 
  ADD CONSTRAINT `fk_order_files_uploaded_by_admin_id` 
  FOREIGN KEY (`uploaded_by_admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL;

-- ============================================
-- マイグレーション完了
-- ============================================
-- 注意事項:
-- 1. 既存データがある場合は、事前にバックアップを取ってください
-- 2. ordersテーブルのorder_noには一時的な値が設定される可能性があります
-- 3. マスタテーブル（master_rd_specs, master_prices_vpn, master_prices_lv4）は
--    既存データが失われるため、必要に応じてデータを再投入してください
