-- Date types table (admin-managed)
CREATE TABLE IF NOT EXISTS date_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  display_order INT DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Seed default date types
INSERT INTO date_types (name, display_order) VALUES
  ('Birthday', 1),
  ('Anniversary', 2),
  ('Wedding', 3),
  ('Graduation', 4),
  ('Memorial', 5),
  ('Holiday', 6),
  ('Other', 7);

-- Add date_type_id to important_dates
ALTER TABLE important_dates
  ADD COLUMN date_type_id INT NULL AFTER profile_id,
  ADD CONSTRAINT fk_important_dates_date_type
    FOREIGN KEY (date_type_id) REFERENCES date_types(id) ON DELETE SET NULL;
