-- 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
);
-- 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;