-- ====================================================================
-- PHPNuxBill Database Optimization Script
-- Adds indexes, constraints, and optimizations for better performance
-- Run this script in your MySQL database
-- ====================================================================

USE `demonux`;

-- ====================================================================
-- 1. ADD MISSING INDEXES FOR FREQUENTLY QUERIED COLUMNS
-- ====================================================================

-- Optimize tbl_appconfig queries (used heavily in download.php)
ALTER TABLE `tbl_appconfig` 
ADD UNIQUE INDEX IF NOT EXISTS `idx_setting` (`setting` ASC);

-- Optimize router lookups
ALTER TABLE `tbl_routers` 
ADD INDEX IF NOT EXISTS `idx_name` (`name` ASC),
ADD INDEX IF NOT EXISTS `idx_type` (`type` ASC),
ADD INDEX IF NOT EXISTS `idx_status` (`enabled` ASC);

-- Optimize plan lookups
ALTER TABLE `tbl_plans` 
ADD INDEX IF NOT EXISTS `idx_router_type` (`routers` ASC, `type` ASC),
ADD INDEX IF NOT EXISTS `idx_enabled` (`enabled` ASC),
ADD INDEX IF NOT EXISTS `idx_price` (`price` ASC);

-- Optimize user lookups
ALTER TABLE `tbl_customers` 
ADD INDEX IF NOT EXISTS `idx_username` (`username` ASC),
ADD INDEX IF NOT EXISTS `idx_phonenumber` (`phonenumber` ASC),
ADD INDEX IF NOT EXISTS `idx_email` (`email` ASC);

-- Optimize voucher searches
ALTER TABLE `tbl_voucher` 
ADD INDEX IF NOT EXISTS `idx_code` (`code` ASC),
ADD INDEX IF NOT EXISTS `idx_status` (`status` ASC),
ADD INDEX IF NOT EXISTS `idx_code_status` (`code` ASC, `status` ASC);

-- Optimize recharge history
ALTER TABLE `tbl_user_recharges` 
ADD INDEX IF NOT EXISTS `idx_customer_id` (`customer_id` ASC),
ADD INDEX IF NOT EXISTS `idx_recharged_on` (`recharged_on` DESC),
ADD INDEX IF NOT EXISTS `idx_status` (`status` ASC),
ADD INDEX IF NOT EXISTS `idx_customer_status_date` (`customer_id` ASC, `status` ASC, `recharged_on` DESC);

-- Optimize transaction lookups  
ALTER TABLE `tbl_transactions` 
ADD INDEX IF NOT EXISTS `idx_invoice` (`invoice` ASC),
ADD INDEX IF NOT EXISTS `idx_username` (`username` ASC),
ADD INDEX IF NOT EXISTS `idx_recharged_on` (`recharged_on` DESC);

-- ====================================================================
-- 2. OPTIMIZE TABLE STRUCTURES
-- ====================================================================

-- Ensure proper character set for international characters
ALTER TABLE `tbl_appconfig` 
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `tbl_customers` 
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- ====================================================================
-- 3. ADD DATABASE CONSTRAINTS FOR DATA INTEGRITY
-- ====================================================================

-- Ensure router type is valid
ALTER TABLE `tbl_routers` 
MODIFY COLUMN `type` ENUM('mikrotik', 'radius', 'freeradius') NOT NULL DEFAULT 'mikrotik';

-- Ensure plan type is valid
ALTER TABLE `tbl_plans` 
MODIFY COLUMN `type` ENUM('Hotspot', 'PPPOE', 'Wireless') NOT NULL DEFAULT 'Hotspot';

-- ====================================================================
-- 4. CREATE ARCHIVE TABLES FOR OLD DATA
-- ====================================================================

-- Archive table for old recharges (keeps main table smaller)
CREATE TABLE IF NOT EXISTS `tbl_user_recharges_archive` LIKE `tbl_user_recharges`;

-- Archive table for old transactions
CREATE TABLE IF NOT EXISTS `tbl_transactions_archive` LIKE `tbl_transactions`;

-- ====================================================================
-- 5. CREATE AUTOMATED CLEANUP EVENTS
-- ====================================================================

-- Enable event scheduler (if not already enabled)
SET GLOBAL event_scheduler = ON;

-- Archive old recharges (older than 12 months) - runs monthly
DROP EVENT IF EXISTS `archive_old_recharges`;
CREATE EVENT `archive_old_recharges`
ON SCHEDULE EVERY 1 MONTH
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    -- Move old records to archive
    INSERT INTO `tbl_user_recharges_archive` 
    SELECT * FROM `tbl_user_recharges` 
    WHERE `recharged_on` < DATE_SUB(NOW(), INTERVAL 12 MONTH);
    
    -- Delete archived records from main table
    DELETE FROM `tbl_user_recharges` 
    WHERE `recharged_on` < DATE_SUB(NOW(), INTERVAL 12 MONTH);
END;

-- Archive old transactions (older than 12 months) - runs monthly
DROP EVENT IF EXISTS `archive_old_transactions`;
CREATE EVENT `archive_old_transactions`
ON SCHEDULE EVERY 1 MONTH
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    INSERT INTO `tbl_transactions_archive` 
    SELECT * FROM `tbl_transactions` 
    WHERE `recharged_on` < DATE_SUB(NOW(), INTERVAL 12 MONTH);
    
    DELETE FROM `tbl_transactions` 
    WHERE `recharged_on` < DATE_SUB(NOW(), INTERVAL 12 MONTH);
END;

-- Clean up expired sessions daily
DROP EVENT IF EXISTS `cleanup_expired_sessions`;
CREATE EVENT `cleanup_expired_sessions`
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    -- Delete sessions older than 7 days (if you have a sessions table)
    -- DELETE FROM `sessions` WHERE `last_activity` < DATE_SUB(NOW(), INTERVAL 7 DAY);
    
    -- Clean up cache directory (via stored procedure)
    -- You may want to create a separate cleanup script for file system
END;

-- ====================================================================
-- 6. OPTIMIZE TABLE STORAGE
-- ====================================================================

-- Optimize all tables to reclaim space and update statistics
OPTIMIZE TABLE `tbl_appconfig`;
OPTIMIZE TABLE `tbl_routers`;
OPTIMIZE TABLE `tbl_plans`;
OPTIMIZE TABLE `tbl_customers`;
OPTIMIZE TABLE `tbl_voucher`;
OPTIMIZE TABLE `tbl_user_recharges`;
OPTIMIZE TABLE `tbl_transactions`;

-- ====================================================================
-- 7. ADD PERFORMANCE MONITORING VIEW
-- ====================================================================

-- Create view for performance monitoring
DROP VIEW IF EXISTS `v_performance_stats`;
CREATE VIEW `v_performance_stats` AS
SELECT 
    'Active Customers' as metric,
    COUNT(*) as value
FROM `tbl_customers`
WHERE `status` = 'Active'

UNION ALL

SELECT 
    'Unused Vouchers',
    COUNT(*)
FROM `tbl_voucher`
WHERE `status` = 'active'

UNION ALL

SELECT 
    'Total Revenue (This Month)',
    COALESCE(SUM(`price`), 0)
FROM `tbl_user_recharges`
WHERE `recharged_on` >= DATE_FORMAT(NOW(), '%Y-%m-01')
AND `status` = 'on'

UNION ALL

SELECT 
    'Plans Configured',
    COUNT(*)
FROM `tbl_plans`
WHERE `enabled` = 1

UNION ALL

SELECT 
    'Routers Active',
    COUNT(*)
FROM `tbl_routers`
WHERE `enabled` = 1;

-- ====================================================================
-- 8. VERIFY INDEX CREATION
-- ====================================================================

-- Show all indexes on critical tables
SHOW INDEX FROM `tbl_appconfig`;
SHOW INDEX FROM `tbl_routers`;
SHOW INDEX FROM `tbl_plans`;
SHOW INDEX FROM `tbl_customers`;
SHOW INDEX FROM `tbl_voucher`;
SHOW INDEX FROM `tbl_user_recharges`;

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

SELECT 
    'Database optimization complete!' as Status,
    'Indexes added, constraints updated, and monitoring enabled' as Message,
    NOW() as Timestamp;

-- ====================================================================
-- RECOMMENDED: Run ANALYZE TABLE monthly in production
-- ====================================================================

-- Schedule this command to run monthly:
-- ANALYZE TABLE tbl_appconfig, tbl_routers, tbl_plans, tbl_customers, tbl_voucher, tbl_user_recharges, tbl_transactions;
