script.sql

-- ============================================
-- EXE POS Database Schema
-- SQL Server Database Schema
-- ============================================

-- Drop existing tables if they exist (in reverse order of dependencies)
IF OBJECT_ID('Payments', 'U') IS NOT NULL DROP TABLE [Payments];
IF OBJECT_ID('InvoiceItems', 'U') IS NOT NULL DROP TABLE [InvoiceItems];
IF OBJECT_ID('Invoice', 'U') IS NOT NULL DROP TABLE [Invoice];
IF OBJECT_ID('Transactions', 'U') IS NOT NULL DROP TABLE [Transactions];
IF OBJECT_ID('batch', 'U') IS NOT NULL DROP TABLE [batch];
IF OBJECT_ID('stores', 'U') IS NOT NULL DROP TABLE [stores];
IF OBJECT_ID('Categories', 'U') IS NOT NULL DROP TABLE [Categories];
IF OBJECT_ID('Brands', 'U') IS NOT NULL DROP TABLE [Brands];
IF OBJECT_ID('CashierSessions', 'U') IS NOT NULL DROP TABLE [CashierSessions];
IF OBJECT_ID('users', 'U') IS NOT NULL DROP TABLE [users];
GO

-- ============================================
-- Lookup/Master Tables
-- ============================================

-- Users Table
CREATE TABLE [users] (
    [id] INT PRIMARY KEY IDENTITY(1,1),
    [username] NVARCHAR(100) NOT NULL UNIQUE,
    [password] NVARCHAR(255) NOT NULL,
    [email] NVARCHAR(255) NULL,
    [role] NVARCHAR(50) NOT NULL CHECK ([role] IN ('Admin', 'Manager', 'Cashier', 'Staff')),
    [isActive] BIT NOT NULL DEFAULT 1,
    [status] INT NOT NULL DEFAULT 1,
    [created_at] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [created_by] UNIQUEIDENTIFIER NULL,
    [modified_at] DATETIME2 NULL,
    [modified_by] UNIQUEIDENTIFIER NULL
);
GO

-- Brands Table
CREATE TABLE [Brands] (
    [id] INT PRIMARY KEY IDENTITY(1,1),
    [BrandName] NVARCHAR(255) NOT NULL UNIQUE,
    [description] NVARCHAR(500) NULL,
    [status] INT NOT NULL DEFAULT 1 CHECK ([status] IN (0, 1)), -- 0=Inactive, 1=Active
    [created_at] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [created_by] UNIQUEIDENTIFIER NULL,
    [modified_at] DATETIME2 NULL,
    [modified_by] UNIQUEIDENTIFIER NULL
);
GO

-- Categories Table (with subcategory support)
CREATE TABLE [Categories] (
    [id] INT PRIMARY KEY IDENTITY(1,1),
    [categoryName] NVARCHAR(255) NOT NULL,
    [parentCategoryID] INT NULL, -- For subcategories (self-referencing)
    [description] NVARCHAR(500) NULL,
    [status] INT NOT NULL DEFAULT 1 CHECK ([status] IN (0, 1)), -- 0=Inactive, 1=Active
    [created_at] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [created_by] UNIQUEIDENTIFIER NULL,
    [modified_at] DATETIME2 NULL,
    [modified_by] UNIQUEIDENTIFIER NULL,
    CONSTRAINT FK_Categories_ParentCategory FOREIGN KEY ([parentCategoryID]) REFERENCES [Categories]([id])
);
GO

-- ============================================
-- Inventory/Store Tables
-- ============================================

-- Stores/Items Master Table
CREATE TABLE [stores] (
    [id] INT PRIMARY KEY IDENTITY(1,1),
    [itemCode] NVARCHAR(50) NOT NULL UNIQUE, -- Changed to NVARCHAR for alphanumeric codes
    [BrandID] INT NULL,
    [CategoryID] INT NULL,
    [model] NVARCHAR(255) NULL,
    [description] NVARCHAR(1000) NULL,
    [conditions] INT NOT NULL DEFAULT 1 CHECK ([conditions] IN (1, 2, 3)), -- 1=New, 2=Used, 3=Refurbished
    [quantity] INT NOT NULL DEFAULT 0,
    [reorderLevel] INT NOT NULL DEFAULT 0,
    [type] INT NOT NULL CHECK ([type] IN (1, 2, 3)), -- 1=Product, 2=Service, 3=Accessory
    [status] INT NOT NULL DEFAULT 1 CHECK ([status] IN (0, 1)), -- 0=Inactive, 1=Active
    [created_at] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [created_by] UNIQUEIDENTIFIER NULL,
    [modified_at] DATETIME2 NULL,
    [modified_by] UNIQUEIDENTIFIER NULL,
    CONSTRAINT FK_Stores_Brand FOREIGN KEY ([BrandID]) REFERENCES [Brands]([id]),
    CONSTRAINT FK_Stores_Category FOREIGN KEY ([CategoryID]) REFERENCES [Categories]([id])
);
GO

-- Batch/Stock Items Table (Individual items/batches)
CREATE TABLE [batch] (
    [id] UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    [itemCode] NVARCHAR(50) NOT NULL, -- FK to stores
    [StockCode] NVARCHAR(50) NULL UNIQUE, -- Unique stock code for tracking
    [Barcode] NVARCHAR(100) NULL, -- Changed to NVARCHAR for various barcode formats
    [Quantity] INT NOT NULL DEFAULT 1,
    [Cost] DECIMAL(18, 2) NOT NULL DEFAULT 0, -- Changed to DECIMAL for money
    [SellingPrice] DECIMAL(18, 2) NOT NULL DEFAULT 0, -- Changed to DECIMAL for money
    [StockInDate] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [ExpiryDate] DATETIME2 NULL, -- Added for items with expiry
    [Reference] NVARCHAR(100) NULL, -- PO/Invoice reference
    [Spec] NVARCHAR(MAX) NULL, -- Specifications
    [IMEINumbers] NVARCHAR(500) NULL, -- Fixed typo: ImiNumbers -> IMEINumbers
    [SerialNumber] NVARCHAR(255) NULL, -- Fixed: SN -> SerialNumber
    [ImageUrl] NVARCHAR(1000) NULL,
    [DescriptionNote] NVARCHAR(1000) NULL,
    [SupplierDetails] NVARCHAR(500) NULL, -- Fixed typo: SupplireDetails
    [status] INT NOT NULL DEFAULT 1 CHECK ([status] IN (0, 1, 2)), -- 0=Sold, 1=Available, 2=Reserved
    [created_at] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [created_by] UNIQUEIDENTIFIER NULL,
    [modified_at] DATETIME2 NULL,
    [modified_by] UNIQUEIDENTIFIER NULL,
    CONSTRAINT FK_Batch_Stores FOREIGN KEY ([itemCode]) REFERENCES [stores]([itemCode]) ON UPDATE CASCADE
);
GO

-- Create index for barcode lookups
CREATE NONCLUSTERED INDEX IX_Batch_Barcode ON [batch]([Barcode]) WHERE [Barcode] IS NOT NULL;
GO

-- ============================================
-- Transaction Tables
-- ============================================

-- Transactions Table (Stock movements)
CREATE TABLE [Transactions] (
    [id] UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    [batchID] UNIQUEIDENTIFIER NOT NULL, -- FK to batch
    [Barcode] NVARCHAR(100) NULL, -- Barcode reference
    [InQty] INT NOT NULL DEFAULT 0,
    [OutQty] INT NOT NULL DEFAULT 0,
    [Reference] NVARCHAR(255) NULL, -- Invoice/PO reference
    [Description] NVARCHAR(1000) NULL,
    [Type] INT NOT NULL CHECK ([Type] IN (1, 2, 3, 4, 5)), 
    -- 1=BillingItem(isOut), 2=StockItemIN(isIn), 3=StockItemOut(isOut), 4=Return(isIn), 5=Dispose(isOut)
    [status] INT NOT NULL DEFAULT 1 CHECK ([status] IN (0, 1)), -- 0=Cancelled, 1=Completed
    [created_at] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [created_by] UNIQUEIDENTIFIER NULL,
    [modified_at] DATETIME2 NULL,
    [modified_by] UNIQUEIDENTIFIER NULL,
    CONSTRAINT FK_Transactions_Batch FOREIGN KEY ([batchID]) REFERENCES [batch]([id])
);
GO

-- ============================================
-- Cashier Session Management
-- ============================================

-- Cashier Sessions Table
CREATE TABLE [CashierSessions] (
    [id] INT PRIMARY KEY IDENTITY(1,1),
    [userID] INT NOT NULL, -- Added FK to users
    [StartTime] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [EndTime] DATETIME2 NULL,
    [StartBalance] DECIMAL(18, 2) NOT NULL DEFAULT 0,
    [EndBalance] DECIMAL(18, 2) NULL,
    -- Cash denomination counts and values
    [CashType1Count] INT NULL DEFAULT 0, -- e.g., 5000 notes
    [CashType1Value] DECIMAL(18, 2) NULL DEFAULT 0,
    [CashType2Count] INT NULL DEFAULT 0, -- e.g., 1000 notes
    [CashType2Value] DECIMAL(18, 2) NULL DEFAULT 0,
    [CashType3Count] INT NULL DEFAULT 0, -- e.g., 500 notes
    [CashType3Value] DECIMAL(18, 2) NULL DEFAULT 0,
    [CashType4Count] INT NULL DEFAULT 0, -- e.g., 100 notes
    [CashType4Value] DECIMAL(18, 2) NULL DEFAULT 0,
    [CashType5Count] INT NULL DEFAULT 0, -- e.g., 50 notes
    [CashType5Value] DECIMAL(18, 2) NULL DEFAULT 0,
    [CashType6Count] INT NULL DEFAULT 0, -- e.g., coins
    [CashType6Value] DECIMAL(18, 2) NULL DEFAULT 0,
    [BankDepositAmount] DECIMAL(18, 2) NULL DEFAULT 0,
    [CardAmount] DECIMAL(18, 2) NULL DEFAULT 0,
    [ChequeAmount] DECIMAL(18, 2) NULL DEFAULT 0,
    [OtherAmount] DECIMAL(18, 2) NULL DEFAULT 0,
    [Difference] DECIMAL(18, 2) NULL DEFAULT 0,
    [ApprovedBy] UNIQUEIDENTIFIER NULL, -- Fixed: Approved_by
    [ApprovedAt] DATETIME2 NULL, -- Fixed: was guid, should be datetime
    [isActive] BIT NOT NULL DEFAULT 1,
    [Notes] NVARCHAR(1000) NULL,
    [status] INT NOT NULL DEFAULT 1 CHECK ([status] IN (0, 1)), -- 0=Closed, 1=Open
    [created_at] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [created_by] UNIQUEIDENTIFIER NULL,
    [modified_at] DATETIME2 NULL,
    [modified_by] UNIQUEIDENTIFIER NULL,
    CONSTRAINT FK_CashierSessions_User FOREIGN KEY ([userID]) REFERENCES [users]([id])
);
GO

-- ============================================
-- Invoice/Billing Tables
-- ============================================

-- Invoice Table
CREATE TABLE [Invoice] (
    [id] INT PRIMARY KEY IDENTITY(1,1),
    [InvoiceNumber] NVARCHAR(50) NOT NULL UNIQUE, -- Added invoice number
    [CustomerID] INT NULL, -- Could be FK to Customers table if exists
    [CustomerName] NVARCHAR(255) NULL,
    [CustomerPhone] NVARCHAR(20) NULL,
    [CustomerAddress] NVARCHAR(500) NULL,
    [SubTotal] DECIMAL(18, 2) NOT NULL DEFAULT 0,
    [TotalDiscount] DECIMAL(18, 2) NOT NULL DEFAULT 0,
    [Tax] DECIMAL(18, 2) NOT NULL DEFAULT 0,
    [GrandTotal] DECIMAL(18, 2) NOT NULL DEFAULT 0,
    [description] NVARCHAR(1000) NULL,
    [print] BIT NOT NULL DEFAULT 0, -- Fixed: bool -> BIT
    [printCount] INT NOT NULL DEFAULT 0,
    [type] INT NOT NULL DEFAULT 1 CHECK ([type] IN (1, 2, 3)), -- 1=Sales, 2=Return, 3=Exchange
    [isCashierIn] BIT NOT NULL DEFAULT 1, -- Fixed: bool -> BIT
    [SessionID] INT NULL,
    [status] INT NOT NULL DEFAULT 1 CHECK ([status] IN (0, 1, 2)), -- 0=Cancelled, 1=Completed, 2=Pending
    [created_at] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [created_by] UNIQUEIDENTIFIER NULL,
    [modified_at] DATETIME2 NULL,
    [modified_by] UNIQUEIDENTIFIER NULL,
    CONSTRAINT FK_Invoice_CashierSession FOREIGN KEY ([SessionID]) REFERENCES [CashierSessions]([id])
);
GO

-- Invoice Items Table
CREATE TABLE [InvoiceItems] (
    [id] INT PRIMARY KEY IDENTITY(1,1),
    [InvoiceID] INT NOT NULL,
    [batchID] UNIQUEIDENTIFIER NOT NULL, -- Added FK to batch
    [itemCode] NVARCHAR(50) NOT NULL,
    [itemDescription] NVARCHAR(500) NULL,
    [Quantity] INT NOT NULL DEFAULT 1,
    [UnitPrice] DECIMAL(18, 2) NOT NULL DEFAULT 0, -- Added unit price
    [price] DECIMAL(18, 2) NOT NULL DEFAULT 0, -- Changed to DECIMAL
    [discount] DECIMAL(18, 2) NOT NULL DEFAULT 0, -- Changed to DECIMAL
    [discountPercent] DECIMAL(5, 2) NULL, -- Added discount percentage
    [LineTotal] DECIMAL(18, 2) NOT NULL DEFAULT 0, -- Added line total
    [ReferenceID] NVARCHAR(255) NULL,
    [billingStatus] INT NOT NULL DEFAULT 1 CHECK ([billingStatus] IN (0, 1)), -- 0=Not Billed, 1=Billed
    [status] INT NOT NULL DEFAULT 1 CHECK ([status] IN (0, 1)), -- 0=Cancelled, 1=Active
    [created_at] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [created_by] UNIQUEIDENTIFIER NULL,
    [modified_at] DATETIME2 NULL,
    [modified_by] UNIQUEIDENTIFIER NULL,
    CONSTRAINT FK_InvoiceItems_Invoice FOREIGN KEY ([InvoiceID]) REFERENCES [Invoice]([id]) ON DELETE CASCADE,
    CONSTRAINT FK_InvoiceItems_Batch FOREIGN KEY ([batchID]) REFERENCES [batch]([id])
);
GO

-- Payments Table
CREATE TABLE [Payments] (
    [id] INT PRIMARY KEY IDENTITY(1,1),
    [InvoiceID] INT NOT NULL,
    [PaymentDate] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [CashAmount] DECIMAL(18, 2) NOT NULL DEFAULT 0, -- Changed to DECIMAL
    [CreditCardAmount] DECIMAL(18, 2) NOT NULL DEFAULT 0, -- Changed to DECIMAL
    [DebitCardAmount] DECIMAL(18, 2) NOT NULL DEFAULT 0, -- Changed to DECIMAL
    [ChequeAmount] DECIMAL(18, 2) NOT NULL DEFAULT 0, -- Changed to DECIMAL (fixed typo)
    [OtherAmount] DECIMAL(18, 2) NOT NULL DEFAULT 0, -- Changed to DECIMAL
    [TotalAmount] DECIMAL(18, 2) NOT NULL DEFAULT 0, -- Added total amount
    [ChangeGiven] DECIMAL(18, 2) NOT NULL DEFAULT 0, -- Added change given
    [IsCashierIn] BIT NOT NULL DEFAULT 1, -- Fixed: bool -> BIT
    [SessionID] INT NULL, -- Changed from guid to int to match CashierSessions
    [reference] NVARCHAR(100) NULL, -- Changed from guid to nvarchar
    [CardTransactionID] NVARCHAR(100) NULL, -- Added for card payments
    [ChequeNumber] NVARCHAR(50) NULL, -- Added for cheque payments
    [type] INT NOT NULL DEFAULT 1 CHECK ([type] IN (1, 2)), -- 1=Payment, 2=Refund
    [status] INT NOT NULL DEFAULT 1 CHECK ([status] IN (0, 1)), -- 0=Failed, 1=Success
    [created_at] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [created_by] UNIQUEIDENTIFIER NULL,
    [modified_at] DATETIME2 NULL,
    [modified_by] UNIQUEIDENTIFIER NULL,
    CONSTRAINT FK_Payments_Invoice FOREIGN KEY ([InvoiceID]) REFERENCES [Invoice]([id]) ON DELETE CASCADE,
    CONSTRAINT FK_Payments_CashierSession FOREIGN KEY ([SessionID]) REFERENCES [CashierSessions]([id])
);
GO

-- ============================================
-- Indexes for Performance
-- ============================================

CREATE NONCLUSTERED INDEX IX_Stores_ItemCode ON [stores]([itemCode]);
CREATE NONCLUSTERED INDEX IX_Batch_ItemCode ON [batch]([itemCode]);
CREATE NONCLUSTERED INDEX IX_Transactions_Type ON [Transactions]([Type]);
CREATE NONCLUSTERED INDEX IX_Transactions_CreatedAt ON [Transactions]([created_at]);
CREATE NONCLUSTERED INDEX IX_Invoice_SessionID ON [Invoice]([SessionID]);
CREATE NONCLUSTERED INDEX IX_Invoice_CreatedAt ON [Invoice]([created_at]);
CREATE NONCLUSTERED INDEX IX_InvoiceItems_InvoiceID ON [InvoiceItems]([InvoiceID]);
CREATE NONCLUSTERED INDEX IX_Payments_InvoiceID ON [Payments]([InvoiceID]);
CREATE NONCLUSTERED INDEX IX_Payments_SessionID ON [Payments]([SessionID]);
CREATE NONCLUSTERED INDEX IX_CashierSessions_UserID ON [CashierSessions]([userID]);
CREATE NONCLUSTERED INDEX IX_CashierSessions_IsActive ON [CashierSessions]([isActive]);
GO

-- ============================================
-- Views for Common Queries
-- ============================================

-- View: Current Stock Levels
CREATE VIEW vw_CurrentStock AS
SELECT 
    s.itemCode,
    s.model,
    s.description,
    b.BrandName,
    c.categoryName,
    s.quantity AS MasterQuantity,
    COUNT(CASE WHEN bat.status = 1 THEN 1 END) AS AvailableItems,
    COUNT(CASE WHEN bat.status = 0 THEN 1 END) AS SoldItems,
    s.reorderLevel,
    CASE 
        WHEN s.quantity <= s.reorderLevel THEN 'Reorder'
        ELSE 'OK'
    END AS StockStatus
FROM stores s
LEFT JOIN Brands b ON s.BrandID = b.id
LEFT JOIN Categories c ON s.CategoryID = c.id
LEFT JOIN batch bat ON s.itemCode = bat.itemCode
WHERE s.status = 1
GROUP BY s.itemCode, s.model, s.description, b.BrandName, c.categoryName, s.quantity, s.reorderLevel;
GO

-- View: Daily Sales Summary
CREATE VIEW vw_DailySales AS
SELECT 
    CAST(i.created_at AS DATE) AS SaleDate,
    COUNT(DISTINCT i.id) AS TotalInvoices,
    SUM(i.GrandTotal) AS TotalSales,
    SUM(i.TotalDiscount) AS TotalDiscounts,
    AVG(i.GrandTotal) AS AverageSale
FROM Invoice i
WHERE i.status = 1 AND i.type = 1
GROUP BY CAST(i.created_at AS DATE);
GO

-- ============================================
-- Stored Procedures (Optional)
-- ============================================

-- Procedure: Get Stock Item Details
CREATE PROCEDURE sp_GetStockItemDetails
    @ItemCode NVARCHAR(50)
AS
BEGIN
    SELECT 
        s.*,
        b.BrandName,
        c.categoryName,
        COUNT(bat.id) AS TotalBatches,
        SUM(CASE WHEN bat.status = 1 THEN bat.Quantity ELSE 0 END) AS AvailableQuantity
    FROM stores s
    LEFT JOIN Brands b ON s.BrandID = b.id
    LEFT JOIN Categories c ON s.CategoryID = c.id
    LEFT JOIN batch bat ON s.itemCode = bat.itemCode
    WHERE s.itemCode = @ItemCode
    GROUP BY s.id, s.itemCode, s.BrandID, s.CategoryID, s.model, s.description, 
             s.conditions, s.quantity, s.reorderLevel, s.type, s.status, 
             s.created_at, s.created_by, s.modified_at, s.modified_by,
             b.BrandName, c.categoryName;
END;
GO

-- ============================================
-- Triggers for Audit and Business Logic
-- ============================================

-- Trigger: Update Invoice Totals when InvoiceItems change
CREATE TRIGGER trg_UpdateInvoiceTotals
ON InvoiceItems
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE i
    SET 
        i.SubTotal = ISNULL((SELECT SUM(price) FROM InvoiceItems WHERE InvoiceID = i.id AND status = 1), 0),
        i.TotalDiscount = ISNULL((SELECT SUM(discount) FROM InvoiceItems WHERE InvoiceID = i.id AND status = 1), 0),
        i.GrandTotal = ISNULL((SELECT SUM(price - discount) FROM InvoiceItems WHERE InvoiceID = i.id AND status = 1), 0),
        i.modified_at = GETDATE()
    FROM Invoice i
    WHERE i.id IN (
        SELECT DISTINCT InvoiceID FROM inserted
        UNION
        SELECT DISTINCT InvoiceID FROM deleted
    );
END;
GO

-- Trigger: Create Transaction on InvoiceItem Insert (Stock Out)
CREATE TRIGGER trg_CreateTransactionOnSale
ON InvoiceItems
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO Transactions (batchID, Barcode, InQty, OutQty, Reference, Description, [Type], created_by)
    SELECT 
        i.batchID,
        b.Barcode,
        0,
        i.Quantity,
        'INV-' + CAST(i.InvoiceID AS NVARCHAR(50)),
        'Sale - Invoice #' + CAST(i.InvoiceID AS NVARCHAR(50)),
        1, -- BillingItem (isOut)
        i.created_by
    FROM inserted i
    INNER JOIN batch b ON i.batchID = b.id
    WHERE i.status = 1;
END;
GO

-- ============================================
-- Sample/Seed Data (Optional - for testing)
-- ============================================

-- Insert default admin user
INSERT INTO [users] ([username], [password], [email], [role], [created_by])
VALUES ('admin', 'hashed_password_here', 'admin@exepos.com', 'Admin', NEWID());

-- Insert sample brands
INSERT INTO [Brands] ([BrandName], [description], [created_by])
VALUES 
    ('Apple', 'Apple Inc. products', NEWID()),
    ('Samsung', 'Samsung Electronics', NEWID()),
    ('Generic', 'Generic/Unbranded items', NEWID());

-- Insert sample categories
INSERT INTO [Categories] ([categoryName], [parentCategoryID], [description], [created_by])
VALUES 
    ('Electronics', NULL, 'Electronic devices and accessories', NEWID()),
    ('Mobile Phones', 1, 'Smartphones and feature phones', NEWID()),
    ('Accessories', 1, 'Electronic accessories', NEWID());
GO

PRINT 'Database schema created successfully!';
PRINT 'Tables: users, Brands, Categories, stores, batch, Transactions, CashierSessions, Invoice, InvoiceItems, Payments';
PRINT 'Views: vw_CurrentStock, vw_DailySales';
PRINT 'Stored Procedures: sp_GetStockItemDetails';
PRINT 'Triggers: trg_UpdateInvoiceTotals, trg_CreateTransactionOnSale';
GO