-- ============================================================================
-- MIGRATION: Add Frequency Data and S2P File Tables
-- Created: 2025-11-20
-- Purpose: Support frequency-dependent parameters and S2P file uploads
-- ============================================================================

-- Table for frequency-dependent parameter data (from CSV import)
CREATE TABLE IF NOT EXISTS component_freq_data (
  id INT AUTO_INCREMENT PRIMARY KEY,
  part_number VARCHAR(50) NOT NULL COMMENT 'Part number (e.g., GRF2012)',
  voltage FLOAT NOT NULL COMMENT 'Supply voltage in volts',
  frequency_mhz FLOAT NOT NULL COMMENT 'Frequency in MHz',
  gain_db FLOAT COMMENT 'Gain in dB',
  nf_db FLOAT COMMENT 'Noise Figure in dB',
  p1db_dbm FLOAT COMMENT 'Output 1dB compression point in dBm',
  oip3_dbm FLOAT COMMENT 'Output third-order intercept point in dBm',
  dc_current_ma FLOAT COMMENT 'DC supply current in mA (RF-applied, not idle)',
  imported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY idx_part_voltage_freq (part_number, voltage, frequency_mhz),
  INDEX idx_part_number (part_number),
  INDEX idx_frequency (frequency_mhz),
  INDEX idx_voltage (voltage)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Frequency-dependent parameters from measurement database';

-- Table for S2P file uploads
CREATE TABLE IF NOT EXISTS component_s2p_files (
  id INT AUTO_INCREMENT PRIMARY KEY,
  component_library_id INT NOT NULL COMMENT 'FK to component_library',
  filename VARCHAR(255) NOT NULL COMMENT 'Original filename',
  file_path VARCHAR(500) NOT NULL COMMENT 'Path to stored .s2p file',
  file_size_bytes INT NOT NULL COMMENT 'File size in bytes',
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  uploaded_by INT COMMENT 'User who uploaded the file',

  -- Parsed S2P data stored as JSON
  parsed_data JSON NOT NULL COMMENT 'Structure: {frequencies: [], s21_mag_db: [], s21_phase_deg: [], s11: [], s22: [], s12: []}',

  -- Frequency range metadata for quick filtering
  freq_min_mhz FLOAT NOT NULL COMMENT 'Minimum frequency in MHz',
  freq_max_mhz FLOAT NOT NULL COMMENT 'Maximum frequency in MHz',
  num_points INT NOT NULL COMMENT 'Number of frequency points',

  FOREIGN KEY (component_library_id) REFERENCES component_library(id) ON DELETE CASCADE,
  FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY idx_component_s2p (component_library_id),
  INDEX idx_freq_range (freq_min_mhz, freq_max_mhz)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S-Parameter files for catalog components';

-- Create upload directory path (filesystem operation handled by server)
-- Files will be stored in: uploads/s2p/

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_freq_data_part_voltage ON component_freq_data(part_number, voltage);
CREATE INDEX IF NOT EXISTS idx_s2p_component ON component_s2p_files(component_library_id);

-- ============================================================================
-- USAGE NOTES:
--
-- 1. component_freq_data table stores CSV import data:
--    - One row per (part_number, voltage, frequency) combination
--    - Query by part_number to get all available data for a component
--    - Filter by voltage to get specific voltage curve
--    - Interpolate between frequency points for analysis
--
-- 2. component_s2p_files table stores uploaded S2P files:
--    - One S2P file per catalog component (unique constraint)
--    - File stored on filesystem, metadata + parsed data in DB
--    - parsed_data JSON contains full S-parameter arrays
--    - freq_min/max for quick range checking
--
-- 3. Data Source Priority (handled in application logic):
--    Gain:  S2P > Database > Manual
--    NF:    Database > Manual (S2P doesn't provide NF)
--    P1dB:  Database > Manual
--    OIP3:  Database > Manual
-- ============================================================================
