<?php
session_start();

if(!isset($_SESSION['user_id'])){

    header("Location: /login.php");
    exit;

}

include("../config/db.php");

/*
|--------------------------------------------------------------------------
| EXPORTAR CSV
|--------------------------------------------------------------------------
*/

if(isset($_GET['export'])){

    $type =
    $_GET['export'];

    header('Content-Type: text/csv; charset=utf-8');

    header(
        'Content-Disposition: attachment; filename='.$type.'_report.csv'
    );

    $output =
    fopen('php://output', 'w');

    /*
    |--------------------------------------------------------------------------
    | LEADS
    |--------------------------------------------------------------------------
    */

    if($type == 'leads'){

        fputcsv($output,[

            'ID',
            'Nombre',
            'Correo',
            'Telefono',
            'Campaña',
            'Estado',
            'Fecha'

        ]);

        $query =
        $conn->query("
        SELECT
            leads.*,
            campaigns.name AS campaign_name
        FROM leads

        LEFT JOIN campaigns
        ON campaigns.id = leads.campaign_id

        ORDER BY leads.id DESC
        ");

        while($row = $query->fetch_assoc()){

            fputcsv($output,[

                $row['id'] ?? '',
                $row['name'] ?? '',
                $row['email'] ?? '',
                $row['phone'] ?? '',
                $row['campaign_name'] ?? '',
                $row['status'] ?? '',
                $row['created_at'] ?? ''

            ]);

        }

    }

    /*
    |--------------------------------------------------------------------------
    | CONTRACTS
    |--------------------------------------------------------------------------
    */

    if($type == 'contracts'){

        fputcsv($output,[

            'ID',
            'Cliente',
            'Estado',
            'Valor',
            'Fecha'

        ]);

        $query =
        $conn->query("
        SELECT *
        FROM contracts
        ORDER BY id DESC
        ");

        while($row = $query->fetch_assoc()){

            fputcsv($output,[

                $row['id'] ?? '',
                $row['client_name'] ?? '',
                $row['status'] ?? '',
                $row['amount'] ?? '',
                $row['created_at'] ?? ''

            ]);

        }

    }

    /*
    |--------------------------------------------------------------------------
    | METRICAS
    |--------------------------------------------------------------------------
    */

    if($type == 'metrics'){

        $total_leads = 0;
        $total_contracts = 0;
        $total_users = 0;
        $total_campaigns = 0;
        $conversion = 0;

        $lead_q =
        $conn->query("
        SELECT COUNT(*) AS total
        FROM leads
        ");

        if($lead_q){

            $lead_data =
            $lead_q->fetch_assoc();

            $total_leads =
            intval($lead_data['total']);

        }

        $contract_q =
        $conn->query("
        SELECT COUNT(*) AS total
        FROM contracts
        ");

        if($contract_q){

            $contract_data =
            $contract_q->fetch_assoc();

            $total_contracts =
            intval($contract_data['total']);

        }

        $user_q =
        $conn->query("
        SELECT COUNT(*) AS total
        FROM users
        ");

        if($user_q){

            $user_data =
            $user_q->fetch_assoc();

            $total_users =
            intval($user_data['total']);

        }

        $campaign_q =
        $conn->query("
        SELECT COUNT(*) AS total
        FROM campaigns
        ");

        if($campaign_q){

            $campaign_data =
            $campaign_q->fetch_assoc();

            $total_campaigns =
            intval($campaign_data['total']);

        }

        if($total_leads > 0){

            $conversion =
            round(
                ($total_contracts / $total_leads) * 100,
                2
            );

        }

        fputcsv($output,['Métrica','Valor']);

        fputcsv($output,['Leads',$total_leads]);

        fputcsv($output,['Contratos',$total_contracts]);

        fputcsv($output,['Usuarios',$total_users]);

        fputcsv($output,['Campañas',$total_campaigns]);

        fputcsv($output,['Conversión',$conversion.'%']);

    }

    fclose($output);
    exit;

}

/*
|--------------------------------------------------------------------------
| KPIS
|--------------------------------------------------------------------------
*/

$total_leads = 0;
$total_contracts = 0;
$total_users = 0;
$total_campaigns = 0;
$conversion_rate = 0;

/*
|--------------------------------------------------------------------------
| LEADS
|--------------------------------------------------------------------------
*/

$lead_query =
$conn->query("
SELECT COUNT(*) AS total
FROM leads
");

if($lead_query){

    $lead_data =
    $lead_query->fetch_assoc();

    $total_leads =
    intval($lead_data['total']);

}

/*
|--------------------------------------------------------------------------
| CONTRACTS
|--------------------------------------------------------------------------
*/

$contract_query =
$conn->query("
SELECT COUNT(*) AS total
FROM contracts
");

if($contract_query){

    $contract_data =
    $contract_query->fetch_assoc();

    $total_contracts =
    intval($contract_data['total']);

}

/*
|--------------------------------------------------------------------------
| USERS
|--------------------------------------------------------------------------
*/

$user_query =
$conn->query("
SELECT COUNT(*) AS total
FROM users
");

if($user_query){

    $user_data =
    $user_query->fetch_assoc();

    $total_users =
    intval($user_data['total']);

}

/*
|--------------------------------------------------------------------------
| CAMPAIGNS
|--------------------------------------------------------------------------
*/

$campaign_query =
$conn->query("
SELECT COUNT(*) AS total
FROM campaigns
");

if($campaign_query){

    $campaign_data =
    $campaign_query->fetch_assoc();

    $total_campaigns =
    intval($campaign_data['total']);

}

/*
|--------------------------------------------------------------------------
| CONVERSION
|--------------------------------------------------------------------------
*/

if($total_leads > 0){

    $conversion_rate =
    round(
        ($total_contracts / $total_leads) * 100,
        1
    );

}

/*
|--------------------------------------------------------------------------
| PRODUCCION POR CAMPAÑA
|--------------------------------------------------------------------------
*/

$campaign_stats = [];

$campaigns_stats_query =
$conn->query("
SELECT
    campaigns.id,
    campaigns.name,
    COUNT(leads.id) AS total_leads
FROM campaigns

LEFT JOIN leads
ON leads.campaign_id = campaigns.id

GROUP BY campaigns.id

ORDER BY total_leads DESC
");

if($campaigns_stats_query){

    while($row = $campaigns_stats_query->fetch_assoc()){

        $campaign_stats[] = $row;

    }

}

/*
|--------------------------------------------------------------------------
| CONTRATOS RECIENTES
|--------------------------------------------------------------------------
*/

$recent_contracts =
$conn->query("
SELECT *
FROM contracts
ORDER BY id DESC
LIMIT 10
");

?>

<!DOCTYPE html>
<html lang="es">

<head>

<meta charset="UTF-8">

<title>
Reports
</title>

<link rel="stylesheet" href="/assets/css/style.css">

<style>

body{
    background:#f1f5f9;
}

.content-area{
    margin-left:260px;
    padding:40px;
}

.page-title{
    font-size:42px;
    font-weight:900;
    color:#02122c;
    margin-bottom:10px;
}

.page-subtitle{
    color:#64748b;
    font-size:16px;
    margin-bottom:35px;
}

.stats-grid{
    display:grid;
    grid-template-columns:
    repeat(auto-fit,minmax(260px,1fr));
    gap:22px;
    margin-bottom:30px;
}

.stat-card{
    background:#ffffff;
    border-radius:24px;
    padding:28px;
    box-shadow:
    0 10px 30px rgba(15,23,42,.05);
}

.stat-label{
    font-size:14px;
    color:#64748b;
    margin-bottom:12px;
    font-weight:700;
}

.stat-value{
    font-size:42px;
    font-weight:900;
    color:#02122c;
}

.white-card{
    background:#ffffff;
    border-radius:24px;
    padding:30px;
    margin-bottom:25px;
    box-shadow:
    0 10px 30px rgba(15,23,42,.05);
}

.section-title{
    font-size:24px;
    font-weight:800;
    color:#02122c;
    margin-bottom:25px;
}

.report-table{
    width:100%;
    border-collapse:collapse;
}

.report-table th{
    text-align:left;
    padding:16px;
    font-size:13px;
    text-transform:uppercase;
    color:#64748b;
    border-bottom:1px solid #e2e8f0;
}

.report-table td{
    padding:18px 16px;
    border-bottom:1px solid #e2e8f0;
    color:#02122c;
    font-size:14px;
}

.progress-bar{
    width:100%;
    height:12px;
    border-radius:999px;
    background:#e2e8f0;
    overflow:hidden;
}

.progress-fill{
    height:100%;
    background:
    linear-gradient(
        90deg,
        #5b4bff,
        #7b61ff
    );
    border-radius:999px;
}

.export-grid{
    display:grid;
    grid-template-columns:
    repeat(auto-fit,minmax(250px,1fr));
    gap:20px;
}

.export-card{
    background:#f8fafc;
    border-radius:20px;
    padding:24px;
}

.export-title{
    font-size:18px;
    font-weight:800;
    color:#02122c;
    margin-bottom:10px;
}

.export-text{
    color:#64748b;
    font-size:14px;
    margin-bottom:18px;
    line-height:1.5;
}

.export-btn{
    display:inline-flex;
    align-items:center;
    justify-content:center;
    height:48px;
    padding:0 20px;
    border-radius:14px;
    text-decoration:none;
    background:
    linear-gradient(
        90deg,
        #5b4bff,
        #7b61ff
    );
    color:#ffffff;
    font-weight:700;
}

.badge{
    display:inline-flex;
    align-items:center;
    justify-content:center;
    padding:8px 14px;
    border-radius:999px;
    background:#eef2ff;
    color:#4338ca;
    font-size:12px;
    font-weight:700;
}

.empty-box{
    color:#94a3b8;
    font-size:14px;
}

</style>

</head>

<body>

<?php include("../includes/sidebar.php"); ?>

<div class="content-area">

    <div class="page-title">
        Reports
    </div>

    <div class="page-subtitle">
        Métricas reales y rendimiento del CRM
    </div>

    <div class="stats-grid">

        <div class="stat-card">

            <div class="stat-label">
                Leads totales
            </div>

            <div class="stat-value">
                <?= $total_leads ?>
            </div>

        </div>

        <div class="stat-card">

            <div class="stat-label">
                Contratos cerrados
            </div>

            <div class="stat-value">
                <?= $total_contracts ?>
            </div>

        </div>

        <div class="stat-card">

            <div class="stat-label">
                Usuarios
            </div>

            <div class="stat-value">
                <?= $total_users ?>
            </div>

        </div>

        <div class="stat-card">

            <div class="stat-label">
                Conversión
            </div>

            <div class="stat-value">
                <?= $conversion_rate ?>%
            </div>

        </div>

    </div>

    <div class="white-card">

        <div class="section-title">
            Descargas
        </div>

        <div class="export-grid">

            <div class="export-card">

                <div class="export-title">
                    Exportar Leads
                </div>

                <div class="export-text">
                    Descarga todos los leads registrados.
                </div>

                <a
                    href="?export=leads"
                    class="export-btn"
                >
                    Descargar CSV
                </a>

            </div>

            <div class="export-card">

                <div class="export-title">
                    Exportar Contratos
                </div>

                <div class="export-text">
                    Descarga contratos reales del CRM.
                </div>

                <a
                    href="?export=contracts"
                    class="export-btn"
                >
                    Descargar CSV
                </a>

            </div>

            <div class="export-card">

                <div class="export-title">
                    Exportar Métricas
                </div>

                <div class="export-text">
                    Descarga KPIs y conversiones.
                </div>

                <a
                    href="?export=metrics"
                    class="export-btn"
                >
                    Descargar CSV
                </a>

            </div>

        </div>

    </div>

    <div class="white-card">

        <div class="section-title">
            Producción por campaña
        </div>

        <?php if(count($campaign_stats) > 0): ?>

            <table class="report-table">

                <thead>

                    <tr>

                        <th>Campaña</th>
                        <th>Leads</th>
                        <th>Progreso</th>

                    </tr>

                </thead>

                <tbody>

                    <?php foreach($campaign_stats as $campaign): ?>

                        <?php

                        $percentage = 0;

                        if($total_leads > 0){

                            $percentage =
                            round(
                                ($campaign['total_leads'] / $total_leads) * 100
                            );

                        }

                        ?>

                        <tr>

                            <td>
                                <?= htmlspecialchars($campaign['name']) ?>
                            </td>

                            <td>
                                <?= $campaign['total_leads'] ?>
                            </td>

                            <td style="width:40%;">

                                <div class="progress-bar">

                                    <div
                                        class="progress-fill"
                                        style="
                                        width:<?= $percentage ?>%;
                                        "
                                    ></div>

                                </div>

                            </td>

                        </tr>

                    <?php endforeach; ?>

                </tbody>

            </table>

        <?php else: ?>

            <div class="empty-box">
                No hay campañas registradas.
            </div>

        <?php endif; ?>

    </div>

    <div class="white-card">

        <div class="section-title">
            Contratos recientes
        </div>

        <?php if($recent_contracts && $recent_contracts->num_rows > 0): ?>

            <table class="report-table">

                <thead>

                    <tr>

                        <th>ID</th>
                        <th>Cliente</th>
                        <th>Estado</th>
                        <th>Fecha</th>

                    </tr>

                </thead>

                <tbody>

                    <?php while($contract = $recent_contracts->fetch_assoc()): ?>

                        <tr>

                            <td>
                                #<?= $contract['id'] ?>
                            </td>

                            <td>
                                <?= htmlspecialchars($contract['client_name'] ?? '-') ?>
                            </td>

                            <td>

                                <span class="badge">
                                    <?= htmlspecialchars($contract['status'] ?? '-') ?>
                                </span>

                            </td>

                            <td>
                                <?= htmlspecialchars($contract['created_at'] ?? '-') ?>
                            </td>

                        </tr>

                    <?php endwhile; ?>

                </tbody>

            </table>

        <?php else: ?>

            <div class="empty-box">
                No hay contratos registrados.
            </div>

        <?php endif; ?>

    </div>

</div>

</body>
</html>