-- Noahvelle POS Database Schema
-- Created for Sri Lanka Context POS System

-- Drop existing tables if any
DROP TABLE IF EXISTS transaction_items;
DROP TABLE IF EXISTS transactions;
DROP TABLE IF EXISTS credit_notes;
DROP TABLE IF EXISTS promotion_products;
DROP TABLE IF EXISTS promotions;
DROP TABLE IF EXISTS customer_loyalty;
DROP TABLE IF EXISTS products_variations;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS expenses;
DROP TABLE IF EXISTS supplier_payments;
DROP TABLE IF EXISTS suppliers;
DROP TABLE IF EXISTS returns;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS categories;

-- Create Categories Table
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Users Table
CREATE TABLE users (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    role ENUM('admin', 'cashier', 'manager') NOT NULL DEFAULT 'cashier',
    pin VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Customers Table
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    address TEXT,
    city VARCHAR(50),
    postal_code VARCHAR(10),
    loyalty_points INT DEFAULT 0,
    total_spent DECIMAL(12, 2) DEFAULT 0.00,
    last_visit DATETIME,
    birthday DATE,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phone (phone),
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Products Table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    category_id INT NOT NULL,
    brand VARCHAR(100),
    cost DECIMAL(10, 2) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    image_url VARCHAR(500),
    description TEXT,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id),
    INDEX idx_category (category_id),
    INDEX idx_status (status),
    FULLTEXT INDEX ft_search (name, brand, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Product Variations Table
CREATE TABLE products_variations (
    id VARCHAR(50) PRIMARY KEY,
    product_id INT NOT NULL,
    size VARCHAR(50),
    color VARCHAR(50),
    stock INT DEFAULT 0,
    sku VARCHAR(100) UNIQUE,
    barcode VARCHAR(100) UNIQUE,
    low_stock_threshold INT DEFAULT 5,
    printed_label_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_barcode (barcode),
    INDEX idx_sku (sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Suppliers Table
CREATE TABLE suppliers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    contact_person VARCHAR(100),
    phone VARCHAR(20),
    email VARCHAR(100),
    address TEXT,
    city VARCHAR(50),
    postal_code VARCHAR(10),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Supplier Payments Table
CREATE TABLE supplier_payments (
    id VARCHAR(50) PRIMARY KEY,
    supplier_id INT NOT NULL,
    total_bill DECIMAL(12, 2) NOT NULL,
    paid_amount DECIMAL(12, 2) DEFAULT 0.00,
    due_amount DECIMAL(12, 2) DEFAULT 0.00,
    payment_date DATETIME NOT NULL,
    payment_method ENUM('cash', 'cheque', 'bank_transfer', 'card') DEFAULT 'bank_transfer',
    reference VARCHAR(100),
    invoice_ref VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    INDEX idx_supplier (supplier_id),
    INDEX idx_date (payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Promotions Table
CREATE TABLE promotions (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    type ENUM('seasonal', 'card', 'clearance', 'bogo') NOT NULL,
    discount_type ENUM('percentage', 'fixed', 'bogo') NOT NULL,
    discount_value DECIMAL(10, 2) DEFAULT 0,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    is_active BOOLEAN DEFAULT 1,
    description TEXT,
    bogo_buy INT,
    bogo_get INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_active (is_active),
    INDEX idx_dates (start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Promotion Products Table (many-to-many)
CREATE TABLE promotion_products (
    promotion_id VARCHAR(50) NOT NULL,
    product_id INT NOT NULL,
    PRIMARY KEY (promotion_id, product_id),
    FOREIGN KEY (promotion_id) REFERENCES promotions(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Transactions Table
CREATE TABLE transactions (
    id VARCHAR(50) PRIMARY KEY,
    transaction_date DATETIME NOT NULL,
    customer_id INT,
    cashier_id VARCHAR(50) NOT NULL,
    total_amount DECIMAL(12, 2) NOT NULL,
    discount_amount DECIMAL(12, 2) DEFAULT 0.00,
    final_amount DECIMAL(12, 2) NOT NULL,
    payment_method ENUM('cash', 'card', 'qr') NOT NULL,
    status ENUM('completed', 'pending', 'cancelled', 'returned') DEFAULT 'completed',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (cashier_id) REFERENCES users(id),
    INDEX idx_date (transaction_date),
    INDEX idx_customer (customer_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Transaction Items Table
CREATE TABLE transaction_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_id VARCHAR(50) NOT NULL,
    product_id INT NOT NULL,
    variation_id VARCHAR(50),
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    discount_amount DECIMAL(10, 2) DEFAULT 0.00,
    line_total DECIMAL(12, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (variation_id) REFERENCES products_variations(id),
    INDEX idx_transaction (transaction_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Credit Notes Table
CREATE TABLE credit_notes (
    id VARCHAR(50) PRIMARY KEY,
    original_transaction_id VARCHAR(50) NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    barcode VARCHAR(100),
    created_date DATETIME NOT NULL,
    is_used BOOLEAN DEFAULT 0,
    used_date DATETIME,
    reason VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (original_transaction_id) REFERENCES transactions(id),
    INDEX idx_used (is_used),
    INDEX idx_created (created_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Returns Table
CREATE TABLE returns (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_id VARCHAR(50) NOT NULL,
    return_date DATETIME NOT NULL,
    reason VARCHAR(255) NOT NULL,
    refund_amount DECIMAL(12, 2) NOT NULL,
    refund_method ENUM('cash', 'credit_note') DEFAULT 'cash',
    credit_note_id VARCHAR(50),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id),
    FOREIGN KEY (credit_note_id) REFERENCES credit_notes(id),
    INDEX idx_date (return_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Expenses Table
CREATE TABLE expenses (
    id VARCHAR(50) PRIMARY KEY,
    category VARCHAR(100) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    expense_date DATE NOT NULL,
    description TEXT,
    payed_by VARCHAR(50),
    receipt_file VARCHAR(255),
    status ENUM('approved', 'pending', 'rejected') DEFAULT 'approved',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_date (expense_date),
    INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Customer Loyalty Table
CREATE TABLE customer_loyalty (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    transaction_id VARCHAR(50),
    points_earned INT DEFAULT 0,
    points_redeemed INT DEFAULT 0,
    transaction_type ENUM('earn', 'redeem', 'adjustment') DEFAULT 'earn',
    note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (transaction_id) REFERENCES transactions(id),
    INDEX idx_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert Default Categories
INSERT INTO categories (name, description) VALUES
('Women', 'Womens clothing and accessories'),
('Men', 'Mens clothing and accessories'),
('Bridal', 'Bridal wear'),
('Fabrics', 'Fabric materials'),
('Accessories', 'Various accessories');

-- Insert Default Users
INSERT INTO users (id, name, role, pin, email, phone) VALUES
('admin', 'Kamal Perera', 'admin', '81dc9bdb52d04dc20036dbd8313ed055', 'kamal@noahvelle.lk', '077-1234567'),
('cashier1', 'Nimali Silva', 'cashier', '4a7d1ed414474e4033ac29ccb8653d9b', 'nimali@noahvelle.lk', '071-9876543'),
('cashier2', 'Sunil Bandara', 'cashier', 'b59c67bf196a4758191e42f76670ceba', 'sunil@noahvelle.lk', '076-4445555');
