<?php
// /edenrms/pages/targets.php
declare(strict_types=1);

if (session_status() !== PHP_SESSION_ACTIVE) {
    session_start();
}

require_once __DIR__ . '/../includes/auth.php';

ec_require_login();

if (!(ec_can('reports.view') || ec_can('targets.manage') || ec_role() === 'super_admin')) {
    header('Location: /edenrms/pages/403.php');
    exit;
}

$db = ec_db();

if (!$db instanceof mysqli) {
    die('DB connection failed');
}

$ACTIVE = 'targets';

/* -------------------------------------------------------
   Helpers
------------------------------------------------------- */
if (!function_exists('h')) {
    function h($v): string {
        return htmlspecialchars((string)$v, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
    }
}

function tgt_table_exists(mysqli $db, string $table): bool {
    $table = $db->real_escape_string($table);
    $rs = $db->query("SHOW TABLES LIKE '{$table}'");
    return $rs && $rs->num_rows > 0;
}

function tgt_col_exists(mysqli $db, string $table, string $col): bool {
    $table = $db->real_escape_string($table);
    $col = $db->real_escape_string($col);
    $rs = $db->query("SHOW COLUMNS FROM `{$table}` LIKE '{$col}'");
    return $rs && $rs->num_rows > 0;
}

function tgt_first_col(mysqli $db, string $table, array $cols, string $fallback = ''): string {
    foreach ($cols as $c) {
        if (tgt_col_exists($db, $table, $c)) {
            return $c;
        }
    }
    return $fallback;
}

function tgt_add_col(mysqli $db, string $table, string $col, string $definition): void {
    if (!tgt_col_exists($db, $table, $col)) {
        @$db->query("ALTER TABLE `$table` ADD COLUMN $definition");
    }
}

function tgt_csrf(): string {
    if (empty($_SESSION['targets_csrf'])) {
        $_SESSION['targets_csrf'] = bin2hex(random_bytes(32));
    }
    return (string)$_SESSION['targets_csrf'];
}

function tgt_flash(string $type, string $msg): void {
    $_SESSION['targets_flash'] = [
        'type' => $type,
        'msg'  => $msg,
    ];
}

function tgt_user_id(): int {
    $u = function_exists('ec_user') ? ec_user() : [];
    return (int)($u['id'] ?? ($_SESSION['user']['id'] ?? 0));
}

function tgt_redirect_after_save(string $targetType, string $targetYear, string $targetMonth, int $branchId = 0, int $courseId = 0): void {
    $qs = [];

    if ($targetType === 'annual') {
        $qs[] = 'type=annual';
        $qs[] = 'year=' . urlencode($targetYear);
    } else {
        $qs[] = 'type=monthly';
        $qs[] = 'month=' . urlencode($targetMonth);
    }

    if ($branchId > 0) {
        $qs[] = 'branch_id=' . $branchId;
    }

    if ($courseId > 0) {
        $qs[] = 'course_id=' . $courseId;
    }

    header('Location: targets.php?' . implode('&', $qs));
    exit;
}

/* -------------------------------------------------------
   Create / migrate table
------------------------------------------------------- */
$db->query("
    CREATE TABLE IF NOT EXISTS revenue_targets (
        id INT AUTO_INCREMENT PRIMARY KEY,
        branch_id INT NULL,
        course_id INT NULL,
        target_type ENUM('monthly','annual') NOT NULL DEFAULT 'monthly',
        target_year CHAR(4) NULL,
        target_from_month CHAR(7) NULL,
        target_to_month CHAR(7) NULL,
        target_month CHAR(7) NULL,
        target_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
        notes TEXT NULL,
        created_by INT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
        INDEX (target_month),
        INDEX (target_year),
        INDEX (target_type),
        INDEX (branch_id),
        INDEX (course_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");

tgt_add_col($db, 'revenue_targets', 'course_id', 'course_id INT NULL AFTER branch_id');
tgt_add_col($db, 'revenue_targets', 'target_type', "target_type ENUM('monthly','annual') NOT NULL DEFAULT 'monthly' AFTER course_id");
tgt_add_col($db, 'revenue_targets', 'target_year', 'target_year CHAR(4) NULL AFTER target_type');
tgt_add_col($db, 'revenue_targets', 'target_from_month', 'target_from_month CHAR(7) NULL AFTER target_year');
tgt_add_col($db, 'revenue_targets', 'target_to_month', 'target_to_month CHAR(7) NULL AFTER target_from_month');
tgt_add_col($db, 'revenue_targets', 'target_month', 'target_month CHAR(7) NULL AFTER target_to_month');
tgt_add_col($db, 'revenue_targets', 'target_amount', 'target_amount DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER target_month');
tgt_add_col($db, 'revenue_targets', 'notes', 'notes TEXT NULL AFTER target_amount');
tgt_add_col($db, 'revenue_targets', 'created_by', 'created_by INT NULL AFTER notes');
tgt_add_col($db, 'revenue_targets', 'created_at', 'created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER created_by');
tgt_add_col($db, 'revenue_targets', 'updated_at', 'updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP AFTER created_at');

@$db->query("ALTER TABLE revenue_targets ADD INDEX idx_rt_target_month (target_month)");
@$db->query("ALTER TABLE revenue_targets ADD INDEX idx_rt_target_year (target_year)");
@$db->query("ALTER TABLE revenue_targets ADD INDEX idx_rt_target_type (target_type)");
@$db->query("ALTER TABLE revenue_targets ADD INDEX idx_rt_branch_id (branch_id)");
@$db->query("ALTER TABLE revenue_targets ADD INDEX idx_rt_course_id (course_id)");

if (tgt_col_exists($db, 'revenue_targets', 'y') && tgt_col_exists($db, 'revenue_targets', 'm')) {
    $targetAmountExpr = tgt_col_exists($db, 'revenue_targets', 'target_lkr') ? 'target_lkr' : 'target_amount';

    @$db->query("
        UPDATE revenue_targets
        SET
            target_type = COALESCE(target_type, 'monthly'),
            target_year = COALESCE(target_year, CAST(y AS CHAR)),
            target_from_month = COALESCE(target_from_month, CONCAT(CAST(y AS CHAR), '-', LPAD(CAST(m AS CHAR), 2, '0'))),
            target_to_month = COALESCE(target_to_month, CONCAT(CAST(y AS CHAR), '-', LPAD(CAST(m AS CHAR), 2, '0'))),
            target_month = COALESCE(target_month, CONCAT(CAST(y AS CHAR), '-', LPAD(CAST(m AS CHAR), 2, '0'))),
            target_amount = CASE
                WHEN target_amount IS NULL OR target_amount = 0 THEN $targetAmountExpr
                ELSE target_amount
            END
        WHERE target_from_month IS NULL OR target_to_month IS NULL OR target_month IS NULL
    ");
}

/* -------------------------------------------------------
   Permissions
------------------------------------------------------- */
$canManage = ec_can('targets.manage') || ec_can('settings.manage') || ec_role() === 'super_admin';

/* -------------------------------------------------------
   Load branches/courses
------------------------------------------------------- */
$branches = [];
if (tgt_table_exists($db, 'branches')) {
    $branchNameCol = tgt_first_col($db, 'branches', ['name', 'branch_name', 'title'], 'name');
    $rs = $db->query("SELECT id, `$branchNameCol` AS name FROM branches ORDER BY `$branchNameCol` ASC");
    if ($rs) {
        while ($row = $rs->fetch_assoc()) {
            $branches[] = $row;
        }
    }
}

$courses = [];
if (tgt_table_exists($db, 'courses')) {
    $courseNameCol = tgt_first_col($db, 'courses', ['course_name', 'name', 'title'], 'name');
    $rs = $db->query("SELECT id, `$courseNameCol` AS name FROM courses ORDER BY `$courseNameCol` ASC");
    if ($rs) {
        while ($row = $rs->fetch_assoc()) {
            $courses[] = $row;
        }
    }
}

/* -------------------------------------------------------
   POST actions
------------------------------------------------------- */
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (!$canManage) {
        tgt_flash('danger', 'You do not have permission to manage targets.');
        header('Location: targets.php');
        exit;
    }

    $csrf = (string)($_POST['csrf'] ?? '');
    if (!hash_equals(tgt_csrf(), $csrf)) {
        tgt_flash('danger', 'Security token expired. Please try again.');
        header('Location: targets.php');
        exit;
    }

    $action = (string)($_POST['action'] ?? '');

    if ($action === 'delete') {
        $id = (int)($_POST['id'] ?? 0);

        if ($id > 0) {
            $st = $db->prepare("DELETE FROM revenue_targets WHERE id = ? LIMIT 1");
            if ($st) {
                $st->bind_param('i', $id);
                $ok = $st->execute();
                $st->close();

                tgt_flash($ok ? 'success' : 'danger', $ok ? 'Target deleted successfully.' : 'Target delete failed.');
            }
        }

        header('Location: targets.php');
        exit;
    }

    if ($action === 'save') {
        $id                = (int)($_POST['id'] ?? 0);
        $targetType        = trim((string)($_POST['target_type'] ?? 'monthly'));
        $targetFromMonth   = trim((string)($_POST['target_from_month'] ?? ''));
        $targetToMonth     = trim((string)($_POST['target_to_month'] ?? ''));
        $targetYear        = trim((string)($_POST['target_year'] ?? date('Y')));
        $branchId          = (int)($_POST['branch_id'] ?? 0);
        $courseId          = (int)($_POST['course_id'] ?? 0);
        $targetAmount      = (float)($_POST['target_amount'] ?? 0);
        $notes             = trim((string)($_POST['notes'] ?? ''));
        $createAllCourses  = isset($_POST['create_all_courses']) ? 1 : 0;

        if (!in_array($targetType, ['monthly', 'annual'], true)) {
            $targetType = 'monthly';
        }

        if ($targetAmount < 0) {
            tgt_flash('danger', 'Target amount cannot be negative.');
            header('Location: targets.php');
            exit;
        }

        if ($targetType === 'monthly') {
            if (!preg_match('/^\d{4}-\d{2}$/', $targetFromMonth) || !preg_match('/^\d{4}-\d{2}$/', $targetToMonth)) {
                tgt_flash('danger', 'Please select valid From Month and To Month.');
                header('Location: targets.php');
                exit;
            }

            if ($targetFromMonth > $targetToMonth) {
                tgt_flash('danger', 'From Month cannot be after To Month.');
                header('Location: targets.php');
                exit;
            }

            $targetMonth = $targetFromMonth;
            $targetYear  = substr($targetFromMonth, 0, 4);
        } else {
            if (!preg_match('/^\d{4}$/', $targetYear)) {
                tgt_flash('danger', 'Please select a valid target year.');
                header('Location: targets.php');
                exit;
            }

            $targetFromMonth = $targetYear . '-01';
            $targetToMonth   = $targetYear . '-12';
            $targetMonth     = $targetYear . '-00';
        }

        $branchDb = $branchId > 0 ? $branchId : null;
        $courseDb = $courseId > 0 ? $courseId : null;
        $userId   = tgt_user_id();

        /* Create separate row for every course */
        if ($id <= 0 && $createAllCourses === 1) {
            if (!tgt_table_exists($db, 'courses')) {
                tgt_flash('danger', 'Courses table not found.');
                header('Location: targets.php');
                exit;
            }

            $courseNameCol = tgt_first_col($db, 'courses', ['course_name', 'name', 'title'], 'name');
            $courseRows = [];
            $rsCourses = $db->query("SELECT id, `$courseNameCol` AS name FROM courses ORDER BY `$courseNameCol` ASC");

            if ($rsCourses) {
                while ($cr = $rsCourses->fetch_assoc()) {
                    $courseRows[] = $cr;
                }
            }

            if (!$courseRows) {
                tgt_flash('danger', 'No courses found to create targets.');
                header('Location: targets.php');
                exit;
            }

            $created = 0;
            $skipped = 0;

            foreach ($courseRows as $courseRow) {
                $eachCourseId = (int)$courseRow['id'];

                if ($branchDb === null) {
                    $stCheck = $db->prepare("
                        SELECT id
                        FROM revenue_targets
                        WHERE target_type = ?
                          AND target_year = ?
                          AND target_from_month = ?
                          AND target_to_month = ?
                          AND branch_id IS NULL
                          AND course_id = ?
                        LIMIT 1
                    ");
                    if ($stCheck) {
                        $stCheck->bind_param('ssssi', $targetType, $targetYear, $targetFromMonth, $targetToMonth, $eachCourseId);
                    }
                } else {
                    $stCheck = $db->prepare("
                        SELECT id
                        FROM revenue_targets
                        WHERE target_type = ?
                          AND target_year = ?
                          AND target_from_month = ?
                          AND target_to_month = ?
                          AND branch_id = ?
                          AND course_id = ?
                        LIMIT 1
                    ");
                    if ($stCheck) {
                        $stCheck->bind_param('ssssii', $targetType, $targetYear, $targetFromMonth, $targetToMonth, $branchDb, $eachCourseId);
                    }
                }

                $exists = false;
                if ($stCheck) {
                    $stCheck->execute();
                    $exists = (bool)$stCheck->get_result()->fetch_assoc();
                    $stCheck->close();
                }

                if ($exists) {
                    $skipped++;
                    continue;
                }

                $finalNotes = $notes !== '' ? $notes : 'Auto-created for all courses';

                $stIns = $db->prepare("
                    INSERT INTO revenue_targets
                    (branch_id, course_id, target_type, target_year, target_from_month, target_to_month, target_month, target_amount, notes, created_by)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ");

                if ($stIns) {
                    $stIns->bind_param(
                        'iisssssdsi',
                        $branchDb,
                        $eachCourseId,
                        $targetType,
                        $targetYear,
                        $targetFromMonth,
                        $targetToMonth,
                        $targetMonth,
                        $targetAmount,
                        $finalNotes,
                        $userId
                    );

                    if ($stIns->execute()) {
                        $created++;
                    }

                    $stIns->close();
                }
            }

            tgt_flash('success', 'Targets created for courses: ' . $created . '. Skipped duplicates: ' . $skipped . '.');
            tgt_redirect_after_save($targetType, $targetYear, $targetMonth, $branchId, 0);
        }

        /* Update existing */
        if ($id > 0) {
            $st = $db->prepare("
                UPDATE revenue_targets
                SET branch_id = ?,
                    course_id = ?,
                    target_type = ?,
                    target_year = ?,
                    target_from_month = ?,
                    target_to_month = ?,
                    target_month = ?,
                    target_amount = ?,
                    notes = ?
                WHERE id = ?
                LIMIT 1
            ");

            if (!$st) {
                tgt_flash('danger', 'Update failed: ' . $db->error);
                header('Location: targets.php');
                exit;
            }

            $st->bind_param(
                'iisssssdsi',
                $branchDb,
                $courseDb,
                $targetType,
                $targetYear,
                $targetFromMonth,
                $targetToMonth,
                $targetMonth,
                $targetAmount,
                $notes,
                $id
            );

            $ok = $st->execute();
            $st->close();

            tgt_flash($ok ? 'success' : 'danger', $ok ? 'Target updated successfully.' : 'Target update failed.');
            tgt_redirect_after_save($targetType, $targetYear, $targetMonth, $branchId, $courseId);
        }

        /* Duplicate check for single/global target */
        if ($branchDb === null && $courseDb === null) {
            $stDup = $db->prepare("
                SELECT id
                FROM revenue_targets
                WHERE target_type = ?
                  AND target_year = ?
                  AND target_from_month = ?
                  AND target_to_month = ?
                  AND branch_id IS NULL
                  AND course_id IS NULL
                LIMIT 1
            ");
            if ($stDup) {
                $stDup->bind_param('ssss', $targetType, $targetYear, $targetFromMonth, $targetToMonth);
            }
        } elseif ($branchDb !== null && $courseDb === null) {
            $stDup = $db->prepare("
                SELECT id
                FROM revenue_targets
                WHERE target_type = ?
                  AND target_year = ?
                  AND target_from_month = ?
                  AND target_to_month = ?
                  AND branch_id = ?
                  AND course_id IS NULL
                LIMIT 1
            ");
            if ($stDup) {
                $stDup->bind_param('ssssi', $targetType, $targetYear, $targetFromMonth, $targetToMonth, $branchDb);
            }
        } elseif ($branchDb === null && $courseDb !== null) {
            $stDup = $db->prepare("
                SELECT id
                FROM revenue_targets
                WHERE target_type = ?
                  AND target_year = ?
                  AND target_from_month = ?
                  AND target_to_month = ?
                  AND branch_id IS NULL
                  AND course_id = ?
                LIMIT 1
            ");
            if ($stDup) {
                $stDup->bind_param('ssssi', $targetType, $targetYear, $targetFromMonth, $targetToMonth, $courseDb);
            }
        } else {
            $stDup = $db->prepare("
                SELECT id
                FROM revenue_targets
                WHERE target_type = ?
                  AND target_year = ?
                  AND target_from_month = ?
                  AND target_to_month = ?
                  AND branch_id = ?
                  AND course_id = ?
                LIMIT 1
            ");
            if ($stDup) {
                $stDup->bind_param('ssssii', $targetType, $targetYear, $targetFromMonth, $targetToMonth, $branchDb, $courseDb);
            }
        }

        $duplicate = false;
        if (isset($stDup) && $stDup) {
            $stDup->execute();
            $duplicate = (bool)$stDup->get_result()->fetch_assoc();
            $stDup->close();
        }

        if ($duplicate) {
            tgt_flash('warning', 'Target already exists for this period, branch and course selection.');
            tgt_redirect_after_save($targetType, $targetYear, $targetMonth, $branchId, $courseId);
        }

        /* Insert new */
        $st = $db->prepare("
            INSERT INTO revenue_targets
            (branch_id, course_id, target_type, target_year, target_from_month, target_to_month, target_month, target_amount, notes, created_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");

        if (!$st) {
            tgt_flash('danger', 'Save failed: ' . $db->error);
            header('Location: targets.php');
            exit;
        }

        $st->bind_param(
            'iisssssdsi',
            $branchDb,
            $courseDb,
            $targetType,
            $targetYear,
            $targetFromMonth,
            $targetToMonth,
            $targetMonth,
            $targetAmount,
            $notes,
            $userId
        );

        $ok = $st->execute();
        $st->close();

        tgt_flash($ok ? 'success' : 'danger', $ok ? 'Target added successfully.' : 'Target save failed.');
        tgt_redirect_after_save($targetType, $targetYear, $targetMonth, $branchId, $courseId);
    }
}

/* -------------------------------------------------------
   Filters
------------------------------------------------------- */
$currentMonth = date('Y-m');
$currentYear  = date('Y');

$filterType = trim((string)($_GET['type'] ?? 'monthly'));
if (!in_array($filterType, ['monthly', 'annual'], true)) {
    $filterType = 'monthly';
}

$filterMonth = trim((string)($_GET['month'] ?? $currentMonth));
if (!preg_match('/^\d{4}-\d{2}$/', $filterMonth)) {
    $filterMonth = $currentMonth;
}

$filterYear = trim((string)($_GET['year'] ?? $currentYear));
if (!preg_match('/^\d{4}$/', $filterYear)) {
    $filterYear = $currentYear;
}

$filterBranch = (int)($_GET['branch_id'] ?? 0);
$filterCourse = (int)($_GET['course_id'] ?? 0);

/* -------------------------------------------------------
   Achieved calculation
------------------------------------------------------- */
function tgt_achieved_amount(
    mysqli $db,
    string $fromMonth,
    string $toMonth,
    ?int $branchId,
    ?int $courseId
): float {
    if (!tgt_table_exists($db, 'payments') || !tgt_table_exists($db, 'studentss')) {
        return 0.0;
    }

    $amountCol = tgt_first_col($db, 'payments', ['amount', 'amount_lkr', 'paid_amount', 'payment_amount'], '');
    if ($amountCol === '') return 0.0;

    $dateCol = tgt_first_col($db, 'payments', ['date_paid', 'payment_date', 'paid_at', 'created_at', 'date', 'payment_created_at'], '');
    if ($dateCol === '') return 0.0;

    $studentIdCol = tgt_first_col($db, 'payments', ['student_id', 'sid'], '');
    if ($studentIdCol === '') return 0.0;

    $studentBranchCol = tgt_first_col($db, 'studentss', ['branch_id'], '');
    $paymentCourseCol = tgt_first_col($db, 'payments', ['course_id'], '');
$studentCourseCol = tgt_first_col($db, 'studentss', ['course_id'], '');

    if (!preg_match('/^\d{4}-\d{2}$/', $fromMonth)) {
        return 0.0;
    }

    if (!preg_match('/^\d{4}-\d{2}$/', $toMonth)) {
        $toMonth = $fromMonth;
    }

    $start = $fromMonth . '-01';
    $end   = date('Y-m-d', strtotime($toMonth . '-01 +1 month'));

    $where = [];
    $types = '';
    $vals  = [];

    $where[] = "p.`$dateCol` >= ?";
    $types .= 's';
    $vals[] = $start;

    $where[] = "p.`$dateCol` < ?";
    $types .= 's';
    $vals[] = $end;

    if ($branchId && $branchId > 0 && $studentBranchCol !== '') {
        $where[] = "s.`$studentBranchCol` = ?";
        $types .= 'i';
        $vals[] = $branchId;
    }

   if ($courseId && $courseId > 0) {
    if ($paymentCourseCol !== '') {
        $where[] = "p.`$paymentCourseCol` = ?";
        $types .= 'i';
        $vals[] = $courseId;
    } elseif ($studentCourseCol !== '') {
        $where[] = "s.`$studentCourseCol` = ?";
        $types .= 'i';
        $vals[] = $courseId;
    }
}

    if (tgt_col_exists($db, 'payments', 'receipt_status')) {
        $where[] = "(p.receipt_status IS NULL OR p.receipt_status <> 'cancelled')";
    }

    if (tgt_col_exists($db, 'payments', 'status')) {
        $where[] = "(p.status IS NULL OR p.status NOT IN ('cancelled','deleted','void'))";
    }

    $sql = "
        SELECT COALESCE(SUM(p.`$amountCol`), 0) AS total
        FROM payments p
        INNER JOIN studentss s ON s.id = p.`$studentIdCol`
        WHERE " . implode(' AND ', $where);

    $st = $db->prepare($sql);
    if (!$st) return 0.0;

    $st->bind_param($types, ...$vals);
    $st->execute();
    $row = $st->get_result()->fetch_assoc();
    $st->close();

    return (float)($row['total'] ?? 0);
}

/* -------------------------------------------------------
   Load targets
------------------------------------------------------- */
$where = ['rt.target_type = ?'];
$types = 's';
$vals  = [$filterType];

if ($filterType === 'monthly') {
    $where[] = 'rt.target_from_month <= ?';
    $types .= 's';
    $vals[] = $filterMonth;

    $where[] = 'rt.target_to_month >= ?';
    $types .= 's';
    $vals[] = $filterMonth;
} else {
    $where[] = 'rt.target_year = ?';
    $types .= 's';
    $vals[] = $filterYear;
}

if ($filterBranch > 0) {
    $where[] = 'rt.branch_id = ?';
    $types .= 'i';
    $vals[] = $filterBranch;
}

if ($filterCourse > 0) {
    $where[] = 'rt.course_id = ?';
    $types .= 'i';
    $vals[] = $filterCourse;
}

$branchJoin = '';
$branchSelect = "NULL AS branch_name";
if (tgt_table_exists($db, 'branches')) {
    $branchNameCol = tgt_first_col($db, 'branches', ['name', 'branch_name', 'title'], 'name');
    $branchJoin = "LEFT JOIN branches b ON b.id = rt.branch_id";
    $branchSelect = "b.`$branchNameCol` AS branch_name";
}

$courseJoin = '';
$courseSelect = "NULL AS course_name";
if (tgt_table_exists($db, 'courses')) {
    $courseNameCol = tgt_first_col($db, 'courses', ['course_name', 'name', 'title'], 'name');
    $courseJoin = "LEFT JOIN courses c ON c.id = rt.course_id";
    $courseSelect = "c.`$courseNameCol` AS course_name";
}

$sql = "
    SELECT
        rt.*,
        $branchSelect,
        $courseSelect
    FROM revenue_targets rt
    $branchJoin
    $courseJoin
    WHERE " . implode(' AND ', $where) . "
    ORDER BY rt.target_year DESC, rt.target_from_month DESC, rt.id DESC
";

$targets = [];
$st = $db->prepare($sql);
if ($st) {
    $st->bind_param($types, ...$vals);
    $st->execute();
    $rs = $st->get_result();

    while ($row = $rs->fetch_assoc()) {
        $branchId = !empty($row['branch_id']) ? (int)$row['branch_id'] : null;
        $courseId = !empty($row['course_id']) ? (int)$row['course_id'] : null;

        $fromMonth = (string)($row['target_from_month'] ?: $row['target_month']);
        $toMonth   = (string)($row['target_to_month'] ?: $row['target_month']);

        if (($row['target_type'] ?? 'monthly') === 'annual') {
            $year = (string)($row['target_year'] ?: date('Y'));
            $fromMonth = $year . '-01';
            $toMonth   = $year . '-12';
        }

        $achieved = tgt_achieved_amount($db, $fromMonth, $toMonth, $branchId, $courseId);

        $targetAmount = (float)$row['target_amount'];
        $balance = $targetAmount - $achieved;
        $progress = $targetAmount > 0 ? min(999, ($achieved / $targetAmount) * 100) : 0;

        $row['achieved_amount'] = $achieved;
        $row['balance_amount']  = $balance;
        $row['progress_percent'] = $progress;
        $row['display_from_month'] = $fromMonth;
        $row['display_to_month'] = $toMonth;

        $targets[] = $row;
    }

    $st->close();
}

/* -------------------------------------------------------
   Summary
------------------------------------------------------- */
$totalTarget = 0.0;
$totalAchieved = 0.0;

foreach ($targets as $t) {
    $totalTarget += (float)$t['target_amount'];
    $totalAchieved += (float)$t['achieved_amount'];
}

$totalBalance = $totalTarget - $totalAchieved;
$totalProgress = $totalTarget > 0 ? ($totalAchieved / $totalTarget) * 100 : 0;

$csrf = tgt_csrf();
$flash = $_SESSION['targets_flash'] ?? null;
unset($_SESSION['targets_flash']);

include __DIR__ . '/../includes/header.php';
include __DIR__ . '/../includes/sidebar.php';
?>

<main class="ec-main">
<div class="container-fluid py-4">

    <div class="d-flex flex-wrap justify-content-between align-items-center gap-2 mb-4">
        <div>
            <h4 class="fw-bold mb-1">Revenue Targets</h4>
            <div class="text-muted small">Set monthly range / annual branch-course targets and compare with achieved payments.</div>
        </div>

        <?php if ($canManage): ?>
            <button class="btn btn-primary rounded-pill px-4" data-bs-toggle="modal" data-bs-target="#targetModal" onclick="openTargetCreate()">
                <i class="bi bi-plus-circle me-1"></i> Add Target
            </button>
        <?php endif; ?>
    </div>

    <?php if ($flash): ?>
        <div class="alert alert-<?= h($flash['type']) ?> alert-dismissible fade show">
            <?= h($flash['msg']) ?>
            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
        </div>
    <?php endif; ?>

    <div class="card border-0 shadow-sm rounded-4 mb-4">
        <div class="card-body p-3">
            <form method="get" class="row g-3 align-items-end">
                <div class="col-md-2">
                    <label class="form-label small fw-semibold">Target Type</label>
                    <select name="type" class="form-select" onchange="toggleFilterPeriod(this.value)">
                        <option value="monthly" <?= $filterType === 'monthly' ? 'selected' : '' ?>>Monthly / Range</option>
                        <option value="annual" <?= $filterType === 'annual' ? 'selected' : '' ?>>Annual</option>
                    </select>
                </div>

                <div class="col-md-2" id="filterMonthBox">
                    <label class="form-label small fw-semibold">Month</label>
                    <input type="month" name="month" class="form-control" value="<?= h($filterMonth) ?>">
                    <div class="form-text">Shows ranges covering this month.</div>
                </div>

                <div class="col-md-2" id="filterYearBox" style="display:none;">
                    <label class="form-label small fw-semibold">Year</label>
                    <input type="number" name="year" class="form-control" min="2020" max="2100" value="<?= h($filterYear) ?>">
                </div>

                <div class="col-md-2">
                    <label class="form-label small fw-semibold">Branch</label>
                    <select name="branch_id" class="form-select">
                        <option value="0">All Branches</option>
                        <?php foreach ($branches as $b): ?>
                            <option value="<?= (int)$b['id'] ?>" <?= $filterBranch === (int)$b['id'] ? 'selected' : '' ?>>
                                <?= h($b['name']) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>

                <div class="col-md-3">
                    <label class="form-label small fw-semibold">Course</label>
                    <select name="course_id" class="form-select">
                        <option value="0">All Courses</option>
                        <?php foreach ($courses as $c): ?>
                            <option value="<?= (int)$c['id'] ?>" <?= $filterCourse === (int)$c['id'] ? 'selected' : '' ?>>
                                <?= h($c['name']) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>

                <div class="col-md-3 d-flex gap-2">
                    <button class="btn btn-dark rounded-pill px-4">
                        <i class="bi bi-search me-1"></i> Filter
                    </button>
                    <a href="targets.php" class="btn btn-light border rounded-pill px-4">Reset</a>
                </div>
            </form>
        </div>
    </div>

    <div class="row g-3 mb-4">
        <div class="col-md-3">
            <div class="card border-0 shadow-sm rounded-4">
                <div class="card-body">
                    <div class="text-muted small">Total Target</div>
                    <h4 class="fw-bold mb-0">LKR <?= number_format($totalTarget, 2) ?></h4>
                </div>
            </div>
        </div>

        <div class="col-md-3">
            <div class="card border-0 shadow-sm rounded-4">
                <div class="card-body">
                    <div class="text-muted small">Achieved</div>
                    <h4 class="fw-bold mb-0 text-success">LKR <?= number_format($totalAchieved, 2) ?></h4>
                </div>
            </div>
        </div>

        <div class="col-md-3">
            <div class="card border-0 shadow-sm rounded-4">
                <div class="card-body">
                    <div class="text-muted small">Balance</div>
                    <h4 class="fw-bold mb-0 <?= $totalBalance <= 0 ? 'text-success' : 'text-danger' ?>">
                        LKR <?= number_format($totalBalance, 2) ?>
                    </h4>
                </div>
            </div>
        </div>

        <div class="col-md-3">
            <div class="card border-0 shadow-sm rounded-4">
                <div class="card-body">
                    <div class="text-muted small">Progress</div>
                    <h4 class="fw-bold mb-0"><?= number_format($totalProgress, 1) ?>%</h4>
                    <div class="progress mt-2" style="height:8px;">
                        <div class="progress-bar" style="width:<?= min(100, $totalProgress) ?>%;"></div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <div class="card border-0 shadow-sm rounded-4">
        <div class="card-header bg-white border-0 p-4 pb-2">
            <h5 class="fw-bold mb-1">Target List</h5>
            <div class="text-muted small">
                <?= $filterType === 'annual' ? 'Year: ' . h($filterYear) : 'Month filter: ' . h($filterMonth) ?>
            </div>
        </div>

        <div class="card-body p-0">
            <div class="table-responsive">
                <table class="table table-hover align-middle mb-0">
                    <thead class="table-light">
                        <tr>
                            <th>Type</th>
                            <th>Period</th>
                            <th>Branch</th>
                            <th>Course</th>
                            <th class="text-end">Target</th>
                            <th class="text-end">Achieved</th>
                            <th class="text-end">Balance</th>
                            <th style="width:180px;">Progress</th>
                            <?php if ($canManage): ?>
                                <th class="text-end">Action</th>
                            <?php endif; ?>
                        </tr>
                    </thead>

                    <tbody>
                        <?php if (!$targets): ?>
                            <tr>
                                <td colspan="<?= $canManage ? 9 : 8 ?>" class="text-center text-muted py-5">
                                    No targets found for selected filters.
                                </td>
                            </tr>
                        <?php endif; ?>

                        <?php foreach ($targets as $t): ?>
                            <?php
                            $progress = (float)$t['progress_percent'];
                            $balance  = (float)$t['balance_amount'];
                            $typeLabel = (($t['target_type'] ?? 'monthly') === 'annual') ? 'Annual' : 'Monthly / Range';
                            $periodLabel = (($t['target_type'] ?? 'monthly') === 'annual')
                                ? (string)($t['target_year'] ?? '')
                                : (string)($t['display_from_month'] . ' to ' . $t['display_to_month']);
                            ?>
                            <tr>
                                <td>
                                    <span class="badge <?= $typeLabel === 'Annual' ? 'text-bg-dark' : 'text-bg-primary' ?>">
                                        <?= h($typeLabel) ?>
                                    </span>
                                </td>
                                <td class="fw-semibold"><?= h($periodLabel) ?></td>
                                <td><?= h($t['branch_name'] ?: 'All Branches') ?></td>
                                <td><?= h($t['course_name'] ?: 'All Courses') ?></td>
                                <td class="text-end fw-semibold">LKR <?= number_format((float)$t['target_amount'], 2) ?></td>
                                <td class="text-end text-success fw-semibold">LKR <?= number_format((float)$t['achieved_amount'], 2) ?></td>
                                <td class="text-end fw-semibold <?= $balance <= 0 ? 'text-success' : 'text-danger' ?>">
                                    LKR <?= number_format($balance, 2) ?>
                                </td>
                                <td>
                                    <div class="d-flex align-items-center gap-2">
                                        <div class="progress flex-grow-1" style="height:8px;">
                                            <div class="progress-bar <?= $progress >= 100 ? 'bg-success' : '' ?>" style="width:<?= min(100, $progress) ?>%;"></div>
                                        </div>
                                        <span class="small fw-semibold"><?= number_format($progress, 0) ?>%</span>
                                    </div>
                                </td>

                                <?php if ($canManage): ?>
                                    <td class="text-end">
                                        <button type="button"
                                                class="btn btn-sm btn-light border rounded-pill"
                                                data-bs-toggle="modal"
                                                data-bs-target="#targetModal"
                                                onclick='openTargetEdit(<?= json_encode([
                                                    'id' => (int)$t['id'],
                                                    'target_type' => (string)($t['target_type'] ?? 'monthly'),
                                                    'target_year' => (string)($t['target_year'] ?? date('Y')),
                                                    'target_from_month' => (string)($t['target_from_month'] ?? $t['target_month']),
                                                    'target_to_month' => (string)($t['target_to_month'] ?? $t['target_month']),
                                                    'branch_id' => (int)($t['branch_id'] ?? 0),
                                                    'course_id' => (int)($t['course_id'] ?? 0),
                                                    'target_amount' => (string)$t['target_amount'],
                                                    'notes' => (string)($t['notes'] ?? ''),
                                                ], JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP) ?>)'>
                                            Edit
                                        </button>

                                        <form method="post" class="d-inline" onsubmit="return confirm('Delete this target?');">
                                            <input type="hidden" name="csrf" value="<?= h($csrf) ?>">
                                            <input type="hidden" name="action" value="delete">
                                            <input type="hidden" name="id" value="<?= (int)$t['id'] ?>">
                                            <button class="btn btn-sm btn-outline-danger rounded-pill">Delete</button>
                                        </form>
                                    </td>
                                <?php endif; ?>
                            </tr>

                            <?php if (!empty($t['notes'])): ?>
                                <tr>
                                    <td></td>
                                    <td colspan="<?= $canManage ? 8 : 7 ?>" class="small text-muted pt-0">
                                        <i class="bi bi-chat-left-text me-1"></i><?= h($t['notes']) ?>
                                    </td>
                                </tr>
                            <?php endif; ?>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<?php if ($canManage): ?>
<div class="modal fade" id="targetModal" tabindex="-1" aria-hidden="true">
    <div class="modal-dialog modal-dialog-centered">
        <form method="post" class="modal-content rounded-4 border-0 shadow">
            <input type="hidden" name="csrf" value="<?= h($csrf) ?>">
            <input type="hidden" name="action" id="target_action" value="save">
            <input type="hidden" name="id" id="target_id" value="0">

            <div class="modal-header border-0">
                <h5 class="modal-title fw-bold" id="targetModalTitle">Add Target</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>

            <div class="modal-body pt-0">
                <div class="mb-3">
                    <label class="form-label fw-semibold">Target Type</label>
                    <select name="target_type" id="target_type" class="form-select" onchange="toggleTargetPeriod()" required>
                        <option value="monthly">Monthly / Month Range Target</option>
                        <option value="annual">Annual Year Target</option>
                    </select>
                </div>

                <div class="row g-3 mb-3" id="monthBox">
                    <div class="col-md-6">
                        <label class="form-label fw-semibold">From Month</label>
                        <input type="month" name="target_from_month" id="target_from_month" class="form-control" value="<?= h($filterMonth) ?>">
                    </div>

                    <div class="col-md-6">
                        <label class="form-label fw-semibold">To Month</label>
                        <input type="month" name="target_to_month" id="target_to_month" class="form-control" value="<?= h($filterMonth) ?>">
                    </div>
                </div>

                <div class="mb-3" id="yearBox" style="display:none;">
                    <label class="form-label fw-semibold">Target Year</label>
                    <input type="number" name="target_year" id="target_year" class="form-control"
                           min="2020" max="2100" value="<?= h($filterYear) ?>">
                </div>

                <div class="mb-3">
                    <label class="form-label fw-semibold">Branch</label>
                    <select name="branch_id" id="target_branch_id" class="form-select">
                        <option value="0">All Branches</option>
                        <?php foreach ($branches as $b): ?>
                            <option value="<?= (int)$b['id'] ?>"><?= h($b['name']) ?></option>
                        <?php endforeach; ?>
                    </select>
                    <div class="form-text">Leave as All Branches for global target.</div>
                </div>

                <div class="mb-3">
                    <label class="form-label fw-semibold">Course</label>
                    <select name="course_id" id="target_course_id" class="form-select">
                        <option value="0">All Courses</option>
                        <?php foreach ($courses as $c): ?>
                            <option value="<?= (int)$c['id'] ?>"><?= h($c['name']) ?></option>
                        <?php endforeach; ?>
                    </select>
                    <div class="form-text">Leave as All Courses for branch/global target.</div>
                </div>

                <div class="form-check form-switch mb-3" id="allCoursesBox">
                    <input class="form-check-input" type="checkbox" id="create_all_courses" name="create_all_courses" value="1">
                    <label class="form-check-label fw-semibold" for="create_all_courses">
                        Create separate target for every course
                    </label>
                    <div class="form-text">
                        If enabled, this will create one target row for each course using the same target amount.
                    </div>
                </div>

                <div class="mb-3">
                    <label class="form-label fw-semibold">Target Amount</label>
                    <input type="number" step="0.01" min="0" name="target_amount" id="target_amount" class="form-control" required>
                </div>

                <div>
                    <label class="form-label fw-semibold">Notes</label>
                    <textarea name="notes" id="target_notes" rows="3" class="form-control"></textarea>
                </div>
            </div>

            <div class="modal-footer border-0">
                <button type="button" class="btn btn-light border rounded-pill px-4" data-bs-dismiss="modal">Cancel</button>
                <button class="btn btn-primary rounded-pill px-4">
                    <i class="bi bi-check2-circle me-1"></i> Save Target
                </button>
            </div>
        </form>
    </div>
</div>
<?php endif; ?>

<?php include __DIR__ . '/../includes/footer.php'; ?>

<script>
function openTargetCreate() {
    document.getElementById('targetModalTitle').textContent = 'Add Target';
    document.getElementById('target_action').value = 'save';
    document.getElementById('target_id').value = '0';

    document.getElementById('target_type').value = 'monthly';
    document.getElementById('target_from_month').value = <?= json_encode($filterMonth) ?>;
    document.getElementById('target_to_month').value = <?= json_encode($filterMonth) ?>;
    document.getElementById('target_year').value = <?= json_encode($filterYear) ?>;

    document.getElementById('target_branch_id').value = '0';
    document.getElementById('target_course_id').value = '0';
    document.getElementById('target_amount').value = '';
    document.getElementById('target_notes').value = '';

    const allBox = document.getElementById('allCoursesBox');
    const allCheck = document.getElementById('create_all_courses');

    if (allBox) allBox.style.display = 'block';
    if (allCheck) allCheck.checked = false;

    toggleTargetPeriod();
}

function openTargetEdit(data) {
    document.getElementById('targetModalTitle').textContent = 'Edit Target';
    document.getElementById('target_action').value = 'save';
    document.getElementById('target_id').value = data.id || 0;

    document.getElementById('target_type').value = data.target_type || 'monthly';
    document.getElementById('target_from_month').value = data.target_from_month || <?= json_encode($filterMonth) ?>;
    document.getElementById('target_to_month').value = data.target_to_month || data.target_from_month || <?= json_encode($filterMonth) ?>;
    document.getElementById('target_year').value = data.target_year || <?= json_encode($filterYear) ?>;

    document.getElementById('target_branch_id').value = data.branch_id || 0;
    document.getElementById('target_course_id').value = data.course_id || 0;
    document.getElementById('target_amount').value = data.target_amount || '';
    document.getElementById('target_notes').value = data.notes || '';

    const allBox = document.getElementById('allCoursesBox');
    const allCheck = document.getElementById('create_all_courses');

    if (allBox) allBox.style.display = 'none';
    if (allCheck) allCheck.checked = false;

    toggleTargetPeriod();
}

function toggleTargetPeriod() {
    const type = document.getElementById('target_type')?.value || 'monthly';
    const monthBox = document.getElementById('monthBox');
    const yearBox = document.getElementById('yearBox');

    if (type === 'annual') {
        if (monthBox) monthBox.style.display = 'none';
        if (yearBox) yearBox.style.display = 'block';
    } else {
        if (monthBox) monthBox.style.display = 'flex';
        if (yearBox) yearBox.style.display = 'none';
    }
}

function toggleFilterPeriod(type) {
    const monthBox = document.getElementById('filterMonthBox');
    const yearBox = document.getElementById('filterYearBox');

    if (type === 'annual') {
        if (monthBox) monthBox.style.display = 'none';
        if (yearBox) yearBox.style.display = 'block';
    } else {
        if (monthBox) monthBox.style.display = 'block';
        if (yearBox) yearBox.style.display = 'none';
    }
}

document.addEventListener('DOMContentLoaded', function () {
    toggleTargetPeriod();
    toggleFilterPeriod(<?= json_encode($filterType) ?>);
});
</script>
</main>