-- 016: RO filter + membrane separation
-- Single RO equipment asset tracks filters and membrane separately.

-- equipment: dedicated next-due dates + optional interval overrides
ALTER TABLE equipment
  ADD COLUMN next_filter_due        DATE NULL COMMENT 'RO only: date filters next due'        AFTER last_membrane_date,
  ADD COLUMN next_membrane_due      DATE NULL COMMENT 'RO only: date membrane next due'       AFTER next_filter_due,
  ADD COLUMN filter_interval_days   INT  NULL COMMENT 'RO only: override default filter interval (days)'   AFTER next_membrane_due,
  ADD COLUMN membrane_interval_days INT  NULL COMMENT 'RO only: override default membrane interval (days)' AFTER filter_interval_days;

-- equipment_types: registry defaults for the two RO components
-- NOTE: no column DEFAULT here - otherwise every non-RO type created later
-- would inherit 180/730 and get treated as reverse osmosis.
ALTER TABLE equipment_types
  ADD COLUMN filter_interval_days   INT NULL COMMENT 'RO only: default filter replacement interval (days)'   AFTER default_interval_days,
  ADD COLUMN membrane_interval_days INT NULL COMMENT 'RO only: default membrane replacement interval (days)' AFTER filter_interval_days;

-- Set sensible defaults for standard RO types
UPDATE equipment_types
   SET filter_interval_days   = 180,
       membrane_interval_days = 730
 WHERE type_name LIKE '%reverse osmosis%'
    OR type_name LIKE '%reverse osmosis / drinking water%';

-- Backfill next-due dates for existing RO equipment rows
-- from the last replacement date + effective interval
UPDATE equipment e
  JOIN equipment_types et ON e.type_id = et.type_id
   SET e.next_filter_due   = CASE WHEN e.last_filter_date   IS NOT NULL
                                  THEN DATE_ADD(e.last_filter_date,
                                        INTERVAL COALESCE(e.filter_interval_days, et.filter_interval_days, 180) DAY)
                                  ELSE e.next_filter_due END,
       e.next_membrane_due = CASE WHEN e.last_membrane_date IS NOT NULL
                                  THEN DATE_ADD(e.last_membrane_date,
                                        INTERVAL COALESCE(e.membrane_interval_days, et.membrane_interval_days, 730) DAY)
                                  ELSE e.next_membrane_due END
 WHERE et.type_name LIKE '%reverse osmosis%'
    OR et.type_name LIKE '%reverse osmosis / drinking water%';