-- Cloud Server Pricing Database Schema
-- SQLite/Cloudflare D1 Compatible
-- ISO 8601 datetime format: YYYY-MM-DD HH:MM:SS

-- ============================================================
-- Table: providers
-- Description: Cloud provider information
-- ============================================================
CREATE TABLE IF NOT EXISTS providers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE, -- linode, vultr, aws, etc.
  display_name TEXT NOT NULL,
  api_base_url TEXT,
  last_sync_at TEXT, -- ISO 8601: YYYY-MM-DD HH:MM:SS
  sync_status TEXT NOT NULL DEFAULT 'pending' CHECK (sync_status IN ('pending', 'syncing', 'success', 'error')),
  sync_error TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Index for status queries
CREATE INDEX IF NOT EXISTS idx_providers_sync_status ON providers(sync_status);
CREATE INDEX IF NOT EXISTS idx_providers_name ON providers(name);

-- ============================================================
-- Table: regions
-- Description: Provider regions and availability zones
-- ============================================================
CREATE TABLE IF NOT EXISTS regions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  provider_id INTEGER NOT NULL,
  region_code TEXT NOT NULL, -- e.g., us-east, ap-south
  region_name TEXT NOT NULL, -- e.g., "US East (Newark)"
  country_code TEXT, -- ISO 3166-1 alpha-2
  latitude REAL,
  longitude REAL,
  available INTEGER NOT NULL DEFAULT 1, -- boolean: 1=true, 0=false
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE CASCADE,
  UNIQUE(provider_id, region_code)
);

-- Indexes for foreign key and queries
CREATE INDEX IF NOT EXISTS idx_regions_provider_id ON regions(provider_id);
CREATE INDEX IF NOT EXISTS idx_regions_available ON regions(available);
CREATE INDEX IF NOT EXISTS idx_regions_country_code ON regions(country_code);

-- ============================================================
-- Table: instance_types
-- Description: VM instance specifications across providers
-- ============================================================
CREATE TABLE IF NOT EXISTS instance_types (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  provider_id INTEGER NOT NULL,
  instance_id TEXT NOT NULL, -- provider's instance identifier
  instance_name TEXT NOT NULL, -- display name
  vcpu INTEGER NOT NULL,
  memory_mb INTEGER NOT NULL,
  storage_gb INTEGER NOT NULL,
  transfer_tb REAL, -- data transfer limit
  network_speed_gbps REAL,
  gpu_count INTEGER DEFAULT 0,
  gpu_type TEXT, -- e.g., "NVIDIA A100"
  instance_family TEXT CHECK (instance_family IN ('general', 'compute', 'memory', 'storage', 'gpu')),
  metadata TEXT, -- JSON for additional provider-specific data
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE CASCADE,
  UNIQUE(provider_id, instance_id)
);

-- Indexes for filtering and sorting
CREATE INDEX IF NOT EXISTS idx_instance_types_provider_id ON instance_types(provider_id);
CREATE INDEX IF NOT EXISTS idx_instance_types_vcpu ON instance_types(vcpu);
CREATE INDEX IF NOT EXISTS idx_instance_types_memory_mb ON instance_types(memory_mb);
CREATE INDEX IF NOT EXISTS idx_instance_types_instance_family ON instance_types(instance_family);
CREATE INDEX IF NOT EXISTS idx_instance_types_gpu_count ON instance_types(gpu_count);

-- Composite index for recommendation service query pattern
-- Optimizes: WHERE provider_id = ? AND memory_mb >= ? AND vcpu >= ?
CREATE INDEX IF NOT EXISTS idx_instance_types_specs_filter
ON instance_types(provider_id, memory_mb, vcpu);

-- ============================================================
-- Table: pricing
-- Description: Region-specific pricing for instance types
-- ============================================================
CREATE TABLE IF NOT EXISTS pricing (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  instance_type_id INTEGER NOT NULL,
  region_id INTEGER NOT NULL,
  hourly_price REAL NOT NULL,
  monthly_price REAL NOT NULL,
  hourly_price_krw REAL,
  monthly_price_krw REAL,
  currency TEXT NOT NULL DEFAULT 'USD',
  available INTEGER NOT NULL DEFAULT 1, -- boolean: 1=true, 0=false
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (instance_type_id) REFERENCES instance_types(id) ON DELETE CASCADE,
  FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE CASCADE,
  UNIQUE(instance_type_id, region_id)
);

-- Indexes for price queries and filtering
CREATE INDEX IF NOT EXISTS idx_pricing_instance_type_id ON pricing(instance_type_id);
CREATE INDEX IF NOT EXISTS idx_pricing_region_id ON pricing(region_id);
CREATE INDEX IF NOT EXISTS idx_pricing_hourly_price ON pricing(hourly_price);
CREATE INDEX IF NOT EXISTS idx_pricing_monthly_price ON pricing(monthly_price);
CREATE INDEX IF NOT EXISTS idx_pricing_available ON pricing(available);

-- Composite partial indexes for optimized price sorting with availability filter
-- These optimize the most common query pattern: filtering by available=1 and sorting by price
-- Partial indexes reduce index size by only indexing available instances
CREATE INDEX IF NOT EXISTS idx_pricing_available_hourly_price
ON pricing(available, hourly_price)
WHERE available = 1;

CREATE INDEX IF NOT EXISTS idx_pricing_available_monthly_price
ON pricing(available, monthly_price)
WHERE available = 1;

-- ============================================================
-- Table: price_history
-- Description: Historical price tracking for trend analysis
-- ============================================================
CREATE TABLE IF NOT EXISTS price_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  pricing_id INTEGER NOT NULL,
  hourly_price REAL NOT NULL,
  monthly_price REAL NOT NULL,
  recorded_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (pricing_id) REFERENCES pricing(id) ON DELETE CASCADE
);

-- Indexes for time-series queries
CREATE INDEX IF NOT EXISTS idx_price_history_pricing_id ON price_history(pricing_id);
CREATE INDEX IF NOT EXISTS idx_price_history_recorded_at ON price_history(recorded_at);
CREATE INDEX IF NOT EXISTS idx_price_history_pricing_recorded ON price_history(pricing_id, recorded_at DESC);

-- ============================================================
-- Triggers: Auto-update updated_at timestamp
-- ============================================================

CREATE TRIGGER IF NOT EXISTS update_providers_updated_at
AFTER UPDATE ON providers
FOR EACH ROW
BEGIN
  UPDATE providers SET updated_at = datetime('now') WHERE id = NEW.id;
END;

CREATE TRIGGER IF NOT EXISTS update_regions_updated_at
AFTER UPDATE ON regions
FOR EACH ROW
BEGIN
  UPDATE regions SET updated_at = datetime('now') WHERE id = NEW.id;
END;

CREATE TRIGGER IF NOT EXISTS update_instance_types_updated_at
AFTER UPDATE ON instance_types
FOR EACH ROW
BEGIN
  UPDATE instance_types SET updated_at = datetime('now') WHERE id = NEW.id;
END;

CREATE TRIGGER IF NOT EXISTS update_pricing_updated_at
AFTER UPDATE ON pricing
FOR EACH ROW
BEGIN
  UPDATE pricing SET updated_at = datetime('now') WHERE id = NEW.id;
END;

-- ============================================================
-- Triggers: Price history tracking
-- ============================================================

-- Record price changes automatically
CREATE TRIGGER IF NOT EXISTS track_price_changes
AFTER UPDATE OF hourly_price, monthly_price ON pricing
FOR EACH ROW
WHEN OLD.hourly_price != NEW.hourly_price OR OLD.monthly_price != NEW.monthly_price
BEGIN
  INSERT INTO price_history (pricing_id, hourly_price, monthly_price, recorded_at)
  VALUES (NEW.id, NEW.hourly_price, NEW.monthly_price, datetime('now'));
END;

-- Record initial price on creation
CREATE TRIGGER IF NOT EXISTS track_initial_price
AFTER INSERT ON pricing
FOR EACH ROW
BEGIN
  INSERT INTO price_history (pricing_id, hourly_price, monthly_price, recorded_at)
  VALUES (NEW.id, NEW.hourly_price, NEW.monthly_price, datetime('now'));
END;

-- ============================================================
-- Table: gpu_instances
-- Description: GPU-specific instance types for specialized workloads
-- ============================================================
CREATE TABLE IF NOT EXISTS gpu_instances (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  provider_id INTEGER NOT NULL,
  instance_id TEXT NOT NULL, -- provider's instance identifier
  instance_name TEXT NOT NULL, -- display name
  vcpu INTEGER NOT NULL,
  memory_mb INTEGER NOT NULL,
  storage_gb INTEGER NOT NULL,
  transfer_tb REAL, -- data transfer limit
  network_speed_gbps REAL,
  gpu_count INTEGER NOT NULL CHECK (gpu_count > 0),
  gpu_type TEXT NOT NULL, -- e.g., "NVIDIA A100", "NVIDIA RTX6000"
  gpu_memory_gb INTEGER, -- GPU memory per GPU in GB
  metadata TEXT, -- JSON for additional provider-specific data
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE CASCADE,
  UNIQUE(provider_id, instance_id)
);

-- Indexes for GPU instance queries
CREATE INDEX IF NOT EXISTS idx_gpu_instances_provider_id ON gpu_instances(provider_id);
CREATE INDEX IF NOT EXISTS idx_gpu_instances_gpu_type ON gpu_instances(gpu_type);
CREATE INDEX IF NOT EXISTS idx_gpu_instances_gpu_count ON gpu_instances(gpu_count);
CREATE INDEX IF NOT EXISTS idx_gpu_instances_provider_type ON gpu_instances(provider_id, gpu_type);

-- ============================================================
-- Table: gpu_pricing
-- Description: Region-specific pricing for GPU instance types
-- ============================================================
CREATE TABLE IF NOT EXISTS gpu_pricing (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  gpu_instance_id INTEGER NOT NULL,
  region_id INTEGER NOT NULL,
  hourly_price REAL NOT NULL,
  monthly_price REAL NOT NULL,
  hourly_price_krw REAL,
  monthly_price_krw REAL,
  currency TEXT NOT NULL DEFAULT 'USD',
  available INTEGER NOT NULL DEFAULT 1, -- boolean: 1=true, 0=false
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (gpu_instance_id) REFERENCES gpu_instances(id) ON DELETE CASCADE,
  FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE CASCADE,
  UNIQUE(gpu_instance_id, region_id)
);

-- Indexes for GPU pricing queries
CREATE INDEX IF NOT EXISTS idx_gpu_pricing_instance_id ON gpu_pricing(gpu_instance_id);
CREATE INDEX IF NOT EXISTS idx_gpu_pricing_region_id ON gpu_pricing(region_id);
CREATE INDEX IF NOT EXISTS idx_gpu_pricing_hourly_price ON gpu_pricing(hourly_price);
CREATE INDEX IF NOT EXISTS idx_gpu_pricing_monthly_price ON gpu_pricing(monthly_price);
CREATE INDEX IF NOT EXISTS idx_gpu_pricing_available ON gpu_pricing(available);

-- ============================================================
-- Composite Indexes: Query Performance Optimization
-- Description: Multi-column indexes to optimize common query patterns
-- ============================================================

-- Composite index for instance_types filtering and queries
-- Optimizes: WHERE provider_id = ? AND instance_family = ? AND vcpu >= ? AND memory_mb >= ?
-- Used in: Main instance query with provider, family, and spec filters
CREATE INDEX IF NOT EXISTS idx_instance_types_provider_family_specs
ON instance_types(provider_id, instance_family, vcpu, memory_mb);

-- Composite index for pricing queries with sorting
-- Optimizes: WHERE instance_type_id = ? AND region_id = ? ORDER BY hourly_price
-- Used in: Main pricing query with JOIN on instance_types and regions, sorted by price
CREATE INDEX IF NOT EXISTS idx_pricing_instance_region_price
ON pricing(instance_type_id, region_id, hourly_price);

-- Composite index for region lookups by provider
-- Optimizes: WHERE provider_id = ? AND region_code = ?
-- Used in: Region filtering in main instance query
CREATE INDEX IF NOT EXISTS idx_regions_provider_code
ON regions(provider_id, region_code);

-- ============================================================
-- Triggers: GPU table auto-update timestamps
-- ============================================================

CREATE TRIGGER IF NOT EXISTS update_gpu_instances_updated_at
AFTER UPDATE ON gpu_instances
FOR EACH ROW
BEGIN
  UPDATE gpu_instances SET updated_at = datetime('now') WHERE id = NEW.id;
END;

CREATE TRIGGER IF NOT EXISTS update_gpu_pricing_updated_at
AFTER UPDATE ON gpu_pricing
FOR EACH ROW
BEGIN
  UPDATE gpu_pricing SET updated_at = datetime('now') WHERE id = NEW.id;
END;

-- ============================================================
-- Table: g8_instances
-- Description: G8 generation Dedicated instances
-- ============================================================
CREATE TABLE IF NOT EXISTS g8_instances (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  provider_id INTEGER NOT NULL,
  instance_id TEXT NOT NULL, -- provider's instance identifier
  instance_name TEXT NOT NULL, -- display name
  vcpu INTEGER NOT NULL,
  memory_mb INTEGER NOT NULL,
  storage_gb INTEGER NOT NULL,
  transfer_tb REAL, -- data transfer limit
  network_speed_gbps REAL,
  metadata TEXT, -- JSON for additional provider-specific data
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE CASCADE,
  UNIQUE(provider_id, instance_id)
);

-- Indexes for G8 instance queries
CREATE INDEX IF NOT EXISTS idx_g8_instances_provider_id ON g8_instances(provider_id);
CREATE INDEX IF NOT EXISTS idx_g8_instances_instance_id ON g8_instances(instance_id);

-- ============================================================
-- Table: g8_pricing
-- Description: Region-specific pricing for G8 instance types
-- ============================================================
CREATE TABLE IF NOT EXISTS g8_pricing (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  g8_instance_id INTEGER NOT NULL,
  region_id INTEGER NOT NULL,
  hourly_price REAL NOT NULL,
  monthly_price REAL NOT NULL,
  hourly_price_krw REAL,
  monthly_price_krw REAL,
  currency TEXT NOT NULL DEFAULT 'USD',
  available INTEGER NOT NULL DEFAULT 1, -- boolean: 1=true, 0=false
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (g8_instance_id) REFERENCES g8_instances(id) ON DELETE CASCADE,
  FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE CASCADE,
  UNIQUE(g8_instance_id, region_id)
);

-- Indexes for G8 pricing queries
CREATE INDEX IF NOT EXISTS idx_g8_pricing_instance_id ON g8_pricing(g8_instance_id);
CREATE INDEX IF NOT EXISTS idx_g8_pricing_region_id ON g8_pricing(region_id);
CREATE INDEX IF NOT EXISTS idx_g8_pricing_hourly_price ON g8_pricing(hourly_price);
CREATE INDEX IF NOT EXISTS idx_g8_pricing_monthly_price ON g8_pricing(monthly_price);
CREATE INDEX IF NOT EXISTS idx_g8_pricing_available ON g8_pricing(available);

-- ============================================================
-- Table: vpu_instances
-- Description: VPU (NETINT Quadra) accelerated instances
-- ============================================================
CREATE TABLE IF NOT EXISTS vpu_instances (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  provider_id INTEGER NOT NULL,
  instance_id TEXT NOT NULL, -- provider's instance identifier
  instance_name TEXT NOT NULL, -- display name
  vcpu INTEGER NOT NULL,
  memory_mb INTEGER NOT NULL,
  storage_gb INTEGER NOT NULL,
  transfer_tb REAL, -- data transfer limit
  network_speed_gbps REAL,
  vpu_type TEXT NOT NULL, -- e.g., "NETINT Quadra"
  metadata TEXT, -- JSON for additional provider-specific data
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE CASCADE,
  UNIQUE(provider_id, instance_id)
);

-- Indexes for VPU instance queries
CREATE INDEX IF NOT EXISTS idx_vpu_instances_provider_id ON vpu_instances(provider_id);
CREATE INDEX IF NOT EXISTS idx_vpu_instances_instance_id ON vpu_instances(instance_id);
CREATE INDEX IF NOT EXISTS idx_vpu_instances_vpu_type ON vpu_instances(vpu_type);

-- ============================================================
-- Table: vpu_pricing
-- Description: Region-specific pricing for VPU instance types
-- ============================================================
CREATE TABLE IF NOT EXISTS vpu_pricing (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  vpu_instance_id INTEGER NOT NULL,
  region_id INTEGER NOT NULL,
  hourly_price REAL NOT NULL,
  monthly_price REAL NOT NULL,
  hourly_price_krw REAL,
  monthly_price_krw REAL,
  currency TEXT NOT NULL DEFAULT 'USD',
  available INTEGER NOT NULL DEFAULT 1, -- boolean: 1=true, 0=false
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (vpu_instance_id) REFERENCES vpu_instances(id) ON DELETE CASCADE,
  FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE CASCADE,
  UNIQUE(vpu_instance_id, region_id)
);

-- Indexes for VPU pricing queries
CREATE INDEX IF NOT EXISTS idx_vpu_pricing_instance_id ON vpu_pricing(vpu_instance_id);
CREATE INDEX IF NOT EXISTS idx_vpu_pricing_region_id ON vpu_pricing(region_id);
CREATE INDEX IF NOT EXISTS idx_vpu_pricing_hourly_price ON vpu_pricing(hourly_price);
CREATE INDEX IF NOT EXISTS idx_vpu_pricing_monthly_price ON vpu_pricing(monthly_price);
CREATE INDEX IF NOT EXISTS idx_vpu_pricing_available ON vpu_pricing(available);

-- ============================================================
-- Triggers: G8 and VPU table auto-update timestamps
-- ============================================================

CREATE TRIGGER IF NOT EXISTS update_g8_instances_updated_at
AFTER UPDATE ON g8_instances
FOR EACH ROW
BEGIN
  UPDATE g8_instances SET updated_at = datetime('now') WHERE id = NEW.id;
END;

CREATE TRIGGER IF NOT EXISTS update_g8_pricing_updated_at
AFTER UPDATE ON g8_pricing
FOR EACH ROW
BEGIN
  UPDATE g8_pricing SET updated_at = datetime('now') WHERE id = NEW.id;
END;

CREATE TRIGGER IF NOT EXISTS update_vpu_instances_updated_at
AFTER UPDATE ON vpu_instances
FOR EACH ROW
BEGIN
  UPDATE vpu_instances SET updated_at = datetime('now') WHERE id = NEW.id;
END;

CREATE TRIGGER IF NOT EXISTS update_vpu_pricing_updated_at
AFTER UPDATE ON vpu_pricing
FOR EACH ROW
BEGIN
  UPDATE vpu_pricing SET updated_at = datetime('now') WHERE id = NEW.id;
END;