-- Textile Manager Database Schema
-- MySQL/MariaDB compatible
-- Supports both localhost and cPanel hosting

-- Create database (for localhost setup)
-- CREATE DATABASE textile_manager CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE textile_manager;

-- Enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================================
-- CORE TABLES
-- ============================================================================

-- Shops table (multi-tenant support)
CREATE TABLE shops (
    id INT PRIMARY KEY AUTO_INCREMENT,
    shop_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    address TEXT,
    contact VARCHAR(50),
    logo_url VARCHAR(500),
    owner_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_shops_email (email),
    INDEX idx_shops_owner (owner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Users table (authentication and roles)
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(255),
    phone VARCHAR(50),
    role ENUM('owner', 'manager', 'employee') NOT NULL DEFAULT 'employee',
    shop_id INT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_users_email (email),
    INDEX idx_users_shop (shop_id),
    INDEX idx_users_role (role),
    INDEX idx_users_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Update shops table to reference users
ALTER TABLE shops ADD FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL;

-- ============================================================================
-- BUSINESS TABLES
-- ============================================================================

-- Customers table
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    email VARCHAR(255),
    address TEXT,
    shop_id INT NOT NULL,
    total_orders INT DEFAULT 0,
    total_spent DECIMAL(10,2) DEFAULT 0.00,
    last_order_date DATE NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_customers_shop (shop_id),
    INDEX idx_customers_phone (phone),
    INDEX idx_customers_name (name),
    INDEX idx_customers_total_spent (total_spent),
    INDEX idx_customers_last_order (last_order_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Customer measurements table
CREATE TABLE customer_measurements (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    item_name VARCHAR(255) NOT NULL,
    measurements TEXT NOT NULL,
    notes TEXT,
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_measurements_customer (customer_id),
    INDEX idx_measurements_shop (shop_id),
    INDEX idx_measurements_item (item_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Inventory table (fabrics and materials)
CREATE TABLE inventory (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    type ENUM('Cotton', 'Silk', 'Wool', 'Linen', 'Other') NOT NULL,
    color VARCHAR(100) NOT NULL,
    price_per_meter DECIMAL(8,2) NOT NULL,
    quantity DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    low_stock_threshold DECIMAL(8,2) NOT NULL DEFAULT 10.00,
    supplier VARCHAR(255),
    purchase_date DATE,
    expiry_date DATE,
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_inventory_shop (shop_id),
    INDEX idx_inventory_type (type),
    INDEX idx_inventory_quantity (quantity),
    INDEX idx_inventory_low_stock (low_stock_threshold)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tailoring items/services table
CREATE TABLE tailoring_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    category ENUM('Men', 'Women', 'Kids', 'Other') NOT NULL,
    sewing_price DECIMAL(8,2) NOT NULL,
    artisan_wage DECIMAL(8,2) NOT NULL,
    description TEXT,
    estimated_time_hours INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_tailoring_shop (shop_id),
    INDEX idx_tailoring_category (category),
    INDEX idx_tailoring_active (is_active),
    INDEX idx_tailoring_price (sewing_price)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Employees table
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    email VARCHAR(255),
    role ENUM('Tailor', 'Manager', 'Cutter', 'Assistant') NOT NULL,
    monthly_salary DECIMAL(8,2),
    hire_date DATE NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    address TEXT,
    emergency_contact VARCHAR(255),
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_employees_shop (shop_id),
    INDEX idx_employees_role (role),
    INDEX idx_employees_active (is_active),
    INDEX idx_employees_hire_date (hire_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Orders table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    memo_id VARCHAR(100),
    customer_id INT NOT NULL,
    customer_name VARCHAR(255) NOT NULL, -- Denormalized for performance
    customer_phone VARCHAR(50) NOT NULL, -- Denormalized for performance
    type ENUM('pickup', 'delivery') NOT NULL DEFAULT 'pickup',
    status ENUM('New', 'In Progress', 'Ready for Delivery', 'Delivered', 'Completed', 'Cancelled') NOT NULL DEFAULT 'New',
    order_date DATE NOT NULL,
    delivery_date DATE,
    delivery_address TEXT,
    total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    paid_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    total_artisan_wage DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    artisan_id INT,
    artisan_name VARCHAR(255), -- Denormalized
    cutting_master_id INT,
    cutting_master_name VARCHAR(255), -- Denormalized
    artisan_assigned_date DATE,
    artisan_completed_date DATE,
    cutting_completed_date DATE,
    priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
    notes TEXT,
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (artisan_id) REFERENCES employees(id) ON DELETE SET NULL,
    FOREIGN KEY (cutting_master_id) REFERENCES employees(id) ON DELETE SET NULL,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_orders_shop (shop_id),
    INDEX idx_orders_customer (customer_id),
    INDEX idx_orders_status (status),
    INDEX idx_orders_date (order_date),
    INDEX idx_orders_delivery (delivery_date),
    INDEX idx_orders_artisan (artisan_id),
    INDEX idx_orders_cutting_master (cutting_master_id),
    INDEX idx_orders_priority (priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Order items table (normalized)
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    price DECIMAL(8,2) NOT NULL,
    artisan_wage DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    measurements TEXT,
    fabric_used DECIMAL(8,2) DEFAULT 0.00,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    INDEX idx_order_items_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Income transactions table
CREATE TABLE income_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category VARCHAR(255) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    description TEXT,
    transaction_date DATE NOT NULL,
    order_id INT, -- Link to order if income is from order
    payment_method ENUM('cash', 'card', 'upi', 'bank_transfer', 'other') DEFAULT 'cash',
    is_editable BOOLEAN DEFAULT TRUE,
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_income_shop (shop_id),
    INDEX idx_income_date (transaction_date),
    INDEX idx_income_category (category),
    INDEX idx_income_order (order_id),
    INDEX idx_income_payment_method (payment_method)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Expense transactions table
CREATE TABLE expense_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category VARCHAR(255) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    description TEXT,
    person VARCHAR(255),
    transaction_date DATE NOT NULL,
    employee_id INT, -- If expense is related to employee
    receipt_url VARCHAR(500), -- URL to receipt image
    payment_method ENUM('cash', 'card', 'upi', 'bank_transfer', 'other') DEFAULT 'cash',
    is_editable BOOLEAN DEFAULT TRUE,
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_expense_shop (shop_id),
    INDEX idx_expense_date (transaction_date),
    INDEX idx_expense_category (category),
    INDEX idx_expense_employee (employee_id),
    INDEX idx_expense_payment_method (payment_method)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SETTINGS TABLES
-- ============================================================================

-- Appearance settings table
CREATE TABLE appearance_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    theme ENUM('light', 'dark') DEFAULT 'light',
    font ENUM('inter', 'manrope', 'system', 'poppins', 'pt-sans') DEFAULT 'inter',
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    UNIQUE KEY unique_shop_appearance (shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Notification settings table
CREATE TABLE notification_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sms_enabled BOOLEAN DEFAULT FALSE,
    sms_provider ENUM('twilio', 'other') DEFAULT 'twilio',
    sms_api_key VARCHAR(255),
    sms_api_secret VARCHAR(255),
    sms_sender_id VARCHAR(50),
    sms_message_template TEXT,
    whatsapp_enabled BOOLEAN DEFAULT FALSE,
    whatsapp_api_key VARCHAR(255),
    whatsapp_sender_number VARCHAR(50),
    whatsapp_message_template TEXT,
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    UNIQUE KEY unique_shop_notifications (shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- AUDIT AND REPORTING TABLES
-- ============================================================================

-- Audit logs table
CREATE TABLE audit_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    action ENUM('create', 'update', 'delete') NOT NULL,
    entity_type VARCHAR(100) NOT NULL,
    entity_id INT NOT NULL,
    user_id INT NOT NULL,
    user_name VARCHAR(255) NOT NULL,
    changes JSON, -- Store the changes as JSON
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_audit_shop (shop_id),
    INDEX idx_audit_entity (entity_type, entity_id),
    INDEX idx_audit_user (user_id),
    INDEX idx_audit_date (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Report data table (for caching complex calculations)
CREATE TABLE report_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    report_type ENUM('daily', 'weekly', 'monthly', 'yearly') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_income DECIMAL(12,2) DEFAULT 0.00,
    total_expenses DECIMAL(12,2) DEFAULT 0.00,
    net_profit DECIMAL(12,2) DEFAULT 0.00,
    order_count INT DEFAULT 0,
    customer_count INT DEFAULT 0,
    data JSON, -- Flexible data structure for different report types
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_report_shop (shop_id),
    INDEX idx_report_type (report_type),
    INDEX idx_report_dates (start_date, end_date),
    UNIQUE KEY unique_shop_report (shop_id, report_type, start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TRIGGERS FOR AUTOMATIC UPDATES
-- ============================================================================

-- Trigger to update customer statistics when orders change
DELIMITER //
CREATE TRIGGER update_customer_stats_after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE customers 
    SET 
        total_orders = (SELECT COUNT(*) FROM orders WHERE customer_id = NEW.customer_id),
        total_spent = (SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE customer_id = NEW.customer_id),
        last_order_date = NEW.order_date
    WHERE id = NEW.customer_id;
END//

CREATE TRIGGER update_customer_stats_after_order_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    UPDATE customers 
    SET 
        total_orders = (SELECT COUNT(*) FROM orders WHERE customer_id = NEW.customer_id),
        total_spent = (SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE customer_id = NEW.customer_id),
        last_order_date = (SELECT MAX(order_date) FROM orders WHERE customer_id = NEW.customer_id)
    WHERE id = NEW.customer_id;
END//

CREATE TRIGGER update_customer_stats_after_order_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    UPDATE customers 
    SET 
        total_orders = (SELECT COUNT(*) FROM orders WHERE customer_id = OLD.customer_id),
        total_spent = (SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE customer_id = OLD.customer_id),
        last_order_date = (SELECT MAX(order_date) FROM orders WHERE customer_id = OLD.customer_id)
    WHERE id = OLD.customer_id;
END//
DELIMITER ;

-- ============================================================================
-- VIEWS FOR COMMON QUERIES
-- ============================================================================

-- View for order summary with customer details
CREATE VIEW order_summary AS
SELECT 
    o.*,
    c.name as customer_name_full,
    c.email as customer_email,
    c.address as customer_address,
    a.name as artisan_name_full,
    cm.name as cutting_master_name_full,
    (o.total_amount - o.paid_amount) as balance_due,
    CASE 
        WHEN o.delivery_date < CURDATE() AND o.status NOT IN ('Delivered', 'Completed', 'Cancelled') THEN 'Overdue'
        WHEN o.delivery_date = CURDATE() AND o.status NOT IN ('Delivered', 'Completed', 'Cancelled') THEN 'Due Today'
        ELSE 'On Track'
    END as delivery_status
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
LEFT JOIN employees a ON o.artisan_id = a.id
LEFT JOIN employees cm ON o.cutting_master_id = cm.id;

-- View for inventory with low stock alerts
CREATE VIEW inventory_alerts AS
SELECT 
    *,
    CASE 
        WHEN quantity <= 0 THEN 'Out of Stock'
        WHEN quantity <= low_stock_threshold THEN 'Low Stock'
        ELSE 'In Stock'
    END as stock_status
FROM inventory
ORDER BY 
    CASE 
        WHEN quantity <= 0 THEN 1
        WHEN quantity <= low_stock_threshold THEN 2
        ELSE 3
    END,
    quantity ASC;

-- View for financial summary
CREATE VIEW financial_summary AS
SELECT 
    shop_id,
    DATE(transaction_date) as date,
    COALESCE(SUM(CASE WHEN 'income' = 'income' THEN amount ELSE 0 END), 0) as daily_income,
    COALESCE(SUM(CASE WHEN 'expense' = 'expense' THEN amount ELSE 0 END), 0) as daily_expenses,
    (COALESCE(SUM(CASE WHEN 'income' = 'income' THEN amount ELSE 0 END), 0) - 
     COALESCE(SUM(CASE WHEN 'expense' = 'expense' THEN amount ELSE 0 END), 0)) as daily_profit
FROM (
    SELECT shop_id, transaction_date, amount, 'income' as type FROM income_transactions
    UNION ALL
    SELECT shop_id, transaction_date, amount, 'expense' as type FROM expense_transactions
) combined
GROUP BY shop_id, DATE(transaction_date)
ORDER BY date DESC;

-- ============================================================================
-- INITIAL DATA
-- ============================================================================

-- Insert default expense categories
INSERT INTO expense_transactions (category, amount, description, transaction_date, shop_id, is_editable) VALUES
('Salary Advance', 0, 'Default category', '2024-01-01', 1, FALSE),
('Rent', 0, 'Default category', '2024-01-01', 1, FALSE),
('Utilities', 0, 'Default category', '2024-01-01', 1, FALSE),
('Supplies', 0, 'Default category', '2024-01-01', 1, FALSE),
('Marketing', 0, 'Default category', '2024-01-01', 1, FALSE),
('Other', 0, 'Default category', '2024-01-01', 1, FALSE)
ON DUPLICATE KEY UPDATE id=id;

-- ============================================================================
-- PERFORMANCE OPTIMIZATIONS
-- ============================================================================

-- Additional indexes for better performance
CREATE INDEX idx_orders_shop_status_date ON orders(shop_id, status, order_date);
CREATE INDEX idx_orders_shop_customer_date ON orders(shop_id, customer_id, order_date);
CREATE INDEX idx_transactions_shop_date ON income_transactions(shop_id, transaction_date);
CREATE INDEX idx_expenses_shop_date ON expense_transactions(shop_id, transaction_date);

-- Full-text search indexes
ALTER TABLE customers ADD FULLTEXT(name, phone, email);
ALTER TABLE orders ADD FULLTEXT(memo_id, customer_name, notes);
ALTER TABLE inventory ADD FULLTEXT(name, color, supplier);

-- ============================================================================
-- STORED PROCEDURES
-- ============================================================================

-- Procedure to get shop statistics
DELIMITER //
CREATE PROCEDURE GetShopStatistics(IN shop_id_param INT)
BEGIN
    SELECT 
        (SELECT COUNT(*) FROM customers WHERE shop_id = shop_id_param) as total_customers,
        (SELECT COUNT(*) FROM orders WHERE shop_id = shop_id_param) as total_orders,
        (SELECT COUNT(*) FROM orders WHERE shop_id = shop_id_param AND status = 'New') as pending_orders,
        (SELECT COUNT(*) FROM employees WHERE shop_id = shop_id_param AND is_active = TRUE) as active_employees,
        (SELECT COUNT(*) FROM inventory WHERE shop_id = shop_id_param AND quantity <= low_stock_threshold) as low_stock_items,
        (SELECT COALESCE(SUM(amount), 0) FROM income_transactions WHERE shop_id = shop_id_param AND MONTH(transaction_date) = MONTH(CURDATE())) as monthly_income,
        (SELECT COALESCE(SUM(amount), 0) FROM expense_transactions WHERE shop_id = shop_id_param AND MONTH(transaction_date) = MONTH(CURDATE())) as monthly_expenses;
END//
DELIMITER ;

-- Procedure to backup shop data
DELIMITER //
CREATE PROCEDURE BackupShopData(IN shop_id_param INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE table_name VARCHAR(255);
    DECLARE cur CURSOR FOR 
        SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME IN ('customers', 'orders', 'inventory', 'employees', 'income_transactions', 'expense_transactions');
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- Create backup tables with timestamp
    SET @backup_suffix = CONCAT('_backup_', DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s'));
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO table_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        SET @sql = CONCAT('CREATE TABLE ', table_name, @backup_suffix, ' AS SELECT * FROM ', table_name, ' WHERE shop_id = ', shop_id_param);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END//
DELIMITER ;