<?php
// Session configuration
session_start();

// MySQL Database configuration
define('DB_HOST', ' ');
define('DB_PORT', ' ');
define('DB_NAME', ' ');
define('DB_USER', ' ');
define('DB_PASS', ' ');

// Connect to MySQL database
function getDB() {
    static $db = null;
    if ($db !== null) {
        return $db;
    }

    try {
        $dsn = 'mysql:host=' . DB_HOST . ';port=' . DB_PORT . ';dbname=' . DB_NAME . ';charset=utf8mb4';
        $db = new PDO($dsn, DB_USER, DB_PASS, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4'
        ]);

        // Initialize database schema
        initializeDB($db);

        return $db;
    } catch (PDOException $e) {
        http_response_code(500);
        die(json_encode(['error' => 'Database connection failed: ' . $e->getMessage()]));
    }
}

// Initialize database schema (MySQL version)
function initializeDB($db) {
    // Create markers table for points
    $db->exec('
        CREATE TABLE IF NOT EXISTS markers (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            nomor_spbu VARCHAR(100) DEFAULT NULL,
            latitude DOUBLE NOT NULL,
            longitude DOUBLE NOT NULL,
            open_24_hours TINYINT(1) DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ');

    // Create roads table for polylines
    $db->exec('
        CREATE TABLE IF NOT EXISTS roads (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            road_type ENUM(\'Nasional\', \'Provinsi\', \'Kabupaten\') NOT NULL,
            coordinates LONGTEXT NOT NULL,
            length_meters DOUBLE NOT NULL,
            condition_status ENUM(\'normal\', \'minor_damage\', \'major_damage\') DEFAULT \'normal\',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ');

    // Create indexes for roads table
    try {
        $db->exec('CREATE INDEX idx_roads_road_type ON roads(road_type)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_roads_created_at ON roads(created_at)');
    } catch (PDOException $e) { /* Index may already exist */ }

    // Create land_parcels table for polygons
    $db->exec('
        CREATE TABLE IF NOT EXISTS land_parcels (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            certificate_type ENUM(\'SHM\', \'HGB\', \'HGU\', \'HP\') NOT NULL,
            coordinates LONGTEXT NOT NULL,
            area_sqm DOUBLE NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ');

    // Create indexes for land_parcels table
    try {
        $db->exec('CREATE INDEX idx_parcels_cert_type ON land_parcels(certificate_type)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_parcels_created_at ON land_parcels(created_at)');
    } catch (PDOException $e) { /* Index may already exist */ }

    // Create pontianak_areas table for choropleth boundaries
    $db->exec('
        CREATE TABLE IF NOT EXISTS pontianak_areas (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            area_type ENUM(\'kelurahan\', \'kecamatan\', \'city\') NOT NULL,
            geometry LONGTEXT NOT NULL,
            population INT DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_area_name_type (name, area_type)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ');

    // Create indexes for pontianak_areas table
    try {
        $db->exec('CREATE INDEX idx_pontianak_areas_type ON pontianak_areas(area_type)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_pontianak_areas_name ON pontianak_areas(name)');
    } catch (PDOException $e) { /* Index may already exist */ }

    // Create area_statistics table for computed densities
    $db->exec('
        CREATE TABLE IF NOT EXISTS area_statistics (
            id INT AUTO_INCREMENT PRIMARY KEY,
            pontianak_area_id INT NOT NULL,
            statistic_type ENUM(\'population_density\', \'road_density\', \'damaged_roads_density\') NOT NULL,
            value DOUBLE NOT NULL,
            data_date DATE NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_area_stat_date (pontianak_area_id, statistic_type, data_date),
            FOREIGN KEY (pontianak_area_id) REFERENCES pontianak_areas(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ');

    // Create indexes for area_statistics table
    try {
        $db->exec('CREATE INDEX idx_stats_area_type ON area_statistics(pontianak_area_id, statistic_type)');
    } catch (PDOException $e) { /* Index may already exist */ }

    // Create masjids table
    $db->exec('
        CREATE TABLE IF NOT EXISTS masjids (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            latitude DOUBLE NOT NULL,
            longitude DOUBLE NOT NULL,
            radius_meters DOUBLE NOT NULL DEFAULT 500,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ');

    // Create indexes for masjids table
    try {
        $db->exec('CREATE INDEX idx_masjids_created_at ON masjids(created_at)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_masjids_name ON masjids(name)');
    } catch (PDOException $e) { /* Index may already exist */ }

    // Create special needs points table
    $db->exec('
        CREATE TABLE IF NOT EXISTS need_points (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            latitude DOUBLE NOT NULL,
            longitude DOUBLE NOT NULL,
            masjid_id INT NOT NULL,
            household_name VARCHAR(255) DEFAULT NULL,
            head_name VARCHAR(255) DEFAULT NULL,
            economic_status VARCHAR(50) DEFAULT NULL,
            is_verified TINYINT(1) NOT NULL DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (masjid_id) REFERENCES masjids(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ');

    // Create indexes for need_points table
    try {
        $db->exec('CREATE INDEX idx_need_points_masjid_id ON need_points(masjid_id)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_need_points_created_at ON need_points(created_at)');
    } catch (PDOException $e) { /* Index may already exist */ }

    // Create households table for poverty alleviation base data
    $db->exec("
        CREATE TABLE IF NOT EXISTS households (
            id INT AUTO_INCREMENT PRIMARY KEY,
            household_code VARCHAR(100) NOT NULL UNIQUE,
            head_name VARCHAR(255) NOT NULL,
            nik_hash VARCHAR(255) DEFAULT NULL,
            latitude DOUBLE NOT NULL,
            longitude DOUBLE NOT NULL,
            need_point_id INT DEFAULT NULL UNIQUE,
            masjid_id INT DEFAULT NULL,
            pontianak_area_id INT DEFAULT NULL,
            monthly_income DOUBLE NOT NULL DEFAULT 0,
            dependents_count INT NOT NULL DEFAULT 0,
            housing_condition_score TINYINT NOT NULL DEFAULT 3,
            employment_status ENUM('formal', 'informal', 'unemployed', 'daily_worker', 'micro_business', 'retired', 'unknown') NOT NULL DEFAULT 'unknown',
            economic_status ENUM('sangat_miskin', 'miskin', 'rentan', 'cukup', 'baik', 'unknown') NOT NULL DEFAULT 'unknown',
            verification_status ENUM('unverified', 'field_verified', 'admin_approved', 'rejected', 'needs_review') NOT NULL DEFAULT 'unverified',
            vulnerability_notes TEXT DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (need_point_id) REFERENCES need_points(id) ON DELETE SET NULL,
            FOREIGN KEY (masjid_id) REFERENCES masjids(id) ON DELETE SET NULL,
            FOREIGN KEY (pontianak_area_id) REFERENCES pontianak_areas(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    // Create indexes for households table
    try {
        $db->exec('CREATE INDEX idx_households_code ON households(household_code)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_households_verification_status ON households(verification_status)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_households_masjid_id ON households(masjid_id)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_households_area_id ON households(pontianak_area_id)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_households_created_at ON households(created_at)');
    } catch (PDOException $e) { /* Index may already exist */ }

    // Create assistance distributions table
    $db->exec("
        CREATE TABLE IF NOT EXISTS assistance_distributions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            household_id INT NOT NULL,
            assistance_type ENUM('pangan', 'tunai', 'kesehatan', 'pendidikan', 'usaha_mikro', 'lainnya') NOT NULL,
            assistance_value DOUBLE NOT NULL DEFAULT 0,
            assistance_frequency ENUM('sekali', 'mingguan', 'bulanan', 'triwulanan', 'tahunan', 'insidental') NOT NULL DEFAULT 'sekali',
            distribution_date DATE NOT NULL,
            delivery_status ENUM('pending', 'delivered', 'failed') NOT NULL DEFAULT 'pending',
            delivered_by VARCHAR(255) DEFAULT NULL,
            notes TEXT DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (household_id) REFERENCES households(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    // Create indexes for assistance distributions table
    try {
        $db->exec('CREATE INDEX idx_assistance_household_id ON assistance_distributions(household_id)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_assistance_date ON assistance_distributions(distribution_date)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_assistance_status ON assistance_distributions(delivery_status)');
    } catch (PDOException $e) { /* Index may already exist */ }

    // Create verification logs table
    $db->exec("
        CREATE TABLE IF NOT EXISTS verification_logs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            household_id INT NOT NULL,
            verification_status ENUM('field_verified', 'admin_approved', 'rejected', 'needs_review') NOT NULL,
            verifier_name VARCHAR(255) NOT NULL,
            verifier_role VARCHAR(100) DEFAULT NULL,
            field_note TEXT DEFAULT NULL,
            evidence_photo_count INT NOT NULL DEFAULT 0,
            data_confidence_score DOUBLE DEFAULT NULL,
            verified_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (household_id) REFERENCES households(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    // Create indexes for verification logs table
    try {
        $db->exec('CREATE INDEX idx_verification_household_id ON verification_logs(household_id)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_verification_status ON verification_logs(verification_status)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_verification_verified_at ON verification_logs(verified_at)');
    } catch (PDOException $e) { /* Index may already exist */ }

    // Create household scores table
    $db->exec("
        CREATE TABLE IF NOT EXISTS household_scores (
            id INT AUTO_INCREMENT PRIMARY KEY,
            household_id INT NOT NULL UNIQUE,
            skr DOUBLE NOT NULL DEFAULT 0,
            sag DOUBLE NOT NULL DEFAULT 0,
            spi DOUBLE NOT NULL DEFAULT 0,
            priority_level ENUM('very_high', 'high', 'medium', 'low') NOT NULL DEFAULT 'low',
            score_version VARCHAR(30) NOT NULL DEFAULT 'v1',
            components_json TEXT DEFAULT NULL,
            computed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (household_id) REFERENCES households(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    // Create indexes for household_scores table
    try {
        $db->exec('CREATE INDEX idx_household_scores_spi ON household_scores(spi)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_household_scores_priority ON household_scores(priority_level)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_household_scores_computed_at ON household_scores(computed_at)');
    } catch (PDOException $e) { /* Index may already exist */ }

    // Create score metadata table for versioning/tuning
    $db->exec('
        CREATE TABLE IF NOT EXISTS score_metadata (
            key_name VARCHAR(100) PRIMARY KEY,
            key_value TEXT NOT NULL,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ');

    // Create users table for authentication
    $db->exec("
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(100) NOT NULL UNIQUE,
            email VARCHAR(255) NOT NULL UNIQUE,
            password_hash VARCHAR(255) NOT NULL,
            role ENUM('Admin', 'Petugas', 'Pimpinan') NOT NULL DEFAULT 'Petugas',
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    // Create indexes for users table
    try {
        $db->exec('CREATE INDEX idx_users_email ON users(email)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_users_username ON users(username)');
    } catch (PDOException $e) { /* Index may already exist */ }
    try {
        $db->exec('CREATE INDEX idx_users_role ON users(role)');
    } catch (PDOException $e) { /* Index may already exist */ }

    // Initialize default admin user
    try {
        $adminCount = $db->query("SELECT COUNT(*) as cnt FROM users WHERE role = 'Admin'")->fetch(PDO::FETCH_ASSOC);
        if ($adminCount['cnt'] == 0) {
            $adminPassword = password_hash('admin123', PASSWORD_BCRYPT, ['cost' => 12]);
            $stmt = $db->prepare('INSERT INTO users (username, email, password_hash, role, is_active) VALUES (?, ?, ?, ?, 1)');
            $stmt->execute(['admin', 'admin@system.local', $adminPassword, 'Admin']);
        }
    } catch (PDOException $e) {
        // User may already exist
    }

    // Initialize default score metadata values
    try {
        $defaultMeta = [
            'score_version' => 'v1',
            'skr_weights' => '{"I":0.25,"E":0.20,"D":0.15,"H":0.15,"A":0.15,"S":0.10}',
            'sag_weights' => '{"J":0.40,"R":0.35,"T":0.25}',
            'spi_weights' => '{"SKR":0.60,"SAG_INV":0.30,"K":0.10}',
            'income_max_reference' => '5000000',
            'distance_max_reference' => '2000',
            'target_nasional' => '7.50',
            'population_reference' => '288315899'
        ];

        $metaStmt = $db->prepare('INSERT IGNORE INTO score_metadata (key_name, key_value) VALUES (?, ?)');
        foreach ($defaultMeta as $key => $value) {
            $metaStmt->execute([$key, $value]);
        }
    } catch (PDOException $e) {
        // Metadata init may already exist
    }
}

function haversineDistanceMeters($lat1, $lng1, $lat2, $lng2) {
    $earthRadius = 6371000; // meters

    $lat1Rad = deg2rad((float)$lat1);
    $lat2Rad = deg2rad((float)$lat2);
    $dLat = deg2rad((float)$lat2 - (float)$lat1);
    $dLng = deg2rad((float)$lng2 - (float)$lng1);

    $a = sin($dLat / 2) * sin($dLat / 2) +
        cos($lat1Rad) * cos($lat2Rad) * sin($dLng / 2) * sin($dLng / 2);
    $c = 2 * atan2(sqrt($a), sqrt(1 - $a));

    return $earthRadius * $c;
}

function normalizeMasjidRadius($radiusValue, &$error = null) {
    $error = null;

    if ($radiusValue === null || $radiusValue === '') {
        return 500.0;
    }

    if (!is_numeric($radiusValue)) {
        $error = 'Radius must be a numeric value';
        return null;
    }

    $radius = (float)$radiusValue;
    if ($radius < 50 || $radius > 5000) {
        $error = 'Radius must be between 50 and 5000 meters';
        return null;
    }

    return $radius;
}

function findNearestMasjidCoveringPoint($db, $latitude, $longitude) {
    $stmt = $db->query('SELECT id, name, latitude, longitude, radius_meters FROM masjids');
    $masjids = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $nearest = null;
    $nearestDistance = null;

    foreach ($masjids as $masjid) {
        $distance = haversineDistanceMeters(
            (float)$latitude,
            (float)$longitude,
            (float)$masjid['latitude'],
            (float)$masjid['longitude']
        );

        if ($distance <= (float)$masjid['radius_meters']) {
            if ($nearestDistance === null || $distance < $nearestDistance) {
                $nearest = $masjid;
                $nearestDistance = $distance;
            }
        }
    }

    if ($nearest === null) {
        return null;
    }

    $nearest['distance_meters'] = $nearestDistance;
    return $nearest;
}

function getNeedPointWithMasjid($db, $id) {
    $stmt = $db->prepare('
        SELECT
            np.id,
            np.name,
            np.latitude,
            np.longitude,
            np.masjid_id,
            np.household_name,
            np.head_name,
            np.economic_status,
            np.is_verified,
            np.created_at,
            m.name AS masjid_name,
            m.latitude AS masjid_latitude,
            m.longitude AS masjid_longitude,
            m.radius_meters
        FROM need_points np
        JOIN masjids m ON m.id = np.masjid_id
        WHERE np.id = ?
    ');
    $stmt->execute([$id]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

function cleanupNeedPointsOutsideMasjidCoverage($db, $masjidId) {
    $masjidStmt = $db->prepare('SELECT id, latitude, longitude, radius_meters FROM masjids WHERE id = ?');
    $masjidStmt->execute([$masjidId]);
    $masjid = $masjidStmt->fetch(PDO::FETCH_ASSOC);

    if (!$masjid) {
        return [
            'deleted_count' => 0,
            'deleted_ids' => []
        ];
    }

    $pointsStmt = $db->prepare('SELECT id, latitude, longitude FROM need_points WHERE masjid_id = ?');
    $pointsStmt->execute([$masjidId]);
    $points = $pointsStmt->fetchAll(PDO::FETCH_ASSOC);

    $idsToDelete = [];
    foreach ($points as $point) {
        $distance = haversineDistanceMeters(
            (float)$point['latitude'],
            (float)$point['longitude'],
            (float)$masjid['latitude'],
            (float)$masjid['longitude']
        );

        if ($distance > (float)$masjid['radius_meters']) {
            $idsToDelete[] = (int)$point['id'];
        }
    }

    if (!empty($idsToDelete)) {
        $placeholders = implode(',', array_fill(0, count($idsToDelete), '?'));
        $deleteStmt = $db->prepare("DELETE FROM need_points WHERE id IN ($placeholders)");
        $deleteStmt->execute($idsToDelete);
    }

    return [
        'deleted_count' => count($idsToDelete),
        'deleted_ids' => $idsToDelete
    ];
}

function validateCoordinates($latitude, $longitude, &$error = null) {
    $error = null;

    if (!is_numeric($latitude) || !is_numeric($longitude)) {
        $error = 'Latitude and longitude must be numeric values';
        return false;
    }

    $lat = (float)$latitude;
    $lng = (float)$longitude;

    if ($lat < -90 || $lat > 90) {
        $error = 'Latitude must be between -90 and 90';
        return false;
    }

    if ($lng < -180 || $lng > 180) {
        $error = 'Longitude must be between -180 and 180';
        return false;
    }

    return true;
}

function normalizeHouseholdCode($db, $inputCode = null) {
    $code = trim((string)($inputCode ?? ''));
    if ($code !== '') {
        return $code;
    }

    do {
        try {
            $random = strtoupper(substr(bin2hex(random_bytes(3)), 0, 6));
        } catch (Exception $e) {
            $random = strtoupper(substr(md5((string)mt_rand()), 0, 6));
        }

        $code = 'HH-' . date('Ymd') . '-' . $random;
        $checkStmt = $db->prepare('SELECT id FROM households WHERE household_code = ? LIMIT 1');
        $checkStmt->execute([$code]);
        $exists = $checkStmt->fetch(PDO::FETCH_ASSOC);
    } while ($exists);

    return $code;
}

function normalizeMonthlyIncome($value, &$error = null) {
    $error = null;
    if ($value === null || $value === '') {
        return 0.0;
    }

    if (!is_numeric($value)) {
        $error = 'Monthly income must be numeric';
        return null;
    }

    $income = (float)$value;
    if ($income < 0 || $income > 1000000000) {
        $error = 'Monthly income must be between 0 and 1000000000';
        return null;
    }

    return $income;
}

function normalizeDependentsCount($value, &$error = null) {
    $error = null;
    if ($value === null || $value === '') {
        return 0;
    }

    if (!is_numeric($value)) {
        $error = 'Dependents count must be numeric';
        return null;
    }

    $count = (int)$value;
    if ($count < 0 || $count > 50) {
        $error = 'Dependents count must be between 0 and 50';
        return null;
    }

    return $count;
}

function normalizeHousingConditionScore($value, &$error = null) {
    $error = null;
    if ($value === null || $value === '') {
        return 3;
    }

    if (!is_numeric($value)) {
        $error = 'Housing condition score must be numeric';
        return null;
    }

    $score = (int)$value;
    if ($score < 1 || $score > 5) {
        $error = 'Housing condition score must be between 1 and 5';
        return null;
    }

    return $score;
}

function normalizeEmploymentStatus($value, &$error = null) {
    $error = null;
    $allowed = ['formal', 'informal', 'unemployed', 'daily_worker', 'micro_business', 'retired', 'unknown'];

    if ($value === null || $value === '') {
        return 'unknown';
    }

    $status = strtolower(trim((string)$value));
    if (!in_array($status, $allowed, true)) {
        $error = 'Employment status is invalid';
        return null;
    }

    return $status;
}

function normalizeVerificationStatus($value, &$error = null, $allowUnverified = true) {
    $error = null;
    $allowed = ['field_verified', 'admin_approved', 'rejected', 'needs_review'];
    if ($allowUnverified) {
        $allowed[] = 'unverified';
    }

    if ($value === null || $value === '') {
        return $allowUnverified ? 'unverified' : 'needs_review';
    }

    $status = strtolower(trim((string)$value));
    if (!in_array($status, $allowed, true)) {
        $error = 'Verification status is invalid';
        return null;
    }

    return $status;
}

function normalizeAssistanceType($value, &$error = null) {
    $error = null;
    $allowed = ['pangan', 'tunai', 'kesehatan', 'pendidikan', 'usaha_mikro', 'lainnya'];

    if ($value === null || $value === '') {
        $error = 'Assistance type is required';
        return null;
    }

    $type = strtolower(trim((string)$value));
    if (!in_array($type, $allowed, true)) {
        $error = 'Assistance type is invalid';
        return null;
    }

    return $type;
}

function normalizeAssistanceFrequency($value, &$error = null) {
    $error = null;
    $allowed = ['sekali', 'mingguan', 'bulanan', 'triwulanan', 'tahunan', 'insidental'];

    if ($value === null || $value === '') {
        return 'sekali';
    }

    $frequency = strtolower(trim((string)$value));
    if (!in_array($frequency, $allowed, true)) {
        $error = 'Assistance frequency is invalid';
        return null;
    }

    return $frequency;
}

function normalizeDeliveryStatus($value, &$error = null) {
    $error = null;
    $allowed = ['pending', 'delivered', 'failed'];

    if ($value === null || $value === '') {
        return 'pending';
    }

    $status = strtolower(trim((string)$value));
    if (!in_array($status, $allowed, true)) {
        $error = 'Delivery status is invalid';
        return null;
    }

    return $status;
}

function normalizeAssistanceValue($value, &$error = null) {
    $error = null;
    if ($value === null || $value === '') {
        return 0.0;
    }

    if (!is_numeric($value)) {
        $error = 'Assistance value must be numeric';
        return null;
    }

    $assistanceValue = (float)$value;
    if ($assistanceValue < 0 || $assistanceValue > 1000000000) {
        $error = 'Assistance value must be between 0 and 1000000000';
        return null;
    }

    return $assistanceValue;
}

function normalizeEvidencePhotoCount($value, &$error = null) {
    $error = null;
    if ($value === null || $value === '') {
        return 0;
    }

    if (!is_numeric($value)) {
        $error = 'Evidence photo count must be numeric';
        return null;
    }

    $count = (int)$value;
    if ($count < 0 || $count > 100) {
        $error = 'Evidence photo count must be between 0 and 100';
        return null;
    }

    return $count;
}

function normalizeDataConfidenceScore($value, &$error = null) {
    $error = null;
    if ($value === null || $value === '') {
        return null;
    }

    if (!is_numeric($value)) {
        $error = 'Data confidence score must be numeric';
        return null;
    }

    $score = (float)$value;
    if ($score < 0 || $score > 1) {
        $error = 'Data confidence score must be between 0 and 1';
        return null;
    }

    return $score;
}

function normalizeNikHashFromInput($nikInput = null) {
    if ($nikInput === null) {
        return null;
    }

    $nik = trim((string)$nikInput);
    if ($nik === '') {
        return null;
    }

    return hash('sha256', $nik);
}

function householdExists($db, $householdId) {
    $stmt = $db->prepare('SELECT id FROM households WHERE id = ? LIMIT 1');
    $stmt->execute([(int)$householdId]);
    return (bool)$stmt->fetch(PDO::FETCH_ASSOC);
}

function getHouseholdById($db, $householdId) {
    $stmt = $db->prepare('
        SELECT
            h.*,
            np.name AS need_point_name,
            m.name AS masjid_name,
            pa.name AS area_name,
            (
                SELECT COUNT(*)
                FROM assistance_distributions ad
                WHERE ad.household_id = h.id
            ) AS assistance_count,
            (
                SELECT MAX(vl.verified_at)
                FROM verification_logs vl
                WHERE vl.household_id = h.id
            ) AS last_verified_at
        FROM households h
        LEFT JOIN need_points np ON np.id = h.need_point_id
        LEFT JOIN masjids m ON m.id = h.masjid_id
        LEFT JOIN pontianak_areas pa ON pa.id = h.pontianak_area_id
        WHERE h.id = ?
    ');
    $stmt->execute([(int)$householdId]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

function mapEconomicVulnerability($economicStatus) {
    $status = strtolower(trim((string)$economicStatus));
    $scores = [
        'sangat_miskin' => 1.0,
        'miskin' => 0.85,
        'rentan' => 0.65,
        'cukup' => 0.35,
        'baik' => 0.15,
        'unknown' => 0.5
    ];

    return isset($scores[$status]) ? $scores[$status] : 0.5;
}

function syncNeedPointsToHouseholds($db) {
    $updatedCount = 0;
    $insertedCount = 0;

    $updateStmt = $db->prepare("
        UPDATE households
        SET economic_status = (
            SELECT COALESCE(NULLIF(TRIM(np.economic_status), ''), 'unknown')
            FROM need_points np
            WHERE np.id = households.need_point_id
        ),
        updated_at = CURRENT_TIMESTAMP
        WHERE need_point_id IS NOT NULL
          AND (economic_status IS NULL OR TRIM(economic_status) = '')
    ");
    $updateStmt->execute();
    $updatedCount += $updateStmt->rowCount();

    $missingStmt = $db->query("
        SELECT
            np.id,
            np.name,
            np.latitude,
            np.longitude,
            np.masjid_id,
            np.head_name,
            np.economic_status,
            np.is_verified
        FROM need_points np
        LEFT JOIN households h ON h.need_point_id = np.id
        WHERE h.id IS NULL
        ORDER BY np.id ASC
    ");
    $missingRows = $missingStmt->fetchAll(PDO::FETCH_ASSOC);

    if (!empty($missingRows)) {
        $insertStmt = $db->prepare("
            INSERT INTO households (
                household_code,
                head_name,
                latitude,
                longitude,
                need_point_id,
                masjid_id,
                monthly_income,
                dependents_count,
                housing_condition_score,
                employment_status,
                economic_status,
                verification_status,
                vulnerability_notes
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");

        foreach ($missingRows as $row) {
            $pointId = (int)$row['id'];
            $householdCode = 'NP-' . str_pad((string)$pointId, 6, '0', STR_PAD_LEFT);
            $economicStatus = strtolower(trim((string)($row['economic_status'] ?? 'unknown')));
            if ($economicStatus === '') {
                $economicStatus = 'unknown';
            }
            $verificationStatus = ((int)($row['is_verified'] ?? 0) === 1) ? 'field_verified' : 'unverified';
            $headName = trim((string)($row['head_name'] ?? ''));
            if ($headName === '') {
                $headName = 'Kepala ' . $householdCode;
            }

            $insertStmt->execute([
                $householdCode,
                $headName,
                (float)$row['latitude'],
                (float)$row['longitude'],
                $pointId,
                !empty($row['masjid_id']) ? (int)$row['masjid_id'] : null,
                0,
                0,
                3,
                'unknown',
                $economicStatus,
                $verificationStatus,
                'Auto-synced from need_point #' . $pointId
            ]);
            $insertedCount++;
        }
    }

    return [
        'updated_count' => $updatedCount,
        'inserted_count' => $insertedCount,
        'total_changed' => $updatedCount + $insertedCount
    ];
}

function ensureHouseholdScoresForAllHouseholds($db) {
    $missingStmt = $db->query("
        SELECT h.*
        FROM households h
        LEFT JOIN household_scores hs ON hs.household_id = h.id
        WHERE hs.household_id IS NULL
        ORDER BY h.id ASC
    ");
    $missingHouseholds = $missingStmt->fetchAll(PDO::FETCH_ASSOC);

    $computed = 0;
    foreach ($missingHouseholds as $household) {
        $scoreData = computeHouseholdScores($db, $household);
        upsertHouseholdScore($db, $household['id'], $scoreData);
        $computed++;
    }

    return $computed;
}

function clamp01($value) {
    $v = (float)$value;
    if ($v < 0) return 0.0;
    if ($v > 1) return 1.0;
    return $v;
}

function normalizeToUnit($value, $minValue, $maxValue, $invert = false) {
    $minV = (float)$minValue;
    $maxV = (float)$maxValue;

    if ($maxV <= $minV) {
        return 0.0;
    }

    $normalized = ((float)$value - $minV) / ($maxV - $minV);
    $normalized = clamp01($normalized);

    if ($invert) {
        return 1.0 - $normalized;
    }

    return $normalized;
}

function getLatestAreaStatisticValue($db, $areaId, $statType) {
    if (!$areaId || !$statType) {
        return null;
    }

    $stmt = $db->prepare('
        SELECT value
        FROM area_statistics
        WHERE pontianak_area_id = ? AND statistic_type = ?
        ORDER BY data_date DESC, created_at DESC
        LIMIT 1
    ');
    $stmt->execute([(int)$areaId, (string)$statType]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$row) {
        return null;
    }

    return (float)$row['value'];
}

function getHouseholdDistanceToMasjidMeters($householdRow, $masjidRow) {
    if (!$householdRow || !$masjidRow) {
        return null;
    }

    return haversineDistanceMeters(
        (float)$householdRow['latitude'],
        (float)$householdRow['longitude'],
        (float)$masjidRow['latitude'],
        (float)$masjidRow['longitude']
    );
}

function mapEmploymentVulnerability($employmentStatus) {
    $status = strtolower(trim((string)$employmentStatus));
    $scores = [
        'unemployed' => 1.0,
        'daily_worker' => 0.85,
        'informal' => 0.7,
        'micro_business' => 0.55,
        'retired' => 0.4,
        'formal' => 0.25,
        'unknown' => 0.5
    ];

    return isset($scores[$status]) ? $scores[$status] : 0.5;
}

function mapShockFactor($verificationStatus) {
    $status = strtolower(trim((string)$verificationStatus));
    $map = [
        'needs_review' => 1.0,
        'unverified' => 0.7,
        'rejected' => 0.6,
        'field_verified' => 0.4,
        'admin_approved' => 0.2
    ];

    return isset($map[$status]) ? $map[$status] : 0.5;
}

function classifyPriorityLevel($spi) {
    $value = (float)$spi;
    if ($value >= 0.75) {
        return 'very_high';
    }
    if ($value >= 0.60) {
        return 'high';
    }
    if ($value >= 0.45) {
        return 'medium';
    }

    return 'low';
}

function computeHouseholdScores($db, $householdRow) {
    $income = (float)($householdRow['monthly_income'] ?? 0);
    $dependents = (int)($householdRow['dependents_count'] ?? 0);
    $housingScore = (int)($householdRow['housing_condition_score'] ?? 3);
    $employmentStatus = $householdRow['employment_status'] ?? 'unknown';
    $economicStatus = $householdRow['economic_status'] ?? 'unknown';
    $verificationStatus = $householdRow['verification_status'] ?? 'unverified';

    // SKR components (higher = more vulnerable)
    $economicVulnerability = mapEconomicVulnerability($economicStatus);        // primary poverty condition
    $incomeDeprivation = normalizeToUnit($income, 0, 5000000, true);          // supporting signal
    $employmentVulnerability = mapEmploymentVulnerability($employmentStatus);  // E
    $dependencyBurden = normalizeToUnit($dependents, 0, 6, false);            // D
    $housingVulnerability = normalizeToUnit($housingScore, 1, 5, true);       // H

    // A: accessibility deprivation (inverse from distance to masjid)
    $masjidDistanceMeters = null;
    $accessibilityService = 0.5;
    if (!empty($householdRow['masjid_id'])) {
        $masjidStmt = $db->prepare('SELECT latitude, longitude FROM masjids WHERE id = ?');
        $masjidStmt->execute([(int)$householdRow['masjid_id']]);
        $masjid = $masjidStmt->fetch(PDO::FETCH_ASSOC);

        if ($masjid) {
            $masjidDistanceMeters = getHouseholdDistanceToMasjidMeters($householdRow, $masjid);
            $distanceAccessScore = normalizeToUnit($masjidDistanceMeters, 0, 2000, true); // J as accessibility
            $accessibilityService = 1.0 - $distanceAccessScore; // convert to deprivation
        }
    }

    // S: shock factor from current verification confidence
    $shockFactor = mapShockFactor($verificationStatus);

    $skr =
        (0.40 * $economicVulnerability) +
        (0.18 * $incomeDeprivation) +
        (0.12 * $employmentVulnerability) +
        (0.10 * $dependencyBurden) +
        (0.10 * $housingVulnerability) +
        (0.06 * $accessibilityService) +
        (0.04 * $shockFactor);
    $skr = round(clamp01($skr), 6);

    // SAG components (higher = better accessibility)
    $J = 0.5;
    if ($masjidDistanceMeters !== null) {
        $J = normalizeToUnit($masjidDistanceMeters, 0, 2000, true);
    }

    $roadDensity = getLatestAreaStatisticValue($db, $householdRow['pontianak_area_id'] ?? null, 'road_density');
    $R = $roadDensity !== null ? normalizeToUnit($roadDensity, 0, 5, false) : 0.5;

    // T fallback because travel-time data is not available yet.
    $T = ($J + $R) / 2;

    $sag = (0.40 * $J) + (0.35 * $R) + (0.25 * $T);
    $sag = round(clamp01($sag), 6);

    $K = $shockFactor;
    $spi = (0.60 * $skr) + (0.30 * (1 - $sag)) + (0.10 * $K);
    $spi = round(clamp01($spi), 6);

    $priorityLevel = classifyPriorityLevel($spi);

    return [
        'skr' => $skr,
        'sag' => $sag,
        'spi' => $spi,
        'priority_level' => $priorityLevel,
        'score_version' => 'v1',
        'components' => [
            'ECO_economic_vulnerability' => round($economicVulnerability, 6),
            'I_income_deprivation' => round($incomeDeprivation, 6),
            'E_employment_vulnerability' => round($employmentVulnerability, 6),
            'D_dependency_burden' => round($dependencyBurden, 6),
            'H_housing_vulnerability' => round($housingVulnerability, 6),
            'A_accessibility_deprivation' => round($accessibilityService, 6),
            'S_shock_factor' => round($shockFactor, 6),
            'J_distance_access' => round($J, 6),
            'R_road_access' => round($R, 6),
            'T_time_access_proxy' => round($T, 6)
        ]
    ];
}

function upsertHouseholdScore($db, $householdId, $scoreData) {
    $stmt = $db->prepare('
        INSERT INTO household_scores (
            household_id,
            skr,
            sag,
            spi,
            priority_level,
            score_version,
            components_json,
            computed_at,
            updated_at
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
        ON DUPLICATE KEY UPDATE
            skr = VALUES(skr),
            sag = VALUES(sag),
            spi = VALUES(spi),
            priority_level = VALUES(priority_level),
            score_version = VALUES(score_version),
            components_json = VALUES(components_json),
            computed_at = CURRENT_TIMESTAMP,
            updated_at = CURRENT_TIMESTAMP
    ');

    $stmt->execute([
        (int)$householdId,
        (float)$scoreData['skr'],
        (float)$scoreData['sag'],
        (float)$scoreData['spi'],
        (string)$scoreData['priority_level'],
        (string)$scoreData['score_version'],
        json_encode($scoreData['components'])
    ]);
}

// Authentication helper functions
function loginUser($email, $password) {
    try {
        $db = getDB();
        $stmt = $db->prepare('SELECT id, username, email, password_hash, role, is_active FROM users WHERE email = ? AND is_active = 1');
        $stmt->execute([$email]);
        $user = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$user) {
            return ['success' => false, 'message' => 'User not found or inactive'];
        }

        if (!password_verify($password, $user['password_hash'])) {
            return ['success' => false, 'message' => 'Invalid password'];
        }

        // Set session
        $_SESSION['user_id'] = $user['id'];
        $_SESSION['username'] = $user['username'];
        $_SESSION['email'] = $user['email'];
        $_SESSION['role'] = $user['role'];
        $_SESSION['login_time'] = time();

        return [
            'success' => true,
            'message' => 'Login successful',
            'user' => [
                'id' => $user['id'],
                'username' => $user['username'],
                'email' => $user['email'],
                'role' => $user['role']
            ]
        ];
    } catch (PDOException $e) {
        return ['success' => false, 'message' => 'Database error: ' . $e->getMessage()];
    }
}

function logoutUser() {
    session_destroy();
    return ['success' => true, 'message' => 'Logout successful'];
}

function getCurrentUser() {
    if (!isset($_SESSION['user_id'])) {
        return null;
    }

    return [
        'id' => $_SESSION['user_id'],
        'username' => $_SESSION['username'],
        'email' => $_SESSION['email'],
        'role' => $_SESSION['role']
    ];
}

function isUserLoggedIn() {
    return isset($_SESSION['user_id']) && !empty($_SESSION['user_id']);
}

function requireLogin() {
    if (!isUserLoggedIn()) {
        http_response_code(401);
        die(json_encode(['error' => 'Unauthorized - Please login']));
    }
}

function requireRole($allowedRoles) {
    requireLogin();
    
    if (!is_array($allowedRoles)) {
        $allowedRoles = [$allowedRoles];
    }

    if (!in_array($_SESSION['role'], $allowedRoles)) {
        http_response_code(403);
        die(json_encode(['error' => 'Forbidden - Insufficient permissions']));
    }
}

function createUser($username, $email, $password, $role = 'Petugas') {
    try {
        $db = getDB();
        
        // Check if user already exists
        $stmt = $db->prepare('SELECT id FROM users WHERE email = ? OR username = ?');
        $stmt->execute([$email, $username]);
        if ($stmt->fetch()) {
            return ['success' => false, 'message' => 'User already exists'];
        }

        $passwordHash = password_hash($password, PASSWORD_BCRYPT, ['cost' => 12]);
        
        $stmt = $db->prepare('
            INSERT INTO users (username, email, password_hash, role, is_active)
            VALUES (?, ?, ?, ?, 1)
        ');
        $stmt->execute([$username, $email, $passwordHash, $role]);

        return [
            'success' => true,
            'message' => 'User created successfully',
            'user_id' => $db->lastInsertId()
        ];
    } catch (PDOException $e) {
        return ['success' => false, 'message' => 'Database error: ' . $e->getMessage()];
    }
}
?>