ROI 자동 계산 시스템

// =============================================
// 마케팅 비용 추적 시스템 - 완전 구현
// =============================================

class Marketing_Cost_Tracker {
    private $table_name;

    public function __construct() {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'marketing_costs';
    }

    // 데이터베이스 테이블 생성
    public function activate() {
        global $wpdb;
        $charset = $wpdb->get_charset_collate();
        $sql = "CREATE TABLE IF NOT EXISTS {$this->table_name} (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            channel VARCHAR(50) NOT NULL,
            sub_channel VARCHAR(100) DEFAULT '',
            campaign VARCHAR(255) DEFAULT '',
            amount DECIMAL(12,2) NOT NULL DEFAULT 0,
            currency VARCHAR(3) DEFAULT 'KRW',
            cost_date DATE NOT NULL,
            cost_type ENUM('advertising','content','tool','labor','other') DEFAULT 'advertising',
            notes TEXT DEFAULT '',
            auto_imported TINYINT(1) DEFAULT 0,
            source VARCHAR(50) DEFAULT 'manual',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_channel_date (channel, cost_date),
            INDEX idx_date (cost_date),
            INDEX idx_campaign (campaign(100))
        ) {$charset};";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }

    // 비용 기록 (단건)
    public function record_cost($data) {
        global $wpdb;
        return $wpdb->insert($this->table_name, array(
            'channel'       => sanitize_text_field($data['channel']),
            'sub_channel'   => sanitize_text_field($data['sub_channel'] ?? ''),
            'campaign'      => sanitize_text_field($data['campaign'] ?? ''),
            'amount'        => (float)$data['amount'],
            'currency'      => $data['currency'] ?? 'KRW',
            'cost_date'     => $data['date'],
            'cost_type'     => $data['cost_type'] ?? 'advertising',
            'notes'         => sanitize_textarea_field($data['notes'] ?? ''),
            'auto_imported' => (int)($data['auto_imported'] ?? 0),
            'source'        => sanitize_text_field($data['source'] ?? 'manual'),
        ));
    }

    // 채널별 비용 합계
    public function get_costs_by_channel($date_from, $date_to) {
        global $wpdb;
        return $wpdb->get_results($wpdb->prepare(
            "SELECT channel,
                    SUM(amount) as total_cost,
                    COUNT(*) as entries,
                    COUNT(DISTINCT campaign) as campaigns,
                    MIN(cost_date) as first_cost,
                    MAX(cost_date) as last_cost
             FROM {$this->table_name}
             WHERE cost_date BETWEEN %s AND %s
             GROUP BY channel
             ORDER BY total_cost DESC",
            $date_from, $date_to
        ), ARRAY_A);
    }

    // 캠페인별 비용 상세
    public function get_costs_by_campaign($date_from, $date_to, $channel = null) {
        global $wpdb;
        $where = "cost_date BETWEEN %s AND %s";
        $params = array($date_from, $date_to);

        if ($channel) {
            $where .= " AND channel = %s";
            $params[] = $channel;
        }

        return $wpdb->get_results($wpdb->prepare(
            "SELECT channel, campaign,
                    SUM(amount) as total_cost,
                    COUNT(DISTINCT cost_date) as days_active
             FROM {$this->table_name}
             WHERE {$where}
             AND campaign != ''
             GROUP BY channel, campaign
             ORDER BY total_cost DESC",
            ...$params
        ), ARRAY_A);
    }

    // 일별 비용 추이
    public function get_daily_costs($date_from, $date_to) {
        global $wpdb;
        return $wpdb->get_results($wpdb->prepare(
            "SELECT cost_date, channel, SUM(amount) as daily_cost
             FROM {$this->table_name}
             WHERE cost_date BETWEEN %s AND %s
             GROUP BY cost_date, channel
             ORDER BY cost_date ASC",
            $date_from, $date_to
        ), ARRAY_A);
    }

    // 총 비용 합계
    public function get_total_cost($date_from, $date_to) {
        global $wpdb;
        return (float)$wpdb->get_var($wpdb->prepare(
            "SELECT COALESCE(SUM(amount), 0) FROM {$this->table_name}
             WHERE cost_date BETWEEN %s AND %s",
            $date_from, $date_to
        ));
    }
}

// Make.com 연동: 광고 비용 자동 가져오기 REST API
add_action('rest_api_init', function() {
    // Google Ads 비용 임포트
    register_rest_route('roi/v1', '/import/google-ads', array(
        'methods' => 'POST',
        'callback' => function($req) {
            $data = $req->get_json_params();
            $tracker = new Marketing_Cost_Tracker();

            $imported = 0;
            foreach ($data['campaigns'] ?? array() as $campaign) {
                $tracker->record_cost(array(
                    'channel'       => 'google_ads',
                    'sub_channel'   => $campaign['network'] ?? 'search',
                    'campaign'      => $campaign['name'],
                    'amount'        => $campaign['cost'],
                    'date'          => $campaign['date'],
                    'auto_imported' => 1,
                    'source'        => 'make_google_ads',
                ));
                $imported++;
            }

            return new WP_REST_Response(array('imported' => $imported));
        },
        'permission_callback' => function($req) {
            return $req->get_header('X-API-Key') === get_option('dashboard_api_key');
        },
    ));

    // Meta(Facebook/Instagram) Ads 비용 임포트
    register_rest_route('roi/v1', '/import/meta-ads', array(
        'methods' => 'POST',
        'callback' => function($req) {
            $data = $req->get_json_params();
            $tracker = new Marketing_Cost_Tracker();

            $imported = 0;
            foreach ($data['campaigns'] ?? array() as $campaign) {
                $tracker->record_cost(array(
                    'channel'       => 'meta_ads',
                    'sub_channel'   => $campaign['platform'] ?? 'facebook',
                    'campaign'      => $campaign['name'],
                    'amount'        => $campaign['spend'],
                    'date'          => $campaign['date'],
                    'auto_imported' => 1,
                    'source'        => 'make_meta_ads',
                ));
                $imported++;
            }

            return new WP_REST_Response(array('imported' => $imported));
        },
        'permission_callback' => function($req) {
            return $req->get_header('X-API-Key') === get_option('dashboard_api_key');
        },
    ));

    // 수동 비용 입력
    register_rest_route('roi/v1', '/costs', array(
        'methods' => 'POST',
        'callback' => function($req) {
            $data = $req->get_json_params();
            $tracker = new Marketing_Cost_Tracker();
            $tracker->record_cost($data);
            return new WP_REST_Response(array('status' => 'recorded'));
        },
        'permission_callback' => function() { return current_user_can('manage_options'); },
    ));
});