9f3d3a245a
- Remove KRW pricing calculations from all pricing tables - Simplify pricing table to store only wholesale USD prices - Simplify anvil_pricing to store only retail USD prices - Remove KRW environment variables (KRW_EXCHANGE_RATE, KRW_MARGIN_RATE) - Remove KRW functions from constants.ts - Update GPU/G8/VPU pricing repositories to match - Add Anvil tables and repositories for branded product support Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
129 lines
5.5 KiB
SQL
129 lines
5.5 KiB
SQL
-- Anvil 초기 데이터 삽입 스크립트
|
||
|
||
-- ============================================================
|
||
-- anvil_instances: 인스턴스 타입 정의
|
||
-- ============================================================
|
||
INSERT INTO anvil_instances (name, display_name, category, vcpus, memory_gb, disk_gb, transfer_tb)
|
||
VALUES
|
||
('anvil-1g-1c', 'Basic 1GB', 'vm', 1, 1, 25, 1),
|
||
('anvil-2g-1c', 'Basic 2GB', 'vm', 1, 2, 50, 2),
|
||
('anvil-4g-2c', 'Standard 4GB', 'vm', 2, 4, 80, 4),
|
||
('anvil-8g-4c', 'Standard 8GB', 'vm', 4, 8, 160, 5),
|
||
('anvil-16g-6c', 'Pro 16GB', 'vm', 6, 16, 320, 8);
|
||
|
||
-- ============================================================
|
||
-- anvil_pricing: 인스턴스별 리전별 가격 (원가 × 1.21)
|
||
-- ============================================================
|
||
|
||
-- Linode 리전 (tyo1, tyo2, osa1) - Region IDs: 1, 2, 4
|
||
INSERT INTO anvil_pricing (anvil_region_id, anvil_instance_id, hourly_price, monthly_price, hourly_price_krw, monthly_price_krw, cost_hourly, cost_monthly)
|
||
SELECT
|
||
r.id as anvil_region_id,
|
||
i.id as anvil_instance_id,
|
||
CASE i.name
|
||
WHEN 'anvil-1g-1c' THEN 0.0091 -- $0.0075 × 1.21
|
||
WHEN 'anvil-2g-1c' THEN 0.0218 -- $0.018 × 1.21
|
||
WHEN 'anvil-4g-2c' THEN 0.0436 -- $0.036 × 1.21
|
||
WHEN 'anvil-8g-4c' THEN 0.0871 -- $0.072 × 1.21
|
||
WHEN 'anvil-16g-6c' THEN 0.1742 -- $0.144 × 1.21
|
||
END as hourly_price,
|
||
CASE i.name
|
||
WHEN 'anvil-1g-1c' THEN 6 -- $5 × 1.21 = 6.05 → 6
|
||
WHEN 'anvil-2g-1c' THEN 15 -- $12 × 1.21 = 14.52 → 15
|
||
WHEN 'anvil-4g-2c' THEN 29 -- $24 × 1.21 = 29.04 → 29
|
||
WHEN 'anvil-8g-4c' THEN 58 -- $48 × 1.21 = 58.08 → 58
|
||
WHEN 'anvil-16g-6c' THEN 116 -- $96 × 1.21 = 116.16 → 116
|
||
END as monthly_price,
|
||
CASE i.name
|
||
WHEN 'anvil-1g-1c' THEN 16 -- $0.0091 × 1450 = 13.2 → 16
|
||
WHEN 'anvil-2g-1c' THEN 32 -- $0.0218 × 1450 = 31.6 → 32
|
||
WHEN 'anvil-4g-2c' THEN 63 -- $0.0436 × 1450 = 63.2 → 63
|
||
WHEN 'anvil-8g-4c' THEN 126 -- $0.0871 × 1450 = 126.3 → 126
|
||
WHEN 'anvil-16g-6c' THEN 253 -- $0.1742 × 1450 = 252.6 → 253
|
||
END as hourly_price_krw,
|
||
CASE i.name
|
||
WHEN 'anvil-1g-1c' THEN 10500 -- $6 × 1450 = 8700 → 10500
|
||
WHEN 'anvil-2g-1c' THEN 26100 -- $15 × 1450 = 21750 → 26100
|
||
WHEN 'anvil-4g-2c' THEN 52200 -- $29 × 1450 = 42050 → 52200
|
||
WHEN 'anvil-8g-4c' THEN 104300 -- $58 × 1450 = 84100 → 104300
|
||
WHEN 'anvil-16g-6c' THEN 208700 -- $116 × 1450 = 168200 → 208700
|
||
END as monthly_price_krw,
|
||
CASE i.name
|
||
WHEN 'anvil-1g-1c' THEN 0.0075
|
||
WHEN 'anvil-2g-1c' THEN 0.018
|
||
WHEN 'anvil-4g-2c' THEN 0.036
|
||
WHEN 'anvil-8g-4c' THEN 0.072
|
||
WHEN 'anvil-16g-6c' THEN 0.144
|
||
END as cost_hourly,
|
||
CASE i.name
|
||
WHEN 'anvil-1g-1c' THEN 5
|
||
WHEN 'anvil-2g-1c' THEN 12
|
||
WHEN 'anvil-4g-2c' THEN 24
|
||
WHEN 'anvil-8g-4c' THEN 48
|
||
WHEN 'anvil-16g-6c' THEN 96
|
||
END as cost_monthly
|
||
FROM anvil_regions r
|
||
CROSS JOIN anvil_instances i
|
||
WHERE r.id IN (1, 2, 4); -- Linode 리전만
|
||
|
||
-- Vultr 리전 (tyo3, osa2, sel1) - Region IDs: 3, 5, 6
|
||
INSERT INTO anvil_pricing (anvil_region_id, anvil_instance_id, hourly_price, monthly_price, hourly_price_krw, monthly_price_krw, cost_hourly, cost_monthly)
|
||
SELECT
|
||
r.id as anvil_region_id,
|
||
i.id as anvil_instance_id,
|
||
CASE i.name
|
||
WHEN 'anvil-1g-1c' THEN 0.0085 -- $0.007 × 1.21
|
||
WHEN 'anvil-2g-1c' THEN 0.0169 -- $0.014 × 1.21
|
||
WHEN 'anvil-4g-2c' THEN 0.0351 -- $0.029 × 1.21
|
||
WHEN 'anvil-8g-4c' THEN 0.0726 -- $0.06 × 1.21
|
||
WHEN 'anvil-16g-6c' THEN 0.1440 -- $0.119 × 1.21
|
||
END as hourly_price,
|
||
CASE i.name
|
||
WHEN 'anvil-1g-1c' THEN 6 -- $5 × 1.21 = 6.05 → 6
|
||
WHEN 'anvil-2g-1c' THEN 12 -- $10 × 1.21 = 12.10 → 12
|
||
WHEN 'anvil-4g-2c' THEN 24 -- $20 × 1.21 = 24.20 → 24
|
||
WHEN 'anvil-8g-4c' THEN 48 -- $40 × 1.21 = 48.40 → 48
|
||
WHEN 'anvil-16g-6c' THEN 97 -- $80 × 1.21 = 96.80 → 97
|
||
END as monthly_price,
|
||
CASE i.name
|
||
WHEN 'anvil-1g-1c' THEN 15 -- $0.0085 × 1450 = 12.3 → 15
|
||
WHEN 'anvil-2g-1c' THEN 25 -- $0.0169 × 1450 = 24.5 → 25
|
||
WHEN 'anvil-4g-2c' THEN 51 -- $0.0351 × 1450 = 50.9 → 51
|
||
WHEN 'anvil-8g-4c' THEN 105 -- $0.0726 × 1450 = 105.3 → 105
|
||
WHEN 'anvil-16g-6c' THEN 209 -- $0.1440 × 1450 = 208.8 → 209
|
||
END as hourly_price_krw,
|
||
CASE i.name
|
||
WHEN 'anvil-1g-1c' THEN 10500 -- $6 × 1450 = 8700 → 10500
|
||
WHEN 'anvil-2g-1c' THEN 21600 -- $12 × 1450 = 17400 → 21600
|
||
WHEN 'anvil-4g-2c' THEN 43200 -- $24 × 1450 = 34800 → 43200
|
||
WHEN 'anvil-8g-4c' THEN 86400 -- $48 × 1450 = 69600 → 86400
|
||
WHEN 'anvil-16g-6c' THEN 174600 -- $97 × 1450 = 140650 → 174600
|
||
END as monthly_price_krw,
|
||
CASE i.name
|
||
WHEN 'anvil-1g-1c' THEN 0.007
|
||
WHEN 'anvil-2g-1c' THEN 0.014
|
||
WHEN 'anvil-4g-2c' THEN 0.029
|
||
WHEN 'anvil-8g-4c' THEN 0.06
|
||
WHEN 'anvil-16g-6c' THEN 0.119
|
||
END as cost_hourly,
|
||
CASE i.name
|
||
WHEN 'anvil-1g-1c' THEN 5
|
||
WHEN 'anvil-2g-1c' THEN 10
|
||
WHEN 'anvil-4g-2c' THEN 20
|
||
WHEN 'anvil-8g-4c' THEN 40
|
||
WHEN 'anvil-16g-6c' THEN 80
|
||
END as cost_monthly
|
||
FROM anvil_regions r
|
||
CROSS JOIN anvil_instances i
|
||
WHERE r.id IN (3, 5, 6); -- Vultr 리전만
|
||
|
||
-- ============================================================
|
||
-- anvil_transfer_pricing: 추가 트래픽 가격 (모든 리전 동일)
|
||
-- ============================================================
|
||
INSERT INTO anvil_transfer_pricing (anvil_region_id, price_per_gb, price_per_gb_krw)
|
||
SELECT
|
||
id as anvil_region_id,
|
||
0.012 as price_per_gb, -- $0.012 원가 × 1.21 = $0.01452 → $0.012 (반올림)
|
||
21 as price_per_gb_krw -- $0.012 × 1450 = 17.4 → 21
|
||
FROM anvil_regions;
|