-- Example: How to add new tables to billing.sql
-- Add these SQL statements to the end of setup/install/billing.sql

-- =============================================
-- NEW TABLES FOR VERSION 3.3
-- =============================================

-- Table: Audit Log for tracking user actions
CREATE TABLE IF NOT EXISTS `db_audit_log` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) DEFAULT NULL,
    `action` varchar(100) DEFAULT NULL,
    `table_name` varchar(100) DEFAULT NULL,
    `record_id` int(11) DEFAULT NULL,
    `old_values` text,
    `new_values` text,
    `ip_address` varchar(45) DEFAULT NULL,
    `user_agent` text,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`),
    KEY `action` (`action`),
    KEY `table_name` (`table_name`),
    KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: System Settings for application configuration
CREATE TABLE IF NOT EXISTS `db_system_settings` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `setting_key` varchar(100) NOT NULL,
    `setting_value` text,
    `setting_type` enum('string','integer','boolean','json') DEFAULT 'string',
    `description` text,
    `is_public` tinyint(1) DEFAULT '0',
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: User Activity Log
CREATE TABLE IF NOT EXISTS `db_user_activity` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) DEFAULT NULL,
    `activity_type` varchar(50) DEFAULT NULL,
    `description` text,
    `ip_address` varchar(45) DEFAULT NULL,
    `user_agent` text,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`),
    KEY `activity_type` (`activity_type`),
    KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: System Notifications
CREATE TABLE IF NOT EXISTS `db_notifications` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) DEFAULT NULL,
    `title` varchar(255) DEFAULT NULL,
    `message` text,
    `type` enum('info','warning','error','success') DEFAULT 'info',
    `is_read` tinyint(1) DEFAULT '0',
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `read_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`),
    KEY `is_read` (`is_read`),
    KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- INSERT DEFAULT DATA
-- =============================================

-- Insert default system settings
INSERT INTO `db_system_settings` (`setting_key`, `setting_value`, `setting_type`, `description`, `is_public`) VALUES
('maintenance_mode', '0', 'boolean', 'Enable/disable maintenance mode', 0),
('backup_retention_days', '30', 'integer', 'Number of days to retain backups', 0),
('email_notifications', '1', 'boolean', 'Enable email notifications', 0),
('max_login_attempts', '5', 'integer', 'Maximum login attempts before lockout', 0),
('session_timeout', '3600', 'integer', 'Session timeout in seconds', 0),
('default_currency', 'USD', 'string', 'Default currency code', 1),
('company_name', 'NCS Billing System', 'string', 'Company name', 1),
('company_email', 'admin@example.com', 'string', 'Company email address', 1);

-- Insert sample notification
INSERT INTO `db_notifications` (`user_id`, `title`, `message`, `type`) VALUES
(1, 'Welcome to NCS Billing System', 'Your system has been successfully updated to version 3.3!', 'success');

-- =============================================
-- ADD NEW COLUMNS TO EXISTING TABLES (if needed)
-- =============================================

-- Example: Add new column to existing table
-- ALTER TABLE `db_users` ADD COLUMN `last_activity` timestamp NULL DEFAULT NULL AFTER `updated_at`;

-- Example: Add new index to existing table
-- ALTER TABLE `db_customers` ADD INDEX `idx_email` (`email`);

-- =============================================
-- NOTES
-- =============================================
-- 1. Always use CREATE TABLE IF NOT EXISTS to avoid errors
-- 2. Include proper indexes for performance
-- 3. Use appropriate data types and constraints
-- 4. Add comments to explain the purpose of each table
-- 5. Test the SQL on a development database first
-- 6. Backup your database before running updates

