#!/usr/bin/env python3 """ Master COGS Processing Orchestrator for orders_items_margin_detailed Runs all COGS update scripts in the correct sequence Execution Order: 1. Import external data (Bolt, ShipHaven, USPS) 2. Update supplier costs (all 9 suppliers) 3. Update payment processor fees (Bolt + Credit Card) 4. Update customer shipping costs (UPS ShipHaven + USPS Stamps) 5. Update commission and fees (Web, B2B, Amazon, eBay - requires .env file) 6. Fix multiplier bugs (216x and 27x bugs in buy prices) 7. Calculate final COGS This script can be run manually or via cron for continuous updates TARGET TABLE: orders_items_margin_detailed (NOT orders_items_margin) """ import subprocess import logging from datetime import datetime import os import sys # ============================================================================ # CONFIGURATION # ============================================================================ BASE_DIR = '/home/centralgoparts/public_html/profitability' LOG_DIR = os.path.join(BASE_DIR, 'logs') os.makedirs(LOG_DIR, exist_ok=True) # Scripts to run in order SCRIPTS = [ { 'name': 'Data Import', 'script': '2_import_all_data.py', 'description': 'Import Bolt, ShipHaven, and USPS data' }, { 'name': 'Supplier Costs', 'script': '3_update_supplier_costs.py', 'description': 'Update costs from 9 supplier tables' }, { 'name': 'Payment Processor Fees', 'script': '4_update_payment_processor_fees.py', 'description': 'Update Bolt and Credit Card fees' }, { 'name': 'Customer Shipping', 'script': '5_update_customer_shipping.py', 'description': 'Update UPS ShipHaven and USPS Stamps shipping costs' }, { 'name': 'Commission and Fees', 'script': '6_update_commission_and_fees.py', 'description': 'Update channel commission and ad fees from .env config' }, { 'name': 'Fix Incorrect Buy Prices', 'script': '8_fix_multiplier_bugs.py', 'description': 'Validate and fix incorrect our_buy_price calculations' }, { 'name': 'Final COGS Calculation', 'script': '7_calculate_final_cogs.py', 'description': 'Calculate final good_sold_cost using COGS formula' } ] logger = logging.getLogger(__name__) # ============================================================================ # ORCHESTRATOR FUNCTIONS # ============================================================================ def run_script(script_info): """ Run a single script and return results Args: script_info: Dictionary with name, script, description Returns: dict: Results with success status, output, error """ script_path = os.path.join(BASE_DIR, script_info['script']) logger.info("="*80) logger.info(f"Running: {script_info['name']}") logger.info(f"Script: {script_info['script']}") logger.info(f"Description: {script_info['description']}") logger.info("="*80) try: # Run the script result = subprocess.run( ['/usr/bin/python3', script_path], capture_output=True, text=True, timeout=600 # 10 minute timeout per script ) # Log the output if result.stdout: logger.info(f"\n{result.stdout}") if result.stderr: logger.warning(f"STDERR:\n{result.stderr}") success = result.returncode == 0 if success: logger.info(f"✓ {script_info['name']} completed successfully") else: logger.error(f"✗ {script_info['name']} failed with return code {result.returncode}") return { 'success': success, 'output': result.stdout, 'error': result.stderr, 'return_code': result.returncode } except subprocess.TimeoutExpired: error_msg = f"Script {script_info['script']} timed out after 10 minutes" logger.error(error_msg) return { 'success': False, 'output': '', 'error': error_msg, 'return_code': -1 } except Exception as e: error_msg = f"Error running {script_info['script']}: {str(e)}" logger.error(error_msg) return { 'success': False, 'output': '', 'error': error_msg, 'return_code': -1 } # ============================================================================ # MAIN EXECUTION # ============================================================================ def main(): """Main execution function""" start_time = datetime.now() logger.info("="*80) logger.info(f"COGS PROCESSING ORCHESTRATOR - {start_time.strftime('%Y-%m-%d %H:%M:%S')}") logger.info("TARGET TABLE: orders_items_margin_detailed") logger.info("="*80) logger.info(f"Running {len(SCRIPTS)} scripts in sequence...\n") results = {} total_success = 0 total_failed = 0 # Run each script in sequence for script_info in SCRIPTS: result = run_script(script_info) results[script_info['name']] = result if result['success']: total_success += 1 else: total_failed += 1 logger.error(f"\n⚠️ {script_info['name']} failed! Check logs for details.\n") # Don't stop on failure - continue with remaining scripts # Summary end_time = datetime.now() duration = end_time - start_time logger.info("\n" + "="*80) logger.info("ORCHESTRATOR SUMMARY") logger.info("="*80) logger.info(f"Start time: {start_time.strftime('%Y-%m-%d %H:%M:%S')}") logger.info(f"End time: {end_time.strftime('%Y-%m-%d %H:%M:%S')}") logger.info(f"Duration: {duration}") logger.info(f"\nScripts executed: {len(SCRIPTS)}") logger.info(f" Successful: {total_success}") logger.info(f" Failed: {total_failed}") logger.info("\nScript Results:") for script_info in SCRIPTS: result = results[script_info['name']] status = "✓ SUCCESS" if result['success'] else "✗ FAILED" logger.info(f" {status:12} - {script_info['name']}") logger.info("="*80 + "\n") # Exit with error code if any script failed if total_failed > 0: logger.warning(f"⚠️ {total_failed} script(s) failed. Review logs for details.") sys.exit(1) else: logger.info("✓ All scripts completed successfully!") sys.exit(0) if __name__ == "__main__": # Configure logging log_filename = os.path.join(LOG_DIR, f'orchestrator_{datetime.now().strftime("%Y%m%d_%H%M%S")}.log') logging.basicConfig( level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s', handlers=[ logging.FileHandler(log_filename), logging.StreamHandler() ] ) logger.info(f"Log file: {log_filename}\n") try: main() except KeyboardInterrupt: logger.warning("\n\n⚠️ Orchestrator interrupted by user") sys.exit(130) except Exception as e: logger.error(f"\n\n✗ Orchestrator failed with error: {e}") sys.exit(1)