-- Speaker Shop POS Database Schema
CREATE DATABASE IF NOT EXISTS speaker_shop_db;
USE speaker_shop_db;

-- Inventory Table
CREATE TABLE IF NOT EXISTS inventory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50),
    name VARCHAR(255) NOT NULL,
    branch ENUM('Mahara', 'Idigahamula') DEFAULT 'Mahara',
    category VARCHAR(100),
    stock INT DEFAULT 0,
    reOrderLevel INT DEFAULT 5,
    cost DECIMAL(10, 2) DEFAULT 0.00,
    wsPrice DECIMAL(10, 2) DEFAULT 0.00,
    retPrice DECIMAL(10, 2) DEFAULT 0.00,
    warranty VARCHAR(100) DEFAULT '12 Months',
    lastUpdated BIGINT,
    UNIQUE KEY (code, branch)
);

-- Stock Transfers Table
CREATE TABLE IF NOT EXISTS stock_transfers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fromBranch VARCHAR(100),
    toBranch VARCHAR(100),
    items JSON,
    status ENUM('Pending', 'Accepted', 'Cancelled') DEFAULT 'Pending',
    senderName VARCHAR(100),
    receiverName VARCHAR(100),
    sentTime BIGINT,
    receivedTime BIGINT,
    note TEXT,
    lastUpdated BIGINT
);

-- Sales Table
CREATE TABLE IF NOT EXISTS sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoiceNo VARCHAR(50) UNIQUE,
    time BIGINT,
    total DECIMAL(10, 2),
    profit DECIMAL(10, 2),
    customerType VARCHAR(50),
    customerName VARCHAR(255),
    userName VARCHAR(100),
    branch VARCHAR(100),
    cartItems JSON, -- Store cart items as JSON for simplicity in migration
    deliveryCharge DECIMAL(10, 2) DEFAULT 0.00,
    paymentMethod VARCHAR(50),
    returnedItems JSON,
    lastUpdated BIGINT
);

-- Customers Table
CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    type ENUM('wholesale', 'retail') DEFAULT 'retail',
    specialDiscount DECIMAL(5, 2) DEFAULT 0.00,
    address TEXT,
    lastUpdated BIGINT
);

-- Expenses Table
CREATE TABLE IF NOT EXISTS expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    time BIGINT,
    amount DECIMAL(10, 2),
    reason TEXT,
    branch VARCHAR(100),
    lastUpdated BIGINT
);

-- Categories Table
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    lastUpdated BIGINT
);

-- Repairs Table
CREATE TABLE IF NOT EXISTS repairs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    trackingNo VARCHAR(50) UNIQUE,
    customerName VARCHAR(255),
    phone VARCHAR(20),
    itemName VARCHAR(255),
    issueDesc TEXT,
    isWarranty VARCHAR(50),
    status ENUM('Received', 'Repaired', 'Issued') DEFAULT 'Received',
    cost DECIMAL(10, 2) DEFAULT 0.00,
    actualCost DECIMAL(10, 2) DEFAULT 0.00,
    receiveDate BIGINT,
    issueDate BIGINT,
    branch VARCHAR(100),
    lastUpdated BIGINT
);

-- Damage Stock Table
CREATE TABLE IF NOT EXISTS damage_stock (
    id INT AUTO_INCREMENT PRIMARY KEY,
    itemId INT,
    itemName VARCHAR(255),
    itemCode VARCHAR(50),
    qty INT,
    cost DECIMAL(10, 2),
    reason TEXT,
    date BIGINT,
    lastUpdated BIGINT
);

-- Staff Table
CREATE TABLE IF NOT EXISTS staff (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employeeId VARCHAR(50) UNIQUE,
    name VARCHAR(255),
    nic VARCHAR(20),
    resigned BOOLEAN DEFAULT FALSE,
    lastUpdated BIGINT
);

-- Attendance Table
CREATE TABLE IF NOT EXISTS attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employeeId VARCHAR(50),
    employeeName VARCHAR(255),
    type ENUM('In', 'Out'),
    time BIGINT,
    photo LONGTEXT, -- Base64 encoded photo
    lastUpdated BIGINT
);

-- Reload Networks Table
CREATE TABLE IF NOT EXISTS reload_networks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    commissionRate DECIMAL(5, 2) DEFAULT 6.00,
    currentBalance DECIMAL(10, 2) DEFAULT 0.00,
    lastUpdated BIGINT
);

-- Reloads Table
CREATE TABLE IF NOT EXISTS reloads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    networkId INT,
    networkName VARCHAR(100),
    date VARCHAR(20),
    timestamp BIGINT,
    openingBalance DECIMAL(10, 2) DEFAULT 0.00,
    deposit DECIMAL(10, 2) DEFAULT 0.00,
    sales DECIMAL(10, 2) DEFAULT 0.00,
    commission DECIMAL(10, 2) DEFAULT 0.00,
    closingBalance DECIMAL(10, 2) DEFAULT 0.00,
    lastUpdated BIGINT
);

INSERT INTO categories (name, lastUpdated) VALUES 
('Speakers', UNIX_TIMESTAMP()), 
('Cables', UNIX_TIMESTAMP()), 
('Pen Drive', UNIX_TIMESTAMP()),
('Memory Card', UNIX_TIMESTAMP()), 
('Power Cord', UNIX_TIMESTAMP()), 
('Charger', UNIX_TIMESTAMP()), 
('Songs', UNIX_TIMESTAMP());

INSERT INTO reload_networks (name, commissionRate, currentBalance, lastUpdated) VALUES
('Dialog', 6.00, 0, UNIX_TIMESTAMP()),
('Mobitel', 6.00, 0, UNIX_TIMESTAMP()),
('Airtel', 6.00, 0, UNIX_TIMESTAMP()),
('Hutch', 6.00, 0, UNIX_TIMESTAMP());