<?php

function ensure_poi_schema(mysqli $conn): void
{
    $queries = [
        "CREATE TABLE IF NOT EXISTS poi_settings (
            id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
            org_id VARCHAR(50) NOT NULL,
            release_enabled TINYINT(1) NOT NULL DEFAULT 0,
            payroll_from_month VARCHAR(20) NOT NULL DEFAULT 'March',
            allow_switch_tax_regime TINYINT(1) NOT NULL DEFAULT 1,
            allow_tds_modification TINYINT(1) NOT NULL DEFAULT 0,
            mandate_attachments TINYINT(1) NOT NULL DEFAULT 0,
            mandate_reviewer_comments TINYINT(1) NOT NULL DEFAULT 0,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_poi_org (org_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
        "CREATE TABLE IF NOT EXISTS poi_submissions (
            id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
            org_id VARCHAR(50) NOT NULL,
            employee_id INT(11) NOT NULL,
            fiscal_year VARCHAR(20) NOT NULL,
            tax_regime VARCHAR(40) NOT NULL DEFAULT 'Old Tax Regime',
            investment_name VARCHAR(150) DEFAULT NULL,
            notes TEXT DEFAULT NULL,
            status ENUM('pending','submitted','approved') NOT NULL DEFAULT 'pending',
            declared_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            approved_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            submitted_at TIMESTAMP NULL DEFAULT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_poi_employee (employee_id),
            KEY idx_poi_status (status),
            KEY idx_poi_year (fiscal_year)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
    ];

    foreach ($queries as $query) {
        $conn->query($query);
    }

    $requiredColumns = [
        'investment_name' => "ALTER TABLE poi_submissions ADD COLUMN investment_name VARCHAR(150) DEFAULT NULL AFTER tax_regime",
        'notes' => "ALTER TABLE poi_submissions ADD COLUMN notes TEXT DEFAULT NULL AFTER investment_name",
        'submitted_at' => "ALTER TABLE poi_submissions ADD COLUMN submitted_at TIMESTAMP NULL DEFAULT NULL AFTER approved_amount",
    ];

    foreach ($requiredColumns as $column => $sql) {
        $databaseName = $conn->query("SELECT DATABASE() AS db_name");
        $dbRow = $databaseName ? $databaseName->fetch_assoc() : null;
        $schemaName = $dbRow['db_name'] ?? null;
        if (!$schemaName) {
            continue;
        }

        $columnStmt = $conn->prepare(
            "SELECT COLUMN_NAME
             FROM information_schema.COLUMNS
             WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'poi_submissions' AND COLUMN_NAME = ?"
        );
        if (!$columnStmt) {
            continue;
        }
        $columnStmt->bind_param('ss', $schemaName, $column);
        $columnStmt->execute();
        $exists = $columnStmt->get_result();
        $hasColumn = $exists && $exists->num_rows > 0;
        $columnStmt->close();

        if (!$hasColumn) {
            $conn->query($sql);
        }
    }
}

function poi_org_context(mysqli $conn): array
{
    $result = $conn->query("SELECT * FROM organizations ORDER BY id DESC LIMIT 1");
    $row = $result ? $result->fetch_assoc() : null;

    return [
        'org_name' => $_SESSION['org_name'] ?? ($row['organisation_name'] ?? 'demo'),
        'org_id' => (string) ($_SESSION['org_id'] ?? ($row['organisation_id'] ?? ($row['id'] ?? '60066881652'))),
    ];
}

function poi_load_employees(mysqli $conn): array
{
    $employees = [];
    $org = poi_org_context($conn);
    $hasOrgColumnResult = $conn->query("SHOW COLUMNS FROM employees LIKE 'organization_id'");
    $hasOrgColumn = (bool) ($hasOrgColumnResult && $hasOrgColumnResult->num_rows > 0);

    if ($hasOrgColumn) {
        $orgId = (int) ($org['org_id'] ?? 0);
        $stmt = $conn->prepare("SELECT * FROM employees WHERE organization_id = ? AND COALESCE(is_deleted, 0) = 0 ORDER BY id DESC");
        if ($stmt) {
            $stmt->bind_param('i', $orgId);
            $stmt->execute();
            $result = $stmt->get_result();
            while ($result && ($row = $result->fetch_assoc())) {
                $employees[] = $row;
            }
            $stmt->close();
        }
    } else {
        $result = $conn->query("SELECT * FROM employees WHERE COALESCE(is_deleted, 0) = 0 ORDER BY id DESC");
        if ($result) {
            while ($row = $result->fetch_assoc()) {
                $employees[] = $row;
            }
        }
    }
    return $employees;
}

function poi_pick_field(array $row, array $candidates)
{
    foreach ($candidates as $field) {
        if (array_key_exists($field, $row) && $row[$field] !== null && $row[$field] !== '') {
            return $row[$field];
        }
    }
    return null;
}

function poi_employee_name(array $row): string
{
    $full = poi_pick_field($row, ['full_name']);
    if ($full) {
        return $full;
    }

    $parts = array_filter([
        poi_pick_field($row, ['first_name', 'firstname']),
        poi_pick_field($row, ['middle_name', 'middlename']),
        poi_pick_field($row, ['last_name', 'lastname']),
    ]);

    return $parts ? implode(' ', $parts) : 'Employee';
}

function poi_employee_label(array $employee): string
{
    $id = (int) ($employee['id'] ?? 0);
    $code = trim((string) ($employee['employee_code'] ?? $employee['employee_id'] ?? $id));
    return poi_employee_name($employee) . ' - ' . $code;
}

function poi_get_settings(mysqli $conn, string $orgId): array
{
    $defaults = [
        'org_id' => $orgId,
        'release_enabled' => 0,
        'payroll_from_month' => 'March',
        'allow_switch_tax_regime' => 1,
        'allow_tds_modification' => 0,
        'mandate_attachments' => 0,
        'mandate_reviewer_comments' => 0,
    ];

    $stmt = $conn->prepare("SELECT * FROM poi_settings WHERE org_id = ? LIMIT 1");
    if (!$stmt) {
        return $defaults;
    }

    $stmt->bind_param('s', $orgId);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result ? $result->fetch_assoc() : null;
    $stmt->close();

    if (!$row) {
        return $defaults;
    }

    return array_merge($defaults, $row);
}

function poi_ensure_settings_row(mysqli $conn, string $orgId): void
{
    $stmt = $conn->prepare(
        "INSERT INTO poi_settings
            (org_id, release_enabled, payroll_from_month, allow_switch_tax_regime, allow_tds_modification, mandate_attachments, mandate_reviewer_comments)
         VALUES (?, 0, 'March', 1, 0, 0, 0)
         ON DUPLICATE KEY UPDATE org_id = VALUES(org_id)"
    );

    if ($stmt) {
        $stmt->bind_param('s', $orgId);
        $stmt->execute();
        $stmt->close();
    }
}

function poi_save_settings(mysqli $conn, string $orgId, array $settings): void
{
    $existing = poi_get_settings($conn, $orgId);
    $values = [
        'release_enabled' => !empty($settings['release_enabled']) ? 1 : 0,
        'payroll_from_month' => $settings['payroll_from_month'] ?? ($existing['payroll_from_month'] ?? 'March'),
        'allow_switch_tax_regime' => !empty($settings['allow_switch_tax_regime']) ? 1 : 0,
        'allow_tds_modification' => !empty($settings['allow_tds_modification']) ? 1 : 0,
        'mandate_attachments' => !empty($settings['mandate_attachments']) ? 1 : 0,
        'mandate_reviewer_comments' => !empty($settings['mandate_reviewer_comments']) ? 1 : 0,
    ];

    $stmt = $conn->prepare(
        "INSERT INTO poi_settings
            (org_id, release_enabled, payroll_from_month, allow_switch_tax_regime, allow_tds_modification, mandate_attachments, mandate_reviewer_comments)
         VALUES (?, ?, ?, ?, ?, ?, ?)
         ON DUPLICATE KEY UPDATE
            release_enabled = VALUES(release_enabled),
            payroll_from_month = VALUES(payroll_from_month),
            allow_switch_tax_regime = VALUES(allow_switch_tax_regime),
            allow_tds_modification = VALUES(allow_tds_modification),
            mandate_attachments = VALUES(mandate_attachments),
            mandate_reviewer_comments = VALUES(mandate_reviewer_comments)"
    );

    if ($stmt) {
        $stmt->bind_param(
            'sisiiii',
            $orgId,
            $values['release_enabled'],
            $values['payroll_from_month'],
            $values['allow_switch_tax_regime'],
            $values['allow_tds_modification'],
            $values['mandate_attachments'],
            $values['mandate_reviewer_comments']
        );
        $stmt->execute();
        $stmt->close();
    }
}

function poi_fiscal_years(): array
{
    return ['2025 - 2026', '2026 - 2027', '2027 - 2028'];
}

function poi_tax_regimes(): array
{
    return ['Select Tax Regime', 'Old Tax Regime', 'New Tax Regime'];
}

function poi_unsubmitted_employees(mysqli $conn, string $orgId, string $fiscalYear = ''): array
{
    $employees = poi_load_employees($conn);
    $submittedEmployeeIds = [];

    $sql = "SELECT DISTINCT employee_id FROM poi_submissions WHERE org_id = ?";
    $types = 's';
    $params = [$orgId];

    if ($fiscalYear !== '') {
        $sql .= " AND fiscal_year = ?";
        $types .= 's';
        $params[] = $fiscalYear;
    }

    $stmt = $conn->prepare($sql);
    if ($stmt) {
        $bind = [$types];
        foreach ($params as $index => $value) {
            $params[$index] = $value;
            $bind[] = &$params[$index];
        }
        call_user_func_array([$stmt, 'bind_param'], $bind);
        $stmt->execute();
        $result = $stmt->get_result();
        while ($row = $result->fetch_assoc()) {
            $submittedEmployeeIds[(int) $row['employee_id']] = true;
        }
        $stmt->close();
    }

    $list = [];
    foreach ($employees as $employee) {
        $employeeId = (int) ($employee['id'] ?? 0);
        if (isset($submittedEmployeeIds[$employeeId])) {
            continue;
        }
        $list[] = $employee;
    }

    return $list;
}

function poi_find_employee(mysqli $conn, int $employeeId): ?array
{
    $org = poi_org_context($conn);
    $hasOrgColumnResult = $conn->query("SHOW COLUMNS FROM employees LIKE 'organization_id'");
    $hasOrgColumn = (bool) ($hasOrgColumnResult && $hasOrgColumnResult->num_rows > 0);
    $sql = $hasOrgColumn
        ? "SELECT * FROM employees WHERE id = ? AND organization_id = ? LIMIT 1"
        : "SELECT * FROM employees WHERE id = ? LIMIT 1";
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        return null;
    }

    if ($hasOrgColumn) {
        $orgId = (int) ($org['org_id'] ?? 0);
        $stmt->bind_param('ii', $employeeId, $orgId);
    } else {
        $stmt->bind_param('i', $employeeId);
    }
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result ? $result->fetch_assoc() : null;
    $stmt->close();

    return $row ?: null;
}

function poi_submission_record(mysqli $conn, string $orgId, int $employeeId, string $fiscalYear): ?array
{
    $stmt = $conn->prepare(
        "SELECT * FROM poi_submissions
         WHERE org_id = ? AND employee_id = ? AND fiscal_year = ?
         ORDER BY id DESC
         LIMIT 1"
    );
    if (!$stmt) {
        return null;
    }

    $stmt->bind_param('sis', $orgId, $employeeId, $fiscalYear);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result ? $result->fetch_assoc() : null;
    $stmt->close();

    return $row ?: null;
}

function poi_save_submission(mysqli $conn, string $orgId, int $employeeId, array $payload): bool
{
    $fiscalYear = trim((string) ($payload['fiscal_year'] ?? ''));
    $taxRegime = trim((string) ($payload['tax_regime'] ?? 'Old Tax Regime'));
    $investmentName = trim((string) ($payload['investment_name'] ?? ''));
    $notes = trim((string) ($payload['notes'] ?? ''));
    $status = trim((string) ($payload['status'] ?? 'submitted'));
    $declaredAmount = (float) ($payload['declared_amount'] ?? 0);
    $approvedAmount = (float) ($payload['approved_amount'] ?? 0);

    $existing = poi_submission_record($conn, $orgId, $employeeId, $fiscalYear);
    if ($existing && !empty($existing['id'])) {
        $stmt = $conn->prepare(
            "UPDATE poi_submissions
             SET tax_regime = ?, investment_name = ?, notes = ?, status = ?, declared_amount = ?, approved_amount = ?,
                 submitted_at = CASE WHEN ? = 'submitted' THEN CURRENT_TIMESTAMP ELSE submitted_at END
             WHERE id = ?"
        );
        if (!$stmt) {
            return false;
        }
        $id = (int) $existing['id'];
        $stmt->bind_param('ssssddsi', $taxRegime, $investmentName, $notes, $status, $declaredAmount, $approvedAmount, $status, $id);
        $ok = $stmt->execute();
        $stmt->close();
        return $ok;
    }

    $stmt = $conn->prepare(
        "INSERT INTO poi_submissions
            (org_id, employee_id, fiscal_year, tax_regime, investment_name, notes, status, declared_amount, approved_amount, submitted_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, CASE WHEN ? = 'submitted' THEN CURRENT_TIMESTAMP ELSE NULL END)"
    );
    if (!$stmt) {
        return false;
    }

    $stmt->bind_param(
        'sisssssdds',
        $orgId,
        $employeeId,
        $fiscalYear,
        $taxRegime,
        $investmentName,
        $notes,
        $status,
        $declaredAmount,
        $approvedAmount,
        $status
    );
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function poi_submission_rows(mysqli $conn, string $orgId, array $filters = []): array
{
    $sql = "SELECT * FROM poi_submissions WHERE org_id = ?";
    $types = 's';
    $params = [$orgId];

    if (!empty($filters['fiscal_year'])) {
        $sql .= " AND fiscal_year = ?";
        $types .= 's';
        $params[] = $filters['fiscal_year'];
    }
    if (!empty($filters['tax_regime']) && $filters['tax_regime'] !== 'Select Tax Regime') {
        $sql .= " AND tax_regime = ?";
        $types .= 's';
        $params[] = $filters['tax_regime'];
    }
    if (!empty($filters['employee_id'])) {
        $sql .= " AND employee_id = ?";
        $types .= 'i';
        $params[] = (int) $filters['employee_id'];
    }
    if (!empty($filters['status'])) {
        $sql .= " AND status = ?";
        $types .= 's';
        $params[] = $filters['status'];
    }

    $sql .= " ORDER BY updated_at DESC, id DESC";
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        return [];
    }

    $bind = [$types];
    foreach ($params as $index => $value) {
        $params[$index] = $value;
        $bind[] = &$params[$index];
    }
    call_user_func_array([$stmt, 'bind_param'], $bind);
    $stmt->execute();
    $result = $stmt->get_result();

    $employees = [];
    foreach (poi_load_employees($conn) as $employee) {
        $employees[(int) ($employee['id'] ?? 0)] = $employee;
    }

    $rows = [];
    while ($result && ($row = $result->fetch_assoc())) {
        $employeeId = (int) ($row['employee_id'] ?? 0);
        $row['employee'] = $employees[$employeeId] ?? null;
        $rows[] = $row;
    }
    $stmt->close();

    return $rows;
}
