CREATE TABLE IF NOT EXISTS historical_contributions (

  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

  member_id INT UNSIGNED NOT NULL,

  contribution_plan_id INT UNSIGNED NOT NULL,

  month_number TINYINT UNSIGNED NOT NULL,

  year SMALLINT UNSIGNED NOT NULL,

  amount DECIMAL(12,2) NOT NULL DEFAULT 0,

  note VARCHAR(255) NULL,

  recorded_by_staff_id INT UNSIGNED NULL,

  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY uniq_plan_month_year (contribution_plan_id, month_number, year),

  CONSTRAINT fk_hc_member FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE CASCADE,

  CONSTRAINT fk_hc_plan FOREIGN KEY (contribution_plan_id) REFERENCES contribution_plans (id) ON DELETE CASCADE,

  CONSTRAINT fk_hc_staff FOREIGN KEY (recorded_by_staff_id) REFERENCES staff (id) ON DELETE SET NULL,

  CHECK (month_number BETWEEN 1 AND 12),

  CHECK (year >= 2026)

);