-- ============================================================
-- Skema Database: Kasir Unta Store
-- Jalankan file ini di phpMyAdmin / mysql client untuk membuat
-- database dan seluruh tabel yang dibutuhkan.
-- ============================================================

CREATE DATABASE IF NOT EXISTS kasir_toko CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE kasir_toko;

-- Pengguna (admin & kasir)
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin','kasir') NOT NULL DEFAULT 'kasir',
  auth_token VARCHAR(255) DEFAULT NULL,
  token_expires DATETIME DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Kategori produk (kaos, celana, jaket, dll)
CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Produk induk (mis. "Kemeja Flanel Kotak")
CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT DEFAULT NULL,
  name VARCHAR(150) NOT NULL,
  description TEXT,
  image VARCHAR(255) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Varian produk: kombinasi ukuran & warna, masing-masing punya stok & harga sendiri
CREATE TABLE IF NOT EXISTS product_variants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  sku VARCHAR(60) NOT NULL UNIQUE,
  barcode VARCHAR(50) DEFAULT NULL UNIQUE,
  size VARCHAR(20) DEFAULT NULL,
  color VARCHAR(40) DEFAULT NULL,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  cost_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  stock INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Transaksi penjualan (1 struk)
CREATE TABLE IF NOT EXISTS transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_no VARCHAR(30) NOT NULL UNIQUE,
  user_id INT NOT NULL,
  total_amount DECIMAL(12,2) NOT NULL,
  paid_amount DECIMAL(12,2) NOT NULL,
  payment_method ENUM('cash','transfer') NOT NULL DEFAULT 'cash',
  payment_proof VARCHAR(255) DEFAULT NULL, -- nama file foto bukti transfer (di admin/uploads/receipts/)
  change_amount DECIMAL(12,2) NOT NULL,
  status ENUM('completed','void') NOT NULL DEFAULT 'completed',
  -- Dipakai fitur mode offline aplikasi kasir:
  client_uuid VARCHAR(64) DEFAULT NULL UNIQUE, -- id unik dari HP, mencegah transaksi tersimpan dobel saat disinkron ulang
  synced_from_offline TINYINT(1) NOT NULL DEFAULT 0, -- 1 jika transaksi ini awalnya dibuat saat kasir sedang offline
  needs_review TINYINT(1) NOT NULL DEFAULT 0, -- 1 jika stok sempat tidak cukup saat disinkron (butuh dicek admin)
  review_note VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- Item per transaksi (baris-baris di struk)
CREATE TABLE IF NOT EXISTS transaction_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  transaction_id INT NOT NULL,
  variant_id INT NOT NULL,
  product_name VARCHAR(150) NOT NULL,
  size VARCHAR(20),
  color VARCHAR(40),
  qty INT NOT NULL,
  price DECIMAL(12,2) NOT NULL,
  subtotal DECIMAL(12,2) NOT NULL,
  discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  discount_name VARCHAR(120) DEFAULT NULL,
  FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
  FOREIGN KEY (variant_id) REFERENCES product_variants(id)
) ENGINE=InnoDB;

-- Index bantu untuk laporan & pencarian
CREATE INDEX idx_transactions_created ON transactions(created_at);
CREATE INDEX idx_variants_product ON product_variants(product_id);
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_variants_barcode ON product_variants(barcode);

-- V4: diskon, stok masuk, pengeluaran, custom struk, snapshot HPP & atribusi offline
ALTER TABLE transactions
  ADD COLUMN subtotal_amount DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER user_id,
  ADD COLUMN discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER subtotal_amount,
  ADD COLUMN discount_note VARCHAR(255) DEFAULT NULL AFTER discount_amount,
  ADD COLUMN offline_user_id INT DEFAULT NULL AFTER client_uuid,
  ADD COLUMN offline_user_name VARCHAR(100) DEFAULT NULL AFTER offline_user_id;
ALTER TABLE transaction_items
  ADD COLUMN cost_price DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER price,
  ADD COLUMN category_name VARCHAR(100) DEFAULT NULL AFTER product_name;
CREATE TABLE IF NOT EXISTS discounts (
 id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, type ENUM('percent','amount','fixed_price') NOT NULL,
 -- type 'fixed_price' = harga produk ditetapkan langsung ke nilai diskon (berapapun harga aslinya),
 -- hanya berlaku untuk scope='product'.
 -- scope 'cart'    = diskon keranjang/voucher, dipilih manual saat checkout (perilaku lama)
 -- scope 'product' = diskon melekat pada produk tertentu (lihat discount_products), berlaku otomatis
 scope ENUM('cart','product') NOT NULL DEFAULT 'cart',
 value DECIMAL(12,2) NOT NULL DEFAULT 0, min_purchase DECIMAL(12,2) NOT NULL DEFAULT 0,
 starts_at DATETIME DEFAULT NULL, ends_at DATETIME DEFAULT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- V9: produk-produk yang melekat pada sebuah diskon berjenis "product" (many-to-many,
-- satu diskon bisa dipasang ke banyak produk sekaligus)
CREATE TABLE IF NOT EXISTS discount_products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  discount_id INT NOT NULL,
  product_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_discount_product (discount_id, product_id),
  FOREIGN KEY (discount_id) REFERENCES discounts(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE INDEX idx_discount_products_product ON discount_products(product_id);
CREATE INDEX idx_discount_products_discount ON discount_products(discount_id);
-- Setiap baris = satu batch stok masuk. qty_remaining = sisa batch ini yang
-- belum terjual, dipakai untuk konsumsi FIFO (batch paling lama dijual duluan)
-- supaya harga modal batch lama TIDAK PERNAH ditimpa oleh batch baru.
CREATE TABLE IF NOT EXISTS inventory_receipts (
 id INT AUTO_INCREMENT PRIMARY KEY, variant_id INT NOT NULL, qty INT NOT NULL, qty_remaining INT NOT NULL DEFAULT 0, cost_price DECIMAL(12,2) NOT NULL DEFAULT 0,
 input_date DATE NOT NULL, note VARCHAR(255) DEFAULT NULL, user_id INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS expenses (
 id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, expense_date DATE NOT NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0,
 note VARCHAR(255) DEFAULT NULL, user_id INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
-- store_name sengaja dibiarkan kosong: kalau kosong DAN belum ada logo,
-- aplikasi & struk otomatis menampilkan "Unta Store" (lihat fallback di kode).
CREATE TABLE IF NOT EXISTS store_settings (
 id TINYINT PRIMARY KEY, store_name VARCHAR(150) DEFAULT NULL, address VARCHAR(255) DEFAULT NULL,
 phone VARCHAR(50) DEFAULT NULL, instagram VARCHAR(100) DEFAULT NULL, tiktok VARCHAR(100) DEFAULT NULL,
 work_start_time TIME DEFAULT '08:00:00', work_end_time TIME DEFAULT '17:00:00',
 late_grace_minutes INT DEFAULT 15, early_leave_grace_minutes INT DEFAULT 15,
 logo VARCHAR(255) DEFAULT NULL,
 -- V13: pengaturan tata letak struk (JSON), diatur dari admin/settings.php,
 -- dipakai bersama oleh struk kasir-pwa & struk cetak web admin.
 receipt_layout TEXT DEFAULT NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
INSERT INTO store_settings (id) VALUES (1) ON DUPLICATE KEY UPDATE id=id;
CREATE INDEX idx_inventory_date ON inventory_receipts(input_date);
CREATE INDEX idx_expenses_date ON expenses(expense_date);

-- V7: absensi karyawan (check-in/check-out) dari kasir-pwa, dengan titik GPS
CREATE TABLE IF NOT EXISTS attendances (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  work_date DATE NOT NULL,
  check_in_at DATETIME NOT NULL,
  check_in_lat DECIMAL(10,7) DEFAULT NULL,
  check_in_lng DECIMAL(10,7) DEFAULT NULL,
  check_out_at DATETIME DEFAULT NULL,
  check_out_lat DECIMAL(10,7) DEFAULT NULL,
  check_out_lng DECIMAL(10,7) DEFAULT NULL,
  note VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_workdate (user_id, work_date),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE INDEX idx_attendances_workdate ON attendances(work_date);

-- V14: member (nama + no. HP), dicari/didaftarkan langsung saat transaksi di kasir-pwa
-- V15: kolom client_uuid ditambahkan supaya member yang didaftarkan saat kasir-pwa OFFLINE
-- bisa disinkronkan ke server secara idempoten (lihat migration_v15.sql).
CREATE TABLE IF NOT EXISTS members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  phone VARCHAR(30) NOT NULL UNIQUE,
  client_uuid VARCHAR(64) DEFAULT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE INDEX idx_members_name ON members(name);

-- Nama & no. HP member disalin (snapshot) ke transaksi juga, supaya struk/riwayat lama tetap
-- menampilkan data member yang benar walau datanya di tabel members belakangan berubah/dihapus.
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);

-- V16: poin member. Nilai rupiah per 1 poin diatur admin (store_settings.rupiah_per_point,
-- 0 = fitur poin nonaktif). Tiap transaksi member menambah poin sejumlah
-- FLOOR(total_amount / rupiah_per_point) (lihat backend/api/transactions.php), dan admin bisa
-- menguranginya lewat fitur "Tukar Poin" (admin/members.php). Semua penambahan & pengurangan
-- poin dicatat di member_point_mutations supaya riwayatnya bisa dilihat/ditelusuri lagi.
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);

-- V17: catat saldo poin sebelum & sesudah tiap mutasi (earn maupun redeem), supaya halaman
-- Detail Member (admin/member_detail.php) bisa menampilkan "poin awal" & "poin setelah" apa
-- adanya dari catatan (bukan dihitung ulang tiap kali dari saldo sekarang, yang gampang
-- salah kalau ada mutasi susulan). Diisi oleh backend/api/transactions.php (saat earn) &
-- admin/members.php (saat redeem).
ALTER TABLE member_point_mutations
  ADD COLUMN points_before INT NOT NULL DEFAULT 0 AFTER points,
  ADD COLUMN points_after INT NOT NULL DEFAULT 0 AFTER points_before;