-- Database Setup for Toko Barokah
-- Import this SQL in your phpMyAdmin

CREATE DATABASE IF NOT EXISTS `barokah_store` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `barokah_store`;

-- 1. Users Table
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `role` VARCHAR(20) NOT NULL DEFAULT 'kasir',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Categories Table
CREATE TABLE IF NOT EXISTS `categories` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Products Table
CREATE TABLE IF NOT EXISTS `products` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `barcode` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(150) NOT NULL,
  `buy_price` DOUBLE NOT NULL DEFAULT 0.0,
  `sell_price` DOUBLE NOT NULL DEFAULT 0.0,
  `stock` INT NOT NULL DEFAULT 0,
  `unit` VARCHAR(20) NOT NULL DEFAULT 'Pcs',
  `category_id` INT NULL,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Transactions Table
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `invoice_no` VARCHAR(50) NOT NULL UNIQUE,
  `user_id` INT NOT NULL,
  `total_amount` DOUBLE NOT NULL DEFAULT 0.0,
  `discount` DOUBLE NOT NULL DEFAULT 0.0,
  `grand_total` DOUBLE NOT NULL DEFAULT 0.0,
  `pay` DOUBLE NOT NULL DEFAULT 0.0,
  `change_amount` DOUBLE NOT NULL DEFAULT 0.0,
  `payment_type` VARCHAR(20) NOT NULL DEFAULT 'cash',
  `customer_name` VARCHAR(100) NULL,
  `due_date` DATE NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'paid',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Transaction Details Table
CREATE TABLE IF NOT EXISTS `transaction_details` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `transaction_id` INT NOT NULL,
  `product_id` INT NULL,
  `product_name` VARCHAR(150) NOT NULL,
  `price` DOUBLE NOT NULL,
  `qty` INT NOT NULL,
  `subtotal` DOUBLE NOT NULL,
  FOREIGN KEY (`transaction_id`) REFERENCES `transactions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Receivables Table
CREATE TABLE IF NOT EXISTS `receivables` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `transaction_id` INT NULL,
  `customer_name` VARCHAR(100) NOT NULL,
  `total_amount` DOUBLE NOT NULL,
  `paid_amount` DOUBLE NOT NULL DEFAULT 0.0,
  `due_date` DATE NULL,
  `notes` TEXT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'unpaid',
  `paid_by` VARCHAR(150) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`transaction_id`) REFERENCES `transactions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. Receivable Payments Table
CREATE TABLE IF NOT EXISTS `receivable_payments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `receivable_id` INT NOT NULL,
  `pay_amount` DOUBLE NOT NULL,
  `payment_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `note` TEXT NULL,
  FOREIGN KEY (`receivable_id`) REFERENCES `receivables`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. Attendance Table
CREATE TABLE IF NOT EXISTS `attendance` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `check_in` DATETIME NOT NULL,
  `check_out` DATETIME NULL,
  `date` DATE NOT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'present',
  `notes` TEXT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. Settings Table
CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `store_name` VARCHAR(100) NOT NULL DEFAULT 'Toko Barokah',
  `store_address` TEXT NOT NULL,
  `store_phone` VARCHAR(20) NOT NULL DEFAULT '',
  `receipt_header` TEXT NULL,
  `receipt_footer` TEXT NULL,
  `low_stock_threshold` INT NOT NULL DEFAULT 10
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed initial data
INSERT INTO `users` (`id`, `username`, `password`, `name`, `role`) VALUES
(1, 'admin', 'admin', 'Administrator', 'admin'),
(2, 'kasir', 'kasir', 'Kasir Toko', 'kasir')
ON DUPLICATE KEY UPDATE `username`=`username`;

INSERT INTO `settings` (`id`, `store_name`, `store_address`, `store_phone`, `receipt_header`, `receipt_footer`, `low_stock_threshold`) VALUES
(1, 'Toko Barokah', 'Jl. Barokah Raya No. 45, Jakarta', '081234567890', 'Terima Kasih Telah Berbelanja', 'Barang yang sudah dibeli tidak dapat ditukar/dikembalikan.', 10)
ON DUPLICATE KEY UPDATE `id`=`id`;

INSERT INTO `categories` (`id`, `name`) VALUES
(1, 'Makanan'),
(2, 'Minuman'),
(3, 'Kebutuhan Rumah'),
(4, 'Lain-lain')
ON DUPLICATE KEY UPDATE `name`=`name`;

INSERT INTO `products` (`barcode`, `name`, `buy_price`, `sell_price`, `stock`, `unit`, `category_id`) VALUES
('8992741901115', 'Indomie Goreng Spesial', 2600.0, 3100.0, 120, 'Bks', 1),
('8992741901116', 'Indomie Rasa Soto Lamongan', 2600.0, 3100.0, 8, 'Bks', 1),
('8991389221151', 'Aqua Gelas 220ml', 500.0, 1000.0, 240, 'Pcs', 2),
('8991038100111', 'Teh Botol Sosro 450ml', 3500.0, 4500.0, 45, 'Botol', 2),
('8999999039912', 'Pepsodent Pencegah Gigi Berlubang 190g', 12500.0, 15000.0, 15, 'Pcs', 3),
('8992696404455', 'Lifebuoy Sabun Mandi Cair Mild Care 400ml', 22000.0, 26500.0, 5, 'Pcs', 3),
('8998866100224', 'Gula Pasir Gulaku 1kg', 14500.0, 17000.0, 25, 'Bks', 1),
('8992759600109', 'Minyak Goreng Bimoli 2L', 28000.0, 33500.0, 18, 'Pouch', 1),
('8993005118123', 'Susu Kental Manis Frisian Flag Cokelat', 11000.0, 13000.0, 12, 'Kaleng', 1),
('8991001101235', 'Beras Ramos 5kg', 62000.0, 71500.0, 14, 'Karung', 1)
ON DUPLICATE KEY UPDATE `barcode`=`barcode`;
