<?php

class Sponsorship {
    private $db;

    const PPC_RATE = 0.50; // Cost per click
    const PPV_RATE = 0.05; // Cost per view

    public function __construct() {
        $this->db = new Database;
    }

    // --- Packages ---
    public function createPackage($data) {
        $this->db->query("INSERT INTO sponsorship_packages (name, type, price, duration_days, description, is_active) VALUES (:name, :type, :price, :duration, :desc, :active)");
        $this->db->bind(':name', $data['name']);
        $this->db->bind(':type', $data['type']);
        $this->db->bind(':price', $data['price']);
        $this->db->bind(':duration', $data['duration_days']);
        $this->db->bind(':desc', $data['description']);
        $this->db->bind(':active', $data['is_active']);
        return $this->db->execute();
    }

    public function getAllPackages($activeOnly = false) {
        $sql = "SELECT * FROM sponsorship_packages";
        if ($activeOnly) $sql .= " WHERE is_active = 1";
        $sql .= " ORDER BY price ASC";
        $this->db->query($sql);
        return $this->db->resultSet();
    }

    public function getPackageById($id) {
        $this->db->query("SELECT * FROM sponsorship_packages WHERE id = :id");
        $this->db->bind(':id', $id);
        return $this->db->single();
    }

    // --- Campaigns ---
    public function createCampaign($data) {
        $this->db->query("INSERT INTO sponsor_campaigns (user_id, package_id, entity_id, ad_content, ad_type, aggression, status, start_date, end_date) VALUES (:uid, :pid, :eid, :content, :type, :agg, 'pending', NOW(), :end_date)");
        $this->db->bind(':uid', $data['user_id']);
        $this->db->bind(':pid', $data['package_id']);
        $this->db->bind(':eid', $data['entity_id']);
        $this->db->bind(':content', $data['ad_content']);
        $this->db->bind(':type', $data['ad_type']);
        $this->db->bind(':agg', $data['aggression']);
        $this->db->bind(':end_date', $data['end_date']);
        return $this->db->execute();
    }

    public function createCustomCampaign($data) {
        $this->db->query("INSERT INTO sponsor_campaigns (user_id, package_id, name, ad_content, ad_type, payment_model, total_budget, remaining_budget, daily_budget, target_url, contact_number, status, start_date, end_date) 
                          VALUES (:uid, :pid, :name, :content, :type, :model, :total, :remaining, :daily, :url, :contact, 'active', NOW(), DATE_ADD(NOW(), INTERVAL 1 YEAR))");
        $this->db->bind(':uid', $data['user_id']);
        $this->db->bind(':pid', $data['package_id']);
        $this->db->bind(':name', $data['name']);
        $this->db->bind(':content', $data['ad_content']);
        $this->db->bind(':type', $data['ad_type']);
        $this->db->bind(':model', $data['payment_model']);
        $this->db->bind(':total', $data['total_budget']);
        $this->db->bind(':remaining', $data['total_budget']); // Start with full budget
        $this->db->bind(':daily', $data['daily_budget']);
        $this->db->bind(':url', $data['target_url']);
        $this->db->bind(':contact', $data['contact_number']);
        return $this->db->execute();
    }

    public function getCampaignsByUser($user_id) {
        $this->db->query("SELECT sc.*, sp.name as package_name, sp.type as package_type, sp.price as package_price, sp.duration_days as package_duration 
                          FROM sponsor_campaigns sc 
                          LEFT JOIN sponsorship_packages sp ON sc.package_id = sp.id 
                          WHERE sc.user_id = :uid ORDER BY sc.created_at DESC");
        $this->db->bind(':uid', $user_id);
        return $this->db->resultSet();
    }

    public function getCampaignById($id) {
        $this->db->query("SELECT * FROM sponsor_campaigns WHERE id = :id");
        $this->db->bind(':id', $id);
        return $this->db->single();
    }

    public function updateCampaign($id, $data) {
        $this->db->query("UPDATE sponsor_campaigns SET ad_content = :content, ad_type = :type, aggression = :agg WHERE id = :id");
        $this->db->bind(':content', $data['ad_content']);
        $this->db->bind(':type', $data['ad_type']);
        $this->db->bind(':agg', $data['aggression']);
        $this->db->bind(':id', $id);
        return $this->db->execute();
    }

    public function extendCampaign($id, $days) {
        $this->db->query("UPDATE sponsor_campaigns SET end_date = DATE_ADD(end_date, INTERVAL :days DAY) WHERE id = :id");
        $this->db->bind(':days', $days);
        $this->db->bind(':id', $id);
        return $this->db->execute();
    }

    // --- Display Logic ---
    public function getActiveLeagueSponsor($league_id) {
        $this->db->query("
            SELECT sc.id, sc.ad_content, sc.ad_type 
            FROM sponsor_campaigns sc
            JOIN sponsorship_packages sp ON sc.package_id = sp.id
            WHERE sp.type = 'league' 
            AND sc.entity_id = :lid 
            AND sc.status = 'active' 
            AND sc.end_date >= NOW()
            LIMIT 1
        ");
        $this->db->bind(':lid', $league_id);
        return $this->db->single();
    }

    public function getMatchSponsors($fixture_id) {
        $this->db->query("
            SELECT sc.id, sc.ad_content, sc.ad_type 
            FROM sponsor_campaigns sc
            JOIN sponsorship_packages sp ON sc.package_id = sp.id
            WHERE sp.type = 'match' 
            AND sc.entity_id = :fid 
            AND sc.status = 'active' 
            AND sc.end_date >= NOW()
        ");
        $this->db->bind(':fid', $fixture_id);
        return $this->db->resultSet();
    }

    public function getActiveAds($limit = 5) {
        $this->db->query("
            SELECT sc.* 
            FROM sponsor_campaigns sc
            WHERE (sc.package_id = 0 OR sc.package_id IN (SELECT id FROM sponsorship_packages WHERE type = 'ad'))
            AND sc.status = 'active' AND sc.end_date >= NOW() AND sc.remaining_budget >= 0
            ORDER BY RAND() LIMIT :limit
        ");
        $this->db->bind(':limit', $limit);
        return $this->db->resultSet();
    }

    public function incrementViews($id) {
        // Check campaign type and deduct budget if PPV
        $camp = $this->getCampaignById($id);
        if ($camp && $camp->payment_model === 'ppv') {
            if ($camp->remaining_budget < self::PPV_RATE) {
                $this->updateCampaignStatus($id, 'completed'); // Budget exhausted
                return false;
            }
            $this->db->query("UPDATE sponsor_campaigns SET remaining_budget = remaining_budget - :rate WHERE id = :id");
            $this->db->bind(':rate', self::PPV_RATE);
            $this->db->bind(':id', $id);
            $this->db->execute();
        }

        // Update total
        $this->db->query("UPDATE sponsor_campaigns SET views = views + 1 WHERE id = :id");
        $this->db->bind(':id', $id);
        $res = $this->db->execute();

        // Update daily analytics
        try {
            $this->db->query("INSERT INTO sponsor_campaign_analytics (campaign_id, date, views) VALUES (:id, CURDATE(), 1) ON DUPLICATE KEY UPDATE views = views + 1");
            $this->db->bind(':id', $id);
            $this->db->execute();
        } catch (Exception $e) {
            // Table might not exist yet or other error, ignore to not break the main flow
        }
        return $res;
    }

    public function incrementClicks($id) {
        // Check campaign type and deduct budget if PPC
        $camp = $this->getCampaignById($id);
        if ($camp && $camp->payment_model === 'ppc') {
            if ($camp->remaining_budget < self::PPC_RATE) {
                $this->updateCampaignStatus($id, 'completed');
                return false;
            }
            $this->db->query("UPDATE sponsor_campaigns SET remaining_budget = remaining_budget - :rate WHERE id = :id");
            $this->db->bind(':rate', self::PPC_RATE);
            $this->db->bind(':id', $id);
            $this->db->execute();
        }

        // Update total
        $this->db->query("UPDATE sponsor_campaigns SET clicks = clicks + 1 WHERE id = :id");
        $this->db->bind(':id', $id);
        $res = $this->db->execute();

        // Update daily analytics
        try {
            $this->db->query("INSERT INTO sponsor_campaign_analytics (campaign_id, date, clicks) VALUES (:id, CURDATE(), 1) ON DUPLICATE KEY UPDATE clicks = clicks + 1");
            $this->db->bind(':id', $id);
            $this->db->execute();
        } catch (Exception $e) {
            // Ignore
        }
        return $res;
    }

    public function getAnalyticsForUser($user_id) {
        $this->db->query("
            SELECT a.date, SUM(a.views) as total_views, SUM(a.clicks) as total_clicks
            FROM sponsor_campaign_analytics a
            JOIN sponsor_campaigns c ON a.campaign_id = c.id
            WHERE c.user_id = :uid
            GROUP BY a.date
            ORDER BY a.date ASC
            LIMIT 30
        ");
        $this->db->bind(':uid', $user_id);
        return $this->db->resultSet();
    }

    public function getAllCampaigns() {
        $this->db->query("
            SELECT sc.*, sp.name as package_name, sp.type as package_type, 
                   u.first_name, u.last_name, u.email
            FROM sponsor_campaigns sc 
            LEFT JOIN sponsorship_packages sp ON sc.package_id = sp.id 
            JOIN users u ON sc.user_id = u.id
            ORDER BY sc.created_at DESC
        ");
        return $this->db->resultSet();
    }

    /**
     * Gets all campaigns for review if their budget is running low.
     */
    public function getCampaignsRunningLow() {
        $this->db->query("SELECT
            sc.id AS campaign_id,
            sc.user_id,
            sp.name AS package_name,
            sp.price AS package_price,
            w.balance AS wallet_balance,
            sc.last_notified_at
        FROM
            sponsor_campaigns sc
        JOIN sponsorship_packages sp ON sc.package_id = sp.id
        JOIN wallets w ON sc.user_id = w.entity_id AND w.entity_type = 'user'
        WHERE
            sc.status = 'active'
            AND sp.price > 0                    
            AND w.balance < (sp.price * 1)
            AND sc.end_date >= NOW()
        ");
        return $this->db->resultSet();
    }

    public function updateLowBudgetNotification($campaign_id) {
        $this->db->query("UPDATE sponsor_campaigns SET last_notified_at = NOW() WHERE id = :id");
        $this->db->bind(':id', $campaign_id);
        return $this->db->execute();
    }

    public function updateCampaignStatus($id, $status) {
        $this->db->query("UPDATE sponsor_campaigns SET status = :status WHERE id = :id");
        $this->db->bind(':status', $status);
        $this->db->bind(':id', $id);
        return $this->db->execute();
    }

    public function updateCampaignStatusByUser($id, $user_id, $status) {
        $this->db->query("UPDATE sponsor_campaigns SET status = :status WHERE id = :id AND user_id = :uid");
        $this->db->bind(':status', $status);
        $this->db->bind(':id', $id);
        $this->db->bind(':uid', $user_id);
        return $this->db->execute();
    }

    public function getSystemAnalytics() {
        try {
            $this->db->query("
                SELECT a.date, SUM(a.views) as total_views, SUM(a.clicks) as total_clicks
                FROM sponsor_campaign_analytics a
                GROUP BY a.date
                ORDER BY a.date ASC
                LIMIT 30
            ");
            return $this->db->resultSet();
        } catch (Exception $e) {
            return [];
        }
    }

    public function getTotalRevenue() {
        $this->db->query("
            SELECT SUM(amount) as total 
            FROM wallet_transactions 
            WHERE type = 'payment' 
            AND (description LIKE 'Sponsorship:%' OR description LIKE 'Campaign Extension:%')
        ");
        $row = $this->db->single();
        return $row ? (float)$row->total : 0.00;
    }

    public function getSponsorProfileByUserId($user_id) {
        $this->db->query("SELECT * FROM sponsor_profiles WHERE user_id = :uid");
        $this->db->bind(':uid', $user_id);
        return $this->db->single();
    }

    public function createOrUpdateSponsorProfile($data) {
        $this->db->query("INSERT INTO sponsor_profiles (user_id, business_name, business_description, business_logo) 
                          VALUES (:uid, :name, :desc, :logo) 
                          ON DUPLICATE KEY UPDATE 
                          business_name = VALUES(business_name), 
                          business_description = VALUES(business_description), 
                          business_logo = VALUES(business_logo)");
        $this->db->bind(':uid', $data['user_id']);
        $this->db->bind(':name', $data['business_name']);
        $this->db->bind(':desc', $data['business_description']);
        $this->db->bind(':logo', $data['business_logo']);
        return $this->db->execute();
    }
}