isSolarNight: boolean("is_solar_night"),
  isCivilNight: boolean("is_civil_night"),
  moonPhase: decimal("moon_phase", { precision: 3, scale: 2 }),

  index("idx_selection_is_solar_night").on(table.isSolarNight),
  index("idx_selection_is_civil_night").on(table.isCivilNight),
  index("idx_selection_moon_phase").on(table.moonPhase),

    is_solar_night BOOLEAN, --calculate with function on client. this is a more accurate value to file table
    is_civil_night BOOLEAN, --calculate with function on client. this is a more accurate value to file table
    moon_phase DECIMAL(3,2) CHECK (moon_phase BETWEEN 0.00 AND 1.00), -- 0.00 to 1.00 (new moon to full moon)

CREATE INDEX idx_selection_is_solar_night ON selection(is_solar_night);
CREATE INDEX idx_selection_is_civil_night ON selection(is_civil_night);
CREATE INDEX idx_selection_moon_phase ON selection(moon_phase);

-- Drop redundant astronomical columns from selection table (data exists in file table)
DROP INDEX idx_selection_is_solar_night;
DROP INDEX idx_selection_is_civil_night;  
DROP INDEX idx_selection_moon_phase;
ALTER TABLE selection DROP COLUMN is_solar_night;
ALTER TABLE selection DROP COLUMN is_civil_night;
ALTER TABLE selection DROP COLUMN moon_phase;
SELECT NOW();
-- 2025-07-07 02:34:12.000000+00