<?php
require __DIR__ . '/../src/auth.php';
require_login();
require __DIR__ . '/../src/db.php';

/*
|--------------------------------------------------------------------------
| Force Download as TAB-separated TEXT
|--------------------------------------------------------------------------
*/
header('Content-Type: text/plain; charset=utf-8');
header('Content-Disposition: attachment; filename="full_export_' . date('Y-m-d_Hi') . '.txt"');

/*
|--------------------------------------------------------------------------
| Helper: TAB-separated output (NO DATA LOSS)
|--------------------------------------------------------------------------
*/
function printRow(array $cols)
{
    foreach ($cols as &$c) {
        $c = str_replace(["\r", "\n"], ' ', (string)$c);
    }
    echo implode("\t", $cols) . "\n";
}

/*
|--------------------------------------------------------------------------
| Get Providers (serial order)
|--------------------------------------------------------------------------
*/
$stmt = $pdo->query("
    SELECT service_provider, MIN(id) AS first_seen_id
    FROM raw_reports
    WHERE service_provider IS NOT NULL AND service_provider != ''
    GROUP BY service_provider
    ORDER BY first_seen_id ASC
");
$providers = $stmt->fetchAll(PDO::FETCH_COLUMN);

/*
|--------------------------------------------------------------------------
| Generate Export (ONLY IF DATA EXISTS)
|--------------------------------------------------------------------------
*/
foreach ($providers as $prov) {

    $hasProviderData = false;
    $buffer = [];

    /* --------------------------------------------------
       PMS UPDATE BACKLOG
    -------------------------------------------------- */
    $stmt = $pdo->prepare("
        SELECT t.*
        FROM pms_backlog t
        JOIN raw_reports r ON t.report_id = r.id
        WHERE r.service_provider = ?
        ORDER BY t.id ASC
    ");
    $stmt->execute([$prov]);
    $pms = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if ($pms) {
        $hasProviderData = true;
        $buffer[] = ["PMS Update Backlog - Properties "];
        $buffer[] = ["Prop ID", "Property Name", "Count"];

        foreach ($pms as $r) {
            $buffer[] = [
                $r['property_id'],
                $r['prop_name'],
                $r['count_value']
            ];
        }
        $buffer[] = [""];
    }

    /* --------------------------------------------------
       OFFLINE RT1000
    -------------------------------------------------- */
    $stmt = $pdo->prepare("
        SELECT t.*
        FROM offline_rt1000 t
        JOIN raw_reports r ON t.report_id = r.id
        WHERE r.service_provider = ?
        ORDER BY t.id ASC
    ");
    $stmt->execute([$prov]);
    $offline = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if ($offline) {
        $hasProviderData = true;
        $buffer[] = ["Offline RT1000 Properties (at least for 10 Minutes) "];
        $buffer[] = ["Prop ID", "Property Name", "RT Name"];

        foreach ($offline as $r) {
            $buffer[] = [
                $r['property_id'],
                $r['prop_name'],
                $r['rt_name']
            ];
        }
        $buffer[] = [""];
    }

    /* --------------------------------------------------
       WAKEUP ERRORS
    -------------------------------------------------- */
    $stmt = $pdo->prepare("
        SELECT t.*
        FROM wakeup_errors t
        JOIN raw_reports r ON t.report_id = r.id
        WHERE r.service_provider = ?
        ORDER BY t.id ASC
    ");
    $stmt->execute([$prov]);
    $wakeup = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if ($wakeup) {
        $hasProviderData = true;
        $buffer[] = ["Wakeup Errors"];
        $buffer[] = [
            "Prop ID", "Property Name", "Room", "Attempts",
            "Schedule Time", "Wakeup Time", "Status",
            "Room Status", "PBX Type"
        ];

        foreach ($wakeup as $r) {
            $buffer[] = [
                $r['property_id'],
                $r['prop_name'],
                $r['room_number'],
                $r['attempts'],
                $r['schedule_time'],
                $r['wakeup_time'],
                $r['status_text'],
                $r['room_status'],
                $r['pbx_type']
            ];
        }
        $buffer[] = [""];
    }

    /* --------------------------------------------------
       CDR STATUS
    -------------------------------------------------- */
    $stmt = $pdo->prepare("
        SELECT t.*
        FROM cdr_status t
        JOIN raw_reports r ON t.report_id = r.id
        WHERE r.service_provider = ?
        ORDER BY t.id ASC
    ");
    $stmt->execute([$prov]);
    $cdr = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if ($cdr) {
        $hasProviderData = true;
        $buffer[] = ["CDR Status"];
        $buffer[] = [
            "Prop ID", "Property Name", "PBX Type",
            "PBX Number", "Raw Seq",
            "Enterprise Code", "Group Code",
            "Last Posted On"
        ];

        foreach ($cdr as $r) {
            $buffer[] = [
                $r['property_id'],
                $r['prop_name'],
                $r['pbx_type'],
                $r['pbx_number'],
                $r['pbx_raw_seq'],
                $r['enterprise_code'],
                $r['group_code'],
                $r['last_posted_on']
            ];
        }
        $buffer[] = [""];
    }

    /* --------------------------------------------------
       OUTPUT PROVIDER ONLY IF ANY DATA EXISTS
    -------------------------------------------------- */
    if ($hasProviderData) {
        printRow(["================================================================="]);
        echo "\n";
        printRow([$prov]);
        echo "\n";
        

        foreach ($buffer as $row) {
            printRow($row);
        }

        printRow([""]);
        printRow([""]);
    }
}

exit;
