-- RRC Church System Upgrade Script (Safe)
-- Use this if you already have an old database and want to add new features.
-- If you get 'Duplicate column name' or 'Duplicate key name', you can safely ignore and continue.

-- Zones leader fields
ALTER TABLE zones ADD COLUMN IF NOT EXISTS leader_member_id INT NULL;
ALTER TABLE zones ADD COLUMN IF NOT EXISTS leader_name VARCHAR(120) NULL;
ALTER TABLE zones ADD COLUMN IF NOT EXISTS leader_phone VARCHAR(40) NULL;

-- Ministries leader fields
ALTER TABLE ministries ADD COLUMN IF NOT EXISTS leader_member_id INT NULL;
ALTER TABLE ministries ADD COLUMN IF NOT EXISTS leader_name VARCHAR(120) NULL;
ALTER TABLE ministries ADD COLUMN IF NOT EXISTS leader_phone VARCHAR(40) NULL;

-- Transactions upgrades
ALTER TABLE transactions ADD COLUMN IF NOT EXISTS category VARCHAR(50) NULL;
ALTER TABLE transactions ADD COLUMN IF NOT EXISTS receipt_no VARCHAR(40) NULL;
ALTER TABLE transactions ADD COLUMN IF NOT EXISTS sub_type VARCHAR(80) NULL;
ALTER TABLE transactions ADD COLUMN IF NOT EXISTS comment TEXT NULL;
ALTER TABLE transactions ADD COLUMN IF NOT EXISTS payment_method VARCHAR(30) NOT NULL DEFAULT 'Cash';
ALTER TABLE transactions ADD COLUMN IF NOT EXISTS pledge_amount DECIMAL(12,2) NULL;
ALTER TABLE transactions ADD COLUMN IF NOT EXISTS seed_amount DECIMAL(12,2) NOT NULL DEFAULT 0;


-- Attendance service breakdown fields
ALTER TABLE attendance_services ADD COLUMN IF NOT EXISTS total_attended INT NOT NULL DEFAULT 0;
ALTER TABLE attendance_services ADD COLUMN IF NOT EXISTS men INT NOT NULL DEFAULT 0;
ALTER TABLE attendance_services ADD COLUMN IF NOT EXISTS women INT NOT NULL DEFAULT 0;
ALTER TABLE attendance_services ADD COLUMN IF NOT EXISTS children INT NOT NULL DEFAULT 0;
ALTER TABLE attendance_services ADD COLUMN IF NOT EXISTS visitors INT NOT NULL DEFAULT 0;

-- Permissions table
CREATE TABLE IF NOT EXISTS user_permissions (
  user_id INT NOT NULL,
  permission VARCHAR(80) NOT NULL,
  allowed TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY(user_id, permission),
  CONSTRAINT fk_user_perm_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- SMS templates
CREATE TABLE IF NOT EXISTS sms_templates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(120) NOT NULL,
  content TEXT NOT NULL,
  created_by INT NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_sms_tpl_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Audit logs
CREATE TABLE IF NOT EXISTS audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  module VARCHAR(60) NOT NULL,
  action VARCHAR(60) NOT NULL,
  record_id INT NULL,
  details TEXT NULL,
  created_at DATETIME NOT NULL,
  INDEX(user_id),
  INDEX(module),
  INDEX(action),
  CONSTRAINT fk_audit_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Optional: Prevent duplicate member registrations
-- If you already created these unique keys, this will error "Duplicate key name" (ignore).
-- Safe add uniq_members_phone1 only if missing
SET @idx_phone := (SELECT COUNT(1) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='members' AND INDEX_NAME='uniq_members_phone1');
SET @sql_phone := IF(@idx_phone = 0, 'ALTER TABLE members ADD UNIQUE KEY uniq_members_phone1 (phone1)', 'SELECT \"Index uniq_members_phone1 already exists\"');
PREPARE stmt_phone FROM @sql_phone;
EXECUTE stmt_phone;
DEALLOCATE PREPARE stmt_phone;
-- Safe add uniq_members_email only if missing
SET @idx_email := (SELECT COUNT(1) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='members' AND INDEX_NAME='uniq_members_email');
SET @sql_email := IF(@idx_email = 0, 'ALTER TABLE members ADD UNIQUE KEY uniq_members_email (email)', 'SELECT \"Index uniq_members_email already exists\"');
PREPARE stmt_email FROM @sql_email;
EXECUTE stmt_email;
DEALLOCATE PREPARE stmt_email;

-- SMS Outbox (per-recipient status + retries)
CREATE TABLE IF NOT EXISTS sms_outbox (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sms_log_id INT NOT NULL,
  phone VARCHAR(30) NOT NULL,
  message TEXT NOT NULL,
  context VARCHAR(40) NOT NULL DEFAULT 'manual',
  ref_type VARCHAR(40) NULL,
  ref_id INT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'queued',
  attempts INT NOT NULL DEFAULT 0,
  last_error VARCHAR(120) NULL,
  provider_response TEXT NULL,
  created_at DATETIME NOT NULL,
  sent_at DATETIME NULL,
  KEY idx_sms_outbox_log(sms_log_id),
  KEY idx_sms_outbox_status(status),
  KEY idx_sms_outbox_phone(phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- SMS advanced config defaults (safe)
INSERT INTO settings(`key`,value) VALUES('sms_phone_format','e164') ON DUPLICATE KEY UPDATE value=value;
INSERT INTO settings(`key`,value) VALUES('sms_chunk_size','100') ON DUPLICATE KEY UPDATE value=value;
INSERT INTO settings(`key`,value) VALUES('sms_timeout','20') ON DUPLICATE KEY UPDATE value=value;
INSERT INTO settings(`key`,value) VALUES('sms_http_method','POST') ON DUPLICATE KEY UPDATE value=value;
INSERT INTO settings(`key`,value) VALUES('sms_auth_scheme','bearer') ON DUPLICATE KEY UPDATE value=value;
INSERT INTO settings(`key`,value) VALUES('sms_auth_header','Authorization') ON DUPLICATE KEY UPDATE value=value;
INSERT INTO settings(`key`,value) VALUES('sms_auth_prefix','Bearer') ON DUPLICATE KEY UPDATE value=value;
INSERT INTO settings(`key`,value) VALUES('sms_json_sender_key','sender') ON DUPLICATE KEY UPDATE value=value;
INSERT INTO settings(`key`,value) VALUES('sms_json_message_key','message') ON DUPLICATE KEY UPDATE value=value;
INSERT INTO settings(`key`,value) VALUES('sms_json_recipients_key','recipients') ON DUPLICATE KEY UPDATE value=value;
INSERT INTO settings(`key`,value) VALUES('sms_recipients_mode','array') ON DUPLICATE KEY UPDATE value=value;


-- v7: Member management (soft-delete / merge / deactivate metadata)
ALTER TABLE members ADD COLUMN IF NOT EXISTS is_deleted TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE members ADD COLUMN IF NOT EXISTS deleted_by INT NULL;
ALTER TABLE members ADD COLUMN IF NOT EXISTS deleted_at DATETIME NULL;
ALTER TABLE members ADD COLUMN IF NOT EXISTS is_merged TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE members ADD COLUMN IF NOT EXISTS merged_into_member_id INT NULL;
ALTER TABLE members ADD COLUMN IF NOT EXISTS merged_by INT NULL;
ALTER TABLE members ADD COLUMN IF NOT EXISTS merged_at DATETIME NULL;
ALTER TABLE members ADD COLUMN IF NOT EXISTS status_changed_by INT NULL;
ALTER TABLE members ADD COLUMN IF NOT EXISTS status_changed_at DATETIME NULL;

-- indexes
SET @idx_del := (SELECT COUNT(1) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='members' AND INDEX_NAME='idx_members_deleted');
SET @sql_del := IF(@idx_del = 0, 'CREATE INDEX idx_members_deleted ON members(is_deleted)', 'SELECT "Index idx_members_deleted already exists"');
PREPARE stmt_del FROM @sql_del; EXECUTE stmt_del; DEALLOCATE PREPARE stmt_del;

SET @idx_mrg := (SELECT COUNT(1) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='members' AND INDEX_NAME='idx_members_merged');
SET @sql_mrg := IF(@idx_mrg = 0, 'CREATE INDEX idx_members_merged ON members(is_merged)', 'SELECT "Index idx_members_merged already exists"');
PREPARE stmt_mrg FROM @sql_mrg; EXECUTE stmt_mrg; DEALLOCATE PREPARE stmt_mrg;

SET @idx_sta := (SELECT COUNT(1) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='members' AND INDEX_NAME='idx_members_status');
SET @sql_sta := IF(@idx_sta = 0, 'CREATE INDEX idx_members_status ON members(member_status)', 'SELECT "Index idx_members_status already exists"');
PREPARE stmt_sta FROM @sql_sta; EXECUTE stmt_sta; DEALLOCATE PREPARE stmt_sta;

-- Attendance Services: store offering amount + link to auto-created transaction
ALTER TABLE attendance_services
  ADD COLUMN IF NOT EXISTS offering_amount DECIMAL(12,2) NULL AFTER visitors,
  ADD COLUMN IF NOT EXISTS offering_tx_id INT NULL AFTER offering_amount;