-- Database Optimization for High Concurrency (100+ concurrent users)
-- Run this script to add performance indexes and optimize the database

-- ============================================================
-- COMPOSITE INDEXES FOR COMMON QUERY PATTERNS
-- ============================================================

-- Sales queries optimization
ALTER TABLE `sales` ADD INDEX `idx_tenant_status_date` (`tenant_id`, `status`, `created_at`);
ALTER TABLE `sales` ADD INDEX `idx_tenant_location_date` (`tenant_id`, `location_id`, `created_at`);
ALTER TABLE `sales` ADD INDEX `idx_tenant_customer_date` (`tenant_id`, `customer_id`, `created_at`);

-- Sale items optimization
ALTER TABLE `sale_items` ADD INDEX `idx_tenant_product` (`tenant_id`, `product_id`);
ALTER TABLE `sale_items` ADD INDEX `idx_sale_product` (`sale_id`, `product_id`);

-- Purchases optimization
ALTER TABLE `purchases` ADD INDEX `idx_tenant_status_date` (`tenant_id`, `status`, `created_at`);
ALTER TABLE `purchases` ADD INDEX `idx_tenant_supplier_date` (`tenant_id`, `supplier_id`, `created_at`);

-- Purchase items optimization
ALTER TABLE `purchase_items` ADD INDEX `idx_tenant_product` (`tenant_id`, `product_id`);
ALTER TABLE `purchase_items` ADD INDEX `idx_purchase_product` (`purchase_id`, `product_id`);

-- Stock optimization for concurrent reads/writes
ALTER TABLE `stock` ADD INDEX `idx_tenant_product_location` (`tenant_id`, `product_id`, `location_id`);
ALTER TABLE `stock` ADD INDEX `idx_tenant_location` (`tenant_id`, `location_id`);

-- Stock movements optimization
ALTER TABLE `stock_movements` ADD INDEX `idx_tenant_product_date` (`tenant_id`, `product_id`, `created_at`);
ALTER TABLE `stock_movements` ADD INDEX `idx_tenant_location_date` (`tenant_id`, `location_id`, `created_at`);
ALTER TABLE `stock_movements` ADD INDEX `idx_tenant_type_date` (`tenant_id`, `type`, `created_at`);

-- Products optimization
ALTER TABLE `products` ADD INDEX `idx_tenant_active_category` (`tenant_id`, `is_active`, `category_id`);
ALTER TABLE `products` ADD INDEX `idx_tenant_name_search` (`tenant_id`, `name`);

-- Customers optimization
ALTER TABLE `customers` ADD INDEX `idx_tenant_active` (`tenant_id`, `is_active`);

-- Suppliers optimization
ALTER TABLE `suppliers` ADD INDEX `idx_tenant_active` (`tenant_id`, `is_active`);

-- Notifications optimization
ALTER TABLE `notifications` ADD INDEX `idx_tenant_user_read` (`tenant_id`, `user_id`, `is_read`);
ALTER TABLE `notifications` ADD INDEX `idx_tenant_type_read` (`tenant_id`, `type`, `is_read`);

-- Audit logs optimization
ALTER TABLE `audit_logs` ADD INDEX `idx_tenant_user_date` (`tenant_id`, `user_id`, `created_at`);
ALTER TABLE `audit_logs` ADD INDEX `idx_tenant_model_date` (`tenant_id`, `model`, `created_at`);

-- ============================================================
-- PERFORMANCE SETTINGS FOR HIGH CONCURRENCY
-- ============================================================

-- Increase InnoDB buffer pool size (adjust based on available RAM)
-- For a server with 4GB RAM, set to 2GB
-- SET GLOBAL innodb_buffer_pool_size = 2147483648;

-- Increase InnoDB log file size for better write performance
-- SET GLOBAL innodb_log_file_size = 268435456;

-- Increase InnoDB log buffer size
-- SET GLOBAL innodb_log_buffer_size = 16777216;

-- Enable InnoDB adaptive hash index for better read performance
-- SET GLOBAL innodb_adaptive_hash_index = ON;

-- Increase table definition cache for better table access
-- SET GLOBAL table_definition_cache = 400;

-- Increase table open cache for concurrent table access
-- SET GLOBAL table_open_cache = 4000;

-- Set query cache size (MySQL 5.7 and below)
-- SET GLOBAL query_cache_size = 67108864;
-- SET GLOBAL query_cache_type = ON;

-- ============================================================
-- PARTITIONING FOR LARGE TABLES (Optional - for very large datasets)
-- ============================================================

-- Partition stock_movements by date for better query performance
-- Uncomment if you have very large datasets (millions of records)
/*
ALTER TABLE `stock_movements` PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);
*/

-- Partition audit_logs by date
/*
ALTER TABLE `audit_logs` PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);
*/

-- ============================================================
-- CLEANUP AND MAINTENANCE
-- ============================================================

-- Analyze tables to update statistics
ANALYZE TABLE `sales`;
ANALYZE TABLE `sale_items`;
ANALYZE TABLE `purchases`;
ANALYZE TABLE `purchase_items`;
ANALYZE TABLE `stock`;
ANALYZE TABLE `stock_movements`;
ANALYZE TABLE `products`;
ANALYZE TABLE `customers`;
ANALYZE TABLE `suppliers`;
ANALYZE TABLE `notifications`;
ANALYZE TABLE `audit_logs`;

-- Optimize tables to reclaim space and rebuild indexes
OPTIMIZE TABLE `sales`;
OPTIMIZE TABLE `sale_items`;
OPTIMIZE TABLE `purchases`;
OPTIMIZE TABLE `purchase_items`;
OPTIMIZE TABLE `stock`;
OPTIMIZE TABLE `stock_movements`;
OPTIMIZE TABLE `products`;
OPTIMIZE TABLE `customers`;
OPTIMIZE TABLE `suppliers`;
OPTIMIZE TABLE `notifications`;
OPTIMIZE TABLE `audit_logs`;

-- ============================================================
-- MONITORING QUERIES
-- ============================================================

-- Check current indexes
-- SHOW INDEX FROM `sales`;
-- SHOW INDEX FROM `stock_movements`;

-- Check table sizes
-- SELECT 
--     table_name AS 'Table',
--     ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
-- FROM information_schema.TABLES
-- WHERE table_schema = 'tellerpro'
-- ORDER BY (data_length + index_length) DESC;

-- Check slow queries (enable slow query log first)
-- SET GLOBAL slow_query_log = 'ON';
-- SET GLOBAL long_query_time = 2;
-- SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
