<?php
/**
 * Payment Reminder Model
 *
 * @package Freelance_Studio
 */

class FS_Payment_Reminder {

    const TYPE_BEFORE_DUE = 'before_due';
    const TYPE_ON_DUE = 'on_due';
    const TYPE_OVERDUE_1 = 'overdue_1';
    const TYPE_OVERDUE_2 = 'overdue_2';
    const TYPE_OVERDUE_3 = 'overdue_3';

    const STATUS_PENDING = 'pending';
    const STATUS_SENT = 'sent';
    const STATUS_SKIPPED = 'skipped';
    const STATUS_CANCELLED = 'cancelled';

    /**
     * Get reminder type labels
     */
    public static function get_types() {
        return array(
            self::TYPE_BEFORE_DUE => __('Before Due Date', 'freelance-studio'),
            self::TYPE_ON_DUE => __('On Due Date', 'freelance-studio'),
            self::TYPE_OVERDUE_1 => __('Overdue (1st notice)', 'freelance-studio'),
            self::TYPE_OVERDUE_2 => __('Overdue (2nd notice)', 'freelance-studio'),
            self::TYPE_OVERDUE_3 => __('Overdue (3rd notice)', 'freelance-studio'),
        );
    }

    /**
     * Get a single reminder by ID
     */
    public static function get($id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_payment_reminders';
        
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE id = %d",
            $id
        ));
    }

    /**
     * Get reminders for an invoice
     */
    public static function get_by_invoice($invoice_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_payment_reminders';
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table} WHERE invoice_id = %d ORDER BY scheduled_date ASC",
            $invoice_id
        ));
    }

    /**
     * Get pending reminders due for sending
     */
    public static function get_due_reminders() {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_payment_reminders';
        $invoices_table = $wpdb->prefix . 'fs_invoices';
        $today = current_time('Y-m-d');
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT r.*, i.invoice_number, i.total, i.balance_due, i.client_id, i.due_date,
                    c.name as client_name, c.company_name, c.email as client_email
             FROM {$table} r
             JOIN {$invoices_table} i ON r.invoice_id = i.id
             JOIN {$wpdb->prefix}fs_clients c ON i.client_id = c.id
             WHERE r.status = 'pending'
               AND r.scheduled_date <= %s
               AND i.status NOT IN ('paid', 'cancelled')
             ORDER BY r.scheduled_date ASC",
            $today
        ));
    }

    /**
     * Create a new reminder
     */
    public static function create($data) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_payment_reminders';
        
        $result = $wpdb->insert(
            $table,
            array(
                'invoice_id' => intval($data['invoice_id']),
                'reminder_type' => sanitize_text_field($data['reminder_type']),
                'scheduled_date' => $data['scheduled_date'],
                'status' => isset($data['status']) ? sanitize_text_field($data['status']) : self::STATUS_PENDING,
                'custom_message' => isset($data['custom_message']) ? sanitize_textarea_field($data['custom_message']) : null,
            ),
            array('%d', '%s', '%s', '%s', '%s')
        );
        
        return $result ? $wpdb->insert_id : false;
    }

    /**
     * Update reminder status
     */
    public static function update_status($id, $status, $sent_at = null) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_payment_reminders';
        
        $data = array('status' => $status);
        $format = array('%s');
        
        if ($sent_at) {
            $data['sent_at'] = $sent_at;
            $format[] = '%s';
        }
        
        return $wpdb->update($table, $data, array('id' => $id), $format, array('%d'));
    }

    /**
     * Mark reminder as sent
     */
    public static function mark_sent($id) {
        return self::update_status($id, self::STATUS_SENT, current_time('mysql'));
    }

    /**
     * Delete reminder
     */
    public static function delete($id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_payment_reminders';
        
        return $wpdb->delete($table, array('id' => $id), array('%d'));
    }

    /**
     * Delete all reminders for an invoice
     */
    public static function delete_by_invoice($invoice_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_payment_reminders';
        
        return $wpdb->delete($table, array('invoice_id' => $invoice_id), array('%d'));
    }

    /**
     * Schedule reminders for an invoice
     */
    public static function schedule_for_invoice($invoice_id) {
        // Check if reminders are enabled
        if (!get_option('fs_reminder_enabled', '1')) {
            return false;
        }
        
        $invoice = FS_Invoice::get($invoice_id);
        if (!$invoice || !$invoice->due_date || $invoice->status === 'paid' || $invoice->status === 'cancelled') {
            return false;
        }
        
        // Clear any existing pending reminders
        self::cancel_pending_by_invoice($invoice_id);
        
        $due_date = strtotime($invoice->due_date);
        $today = strtotime(current_time('Y-m-d'));
        
        $reminders_created = array();
        
        // Before due date reminder
        $before_days = intval(get_option('fs_reminder_before_due', '3'));
        if ($before_days > 0) {
            $before_date = gmdate('Y-m-d', strtotime("-{$before_days} days", $due_date));
            if (strtotime($before_date) >= $today) {
                $id = self::create(array(
                    'invoice_id' => $invoice_id,
                    'reminder_type' => self::TYPE_BEFORE_DUE,
                    'scheduled_date' => $before_date,
                ));
                if ($id) $reminders_created[] = $id;
            }
        }
        
        // Overdue reminder 1
        $overdue_1_days = intval(get_option('fs_reminder_after_due_1', '1'));
        if ($overdue_1_days > 0) {
            $overdue_1_date = gmdate('Y-m-d', strtotime("+{$overdue_1_days} days", $due_date));
            $id = self::create(array(
                'invoice_id' => $invoice_id,
                'reminder_type' => self::TYPE_OVERDUE_1,
                'scheduled_date' => $overdue_1_date,
            ));
            if ($id) $reminders_created[] = $id;
        }
        
        // Overdue reminder 2
        $overdue_2_days = intval(get_option('fs_reminder_after_due_2', '7'));
        if ($overdue_2_days > 0) {
            $overdue_2_date = gmdate('Y-m-d', strtotime("+{$overdue_2_days} days", $due_date));
            $id = self::create(array(
                'invoice_id' => $invoice_id,
                'reminder_type' => self::TYPE_OVERDUE_2,
                'scheduled_date' => $overdue_2_date,
            ));
            if ($id) $reminders_created[] = $id;
        }
        
        // Overdue reminder 3
        $overdue_3_days = intval(get_option('fs_reminder_after_due_3', '14'));
        if ($overdue_3_days > 0) {
            $overdue_3_date = gmdate('Y-m-d', strtotime("+{$overdue_3_days} days", $due_date));
            $id = self::create(array(
                'invoice_id' => $invoice_id,
                'reminder_type' => self::TYPE_OVERDUE_3,
                'scheduled_date' => $overdue_3_date,
            ));
            if ($id) $reminders_created[] = $id;
        }
        
        return $reminders_created;
    }

    /**
     * Cancel all pending reminders for an invoice
     */
    public static function cancel_pending_by_invoice($invoice_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_payment_reminders';
        
        return $wpdb->update(
            $table,
            array('status' => self::STATUS_CANCELLED),
            array('invoice_id' => $invoice_id, 'status' => self::STATUS_PENDING),
            array('%s'),
            array('%d', '%s')
        );
    }

    /**
     * Get reminder history for an invoice
     */
    public static function get_history($invoice_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_payment_reminders';
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table} 
             WHERE invoice_id = %d AND status = 'sent'
             ORDER BY sent_at DESC",
            $invoice_id
        ));
    }

    /**
     * Count pending reminders
     */
    public static function count_pending() {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_payment_reminders';
        
        return (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$table} WHERE status = 'pending'"
        );
    }

    /**
     * Get upcoming reminders (next 7 days)
     */
    public static function get_upcoming($days = 7) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_payment_reminders';
        $invoices_table = $wpdb->prefix . 'fs_invoices';
        $clients_table = $wpdb->prefix . 'fs_clients';
        $today = current_time('Y-m-d');
        $end_date = gmdate('Y-m-d', strtotime("+{$days} days"));
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT r.*, i.invoice_number, i.total, i.balance_due,
                    c.name as client_name, c.company_name
             FROM {$table} r
             JOIN {$invoices_table} i ON r.invoice_id = i.id
             JOIN {$clients_table} c ON i.client_id = c.id
             WHERE r.status = 'pending'
               AND r.scheduled_date BETWEEN %s AND %s
               AND i.status NOT IN ('paid', 'cancelled')
             ORDER BY r.scheduled_date ASC",
            $today,
            $end_date
        ));
    }
}
