-- Relevant Reflex Panel Management System Database Schema
-- Create this database structure in phpMyAdmin
-- Make sure to update the database credentials in config.php

-- Create database (run this first if database doesn't exist)
-- CREATE DATABASE relevant_reflex_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE relevant_reflex_db;

-- --------------------------------------------------------
-- Table structure for table `users`
-- --------------------------------------------------------

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `role` enum('admin','manager','user') NOT NULL DEFAULT 'user',
  `status` enum('active','inactive','suspended') NOT NULL DEFAULT 'active',
  `phone` varchar(20) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `last_login` timestamp NULL DEFAULT NULL,
  `login_count` int(11) DEFAULT 0,
  `email_verified` tinyint(1) DEFAULT 0,
  `two_factor_enabled` tinyint(1) DEFAULT 0,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_role` (`role`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `panels`
-- --------------------------------------------------------

CREATE TABLE `panels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `type` enum('survey','feedback','research','evaluation') DEFAULT 'survey',
  `status` enum('draft','active','paused','completed','archived') DEFAULT 'draft',
  `created_by` int(11) NOT NULL,
  `target_responses` int(11) DEFAULT 100,
  `current_responses` int(11) DEFAULT 0,
  `completion_rate` decimal(5,2) DEFAULT 0.00,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `settings` json DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_created_by` (`created_by`),
  KEY `idx_status` (`status`),
  KEY `idx_type` (`type`),
  KEY `idx_end_date` (`end_date`),
  FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `panel_responses`
-- --------------------------------------------------------

CREATE TABLE `panel_responses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `panel_id` int(11) NOT NULL,
  `respondent_email` varchar(255) NOT NULL,
  `respondent_name` varchar(255) DEFAULT NULL,
  `response_data` json NOT NULL,
  `completion_time` int(11) DEFAULT NULL COMMENT 'Time in seconds',
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `submitted_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_panel_id` (`panel_id`),
  KEY `idx_submitted_at` (`submitted_at`),
  KEY `idx_respondent_email` (`respondent_email`),
  FOREIGN KEY (`panel_id`) REFERENCES `panels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `supplies`
-- --------------------------------------------------------

CREATE TABLE `supplies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `unit_cost` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total_cost` decimal(12,2) GENERATED ALWAYS AS (`quantity` * `unit_cost`) STORED,
  `supplier` varchar(255) DEFAULT NULL,
  `supplier_contact` varchar(255) DEFAULT NULL,
  `status` enum('ordered','pending','received','cancelled','returned') DEFAULT 'pending',
  `order_date` date DEFAULT NULL,
  `expected_delivery` date DEFAULT NULL,
  `actual_delivery` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_category` (`category`),
  KEY `idx_order_date` (`order_date`),
  KEY `idx_created_by` (`created_by`),
  FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `finances`
-- --------------------------------------------------------

CREATE TABLE `finances` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_type` enum('revenue','expense','refund','adjustment') NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `description` varchar(500) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `reference_id` varchar(100) DEFAULT NULL COMMENT 'External reference (invoice, order, etc.)',
  `related_panel_id` int(11) DEFAULT NULL,
  `related_supply_id` int(11) DEFAULT NULL,
  `payment_method` varchar(50) DEFAULT NULL,
  `transaction_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `paid_date` date DEFAULT NULL,
  `status` enum('pending','completed','cancelled','failed') DEFAULT 'completed',
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_transaction_type` (`transaction_type`),
  KEY `idx_category` (`category`),
  KEY `idx_transaction_date` (`transaction_date`),
  KEY `idx_status` (`status`),
  KEY `idx_related_panel` (`related_panel_id`),
  KEY `idx_related_supply` (`related_supply_id`),
  KEY `idx_created_by` (`created_by`),
  FOREIGN KEY (`related_panel_id`) REFERENCES `panels` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`related_supply_id`) REFERENCES `supplies` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `support_tickets`
-- --------------------------------------------------------

CREATE TABLE `support_tickets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticket_number` varchar(20) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `subject` varchar(500) NOT NULL,
  `message` text NOT NULL,
  `priority` enum('low','medium','high','critical') DEFAULT 'medium',
  `status` enum('open','in_progress','resolved','closed','reopened') DEFAULT 'open',
  `category` varchar(100) DEFAULT NULL,
  `assigned_to` int(11) DEFAULT NULL,
  `resolution` text DEFAULT NULL,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ticket_number` (`ticket_number`),
  KEY `idx_status` (`status`),
  KEY `idx_priority` (`priority`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_assigned_to` (`assigned_to`),
  KEY `idx_created_at` (`created_at`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `system_settings`
-- --------------------------------------------------------

CREATE TABLE `system_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `setting_type` enum('string','integer','boolean','json','date') DEFAULT 'string',
  `description` varchar(500) DEFAULT NULL,
  `is_public` tinyint(1) DEFAULT 0,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp 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 structure for table `activity_logs`
-- --------------------------------------------------------

CREATE TABLE `activity_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `resource_type` varchar(50) DEFAULT NULL COMMENT 'panel, user, supply, etc.',
  `resource_id` int(11) DEFAULT NULL,
  `description` varchar(500) NOT NULL,
  `metadata` json DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_action` (`action`),
  KEY `idx_resource` (`resource_type`, `resource_id`),
  KEY `idx_created_at` (`created_at`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `demand_analytics`
-- --------------------------------------------------------

CREATE TABLE `demand_analytics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category` varchar(100) NOT NULL,
  `demand_level` enum('low','medium','high') NOT NULL,
  `growth_rate` decimal(5,2) DEFAULT NULL COMMENT 'Percentage growth',
  `market_score` int(11) DEFAULT NULL COMMENT '0-100 score',
  `forecast_accuracy` decimal(5,2) DEFAULT NULL,
  `recorded_date` date NOT NULL,
  `data_source` varchar(100) DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category`),
  KEY `idx_recorded_date` (`recorded_date`),
  KEY `idx_demand_level` (`demand_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `notifications`
-- --------------------------------------------------------

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `type` enum('info','success','warning','error') DEFAULT 'info',
  `is_read` tinyint(1) DEFAULT 0,
  `action_url` varchar(500) DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_is_read` (`is_read`),
  KEY `idx_created_at` (`created_at`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Insert default system settings
-- --------------------------------------------------------

INSERT INTO `system_settings` (`setting_key`, `setting_value`, `setting_type`, `description`, `is_public`) VALUES
('site_name', 'Relevant Reflex', 'string', 'Site name displayed throughout the application', 1),
('timezone', 'America/New_York', 'string', 'Default system timezone', 0),
('currency', 'USD', 'string', 'Default currency for financial transactions', 0),
('date_format', 'M j, Y', 'string', 'Default date format', 0),
('pagination_limit', '20', 'integer', 'Default number of items per page', 0),
('email_notifications', '1', 'boolean', 'Enable email notifications system-wide', 0),
('sms_notifications', '0', 'boolean', 'Enable SMS notifications system-wide', 0),
('session_timeout', '1800', 'integer', 'Session timeout in seconds (30 minutes)', 0),
('max_file_upload', '5242880', 'integer', 'Maximum file upload size in bytes (5MB)', 0),
('maintenance_mode', '0', 'boolean', 'Enable maintenance mode', 0);

-- --------------------------------------------------------
-- Insert sample admin user (password: admin123)
-- --------------------------------------------------------

INSERT INTO `users` (`username`, `email`, `password_hash`, `first_name`, `last_name`, `role`, `status`, `email_verified`) VALUES
('admin', 'admin@relevantreflex.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System', 'Administrator', 'admin', 'active', 1);

-- --------------------------------------------------------
-- Insert sample data for demonstration
-- --------------------------------------------------------

-- Sample panels
INSERT INTO `panels` (`name`, `description`, `type`, `status`, `created_by`, `target_responses`, `current_responses`, `completion_rate`, `start_date`, `end_date`) VALUES
('Customer Satisfaction Q3 2025', 'Quarterly customer satisfaction survey to measure service quality and identify improvement areas.', 'survey', 'active', 1, 500, 245, 49.00, '2025-08-15', '2025-09-30'),
('Product Feedback Survey', 'Collecting feedback on our latest product features and user experience.', 'feedback', 'draft', 1, 300, 0, 0.00, NULL, '2025-10-15'),
('Market Research Panel Q2', 'Comprehensive market analysis for Q2 strategic planning and competitive positioning.', 'research', 'completed', 1, 1000, 1024, 102.40, '2025-07-20', '2025-08-31'),
('Employee Engagement Survey', 'Internal survey to measure employee satisfaction and engagement levels.', 'survey', 'active', 1, 150, 67, 44.67, '2025-08-28', '2025-09-15');

-- Sample supplies
INSERT INTO `supplies` (`item_name`, `description`, `category`, `quantity`, `unit_cost`, `supplier`, `status`, `order_date`, `expected_delivery`) VALUES
('Survey Tablets', 'iPad Air for field data collection', 'Equipment', 25, 299.99, 'TechCorp Solutions', 'received', '2025-08-15', '2025-08-25'),
('Data Collection Software License', 'Annual license for survey platform', 'Software', 50, 49.99, 'DataSoft Inc', 'received', '2025-08-20', '2025-08-22'),
('Mobile Hotspots', 'Portable internet connectivity devices', 'Equipment', 15, 89.99, 'ConnectTech Ltd', 'pending', '2025-09-01', '2025-09-10'),
('Office Supplies', 'General office supplies for Q3', 'Office', 100, 12.50, 'OfficeMax Pro', 'ordered', '2025-09-02', '2025-09-08'),
('Cloud Storage Subscription', 'Enterprise cloud storage solution', 'Software', 1, 199.99, 'CloudProvider Inc', 'received', '2025-07-01', '2025-07-01');

-- Sample financial transactions
INSERT INTO `finances` (`transaction_type`, `amount`, `description`, `category`, `transaction_date`, `status`) VALUES
('revenue', 2500.00, 'Panel completion payment - Technology Survey', 'Panel Revenue', '2025-09-03', 'completed'),
('expense', -450.00, 'Cloud hosting monthly subscription', 'Infrastructure', '2025-09-02', 'completed'),
('revenue', 1800.00, 'Market research project payment', 'Research Revenue', '2025-09-01', 'completed'),
('expense', -320.00, 'Software licenses renewal', 'Software', '2025-08-31', 'completed'),
('revenue', 3200.00, 'Enterprise client onboarding fee', 'New Business', '2025-08-30', 'completed'),
('expense', -125.00, 'Office supplies purchase', 'Office', '2025-08-29', 'completed'),
('revenue', 1500.00, 'Consultation services', 'Services', '2025-08-28', 'completed');

-- Sample support tickets
INSERT INTO `support_tickets` (`ticket_number`, `user_id`, `name`, `email`, `subject`, `message`, `priority`, `status`) VALUES
('TK-2025-001', 1, 'John Smith', 'john@example.com', 'Dashboard loading slowly', 'The dashboard takes too long to load, especially the charts section. This has been happening for the past few days.', 'medium', 'in_progress'),
('TK-2025-002', NULL, 'Sarah Johnson', 'sarah@external.com', 'Export function not working', 'When I try to export panel data to CSV, I get an error message. Please help resolve this issue.', 'high', 'resolved'),
('TK-2025-003', 1, 'Mike Davis', 'mike@contractor.com', 'User permissions question', 'I need clarification on user permission levels and how to assign different access rights to team members.', 'low', 'open');

-- Sample activity logs
INSERT INTO `activity_logs` (`user_id`, `action`, `resource_type`, `resource_id`, `description`, `ip_address`) VALUES
(1, 'login', NULL, NULL, 'User logged in successfully', '192.168.1.100'),
(1, 'create', 'panel', 1, 'Created new panel: Customer Satisfaction Q3 2025', '192.168.1.100'),
(1, 'update', 'user', 1, 'Updated user profile information', '192.168.1.100'),
(1, 'create', 'supply', 1, 'Added new supply item: Survey Tablets', '192.168.1.100');

-- Sample demand analytics
INSERT INTO `demand_analytics` (`category`, `demand_level`, `growth_rate`, `market_score`, `forecast_accuracy`, `recorded_date`) VALUES
('Technology Panels', 'high', 23.50, 92, 87.50, '2025-09-01'),
('Healthcare Surveys', 'medium', 12.30, 76, 82.10, '2025-09-01'),
('Financial Services', 'high', 18.70, 88, 85.30, '2025-09-01'),
('Retail Analytics', 'low', -5.20, 54, 78.90, '2025-09-01'),
('Education Research', 'medium', 8.40, 69, 80.20, '2025-09-01');

-- --------------------------------------------------------
-- Create indexes for better performance
-- --------------------------------------------------------

-- Additional composite indexes for common queries
ALTER TABLE `panel_responses` ADD INDEX `idx_panel_date` (`panel_id`, `submitted_at`);
ALTER TABLE `finances` ADD INDEX `idx_type_date` (`transaction_type`, `transaction_date`);
ALTER TABLE `activity_logs` ADD INDEX `idx_user_date` (`user_id`, `created_at`);
ALTER TABLE `supplies` ADD INDEX `idx_status_date` (`status`, `order_date`);

-- --------------------------------------------------------
-- Create views for common queries
-- --------------------------------------------------------

-- View for panel statistics
CREATE VIEW `panel_stats` AS
SELECT 
    p.id,
    p.name,
    p.status,
    p.target_responses,
    p.current_responses,
    p.completion_rate,
    COUNT(pr.id) as actual_responses,
    p.created_at,
    CONCAT(u.first_name, ' ', u.last_name) as created_by_name
FROM panels p
LEFT JOIN panel_responses pr ON p.id = pr.panel_id
LEFT JOIN users u ON p.created_by = u.id
GROUP BY p.id;

-- View for financial summary
CREATE VIEW `financial_summary` AS
SELECT 
    DATE_FORMAT(transaction_date, '%Y-%m') as month,
    SUM(CASE WHEN transaction_type = 'revenue' THEN amount ELSE 0 END) as total_revenue,
    SUM(CASE WHEN transaction_type = 'expense' THEN ABS(amount) ELSE 0 END) as total_expenses,
    SUM(CASE WHEN transaction_type = 'revenue' THEN amount ELSE -ABS(amount) END) as net_profit,
    COUNT(*) as total_transactions
FROM finances 
WHERE status = 'completed'
GROUP BY DATE_FORMAT(transaction_date, '%Y-%m')
ORDER BY month DESC;

-- View for user activity summary
CREATE VIEW `user_activity_summary` AS
SELECT 
    u.id,
    u.username,
    u.email,
    u.last_login,
    COUNT(al.id) as total_activities,
    MAX(al.created_at) as last_activity
FROM users u
LEFT JOIN activity_logs al ON u.id = al.user_id
GROUP BY u.id;

-- --------------------------------------------------------
-- Create stored procedures for common operations
-- --------------------------------------------------------

DELIMITER //

-- Procedure to update panel completion rate
CREATE PROCEDURE UpdatePanelStats(IN panel_id INT)
BEGIN
    UPDATE panels 
    SET 
        current_responses = (SELECT COUNT(*) FROM panel_responses WHERE panel_id = panel_id),
        completion_rate = (
            (SELECT COUNT(*) FROM panel_responses WHERE panel_id = panel_id) / target_responses * 100
        )
    WHERE id = panel_id;
END//

-- Procedure to log user activity
CREATE PROCEDURE LogActivity(
    IN user_id INT,
    IN action VARCHAR(100),
    IN resource_type VARCHAR(50),
    IN resource_id INT,
    IN description VARCHAR(500),
    IN ip_address VARCHAR(45)
)
BEGIN
    INSERT INTO activity_logs (user_id, action, resource_type, resource_id, description, ip_address)
    VALUES (user_id, action, resource_type, resource_id, description, ip_address);
END//

DELIMITER ;

-- --------------------------------------------------------
-- Create triggers for automatic updates
-- --------------------------------------------------------

DELIMITER //

-- Trigger to update panel stats when response is added
CREATE TRIGGER update_panel_stats_after_response
AFTER INSERT ON panel_responses
FOR EACH ROW
BEGIN
    CALL UpdatePanelStats(NEW.panel_id);
END//

-- Trigger to log user login
CREATE TRIGGER log_user_login
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF NEW.last_login != OLD.last_login THEN
        INSERT INTO activity_logs (user_id, action, description)
        VALUES (NEW.id, 'login', 'User logged in');
    END IF;
END//

DELIMITER ;

-- --------------------------------------------------------
-- Grant appropriate permissions (run as database administrator)
-- --------------------------------------------------------

-- Create application user with limited permissions
-- CREATE USER 'rr_app_user'@'localhost' IDENTIFIED BY 'secure_password_here';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON relevant_reflex_db.* TO 'rr_app_user'@'localhost';
-- GRANT EXECUTE ON relevant_reflex_db.* TO 'rr_app_user'@'localhost';
-- FLUSH PRIVILEGES;

-- --------------------------------------------------------
-- Performance optimization queries
-- --------------------------------------------------------

-- Analyze table performance (run periodically)
-- ANALYZE TABLE users, panels, panel_responses, supplies, finances, support_tickets;

-- Optimize tables (run during maintenance)
-- OPTIMIZE TABLE users, panels, panel_responses, supplies, finances, support_tickets;

-- --------------------------------------------------------
-- End of schema
-- --------------------------------------------------------