-- =====================================================
-- SKEMA RESTO POS (MySQL / MariaDB, cPanel)
-- Jalankan di phpMyAdmin pada database kosong.
-- =====================================================
SET NAMES utf8mb4;

-- ===== Pengguna & hak akses =====
CREATE TABLE roles (
  kode VARCHAR(30) PRIMARY KEY,
  nama VARCHAR(60) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE permissions (
  kode VARCHAR(60) PRIMARY KEY,
  keterangan VARCHAR(150) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
  role_kode VARCHAR(30) NOT NULL,
  permission_kode VARCHAR(60) NOT NULL,
  PRIMARY KEY (role_kode, permission_kode),
  FOREIGN KEY (role_kode) REFERENCES roles(kode) ON DELETE CASCADE,
  FOREIGN KEY (permission_kode) REFERENCES permissions(kode) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  pin_otorisasi_hash VARCHAR(255) NULL,      -- PIN untuk menyetujui void/diskon/refund
  role_kode VARCHAR(30) NOT NULL,
  aktif TINYINT(1) NOT NULL DEFAULT 1,
  dibuat_waktu DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (role_kode) REFERENCES roles(kode)
) ENGINE=InnoDB;

-- ===== Pengaturan restoran (key-value) =====
CREATE TABLE restaurant_settings (
  kunci VARCHAR(60) PRIMARY KEY,
  nilai TEXT
) ENGINE=InnoDB;

-- ===== Meja, menu, order =====
CREATE TABLE tables (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nomor_meja VARCHAR(10) NOT NULL UNIQUE,
  token_qr VARCHAR(64) NOT NULL UNIQUE,
  status ENUM('kosong','terisi') NOT NULL DEFAULT 'kosong'
) ENGINE=InnoDB;

CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL,
  station ENUM('dapur','bar') NOT NULL
) ENGINE=InnoDB;

CREATE TABLE menu_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  nama VARCHAR(120) NOT NULL,
  harga DECIMAL(14,2) NOT NULL,
  aktif TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB;

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  table_id INT NOT NULL,
  waktu DATETIME DEFAULT CURRENT_TIMESTAMP,
  status ENUM('terbuka','dibayar','batal') NOT NULL DEFAULT 'terbuka',
  catatan VARCHAR(255),
  diskon DECIMAL(14,2) NOT NULL DEFAULT 0,
  diskon_disetujui_oleh INT NULL,
  FOREIGN KEY (table_id) REFERENCES tables(id),
  FOREIGN KEY (diskon_disetujui_oleh) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE order_items (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  menu_item_id INT NOT NULL,
  qty INT NOT NULL DEFAULT 1,
  harga_satuan DECIMAL(14,2) NOT NULL,
  station ENUM('dapur','bar') NOT NULL,
  status_item ENUM('baru','selesai','dibatalkan') NOT NULL DEFAULT 'baru',
  catatan VARCHAR(255),
  dibuat_waktu DATETIME DEFAULT CURRENT_TIMESTAMP,
  selesai_waktu DATETIME NULL,
  selesai_oleh INT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (menu_item_id) REFERENCES menu_items(id),
  FOREIGN KEY (selesai_oleh) REFERENCES users(id),
  INDEX idx_antrian (station, status_item)
) ENGINE=InnoDB;

-- ===== Shift kasir =====
CREATE TABLE shifts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  buka_waktu DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  saldo_awal DECIMAL(14,2) NOT NULL,
  tutup_waktu DATETIME NULL,
  saldo_sistem DECIMAL(14,2) NULL,   -- seharusnya ada di laci
  saldo_fisik DECIMAL(14,2) NULL,    -- hasil hitung kasir
  selisih DECIMAL(14,2) NULL,
  status ENUM('buka','tutup') NOT NULL DEFAULT 'buka',
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- ===== Pembayaran & refund =====
CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  metode ENUM('tunai','kartu','qris') NOT NULL,
  jumlah DECIMAL(14,2) NOT NULL,
  waktu DATETIME DEFAULT CURRENT_TIMESTAMP,
  kasir_id INT NOT NULL,
  shift_id INT NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (kasir_id) REFERENCES users(id),
  FOREIGN KEY (shift_id) REFERENCES shifts(id)
) ENGINE=InnoDB;

CREATE TABLE refunds (
  id INT AUTO_INCREMENT PRIMARY KEY,
  payment_id INT NOT NULL,
  shift_id INT NOT NULL,
  jumlah DECIMAL(14,2) NOT NULL,
  alasan VARCHAR(255) NOT NULL,
  diajukan_oleh INT NOT NULL,
  disetujui_oleh INT NOT NULL,
  waktu DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (payment_id) REFERENCES payments(id),
  FOREIGN KEY (shift_id) REFERENCES shifts(id),
  FOREIGN KEY (diajukan_oleh) REFERENCES users(id),
  FOREIGN KEY (disetujui_oleh) REFERENCES users(id)
) ENGINE=InnoDB;

-- ===== Log aktivitas (termasuk siapa yang menyetujui) =====
CREATE TABLE activity_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  otorisasi_oleh INT NULL,
  aksi VARCHAR(60) NOT NULL,
  referensi VARCHAR(100),
  detail TEXT,
  waktu DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (otorisasi_oleh) REFERENCES users(id),
  INDEX idx_log_aksi (aksi, waktu)
) ENGINE=InnoDB;

-- ===== Stok (bahan, resep, pergerakan) =====
CREATE TABLE ingredients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL,
  satuan ENUM('gram','ml','pcs') NOT NULL,
  stok_sekarang DECIMAL(12,3) NOT NULL DEFAULT 0,
  stok_minimum DECIMAL(12,3) NOT NULL DEFAULT 0,
  harga_beli_rata DECIMAL(14,2) NOT NULL DEFAULT 0,
  aktif TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE recipes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  menu_item_id INT NOT NULL,
  ingredient_id INT NOT NULL,
  qty DECIMAL(12,3) NOT NULL,
  UNIQUE KEY uq_resep (menu_item_id, ingredient_id),
  FOREIGN KEY (menu_item_id) REFERENCES menu_items(id),
  FOREIGN KEY (ingredient_id) REFERENCES ingredients(id)
) ENGINE=InnoDB;

CREATE TABLE stock_movements (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  ingredient_id INT NOT NULL,
  qty DECIMAL(12,3) NOT NULL,
  tipe ENUM('pembelian','pemakaian','pembatalan','waste','opname') NOT NULL,
  order_item_id BIGINT NULL,
  catatan VARCHAR(255),
  user_id INT NULL,
  waktu DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ingredient_id) REFERENCES ingredients(id),
  FOREIGN KEY (order_item_id) REFERENCES order_items(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- =====================================================
-- DATA AWAL
-- =====================================================
INSERT INTO roles (kode, nama) VALUES
 ('owner','Owner'), ('admin','Admin'), ('kasir','Kasir'),
 ('dapur','Dapur'), ('bar','Bar'), ('pelayan','Pelayan');

INSERT INTO permissions (kode, keterangan) VALUES
 ('menu.kelola','Kelola menu dan resep'),
 ('meja.kelola','Kelola meja dan QR code'),
 ('stok.kelola','Kelola bahan, pembelian, opname'),
 ('pengguna.kelola','Kelola pengguna dan hak akses'),
 ('pengaturan.kelola','Ubah pengaturan restoran'),
 ('laporan.lihat','Lihat laporan'),
 ('log.lihat','Lihat log aktivitas'),
 ('shift.buka','Buka shift kasir'),
 ('shift.tutup','Tutup shift kasir'),
 ('order.buat','Buat pesanan meja'),
 ('antrian.dapur','Lihat dan tandai antrian dapur'),
 ('antrian.bar','Lihat dan tandai antrian bar'),
 ('pos.bayar','Terima pembayaran'),
 ('pos.void','Void item atau order (perlu otorisasi)'),
 ('pos.diskon','Beri diskon (perlu otorisasi)'),
 ('pos.refund','Refund pembayaran (perlu otorisasi)'),
 ('otorisasi.void','Menyetujui void dengan PIN'),
 ('otorisasi.diskon','Menyetujui diskon dengan PIN'),
 ('otorisasi.refund','Menyetujui refund dengan PIN');

-- Owner mendapat semua izin
INSERT INTO role_permissions SELECT 'owner', kode FROM permissions;

INSERT INTO role_permissions (role_kode, permission_kode) VALUES
 ('admin','menu.kelola'), ('admin','meja.kelola'), ('admin','stok.kelola'),
 ('admin','pengguna.kelola'), ('admin','pengaturan.kelola'), ('admin','laporan.lihat'),
 ('admin','log.lihat'), ('admin','otorisasi.void'), ('admin','otorisasi.diskon'),
 ('admin','otorisasi.refund'),
 ('kasir','shift.buka'), ('kasir','shift.tutup'), ('kasir','pos.bayar'),
 ('kasir','pos.void'), ('kasir','pos.diskon'), ('kasir','pos.refund'),
 ('dapur','antrian.dapur'),
 ('bar','antrian.bar'),
 ('pelayan','order.buat');

INSERT INTO restaurant_settings (kunci, nilai) VALUES
 ('nama_usaha', 'Nama Restoran Anda'),
 ('alamat', ''),
 ('telepon', ''),
 ('pajak_persen', '10'),
 ('service_charge_persen', '5'),
 ('format_struk', '80mm'),
 ('metode_bayar_aktif', 'tunai,kartu,qris');