<?php
require_once "Database.php";

class Record {

    protected $conn;

    public function __construct(){
        $db = new Database();
        $this->conn = $db->connect();
    }

    /* FETCH RECORDS WITH FILTER + SEARCH */
    public function getRecords($status="",$search="",$page=1,$perPage=10,$sort="id",$order="desc")
    {

        $conditions = [];

        // status filter
        if($status=="trash"){
            $conditions[]="is_deleted=1";
        }else{
            $conditions[]="is_deleted=0";
            if($status!=""){
                $status = $this->conn->real_escape_string($status);
                $conditions[]="status='$status'";
            }
        }

        // search filter
        if($search!=""){
            $search = $this->conn->real_escape_string($search);
            $conditions[]="(
                CAST(id AS CHAR) LIKE '%$search%' OR
                LOWER(name) LIKE LOWER('%$search%') OR
                LOWER(email) LIKE LOWER('%$search%') OR
                CAST(phone AS CHAR) LIKE '%$search%'
            )";
        }

        $where = count($conditions)>0 ? "WHERE ".implode(" AND ",$conditions):"";

        $start = ($page - 1) * $perPage;

         $allowedSort = ["id","name","email","status"];

            if(!in_array($sort,$allowedSort))
                {
                    $sort="id";
                }

        $order = strtolower($order)=="asc" ? "ASC":"DESC";

        if($sort == "status"){
    if($order == "ASC"){
        // Inactive first, then Active
        $orderBy = "FIELD(status,'inactive','active'), id DESC";
    }else{
        // Active first, then Inactive
        $orderBy = "FIELD(status,'active','inactive'), id DESC";
    }
}else{
    $orderBy = "$sort $order";
}


        $sql="SELECT * FROM records $where ORDER BY $orderBy LIMIT $perPage OFFSET $start";


        return $this->conn->query($sql);

    }

    /* TOTAL PAGES */
public function countFiltered($status="",$search=""){

    $conditions = [];

    if($status=="trash"){
        $conditions[]="is_deleted=1";
    }else{
        $conditions[]="is_deleted=0";
        if($status!=""){
            $status = $this->conn->real_escape_string($status);
            $conditions[]="status='$status'";
        }
    }

    if($search!=""){
        $search = $this->conn->real_escape_string($search);
        $conditions[]="(
            CAST(id AS CHAR) LIKE '%$search%' OR
            LOWER(name) LIKE LOWER('%$search%') OR
            LOWER(email) LIKE LOWER('%$search%') OR
            CAST(phone AS CHAR) LIKE '%$search%'
        )";
    }

    $where = count($conditions)>0 ? "WHERE ".implode(" AND ",$conditions):"";

    $result = $this->conn->query("SELECT COUNT(*) as total FROM records $where");
    return $result->fetch_assoc()['total'];
}


    /* SOFT DELETE */
    public function deleteRecord($id){
        $id = $this->conn->real_escape_string($id);
        return $this->conn->query("UPDATE records SET is_deleted=1 WHERE id='$id'");
    }

    /* RESTORE RECORD */
    public function restoreRecord($id){
        $id = $this->conn->real_escape_string($id);
        return $this->conn->query("UPDATE records SET is_deleted=0 WHERE id='$id'");
    }

    /* BULK DELETE */
    public function bulkDelete($ids){
        foreach($ids as $id){
            $this->deleteRecord($id);
        }
    }

    /* TOTAL RECORDS */
public function countTotal(){
    $result = $this->conn->query("SELECT COUNT(*) as total FROM records WHERE is_deleted=0");
    return $result->fetch_assoc()['total'];
}

/* ACTIVE RECORDS */
public function countActive(){
    $result = $this->conn->query("SELECT COUNT(*) as active FROM records WHERE status='active' AND is_deleted=0");
    return $result->fetch_assoc()['active'];
}

/* INACTIVE / PENDING RECORDS */
public function countInactive(){
    $result = $this->conn->query("SELECT COUNT(*) as inactive FROM records WHERE status='inactive' AND is_deleted=0");
    return $result->fetch_assoc()['inactive'];
}


}
?>