<?php
/**
 * Model Siswa
 */
class SiswaModel {
    private $db;

    public function __construct() {
        $this->db = getDB();
    }

    public function getAll($search = '', $kelasId = '', $status = '', $limit = 15, $offset = 0) {
        $where = ['1=1'];
        $params = [];
        $types = '';

        if ($search) {
            $where[] = '(s.nama LIKE ? OR s.nis LIKE ? OR s.nisn LIKE ? OR s.nik LIKE ?)';
            $like = '%' . $search . '%';
            $params = array_merge($params, [$like, $like, $like, $like]);
            $types .= 'ssss';
        }
        if ($kelasId) {
            $where[] = 's.kelas_id = ?';
            $params[] = $kelasId;
            $types .= 'i';
        }
        if ($status) {
            $where[] = 's.status = ?';
            $params[] = $status;
            $types .= 's';
        }

        $whereStr = implode(' AND ', $where);
        $sql = "SELECT s.*, k.nama_kelas FROM siswa s
                LEFT JOIN kelas k ON s.kelas_id = k.id
                WHERE $whereStr
                ORDER BY s.nama ASC
                LIMIT ? OFFSET ?";
        $params[] = $limit;
        $params[] = $offset;
        $types .= 'ii';

        $stmt = $this->db->prepare($sql);
        if ($params) $stmt->bind_param($types, ...$params);
        $stmt->execute();
        return stmt_get_result($stmt)->fetch_all(MYSQLI_ASSOC);
    }

    public function countAll($search = '', $kelasId = '', $status = '') {
        $where = ['1=1'];
        $params = [];
        $types = '';

        if ($search) {
            $where[] = '(s.nama LIKE ? OR s.nis LIKE ? OR s.nisn LIKE ? OR s.nik LIKE ?)';
            $like = '%' . $search . '%';
            $params = array_merge($params, [$like, $like, $like, $like]);
            $types .= 'ssss';
        }
        if ($kelasId) {
            $where[] = 's.kelas_id = ?';
            $params[] = $kelasId;
            $types .= 'i';
        }
        if ($status) {
            $where[] = 's.status = ?';
            $params[] = $status;
            $types .= 's';
        }

        $whereStr = implode(' AND ', $where);
        $sql = "SELECT COUNT(*) as total FROM siswa s WHERE $whereStr";
        $stmt = $this->db->prepare($sql);
        if ($params) $stmt->bind_param($types, ...$params);
        $stmt->execute();
        $row = stmt_get_result($stmt)->fetch_assoc();
        return (int)($row['total'] ?? 0);
    }

    public function getById($id) {
        $stmt = $this->db->prepare(
            "SELECT s.*, k.nama_kelas FROM siswa s
             LEFT JOIN kelas k ON s.kelas_id = k.id
             WHERE s.id = ?"
        );
        $stmt->bind_param('i', $id);
        $stmt->execute();
        return stmt_get_result($stmt)->fetch_assoc();
    }

    public function getAlamat($siswaId) {
        $stmt = $this->db->prepare("SELECT * FROM alamat_siswa WHERE siswa_id = ?");
        $stmt->bind_param('i', $siswaId);
        $stmt->execute();
        return stmt_get_result($stmt)->fetch_assoc();
    }

    public function getOrangTua($siswaId) {
        $stmt = $this->db->prepare("SELECT * FROM orang_tua WHERE siswa_id = ?");
        $stmt->bind_param('i', $siswaId);
        $stmt->execute();
        return stmt_get_result($stmt)->fetch_assoc();
    }

    public function getWali($siswaId) {
        $stmt = $this->db->prepare("SELECT * FROM wali WHERE siswa_id = ?");
        $stmt->bind_param('i', $siswaId);
        $stmt->execute();
        return stmt_get_result($stmt)->fetch_assoc();
    }

    public function insert($data) {
        $stmt = $this->db->prepare(
            "INSERT INTO siswa (no, nis, nisn, nik, nama, jenis_kelamin, kelas_id, agama,
             tempat_lahir, tanggal_lahir, anak_ke, tgl_masuk, asal_sekolah, status, foto)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
        );
        $stmt->bind_param('isssssssssiisss',
            $data['no'], $data['nis'], $data['nisn'], $data['nik'],
            $data['nama'], $data['jenis_kelamin'], $data['kelas_id'],
            $data['agama'], $data['tempat_lahir'], $data['tanggal_lahir'],
            $data['anak_ke'], $data['tgl_masuk'], $data['asal_sekolah'],
            $data['status'], $data['foto']
        );
        $stmt->execute();
        return $this->db->insert_id;
    }

    public function update($id, $data) {
        $stmt = $this->db->prepare(
            "UPDATE siswa SET no=?, nis=?, nisn=?, nik=?, nama=?, jenis_kelamin=?,
             kelas_id=?, agama=?, tempat_lahir=?, tanggal_lahir=?, anak_ke=?,
             tgl_masuk=?, asal_sekolah=?, status=?
             WHERE id=?"
        );
        $stmt->bind_param('isssssssssiissi',
            $data['no'], $data['nis'], $data['nisn'], $data['nik'],
            $data['nama'], $data['jenis_kelamin'], $data['kelas_id'],
            $data['agama'], $data['tempat_lahir'], $data['tanggal_lahir'],
            $data['anak_ke'], $data['tgl_masuk'], $data['asal_sekolah'],
            $data['status'], $id
        );
        return $stmt->execute();
    }

    public function updateFoto($id, $foto) {
        $stmt = $this->db->prepare("UPDATE siswa SET foto=? WHERE id=?");
        $stmt->bind_param('si', $foto, $id);
        return $stmt->execute();
    }

    public function delete($id) {
        $stmt = $this->db->prepare("DELETE FROM siswa WHERE id=?");
        $stmt->bind_param('i', $id);
        return $stmt->execute();
    }

    public function upsertAlamat($siswaId, $data) {
        $existing = $this->getAlamat($siswaId);
        if ($existing) {
            $stmt = $this->db->prepare(
                "UPDATE alamat_siswa SET alamat=?, rt=?, rw=?, dusun=?, kelurahan=?,
                 kecamatan=?, kabupaten=?, provinsi=?, kode_pos=? WHERE siswa_id=?"
            );
            $stmt->bind_param('sssssssssi',
                $data['alamat'], $data['rt'], $data['rw'], $data['dusun'],
                $data['kelurahan'], $data['kecamatan'], $data['kabupaten'],
                $data['provinsi'], $data['kode_pos'], $siswaId
            );
        } else {
            $stmt = $this->db->prepare(
                "INSERT INTO alamat_siswa (siswa_id, alamat, rt, rw, dusun, kelurahan,
                 kecamatan, kabupaten, provinsi, kode_pos)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
            );
            $stmt->bind_param('isssssssss',
                $siswaId, $data['alamat'], $data['rt'], $data['rw'], $data['dusun'],
                $data['kelurahan'], $data['kecamatan'], $data['kabupaten'],
                $data['provinsi'], $data['kode_pos']
            );
        }
        return $stmt->execute();
    }

    public function upsertOrangTua($siswaId, $data) {
        $existing = $this->getOrangTua($siswaId);
        if ($existing) {
            $stmt = $this->db->prepare(
                "UPDATE orang_tua SET nama_ayah=?, nama_ibu=?, pekerjaan_ayah=?,
                 pekerjaan_ibu=?, alamat_orang_tua=?, no_hp_ayah=?, no_hp_ibu=?, penghasilan=?
                 WHERE siswa_id=?"
            );
            $stmt->bind_param('ssssssssi',
                $data['nama_ayah'], $data['nama_ibu'], $data['pekerjaan_ayah'],
                $data['pekerjaan_ibu'], $data['alamat_orang_tua'],
                $data['no_hp_ayah'], $data['no_hp_ibu'], $data['penghasilan'], $siswaId
            );
        } else {
            $stmt = $this->db->prepare(
                "INSERT INTO orang_tua (siswa_id, nama_ayah, nama_ibu, pekerjaan_ayah,
                 pekerjaan_ibu, alamat_orang_tua, no_hp_ayah, no_hp_ibu, penghasilan)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
            );
            $stmt->bind_param('issssssss',
                $siswaId, $data['nama_ayah'], $data['nama_ibu'], $data['pekerjaan_ayah'],
                $data['pekerjaan_ibu'], $data['alamat_orang_tua'],
                $data['no_hp_ayah'], $data['no_hp_ibu'], $data['penghasilan']
            );
        }
        return $stmt->execute();
    }

    public function upsertWali($siswaId, $data) {
        $existing = $this->getWali($siswaId);
        if ($existing) {
            $stmt = $this->db->prepare(
                "UPDATE wali SET nama_wali=?, alamat_wali=?, pekerjaan_wali=?,
                 no_hp_wali=?, hubungan=? WHERE siswa_id=?"
            );
            $stmt->bind_param('sssssi',
                $data['nama_wali'], $data['alamat_wali'], $data['pekerjaan_wali'],
                $data['no_hp_wali'], $data['hubungan'], $siswaId
            );
        } else {
            $stmt = $this->db->prepare(
                "INSERT INTO wali (siswa_id, nama_wali, alamat_wali, pekerjaan_wali, no_hp_wali, hubungan)
                 VALUES (?, ?, ?, ?, ?, ?)"
            );
            $stmt->bind_param('isssss',
                $siswaId, $data['nama_wali'], $data['alamat_wali'],
                $data['pekerjaan_wali'], $data['no_hp_wali'], $data['hubungan']
            );
        }
        return $stmt->execute();
    }

    public function countByStatus() {
        $rows = query_fetch_all($this->db,
            "SELECT status, COUNT(*) as total FROM siswa GROUP BY status"
        );
        $data = [];
        foreach ($rows as $row) {
            $data[$row['status']] = $row['total'];
        }
        return $data;
    }

    public function countByKelas() {
        return query_fetch_all($this->db,
            "SELECT k.nama_kelas, COUNT(s.id) as total
             FROM kelas k LEFT JOIN siswa s ON k.id = s.kelas_id AND s.status = 'Aktif'
             GROUP BY k.id ORDER BY k.nama_kelas"
        );
    }

    public function getAllForExport($kelasId = '', $status = '') {
        $where  = ['1=1'];
        $params = [];
        $types  = '';

        if ($kelasId) {
            $where[]  = 's.kelas_id = ?';
            $params[] = $kelasId;
            $types   .= 'i';
        }
        if ($status) {
            $where[]  = 's.status = ?';
            $params[] = $status;
            $types   .= 's';
        }

        $whereStr = implode(' AND ', $where);
        $sql = "SELECT s.*,
                kk.nama_kelas,
                a.alamat, a.rt, a.rw, a.dusun, a.kelurahan, a.kecamatan,
                o.nama_ayah, o.nama_ibu, o.pekerjaan_ayah, o.pekerjaan_ibu, o.alamat_orang_tua,
                w.nama_wali, w.alamat_wali, w.pekerjaan_wali
                FROM siswa s
                LEFT JOIN kelas kk       ON s.kelas_id = kk.id
                LEFT JOIN alamat_siswa a ON s.id = a.siswa_id
                LEFT JOIN orang_tua o    ON s.id = o.siswa_id
                LEFT JOIN wali w         ON s.id = w.siswa_id
                WHERE $whereStr
                ORDER BY kk.nama_kelas, s.nama";

        $stmt = $this->db->prepare($sql);
        if ($params) $stmt->bind_param($types, ...$params);
        $stmt->execute();
        return stmt_get_result($stmt)->fetch_all(MYSQLI_ASSOC);
    }
}
