CREATE DATABASE IF NOT EXISTS dms_db;
USE dms_db;

CREATE TABLE roles(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    strRoleName VARCHAR(100) NOT NULL,
    intStatus TINYINT DEFAULT 1,
    dtmCreated DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intCreatedBy INT,
    intRoleId INT,
    strFullName VARCHAR(150),
    strEmail VARCHAR(150),
    strUsername VARCHAR(100) UNIQUE,
    strPassword VARCHAR(255),
    intStatus TINYINT DEFAULT 1,
    dtmCreated DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (intRoleId) REFERENCES roles(intId)
);

CREATE TABLE customers(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intCreatedBy INT,
    strCustomerCode VARCHAR(50),
    strName VARCHAR(200),
    strPhone VARCHAR(50),
    strEmail VARCHAR(150),
    txtAddress TEXT,
    intStatus TINYINT DEFAULT 1,
    dtmCreated DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE suppliers(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intCreatedBy INT,
    strSupplierCode VARCHAR(50),
    strSupplierName VARCHAR(200),
    strContact VARCHAR(100),
    strEmail VARCHAR(100),
    txtAddress TEXT,
    intStatus TINYINT DEFAULT 1,
    dtmCreated DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intCreatedBy INT,
    strCategoryName VARCHAR(150),
    intStatus TINYINT DEFAULT 1
);

CREATE TABLE units(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intCreatedBy INT,
    strUnitName VARCHAR(100),
    strUnitCode VARCHAR(20),
    intStatus TINYINT DEFAULT 1
);

CREATE TABLE currencies(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intCreatedBy INT,
    strCurrencyName VARCHAR(100),
    strCurrencyCode VARCHAR(10),
    strSymbol VARCHAR(10),
    intIsDefault TINYINT DEFAULT 0,
    intStatus TINYINT DEFAULT 1
);

CREATE TABLE products(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intCreatedBy INT,
    intCategoryId INT,
    intUnitId INT,
    strProductCode VARCHAR(50),
    strProductName VARCHAR(150),
    dblCost DECIMAL(18,2) DEFAULT 0,
    dblPrice DECIMAL(18,2) DEFAULT 0,
    dblStock DECIMAL(18,2) DEFAULT 0,
    dblMinStock DECIMAL(18,2) DEFAULT 0,
    intStatus TINYINT DEFAULT 1,
    FOREIGN KEY(intCategoryId) REFERENCES categories(intId)
);

CREATE TABLE warehouses(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    strWarehouseName VARCHAR(200),
    txtLocation TEXT,
    intStatus TINYINT DEFAULT 1
);

CREATE TABLE warehouse_stock(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intWarehouseId INT,
    intProductId INT,
    dblQty DECIMAL(18,2),
    UNIQUE KEY uk_warehouse_product (intWarehouseId, intProductId),
    FOREIGN KEY(intWarehouseId) REFERENCES warehouses(intId),
    FOREIGN KEY(intProductId) REFERENCES products(intId)
);

CREATE TABLE stock_transactions(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intCreatedBy INT,
    intProductId INT,
    strTransactionType ENUM('IN','OUT','TRANSFER'),
    dblQty DECIMAL(18,2),
    dtmDate DATETIME,
    FOREIGN KEY(intProductId) REFERENCES products(intId)
);

CREATE TABLE purchase_orders(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intCreatedBy INT,
    strPurchaseNo VARCHAR(50),
    intSupplierId INT,
    dtmOrderDate DATE,
    dblTotal DECIMAL(18,2),
    strStatus ENUM('PENDING','APPROVED','RECEIVED'),
    FOREIGN KEY(intSupplierId) REFERENCES suppliers(intId)
);

CREATE TABLE purchase_items(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intPurchaseId INT,
    intProductId INT,
    dblQty DECIMAL(18,2),
    dblCost DECIMAL(18,2),
    FOREIGN KEY(intPurchaseId) REFERENCES purchase_orders(intId),
    FOREIGN KEY(intProductId) REFERENCES products(intId)
);

CREATE TABLE sales_orders(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intCreatedBy INT,
    intApprovedBy INT,
    strOrderNo VARCHAR(50),
    intCustomerId INT,
    dtmOrderDate DATE,
    dblTotal DECIMAL(18,2),
    dblPaid DECIMAL(18,2) DEFAULT 0,
    dblBalance DECIMAL(18,2) DEFAULT 0,
    strStatus ENUM('PENDING','APPROVED','DELIVERED','CANCELLED'),
    FOREIGN KEY(intCustomerId) REFERENCES customers(intId)
);

CREATE TABLE sales_items(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intSalesId INT,
    intProductId INT,
    dblQty DECIMAL(18,2),
    dblPrice DECIMAL(18,2),
    FOREIGN KEY(intSalesId) REFERENCES sales_orders(intId),
    FOREIGN KEY(intProductId) REFERENCES products(intId)
);

CREATE TABLE deliveries(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intCreatedBy INT,
    intSalesOrderId INT,
    intVehicleId INT,
    intDriverId INT,
    strVehicleNo VARCHAR(50),
    strDriver VARCHAR(150),
    dtmDeliveryDate DATE,
    strStatus ENUM('PENDING','DELIVERED'),
    FOREIGN KEY(intSalesOrderId) REFERENCES sales_orders(intId),
    FOREIGN KEY(intVehicleId) REFERENCES vehicles(intId),
    FOREIGN KEY(intDriverId) REFERENCES drivers(intId)
);

CREATE TABLE vehicles(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intCreatedBy INT,
    strPlateNo VARCHAR(50) NOT NULL,
    strModel VARCHAR(100),
    strBrand VARCHAR(100),
    strColor VARCHAR(50),
    intStatus TINYINT DEFAULT 1,
    dtmCreated DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE drivers(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intCreatedBy INT,
    strName VARCHAR(150) NOT NULL,
    strPhone VARCHAR(50),
    strLicenseNo VARCHAR(100),
    intStatus TINYINT DEFAULT 1,
    dtmCreated DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE payments(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intCreatedBy INT,
    intSalesOrderId INT,
    dblAmount DECIMAL(18,2),
    strMethod ENUM('CASH','BANK','CARD','CHEQUE'),
    dtmPaymentDate DATE,
    FOREIGN KEY(intSalesOrderId) REFERENCES sales_orders(intId)
);

CREATE TABLE cheques(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intPaymentId INT NOT NULL,
    strChequeNo VARCHAR(50) NOT NULL,
    strBankName VARCHAR(100) NOT NULL,
    strBranch VARCHAR(100),
    dtmChequeDate DATE,
    dblAmount DECIMAL(18,2),
    dtmClearedDate DATE,
    strStatus ENUM('PENDING','CLEARED','BOUNCED','CANCELLED') DEFAULT 'PENDING',
    intSettledBy INT,
    dtmSettledAt DATETIME,
    txtRemarks TEXT,
    FOREIGN KEY(intPaymentId) REFERENCES payments(intId) ON DELETE CASCADE
);

CREATE TABLE banks(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    strBankName VARCHAR(100) NOT NULL UNIQUE,
    strDescription VARCHAR(255),
    intStatus TINYINT DEFAULT 1,
    dtmCreated DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE branches(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intBankId INT NOT NULL,
    strBranchName VARCHAR(100) NOT NULL,
    strAddress TEXT,
    strCity VARCHAR(100),
    intStatus TINYINT DEFAULT 1,
    dtmCreated DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(intBankId) REFERENCES banks(intId) ON DELETE CASCADE,
    UNIQUE(intBankId, strBranchName)
);

CREATE TABLE company_settings(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    strCompanyName VARCHAR(200) NOT NULL DEFAULT '',
    txtAddress TEXT,
    strPhone VARCHAR(50),
    strEmail VARCHAR(150),
    strRegNo VARCHAR(100),
    strTaxId VARCHAR(100),
    strLogo VARCHAR(255),
    dtmUpdated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO company_settings(strCompanyName) VALUES('My Company');

CREATE TABLE audit_logs(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intUserId INT,
    strAction VARCHAR(255),
    dtmAction DATETIME,
    FOREIGN KEY(intUserId) REFERENCES users(intId)
);

INSERT INTO roles (strRoleName) VALUES
('Admin'),
('Manager'),
('Sales'),
('Warehouse'),
('Delivery');

INSERT INTO users(intRoleId, strFullName, strEmail, strUsername, strPassword)
VALUES(1, 'Administrator', 'admin@dms.com', 'admin', '$2y$10$uCFSYRpbAXYn8sVekKI0wOTB9P.oc.d5oJ447rBHcn2ch0rxNftmu');

CREATE INDEX idx_customer ON customers(strName);
CREATE INDEX idx_product ON products(strProductName);
CREATE INDEX idx_sales ON sales_orders(strOrderNo);
CREATE INDEX idx_purchase ON purchase_orders(strPurchaseNo);

INSERT INTO categories(strCategoryName) VALUES('General'),('Electronics'),('Furniture'),('Stationery');
INSERT INTO units(strUnitName, strUnitCode) VALUES('Pieces', 'pcs'), ('Kilograms', 'kg'), ('Liters', 'L'), ('Box', 'box'), ('Pack', 'pkt');
INSERT INTO currencies(strCurrencyName, strCurrencyCode, strSymbol, intIsDefault) VALUES('Sri Lankan Rupee', 'LKR', 'Rs.', 1);
INSERT INTO warehouses(strWarehouseName, txtLocation) VALUES('Main Warehouse', 'Head Office'),('Secondary Warehouse', 'Branch Office');