-- V14 untuk database yang SUDAH BERJALAN.
-- Menambahkan fitur member (nama + no. HP): tabel `members`, serta kolom
-- member_id/member_name/member_phone di `transactions` (snapshot nama & no.
-- HP saat transaksi dibuat, supaya struk/riwayat lama tetap tampil benar
-- walau datanya di tabel members belakangan berubah/dihapus).
-- Tidak perlu dijalankan kalau Anda baru install dari nol (schema.sql yang
-- baru sudah menyertakan ini).
-- ============================================================

CREATE TABLE IF NOT EXISTS members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  phone VARCHAR(30) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE INDEX idx_members_name ON members(name);

ALTER TABLE transactions
  ADD COLUMN member_id INT DEFAULT NULL AFTER user_id,
  ADD COLUMN member_name VARCHAR(100) DEFAULT NULL AFTER member_id,
  ADD COLUMN member_phone VARCHAR(30) DEFAULT NULL AFTER member_name,
  ADD FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL;
CREATE INDEX idx_transactions_member ON transactions(member_id);