SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_orders` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_package_modifiers' AND COLUMN_NAME = 'product_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_package_modifiers` ADD COLUMN `product_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_package_modifiers' AND COLUMN_NAME = 'modifier_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_package_modifiers` ADD COLUMN `modifier_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'employee_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `employee_id` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'employee_name');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `employee_name` varchar(255) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'month');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `month` varchar(20) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'basic_salary');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `basic_salary` decimal(15,2) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'allowances');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `allowances` decimal(15,2) DEFAULT ''0.00''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'deductions');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `deductions` decimal(15,2) DEFAULT ''0.00''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'tax');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `tax` decimal(15,2) DEFAULT ''0.00''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'net_pay');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `net_pay` decimal(15,2) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'currency');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `currency` varchar(10) DEFAULT ''USD''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'status');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `status` enum(''Draft'',''Approved'',''Paid'') DEFAULT ''Draft''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'pay_date');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `pay_date` date DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_payroll' AND COLUMN_NAME = 'base_salary');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_payroll` ADD COLUMN `base_salary` decimal(15,2) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'business_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `business_id` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'project_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `project_id` bigint NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'title');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `title` varchar(255) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'project_name');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `project_name` varchar(255) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'type');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `type` tinytext NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'status');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `status` tinytext NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'priority');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `priority` tinytext NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'assigned_to');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `assigned_to` varchar(255) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'due_date');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `due_date` date DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'start_date');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `start_date` date DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'estimate_hours');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `estimate_hours` decimal(10,2) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'story_points');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `story_points` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_projects' AND COLUMN_NAME = 'live_url');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `dim_projects` ADD COLUMN `live_url` varchar(500) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_projects' AND COLUMN_NAME = 'staging_url');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `dim_projects` ADD COLUMN `staging_url` varchar(500) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'description');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `description` tinytext', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'tags_json');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `tags_json` json DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_purchases' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_purchases` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_purchases' AND COLUMN_NAME = 'supplier_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_purchases` ADD COLUMN `supplier_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_purchases' AND COLUMN_NAME = 'wallet_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_purchases` ADD COLUMN `wallet_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_purchases' AND COLUMN_NAME = 'is_capital_expenditure');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_purchases` ADD COLUMN `is_capital_expenditure` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_purchases' AND COLUMN_NAME = 'amount');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_purchases` ADD COLUMN `amount` decimal(15,2) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_purchases' AND COLUMN_NAME = 'purchase_date');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_purchases` ADD COLUMN `purchase_date` date DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_purchases' AND COLUMN_NAME = 'notes');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_purchases` ADD COLUMN `notes` text', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_purchases' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_purchases` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_purchases' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_purchases` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_purchases' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_purchases` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_purchases' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_purchases` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_purchases' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_purchases` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_recruitment_funnel' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_recruitment_funnel` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_recruitment_funnel' AND COLUMN_NAME = 'candidate_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_recruitment_funnel` ADD COLUMN `candidate_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_recruitment_funnel' AND COLUMN_NAME = 'stage');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_recruitment_funnel` ADD COLUMN `stage` tinytext NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_recruitment_funnel' AND COLUMN_NAME = 'score');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_recruitment_funnel` ADD COLUMN `score` decimal(5,2) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_recruitment_funnel' AND COLUMN_NAME = 'stage_date');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_recruitment_funnel` ADD COLUMN `stage_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_recruitment_funnel' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_recruitment_funnel` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_recruitment_funnel' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_recruitment_funnel` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_recruitment_funnel' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_recruitment_funnel` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_recruitment_funnel' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_recruitment_funnel` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_recruitment_funnel' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_recruitment_funnel` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'business_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `business_id` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'statement_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `statement_id` bigint NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'transaction_date');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `transaction_date` date NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'transaction_type');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `transaction_type` enum(''Credit'',''Debit'',''Transfer'',''Fee'',''Interest'',''Reversal'') DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'description');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `description` text', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'vendor');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `vendor` varchar(255) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'amount');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `amount` decimal(15,2) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'cash_in_or_out');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `cash_in_or_out` enum(''Cash In'',''Cash Out'') NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'personal_or_business');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `personal_or_business` enum(''Personal'',''Business'') DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'associated_card_number');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `associated_card_number` varchar(100) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'home_tax_percent');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `home_tax_percent` decimal(5,2) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'person_or_project_1');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `person_or_project_1` varchar(255) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'expense_labels');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `expense_labels` text', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'pdf_link');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `pdf_link` varchar(512) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statement_transactions' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statement_transactions` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statements' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statements` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statements' AND COLUMN_NAME = 'business_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statements` ADD COLUMN `business_id` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statements' AND COLUMN_NAME = 'entity_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statements` ADD COLUMN `entity_id` bigint NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statements' AND COLUMN_NAME = 'statement_period');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statements` ADD COLUMN `statement_period` varchar(100) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statements' AND COLUMN_NAME = 'beginning_balance');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statements` ADD COLUMN `beginning_balance` decimal(15,2) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statements' AND COLUMN_NAME = 'current_balance');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statements` ADD COLUMN `current_balance` decimal(15,2) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statements' AND COLUMN_NAME = 'current_interest_rate');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statements` ADD COLUMN `current_interest_rate` decimal(5,2) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statements' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statements` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statements' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statements` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statements' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statements` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statements' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statements` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_statements' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_statements` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'business_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `business_id` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'from_wallet_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `from_wallet_id` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'to_wallet_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `to_wallet_id` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'reference');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `reference` varchar(100) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'type');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `type` enum(''Income'',''Expense'',''Transfer'') DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'description');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `description` text NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'amount');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `amount` decimal(15,2) NOT NULL DEFAULT ''0.00''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'currency');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `currency` varchar(10) DEFAULT ''USD''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'wallet_name');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `wallet_name` varchar(255) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'category');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `category` varchar(100) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'date');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `date` date DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'status');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `status` tinytext', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'notes');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `notes` text', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'reference_entity');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `reference_entity` varchar(100) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'reference_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `reference_id` bigint DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'transaction_date');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `transaction_date` datetime(6) DEFAULT CURRENT_TIMESTAMP(6)', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'transaction_type');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `transaction_type` enum(''Income'',''Expense'',''Transfer'') DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'wallet_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `wallet_id` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'category_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `category_id` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_transactions' AND COLUMN_NAME = 'subcategory_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_transactions` ADD COLUMN `subcategory_id` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_vat' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_vat` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_vat' AND COLUMN_NAME = 'business_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_vat` ADD COLUMN `business_id` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_vat' AND COLUMN_NAME = 'period');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_vat` ADD COLUMN `period` varchar(50) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_vat' AND COLUMN_NAME = 'currency');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_vat` ADD COLUMN `currency` varchar(10) DEFAULT ''USD''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_vat' AND COLUMN_NAME = 'sales_vat');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_vat` ADD COLUMN `sales_vat` decimal(15,2) DEFAULT ''0.00''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_vat' AND COLUMN_NAME = 'purchase_vat');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_vat` ADD COLUMN `purchase_vat` decimal(15,2) DEFAULT ''0.00''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_vat' AND COLUMN_NAME = 'net_vat');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_vat` ADD COLUMN `net_vat` decimal(15,2) GENERATED ALWAYS AS ((`sales_vat` - `purchase_vat`)) STORED', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_vat' AND COLUMN_NAME = 'due_date');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_vat` ADD COLUMN `due_date` date DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_vat' AND COLUMN_NAME = 'status');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_vat` ADD COLUMN `status` enum(''Draft'',''Submitted'',''Paid'') DEFAULT ''Draft''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_vat' AND COLUMN_NAME = 'notes');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_vat` ADD COLUMN `notes` text', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_vat' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_vat` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_vat' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_vat` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_vat' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_vat` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_vat' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_vat` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_vat' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_vat` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'name');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `name` varchar(255) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'type');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `type` varchar(50) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'counterparty');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `counterparty` varchar(255) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'principal');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `principal` decimal(15,2) NOT NULL DEFAULT ''0.00''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'outstanding');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `outstanding` decimal(15,2) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'currency');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `currency` varchar(10) DEFAULT ''USD''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'interest_rate');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `interest_rate` decimal(6,3) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'start_date');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `start_date` date DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'due_date');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `due_date` date DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'status');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `status` enum(''Active'',''Settled'',''Defaulted'') DEFAULT ''Active''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'notes');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `notes` text', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fin_liabilities' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fin_liabilities` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_digital_assets` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'name');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_digital_assets` ADD COLUMN `name` varchar(255) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'category');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_digital_assets` ADD COLUMN `category` varchar(150) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'vendor');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_digital_assets` ADD COLUMN `vendor` varchar(255) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'cost');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_digital_assets` ADD COLUMN `cost` decimal(15,2) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'currency');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_digital_assets` ADD COLUMN `currency` varchar(10) DEFAULT ''USD''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'status');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_digital_assets` ADD COLUMN `status` varchar(50) DEFAULT ''Active''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'expiry_date');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_digital_assets` ADD COLUMN `expiry_date` date DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'assigned_to');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_digital_assets` ADD COLUMN `assigned_to` varchar(255) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_digital_assets` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_digital_assets` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_digital_assets` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_digital_assets` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_digital_assets` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_materials' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_materials` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_materials' AND COLUMN_NAME = 'name');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_materials` ADD COLUMN `name` varchar(255) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_materials' AND COLUMN_NAME = 'code');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_materials` ADD COLUMN `code` varchar(100) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_materials' AND COLUMN_NAME = 'category');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_materials` ADD COLUMN `category` varchar(150) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_materials' AND COLUMN_NAME = 'unit');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_materials` ADD COLUMN `unit` varchar(50) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_materials' AND COLUMN_NAME = 'stock_qty');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_materials` ADD COLUMN `stock_qty` decimal(15,2) DEFAULT ''0.00''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_materials' AND COLUMN_NAME = 'unit_cost');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_materials` ADD COLUMN `unit_cost` decimal(15,2) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_materials' AND COLUMN_NAME = 'currency');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_materials` ADD COLUMN `currency` varchar(10) DEFAULT ''USD''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_materials' AND COLUMN_NAME = 'reorder_level');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_materials` ADD COLUMN `reorder_level` decimal(15,2) DEFAULT ''0.00''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_materials' AND COLUMN_NAME = 'status');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_materials` ADD COLUMN `status` enum(''Active'',''Inactive'',''Discontinued'') DEFAULT ''Active''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_materials' AND COLUMN_NAME = 'notes');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_materials` ADD COLUMN `notes` text', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_materials' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_materials` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_materials' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_materials` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_materials' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_materials` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_materials' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_materials` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_materials' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_materials` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_products' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_products` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_products' AND COLUMN_NAME = 'name');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_products` ADD COLUMN `name` varchar(255) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_products' AND COLUMN_NAME = 'sku');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_products` ADD COLUMN `sku` varchar(100) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_products' AND COLUMN_NAME = 'category');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_products` ADD COLUMN `category` varchar(150) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_products' AND COLUMN_NAME = 'unit_price');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_products` ADD COLUMN `unit_price` decimal(15,2) NOT NULL DEFAULT ''0.00''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_products' AND COLUMN_NAME = 'cost_price');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_products` ADD COLUMN `cost_price` decimal(15,2) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_products' AND COLUMN_NAME = 'currency');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_products` ADD COLUMN `currency` varchar(10) DEFAULT ''USD''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_products' AND COLUMN_NAME = 'stock_qty');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_products` ADD COLUMN `stock_qty` int DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_products' AND COLUMN_NAME = 'reorder_level');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_products` ADD COLUMN `reorder_level` int DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_products' AND COLUMN_NAME = 'unit');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_products` ADD COLUMN `unit` varchar(50) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_products' AND COLUMN_NAME = 'status');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_products` ADD COLUMN `status` enum(''Active'',''Inactive'',''Discontinued'') DEFAULT ''Active''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_products' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_products` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_products' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_products` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_products' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_products` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_products' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_products` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_products' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `inv_products` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'permissions' AND COLUMN_NAME = 'permission_key');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `permissions` ADD COLUMN `permission_key` varchar(100) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'permissions' AND COLUMN_NAME = 'description');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `permissions` ADD COLUMN `description` tinytext', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'platform_audit_logs' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `platform_audit_logs` ADD COLUMN `tenant_id` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'platform_audit_logs' AND COLUMN_NAME = 'event_type');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `platform_audit_logs` ADD COLUMN `event_type` varchar(100) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'platform_audit_logs' AND COLUMN_NAME = 'actor_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `platform_audit_logs` ADD COLUMN `actor_id` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'platform_audit_logs' AND COLUMN_NAME = 'details');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `platform_audit_logs` ADD COLUMN `details` json DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'platform_audit_logs' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `platform_audit_logs` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'platform_feature_flags' AND COLUMN_NAME = 'feature_key');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `platform_feature_flags` ADD COLUMN `feature_key` varchar(100) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'platform_feature_flags' AND COLUMN_NAME = 'is_enabled');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `platform_feature_flags` ADD COLUMN `is_enabled` tinyint(1) DEFAULT ''1''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_resources' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_resources` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_resources' AND COLUMN_NAME = 'name');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_resources` ADD COLUMN `name` varchar(255) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_resources' AND COLUMN_NAME = 'type');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_resources` ADD COLUMN `type` varchar(50) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_resources' AND COLUMN_NAME = 'project_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_resources` ADD COLUMN `project_id` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_resources' AND COLUMN_NAME = 'role');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_resources` ADD COLUMN `role` varchar(150) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_resources' AND COLUMN_NAME = 'allocation_pct');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_resources` ADD COLUMN `allocation_pct` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_resources' AND COLUMN_NAME = 'cost_rate');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_resources` ADD COLUMN `cost_rate` decimal(15,2) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_resources' AND COLUMN_NAME = 'currency');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_resources` ADD COLUMN `currency` varchar(10) DEFAULT ''USD''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_resources' AND COLUMN_NAME = 'availability');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_resources` ADD COLUMN `availability` enum(''Available'',''Allocated'',''Unavailable'') DEFAULT ''Available''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_resources' AND COLUMN_NAME = 'notes');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_resources` ADD COLUMN `notes` text', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_resources' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_resources` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_resources' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_resources` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_resources' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_resources` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_resources' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_resources` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_resources' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_resources` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_sprints' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_sprints` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_sprints' AND COLUMN_NAME = 'name');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_sprints` ADD COLUMN `name` varchar(255) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_sprints' AND COLUMN_NAME = 'project_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_sprints` ADD COLUMN `project_id` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_sprints' AND COLUMN_NAME = 'goal');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_sprints` ADD COLUMN `goal` text', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_sprints' AND COLUMN_NAME = 'start_date');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_sprints` ADD COLUMN `start_date` date DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_sprints' AND COLUMN_NAME = 'end_date');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_sprints` ADD COLUMN `end_date` date DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_sprints' AND COLUMN_NAME = 'status');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_sprints` ADD COLUMN `status` enum(''Planning'',''Active'',''Completed'',''Cancelled'') DEFAULT ''Planning''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_sprints' AND COLUMN_NAME = 'capacity');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_sprints` ADD COLUMN `capacity` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_sprints' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_sprints` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_sprints' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_sprints` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_sprints' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_sprints` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_sprints' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_sprints` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'proj_sprints' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `proj_sprints` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'role_permissions' AND COLUMN_NAME = 'role_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `role_permissions` ADD COLUMN `role_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'role_permissions' AND COLUMN_NAME = 'permission_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `role_permissions` ADD COLUMN `permission_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'roles' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `roles` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'roles' AND COLUMN_NAME = 'role_name');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `roles` ADD COLUMN `role_name` varchar(100) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'roles' AND COLUMN_NAME = 'description');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `roles` ADD COLUMN `description` text', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'roles' AND COLUMN_NAME = 'is_system');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `roles` ADD COLUMN `is_system` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'roles' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `roles` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'roles' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `roles` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'roles' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `roles` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'roles' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `roles` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'roles' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `roles` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenant_usage_metrics' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenant_usage_metrics` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenant_usage_metrics' AND COLUMN_NAME = 'metric_type');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenant_usage_metrics` ADD COLUMN `metric_type` varchar(50) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenant_usage_metrics' AND COLUMN_NAME = 'usage_value');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenant_usage_metrics` ADD COLUMN `usage_value` decimal(15,2) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenant_usage_metrics' AND COLUMN_NAME = 'recorded_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenant_usage_metrics` ADD COLUMN `recorded_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'business_name');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenants` ADD COLUMN `business_name` varchar(255) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'subscription_plan');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenants` ADD COLUMN `subscription_plan` varchar(255) DEFAULT ''Basic''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'status');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenants` ADD COLUMN `status` varchar(255) DEFAULT ''Active''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'email');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenants` ADD COLUMN `email` varchar(255) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'phone');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenants` ADD COLUMN `phone` varchar(50) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'country');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenants` ADD COLUMN `country` varchar(100) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'industry');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenants` ADD COLUMN `industry` varchar(100) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'trial_ends');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenants` ADD COLUMN `trial_ends` date DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'admin_email');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenants` ADD COLUMN `admin_email` varchar(255) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenants` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenants` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenants` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenants` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `tenants` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_api_keys' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_api_keys` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_api_keys' AND COLUMN_NAME = 'user_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_api_keys` ADD COLUMN `user_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_api_keys' AND COLUMN_NAME = 'key_hash');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_api_keys` ADD COLUMN `key_hash` varchar(512) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_api_keys' AND COLUMN_NAME = 'scopes_json');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_api_keys` ADD COLUMN `scopes_json` tinytext', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_api_keys' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_api_keys` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_api_keys' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_api_keys` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_business_access' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_business_access` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_business_access' AND COLUMN_NAME = 'user_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_business_access` ADD COLUMN `user_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_business_access' AND COLUMN_NAME = 'business_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_business_access` ADD COLUMN `business_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_business_access' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_business_access` ADD COLUMN `is_deleted` tinyint(1) NOT NULL DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_business_access' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_business_access` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_business_access' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_business_access` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_business_access' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_business_access` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_module_access' AND COLUMN_NAME = 'user_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_module_access` ADD COLUMN `user_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_module_access' AND COLUMN_NAME = 'module');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_module_access` ADD COLUMN `module` varchar(50) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_module_access' AND COLUMN_NAME = 'access_level');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_module_access` ADD COLUMN `access_level` enum(''READ'',''WRITE'',''ADMIN'') DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_module_access' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_module_access` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_module_access' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_module_access` ADD COLUMN `created_at` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6)', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_module_access' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_module_access` ADD COLUMN `updated_at` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_module_access' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_module_access` ADD COLUMN `is_deleted` tinyint NOT NULL DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_roles' AND COLUMN_NAME = 'user_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_roles` ADD COLUMN `user_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_roles' AND COLUMN_NAME = 'role_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_roles` ADD COLUMN `role_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_roles' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `user_roles` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'tenant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `tenant_id` int NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'first_name');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `first_name` varchar(100) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'last_name');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `last_name` varchar(100) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'email');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `email` varchar(255) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'password_hash');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `password_hash` varchar(512) NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'department');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `department` varchar(100) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'role_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `role_id` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'status');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `status` enum(''Active'',''Inactive'',''Pending'') DEFAULT ''Pending''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'is_super_admin');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `is_super_admin` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'last_login_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `last_login_at` timestamp NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'created_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `created_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'updated_by');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `updated_by` int DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'is_deleted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `is_deleted` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'is_enabled');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `is_enabled` bit(1) DEFAULT b''1''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'otp_code');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `otp_code` varchar(10) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'otp_expires_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `otp_expires_at` datetime(6) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'password_salt');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `password_salt` varchar(255) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'user_type');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `users` ADD COLUMN `user_type` enum(''SUPER_ADMIN'',''TENANT_ADMIN'',''TENANT_MANAGER'') DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- ============================================================================================
-- SECTION 2 — VIEWS (33)
-- ============================================================================================

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_attendance` AS
select `a`.`id` AS `id`,`a`.`id` AS `attendanceId`,`a`.`tenant_id` AS `tenant_id`,`a`.`employee_id` AS `employeeId`,`a`.`employee_name` AS `employeeName`,`a`.`date` AS `date`,`a`.`status` AS `status`,`a`.`hours_worked` AS `hours`,`a`.`check_in` AS `checkIn`,`a`.`check_out` AS `checkOut`,`a`.`notes` AS `notes`,`a`.`created_at` AS `createdAt`,`a`.`updated_at` AS `updatedAt` from `fact_attendance` `a` where (`a`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_bank_reconciliation` AS
select `s`.`tenant_id` AS `tenant_id`,`s`.`id` AS `statement_id`,`e`.`entity_name` AS `bank_name`,`e`.`account_number` AS `account_number`,`s`.`beginning_balance` AS `beginning_balance`,`s`.`current_balance` AS `current_balance`,coalesce(sum((case when (`bt`.`cash_in_or_out` = 'Cash In') then `bt`.`amount` else 0 end)),0) AS `total_cash_in`,coalesce(sum((case when (`bt`.`cash_in_or_out` = 'Cash Out') then `bt`.`amount` else 0 end)),0) AS `total_cash_out`,(`s`.`beginning_balance` + coalesce(sum((case when (`bt`.`cash_in_or_out` = 'Cash In') then `bt`.`amount` when (`bt`.`cash_in_or_out` = 'Cash Out') then -(`bt`.`amount`) else 0 end)),0)) AS `reconciled_balance`,count(`bt`.`id`) AS `transaction_count` from ((`fact_statements` `s` join `dim_financial_entities` `e` on(((`e`.`id` = `s`.`entity_id`) and (`e`.`entity_type` = 'Bank') and (`e`.`is_deleted` = 0)))) left join `fact_statement_transactions` `bt` on(((`bt`.`statement_id` = `s`.`id`) and (`bt`.`is_deleted` = 0)))) where (`s`.`is_deleted` = 0) group by `s`.`tenant_id`,`s`.`id`,`e`.`entity_name`,`e`.`account_number`,`s`.`beginning_balance`,`s`.`current_balance`;

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_cash_flow` AS
select `t`.`tenant_id` AS `tenant_id`,`t`.`id` AS `transaction_id`,`t`.`from_wallet_id` AS `from_wallet_id`,`fw`.`wallet_name` AS `from_wallet_name`,`t`.`to_wallet_id` AS `to_wallet_id`,`tw`.`wallet_name` AS `to_wallet_name`,`t`.`amount` AS `amount`,`t`.`currency` AS `currency`,`t`.`transaction_date` AS `transaction_date`,`t`.`status` AS `status`,`t`.`reference` AS `reference`,`t`.`type` AS `type` from ((`fact_transactions` `t` left join `dim_wallets` `fw` on((`fw`.`id` = `t`.`from_wallet_id`))) left join `dim_wallets` `tw` on((`tw`.`id` = `t`.`to_wallet_id`))) where (`t`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_commission_report` AS
select `c`.`tenant_id` AS `tenant_id`,`c`.`id` AS `commission_id`,`c`.`agent_id` AS `agent_id`,`c`.`agent_name` AS `agent_name`,`c`.`period` AS `period`,`c`.`deal_ref` AS `deal_ref`,`c`.`gross_amount` AS `gross_amount`,`c`.`rate` AS `rate`,`c`.`earned` AS `earned`,`c`.`bonus` AS `bonus`,`c`.`total` AS `total`,`c`.`currency` AS `currency`,`c`.`status` AS `status`,`c`.`paid_date` AS `paid_date` from `fact_commissions` `c` where (`c`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_compliance_tracker` AS
select `a`.`id` AS `asset_id`,`a`.`tenant_id` AS `tenant_id`,`a`.`asset_name` AS `asset_name`,`a`.`asset_type` AS `asset_type`,`a`.`issue_date` AS `issue_date`,`a`.`expiry_date` AS `expiry_date`,`l`.`log_type` AS `log_type`,`l`.`log_date` AS `log_date`,`l`.`details` AS `details` from (`dim_legal_assets` `a` left join `fact_compliance_logs` `l` on(((`a`.`id` = `l`.`legal_asset_id`) and (`l`.`is_deleted` = 0)))) where (`a`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_digital_assets` AS
select `d`.`id` AS `id`,`d`.`id` AS `assetId`,`d`.`tenant_id` AS `tenant_id`,`d`.`name` AS `name`,`d`.`category` AS `category`,`d`.`vendor` AS `vendor`,`d`.`cost` AS `cost`,`d`.`currency` AS `currency`,`d`.`status` AS `status`,`d`.`expiry_date` AS `expiryDate`,`d`.`assigned_to` AS `assignedTo`,`d`.`created_at` AS `createdAt`,`d`.`updated_at` AS `updatedAt` from `inv_digital_assets` `d` where (`d`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_employee_full_profile` AS
select `e`.`id` AS `id`,`e`.`tenant_id` AS `tenant_id`,`e`.`status` AS `status`,`d`.`department_name` AS `department_name`,`des`.`title` AS `designation` from ((`dim_employees` `e` left join `dim_departments` `d` on((`e`.`department_id` = `d`.`id`))) left join `dim_designations` `des` on((`e`.`designation_id` = `des`.`id`)));

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_employee_payroll_wallet` AS
select `p`.`id` AS `payroll_id`,`p`.`tenant_id` AS `tenant_id`,`p`.`employee_id` AS `employee_id`,`p`.`net_pay` AS `net_pay`,`p`.`pay_date` AS `pay_date`,`p`.`status` AS `payroll_status`,`w`.`id` AS `wallet_id`,`w`.`balance` AS `wallet_balance` from ((`fact_payroll` `p` join `dim_employees` `e` on((`p`.`employee_id` = `e`.`id`))) left join `dim_wallets` `w` on(((`w`.`employee_id` = `e`.`id`) and (`w`.`is_deleted` = 0)))) where (`p`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_employee_self_profile` AS
select `e`.`id` AS `id`,`e`.`tenant_id` AS `tenant_id`,`e`.`user_id` AS `user_id`,`e`.`first_name` AS `first_name`,`e`.`last_name` AS `last_name`,`e`.`email` AS `email`,`e`.`phone` AS `phone`,`e`.`status` AS `status`,`d`.`department_name` AS `department_name`,`des`.`title` AS `designation`,`c`.`start_date` AS `start_date`,`c`.`end_date` AS `end_date`,`c`.`allowances_json` AS `allowances_json` from (((`dim_employees` `e` left join `dim_departments` `d` on((`e`.`department_id` = `d`.`id`))) left join `dim_designations` `des` on((`e`.`designation_id` = `des`.`id`))) left join `dim_employee_contracts` `c` on(((`e`.`id` = `c`.`employee_id`) and (`c`.`is_deleted` = 0)))) where (`e`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_employee_task_board` AS
select `t`.`id` AS `id`,`t`.`tenant_id` AS `tenant_id`,`t`.`assigned_to` AS `assigned_to`,`t`.`project_id` AS `project_id`,`p`.`name` AS `project_name`,`t`.`title` AS `title`,`t`.`status` AS `status`,`t`.`priority` AS `priority`,`t`.`due_date` AS `due_date` from (`fact_project_tasks` `t` join `dim_projects` `p` on((`t`.`project_id` = `p`.`id`))) where ((`t`.`is_deleted` = 0) and (`p`.`is_deleted` = 0));

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_hr_events_by_employee` AS
select `h`.`id` AS `id`,`h`.`tenant_id` AS `tenant_id`,`h`.`employee_id` AS `employee_id`,`e`.`first_name` AS `first_name`,`e`.`last_name` AS `last_name`,`h`.`event_type` AS `event_type`,`h`.`amount` AS `amount`,`h`.`event_date` AS `event_date`,`h`.`description` AS `description` from (`fact_hr_events` `h` join `dim_employees` `e` on((`h`.`employee_id` = `e`.`id`))) where (`h`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_invoice_summary` AS
select `i`.`id` AS `id`,`i`.`tenant_id` AS `tenant_id`,`i`.`invoice_no` AS `invoice_no`,`i`.`client_name` AS `client_name`,`i`.`status` AS `status`,`i`.`amount` AS `amount`,`i`.`tax_amount` AS `tax_amount`,`i`.`issue_date` AS `issue_date`,`i`.`due_date` AS `due_date`,`w`.`wallet_name` AS `wallet_name`,`o`.`title` AS `deal_title` from ((`fact_invoices` `i` join `dim_wallets` `w` on((`i`.`wallet_id` = `w`.`id`))) left join `fact_opportunities` `o` on((`i`.`opportunity_id` = `o`.`id`))) where (`i`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_liabilities` AS
select `fin_liabilities`.`id` AS `id`,`fin_liabilities`.`id` AS `liabilityId`,`fin_liabilities`.`tenant_id` AS `tenant_id`,`fin_liabilities`.`name` AS `name`,`fin_liabilities`.`type` AS `type`,`fin_liabilities`.`counterparty` AS `counterparty`,`fin_liabilities`.`principal` AS `principal`,coalesce(`fin_liabilities`.`outstanding`,`fin_liabilities`.`principal`) AS `outstanding`,`fin_liabilities`.`currency` AS `currency`,`fin_liabilities`.`interest_rate` AS `interestRate`,`fin_liabilities`.`start_date` AS `startDate`,`fin_liabilities`.`due_date` AS `dueDate`,`fin_liabilities`.`status` AS `status`,`fin_liabilities`.`notes` AS `notes`,`fin_liabilities`.`created_at` AS `createdAt`,`fin_liabilities`.`updated_at` AS `updatedAt` from `fin_liabilities` where (`fin_liabilities`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_loans` AS
select `l`.`id` AS `id`,`l`.`id` AS `loanId`,`l`.`tenant_id` AS `tenant_id`,`l`.`employee_id` AS `employeeId`,`l`.`employee_name` AS `employeeName`,`l`.`loan_amount` AS `loanAmount`,`l`.`currency` AS `currency`,`l`.`no_of_installments` AS `installments`,`l`.`monthly_amount` AS `monthlyAmount`,`l`.`start_date` AS `startDate`,`l`.`status` AS `status`,`l`.`purpose` AS `purpose`,`l`.`notes` AS `notes`,`l`.`created_at` AS `createdAt`,`l`.`updated_at` AS `updatedAt` from `fact_loans` `l` where (`l`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_materials` AS
select `inv_materials`.`id` AS `id`,`inv_materials`.`id` AS `materialId`,`inv_materials`.`tenant_id` AS `tenant_id`,`inv_materials`.`name` AS `name`,`inv_materials`.`code` AS `code`,`inv_materials`.`category` AS `category`,`inv_materials`.`unit` AS `unit`,`inv_materials`.`stock_qty` AS `stockQty`,`inv_materials`.`unit_cost` AS `unitCost`,`inv_materials`.`currency` AS `currency`,`inv_materials`.`reorder_level` AS `reorderLevel`,`inv_materials`.`status` AS `status`,`inv_materials`.`notes` AS `notes`,`inv_materials`.`created_at` AS `createdAt`,`inv_materials`.`updated_at` AS `updatedAt` from `inv_materials` where (`inv_materials`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_opportunity_pipeline` AS
select `o`.`id` AS `id`,`o`.`tenant_id` AS `tenant_id`,`c`.`company_name` AS `company_name`,`o`.`status` AS `status`,`o`.`deal_value` AS `deal_value`,`o`.`expected_close_date` AS `expected_close_date` from (`fact_opportunities` `o` join `dim_companies` `c` on((`o`.`company_id` = `c`.`id`))) where (`o`.`is_deleted` = false);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_orders` AS
select `o`.`id` AS `id`,`o`.`id` AS `orderId`,`o`.`tenant_id` AS `tenant_id`,`o`.`order_no` AS `orderNo`,`o`.`client_name` AS `clientName`,`o`.`status` AS `status`,`o`.`total_amount` AS `totalAmount`,`o`.`total_amount` AS `total`,`o`.`currency` AS `currency`,`o`.`no_of_items` AS `items`,`o`.`order_date` AS `orderDate`,`o`.`delivery_date` AS `deliveryDate`,`o`.`notes` AS `notes`,`o`.`created_at` AS `createdAt`,`o`.`updated_at` AS `updatedAt` from `fact_orders` `o` where (`o`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_payroll_summary` AS
select `p`.`id` AS `id`,`p`.`tenant_id` AS `tenant_id`,`e`.`id` AS `employee_id`,`p`.`net_pay` AS `net_pay`,`p`.`pay_date` AS `pay_date` from (`fact_payroll` `p` join `dim_employees` `e` on((`p`.`employee_id` = `e`.`id`)));

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_products` AS
select `p`.`id` AS `id`,`p`.`id` AS `productId`,`p`.`tenant_id` AS `tenant_id`,`p`.`name` AS `name`,`p`.`sku` AS `sku`,`p`.`category` AS `category`,`p`.`unit_price` AS `unitPrice`,`p`.`cost_price` AS `costPrice`,`p`.`currency` AS `currency`,`p`.`stock_qty` AS `stockQty`,`p`.`reorder_level` AS `reorderLevel`,`p`.`unit` AS `unit`,`p`.`status` AS `status`,`p`.`created_at` AS `createdAt`,`p`.`updated_at` AS `updatedAt` from `inv_products` `p` where (`p`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_project_board` AS
select `t`.`id` AS `id`,`t`.`tenant_id` AS `tenant_id`,`t`.`project_id` AS `project_id`,`p`.`name` AS `project_name`,`t`.`title` AS `title`,`t`.`status` AS `status`,`t`.`priority` AS `priority`,`t`.`due_date` AS `due_date`,`t`.`assigned_to` AS `assigned_to` from (`fact_project_tasks` `t` join `dim_projects` `p` on((`t`.`project_id` = `p`.`id`))) where ((`t`.`is_deleted` = 0) and (`p`.`is_deleted` = 0));

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_purchases` AS
select `p`.`id` AS `id`,`p`.`id` AS `purchaseId`,`p`.`tenant_id` AS `tenant_id`,`p`.`supplier_id` AS `supplierId`,`s`.`vendor_name` AS `supplierName`,`s`.`vendor_name` AS `vendorName`,`p`.`wallet_id` AS `walletId`,`w`.`wallet_name` AS `walletName`,`p`.`amount` AS `amount`,`p`.`is_capital_expenditure` AS `isCapitalExpenditure`,`p`.`purchase_date` AS `purchaseDate`,`p`.`notes` AS `notes`,`p`.`created_at` AS `createdAt`,`p`.`updated_at` AS `updatedAt` from ((`fact_purchases` `p` left join `dim_suppliers` `s` on((`s`.`id` = `p`.`supplier_id`))) left join `dim_wallets` `w` on((`w`.`id` = `p`.`wallet_id`))) where (`p`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_recruitment_pipeline` AS
select `f`.`id` AS `id`,`f`.`tenant_id` AS `tenant_id`,`c`.`name` AS `candidate_name`,`f`.`stage` AS `stage`,`f`.`score` AS `score`,`f`.`stage_date` AS `stage_date` from (`fact_recruitment_funnel` `f` join `dim_candidates` `c` on((`f`.`candidate_id` = `c`.`id`))) where (`f`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_resources` AS
select `r`.`id` AS `id`,`r`.`id` AS `resourceId`,`r`.`tenant_id` AS `tenant_id`,`r`.`name` AS `name`,`r`.`type` AS `type`,`r`.`project_id` AS `projectId`,`p`.`name` AS `projectName`,`r`.`role` AS `role`,`r`.`allocation_pct` AS `allocationPct`,`r`.`cost_rate` AS `costRate`,`r`.`currency` AS `currency`,`r`.`availability` AS `availability`,`r`.`notes` AS `notes`,`r`.`created_at` AS `createdAt`,`r`.`updated_at` AS `updatedAt` from (`proj_resources` `r` left join `dim_projects` `p` on((`p`.`id` = `r`.`project_id`))) where (`r`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_sprints` AS
select `s`.`id` AS `id`,`s`.`id` AS `sprintId`,`s`.`tenant_id` AS `tenant_id`,`s`.`name` AS `name`,`s`.`project_id` AS `projectId`,`p`.`name` AS `projectName`,`s`.`goal` AS `goal`,`s`.`start_date` AS `startDate`,`s`.`end_date` AS `endDate`,`s`.`status` AS `status`,`s`.`capacity` AS `capacity`,`s`.`created_at` AS `createdAt`,`s`.`updated_at` AS `updatedAt` from (`proj_sprints` `s` left join `dim_projects` `p` on((`p`.`id` = `s`.`project_id`))) where (`s`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_statement_reconciliation_detail` AS
select `s`.`id` AS `statement_id`,`t`.`transaction_date` AS `transaction_date`,`t`.`amount` AS `amount`,`t`.`description` AS `description` from (`fact_statements` `s` join `fact_statement_transactions` `t` on((`s`.`id` = `t`.`statement_id`)));

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_suppliers` AS
select `s`.`id` AS `id`,`s`.`id` AS `supplierId`,`s`.`tenant_id` AS `tenant_id`,`s`.`vendor_name` AS `vendorName`,`s`.`vendor_name` AS `name`,`s`.`category` AS `category`,`s`.`tax_id` AS `taxId`,`s`.`created_at` AS `createdAt`,`s`.`updated_at` AS `updatedAt` from `dim_suppliers` `s` where (`s`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_tenant_employee_directory` AS
select `e`.`id` AS `id`,`e`.`tenant_id` AS `tenant_id`,`e`.`first_name` AS `first_name`,`e`.`last_name` AS `last_name`,`e`.`email` AS `email`,`e`.`phone` AS `phone`,`d`.`department_name` AS `department_name`,`des`.`title` AS `designation`,`e`.`status` AS `status` from ((`dim_employees` `e` left join `dim_departments` `d` on((`e`.`department_id` = `d`.`id`))) left join `dim_designations` `des` on((`e`.`designation_id` = `des`.`id`))) where (`e`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_tenant_summary` AS
select `t`.`id` AS `tenant_id`,`t`.`business_name` AS `business_name`,`t`.`subscription_plan` AS `subscription_plan`,`t`.`status` AS `status`,`t`.`created_at` AS `created_at`,count(`u`.`id`) AS `user_count` from (`tenants` `t` left join `users` `u` on(((`t`.`id` = `u`.`tenant_id`) and (`u`.`is_deleted` = 0)))) where (`t`.`is_deleted` = 0) group by `t`.`id`,`t`.`business_name`,`t`.`subscription_plan`,`t`.`status`,`t`.`created_at`;

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_tenant_user_roster` AS
select `u`.`id` AS `id`,`u`.`tenant_id` AS `tenant_id`,`u`.`email` AS `email`,`r`.`role_name` AS `role_name` from ((`users` `u` join `user_roles` `ur` on((`u`.`id` = `ur`.`user_id`))) join `roles` `r` on((`ur`.`role_id` = `r`.`id`)));

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_user_full_profile` AS
select `u`.`id` AS `id`,`u`.`tenant_id` AS `tenant_id`,`u`.`email` AS `email`,`u`.`is_super_admin` AS `is_super_admin`,`e`.`status` AS `emp_status` from (`users` `u` left join `dim_employees` `e` on((`u`.`id` = `e`.`user_id`)));

CREATE OR REPLACE SQL SECURITY INVOKER VIEW `vw_user_module_access_detail` AS
select `uma`.`id` AS `id`,`uma`.`tenant_id` AS `tenant_id`,`uma`.`user_id` AS `user_id`,`u`.`email` AS `email`,`uma`.`module` AS `module_name`,`uma`.`access_level` AS `access_level` from (`user_module_access` `uma` join `users` `u` on((`uma`.`user_id` = `u`.`id`))) where (`u`.`is_deleted` = 0);

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_user_permissions` AS
select `u`.`id` AS `user_id`,`u`.`tenant_id` AS `tenant_id`,`p`.`permission_key` AS `permission_key`,`p`.`description` AS `description` from (((`users` `u` join `roles` `r` on((`u`.`role_id` = `r`.`id`))) join `role_permissions` `rp` on((`r`.`id` = `rp`.`role_id`))) join `permissions` `p` on((`rp`.`permission_id` = `p`.`id`))) where ((`u`.`is_deleted` = 0) and (`r`.`is_deleted` = 0));

CREATE OR REPLACE SQL SECURITY DEFINER VIEW `vw_wallet_ledger_summary` AS
select `w`.`tenant_id` AS `tenant_id`,`w`.`id` AS `wallet_id`,`w`.`wallet_name` AS `wallet_name`,`w`.`balance` AS `balance`,`w`.`currency` AS `currency`,`es`.`id` AS `subcategory_id`,`es`.`subcategory_name` AS `subcategory_name`,coalesce(sum((case when (`le`.`entry_type` = 'Debit') then `le`.`amount` else 0 end)),0) AS `total_debits`,coalesce(sum((case when (`le`.`entry_type` = 'Credit') then `le`.`amount` else 0 end)),0) AS `total_credits` from ((`dim_wallets` `w` left join `fact_ledger_entries` `le` on(((`le`.`wallet_id` = `w`.`id`) and (`le`.`is_deleted` = 0)))) left join `dim_expense_subcategories` `es` on((`es`.`id` = `le`.`subcategory_id`))) where (`w`.`is_deleted` = 0) group by `w`.`tenant_id`,`w`.`id`,`w`.`wallet_name`,`w`.`balance`,`w`.`currency`,`es`.`id`,`es`.`subcategory_name`;

-- ============================================================================================
-- SECTION 3 — STORED PROCEDURES (61)
-- ============================================================================================

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_add_departments_and_manager`$$
CREATE PROCEDURE `sp_add_departments_and_manager`(
    IN p_tenant_id INT,
    IN p_manager_first VARCHAR(100),
    IN p_manager_last VARCHAR(100),
    IN p_manager_email VARCHAR(255),
    IN p_password_hash VARCHAR(512),
    IN p_primary_department VARCHAR(100),
    IN p_additional_departments_json JSON, -- E.g., '["Marketing", "PR"]'
    IN p_creator_id INT
)
BEGIN
    DECLARE v_role_id INT;
    DECLARE v_dept_count INT;
    DECLARE v_dept_name VARCHAR(100);
    DECLARE i INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK;
    END;

    START TRANSACTION;

    -- 1. Check for 'Tenant Manager' role, create if missing, and enforce RLS
    SELECT id INTO v_role_id FROM roles WHERE tenant_id = p_tenant_id AND role_name = 'Tenant Manager' LIMIT 1;
    
    IF v_role_id IS NULL THEN
        INSERT INTO roles (tenant_id, role_name, created_by) VALUES (p_tenant_id, 'Tenant Manager', p_creator_id);
        SET v_role_id = LAST_INSERT_ID();
        
        -- Restrict this role so they only see data belonging to their department
        INSERT INTO data_scopes (tenant_id, role_id, scope_type) VALUES (p_tenant_id, v_role_id, 'Department');
    END IF;

    -- 2. Create the Manager User Profile
    INSERT INTO users (
        tenant_id, first_name, last_name, email, password_hash, 
        department, role_id, status, is_super_admin, user_type, is_enabled, created_by
    ) VALUES (
        p_tenant_id, p_manager_first, p_manager_last, p_manager_email, p_password_hash, 
        p_primary_department, v_role_id, 'Active', 0, 'TENANT_MANAGER', b'1', p_creator_id
    );

    -- 3. Iteratively Create Multiple Departments
    IF p_additional_departments_json IS NOT NULL THEN
        SET v_dept_count = JSON_LENGTH(p_additional_departments_json);
        WHILE i < v_dept_count DO
            SET v_dept_name = JSON_UNQUOTE(JSON_EXTRACT(p_additional_departments_json, CONCAT('$[', i, ']')));
            
            -- Insert department blindly (assuming unique combinations are handled by app logic or DB index)
            INSERT INTO dim_departments (tenant_id, department_name, created_by)
            VALUES (p_tenant_id, v_dept_name, p_creator_id);
            
            SET i = i + 1;
        END WHILE;
    END IF;

    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_assign_role_to_user`$$
CREATE PROCEDURE `sp_assign_role_to_user`(
    IN p_tenant_id INT,
    IN p_user_id INT,
    IN p_role_id INT,
    IN p_scope_type VARCHAR(50),
    IN p_changed_by INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    UPDATE users SET role_id = p_role_id, updated_by = p_changed_by, updated_at = CURRENT_TIMESTAMP 
    WHERE id = p_user_id AND tenant_id = p_tenant_id;
    IF NOT EXISTS (SELECT 1 FROM data_scopes WHERE tenant_id = p_tenant_id AND role_id = p_role_id) THEN
        INSERT INTO data_scopes (tenant_id, role_id, scope_type) VALUES (p_tenant_id, p_role_id, p_scope_type);
    END IF;
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_bulk_create_tenant_users`$$
CREATE PROCEDURE `sp_bulk_create_tenant_users`(
    IN p_tenant_id INT,
    IN p_users_json JSON,
    /* Example payload element:
    {
      "firstName": "John", "lastName": "Doe", "email": "j@test.com",
      "passwordHash": "$2a$...", "passwordSalt": "uuid", "userType": "TENANT_MANAGER",
      "roleId": 5,
      "moduleAccess": [{"module": "CRM", "accessLevel": "WRITE"}]
    }
    */
    IN p_created_by INT,
    OUT p_created_count INT
)
BEGIN
    DECLARE v_user_count INT DEFAULT 0;
    DECLARE v_module_count INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE j INT DEFAULT 0;

    DECLARE v_first VARCHAR(100);
    DECLARE v_last VARCHAR(100);
    DECLARE v_email VARCHAR(255);
    DECLARE v_hash VARCHAR(512);
    DECLARE v_salt VARCHAR(255);
    DECLARE v_user_type VARCHAR(20);
    DECLARE v_role_id INT;
    DECLARE v_new_user_id INT;
    DECLARE v_module_json JSON;
    DECLARE v_module_name VARCHAR(50);
    DECLARE v_access_level VARCHAR(20);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SET v_user_count = JSON_LENGTH(p_users_json);

    WHILE i < v_user_count DO
        SET v_first     = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].firstName')));
        SET v_last      = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].lastName')));
        SET v_email     = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].email')));
        SET v_hash       = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].passwordHash')));
        SET v_salt       = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].passwordSalt')));
        SET v_user_type  = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].userType')));
        SET v_role_id    = JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].roleId'));
        SET v_module_json = JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].moduleAccess'));

        INSERT INTO users (
            tenant_id, first_name, last_name, email, password_hash, password_salt,
            role_id, status, is_super_admin, user_type, is_enabled, created_by
        ) VALUES (
            p_tenant_id, v_first, v_last, v_email, v_hash, v_salt,
            v_role_id, 'Active', 0, v_user_type, b'1', p_created_by
        );

        SET v_new_user_id = LAST_INSERT_ID();

        IF v_module_json IS NOT NULL THEN
            SET v_module_count = JSON_LENGTH(v_module_json);
            SET j = 0;
            WHILE j < v_module_count DO
                SET v_module_name  = JSON_UNQUOTE(JSON_EXTRACT(v_module_json, CONCAT('$[', j, '].module')));
                SET v_access_level = JSON_UNQUOTE(JSON_EXTRACT(v_module_json, CONCAT('$[', j, '].accessLevel')));
                INSERT INTO user_module_access (user_id, module, access_level, tenant_id, is_deleted)
                VALUES (v_new_user_id, v_module_name, IFNULL(v_access_level, 'READ'), p_tenant_id, 0);
                SET j = j + 1;
            END WHILE;
        END IF;

        SET i = i + 1;
    END WHILE;

    SET p_created_count = v_user_count;

    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_change_tenant_subscription`$$
CREATE PROCEDURE `sp_change_tenant_subscription`(IN p_tenant_id INT, IN p_new_plan VARCHAR(255), IN p_performed_by INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    UPDATE tenants SET subscription_plan = p_new_plan, updated_by = p_performed_by WHERE id = p_tenant_id;
    INSERT INTO platform_audit_logs (tenant_id, action_type, performed_by, description) 
    VALUES (p_tenant_id, 'CHANGE_SUBSCRIPTION', p_performed_by, JSON_OBJECT('new_plan', p_new_plan));
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_create_attendance`$$
CREATE PROCEDURE `sp_create_attendance`(
    IN  p_tenant_id     INT,
    IN  p_employee_id   INT,
    IN  p_employee_name VARCHAR(255),
    IN  p_date          DATE,
    IN  p_status        VARCHAR(50),
    IN  p_hours         DECIMAL(5,2),
    IN  p_check_in      VARCHAR(20),
    IN  p_check_out     VARCHAR(20),
    IN  p_notes         TEXT,
    IN  p_created_by    INT,
    OUT p_attendance_id BIGINT
)
proc: BEGIN
    DECLARE v_name VARCHAR(255);
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_date IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Attendance date is required';
    END IF;

    SET v_name = NULLIF(TRIM(COALESCE(p_employee_name, '')), '');
    IF v_name IS NULL AND p_employee_id IS NOT NULL THEN
        SELECT NULLIF(TRIM(CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, ''))), '')
          INTO v_name
          FROM dim_employees
         WHERE id = p_employee_id AND tenant_id = p_tenant_id
         LIMIT 1;
    END IF;
    SET v_name = COALESCE(v_name, 'Unknown');

    INSERT INTO fact_attendance (
        tenant_id, employee_id, employee_name, date, status, hours_worked,
        check_in, check_out, notes, created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_employee_id, v_name, p_date,
        COALESCE(p_status, 'Present'), p_hours,
        NULLIF(p_check_in, ''), NULLIF(p_check_out, ''), p_notes,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_attendance_id = LAST_INSERT_ID();
END $$

DROP PROCEDURE IF EXISTS `sp_create_commission_on_deal_close`$$
CREATE PROCEDURE `sp_create_commission_on_deal_close`(
    IN p_tenant_id INT,
    IN p_opportunity_id BIGINT,
    IN p_agent_id INT,
    IN p_period VARCHAR(50),
    IN p_updated_by INT
)
BEGIN
    DECLARE v_deal_value DECIMAL(15,2);
    DECLARE v_old_status VARCHAR(50);
    DECLARE v_agent_name VARCHAR(255);
    DECLARE v_rate DECIMAL(5,2);
    DECLARE v_earned DECIMAL(15,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    SELECT deal_value, status INTO v_deal_value, v_old_status FROM fact_opportunities WHERE id = p_opportunity_id;
    SELECT CONCAT(first_name, ' ', last_name), commission_rate INTO v_agent_name, v_rate FROM dim_sales_agents WHERE id = p_agent_id;
    SET v_earned = v_deal_value * (v_rate / 100);
    UPDATE fact_opportunities SET status = 'Deal_Won', updated_by = p_updated_by WHERE id = p_opportunity_id;
    INSERT INTO fact_opportunity_logs (tenant_id, opportunity_id, old_status, new_status, changed_by)
    VALUES (p_tenant_id, p_opportunity_id, v_old_status, 'Deal_Won', p_updated_by);
    INSERT INTO fact_commissions (tenant_id, agent_id, agent_name, period, deal_ref, gross_amount, rate, earned, total, status, created_by)
    VALUES (p_tenant_id, p_agent_id, v_agent_name, p_period, CAST(p_opportunity_id AS CHAR), v_deal_value, v_rate, v_earned, v_earned, 'Pending', p_updated_by);
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_create_digital_asset`$$
CREATE PROCEDURE `sp_create_digital_asset`(
    IN  p_tenant_id   INT,
    IN  p_name        VARCHAR(255),
    IN  p_category    VARCHAR(150),
    IN  p_vendor      VARCHAR(255),
    IN  p_cost        DECIMAL(15,2),
    IN  p_currency    VARCHAR(10),
    IN  p_status      VARCHAR(50),
    IN  p_expiry_date DATE,
    IN  p_assigned_to VARCHAR(255),
    IN  p_created_by  INT,
    OUT p_asset_id    INT
)
proc: BEGIN
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_name IS NULL OR p_name = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Asset name is required';
    END IF;

    INSERT INTO inv_digital_assets (
        tenant_id, name, category, vendor, cost, currency, status, expiry_date, assigned_to,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_name, p_category, p_vendor, p_cost, COALESCE(p_currency, 'USD'),
        COALESCE(p_status, 'Active'), p_expiry_date, p_assigned_to,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_asset_id = LAST_INSERT_ID();
END $$

DROP PROCEDURE IF EXISTS `sp_create_invoice_with_ledger`$$
CREATE PROCEDURE `sp_create_invoice_with_ledger`(
    IN p_tenant_id INT,
    IN p_opportunity_id BIGINT,
    IN p_wallet_id INT,
    IN p_client_id INT,
    IN p_invoice_no VARCHAR(100),
    IN p_amount DECIMAL(15,2),
    IN p_tax_amount DECIMAL(15,2),
    IN p_currency VARCHAR(10),
    IN p_issue_date DATE,
    IN p_due_date DATE,
    IN p_notes TEXT,
    IN p_created_by INT,
    OUT p_invoice_id BIGINT
)
BEGIN
    DECLARE v_client_name VARCHAR(255);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT company_name INTO v_client_name
    FROM dim_companies
    WHERE id = p_client_id AND tenant_id = p_tenant_id;

    INSERT INTO fact_invoices (
        tenant_id, opportunity_id, wallet_id, client_id, invoice_no, client_name, amount,
        tax_amount, currency, status, issue_date, due_date, notes, created_by
    ) VALUES (
        p_tenant_id, p_opportunity_id, p_wallet_id, p_client_id, p_invoice_no, v_client_name,
        p_amount, p_tax_amount, p_currency, 'Draft', p_issue_date, p_due_date, p_notes, p_created_by
    );

    SET p_invoice_id = LAST_INSERT_ID();

    INSERT INTO fact_ledger_entries (
        tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by
    ) VALUES (
        p_tenant_id, p_wallet_id, 'Debit', p_amount + p_tax_amount, 'Invoice', p_invoice_id, p_created_by
    );

    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_create_liability`$$
CREATE PROCEDURE `sp_create_liability`(
    IN p_tenant_id INT, IN p_name VARCHAR(255), IN p_type VARCHAR(50), IN p_counterparty VARCHAR(255),
    IN p_principal DECIMAL(15,2), IN p_outstanding DECIMAL(15,2), IN p_currency VARCHAR(10), IN p_interest_rate DECIMAL(6,3),
    IN p_start_date DATE, IN p_due_date DATE, IN p_status VARCHAR(50), IN p_notes TEXT, IN p_created_by INT, OUT p_liability_id INT)
proc: BEGIN
    IF p_name IS NULL OR p_name = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Liability name is required'; END IF;
    INSERT INTO fin_liabilities (tenant_id,name,type,counterparty,principal,outstanding,currency,interest_rate,start_date,due_date,status,notes,created_at,created_by,updated_at,updated_by,is_deleted)
    VALUES (p_tenant_id,p_name,p_type,p_counterparty,COALESCE(p_principal,0),COALESCE(p_outstanding,p_principal),COALESCE(p_currency,'USD'),p_interest_rate,p_start_date,p_due_date,COALESCE(p_status,'Active'),p_notes,NOW(),p_created_by,NOW(),p_created_by,0);
    SET p_liability_id = LAST_INSERT_ID();
END $$

DROP PROCEDURE IF EXISTS `sp_create_loan`$$
CREATE PROCEDURE `sp_create_loan`(
    IN  p_tenant_id     INT,
    IN  p_employee_id   INT,
    IN  p_employee_name VARCHAR(255),
    IN  p_loan_amount   DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_installments  INT,
    IN  p_monthly       DECIMAL(15,2),
    IN  p_start_date    DATE,
    IN  p_status        VARCHAR(50),
    IN  p_purpose       TEXT,
    IN  p_notes         TEXT,
    IN  p_created_by    INT,
    OUT p_loan_id       BIGINT
)
proc: BEGIN
    DECLARE v_name VARCHAR(255);
    DECLARE v_installments INT;
    DECLARE v_monthly DECIMAL(15,2);
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_loan_amount IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Loan amount is required';
    END IF;

    SET v_name = NULLIF(TRIM(COALESCE(p_employee_name, '')), '');
    IF v_name IS NULL AND p_employee_id IS NOT NULL THEN
        SELECT NULLIF(TRIM(CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, ''))), '')
          INTO v_name FROM dim_employees
         WHERE id = p_employee_id AND tenant_id = p_tenant_id LIMIT 1;
    END IF;
    SET v_name = COALESCE(v_name, 'Unknown');

    SET v_installments = COALESCE(NULLIF(p_installments, 0), 1);
    SET v_monthly = COALESCE(p_monthly, ROUND(p_loan_amount / v_installments, 2));

    INSERT INTO fact_loans (
        tenant_id, employee_id, employee_name, loan_amount, currency, no_of_installments,
        monthly_amount, start_date, status, purpose, notes,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_employee_id, v_name, p_loan_amount, COALESCE(p_currency, 'USD'), v_installments,
        v_monthly, p_start_date, COALESCE(p_status, 'Pending'), p_purpose, p_notes,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_loan_id = LAST_INSERT_ID();
END $$

DROP PROCEDURE IF EXISTS `sp_create_material`$$
CREATE PROCEDURE `sp_create_material`(
    IN p_tenant_id INT, IN p_name VARCHAR(255), IN p_code VARCHAR(100), IN p_category VARCHAR(150),
    IN p_unit VARCHAR(50), IN p_stock_qty DECIMAL(15,2), IN p_unit_cost DECIMAL(15,2), IN p_currency VARCHAR(10),
    IN p_reorder_level DECIMAL(15,2), IN p_status VARCHAR(50), IN p_notes TEXT, IN p_created_by INT, OUT p_material_id INT)
proc: BEGIN
    IF p_name IS NULL OR p_name = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Material name is required'; END IF;
    INSERT INTO inv_materials (tenant_id,name,code,category,unit,stock_qty,unit_cost,currency,reorder_level,status,notes,created_at,created_by,updated_at,updated_by,is_deleted)
    VALUES (p_tenant_id,p_name,p_code,p_category,p_unit,COALESCE(p_stock_qty,0),p_unit_cost,COALESCE(p_currency,'USD'),COALESCE(p_reorder_level,0),COALESCE(p_status,'Active'),p_notes,NOW(),p_created_by,NOW(),p_created_by,0);
    SET p_material_id = LAST_INSERT_ID();
END $$

DROP PROCEDURE IF EXISTS `sp_create_opportunity`$$
CREATE PROCEDURE `sp_create_opportunity`(
    IN p_tenant_id INT,
    IN p_company_id INT,
    IN p_contact_id INT,
    IN p_title VARCHAR(255),
    IN p_status VARCHAR(50),
    IN p_stage VARCHAR(50),
    IN p_source VARCHAR(100),
    IN p_probability INT,
    IN p_deal_value DECIMAL(15,2),
    IN p_currency VARCHAR(10),
    IN p_assigned_to INT,
    IN p_expected_close_date DATE,
    IN p_notes TEXT,
    IN p_created_by INT,
    OUT p_opportunity_id BIGINT
)
BEGIN
    DECLARE v_full_name VARCHAR(255);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SET v_full_name = p_title;
    IF p_contact_id IS NOT NULL THEN
        SELECT CONCAT(first_name, ' ', last_name) INTO v_full_name
        FROM dim_contacts
        WHERE id = p_contact_id AND tenant_id = p_tenant_id;
        IF v_full_name IS NULL THEN
            SET v_full_name = p_title;
        END IF;
    END IF;

    INSERT INTO fact_opportunities (
        tenant_id, company_id, contact_id, full_name, title, deal_title, status, stage, source,
        probability, deal_value, currency, assigned_to, expected_close_date, notes, created_by
    ) VALUES (
        p_tenant_id, p_company_id, p_contact_id, v_full_name, p_title, p_title, p_status, p_stage, p_source,
        p_probability, p_deal_value, p_currency, p_assigned_to, p_expected_close_date, p_notes, p_created_by
    );

    SET p_opportunity_id = LAST_INSERT_ID();
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_create_order`$$
CREATE PROCEDURE `sp_create_order`(
    IN  p_tenant_id     INT,
    IN  p_order_no      VARCHAR(100),
    IN  p_client_name   VARCHAR(255),
    IN  p_status        VARCHAR(50),
    IN  p_total_amount  DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_no_of_items   INT,
    IN  p_order_date    DATE,
    IN  p_delivery_date DATE,
    IN  p_notes         TEXT,
    IN  p_created_by    INT,
    OUT p_order_id      BIGINT
)
proc: BEGIN
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_order_no IS NULL OR p_order_no = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Order number is required';
    END IF;
    IF p_client_name IS NULL OR p_client_name = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Client name is required';
    END IF;

    INSERT INTO fact_orders (
        tenant_id, order_no, client_name, status, total_amount, currency,
        no_of_items, order_date, delivery_date, notes,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_order_no, p_client_name,
        COALESCE(p_status, 'Pending'),
        COALESCE(p_total_amount, 0),
        COALESCE(p_currency, 'USD'),
        COALESCE(p_no_of_items, 0),
        p_order_date, p_delivery_date, p_notes,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );

    SET p_order_id = LAST_INSERT_ID();
END $$

DROP PROCEDURE IF EXISTS `sp_create_product`$$
CREATE PROCEDURE `sp_create_product`(
    IN  p_tenant_id     INT,
    IN  p_name          VARCHAR(255),
    IN  p_sku           VARCHAR(100),
    IN  p_category      VARCHAR(150),
    IN  p_unit_price    DECIMAL(15,2),
    IN  p_cost_price    DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_stock_qty     INT,
    IN  p_reorder_level INT,
    IN  p_unit          VARCHAR(50),
    IN  p_status        VARCHAR(50),
    IN  p_created_by    INT,
    OUT p_product_id    INT
)
proc: BEGIN
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_name IS NULL OR p_name = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Product name is required';
    END IF;

    INSERT INTO inv_products (
        tenant_id, name, sku, category, unit_price, cost_price, currency,
        stock_qty, reorder_level, unit, status,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_name, p_sku, p_category, COALESCE(p_unit_price, 0), p_cost_price,
        COALESCE(p_currency, 'USD'), COALESCE(p_stock_qty, 0), COALESCE(p_reorder_level, 0),
        p_unit, COALESCE(p_status, 'Active'),
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_product_id = LAST_INSERT_ID();
END $$

DROP PROCEDURE IF EXISTS `sp_create_purchase`$$
CREATE PROCEDURE `sp_create_purchase`(
    IN  p_tenant_id     INT,
    IN  p_supplier_id   INT,
    IN  p_wallet_id     INT,
    IN  p_is_capex      TINYINT,
    IN  p_amount        DECIMAL(15,2),
    IN  p_purchase_date DATE,
    IN  p_notes         TEXT,
    IN  p_created_by    INT,
    OUT p_purchase_id   BIGINT
)
proc: BEGIN
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_supplier_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Supplier is required';
    END IF;
    IF p_wallet_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Wallet is required';
    END IF;

    INSERT INTO fact_purchases (
        tenant_id, supplier_id, wallet_id, is_capital_expenditure, amount,
        purchase_date, notes, created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_supplier_id, p_wallet_id, COALESCE(p_is_capex, 0), COALESCE(p_amount, 0),
        p_purchase_date, p_notes, NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_purchase_id = LAST_INSERT_ID();
END $$

DROP PROCEDURE IF EXISTS `sp_create_resource`$$
CREATE PROCEDURE `sp_create_resource`(
    IN p_tenant_id INT, IN p_name VARCHAR(255), IN p_type VARCHAR(50), IN p_project_id INT, IN p_role VARCHAR(150),
    IN p_allocation_pct INT, IN p_cost_rate DECIMAL(15,2), IN p_currency VARCHAR(10), IN p_availability VARCHAR(50),
    IN p_notes TEXT, IN p_created_by INT, OUT p_resource_id INT)
proc: BEGIN
    IF p_name IS NULL OR p_name = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Resource name is required'; END IF;
    INSERT INTO proj_resources (tenant_id,name,type,project_id,role,allocation_pct,cost_rate,currency,availability,notes,created_at,created_by,updated_at,updated_by,is_deleted)
    VALUES (p_tenant_id,p_name,p_type,p_project_id,p_role,p_allocation_pct,p_cost_rate,COALESCE(p_currency,'USD'),COALESCE(p_availability,'Available'),p_notes,NOW(),p_created_by,NOW(),p_created_by,0);
    SET p_resource_id = LAST_INSERT_ID();
END $$

DROP PROCEDURE IF EXISTS `sp_create_sprint`$$
CREATE PROCEDURE `sp_create_sprint`(
    IN p_tenant_id INT, IN p_name VARCHAR(255), IN p_project_id INT, IN p_goal TEXT,
    IN p_start_date DATE, IN p_end_date DATE, IN p_status VARCHAR(50), IN p_capacity INT, IN p_created_by INT, OUT p_sprint_id INT)
proc: BEGIN
    IF p_name IS NULL OR p_name = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sprint name is required'; END IF;
    INSERT INTO proj_sprints (tenant_id,name,project_id,goal,start_date,end_date,status,capacity,created_at,created_by,updated_at,updated_by,is_deleted)
    VALUES (p_tenant_id,p_name,p_project_id,p_goal,p_start_date,p_end_date,COALESCE(p_status,'Planning'),p_capacity,NOW(),p_created_by,NOW(),p_created_by,0);
    SET p_sprint_id = LAST_INSERT_ID();
END $$

DROP PROCEDURE IF EXISTS `sp_create_supplier`$$
CREATE PROCEDURE `sp_create_supplier`(
    IN  p_tenant_id   INT,
    IN  p_vendor_name VARCHAR(255),
    IN  p_category    VARCHAR(100),
    IN  p_tax_id      VARCHAR(50),
    IN  p_created_by  INT,
    OUT p_supplier_id INT
)
proc: BEGIN
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_vendor_name IS NULL OR p_vendor_name = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Vendor name is required';
    END IF;

    INSERT INTO dim_suppliers (
        tenant_id, vendor_name, category, tax_id,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_vendor_name, p_category, p_tax_id,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_supplier_id = LAST_INSERT_ID();
END $$

DROP PROCEDURE IF EXISTS `sp_create_vat_record`$$
CREATE PROCEDURE `sp_create_vat_record`(IN p_tenant_id INT, IN p_period VARCHAR(50), IN p_sales_vat DECIMAL(15,2), IN p_purchase_vat DECIMAL(15,2), IN p_due_date DATE, IN p_created_by INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    -- Guard logic: prevent duplicate periods natively
    IF EXISTS (SELECT 1 FROM fact_vat WHERE tenant_id = p_tenant_id AND period = p_period AND is_deleted = 0) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate VAT period detected.';
    ELSE
        INSERT INTO fact_vat (tenant_id, period, sales_vat, purchase_vat, due_date, created_by)
        VALUES (p_tenant_id, p_period, p_sales_vat, p_purchase_vat, p_due_date, p_created_by);
    END IF;
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_link_employee_to_user`$$
CREATE PROCEDURE `sp_link_employee_to_user`(
    IN p_tenant_id INT,
    IN p_employee_id INT,
    IN p_user_id INT,
    IN p_role_id INT,
    IN p_updated_by INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    UPDATE dim_employees SET user_id = p_user_id, updated_by = p_updated_by WHERE id = p_employee_id AND tenant_id = p_tenant_id;
    UPDATE users SET role_id = p_role_id, updated_by = p_updated_by WHERE id = p_user_id AND tenant_id = p_tenant_id;
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_log_compliance_event`$$
CREATE PROCEDURE `sp_log_compliance_event`(
    IN p_tenant_id INT,
    IN p_legal_asset_id INT,
    IN p_log_type VARCHAR(50),
    IN p_details TEXT,
    IN p_new_expiry_date DATE,
    IN p_created_by INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    IF p_new_expiry_date IS NOT NULL THEN
        UPDATE dim_legal_assets SET expiry_date = p_new_expiry_date, updated_by = p_created_by WHERE id = p_legal_asset_id AND tenant_id = p_tenant_id;
    END IF;
    INSERT INTO fact_compliance_logs (tenant_id, legal_asset_id, log_type, details, created_by)
    VALUES (p_tenant_id, p_legal_asset_id, p_log_type, p_details, p_created_by);
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_onboard_employee_full`$$
CREATE PROCEDURE `sp_onboard_employee_full`(
    IN p_tenant_id INT,
    IN p_first_name VARCHAR(100),
    IN p_last_name VARCHAR(100),
    IN p_email VARCHAR(255),
    IN p_phone VARCHAR(50),
    IN p_department_id INT,
    IN p_designation_id INT,
    IN p_contract_type VARCHAR(50),
    IN p_salary DECIMAL(15,2),
    IN p_currency VARCHAR(10),
    IN p_join_date DATE,
    IN p_manager VARCHAR(100),
    IN p_location VARCHAR(100),
    IN p_contract_start DATE,
    IN p_contract_end DATE,
    IN p_allowances_json JSON,
    IN p_create_user TINYINT,
    IN p_password_hash VARCHAR(512),
    IN p_password_salt VARCHAR(255),
    IN p_role_id INT,
    IN p_created_by INT,
    OUT p_employee_id INT
)
BEGIN
    DECLARE v_user_id INT DEFAULT NULL;
    DECLARE v_department_name VARCHAR(100) DEFAULT NULL;
    DECLARE v_job_title VARCHAR(100) DEFAULT NULL;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    IF p_department_id IS NOT NULL THEN
        SELECT department_name INTO v_department_name
        FROM dim_departments
        WHERE id = p_department_id AND tenant_id = p_tenant_id;
    END IF;

    IF p_designation_id IS NOT NULL THEN
        SELECT title INTO v_job_title
        FROM dim_designations
        WHERE id = p_designation_id AND tenant_id = p_tenant_id;
    END IF;

    IF p_create_user = 1 THEN
        INSERT INTO users (
            tenant_id, first_name, last_name, email, password_hash, password_salt, role_id,
            is_enabled, created_by
        ) VALUES (
            p_tenant_id, p_first_name, p_last_name, p_email, p_password_hash, p_password_salt,
            p_role_id, b'1', p_created_by
        );
        SET v_user_id = LAST_INSERT_ID();
    END IF;

    INSERT INTO dim_employees (
        tenant_id, user_id, department_id, designation_id, first_name, last_name, email, phone,
        department, job_title, contract_type, salary, currency, join_date, manager, location,
        created_by
    ) VALUES (
        p_tenant_id, v_user_id, p_department_id, p_designation_id, p_first_name, p_last_name,
        p_email, p_phone, v_department_name, v_job_title, p_contract_type, p_salary, p_currency,
        p_join_date, p_manager, p_location, p_created_by
    );
    SET p_employee_id = LAST_INSERT_ID();

    INSERT INTO dim_employee_contracts (
        tenant_id, employee_id, start_date, end_date, allowances_json, created_by
    ) VALUES (
        p_tenant_id, p_employee_id, p_contract_start, p_contract_end, p_allowances_json, p_created_by
    );

    INSERT INTO dim_wallets (
        tenant_id, wallet_name, owner_type, employee_id, currency, created_by
    ) VALUES (
        p_tenant_id, CONCAT(p_first_name, ' ', p_last_name, ' Wallet'), 'Employee', p_employee_id,
        p_currency, p_created_by
    );

    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_pay_commission`$$
CREATE PROCEDURE `sp_pay_commission`(IN p_tenant_id INT, IN p_commission_id BIGINT, IN p_corp_wallet_id INT, IN p_agent_wallet_id INT, IN p_performed_by INT)
BEGIN
    DECLARE v_amount DECIMAL(15,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    
    SELECT total INTO v_amount FROM fact_commissions WHERE id = p_commission_id AND tenant_id = p_tenant_id;
    
    UPDATE fact_commissions SET status = 'Paid', paid_date = CURRENT_DATE, updated_by = p_performed_by WHERE id = p_commission_id AND tenant_id = p_tenant_id;
    
    INSERT INTO fact_transactions (tenant_id, from_wallet_id, to_wallet_id, amount, reference_entity, reference_id, transaction_type, status, created_by)
    VALUES (p_tenant_id, p_corp_wallet_id, p_agent_wallet_id, v_amount, 'Commission', p_commission_id, 'Transfer', 'Completed', p_performed_by);
    
    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by)
    VALUES (p_tenant_id, p_corp_wallet_id, 'Debit', v_amount, 'Commission', p_commission_id, p_performed_by);
    
    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by)
    VALUES (p_tenant_id, p_agent_wallet_id, 'Credit', v_amount, 'Commission', p_commission_id, p_performed_by);
    
    UPDATE dim_wallets SET balance = balance - v_amount WHERE id = p_corp_wallet_id;
    UPDATE dim_wallets SET balance = balance + v_amount WHERE id = p_agent_wallet_id;
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_pay_invoice`$$
CREATE PROCEDURE `sp_pay_invoice`(
    IN p_tenant_id INT,
    IN p_invoice_id BIGINT,
    IN p_client_wallet_id INT,
    IN p_corp_wallet_id INT,
    IN p_updated_by INT
)
BEGIN
    DECLARE v_total_amount DECIMAL(15,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT (amount + tax_amount) INTO v_total_amount FROM fact_invoices WHERE id = p_invoice_id;

    UPDATE fact_invoices SET status = 'Paid', updated_by = p_updated_by
    WHERE id = p_invoice_id AND tenant_id = p_tenant_id;

    INSERT INTO fact_transactions (
        tenant_id, from_wallet_id, to_wallet_id, amount, description, reference_entity,
        reference_id, transaction_type, status, created_by
    ) VALUES (
        p_tenant_id, p_client_wallet_id, p_corp_wallet_id, v_total_amount,
        CONCAT('Payment for Invoice #', p_invoice_id), 'Invoice', p_invoice_id, 'Income',
        'Completed', p_updated_by
    );

    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by)
    VALUES (p_tenant_id, p_client_wallet_id, 'Debit', v_total_amount, 'Invoice', p_invoice_id, p_updated_by);

    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_process_ai_bank_statement`$$
CREATE PROCEDURE `sp_process_ai_bank_statement`(
    IN  p_statement_json     LONGTEXT,
    IN  p_transactions_json  LONGTEXT,
    IN  p_created_by         INT,
    IN  p_entity_type        VARCHAR(50),
    IN  p_entity_id          BIGINT,
    OUT p_statement_id       BIGINT,
    OUT p_transaction_count  INT
)
proc: BEGIN
    DECLARE v_tenant_id     INT;
    DECLARE v_entity_id     BIGINT;
    DECLARE v_entity_type   VARCHAR(20);
    DECLARE v_account_no    VARCHAR(100);
    DECLARE v_bank_name     VARCHAR(255);
    DECLARE v_entity_label  VARCHAR(255);
    DECLARE v_business_id   INT;

    SET v_tenant_id = @current_tenant_id;
    IF v_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant context (@current_tenant_id) is required';
    END IF;

    -- Selected business scope (nullable). Persisted on the entity + statement so
    -- business-filtered list screens can find these imported records.
    SET v_business_id = @current_business_id;

    IF p_statement_json IS NULL OR JSON_VALID(p_statement_json) = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid statement JSON';
    END IF;
    IF p_transactions_json IS NULL OR JSON_VALID(p_transactions_json) = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid transactions JSON';
    END IF;

    -- Normalise entity type to the dim_financial_entities enum ('Bank' | 'Credit Card')
    SET v_entity_type = CASE
        WHEN p_entity_type IN ('CreditCard', 'Credit Card', 'CREDIT_CARD') THEN 'Credit Card'
        ELSE 'Bank'
    END;

    SET v_account_no = JSON_VALUE(p_statement_json, '$.accountNumber');

    -- ── Ensure the bank exists in the global reference list (dim_banks) ──────
    -- The AI extraction may surface a bank/issuer not yet in the Bank Name
    -- dropdown. Register it so it becomes selectable for future entities.
    -- INSERT IGNORE + UNIQUE(name) makes this an idempotent "add if missing".
    SET v_bank_name = NULLIF(TRIM(JSON_VALUE(p_statement_json, '$.bankName')), '');
    IF v_bank_name IS NOT NULL THEN
        INSERT IGNORE INTO dim_banks (name) VALUES (v_bank_name);
    END IF;

    -- User-supplied account label/nickname (falls back to bank/holder name below).
    SET v_entity_label = NULLIF(TRIM(JSON_VALUE(p_statement_json, '$.entityLabel')), '');

    -- ── Resolve the financial entity ────────────────────────────────────────
    -- 1) explicit entity_id, 2) existing tenant entity by account number, 3) create.
    IF p_entity_id IS NOT NULL THEN
        SELECT id INTO v_entity_id
        FROM dim_financial_entities
        WHERE id = p_entity_id AND tenant_id = v_tenant_id AND is_deleted = 0
        LIMIT 1;
    END IF;

    IF v_entity_id IS NULL AND v_account_no IS NOT NULL AND v_account_no <> '' THEN
        SELECT id INTO v_entity_id
        FROM dim_financial_entities
        WHERE tenant_id = v_tenant_id
          AND account_number = v_account_no
          AND entity_type = v_entity_type
          AND is_deleted = 0
        ORDER BY id LIMIT 1;
    END IF;

    IF v_entity_id IS NULL THEN
        INSERT INTO dim_financial_entities (
            tenant_id, business_id, entity_name, entity_type, account_holder_name, account_number,
            bank_or_issuer_name, currency, status, created_by, updated_by, is_deleted
        ) VALUES (
            v_tenant_id,
            v_business_id,
            COALESCE(
                v_entity_label,
                v_bank_name,
                NULLIF(JSON_VALUE(p_statement_json, '$.accountHolderName'), ''),
                'Imported Entity'),
            v_entity_type,
            JSON_VALUE(p_statement_json, '$.accountHolderName'),
            v_account_no,
            v_bank_name,
            COALESCE(NULLIF(JSON_VALUE(p_statement_json, '$.currency'), ''), 'USD'),
            'Active',
            p_created_by, p_created_by, 0
        );
        SET v_entity_id = LAST_INSERT_ID();
    END IF;

    -- Backfill business scope on a pre-existing entity that was imported without one
    -- (e.g. before business_id propagation existed), so it appears in scoped lists.
    IF v_business_id IS NOT NULL AND v_entity_id IS NOT NULL THEN
        UPDATE dim_financial_entities
        SET business_id = v_business_id
        WHERE id = v_entity_id AND tenant_id = v_tenant_id AND business_id IS NULL;
    END IF;

    -- ── Insert the statement header ─────────────────────────────────────────
    INSERT INTO fact_statements (
        tenant_id, business_id, entity_id, statement_period, beginning_balance, current_balance,
        current_interest_rate, created_by, updated_by, is_deleted
    ) VALUES (
        v_tenant_id,
        v_business_id,
        v_entity_id,
        JSON_VALUE(p_statement_json, '$.statementPeriod'),
        CAST(JSON_VALUE(p_statement_json, '$.beginningBalance') AS DECIMAL(15,2)),
        CAST(JSON_VALUE(p_statement_json, '$.currentBalance') AS DECIMAL(15,2)),
        CAST(JSON_VALUE(p_statement_json, '$.interestRate') AS DECIMAL(5,2)),
        p_created_by, p_created_by, 0
    );

    SET p_statement_id = LAST_INSERT_ID();

    -- ── Insert the transactions ─────────────────────────────────────────────
    INSERT INTO fact_statement_transactions (
        tenant_id, statement_id, transaction_date, transaction_type, description, vendor,
        amount, cash_in_or_out, personal_or_business, associated_card_number, home_tax_percent,
        person_or_project_1, expense_labels, pdf_link, created_by, updated_by, is_deleted
    )
    SELECT
        v_tenant_id,
        p_statement_id,
        jt.transaction_date,
        CASE
            WHEN jt.transaction_type IN ('Credit','Debit','Transfer','Fee','Interest','Reversal')
                THEN jt.transaction_type
            WHEN LOWER(COALESCE(jt.cash_in_or_out, '')) LIKE '%in%' THEN 'Credit'
            ELSE 'Debit'
        END,
        jt.description,
        jt.vendor,
        jt.amount,
        CASE WHEN LOWER(COALESCE(jt.cash_in_or_out, '')) LIKE '%in%' THEN 'Cash In' ELSE 'Cash Out' END,
        CASE WHEN jt.personal_or_business IN ('Personal','Business') THEN jt.personal_or_business ELSE NULL END,
        jt.associated_card_number,
        jt.home_tax_percent,
        jt.person_or_project_1,
        jt.expense_labels,
        jt.pdf_link,
        p_created_by, p_created_by, 0
    FROM JSON_TABLE(
        p_transactions_json,
        '$[*]' COLUMNS (
            transaction_date      DATE           PATH '$.transactionDate',
            transaction_type      VARCHAR(50)    PATH '$.transactionType',
            description           TEXT           PATH '$.description',
            vendor                VARCHAR(255)   PATH '$.vendor',
            amount                DECIMAL(15,2)  PATH '$.amount',
            cash_in_or_out        VARCHAR(20)    PATH '$.cashInOrOut',
            personal_or_business  VARCHAR(20)    PATH '$.personalOrBusiness',
            associated_card_number VARCHAR(100)  PATH '$.associatedCardNumber',
            home_tax_percent      DECIMAL(5,2)   PATH '$.homeTaxPercent',
            person_or_project_1   VARCHAR(255)   PATH '$.personOrProject1',
            expense_labels        TEXT           PATH '$.expenseLabels',
            pdf_link              VARCHAR(512)   PATH '$.pdfLink'
        )
    ) AS jt;

    SET p_transaction_count = ROW_COUNT();
END proc $$

DROP PROCEDURE IF EXISTS `sp_process_payroll`$$
CREATE PROCEDURE `sp_process_payroll`(
    IN p_tenant_id INT,
    IN p_employee_id INT,
    IN p_month VARCHAR(20),
    IN p_base_salary DECIMAL(15,2),
    IN p_allowances DECIMAL(15,2),
    IN p_deductions DECIMAL(15,2),
    IN p_tax DECIMAL(15,2),
    IN p_net_pay DECIMAL(15,2),
    IN p_corp_wallet_id INT,
    IN p_created_by INT
)
BEGIN
    DECLARE v_employee_wallet_id INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    SELECT id INTO v_employee_wallet_id FROM dim_wallets WHERE employee_id = p_employee_id AND tenant_id = p_tenant_id LIMIT 1;
    INSERT INTO fact_payroll (tenant_id, employee_id, month, base_salary, allowances, deductions, tax, net_pay, status, pay_date, created_by)
    VALUES (p_tenant_id, p_employee_id, p_month, p_base_salary, p_allowances, p_deductions, p_tax, p_net_pay, 'Paid', CURRENT_DATE, p_created_by);
    INSERT INTO fact_transactions (tenant_id, from_wallet_id, to_wallet_id, amount, reference_entity, transaction_type, status, created_by)
    VALUES (p_tenant_id, p_corp_wallet_id, v_employee_wallet_id, p_net_pay, 'Payroll', 'Transfer', 'Completed', p_created_by);
    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, created_by)
    VALUES (p_tenant_id, p_corp_wallet_id, 'Debit', p_net_pay, 'Payroll', p_created_by);
    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, created_by)
    VALUES (p_tenant_id, v_employee_wallet_id, 'Credit', p_net_pay, 'Payroll', p_created_by);
    UPDATE dim_wallets SET balance = balance - p_net_pay WHERE id = p_corp_wallet_id;
    UPDATE dim_wallets SET balance = balance + p_net_pay WHERE id = v_employee_wallet_id;
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_provision_tenant_with_departments`$$
CREATE PROCEDURE `sp_provision_tenant_with_departments`(
    IN p_business_name VARCHAR(255),
    IN p_business_email VARCHAR(255),
    IN p_subscription_plan VARCHAR(255),
    IN p_admin_first VARCHAR(100),
    IN p_admin_last VARCHAR(100),
    IN p_admin_email VARCHAR(255),
    IN p_password_hash VARCHAR(512),
    IN p_departments_json JSON,      -- E.g., '[{"name": "Sales", "code": "SALES"}]'
    IN p_creator_id INT,
    OUT p_tenant_id INT,             -- Returned to Spring Boot
    OUT p_admin_user_id INT          -- Returned to Spring Boot
)
BEGIN
    DECLARE v_role_id INT;
    DECLARE v_dept_count INT;
    DECLARE v_dept_name VARCHAR(100);
    DECLARE i INT DEFAULT 0;

    -- Strict ACID compliance: Rollback everything if any insert fails
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- ============================================================================
    -- 1. Create the New Tenant Workspace
    -- ============================================================================
    INSERT INTO tenants (
        business_name, email, subscription_plan, admin_email, status, created_by
    ) VALUES (
        p_business_name, p_business_email, p_subscription_plan, p_admin_email, 'Active', p_creator_id
    );
    
    SET p_tenant_id = LAST_INSERT_ID();

    -- ============================================================================
    -- 2. Create the 'Tenant Admin' Role & Enforce Platform RLS
    -- ============================================================================
    SELECT id INTO v_role_id FROM roles WHERE tenant_id = p_tenant_id AND role_name = 'Tenant Admin' LIMIT 1;
    
    IF v_role_id IS NULL THEN
        INSERT INTO roles (tenant_id, role_name, created_by) VALUES (p_tenant_id, 'Tenant Admin', p_creator_id);
        SET v_role_id = LAST_INSERT_ID();
        
        -- Restrict this role so the Admin sees all data belonging strictly to their tenant
        INSERT INTO data_scopes (tenant_id, role_id, scope_type) VALUES (p_tenant_id, v_role_id, 'All');
    END IF;

    -- ============================================================================
    -- 3. Create the Master Admin User Profile
    -- ============================================================================
    INSERT INTO users (
        tenant_id, first_name, last_name, email, password_hash, 
        role_id, status, is_super_admin, user_type, is_enabled, created_by
    ) VALUES (
        p_tenant_id, p_admin_first, p_admin_last, p_admin_email, p_password_hash, 
        v_role_id, 'Active', 0, 'TENANT_ADMIN', b'1', p_creator_id
    );
    
    SET p_admin_user_id = LAST_INSERT_ID();

    -- ============================================================================
    -- 4. Iteratively Create Multiple Departments from the JSON Array
    -- ============================================================================
    IF p_departments_json IS NOT NULL THEN
        SET v_dept_count = JSON_LENGTH(p_departments_json);
        WHILE i < v_dept_count DO
            -- Extracts ONLY the 'name' field from the object: {"name": "Sales", "code": "SALES"}
            SET v_dept_name = JSON_UNQUOTE(JSON_EXTRACT(p_departments_json, CONCAT('$[', i, '].name')));
            
            IF v_dept_name IS NOT NULL THEN
                INSERT INTO dim_departments (tenant_id, department_name, created_by)
                VALUES (p_tenant_id, v_dept_name, p_admin_user_id);
            END IF;
            
            SET i = i + 1;
        END WHILE;
    END IF;

    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_reactivate_tenant`$$
CREATE PROCEDURE `sp_reactivate_tenant`(IN p_tenant_id INT, IN p_performed_by INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    UPDATE tenants SET status = 'Active', updated_by = p_performed_by WHERE id = p_tenant_id;
    UPDATE users SET is_enabled = b'1', status = 'Active', updated_by = p_performed_by WHERE tenant_id = p_tenant_id AND is_deleted = 0;
    INSERT INTO platform_audit_logs (tenant_id, action_type, performed_by, description) 
    VALUES (p_tenant_id, 'REACTIVATE_TENANT', p_performed_by, '{"status": "Active"}');
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_renew_employee_contract`$$
CREATE PROCEDURE `sp_renew_employee_contract`(IN p_tenant_id INT, IN p_employee_id INT, IN p_start_date DATE, IN p_end_date DATE, IN p_allowances_json JSON, IN p_created_by INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    -- Soft-delete existing active contract
    UPDATE dim_employee_contracts SET is_deleted = 1, updated_by = p_created_by, updated_at = CURRENT_TIMESTAMP 
    WHERE employee_id = p_employee_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    
    -- Insert new active contract
    INSERT INTO dim_employee_contracts (tenant_id, employee_id, start_date, end_date, allowances_json, created_by)
    VALUES (p_tenant_id, p_employee_id, p_start_date, p_end_date, p_allowances_json, p_created_by);
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_revoke_role_from_user`$$
CREATE PROCEDURE `sp_revoke_role_from_user`(
    IN p_tenant_id INT,
    IN p_user_id INT,
    IN p_changed_by INT
)
BEGIN
    DECLARE v_role_id INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    SELECT role_id INTO v_role_id FROM users WHERE id = p_user_id;
    UPDATE users SET role_id = NULL, updated_by = p_changed_by, updated_at = CURRENT_TIMESTAMP 
    WHERE id = p_user_id AND tenant_id = p_tenant_id;
    DELETE FROM data_scopes WHERE tenant_id = p_tenant_id AND role_id = v_role_id;
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_set_feature_flag`$$
CREATE PROCEDURE `sp_set_feature_flag`(IN p_tenant_id INT, IN p_feature_key VARCHAR(100), IN p_is_enabled BOOLEAN, IN p_performed_by INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    IF EXISTS (SELECT 1 FROM platform_feature_flags WHERE flag_key = p_feature_key AND (tenant_id = p_tenant_id OR (tenant_id IS NULL AND p_tenant_id IS NULL))) THEN
        UPDATE platform_feature_flags SET is_enabled = p_is_enabled WHERE flag_key = p_feature_key AND (tenant_id = p_tenant_id OR (tenant_id IS NULL AND p_tenant_id IS NULL));
    ELSE
        INSERT INTO platform_feature_flags (tenant_id, flag_key, is_enabled) VALUES (p_tenant_id, p_feature_key, p_is_enabled);
    END IF;
    INSERT INTO platform_audit_logs (tenant_id, action_type, performed_by, description) 
    VALUES (p_tenant_id, 'SET_FEATURE_FLAG', p_performed_by, JSON_OBJECT('feature_key', p_feature_key, 'is_enabled', p_is_enabled));
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_snapshot_tenant_usage`$$
CREATE PROCEDURE `sp_snapshot_tenant_usage`(IN p_tenant_id INT, IN p_snapshot_date DATE, IN p_metric_type VARCHAR(50), IN p_usage_value DECIMAL(15,2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    IF EXISTS (SELECT 1 FROM tenant_usage_metrics WHERE tenant_id = p_tenant_id AND snapshot_date = p_snapshot_date AND metric_key = p_metric_type) THEN
        UPDATE tenant_usage_metrics SET metric_value = p_usage_value WHERE tenant_id = p_tenant_id AND snapshot_date = p_snapshot_date AND metric_key = p_metric_type;
    ELSE
        INSERT INTO tenant_usage_metrics (tenant_id, metric_key, metric_value, snapshot_date) 
        VALUES (p_tenant_id, p_metric_type, p_usage_value, p_snapshot_date);
    END IF;
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_soft_delete_attendance`$$
CREATE PROCEDURE `sp_soft_delete_attendance`(
    IN  p_tenant_id     INT,
    IN  p_attendance_id BIGINT,
    IN  p_deleted_by    INT
)
proc: BEGIN
    UPDATE fact_attendance SET
        is_deleted = 1, updated_at = NOW(), updated_by = p_deleted_by
    WHERE id = p_attendance_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END $$

DROP PROCEDURE IF EXISTS `sp_soft_delete_company`$$
CREATE PROCEDURE `sp_soft_delete_company`(
    IN p_tenant_id INT,
    IN p_company_id INT,
    IN p_deleted_by INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    UPDATE dim_companies SET is_deleted = 1, updated_by = p_deleted_by WHERE id = p_company_id AND tenant_id = p_tenant_id;
    UPDATE dim_contacts SET is_deleted = 1, updated_by = p_deleted_by WHERE company_id = p_company_id AND tenant_id = p_tenant_id;
    UPDATE fact_opportunities SET is_deleted = 1, updated_by = p_deleted_by WHERE company_id = p_company_id AND tenant_id = p_tenant_id AND status != 'Deal_Won';
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_soft_delete_digital_asset`$$
CREATE PROCEDURE `sp_soft_delete_digital_asset`(
    IN  p_tenant_id  INT,
    IN  p_asset_id   INT,
    IN  p_deleted_by INT
)
proc: BEGIN
    UPDATE inv_digital_assets SET
        is_deleted = 1, updated_at = NOW(), updated_by = p_deleted_by
    WHERE id = p_asset_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END $$

DROP PROCEDURE IF EXISTS `sp_soft_delete_liability`$$
CREATE PROCEDURE `sp_soft_delete_liability`(IN p_tenant_id INT, IN p_liability_id INT, IN p_deleted_by INT)
proc: BEGIN
    UPDATE fin_liabilities SET is_deleted=1,updated_at=NOW(),updated_by=p_deleted_by WHERE id=p_liability_id AND tenant_id=p_tenant_id AND is_deleted=0;
END $$

DROP PROCEDURE IF EXISTS `sp_soft_delete_loan`$$
CREATE PROCEDURE `sp_soft_delete_loan`(
    IN  p_tenant_id  INT,
    IN  p_loan_id    BIGINT,
    IN  p_deleted_by INT
)
proc: BEGIN
    UPDATE fact_loans SET
        is_deleted = 1, updated_at = NOW(), updated_by = p_deleted_by
    WHERE id = p_loan_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END $$

DROP PROCEDURE IF EXISTS `sp_soft_delete_material`$$
CREATE PROCEDURE `sp_soft_delete_material`(IN p_tenant_id INT, IN p_material_id INT, IN p_deleted_by INT)
proc: BEGIN
    UPDATE inv_materials SET is_deleted=1,updated_at=NOW(),updated_by=p_deleted_by WHERE id=p_material_id AND tenant_id=p_tenant_id AND is_deleted=0;
END $$

DROP PROCEDURE IF EXISTS `sp_soft_delete_order`$$
CREATE PROCEDURE `sp_soft_delete_order`(
    IN  p_tenant_id  INT,
    IN  p_order_id   BIGINT,
    IN  p_deleted_by INT
)
proc: BEGIN
    UPDATE fact_orders SET
        is_deleted = 1,
        updated_at = NOW(),
        updated_by = p_deleted_by
    WHERE id = p_order_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END $$

DROP PROCEDURE IF EXISTS `sp_soft_delete_product`$$
CREATE PROCEDURE `sp_soft_delete_product`(
    IN  p_tenant_id  INT,
    IN  p_product_id INT,
    IN  p_deleted_by INT
)
proc: BEGIN
    UPDATE inv_products SET
        is_deleted = 1, updated_at = NOW(), updated_by = p_deleted_by
    WHERE id = p_product_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END $$

DROP PROCEDURE IF EXISTS `sp_soft_delete_purchase`$$
CREATE PROCEDURE `sp_soft_delete_purchase`(
    IN  p_tenant_id   INT,
    IN  p_purchase_id BIGINT,
    IN  p_deleted_by  INT
)
proc: BEGIN
    UPDATE fact_purchases SET
        is_deleted = 1, updated_at = NOW(), updated_by = p_deleted_by
    WHERE id = p_purchase_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END $$

DROP PROCEDURE IF EXISTS `sp_soft_delete_resource`$$
CREATE PROCEDURE `sp_soft_delete_resource`(IN p_tenant_id INT, IN p_resource_id INT, IN p_deleted_by INT)
proc: BEGIN
    UPDATE proj_resources SET is_deleted=1,updated_at=NOW(),updated_by=p_deleted_by WHERE id=p_resource_id AND tenant_id=p_tenant_id AND is_deleted=0;
END $$

DROP PROCEDURE IF EXISTS `sp_soft_delete_sprint`$$
CREATE PROCEDURE `sp_soft_delete_sprint`(IN p_tenant_id INT, IN p_sprint_id INT, IN p_deleted_by INT)
proc: BEGIN
    UPDATE proj_sprints SET is_deleted=1,updated_at=NOW(),updated_by=p_deleted_by WHERE id=p_sprint_id AND tenant_id=p_tenant_id AND is_deleted=0;
END $$

DROP PROCEDURE IF EXISTS `sp_soft_delete_supplier`$$
CREATE PROCEDURE `sp_soft_delete_supplier`(
    IN  p_tenant_id   INT,
    IN  p_supplier_id INT,
    IN  p_deleted_by  INT
)
proc: BEGIN
    UPDATE dim_suppliers SET
        is_deleted = 1, updated_at = NOW(), updated_by = p_deleted_by
    WHERE id = p_supplier_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END $$

DROP PROCEDURE IF EXISTS `sp_soft_delete_user`$$
CREATE PROCEDURE `sp_soft_delete_user`(
    IN p_tenant_id INT,
    IN p_user_id INT,
    IN p_deleted_by INT
)
BEGIN
    DECLARE v_role_id INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    SELECT role_id INTO v_role_id FROM users WHERE id = p_user_id;
    UPDATE users SET is_deleted = 1, is_enabled = b'0', updated_by = p_deleted_by WHERE id = p_user_id AND tenant_id = p_tenant_id;
    UPDATE user_api_keys SET is_deleted = 1 WHERE user_id = p_user_id AND tenant_id = p_tenant_id;
    DELETE FROM data_scopes WHERE tenant_id = p_tenant_id AND role_id = v_role_id;
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_suspend_tenant`$$
CREATE PROCEDURE `sp_suspend_tenant`(
    IN p_tenant_id INT,
    IN p_reason VARCHAR(500),
    IN p_suspend_until DATE,
    IN p_performed_by INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    UPDATE tenants SET status = 'Suspended', updated_by = p_performed_by WHERE id = p_tenant_id;
    UPDATE users SET is_enabled = b'0', status = 'Inactive', updated_by = p_performed_by
    WHERE tenant_id = p_tenant_id;

    INSERT INTO platform_audit_logs (tenant_id, action_type, performed_by, description)
    VALUES (
        p_tenant_id, 'SUSPEND_TENANT', p_performed_by,
        JSON_OBJECT('status', 'Suspended', 'reason', p_reason, 'suspendUntil', p_suspend_until)
    );

    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_terminate_employee`$$
CREATE PROCEDURE `sp_terminate_employee`(
    IN p_tenant_id INT,
    IN p_employee_id INT,
    IN p_termination_date DATE,
    IN p_reason VARCHAR(500),
    IN p_updated_by INT
)
BEGIN
    DECLARE v_user_id INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT user_id INTO v_user_id FROM dim_employees WHERE id = p_employee_id;

    UPDATE dim_employees
    SET status = 'Terminated',
        is_deleted = 1,
        termination_date = p_termination_date,
        termination_reason = p_reason,
        updated_by = p_updated_by
    WHERE id = p_employee_id AND tenant_id = p_tenant_id;

    UPDATE dim_wallets
    SET status = 'Frozen', updated_by = p_updated_by
    WHERE employee_id = p_employee_id AND tenant_id = p_tenant_id;

    IF v_user_id IS NOT NULL THEN
        UPDATE users SET is_enabled = b'0', is_deleted = 1, updated_by = p_updated_by
        WHERE id = v_user_id AND tenant_id = p_tenant_id;

        UPDATE user_api_keys SET is_deleted = 1 WHERE user_id = v_user_id;
    END IF;

    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_transfer_wallet`$$
CREATE PROCEDURE `sp_transfer_wallet`(
    IN p_tenant_id INT,
    IN p_from_wallet_id INT,
    IN p_to_wallet_id INT,
    IN p_amount DECIMAL(15,2),
    IN p_created_by INT
)
BEGIN
    DECLARE v_transaction_id BIGINT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    INSERT INTO fact_transactions (tenant_id, from_wallet_id, to_wallet_id, amount, transaction_type, status, created_by)
    VALUES (p_tenant_id, p_from_wallet_id, p_to_wallet_id, p_amount, 'Transfer', 'Completed', p_created_by);
    SET v_transaction_id = LAST_INSERT_ID();
    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by)
    VALUES (p_tenant_id, p_from_wallet_id, 'Debit', p_amount, 'Transfer', v_transaction_id, p_created_by);
    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by)
    VALUES (p_tenant_id, p_to_wallet_id, 'Credit', p_amount, 'Transfer', v_transaction_id, p_created_by);
    UPDATE dim_wallets SET balance = balance - p_amount WHERE id = p_from_wallet_id;
    UPDATE dim_wallets SET balance = balance + p_amount WHERE id = p_to_wallet_id;
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_update_attendance`$$
CREATE PROCEDURE `sp_update_attendance`(
    IN  p_tenant_id     INT,
    IN  p_attendance_id BIGINT,
    IN  p_employee_id   INT,
    IN  p_employee_name VARCHAR(255),
    IN  p_date          DATE,
    IN  p_status        VARCHAR(50),
    IN  p_hours         DECIMAL(5,2),
    IN  p_check_in      VARCHAR(20),
    IN  p_check_out     VARCHAR(20),
    IN  p_notes         TEXT,
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM fact_attendance
        WHERE id = p_attendance_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Attendance record not found';
    END IF;

    UPDATE fact_attendance SET
        employee_id   = COALESCE(p_employee_id, employee_id),
        employee_name = COALESCE(NULLIF(TRIM(COALESCE(p_employee_name, '')), ''), employee_name),
        date          = COALESCE(p_date, date),
        status        = COALESCE(p_status, status),
        hours_worked  = COALESCE(p_hours, hours_worked),
        check_in      = COALESCE(NULLIF(p_check_in, ''), check_in),
        check_out     = COALESCE(NULLIF(p_check_out, ''), check_out),
        notes         = COALESCE(p_notes, notes),
        updated_at    = NOW(),
        updated_by    = p_updated_by
    WHERE id = p_attendance_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END $$

DROP PROCEDURE IF EXISTS `sp_update_digital_asset`$$
CREATE PROCEDURE `sp_update_digital_asset`(
    IN  p_tenant_id   INT,
    IN  p_asset_id    INT,
    IN  p_name        VARCHAR(255),
    IN  p_category    VARCHAR(150),
    IN  p_vendor      VARCHAR(255),
    IN  p_cost        DECIMAL(15,2),
    IN  p_currency    VARCHAR(10),
    IN  p_status      VARCHAR(50),
    IN  p_expiry_date DATE,
    IN  p_assigned_to VARCHAR(255),
    IN  p_updated_by  INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM inv_digital_assets
        WHERE id = p_asset_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Digital asset not found';
    END IF;

    UPDATE inv_digital_assets SET
        name        = COALESCE(p_name, name),
        category    = COALESCE(p_category, category),
        vendor      = COALESCE(p_vendor, vendor),
        cost        = COALESCE(p_cost, cost),
        currency    = COALESCE(p_currency, currency),
        status      = COALESCE(p_status, status),
        expiry_date = COALESCE(p_expiry_date, expiry_date),
        assigned_to = COALESCE(p_assigned_to, assigned_to),
        updated_at  = NOW(),
        updated_by  = p_updated_by
    WHERE id = p_asset_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END $$

DROP PROCEDURE IF EXISTS `sp_update_liability`$$
CREATE PROCEDURE `sp_update_liability`(
    IN p_tenant_id INT, IN p_liability_id INT, IN p_name VARCHAR(255), IN p_type VARCHAR(50), IN p_counterparty VARCHAR(255),
    IN p_principal DECIMAL(15,2), IN p_outstanding DECIMAL(15,2), IN p_currency VARCHAR(10), IN p_interest_rate DECIMAL(6,3),
    IN p_start_date DATE, IN p_due_date DATE, IN p_status VARCHAR(50), IN p_notes TEXT, IN p_updated_by INT)
proc: BEGIN
    UPDATE fin_liabilities SET name=COALESCE(p_name,name),type=COALESCE(p_type,type),counterparty=COALESCE(p_counterparty,counterparty),
        principal=COALESCE(p_principal,principal),outstanding=COALESCE(p_outstanding,outstanding),currency=COALESCE(p_currency,currency),
        interest_rate=COALESCE(p_interest_rate,interest_rate),start_date=COALESCE(p_start_date,start_date),due_date=COALESCE(p_due_date,due_date),
        status=COALESCE(p_status,status),notes=COALESCE(p_notes,notes),updated_at=NOW(),updated_by=p_updated_by
    WHERE id=p_liability_id AND tenant_id=p_tenant_id AND is_deleted=0;
END $$

DROP PROCEDURE IF EXISTS `sp_update_loan`$$
CREATE PROCEDURE `sp_update_loan`(
    IN  p_tenant_id     INT,
    IN  p_loan_id       BIGINT,
    IN  p_employee_id   INT,
    IN  p_employee_name VARCHAR(255),
    IN  p_loan_amount   DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_installments  INT,
    IN  p_monthly       DECIMAL(15,2),
    IN  p_start_date    DATE,
    IN  p_status        VARCHAR(50),
    IN  p_purpose       TEXT,
    IN  p_notes         TEXT,
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM fact_loans
        WHERE id = p_loan_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Loan not found';
    END IF;

    UPDATE fact_loans SET
        employee_id        = COALESCE(p_employee_id, employee_id),
        employee_name      = COALESCE(NULLIF(TRIM(COALESCE(p_employee_name, '')), ''), employee_name),
        loan_amount        = COALESCE(p_loan_amount, loan_amount),
        currency           = COALESCE(p_currency, currency),
        no_of_installments = COALESCE(NULLIF(p_installments, 0), no_of_installments),
        monthly_amount     = COALESCE(p_monthly, monthly_amount),
        start_date         = COALESCE(p_start_date, start_date),
        status             = COALESCE(p_status, status),
        purpose            = COALESCE(p_purpose, purpose),
        notes              = COALESCE(p_notes, notes),
        updated_at         = NOW(),
        updated_by         = p_updated_by
    WHERE id = p_loan_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END $$

DROP PROCEDURE IF EXISTS `sp_update_material`$$
CREATE PROCEDURE `sp_update_material`(
    IN p_tenant_id INT, IN p_material_id INT, IN p_name VARCHAR(255), IN p_code VARCHAR(100), IN p_category VARCHAR(150),
    IN p_unit VARCHAR(50), IN p_stock_qty DECIMAL(15,2), IN p_unit_cost DECIMAL(15,2), IN p_currency VARCHAR(10),
    IN p_reorder_level DECIMAL(15,2), IN p_status VARCHAR(50), IN p_notes TEXT, IN p_updated_by INT)
proc: BEGIN
    UPDATE inv_materials SET name=COALESCE(p_name,name),code=COALESCE(p_code,code),category=COALESCE(p_category,category),
        unit=COALESCE(p_unit,unit),stock_qty=COALESCE(p_stock_qty,stock_qty),unit_cost=COALESCE(p_unit_cost,unit_cost),
        currency=COALESCE(p_currency,currency),reorder_level=COALESCE(p_reorder_level,reorder_level),status=COALESCE(p_status,status),
        notes=COALESCE(p_notes,notes),updated_at=NOW(),updated_by=p_updated_by
    WHERE id=p_material_id AND tenant_id=p_tenant_id AND is_deleted=0;
END $$

DROP PROCEDURE IF EXISTS `sp_update_opportunity_status`$$
CREATE PROCEDURE `sp_update_opportunity_status`(
    IN p_tenant_id INT,
    IN p_opportunity_id BIGINT,
    IN p_new_status VARCHAR(50),
    IN p_updated_by INT
)
BEGIN
    DECLARE v_old_status VARCHAR(50);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    SELECT status INTO v_old_status FROM fact_opportunities WHERE id = p_opportunity_id AND tenant_id = p_tenant_id;
    UPDATE fact_opportunities SET status = p_new_status, updated_by = p_updated_by WHERE id = p_opportunity_id AND tenant_id = p_tenant_id;
    INSERT INTO fact_opportunity_logs (tenant_id, opportunity_id, old_status, new_status, changed_by)
    VALUES (p_tenant_id, p_opportunity_id, v_old_status, p_new_status, p_updated_by);
    COMMIT;
END $$

DROP PROCEDURE IF EXISTS `sp_update_order`$$
CREATE PROCEDURE `sp_update_order`(
    IN  p_tenant_id     INT,
    IN  p_order_id      BIGINT,
    IN  p_order_no      VARCHAR(100),
    IN  p_client_name   VARCHAR(255),
    IN  p_status        VARCHAR(50),
    IN  p_total_amount  DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_no_of_items   INT,
    IN  p_order_date    DATE,
    IN  p_delivery_date DATE,
    IN  p_notes         TEXT,
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM fact_orders
        WHERE id = p_order_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Order not found';
    END IF;

    UPDATE fact_orders SET
        order_no      = COALESCE(p_order_no, order_no),
        client_name   = COALESCE(p_client_name, client_name),
        status        = COALESCE(p_status, status),
        total_amount  = COALESCE(p_total_amount, total_amount),
        currency      = COALESCE(p_currency, currency),
        no_of_items   = COALESCE(p_no_of_items, no_of_items),
        order_date    = COALESCE(p_order_date, order_date),
        delivery_date = COALESCE(p_delivery_date, delivery_date),
        notes         = COALESCE(p_notes, notes),
        updated_at    = NOW(),
        updated_by    = p_updated_by
    WHERE id = p_order_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END $$

DROP PROCEDURE IF EXISTS `sp_update_product`$$
CREATE PROCEDURE `sp_update_product`(
    IN  p_tenant_id     INT,
    IN  p_product_id    INT,
    IN  p_name          VARCHAR(255),
    IN  p_sku           VARCHAR(100),
    IN  p_category      VARCHAR(150),
    IN  p_unit_price    DECIMAL(15,2),
    IN  p_cost_price    DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_stock_qty     INT,
    IN  p_reorder_level INT,
    IN  p_unit          VARCHAR(50),
    IN  p_status        VARCHAR(50),
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM inv_products
        WHERE id = p_product_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Product not found';
    END IF;

    UPDATE inv_products SET
        name          = COALESCE(p_name, name),
        sku           = COALESCE(p_sku, sku),
        category      = COALESCE(p_category, category),
        unit_price    = COALESCE(p_unit_price, unit_price),
        cost_price    = COALESCE(p_cost_price, cost_price),
        currency      = COALESCE(p_currency, currency),
        stock_qty     = COALESCE(p_stock_qty, stock_qty),
        reorder_level = COALESCE(p_reorder_level, reorder_level),
        unit          = COALESCE(p_unit, unit),
        status        = COALESCE(p_status, status),
        updated_at    = NOW(),
        updated_by    = p_updated_by
    WHERE id = p_product_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END $$

DROP PROCEDURE IF EXISTS `sp_update_purchase`$$
CREATE PROCEDURE `sp_update_purchase`(
    IN  p_tenant_id     INT,
    IN  p_purchase_id   BIGINT,
    IN  p_supplier_id   INT,
    IN  p_wallet_id     INT,
    IN  p_is_capex      TINYINT,
    IN  p_amount        DECIMAL(15,2),
    IN  p_purchase_date DATE,
    IN  p_notes         TEXT,
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM fact_purchases
        WHERE id = p_purchase_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Purchase not found';
    END IF;

    UPDATE fact_purchases SET
        supplier_id            = COALESCE(p_supplier_id, supplier_id),
        wallet_id              = COALESCE(p_wallet_id, wallet_id),
        is_capital_expenditure = COALESCE(p_is_capex, is_capital_expenditure),
        amount                 = COALESCE(p_amount, amount),
        purchase_date          = COALESCE(p_purchase_date, purchase_date),
        notes                  = COALESCE(p_notes, notes),
        updated_at             = NOW(),
        updated_by             = p_updated_by
    WHERE id = p_purchase_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END $$

DROP PROCEDURE IF EXISTS `sp_update_resource`$$
CREATE PROCEDURE `sp_update_resource`(
    IN p_tenant_id INT, IN p_resource_id INT, IN p_name VARCHAR(255), IN p_type VARCHAR(50), IN p_project_id INT, IN p_role VARCHAR(150),
    IN p_allocation_pct INT, IN p_cost_rate DECIMAL(15,2), IN p_currency VARCHAR(10), IN p_availability VARCHAR(50),
    IN p_notes TEXT, IN p_updated_by INT)
proc: BEGIN
    UPDATE proj_resources SET name=COALESCE(p_name,name),type=COALESCE(p_type,type),project_id=COALESCE(p_project_id,project_id),
        role=COALESCE(p_role,role),allocation_pct=COALESCE(p_allocation_pct,allocation_pct),cost_rate=COALESCE(p_cost_rate,cost_rate),
        currency=COALESCE(p_currency,currency),availability=COALESCE(p_availability,availability),notes=COALESCE(p_notes,notes),
        updated_at=NOW(),updated_by=p_updated_by
    WHERE id=p_resource_id AND tenant_id=p_tenant_id AND is_deleted=0;
END $$

DROP PROCEDURE IF EXISTS `sp_update_sprint`$$
CREATE PROCEDURE `sp_update_sprint`(
    IN p_tenant_id INT, IN p_sprint_id INT, IN p_name VARCHAR(255), IN p_project_id INT, IN p_goal TEXT,
    IN p_start_date DATE, IN p_end_date DATE, IN p_status VARCHAR(50), IN p_capacity INT, IN p_updated_by INT)
proc: BEGIN
    UPDATE proj_sprints SET name=COALESCE(p_name,name),project_id=COALESCE(p_project_id,project_id),goal=COALESCE(p_goal,goal),
        start_date=COALESCE(p_start_date,start_date),end_date=COALESCE(p_end_date,end_date),status=COALESCE(p_status,status),
        capacity=COALESCE(p_capacity,capacity),updated_at=NOW(),updated_by=p_updated_by
    WHERE id=p_sprint_id AND tenant_id=p_tenant_id AND is_deleted=0;
END $$

DROP PROCEDURE IF EXISTS `sp_update_supplier`$$
CREATE PROCEDURE `sp_update_supplier`(
    IN  p_tenant_id   INT,
    IN  p_supplier_id INT,
    IN  p_vendor_name VARCHAR(255),
    IN  p_category    VARCHAR(100),
    IN  p_tax_id      VARCHAR(50),
    IN  p_updated_by  INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM dim_suppliers
        WHERE id = p_supplier_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Supplier not found';
    END IF;

    UPDATE dim_suppliers SET
        vendor_name = COALESCE(p_vendor_name, vendor_name),
        category    = COALESCE(p_category, category),
        tax_id      = COALESCE(p_tax_id, tax_id),
        updated_at  = NOW(),
        updated_by  = p_updated_by
    WHERE id = p_supplier_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END $$

DROP PROCEDURE IF EXISTS `sp_void_invoice`$$
CREATE PROCEDURE `sp_void_invoice`(IN p_tenant_id INT, IN p_invoice_id BIGINT, IN p_voided_by INT)
BEGIN
    DECLARE v_status VARCHAR(50);
    DECLARE v_amount DECIMAL(15,2);
    DECLARE v_wallet_id INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    
    SELECT status, (amount + tax_amount), wallet_id INTO v_status, v_amount, v_wallet_id 
    FROM fact_invoices WHERE id = p_invoice_id AND tenant_id = p_tenant_id;
    
    UPDATE fact_invoices SET status = 'Cancelled', updated_by = p_voided_by WHERE id = p_invoice_id AND tenant_id = p_tenant_id;
    
    -- If Paid previously, we must safely reverse the ledger impact
    IF v_status = 'Paid' THEN
        INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by)
        VALUES (p_tenant_id, v_wallet_id, 'Debit', v_amount, 'Void_Invoice', p_invoice_id, p_voided_by);
        
        -- Reverse the balance of the affected corporate wallet
        UPDATE dim_wallets SET balance = balance - v_amount WHERE id = v_wallet_id AND tenant_id = p_tenant_id;
    END IF;
    COMMIT;
END $$

DELIMITER ;
