1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124

use chrono::NaiveDate;

use crate::prelude::*;

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
#[cfg_attr(feature = "ts_rs", derive(TS))]
pub struct PageVisitDatumSummaryByCourse {
    pub id: Uuid,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
    pub deleted_at: Option<DateTime<Utc>>,
    pub course_id: Option<Uuid>,
    pub exam_id: Option<Uuid>,
    pub referrer: Option<String>,
    pub utm_source: Option<String>,
    pub utm_medium: Option<String>,
    pub utm_campaign: Option<String>,
    pub utm_term: Option<String>,
    pub utm_content: Option<String>,
    pub num_visitors: i32,
    pub visit_date: NaiveDate,
}

/// Calculates the statistics for a single day.
pub async fn calculate_and_update_for_date(
    conn: &mut PgConnection,
    date: NaiveDate,
) -> ModelResult<Vec<PageVisitDatumSummaryByCourse>> {
    let res = sqlx::query_as!(
        PageVisitDatumSummaryByCourse,
        r#"
INSERT INTO page_visit_datum_summary_by_courses (
    course_id,
    exam_id,
    referrer,
    utm_source,
    utm_medium,
    utm_campaign,
    utm_term,
    utm_content,
    num_visitors,
    visit_date
  )
SELECT
  course_id,
  exam_id,
  referrer,
  utm_source,
  utm_medium,
  utm_campaign,
  utm_term,
  utm_content,
  COUNT(DISTINCT anonymous_identifier) AS num_visitors,
  $1 AS visit_date
FROM page_visit_datum
WHERE deleted_at IS NULL
  AND created_at::date = $1
  AND is_bot = FALSE
GROUP BY course_id,
  exam_id,
  referrer,
  utm_source,
  utm_medium,
  utm_campaign,
  utm_term,
  utm_content
  ON CONFLICT (
    course_id,
    exam_id,
    referrer,
    visit_date,
    utm_source,
    utm_medium,
    utm_campaign,
    utm_term,
    utm_content,
    deleted_at
  ) DO
UPDATE
SET num_visitors = EXCLUDED.num_visitors
RETURNING *
"#,
        date
    )
    .fetch_all(conn)
    .await?;

    Ok(res)
}

/// Gets the latest date for which the statistics have been calculated.
pub async fn get_latest_date(conn: &mut PgConnection) -> ModelResult<Option<NaiveDate>> {
    let res = sqlx::query!(
        r#"
SELECT MAX(visit_date) AS latest_date
FROM page_visit_datum_summary_by_courses
WHERE deleted_at IS NULL
"#,
    )
    .fetch_optional(conn)
    .await?;

    Ok(res.and_then(|r| r.latest_date))
}

pub async fn get_all_for_course(
    conn: &mut PgConnection,
    course_id: Uuid,
) -> ModelResult<Vec<PageVisitDatumSummaryByCourse>> {
    let res = sqlx::query_as!(
        PageVisitDatumSummaryByCourse,
        r#"
SELECT *
FROM page_visit_datum_summary_by_courses
WHERE course_id = $1
AND deleted_at IS NULL
"#,
        course_id
    )
    .fetch_all(conn)
    .await?;

    Ok(res)
}