<?php
require_once 'layouts/dbconn.php';

class DebitScheduleInitializer {
    private $conn;
    
    private const FREQUENCIES = [
        'MONTHLY' => 1,
        'QUARTERLY' => 3,
        'SEMI_ANNUAL' => 6,
        'ANNUAL' => 12
    ];
    
    public function __construct($conn) {
        $this->conn = $conn;
    }
    
    public function initializeSchedules() {
        $this->conn->begin_transaction();
        
        try {
            // Get all active corporates without schedules
            $sql = "SELECT c.*, ca.start_date, ca.end_date, ca.anniv
                    FROM corporate c
                    JOIN corp_anniversary ca ON c.corp_id = ca.corp_id
                    WHERE c.cancelled = 0 
                    AND NOT EXISTS (
                        SELECT 1 FROM debit_schedule ds 
                        WHERE ds.corp_id = c.corp_id
                        AND ds.status = 'PENDING'
                    )
                    AND ca.corp_status = '1'";
                    
            $result = $this->conn->query($sql);
            
            while ($corporate = $result->fetch_assoc()) {
                $this->createInitialSchedule($corporate);
            }
            
            $this->conn->commit();
            return true;
            
        } catch (Exception $e) {
            $this->conn->rollback();
            error_log("Failed to initialize schedules: " . $e->getMessage());
            throw $e;
        }
    }
    
    private function createInitialSchedule($corporate) {
        $frequency = $corporate['payment_frequency'];
        $months = self::FREQUENCIES[$frequency];
        
        // Calculate first period dates
        $start_date = $corporate['start_date'];
        $initial_amount = $this->calculateInitialAmount($corporate);
        
        // Create initial schedule
        $sql = "INSERT INTO debit_schedule (
            corp_id,
            frequency,
            next_debit_date,
            period_start,
            period_end,
            amount,
            status
        ) VALUES (?, ?, ?, ?, ?, ?, 'PENDING')";
        
        $end_date = date('Y-m-d', strtotime("$start_date +$months months -1 day"));
        $debit_date = date('Y-m-d', strtotime("$start_date -7 days")); // 7 days before period starts
        
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('sssssd',
            $corporate['corp_id'],
            $frequency,
            $debit_date,
            $start_date,
            $end_date,
            $initial_amount
        );
        
        if (!$stmt->execute()) {
            throw new Exception("Failed to create schedule for corporate {$corporate['corp_id']}: " . $stmt->error);
        }
        
        // Log initialization
        $this->logInitialization($corporate['corp_id'], $start_date, $end_date);
    }
    
    private function calculateInitialAmount($corporate) {
        // Get total annual premium from corporate benefits
        $sql = "SELECT SUM(r.premium * cg.lives) as total_premium
                FROM corp_groups cg
                JOIN insuring_rates r ON cg.benefit = r.benefit 
                    AND r.corp_id = cg.corp_id
                WHERE cg.corp_id = ?
                AND r.year = YEAR(CURDATE())";
                
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('s', $corporate['corp_id']);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        
        $annual_premium = $row['total_premium'] ?? 0;
        
        // Pro-rate based on frequency
        $frequency_months = self::FREQUENCIES[$corporate['payment_frequency']];
        return ($annual_premium / 12) * $frequency_months;
    }
    
    private function logInitialization($corp_id, $start_date, $end_date) {
        $sql = "INSERT INTO auto_debit_log (
            corp_id,
            status,
            message
        ) VALUES (?, 'SUCCESS', ?)";
        
        $message = "Initialized debit schedule for period $start_date to $end_date";
        
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('ss', $corp_id, $message);
        $stmt->execute();
    }
}

?>