= Напредни извештаи од базата (SQL и складирани процедури) =

=== Извештај за употребата на пакети - последните 6 месеци, вклучувајќи ги информациите за насловот на пакетот, бројот на употреби, видот на полисата , и податоците за пакетот, сортирано според бројот на употреби во опаѓачки редослед ===

{{{#!sql

SELECT 
    p2.title as Package_title,
    P.package,
    COUNT(*) AS usage_count,
    CASE
        WHEN Pa.pol_id IS NOT NULL THEN 'Auto Registration'
        WHEN Pt.pol_id IS NOT NULL THEN 'Travel Health'
        ELSE ''
    END AS policy_type
FROM 
    project.Policy AS P
LEFT JOIN 
    project.Pol_dog AS Pd ON P.p_id = Pd.policy
LEFT JOIN 
    project.Pol_auto AS Pa ON P.p_id = Pa.pol_id
LEFT JOIN 
    project.Pol_travel AS Pt ON P.p_id = Pt.pol_id
LEFT JOIN 
    project.Pol_osi AS Po ON P.p_id = Po.policy
left join 
	project.package p2 on p.package=p2.code 
WHERE 
    P.sdate >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY 
    P.package, policy_type, p2.title 
ORDER BY 
    usage_count DESC;

}}}

=== Преференца на клиентот за број на денови според неговите објави за Travel Health полиса за минатата година во опаѓачки редослед ===

{{{#!sql

select
	'Travel Health' as Typepolicy,
    COUNT(*) AS posts_count,
    (p.edate::DATE - p.sdate::DATE) AS average_days_preference
FROM
    project."policy" as p
JOIN 
    project.Pol_travel AS Pt ON p.p_id  = Pt.pol_id 
WHERE 
    EXTRACT(YEAR FROM p.edate) = EXTRACT(YEAR FROM CURRENT_DATE) - 1
GROUP BY 
   average_days_preference
order by average_days_preference desc

}}}

=== Извештај за сите податоци за клиент, возило, осигурител, почетен датум, краен датум===

{{{#!sql

WITH CustomerPolicyInfo AS (
    SELECT
        c.c_id,
        c.name AS customer_name,
        c.email,
        c.type AS customer_type,
        p.sdate,
        p.edate,
        pd.d_embg,
        pd.name AS policy_holder_name,
        pd.surname AS policy_holder_surname,
        pt.title AS package_title,
        pt.total AS package_total,
        p.p_id as policy_id
    FROM
        project.Customer c
    INNER JOIN project.Pol_dog pd ON c.c_id = pd.c_id
    INNER JOIN project.Policy p ON pd.policy = p.p_id
    INNER JOIN project.Package pt ON p.package = pt.code
)
SELECT
    cpi.c_id,
    cpi.customer_name,
    cpi.email,
    cpi.customer_type,
    cpi.sdate,
    cpi.edate,
    cpi.d_embg,
    cpi.policy_holder_name,
    cpi.policy_holder_surname,
    cpi.package_title,
    cpi.package_total,
    cpi.policy_id,
CASE
    WHEN po.o_embg IS NOT NULL THEN 'Travel Health'
    ELSE 'Auto registration'
END AS policy_type,
    po.o_embg AS osi_embg,
    po.name AS osi_name,
    po.surname AS osi_surname,
    po.birthdate AS osi_birthdate,
    po.kontakt AS osi_kontakt,
    v."type"  AS vehicle_type,
    v.marka  AS vehicle_marka,
    v.model  AS vehicle_model,
    v.license_plate  AS license_plate
FROM
    CustomerPolicyInfo cpi
LEFT JOIN project.Pol_travel pt ON cpi.policy_id = pt.pol_id
LEFT JOIN project.Pol_osi po ON pt.tr_id = po.policy
LEFT JOIN project.Pol_auto pa ON cpi.policy_id = pa.pol_id
LEFT JOIN project.Vehicle v ON pa.a_id = v.policy


}}}

=== Извештај за број на закажани полиси за одреден клиент ===

{{{#!sql

SELECT
    c.c_id,
    c.name AS customer_name,
    c.email,
    COUNT(p.p_id) AS reserved_policies_count
FROM
    project.Customer c
LEFT JOIN project.Pol_dog pd ON c.c_id = pd.c_id
LEFT JOIN project.Policy p ON pd.policy = p.p_id
GROUP BY
    c.c_id, c.name, c.email
ORDER BY
    reserved_policies_count DESC;
}}}