-- V16 untuk database yang SUDAH BERJALAN.
-- Menambahkan fitur poin member:
--   - members.points            : saldo poin member saat ini.
--   - store_settings.rupiah_per_point : nilai rupiah untuk 1 poin, diatur admin di
--                                        halaman Member (0 = fitur poin nonaktif/belum diatur).
--   - member_point_mutations    : catatan riwayat setiap penambahan poin (otomatis dari
--                                  transaksi) & pengurangan poin (fitur "Tukar Poin" oleh admin,
--                                  lengkap dengan keterangan).
-- Poin didapat otomatis tiap transaksi member sejumlah FLOOR(total_amount / rupiah_per_point)
-- -- lihat backend/api/transactions.php. Tidak perlu dijalankan kalau Anda baru install dari
-- nol (schema.sql yang baru sudah menyertakan ini).
-- ============================================================

ALTER TABLE members
  ADD COLUMN points INT NOT NULL DEFAULT 0 AFTER client_uuid;

ALTER TABLE store_settings
  ADD COLUMN rupiah_per_point INT NOT NULL DEFAULT 0 AFTER receipt_layout;

CREATE TABLE IF NOT EXISTS member_point_mutations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  member_id INT NOT NULL,
  type ENUM('earn','redeem') NOT NULL,
  points INT NOT NULL,
  note VARCHAR(255) DEFAULT NULL,
  transaction_id INT DEFAULT NULL,
  user_id INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
  FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE INDEX idx_member_point_mutations_member ON member_point_mutations(member_id);