-- RRC Church System: Missions pledge persistence and monthly report grouping
-- This patch is safe to run more than once through the provided BAT file.

-- Ensure the older pledge_amount column exists before backfilling.
-- When running with the provided BAT file, duplicate-column/index warnings are ignored safely.
ALTER TABLE transactions ADD COLUMN pledge_amount DECIMAL(12,2) NULL;
ALTER TABLE transactions ADD KEY idx_tx_member_offering_report (type, sub_type, member_id, tx_date);

CREATE TABLE IF NOT EXISTS member_offering_pledges (
  id INT AUTO_INCREMENT PRIMARY KEY,
  member_id INT NOT NULL,
  offering_key VARCHAR(80) NOT NULL,
  sub_type VARCHAR(120) NOT NULL,
  pledge_amount DECIMAL(12,2) NOT NULL,
  created_by INT NULL,
  updated_by INT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uniq_member_offering_pledge(member_id, offering_key),
  KEY idx_mop_sub_type(sub_type),
  CONSTRAINT fk_mop_member FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Backfill saved pledges from earlier transaction-level pledge values.
INSERT INTO member_offering_pledges(member_id, offering_key, sub_type, pledge_amount, created_at, updated_at)
SELECT t.member_id,
       CASE
         WHEN t.sub_type = 'Missions Fund Pledge' THEN 'missions_fund_pledge'
         WHEN t.sub_type = 'Monthly Bills' THEN 'monthly_bills'
         ELSE LOWER(REPLACE(REPLACE(REPLACE(TRIM(t.sub_type), ' ', '_'), '-', '_'), '/', '_'))
       END AS offering_key,
       t.sub_type,
       MAX(NULLIF(t.pledge_amount,0)) AS pledge_amount,
       NOW(),
       NOW()
  FROM transactions t
 WHERE t.member_id IS NOT NULL
   AND t.type='offering'
   AND t.pledge_amount IS NOT NULL
   AND t.pledge_amount > 0
 GROUP BY t.member_id, t.sub_type
ON DUPLICATE KEY UPDATE
  sub_type = VALUES(sub_type),
  pledge_amount = member_offering_pledges.pledge_amount,
  updated_at = member_offering_pledges.updated_at;

INSERT INTO settings(`key`, value) VALUES ('schema_version','10')
ON DUPLICATE KEY UPDATE value = IF(CAST(value AS UNSIGNED) < 10, '10', value);