-- Migration 002: Add CHECK constraints for data validation
-- MariaDB 10.2.1+ supports CHECK constraints

-- =============================================================================
-- FREQUENCY DATA VALIDATION
-- =============================================================================
-- Ensure frequency values are positive
ALTER TABLE component_freq_data
  ADD CONSTRAINT chk_freq_positive CHECK (frequency_mhz > 0),
  ADD CONSTRAINT chk_voltage_positive CHECK (voltage > 0);

-- =============================================================================
-- PROJECT FREQUENCY VALIDATION
-- =============================================================================
ALTER TABLE projects
  ADD CONSTRAINT chk_freq_single_positive CHECK (frequency_single > 0 OR frequency_single IS NULL),
  ADD CONSTRAINT chk_freq_start_positive CHECK (frequency_start > 0 OR frequency_start IS NULL),
  ADD CONSTRAINT chk_freq_stop_positive CHECK (frequency_stop > 0 OR frequency_stop IS NULL),
  ADD CONSTRAINT chk_freq_points_positive CHECK (frequency_points > 0 OR frequency_points IS NULL),
  ADD CONSTRAINT chk_freq_range_valid CHECK (
    frequency_start IS NULL OR frequency_stop IS NULL OR frequency_start < frequency_stop
  );

-- =============================================================================
-- S2P FILE VALIDATION
-- =============================================================================
ALTER TABLE component_s2p_files
  ADD CONSTRAINT chk_s2p_freq_range CHECK (freq_min_mhz < freq_max_mhz),
  ADD CONSTRAINT chk_s2p_points_positive CHECK (num_points > 0),
  ADD CONSTRAINT chk_s2p_size_positive CHECK (file_size_bytes > 0);

-- =============================================================================
-- NODE VALIDATION
-- =============================================================================
-- Ensure node_id is not empty
ALTER TABLE project_nodes
  ADD CONSTRAINT chk_node_id_not_empty CHECK (LENGTH(node_id) > 0),
  ADD CONSTRAINT chk_node_type_not_empty CHECK (LENGTH(node_type) > 0);

-- =============================================================================
-- EDGE VALIDATION
-- =============================================================================
-- Ensure edges have valid node references
ALTER TABLE project_edges
  ADD CONSTRAINT chk_edge_id_not_empty CHECK (LENGTH(edge_id) > 0),
  ADD CONSTRAINT chk_edge_source_not_empty CHECK (LENGTH(source_node_id) > 0),
  ADD CONSTRAINT chk_edge_target_not_empty CHECK (LENGTH(target_node_id) > 0),
  ADD CONSTRAINT chk_edge_no_self_loop CHECK (source_node_id != target_node_id);

-- =============================================================================
-- USERNAME/PASSWORD VALIDATION
-- =============================================================================
ALTER TABLE users
  ADD CONSTRAINT chk_username_length CHECK (LENGTH(username) >= 3 AND LENGTH(username) <= 50),
  ADD CONSTRAINT chk_email_format CHECK (email LIKE '%@%.%');
