-- Silver Jewelry ERP Database Schema
-- Created for premium jewelry management system

-- Create database
CREATE DATABASE IF NOT EXISTS silver_jewelry_erp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE silver_jewelry_erp;

-- Admin Users Table
CREATE TABLE admin_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('super_admin', 'admin', 'manager') DEFAULT 'admin',
    status ENUM('active', 'inactive') DEFAULT 'active',
    last_login DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Category Master
CREATE TABLE categories (
    category_id VARCHAR(3) PRIMARY KEY ZEROFILL,
    category_name VARCHAR(100) NOT NULL,
    description TEXT,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Supplier Master
CREATE TABLE suppliers (
    supplier_code VARCHAR(3) PRIMARY KEY ZEROFILL,
    supplier_name VARCHAR(150) NOT NULL,
    contact_person VARCHAR(100),
    phone VARCHAR(20),
    email VARCHAR(100),
    address TEXT,
    gst_number VARCHAR(20),
    pan_number VARCHAR(20),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Silver Rate Management
CREATE TABLE silver_rates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    rate_per_gram DECIMAL(10,2) NOT NULL,
    effective_datetime DATETIME NOT NULL,
    rate_type ENUM('morning', 'evening', 'special') DEFAULT 'morning',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES admin_users(id)
);

-- Item Master
CREATE TABLE items (
    item_code VARCHAR(10) PRIMARY KEY,
    item_name VARCHAR(200) NOT NULL,
    category_id VARCHAR(3) ZEROFILL,
    supplier_code VARCHAR(3) ZEROFILL,
    weight_type ENUM('grams', 'numbers', 'both') NOT NULL,
    silver_type ENUM('jewelry', 'coin') NOT NULL,
    default_wastage_percentage DECIMAL(5,2) DEFAULT 0.00,
    description TEXT,
    barcode VARCHAR(50) UNIQUE,
    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(category_id),
    FOREIGN KEY (supplier_code) REFERENCES suppliers(supplier_code)
);

-- Purchase Header
CREATE TABLE purchase_header (
    purchase_id INT PRIMARY KEY AUTO_INCREMENT,
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    supplier_code VARCHAR(3) ZEROFILL,
    purchase_date DATE NOT NULL,
    purchase_time TIME NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    total_weight DECIMAL(10,3) NOT NULL,
    total_quantity INT DEFAULT 0,
    cgst_amount DECIMAL(10,2) DEFAULT 0.00,
    sgst_amount DECIMAL(10,2) DEFAULT 0.00,
    igst_amount DECIMAL(10,2) DEFAULT 0.00,
    other_charges DECIMAL(10,2) DEFAULT 0.00,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_code) REFERENCES suppliers(supplier_code),
    FOREIGN KEY (created_by) REFERENCES admin_users(id)
);

-- Purchase Details
CREATE TABLE purchase_details (
    detail_id INT PRIMARY KEY AUTO_INCREMENT,
    purchase_id INT NOT NULL,
    item_code VARCHAR(10) NOT NULL,
    quantity INT NOT NULL,
    weight DECIMAL(10,3) NOT NULL,
    rate_per_gram DECIMAL(10,2) NOT NULL,
    making_charges DECIMAL(10,2) DEFAULT 0.00,
    wastage_percentage DECIMAL(5,2) DEFAULT 0.00,
    wastage_amount DECIMAL(10,2) DEFAULT 0.00,
    total_amount DECIMAL(12,2) NOT NULL,
    barcode VARCHAR(50) UNIQUE,
    FOREIGN KEY (purchase_id) REFERENCES purchase_header(purchase_id),
    FOREIGN KEY (item_code) REFERENCES items(item_code)
);

-- Sales Header
CREATE TABLE sales_header (
    sales_id INT PRIMARY KEY AUTO_INCREMENT,
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    customer_name VARCHAR(150) NOT NULL,
    customer_phone VARCHAR(20),
    customer_email VARCHAR(100),
    customer_address TEXT,
    sales_date DATE NOT NULL,
    sales_time TIME NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    total_weight DECIMAL(10,3) NOT NULL,
    total_quantity INT DEFAULT 0,
    cgst_amount DECIMAL(10,2) DEFAULT 0.00,
    sgst_amount DECIMAL(10,2) DEFAULT 0.00,
    igst_amount DECIMAL(10,2) DEFAULT 0.00,
    making_charges_total DECIMAL(10,2) DEFAULT 0.00,
    discount_amount DECIMAL(10,2) DEFAULT 0.00,
    payment_mode ENUM('cash', 'card', 'upi', 'bank_transfer', 'other') DEFAULT 'cash',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES admin_users(id)
);

-- Sales Details
CREATE TABLE sales_details (
    detail_id INT PRIMARY KEY AUTO_INCREMENT,
    sales_id INT NOT NULL,
    item_code VARCHAR(10) NOT NULL,
    quantity INT NOT NULL,
    weight DECIMAL(10,3) NOT NULL,
    rate_per_gram DECIMAL(10,2) NOT NULL,
    making_charges DECIMAL(10,2) DEFAULT 0.00,
    wastage_percentage DECIMAL(5,2) DEFAULT 0.00,
    wastage_amount DECIMAL(10,2) DEFAULT 0.00,
    total_amount DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (sales_id) REFERENCES sales_header(sales_id),
    FOREIGN KEY (item_code) REFERENCES items(item_code)
);

-- Stock Management
CREATE TABLE stock (
    stock_id INT PRIMARY KEY AUTO_INCREMENT,
    item_code VARCHAR(10) UNIQUE NOT NULL,
    total_quantity INT DEFAULT 0,
    total_weight DECIMAL(10,3) DEFAULT 0.000,
    available_quantity INT DEFAULT 0,
    available_weight DECIMAL(10,3) DEFAULT 0.000,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (item_code) REFERENCES items(item_code)
);

-- Stock Movement History
CREATE TABLE stock_movements (
    movement_id INT PRIMARY KEY AUTO_INCREMENT,
    item_code VARCHAR(10) NOT NULL,
    movement_type ENUM('purchase', 'sale', 'adjustment', 'return') NOT NULL,
    quantity_change INT NOT NULL,
    weight_change DECIMAL(10,3) NOT NULL,
    reference_id INT,
    reference_type ENUM('purchase', 'sales', 'adjustment') NOT NULL,
    remarks TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (item_code) REFERENCES items(item_code)
);

-- Rate Change Log
CREATE TABLE rate_change_log (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    old_rate DECIMAL(10,2),
    new_rate DECIMAL(10,2) NOT NULL,
    change_datetime DATETIME NOT NULL,
    rate_type ENUM('morning', 'evening', 'special') DEFAULT 'morning',
    changed_by INT,
    remarks TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (changed_by) REFERENCES admin_users(id)
);

-- User Sessions
CREATE TABLE user_sessions (
    session_id VARCHAR(128) PRIMARY KEY,
    user_id INT NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    FOREIGN KEY (user_id) REFERENCES admin_users(id)
);

-- System Settings
CREATE TABLE system_settings (
    setting_id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_description TEXT,
    updated_by INT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (updated_by) REFERENCES admin_users(id)
);

-- Create indexes for better performance
CREATE INDEX idx_categories_status ON categories(status);
CREATE INDEX idx_suppliers_status ON suppliers(status);
CREATE INDEX idx_items_category ON items(category_id);
CREATE INDEX idx_items_supplier ON items(supplier_code);
CREATE INDEX idx_items_status ON items(status);
CREATE INDEX idx_silver_rates_datetime ON silver_rates(effective_datetime);
CREATE INDEX idx_purchase_header_date ON purchase_header(purchase_date);
CREATE INDEX idx_purchase_header_supplier ON purchase_header(supplier_code);
CREATE INDEX idx_sales_header_date ON sales_header(sales_date);
CREATE INDEX idx_stock_movements_item ON stock_movements(item_code);
CREATE INDEX idx_stock_movements_date ON stock_movements(created_at);
CREATE INDEX idx_user_sessions_user ON user_sessions(user_id);

-- Insert default admin user (password: admin123)
INSERT INTO admin_users (username, password, email, full_name, role) VALUES 
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@jewelry.com', 'System Administrator', 'super_admin');

-- Insert default categories
INSERT INTO categories (category_id, category_name, description) VALUES
('001', 'Rings', 'Silver rings in various designs'),
('002', 'Chains', 'Silver chains for men and women'),
('003', 'Coins', 'Silver coins and bars'),
('004', 'Anklets', 'Silver anklets and payal'),
('005', 'Earrings', 'Silver earrings and studs'),
('006', 'Pendants', 'Silver pendants and lockets'),
('007', 'Bracelets', 'Silver bracelets and bangles'),
('008', 'Necklaces', 'Silver necklaces and sets');

-- Insert default system settings
INSERT INTO system_settings (setting_key, setting_value, setting_description) VALUES
('company_name', 'Silver Jewelry ERP', 'Company name for invoices and reports'),
('gst_rate', '3', 'Default GST rate for silver jewelry'),
('company_address', '123 Jewelry Street, Silver City', 'Company address'),
('company_phone', '+91-9876543210', 'Company contact number'),
('company_email', 'info@silverjewelry.com', 'Company email address'),
('barcode_prefix', 'SJ', 'Prefix for barcode generation'),
('invoice_prefix', 'SJE', 'Prefix for invoice numbers'),
('default_wastage', '5.00', 'Default wastage percentage for jewelry');

-- Create trigger for auto-generating category codes
DELIMITER //
CREATE TRIGGER before_category_insert 
BEFORE INSERT ON categories
FOR EACH ROW
BEGIN
    IF NEW.category_id IS NULL OR NEW.category_id = '' THEN
        DECLARE next_id INT;
        SELECT IFNULL(MAX(CAST(category_id AS UNSIGNED)), 0) + 1 INTO next_id FROM categories;
        SET NEW.category_id = LPAD(next_id, 3, '0');
    END IF;
END//
DELIMITER ;

-- Create trigger for auto-generating supplier codes
DELIMITER //
CREATE TRIGGER before_supplier_insert 
BEFORE INSERT ON suppliers
FOR EACH ROW
BEGIN
    IF NEW.supplier_code IS NULL OR NEW.supplier_code = '' THEN
        DECLARE next_id INT;
        SELECT IFNULL(MAX(CAST(supplier_code AS UNSIGNED)), 0) + 1 INTO next_id FROM suppliers;
        SET NEW.supplier_code = LPAD(next_id, 3, '0');
    END IF;
END//
DELIMITER ;

-- Create trigger for updating stock on purchase
DELIMITER //
CREATE TRIGGER after_purchase_insert
AFTER INSERT ON purchase_details
FOR EACH ROW
BEGIN
    INSERT INTO stock (item_code, total_quantity, total_weight, available_quantity, available_weight)
    VALUES (NEW.item_code, NEW.quantity, NEW.weight, NEW.quantity, NEW.weight)
    ON DUPLICATE KEY UPDATE
        total_quantity = total_quantity + NEW.quantity,
        total_weight = total_weight + NEW.weight,
        available_quantity = available_quantity + NEW.quantity,
        available_weight = available_weight + NEW.weight;
    
    INSERT INTO stock_movements (item_code, movement_type, quantity_change, weight_change, reference_id, reference_type, remarks)
    VALUES (NEW.item_code, 'purchase', NEW.quantity, NEW.weight, NEW.purchase_id, 'purchase', CONCAT('Purchase - Invoice: ', (SELECT invoice_number FROM purchase_header WHERE purchase_id = NEW.purchase_id)));
END//
DELIMITER ;

-- Create trigger for updating stock on sales
DELIMITER //
CREATE TRIGGER after_sales_insert
AFTER INSERT ON sales_details
FOR EACH ROW
BEGIN
    UPDATE stock SET
        available_quantity = available_quantity - NEW.quantity,
        available_weight = available_weight - NEW.weight,
        last_updated = CURRENT_TIMESTAMP
    WHERE item_code = NEW.item_code;
    
    INSERT INTO stock_movements (item_code, movement_type, quantity_change, weight_change, reference_id, reference_type, remarks)
    VALUES (NEW.item_code, 'sale', -NEW.quantity, -NEW.weight, NEW.sales_id, 'sales', CONCAT('Sales - Invoice: ', (SELECT invoice_number FROM sales_header WHERE sales_id = NEW.sales_id)));
END//
DELIMITER ;