Relaciones de Entidades

// Crop entity relationships
type Crop = {
  id: string
  strain: Pick<Strain, 'id' | 'name'>  // Required reference
  seed?: { id: string; name: string }   // Optional origin
  spaces: Space[]                        // N:N relationship
  lights: Light[]                        // N:N relationship
  timeline: PlantInteractionLog[]        // 1:N embedded
  // ... other fields
}

// Space entity relationships
type Space = {
  id: string
  name: string
  sensors?: Sensor[]  // 1:N - owned by space
  lights?: Light[]    // 1:N - owned by space
  crops?: Crop[]      // N:N - referenced
  // ... other fields
}

// Hierarchical container structure
type StorageContainer = {
  id: string
  name: string
  type: 'room' | 'shelf' | 'container' | 'vault'
  parentContainerId?: string  // Self-reference for hierarchy
  items: StorageItem[]        // 1:N - contained items
  // ... other fields
}

-- Crops
CREATE INDEX idx_crops_stage ON crops(stage);
CREATE INDEX idx_crops_strain_id ON crops(strain_id);
CREATE INDEX idx_crops_planted_at ON crops(planted_at);

-- Sensors
CREATE INDEX idx_sensors_space_id ON sensors(space_id);
CREATE INDEX idx_sensor_readings_sensor_id_timestamp ON sensor_readings(sensor_id, timestamp);
CREATE INDEX idx_sensor_alerts_resolved ON sensor_alerts(resolved);

-- Storage
CREATE INDEX idx_storage_items_container_id ON storage_items(container_id);
CREATE INDEX idx_storage_items_strain_id ON storage_items(strain_id);
CREATE INDEX idx_storage_items_expiration ON storage_items(expiration_date);
CREATE INDEX idx_storage_logs_item_id ON storage_logs(item_id);
CREATE INDEX idx_storage_logs_timestamp ON storage_logs(timestamp);

-- Harvests
CREATE INDEX idx_harvests_crop_id ON harvests(crop_id);
CREATE INDEX idx_harvests_status ON harvests(status);