-- Database Optimization Script for WHMLab
-- Run this after backing up your database

-- ============================================
-- 1. Add Performance Indexes
-- ============================================

-- User table optimizations
ALTER TABLE `users` ADD INDEX `idx_email_status` (`email`, `status`);
ALTER TABLE `users` ADD INDEX `idx_status_created` (`status`, `created_at`);
ALTER TABLE `users` ADD INDEX `idx_email_verified` (`email_verified_at`);

-- Admin table optimizations
ALTER TABLE `admins` ADD INDEX `idx_username_status` (`username`, `status`);
ALTER TABLE `admins` ADD INDEX `idx_email_status` (`email`, `status`);
ALTER TABLE `admins` ADD INDEX `idx_role_status` (`role_id`, `status`);

-- Orders table optimizations
ALTER TABLE `orders` ADD INDEX `idx_user_status_created` (`user_id`, `status`, `created_at`);
ALTER TABLE `orders` ADD INDEX `idx_status_total` (`status`, `total_amount`);
ALTER TABLE `orders` ADD INDEX `idx_created_status` (`created_at`, `status`);

-- Invoices table optimizations
ALTER TABLE `invoices` ADD INDEX `idx_user_status_due` (`user_id`, `status`, `due_date`);
ALTER TABLE `invoices` ADD INDEX `idx_due_date_status` (`due_date`, `status`);
ALTER TABLE `invoices` ADD INDEX `idx_invoice_number` (`invoice_number`);

-- Domains table optimizations  
ALTER TABLE `domains` ADD INDEX `idx_user_status_expire` (`user_id`, `status`, `expired_date`);
ALTER TABLE `domains` ADD INDEX `idx_domain_name` (`domain`);
ALTER TABLE `domains` ADD INDEX `idx_expired_date_status` (`expired_date`, `status`);

-- Hosting table optimizations
ALTER TABLE `hostings` ADD INDEX `idx_user_domain_status` (`user_id`, `domain_id`, `status`);
ALTER TABLE `hostings` ADD INDEX `idx_server_status` (`server_id`, `status`);
ALTER TABLE `hostings` ADD INDEX `idx_expired_date` (`expired_date`);

-- Deposits table optimizations
ALTER TABLE `deposits` ADD INDEX `idx_user_method_status` (`user_id`, `method_code`, `status`);
ALTER TABLE `deposits` ADD INDEX `idx_trx_status` (`trx`, `status`);
ALTER TABLE `deposits` ADD INDEX `idx_created_status` (`created_at`, `status`);

-- Support tickets optimizations
ALTER TABLE `support_tickets` ADD INDEX `idx_user_status_priority` (`user_id`, `status`, `priority`);
ALTER TABLE `support_tickets` ADD INDEX `idx_ticket_number` (`ticket`);
ALTER TABLE `support_tickets` ADD INDEX `idx_last_reply` (`last_reply`);

-- Products table optimizations
ALTER TABLE `products` ADD INDEX `idx_category_status` (`category_id`, `status`);
ALTER TABLE `products` ADD INDEX `idx_featured_status` (`featured`, `status`);
ALTER TABLE `products` ADD INDEX `idx_price_status` (`price`, `status`);

-- Shopping cart optimizations
ALTER TABLE `shopping_carts` ADD INDEX `idx_session_type` (`session_id`, `type`);
ALTER TABLE `shopping_carts` ADD INDEX `idx_user_session` (`user_id`, `session_id`);

-- ============================================
-- 2. Optimize Table Structure
-- ============================================

-- Optimize varchar sizes based on actual usage
-- (Run these only if your data fits within the new limits)

-- ALTER TABLE `users` MODIFY `mobile` VARCHAR(20);
-- ALTER TABLE `users` MODIFY `username` VARCHAR(30);
-- ALTER TABLE `admins` MODIFY `username` VARCHAR(30);

-- ============================================
-- 3. Add Missing Primary Keys and Foreign Keys
-- ============================================

-- Add foreign key constraints for data integrity
-- (Uncomment and modify based on your actual table structure)

-- ALTER TABLE `orders` ADD FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE;
-- ALTER TABLE `invoices` ADD FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE;
-- ALTER TABLE `domains` ADD FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE;
-- ALTER TABLE `hostings` ADD FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE;
-- ALTER TABLE `hostings` ADD FOREIGN KEY (`domain_id`) REFERENCES `domains`(`id`) ON DELETE CASCADE;

-- ============================================
-- 4. Clean Up Old Data (Optional - BE CAREFUL)
-- ============================================

-- Clean up old sessions (older than 30 days)
-- DELETE FROM `sessions` WHERE `last_activity` < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY));

-- Clean up expired password resets (older than 24 hours)
-- DELETE FROM `password_resets` WHERE `created_at` < DATE_SUB(NOW(), INTERVAL 24 HOUR);

-- Clean up old failed jobs (older than 7 days)
-- DELETE FROM `failed_jobs` WHERE `failed_at` < DATE_SUB(NOW(), INTERVAL 7 DAY);

-- ============================================
-- 5. Optimize Tables
-- ============================================

-- Analyze tables to update statistics
ANALYZE TABLE `users`;
ANALYZE TABLE `admins`;
ANALYZE TABLE `orders`;
ANALYZE TABLE `invoices`;
ANALYZE TABLE `domains`;
ANALYZE TABLE `hostings`;
ANALYZE TABLE `deposits`;
ANALYZE TABLE `support_tickets`;
ANALYZE TABLE `products`;
ANALYZE TABLE `shopping_carts`;

-- Optimize tables to reclaim space and defragment
OPTIMIZE TABLE `users`;
OPTIMIZE TABLE `admins`;
OPTIMIZE TABLE `orders`;
OPTIMIZE TABLE `invoices`;
OPTIMIZE TABLE `domains`;
OPTIMIZE TABLE `hostings`;
OPTIMIZE TABLE `deposits`;
OPTIMIZE TABLE `support_tickets`;
OPTIMIZE TABLE `products`;
OPTIMIZE TABLE `shopping_carts`;

-- ============================================
-- 6. Update MySQL Configuration Suggestions
-- ============================================

-- Add these to your MySQL configuration file (my.cnf or my.ini)
/*
[mysqld]
# Buffer pool size (set to 70-80% of available RAM)
innodb_buffer_pool_size = 1G

# Log file settings
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M

# Connection settings
max_connections = 200
max_connect_errors = 10000

# Query cache (if using MySQL 5.7 or earlier)
query_cache_size = 32M
query_cache_type = 1

# Table cache
table_open_cache = 2000

# Thread cache
thread_cache_size = 32

# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

# Binary logging
log_bin = mysql-bin
expire_logs_days = 7
max_binlog_size = 100M

# InnoDB settings
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
*/

-- ============================================
-- 7. Create Stored Procedures for Common Operations
-- ============================================

DELIMITER //

-- Procedure to get user statistics
DROP PROCEDURE IF EXISTS GetUserStats//
CREATE PROCEDURE GetUserStats(IN user_id INT)
BEGIN
    SELECT 
        u.id,
        u.username,
        u.email,
        COUNT(DISTINCT o.id) as total_orders,
        COUNT(DISTINCT i.id) as total_invoices,
        COUNT(DISTINCT d.id) as total_domains,
        COUNT(DISTINCT h.id) as total_hostings,
        COALESCE(SUM(i.total_amount), 0) as total_spent
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    LEFT JOIN invoices i ON u.id = i.user_id AND i.status = 1
    LEFT JOIN domains d ON u.id = d.user_id
    LEFT JOIN hostings h ON u.id = h.user_id
    WHERE u.id = user_id
    GROUP BY u.id, u.username, u.email;
END//

-- Procedure to clean up expired data
DROP PROCEDURE IF EXISTS CleanupExpiredData//
CREATE PROCEDURE CleanupExpiredData()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cleanup_count INT DEFAULT 0;
    
    -- Clean up old sessions
    DELETE FROM sessions WHERE last_activity < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY));
    SET cleanup_count = ROW_COUNT();
    
    -- Clean up expired password resets
    DELETE FROM password_resets WHERE created_at < DATE_SUB(NOW(), INTERVAL 24 HOUR);
    SET cleanup_count = cleanup_count + ROW_COUNT();
    
    -- Clean up old failed jobs
    DELETE FROM failed_jobs WHERE failed_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
    SET cleanup_count = cleanup_count + ROW_COUNT();
    
    SELECT CONCAT('Cleaned up ', cleanup_count, ' expired records') as result;
END//

DELIMITER ;

-- ============================================
-- 8. Create Views for Common Queries
-- ============================================

-- View for active users with their stats
CREATE OR REPLACE VIEW active_users_stats AS
SELECT 
    u.id,
    u.username,
    u.email,
    u.firstname,
    u.lastname,
    u.created_at,
    COUNT(DISTINCT o.id) as total_orders,
    COUNT(DISTINCT i.id) as total_invoices,
    COUNT(DISTINCT d.id) as total_domains,
    COALESCE(SUM(i.total_amount), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN invoices i ON u.id = i.user_id AND i.status = 1
LEFT JOIN domains d ON u.id = d.user_id
WHERE u.status = 1
GROUP BY u.id, u.username, u.email, u.firstname, u.lastname, u.created_at;

-- View for pending invoices
CREATE OR REPLACE VIEW pending_invoices AS
SELECT 
    i.*,
    u.username,
    u.email,
    DATEDIFF(i.due_date, CURDATE()) as days_until_due
FROM invoices i
JOIN users u ON i.user_id = u.id
WHERE i.status = 0
ORDER BY i.due_date ASC;

-- View for expiring domains
CREATE OR REPLACE VIEW expiring_domains AS
SELECT 
    d.*,
    u.username,
    u.email,
    DATEDIFF(d.expired_date, CURDATE()) as days_until_expiry
FROM domains d
JOIN users u ON d.user_id = u.id
WHERE d.status = 1 AND d.expired_date <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)
ORDER BY d.expired_date ASC;

-- ============================================
-- 9. Add Database Triggers for Audit
-- ============================================

-- Trigger to log admin login attempts
DROP TRIGGER IF EXISTS admin_login_log;

DELIMITER //
CREATE TRIGGER admin_login_log
AFTER UPDATE ON admins
FOR EACH ROW
BEGIN
    IF NEW.last_login_at != OLD.last_login_at THEN
        INSERT INTO admin_login_logs (admin_id, login_time, ip_address, created_at)
        VALUES (NEW.id, NEW.last_login_at, NEW.last_login_ip, NOW());
    END IF;
END//
DELIMITER ;

-- Create admin login logs table if it doesn't exist
CREATE TABLE IF NOT EXISTS admin_login_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL,
    login_time TIMESTAMP NULL,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_admin_time (admin_id, login_time)
);

-- ============================================
-- 10. Performance Monitoring Queries
-- ============================================

-- Query to find slow queries (run periodically)
/*
SELECT 
    query_time,
    lock_time,
    rows_sent,
    rows_examined,
    sql_text
FROM mysql.slow_log 
WHERE start_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
ORDER BY query_time DESC 
LIMIT 10;
*/

-- Query to check index usage
/*
SELECT 
    table_name,
    index_name,
    cardinality,
    sub_part,
    packed,
    nullable,
    index_type
FROM information_schema.statistics
WHERE table_schema = 'zeeshanmudassir_clientarea'
ORDER BY table_name, seq_in_index;
*/

-- ============================================
-- COMPLETION MESSAGE
-- ============================================

SELECT 'Database optimization completed successfully!' as MESSAGE,
       'Run ANALYZE TABLE and OPTIMIZE TABLE periodically' as RECOMMENDATION,
       'Monitor slow query log for performance issues' as MONITORING,
       NOW() as COMPLETED_AT;