-- POS RESTO - Database MySQL
-- Jalankan di phpMyAdmin atau MySQL CLI

CREATE DATABASE IF NOT EXISTS pos_resto_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE pos_resto_db;

-- 1. Users
-- 0. Outlets
CREATE TABLE outlets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  address VARCHAR(255) NULL,
  is_active TINYINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Categories
CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT NULL,
  image VARCHAR(500) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Products
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NULL,
  name VARCHAR(200) NOT NULL,
  description TEXT NULL,
  image VARCHAR(500) NULL,
  price VARCHAR(20) NOT NULL DEFAULT '0',
  stock INT NOT NULL DEFAULT 0,
  status TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=active 0=inactive',
  is_favorite TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Orders
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_number INT NOT NULL DEFAULT 1,
  order_type VARCHAR(50) NOT NULL DEFAULT 'Dine In',
  table_number VARCHAR(20) NULL,
  payment_method VARCHAR(50) NOT NULL DEFAULT 'Cash',
  nama_kasir VARCHAR(100) NOT NULL DEFAULT 'Admin',
  customer_name VARCHAR(100) NULL,
  customer_phone VARCHAR(30) NULL,
  shift VARCHAR(20) NULL,
  outlet_id INT NULL,
  sub_total INT NOT NULL DEFAULT 0,
  discount INT NOT NULL DEFAULT 0,
  tax INT NOT NULL DEFAULT 0,
  service_charge INT NOT NULL DEFAULT 0,
  total DOUBLE NOT NULL DEFAULT 0,
  payment_amount DOUBLE NOT NULL DEFAULT 0,
  transaction_time VARCHAR(50) NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'completed',
  refunded_at TIMESTAMP NULL,
  refunded_by VARCHAR(100) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Order Items
CREATE TABLE order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NULL,
  product_name VARCHAR(200) NOT NULL,
  price INT NOT NULL DEFAULT 0,
  quantity INT NOT NULL DEFAULT 1,
  sub_total INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Sync Log
CREATE TABLE sync_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_id VARCHAR(100) NOT NULL,
  last_sync_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. Payment Methods
CREATE TABLE payment_methods (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  type VARCHAR(20) NOT NULL DEFAULT 'cash',
  bank_name VARCHAR(100) NULL,
  account_name VARCHAR(100) NULL,
  account_number VARCHAR(50) NULL,
  is_active TINYINT NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================
-- FOREIGN KEYS
-- =====================

ALTER TABLE products ADD CONSTRAINT fk_products_category
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL;

ALTER TABLE order_items ADD CONSTRAINT fk_order_items_order
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE;

ALTER TABLE order_items ADD CONSTRAINT fk_order_items_product
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL;

INSERT INTO categories (name, description) VALUES
('Makanan', 'Menu makanan berat'),
('Minuman', 'Menu minuman segar'),
('Snack', 'Menu snack dan cemilan');

INSERT INTO products (category_id, name, description, image, price, stock, status, is_favorite) VALUES
(1, 'Nasi Goreng', 'Nasi goreng spesial dengan telur dan ayam', '', '25000', 50, 1, 1),
(1, 'Mie Goreng', 'Mie goreng bumbu oriental', '', '22000', 40, 1, 0),
(1, 'Ayam Bakar', 'Ayam bakar bumbu kecap', '', '35000', 30, 1, 1),
(1, 'Sate Ayam', 'Sate ayam dengan bumbu kacang', '', '30000', 25, 1, 0),
(1, 'Nasi Padang', 'Nasi padang komplit', '', '28000', 35, 1, 0),
(2, 'Es Teh Manis', 'Es teh manis segar', '', '8000', 100, 1, 1),
(2, 'Es Jeruk', 'Es jeruk peras segar', '', '10000', 80, 1, 0),
(2, 'Jus Alpukat', 'Jus alpukat creamy', '', '15000', 40, 1, 1),
(2, 'Kopi Hitam', 'Kopi hitam pilihan', '', '12000', 60, 1, 0),
(3, 'Kentang Goreng', 'Kentang goreng crispy', '', '15000', 50, 1, 0),
(3, 'Pisang Goreng', 'Pisang goreng crispy keju', '', '12000', 45, 1, 1),
(3, 'Tahu Crispy', 'Tahu goreng crispy sambal', '', '10000', 40, 1, 0);


-- 7. Payment Methods seed
INSERT INTO payment_methods (name, type, bank_name, account_name, account_number, is_active, sort_order) VALUES
('Cash', 'cash', NULL, NULL, NULL, 1, 1),
('QRIS', 'qris', NULL, NULL, NULL, 1, 2),
('Transfer Bank', 'transfer', NULL, NULL, NULL, 1, 3);


-- Outlets seed
INSERT INTO outlets (name, address, is_active) VALUES
('Outlet 1', '', 1);
