<?php if (!defined('BASEPATH')) {
    exit('No direct script access allowed');
}

class Farm_record_model extends CI_Model
{
    private $read_replica;

    public function __construct()
    {
        parent::__construct();
        $this->read_replica = $this->load->database('savvy_replica', TRUE);
    }

    public function get_farm_records(
        $filters
    ) {
        $query = 
            "SELECT
                a.*,
                b.total AS success,
            CASE
                WHEN a.total > 0 THEN round(100.0 * b.total / a.total, 2)
                ELSE 0.0 END AS success_rate
            FROM
                (
                    SELECT
                        date_trunc('day', aa.created) AS created,
                        count(*) AS total
                    FROM
                        quotes aa
                    LEFT JOIN address ab ON
                        ab.id = aa.location_start_id
                    WHERE
                        aa.created>now() - interval '9 days'
                        AND ab.country = '{$filters['country']}'
                    GROUP BY
                        date_trunc('day', aa.created)
                ) AS a
            LEFT JOIN
                (
                    SELECT
                        date_trunc('day', ba.created) AS created,
                        count(*) AS total
                    FROM
                        quotes ba
                    LEFT JOIN address bb ON
                        bb.id = ba.location_start_id
                    WHERE
                        ba.created > now() - interval '9 days'
                        AND ba.cost > 0
                        AND bb.country = '{$filters['country']}'
                    GROUP BY
                        date_trunc('day', created)
                ) AS b
            ON (b.created = a.created)
            ORDER BY
                a.created DESC
            ";

        return $this->read_replica->query($query);
    }
}