-- VPN受注システム データベーススキーマ（注文管理）
-- 共有サーバ想定（InnoDB）

-- ユーザーテーブル
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NULL DEFAULT NULL COMMENT '任意。未入力OK。入力があっても認証しない',
  `password_hash` VARCHAR(255) NOT NULL COMMENT '必須',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_email` (`email`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 注文テーブル
CREATE TABLE IF NOT EXISTS `orders` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_no` VARCHAR(50) NOT NULL COMMENT '表示用注文番号',
  `user_id` INT UNSIGNED NOT NULL,
  `product_type` ENUM('VPN','LV4','LV5') NOT NULL,
  `vpn_hops` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1/2/3',
  `vpn_country_1` VARCHAR(10) NULL DEFAULT NULL COMMENT 'VPN国コード1',
  `vpn_country_2` VARCHAR(10) NULL DEFAULT NULL COMMENT 'VPN国コード2',
  `vpn_country_3` VARCHAR(10) NULL DEFAULT NULL COMMENT 'VPN国コード3',
  `rdp_country` VARCHAR(10) NULL DEFAULT NULL COMMENT 'RDP国コード',
  `rd_spec` ENUM('NanoRD','MiniRD','StandardRD','MasterRD','ProRD','UltraRD') NULL DEFAULT NULL,
  `amount_usd` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('RECEIVED','AWAITING_PAYMENT','PAYMENT_CONFIRMED','IN_PROGRESS','DELIVERED','COMPLETED') NOT NULL DEFAULT 'RECEIVED',
  `admin_note` TEXT NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_deleted_at` (`deleted_at`),
  CONSTRAINT `fk_orders_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 注文支払いテーブル（仮想通貨入金管理）
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;

-- 注文ファイルテーブル（納品ファイル）
CREATE TABLE IF NOT EXISTS `order_files` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT UNSIGNED NOT NULL,
  `original_name` VARCHAR(255) NOT NULL COMMENT '元のファイル名',
  `stored_name` VARCHAR(255) NOT NULL COMMENT '保存時のファイル名',
  `mime_type` VARCHAR(100) NULL DEFAULT NULL,
  `size_bytes` INT UNSIGNED NOT NULL,
  `uploaded_by_admin_id` INT UNSIGNED NULL DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_deleted_at` (`deleted_at`),
  CONSTRAINT `fk_order_files_order_id` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- マスタテーブル: VPN国
CREATE TABLE IF NOT EXISTS `master_vpn_countries` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(20) NOT NULL COMMENT '例: jp, au, us_atlantaなど',
  `name_ja` VARCHAR(100) NOT NULL,
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `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_code` (`code`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- マスタテーブル: RDP国
CREATE TABLE IF NOT EXISTS `master_rdp_countries` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(20) NOT NULL,
  `name_ja` VARCHAR(100) NOT NULL,
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `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_code` (`code`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- マスタテーブル: RDPスペック
CREATE TABLE IF NOT EXISTS `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;

-- マスタテーブル: VPN単体価格
CREATE TABLE IF NOT EXISTS `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;

-- マスタテーブル: LV4価格表
CREATE TABLE IF NOT EXISTS `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;

-- マスタテーブル: LV5アドオン価格
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;

-- 管理操作ログテーブル
CREATE TABLE IF NOT EXISTS `admin_order_logs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT UNSIGNED NOT NULL,
  `action` VARCHAR(50) NOT NULL COMMENT '例: status_change, file_upload',
  `detail` TEXT NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by_admin_id` INT UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_action` (`action`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_created_by_admin_id` (`created_by_admin_id`),
  CONSTRAINT `fk_admin_order_logs_order_id` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 管理者テーブル（既存のadminsテーブルと互換性を保つため、必要に応じて追加）
CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_username` (`username`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 外部キー制約の追加（order_files.uploaded_by_admin_id）
-- 注意: adminsテーブルが先に作成されている必要があります
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;

-- 外部キー制約の追加（admin_order_logs.created_by_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;
