-- Perfect Water Dashboard Database Setup
-- This script creates the database schema for the PHP dashboard system

-- Create database (uncomment if needed)
-- CREATE DATABASE IF NOT EXISTS perfect_water_dev;
-- USE perfect_water_dev;

-- Create tables based on the existing Prisma schema

-- Status enum values
-- ACTIVE, INACTIVE

-- MessageChannel enum values  
-- whatsapp_web, cloud_api

-- Franchise table
CREATE TABLE IF NOT EXISTS Franchise (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    regNumber VARCHAR(64),
    vatRegistered BOOLEAN DEFAULT FALSE,
    vatNumber VARCHAR(64),
    address TEXT,
    primaryContact INT,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name)
);

-- Entity table
CREATE TABLE IF NOT EXISTS Entity (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franchiseId INT NOT NULL,
    displayName VARCHAR(255) NOT NULL,
    address TEXT,
    phone VARCHAR(32),
    email VARCHAR(191),
    vatNumber VARCHAR(64),
    logoUrl VARCHAR(512),
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE CASCADE
);

-- Store table
CREATE TABLE IF NOT EXISTS Store (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franchiseId INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(32),
    address TEXT,
    timezone VARCHAR(64),
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE CASCADE,
    UNIQUE KEY unique_franchise_code (franchiseId, code)
);

-- Role table
CREATE TABLE IF NOT EXISTS Role (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- User table
CREATE TABLE IF NOT EXISTS User (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franchiseId INT,
    email VARCHAR(191) UNIQUE,
    phoneE164 VARCHAR(32) UNIQUE,
    passwordHash VARCHAR(255),
    status ENUM('ACTIVE', 'INACTIVE') DEFAULT 'ACTIVE',
    lastLogin TIMESTAMP NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE SET NULL,
    INDEX idx_email (email),
    INDEX idx_phone (phoneE164)
);

-- UserRole table
CREATE TABLE IF NOT EXISTS UserRole (
    userId INT NOT NULL,
    roleId INT NOT NULL,
    franchiseId INT,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (userId, roleId),
    FOREIGN KEY (userId) REFERENCES User(id) ON DELETE CASCADE,
    FOREIGN KEY (roleId) REFERENCES Role(id) ON DELETE CASCADE,
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE SET NULL
);

-- Customer table
CREATE TABLE IF NOT EXISTS Customer (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franchiseId INT NOT NULL,
    storeId INT,
    loyverseCustomerId VARCHAR(255) UNIQUE,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(191) UNIQUE,
    phoneE164 VARCHAR(32) UNIQUE,
    status ENUM('ACTIVE', 'INACTIVE') DEFAULT 'ACTIVE',
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE CASCADE,
    FOREIGN KEY (storeId) REFERENCES Store(id) ON DELETE SET NULL,
    INDEX idx_franchise_store (franchiseId, storeId),
    INDEX idx_email (email),
    INDEX idx_phone (phoneE164)
);

-- Category table
CREATE TABLE IF NOT EXISTS Category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franchiseId INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    parentId INT,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE CASCADE,
    FOREIGN KEY (parentId) REFERENCES Category(id) ON DELETE SET NULL
);

-- TaxRate table
CREATE TABLE IF NOT EXISTS TaxRate (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franchiseId INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    rate DECIMAL(5,4) NOT NULL,
    activeFrom TIMESTAMP NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE CASCADE
);

-- Item table
CREATE TABLE IF NOT EXISTS Item (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franchiseId INT NOT NULL,
    loyverseItemId VARCHAR(255) UNIQUE,
    sku VARCHAR(64),
    name VARCHAR(255) NOT NULL,
    categoryId INT,
    price DECIMAL(10,2) NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE CASCADE,
    FOREIGN KEY (categoryId) REFERENCES Category(id) ON DELETE SET NULL,
    UNIQUE KEY unique_franchise_sku (franchiseId, sku)
);

-- Inventory table
CREATE TABLE IF NOT EXISTS Inventory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franchiseId INT NOT NULL,
    storeId INT NOT NULL,
    itemId INT NOT NULL,
    qtyOnHand DECIMAL(12,3) NOT NULL DEFAULT 0,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE CASCADE,
    FOREIGN KEY (storeId) REFERENCES Store(id) ON DELETE CASCADE,
    FOREIGN KEY (itemId) REFERENCES Item(id) ON DELETE CASCADE,
    UNIQUE KEY unique_store_item (storeId, itemId)
);

-- PaymentType table
CREATE TABLE IF NOT EXISTS PaymentType (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franchiseId INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE CASCADE
);

-- Receipt table
CREATE TABLE IF NOT EXISTS Receipt (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franchiseId INT NOT NULL,
    storeId INT NOT NULL,
    loyverseReceiptId VARCHAR(255) UNIQUE NOT NULL,
    customerId INT,
    total DECIMAL(10,2) NOT NULL,
    paid DECIMAL(10,2) NOT NULL DEFAULT 0,
    paymentTypeId INT,
    datetime TIMESTAMP NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE CASCADE,
    FOREIGN KEY (storeId) REFERENCES Store(id) ON DELETE CASCADE,
    FOREIGN KEY (customerId) REFERENCES Customer(id) ON DELETE SET NULL,
    FOREIGN KEY (paymentTypeId) REFERENCES PaymentType(id) ON DELETE SET NULL
);

-- ReceiptLine table
CREATE TABLE IF NOT EXISTS ReceiptLine (
    id INT AUTO_INCREMENT PRIMARY KEY,
    receiptId INT NOT NULL,
    itemId INT NOT NULL,
    qty DECIMAL(12,3) NOT NULL,
    unitPrice DECIMAL(10,2) NOT NULL,
    lineTotal DECIMAL(10,2) NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (receiptId) REFERENCES Receipt(id) ON DELETE CASCADE,
    FOREIGN KEY (itemId) REFERENCES Item(id) ON DELETE CASCADE
);

-- Invoice table
CREATE TABLE IF NOT EXISTS Invoice (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franchiseId INT NOT NULL,
    storeId INT,
    customerId INT NOT NULL,
    entityId INT NOT NULL,
    number VARCHAR(255) UNIQUE NOT NULL,
    status VARCHAR(32) NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL,
    taxTotal DECIMAL(10,2) NOT NULL,
    grandTotal DECIMAL(10,2) NOT NULL,
    issuedAt TIMESTAMP NOT NULL,
    dueAt TIMESTAMP NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE CASCADE,
    FOREIGN KEY (storeId) REFERENCES Store(id) ON DELETE SET NULL,
    FOREIGN KEY (customerId) REFERENCES Customer(id) ON DELETE CASCADE,
    FOREIGN KEY (entityId) REFERENCES Entity(id) ON DELETE CASCADE
);

-- InvoiceLine table
CREATE TABLE IF NOT EXISTS InvoiceLine (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoiceId INT NOT NULL,
    itemId INT,
    description TEXT NOT NULL,
    qty DECIMAL(12,3) NOT NULL,
    unitPrice DECIMAL(10,2) NOT NULL,
    taxRateId INT,
    lineTotal DECIMAL(10,2) NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoiceId) REFERENCES Invoice(id) ON DELETE CASCADE,
    FOREIGN KEY (itemId) REFERENCES Item(id) ON DELETE SET NULL,
    FOREIGN KEY (taxRateId) REFERENCES TaxRate(id) ON DELETE SET NULL
);

-- Payment table
CREATE TABLE IF NOT EXISTS Payment (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franchiseId INT NOT NULL,
    storeId INT,
    customerId INT NOT NULL,
    paymentTypeId INT,
    amount DECIMAL(10,2) NOT NULL,
    method VARCHAR(64) NOT NULL,
    receivedAt TIMESTAMP NOT NULL,
    reference VARCHAR(128),
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE CASCADE,
    FOREIGN KEY (storeId) REFERENCES Store(id) ON DELETE SET NULL,
    FOREIGN KEY (customerId) REFERENCES Customer(id) ON DELETE CASCADE,
    FOREIGN KEY (paymentTypeId) REFERENCES PaymentType(id) ON DELETE SET NULL
);

-- PaymentAllocation table
CREATE TABLE IF NOT EXISTS PaymentAllocation (
    paymentId INT NOT NULL,
    invoiceId INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (paymentId, invoiceId),
    FOREIGN KEY (paymentId) REFERENCES Payment(id) ON DELETE CASCADE,
    FOREIGN KEY (invoiceId) REFERENCES Invoice(id) ON DELETE CASCADE
);

-- BankTransaction table
CREATE TABLE IF NOT EXISTS BankTransaction (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franchiseId INT NOT NULL,
    accountName VARCHAR(128) NOT NULL,
    txnDate TIMESTAMP NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    description VARCHAR(512),
    reference VARCHAR(128),
    rawPayload JSON,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE CASCADE,
    INDEX idx_franchise_date (franchiseId, txnDate)
);

-- BankMatch table
CREATE TABLE IF NOT EXISTS BankMatch (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bankTransactionId INT NOT NULL,
    paymentId INT,
    invoiceId INT,
    confidence DECIMAL(5,4) NOT NULL,
    matchedBy VARCHAR(32) NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bankTransactionId) REFERENCES BankTransaction(id) ON DELETE CASCADE,
    FOREIGN KEY (paymentId) REFERENCES Payment(id) ON DELETE SET NULL,
    FOREIGN KEY (invoiceId) REFERENCES Invoice(id) ON DELETE SET NULL
);

-- Employee table
CREATE TABLE IF NOT EXISTS Employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franchiseId INT NOT NULL,
    storeId INT NOT NULL,
    loyverseEmployeeId VARCHAR(255) UNIQUE,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(191),
    phoneE164 VARCHAR(32),
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE CASCADE,
    FOREIGN KEY (storeId) REFERENCES Store(id) ON DELETE CASCADE
);

-- Shift table
CREATE TABLE IF NOT EXISTS Shift (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employeeId INT NOT NULL,
    storeId INT NOT NULL,
    start TIMESTAMP NOT NULL,
    end TIMESTAMP NULL,
    breakMin INT DEFAULT 0,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employeeId) REFERENCES Employee(id) ON DELETE CASCADE,
    FOREIGN KEY (storeId) REFERENCES Store(id) ON DELETE CASCADE
);

-- DailyCompliance table
CREATE TABLE IF NOT EXISTS DailyCompliance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franchiseId INT NOT NULL,
    storeId INT NOT NULL,
    date DATE NOT NULL,
    submittedById INT NOT NULL,
    cashCount DECIMAL(10,2),
    cleanlinessOk BOOLEAN DEFAULT FALSE,
    fridgesOk BOOLEAN DEFAULT FALSE,
    tdsPoints JSON,
    notes TEXT,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE CASCADE,
    FOREIGN KEY (storeId) REFERENCES Store(id) ON DELETE CASCADE,
    FOREIGN KEY (submittedById) REFERENCES User(id) ON DELETE CASCADE,
    UNIQUE KEY unique_store_date (storeId, date)
);

-- CompliancePhoto table
CREATE TABLE IF NOT EXISTS CompliancePhoto (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dailyComplianceId INT NOT NULL,
    photoUrl VARCHAR(512) NOT NULL,
    type VARCHAR(64),
    takenAt TIMESTAMP NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (dailyComplianceId) REFERENCES DailyCompliance(id) ON DELETE CASCADE
);

-- AIMessageLog table
CREATE TABLE IF NOT EXISTS AIMessageLog (
    id INT AUTO_INCREMENT PRIMARY KEY,
    franchiseId INT NOT NULL,
    customerId INT,
    type VARCHAR(64) NOT NULL,
    messageText TEXT NOT NULL,
    contextJson JSON,
    createdById INT NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    sentVia ENUM('whatsapp_web', 'cloud_api') DEFAULT 'whatsapp_web',
    FOREIGN KEY (franchiseId) REFERENCES Franchise(id) ON DELETE CASCADE,
    FOREIGN KEY (customerId) REFERENCES Customer(id) ON DELETE SET NULL,
    FOREIGN KEY (createdById) REFERENCES User(id) ON DELETE CASCADE
);

-- AuditLog table
CREATE TABLE IF NOT EXISTS AuditLog (
    id INT AUTO_INCREMENT PRIMARY KEY,
    userId INT NOT NULL,
    action VARCHAR(64) NOT NULL,
    resource VARCHAR(64) NOT NULL,
    resourceId INT,
    beforeJson JSON,
    afterJson JSON,
    ip VARCHAR(64),
    ua VARCHAR(255),
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (userId) REFERENCES User(id) ON DELETE CASCADE
);

-- Insert default roles
INSERT IGNORE INTO Role (name) VALUES 
('super_user'),
('owner'),
('manager'),
('admin'),
('cashier'),
('customer');

-- Insert default franchise (you can modify this)
INSERT IGNORE INTO Franchise (id, name, regNumber, vatRegistered, vatNumber, address) VALUES 
(1, 'Perfect Water Louis Trichardt', 'REG123456', TRUE, 'VAT123456789', '123 Main Street, Louis Trichardt, Limpopo');

-- Insert default store
INSERT IGNORE INTO Store (id, franchiseId, name, code, address, timezone) VALUES 
(1, 1, 'Main Store', 'MAIN', '123 Main Street, Louis Trichardt, Limpopo', 'Africa/Johannesburg');

-- Insert default entity
INSERT IGNORE INTO Entity (id, franchiseId, displayName, address, phone, email, vatNumber) VALUES 
(1, 1, 'Perfect Water (Pty) Ltd', '123 Main Street, Louis Trichardt, Limpopo', '+27123456789', 'info@perfectwater.co.za', 'VAT123456789');

-- Insert default payment types
INSERT IGNORE INTO PaymentType (franchiseId, name) VALUES 
(1, 'Cash'),
(1, 'Card'),
(1, 'EFT'),
(1, 'Account');

-- Insert default admin user (password: admin123)
INSERT IGNORE INTO User (id, franchiseId, email, phoneE164, passwordHash, status) VALUES 
(1, 1, 'admin@perfectwater.co.za', '+27123456789', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'ACTIVE');

-- Assign admin role to default user
INSERT IGNORE INTO UserRole (userId, roleId, franchiseId) VALUES 
(1, 1, 1); -- super_user role

-- Insert sample categories
INSERT IGNORE INTO Category (franchiseId, name) VALUES 
(1, 'Water Refills'),
(1, 'Bottled Water'),
(1, 'Dispensers'),
(1, 'Filtration Systems'),
(1, 'Testing Services'),
(1, 'Delivery Services');

-- Insert sample items
INSERT IGNORE INTO Item (franchiseId, sku, name, categoryId, price) VALUES 
(1, 'WR001', '5L Water Refill', 1, 15.00),
(1, 'WR002', '10L Water Refill', 1, 25.00),
(1, 'WR003', '20L Water Refill', 1, 45.00),
(1, 'BW001', '500ml Bottled Water', 2, 8.00),
(1, 'BW002', '1L Bottled Water', 2, 12.00),
(1, 'DISP001', 'Water Dispenser Rental', 3, 150.00),
(1, 'FILT001', 'Water Filtration System', 4, 2500.00),
(1, 'TEST001', 'Water Quality Test', 5, 200.00),
(1, 'DEL001', 'Home Delivery Service', 6, 50.00);

-- Insert sample inventory
INSERT IGNORE INTO Inventory (franchiseId, storeId, itemId, qtyOnHand) VALUES 
(1, 1, 1, 100),
(1, 1, 2, 50),
(1, 1, 3, 25),
(1, 1, 4, 200),
(1, 1, 5, 150),
(1, 1, 6, 10),
(1, 1, 7, 5),
(1, 1, 8, 0),
(1, 1, 9, 0);

-- Insert sample customer
INSERT IGNORE INTO Customer (franchiseId, storeId, name, email, phoneE164, status) VALUES 
(1, 1, 'John Doe', 'john.doe@example.com', '+27123456780', 'ACTIVE'),
(1, 1, 'Jane Smith', 'jane.smith@example.com', '+27123456781', 'ACTIVE');

-- Insert sample tax rate
INSERT IGNORE INTO TaxRate (franchiseId, name, rate) VALUES 
(1, 'VAT', 0.15);

COMMIT;