%%time

q = """select count(1), extract(year from inc_date) as year, COALESCE(b.risk_category, 'N/A') as risk_category
FROM ffcasualty a left join (SELECT
  *
FROM (
  SELECT state, 
    fdid,
    inc_date,
    inc_no,
    exp_no,
    geom,
    b.parcel_id,
    b.wkb_geometry,
    b.risk_category,
    ROW_NUMBER() OVER (PARTITION BY state, fdid, inc_date, inc_no, exp_no, geom ORDER BY st_distance(b.wkb_geometry, a.geom)) AS r
  FROM (select * from incidentaddress where state=%(state)s and fdid=%(fdid)s) a
     left join parcel_risk_category_local b on a.geom && b.wkb_geometry
     ) x
WHERE x.r = 1) b using (state, inc_date, exp_no, fdid, inc_no) where state=%(state)s and fdid=%(fdid)s and extract(year from inc_date) in %(years)s
GROUP by extract(year from inc_date), COALESCE(b.risk_category, 'N/A')
ORDER BY extract(year from inc_date) DESC"""
    
df = pd.read_sql_query(q, connections['nfirs'], params=params)

print df

print df.groupby('year').sum()

"""
Explanation: Firefighter casualty segmented by structure hazard level vs flat NFIRS statistics
Using closest parcel
End of explanation
"""


%%time
q = """select extract(year from inc_date) as year, count(*) from ffcasualty where fdid=%(fdid)s and state=%(state)s and extract(year from inc_date) in %(years)s group by year order by year desc;"""
cur = connections['nfirs'].cursor()


print pd.read_sql_query(q, connections['nfirs'], params=params)

"""
Explanation: Using old non risk-aware method
End of explanation
"""


%%time

q = """SELECT count(1) as count, extract(year from a.inc_date) as year, COALESCE(y.risk_category, 'N/A') as risk_level
FROM ffcasualty a
LEFT JOIN
    (SELECT state, fdid, inc_date, inc_no, exp_no, x.parcel_id, x.risk_category
        FROM ( SELECT *
            FROM incidentaddress a
            LEFT JOIN parcel_risk_category_local using (parcel_id)
        ) AS x
    ) AS y
USING (state, fdid, inc_date, inc_no, exp_no)
WHERE a.state = %(state)s AND a.fdid = %(fdid)s AND extract(year FROM a.inc_date) IN %(years)s
GROUP BY y.risk_category, extract(year from a.inc_date)
ORDER BY extract(year from a.inc_date) DESC"""

print pd.read_sql_query(q, connections['nfirs'], params=params)

"""
Explanation: Using parcel_id join to parcel_risk_category_local
End of explanation
"""


%%time

q = """select count(1), extract(year from alarm) as year, COALESCE(b.risk_category, 'N/A') as risk_category
FROM buildingfires a left join (SELECT
  *
FROM (
  SELECT state,
    fdid,
    inc_date,
    inc_no,
    exp_no,
    geom,
    b.parcel_id,
    b.wkb_geometry,
    b.risk_category,
    ROW_NUMBER() OVER (PARTITION BY state, fdid, inc_date, inc_no, exp_no, geom ORDER BY st_distance(st_centroid(b.wkb_geometry), a.geom)) AS r
  FROM (select * from incidentaddress where state=%(state)s and fdid=%(fdid)s) a
     left join parcel_risk_category_local b on a.geom && b.wkb_geometry
     ) x
WHERE x.r = 1) b using (state, inc_date, exp_no, fdid, inc_no) where state=%(state)s and fdid=%(fdid)s and extract(year from alarm) in %(years)s
GROUP by extract(year from alarm), COALESCE(b.risk_category, 'N/A')
ORDER BY extract(year from alarm) DESC"""

print pd.read_sql_query(q, connections['nfirs'], params=params)

"""
Explanation: Building fires segmented by structure hazard level vs existing NFIRS statistics
End of explanation
"""


%%time

q = """SELECT count(1) as count, extract(year from a.alarm) as year, COALESCE(y.risk_category, 'N/A') as risk_level
FROM buildingfires a
LEFT JOIN
    (SELECT state, fdid, inc_date, inc_no, exp_no, x.parcel_id, x.risk_category
        FROM ( SELECT *
            FROM incidentaddress a
            LEFT JOIN parcel_risk_category_local using (parcel_id)
        ) AS x
    ) AS y
USING (state, fdid, inc_date, inc_no, exp_no)
WHERE a.state = %(state)s AND a.fdid = %(fdid)s AND extract(year FROM a.inc_date) IN %(years)s
GROUP BY y.risk_category, extract(year from a.alarm)
ORDER BY extract(year from a.alarm) DESC"""

display(pd.read_sql_query(q, connections['nfirs'], params=params))

%%time

q = """select extract(year from inc_date) as year, count(*) from buildingfires where fdid=%(fdid)s and state=%(state)s and extract(year from inc_date) in %(years)s group by year order by year desc;"""
    
print pd.read_sql_query(q, connections['nfirs'], params=params)

"""
Explanation: Using directly-attached parcel
End of explanation
"""


%%time

print 'Using closest parcel'

q = """select count(1), extract(year from inc_date) as year, COALESCE(b.risk_category, 'N/A') as risk_category
FROM civiliancasualty a left join (SELECT
  *
FROM (
  SELECT state, 
    fdid,
    inc_date,
    inc_no,
    exp_no,
    geom,
    b.parcel_id,
    b.wkb_geometry,
    b.risk_category,
    ROW_NUMBER() OVER (PARTITION BY state, fdid, inc_date, inc_no, exp_no, geom ORDER BY st_distance(st_centroid(b.wkb_geometry), a.geom)) AS r
  FROM (select * from incidentaddress where state=%(state)s and fdid=%(fdid)s) a
     left join parcel_risk_category_local b on a.geom && b.wkb_geometry
     ) x
WHERE x.r = 1) b using (state, inc_date, exp_no, fdid, inc_no) where state=%(state)s and fdid=%(fdid)s and extract(year from inc_date) in %(years)s
GROUP by extract(year from inc_date), COALESCE(b.risk_category, 'N/A')
ORDER BY extract(year from inc_date) DESC"""

print pd.read_sql_query(q, connections['nfirs'], params=params)

"""
Explanation: Civilian casualities segmented by structure hazard level vs existing NFIRS statistics
End of explanation
"""


%%time

q = """SELECT count(1) as count, extract(year from b.inc_date) as year, COALESCE(y.risk_category, 'N/A') as risk_level
FROM fireincident b
LEFT JOIN
    (SELECT state, fdid, inc_date, inc_no, exp_no, x.parcel_id, x.risk_category
        FROM (SELECT *
            FROM incidentaddress a
            LEFT JOIN parcel_risk_category_local using (parcel_id)
        ) AS x
    ) AS y
USING (state, fdid, inc_date, inc_no, exp_no)
WHERE b.state = %(state)s AND b.fdid = %(fdid)s AND extract(year FROM b.inc_date) IN %(years)s
GROUP BY y.risk_category, extract(year FROM b.inc_date)
ORDER BY extract(year FROM b.inc_date) DESC"""

display(pd.read_sql_query(q, connections['nfirs'], params=params))

"""
Explanation: All fire-related calls
End of explanation
"""


%%time

q = """SELECT count(1) as count, extract(year from a.alarm) as year, COALESCE(y.risk_category, 'N/A') as risk_level
FROM buildingfires a
LEFT JOIN
    (SELECT state, fdid, inc_date, inc_no, exp_no, x.parcel_id, x.risk_category
        FROM ( SELECT *
            FROM incidentaddress a
            LEFT JOIN parcel_risk_category_local using (parcel_id)
        ) AS x
    ) AS y
USING (state, fdid, inc_date, inc_no, exp_no)
WHERE a.state = %(state)s AND a.fdid = %(fdid)s AND extract(year FROM a.inc_date) IN %(years)s
GROUP BY y.risk_category, extract(year from a.alarm)
ORDER BY extract(year from a.alarm) DESC"""

display(pd.read_sql_query(q, connections['nfirs'], params=params))

from firecares.tasks.update import update_nfirs_counts

update_nfirs_counts.delay(fd.id)

"""
Explanation: Structure fire calls
End of explanation
"""


