-- Database: supermarket
-- Run this entire script in phpMyAdmin (SELECT the 'supermarket' database first, or include CREATE DATABASE)

CREATE DATABASE IF NOT EXISTS supermarket;
USE supermarket;

-- Table: products
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    barcode VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0
);

-- Table: transactions
CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_code VARCHAR(50) UNIQUE NOT NULL,
    items JSON NOT NULL,  -- e.g., [{"id":1,"name":"Milk","price":50.00}]
    total DECIMAL(10, 2) NOT NULL,
    phone VARCHAR(20),
    mpesa_receipt VARCHAR(50),
    status ENUM('pending', 'paid', 'failed') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Table: users
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,  -- Hashed with password_hash()
    role ENUM('admin', 'cashier') NOT NULL
);

-- Insert default Admin user
-- Password for admin: "admin123" (hashed below - generated with password_hash('admin123', PASSWORD_DEFAULT))
-- You can change it later via the admin dashboard
INSERT INTO users (username, password, role) VALUES 
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin');  
-- This hash is for "password" - replace if needed. To generate a new one, run in PHP: echo password_hash('yourpassword', PASSWORD_DEFAULT);

-- Insert default Cashier user
-- Password: "cashier123"
INSERT INTO users (username, password, role) VALUES 
('cashier1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'cashier');  
-- Same hash example - change as above.

-- Sample Products (add more via admin dashboard)
INSERT INTO products (barcode, name, price, stock) VALUES
('123456789012', 'Fresh Milk 500ml', 50.00, 100),
('234567890123', 'Brown Bread', 60.00, 50),
('345678901234', 'Cooking Oil 1L', 250.00, 30),
('456789012345', 'Sugar 1kg', 150.00, 80),
('567890123456', 'Rice 2kg', 300.00, 40),
('678901234567', 'Toilet Soap', 80.00, 200),
('789012345678', 'Detergent 1kg', 200.00, 60),
('890123456789', 'Coca Cola 500ml', 40.00, 150),
('901234567890', 'Maize Flour 2kg', 180.00, 70),
('012345678901', 'Eggs Tray (30)', 450.00, 20);

-- Sample Transactions (for testing reports - one paid, one pending)
INSERT INTO transactions (transaction_code, items, total, phone, mpesa_receipt, status) VALUES
('1733150000001', '[{"id":1,"name":"Fresh Milk 500ml","price":50.00}]', 50.00, '254708374149', 'TEST123ABC', 'paid'),
('1733150000002', '[{"id":2,"name":"Brown Bread","price":60.00},{"id":3,"name":"Cooking Oil 1L","price":250.00}]', 310.00, '254712345678', NULL, 'pending');




ALTER TABLE products 
CHANGE price selling_price DECIMAL(10,2) NOT NULL,
ADD buying_price DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER name;

-- Update existing products (set buying = selling temporarily)
UPDATE products SET buying_price = selling_price WHERE buying_price = 0;