SWDB/application/API/v3/units/GET.php

<?php
    /**
     * Unit getter script.
     *
     * Exposes an API to rerieve unit info.
     *
     * Optional query parameters are:
     *  - id: Unit ID.
     *
     * @category API
     */

    global $db;

    /**
     * Gets info on a unit.
     *
     * @param resource $db Database connection.
     * @param int $id Unit identifier
     * @return mixed[] Content.
     */
    function get_unit($db, $id){
        $result = [
            "status" => 404,
            "message" => "Unit not found",
            "content" => ""
        ];
        $statement = $db->prepare("
          SELECT
            k_unit.id AS id,
            k_unit.name AS name,
            k_unit.family AS family,
            k_unit.element AS element,
            k_element.name AS element_name,
            k_unit.archetype AS archetype,
            k_archetype.name AS archetype_name,
            k_unit.base_stars AS base_stars,
            k_unit.natural_stars AS natural_stars,
            k_unit.obtainable AS obtainable,
            k_unit.awakens_from AS awakens_from,
            k_unit.awakens_to AS awakens_to,
            k_unit.homunculus AS homunculus,
            k_unit.hp AS hp,
            k_unit.attack AS attack,
            k_unit.defense AS defense,
            k_unit.speed AS speed,
            k_unit.crit_rate AS crit_rate,
            k_unit.crit_damage AS crit_damage,
            k_unit.accuracy AS accuracy,
            k_unit.resistance AS resistance,
            k_unit.leader_skill AS leader_skill
          FROM
            k_unit,
            k_element,
            k_archetype
          WHERE
            k_unit.element = k_element.id AND
            k_unit.archetype = k_archetype.id AND
            k_unit.id = :id ;
        ");
        $statement->bindValue(":id", $id, SQLITE3_INTEGER);
        $q = $statement->execute();
        $r = $q->fetchArray(SQLITE3_ASSOC);
        if ($r){
            $unit = [
                "id" => $r["id"],
                "url" => URL::BASE . "API/v3/units/" . $r["id"] . "/",
                "name" => $r["name"],
                "family" => $r["family"],
                "image" => APPLICATION::img("UNIT", $r["id"]),
                "element" => $r["element"],
                "element_name" => $r["element_name"],
                "archetype" => $r["archetype"],
                "archetype_name" => $r["archetype_name"],
                "stars" => $r["base_stars"],
                "natural_stars" => $r["natural_stars"],
                "obtainable" => (($r["obtainable"] == 1) ? (true) : (false)),
                "awakens_from" => $r["awakens_from"],
                "awakens_to" => $r["awakens_to"],
                "homunculus" => (($r["homunculus"] == 1) ? (true) : (false)),
                "hp" => $r["hp"],
                "attack" => $r["attack"],
                "defense" => $r["defense"],
                "speed" => $r["speed"],
                "critical_rate" => $r["crit_rate"],
                "critical_damage" => $r["crit_damage"],
                "accuracy" => $r["accuracy"],
                "resistance" => $r["resistance"],
                "leader_skill" => null,
                "skills" => [],
            ];

            // Leader_skill
            if ($r["leader_skill"] != null){
                $statement_leader = $db->prepare("
                SELECT
                    k_leader_skill.id AS id,
                    k_leader_skill.stat AS stat,
                    k_stat.name AS stat_name,
                    k_leader_skill.amount AS amount,
                    k_leader_skill.area AS area,
                    k_effect_area.name AS area_name,
                    k_leader_skill.element AS element,
                    k_element.name AS element_name
                FROM
                    k_leader_skill,
                    k_stat,
                    k_effect_area,
                    k_element
                WHERE
                    k_leader_skill.id = :id AND
                    k_leader_skill.stat = k_stat.id AND
                    k_leader_skill.area = k_effect_area.id AND
                    k_leader_skill.element = k_element.id;
                ");
                $statement_leader->bindValue(":id", $r["leader_skill"], SQLITE3_INTEGER);
                $q_leader = $statement_leader->execute();
                $r_leader = $q_leader->fetchArray(SQLITE3_ASSOC);
                if ($r_leader){
                    $description = "Increases the ";
                    switch ($r_leader["stat"]){
                        case STAT_ID::ATK:
                            $description .= "attack power";
                            break;
                        case STAT_ID::DEF:
                            $description .= "defense";
                            break;
                        case STAT_ID::HP:
                            $description .= "HP";
                            break;
                        case STAT_ID::SPD:
                            $description .= "attack speed";
                            break;
                        case STAT_ID::CRIT_RATE:
                            $description .= "critical rate";
                            break;
                        case STAT_ID::CRIT_DMG:
                            $description .= "critical damage";
                            break;
                        case STAT_ID::ACCURACY:
                            $description .= "accuracy";
                            break;
                        case STAT_ID::RESIST:
                            $description .= "resistance";
                            break;
                    }
                    $description .= " of ally monsters ";
                    switch ($r_leader["element"]){
                        case ELEMENT_ID::WATER:
                            $description .= " with Water attribute ";
                            break;
                        case ELEMENT_ID::FIRE:
                            $description .= " with Fire attribute ";
                            break;
                        case ELEMENT_ID::WIND:
                            $description .= " with Wind attribute ";
                            break;
                        case ELEMENT_ID::LIGHT:
                            $description .= " with Light attribute ";
                            break;
                        case ELEMENT_ID::DARK:
                            $description .= " with Dark attribute ";
                            break;
                    }
                    $description = $description . "by " . $r_leader["amount"] . "%";
                    switch ($r_leader["area"]){
                        case EFFECT_AREA_ID::DUNGEON:
                            $description .= " in the Dungeons";
                            break;
                        case EFFECT_AREA_ID::ARENA:
                            $description .= " in the Arena";
                            break;
                        case EFFECT_AREA_ID::GUILD:
                            $description .= " in Guild content";
                            break;
                    }
                    $description = str_replace("  ", " ", $description) . ".";
                    $unit["leader_skill"] = [
                        "id" => $r_leader["id"],
                        "stat" => $r_leader["stat"],
                        "stat_name" => $r_leader["stat_name"],
                        "increase" => $r_leader["amount"],
                        "area" => $r_leader["area"],
                        "area_name" => $r_leader["area_name"],
                        "element" => $r_leader["element"],
                        "element_name" => $r_leader["element_name"],
                        "description" => $description
                    ];
                }
            }

            // Skill
            $statement_skills = $db->prepare("
              SELECT
                k_skill.id AS id,
                k_skill.slot AS slot,
                k_skill.name AS name,
                k_skill.description AS description,
                k_skill.passive AS passive,
                k_skill.cooltime AS cooltime,
                k_skill.hits AS hits,
                k_skill.aoe AS aoe,
                k_skill.multiplier_formula AS multiplier_formula,
                k_skill.max_level AS max_level
              FROM
                k_skill,
                k_unit_skill
              WHERE
                k_skill.id = k_unit_skill.skill AND
                k_unit_skill.unit = :id 
              ORDER BY slot;
            ");
            $statement_skills->bindValue(":id", $id, SQLITE3_INTEGER);
            $q_skills = $statement_skills->execute();
            while ($r_skills = $q_skills->fetchArray(SQLITE3_ASSOC)){
                $skill = [
                    "id" => $r_skills["id"],
                    "slot" => $r_skills["slot"],
                    "name" => $r_skills["name"],
                    "description" => $r_skills["description"],
                    "image" => APPLICATION::img("SKILL", $r_skills["id"]),
                    "passive" => (($r_skills["passive"] == 1) ? (true) : (false)),
                    "cooltime" => $r_skills["cooltime"],
                    "hits" => $r_skills["hits"],
                    "aoe" => (($r_skills["aoe"] == 1) ? (true) : (false)),
                    "effects" => [],
                    "max_level" => $r_skills["max_level"],
                    "upgrades" => [],
                    "formula" => $r_skills["multiplier_formula"]
                ];
                // Skill levels
                $statement_skill_levels = $db->prepare("
                  SELECT
                    level,
                    description
                  FROM k_skill_level
                  WHERE
                    level > 1 AND
                    skill = :id
                  ORDER BY level;
                ");
                $statement_skill_levels->bindValue(":id", $r_skills["id"], SQLITE3_INTEGER);
                $q_skill_levels = $statement_skill_levels->execute();
                while ($r_skill_levels = $q_skill_levels->fetchArray(SQLITE3_ASSOC)){
                    $level = [
                        "level" => $r_skill_levels["level"],
                        "description" => $r_skill_levels["description"]
                    ];
                    array_push($skill["upgrades"], $level);
                }
                // Skill efffects
                $statement_skill_effects = $db->prepare("
                  SELECT
                    k_effect.id AS id,
                    k_effect.is_buff AS is_buff,
                    k_effect.name AS name,
                    k_effect.description AS description
                  FROM
                    k_effect,
                    k_skill_effect
                  WHERE
                    k_skill_effect.skill = :id AND
                    k_skill_effect.effect = k_effect.id
                ");
                $statement_skill_effects->bindValue(":id", $r_skills["id"], SQLITE3_INTEGER);
                $q_skill_effects = $statement_skill_effects->execute();
                while ($r_skill_effects = $q_skill_effects->fetchArray(SQLITE3_ASSOC)){
                    $effect = [
                        "id" => $r_skill_effects["id"],
                        "buff" => (($r_skill_effects["is_buff"] == 1) ? (true) : (false)),
                        "name" => $r_skill_effects["name"],
                        "description" => $r_skill_effects["description"],
                        "image" => APPLICATION::img("EFFECT", $r_skill_effects["id"]),
                    ];
                    array_push($skill["effects"], $effect);
                }
                array_push($unit["skills"], $skill);
            }

            // Add to array
            $result["content"] = $unit;
            $result["status"] = 200;
            $result["message"] = "Success.";
        }
        return $result;
    }

    /**
     * Gets a list of units, paginated.
     *
     * @param resource $db Database connection.
     * @return mixed[] Content.
     */
    function get_unit_list($db){
        $result = [
            "status" => 200,
            "message" => "Success.",
            "content" => ""
        ];

        // Get filters
        $parameters = "";
        $filters = [
            "page" => 1,
            "per_page" => 30,
            "name" => "",
            "element" => "",
            "archetype" => "",
            "stars" => "",
            "stars_lte" => "",
            "stars_gte" => "",
        ];

        foreach (array_keys($filters) as $filter_key){
            $filter = filter_input(INPUT_GET, $filter_key);
            if ($filter != null && $filter !== false){
                $filters[$filter_key] = $filter;
                // Also, build the new url parameters
                if ($filter_key != "page"){
                    $parameters .= ("&" . $filter_key . "=" . $filter);
                }
            }
        }

        // Fix some filters
        if (intval($filters["page"]) < 1){
            $filters["page"] = 1;
        }
        if (intval($filters["per_page"]) < 1){
            $filters["per_page"] = 1;
        }
        elseif (intval($filters["per_page"]) > 100){
            $filters["per_page"] = 100;
        }

        // Build the where clause
        $where = "
          k_unit.element = k_element.id AND
          k_unit.archetype = k_archetype.id
        ";
        if (strlen($filters["name"]) > 0){
            $where .= " AND upper(k_unit.name) LIKE upper(:name) ";
        }
        if (strlen($filters["element"]) > 0){
            $where .= " AND (k_element.id = :element OR upper(k_element.name) = upper(:element)) ";
        }
        if (strlen($filters["archetype"]) > 0){
            $where .= " AND (k_archetype.id = :archetype OR upper(k_archetype.name) = upper(:archetype)) ";
        }
        if (strlen($filters["stars"]) > 0){
            $where .= " AND k_unit.base_stars = :stars ";
        }
        if (strlen($filters["stars_lte"]) > 0){
            $where .= " AND k_unit.base_stars <= :stars_lte ";
        }
        if (strlen($filters["stars_gte"]) > 0){
            $where .= " AND k_unit.base_stars >= :stars_gte ";
        }

        // Get total
        $statement = $db->prepare("
          SELECT count(k_unit.id) AS c
          FROM
            k_unit,
            k_element,
            k_archetype
          WHERE $where ;
        ");
        $statement->bindValue(":name", "%" . $filters["name"] . "%", SQLITE3_TEXT);
        $statement->bindValue(":element", $filters["element"], SQLITE3_TEXT);
        $statement->bindValue(":archetype", $filters["archetype"], SQLITE3_TEXT);
        $statement->bindValue(":stars", $filters["stars"], SQLITE3_INTEGER);
        $statement->bindValue(":stars_gte", $filters["stars_gte"], SQLITE3_INTEGER);
        $statement->bindValue(":stars_lte", $filters["stars_lte"], SQLITE3_INTEGER);
        $total = $statement->execute()->fetchArray(SQLITE3_ASSOC)["c"];

        // Selet all units
        $statement = $db->prepare("
          SELECT k_unit.id AS id
          FROM
            k_unit,
            k_element,
            k_archetype
          WHERE $where
          LIMIT :limit
          OFFSET :offset ;
        ");
        $statement->bindValue(":name", "%" . $filters["name"] . "%", SQLITE3_TEXt);
        $statement->bindValue(":element", $filters["element"], SQLITE3_TEXT);
        $statement->bindValue(":archetype", $filters["archetype"], SQLITE3_TEXT);
        $statement->bindValue(":stars", $filters["stars"], SQLITE3_INTEGER);
        $statement->bindValue(":stars_gte", $filters["stars_gte"], SQLITE3_INTEGER);
        $statement->bindValue(":stars_lte", $filters["stars_lte"], SQLITE3_INTEGER);
        $statement->bindValue(":limit", $filters["per_page"], SQLITE3_INTEGER);
        $statement->bindValue(":offset", $filters["per_page"] * ($filters["page"] - 1), SQLITE3_INTEGER);
        $q = $statement->execute();
        $units = [];

        // Loop and get unit info
        while ($r = $q->fetchArray(SQLITE3_ASSOC)){
            $unit = get_unit($db, $r["id"]);
            if ($unit["status"] == 200){
                array_push($units, $unit["content"]);
            }
        };

        // Calculate previous and next pages
        if ($filters["page"] == 1){
            $url_prev = null;
        }
        else{
            $url_prev = URL::BASE . "API/v3/units/?page=" . ($filters["page"] - 1) . $parameters;
        }
        if ($filters["page"] < ceil($total / $filters["per_page"])){
            $url_next = URL::BASE . "API/v3/units/?page=" . ($filters["page"] + 1) . $parameters;
        }
        else{
            $url_next = null;
        }
        $content = [
            "count" => $total,
            "next" => $url_next,
            "previous" => $url_prev,
            "results" => $units
        ];
        $result["content"] = $content;
        return $result;
    }

    try{
        header("Content-type: application/json; charset=utf-8");
        $result = [
            "status" => 200,
            "message" => "Success.",
            "content" => ""
        ];
        // Get profile ID
        /** @var mixed $query Request parameters, from API_CONTROLLER*/
        if (strlen($query[0]) > 0){
            $id = $query[0];
            $result = get_unit($db, $id);
        }
        else{
            $result = get_unit_list($db);
        }
        header("HTTP/1.1 " . $result["status"] . " " . $result["message"]);
        echo(json_encode($result["content"]));
        syslog(LOG_ERR, "HTTP/1.1 " . $result["status"] . " " . $result["message"]);
        return $result["status"];
    }
    catch(Exception $e) {
        header("HTTP/1.1 500 Unexpeced error: " . $e->getMessage());
        syslog(LOG_ERR, "[APIv3] HTTP/1.1 500 Unexpected error: " . $e->getMessage());
        return 500;
    }