use crate::qp::Exam;
#[allow(dead_code)]
const INIT_DB: &str = "
CREATE TABLE IF NOT EXISTS iqps (
id integer primary key GENERATED ALWAYS AS identity,
course_code TEXT NOT NULL DEFAULT '',
course_name TEXT NOT NULL DEFAULT '',
year INTEGER NOT NULL,
exam TEXT NOT NULL DEFAULT '',
semester TEXT NOT NULL DEFAULT '',
note TEXT NOT NULL DEFAULT '',
filelink TEXT NOT NULL,
from_library BOOLEAN DEFAULT FALSE,
upload_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
approve_status BOOLEAN DEFAULT FALSE,
approved_by TEXT DEFAULT '',
is_deleted BOOLEAN DEFAULT FALSE,
fts_course_details tsvector GENERATED ALWAYS AS (to_tsvector('english', course_code || ' ' || course_name)) stored
);
CREATE INDEX IF NOT EXISTS iqps_fts ON iqps USING gin (fts_course_details);
CREATE EXTENSION pg_trgm;
CREATE INDEX IF NOT EXISTS idx_course_name_trgm ON iqps USING gin (course_name gin_trgm_ops);";
pub fn get_similar_papers_query(year: bool, semester: bool, exam: bool) -> String {
let mut param_num = 1;
format!(
"SELECT {} from iqps where is_deleted=false and course_code = $1 {} {} {}",
ADMIN_DASHBOARD_QP_FIELDS,
if year {
param_num += 1;
format!("AND year=${}", param_num)
} else {
"".to_string()
},
if semester {
param_num += 1;
format!("AND semester=${}", param_num)
} else {
"".to_string()
},
if exam {
param_num += 1;
format!("AND exam=${}", param_num)
} else {
"".to_string()
},
)
}
pub const SOFT_DELETE_BY_ID: &str =
"UPDATE iqps SET approve_status=false, is_deleted = true WHERE id=$1 AND from_library = false";
pub fn get_get_paper_by_id_query() -> String {
format!(
"SELECT {} FROM iqps WHERE id = $1",
ADMIN_DASHBOARD_QP_FIELDS
)
}
pub fn get_edit_paper_query(approval: bool) -> String {
format!(
"UPDATE iqps set course_code=$2, course_name=$3, year=$4, semester=$5, exam=$6, note=$7, approve_status=$8, filelink=$9{} WHERE id=$1 AND is_deleted=false RETURNING {}",
if approval {", approved_by=$10"} else {""},
ADMIN_DASHBOARD_QP_FIELDS
)
}
pub fn get_all_unapproved_query() -> String {
format!("SELECT {} FROM iqps WHERE approve_status = false and is_deleted=false ORDER BY upload_timestamp ASC", ADMIN_DASHBOARD_QP_FIELDS)
}
pub fn get_qp_search_query(exam_filter: Vec<Exam>) -> String {
let exam_filter_clause = exam_filter
.iter()
.map(|&exam| {
if let Exam::CT(_) = exam {
"exam LIKE 'ct%'".into()
} else {
format!("exam = '{}'", String::from(exam))
}
})
.collect::<Vec<String>>()
.join(" OR ");
let exam_clause_str = if exam_filter_clause.is_empty() {
"".into()
} else {
format!("WHERE ({} OR exam = '')", exam_filter_clause)
};
format!("
WITH filtered AS (
SELECT * from iqps {exam_filter} ORDER BY year DESC
),
fuzzy AS (
SELECT id,
similarity(course_code || ' ' || course_name, $1) AS sim_score,
row_number() OVER (ORDER BY similarity(course_code || ' ' || course_name, $1) DESC) AS rank_ix
FROM filtered
WHERE (course_code || ' ' || course_name) %>> $1 AND approve_status = true
ORDER BY rank_ix
LIMIT 30
),
full_text AS (
SELECT id,
ts_rank_cd(fts_course_details, websearch_to_tsquery($1)) AS rank_score,
row_number() OVER (ORDER BY ts_rank_cd(fts_course_details, websearch_to_tsquery($1)) DESC) AS rank_ix
FROM filtered
WHERE fts_course_details @@ websearch_to_tsquery($1) AND approve_status = true
ORDER BY rank_ix
LIMIT 30
),
partial_search AS (
SELECT id,
ts_rank_cd(fts_course_details, {to_tsquery}) AS rank_score,
row_number() OVER (ORDER BY ts_rank_cd(fts_course_details, {to_tsquery}) DESC) as rank_ix
FROM filtered
WHERE fts_course_details @@ {to_tsquery} AND approve_status = true
LIMIT 30
),
result AS (
SELECT {intermediate_fields}
FROM fuzzy
FULL OUTER JOIN full_text ON fuzzy.id = full_text.id
FULL OUTER JOIN partial_search ON coalesce(fuzzy.id, full_text.id) = partial_search.id
JOIN filtered ON coalesce(fuzzy.id, full_text.id, partial_search.id) = filtered.id
ORDER BY
coalesce(1.0 / (50 + fuzzy.rank_ix), 0.0) * 1 +
coalesce(1.0 / (50 + full_text.rank_ix), 0.0) * 1 +
coalesce(1.0 / (50 + partial_search.rank_ix), 0.0) * 1
DESC
) SELECT {search_qp_fields} FROM result",
search_qp_fields = SEARCH_QP_FIELDS,
to_tsquery = "to_tsquery('simple', websearch_to_tsquery('simple', $1)::text || ':*')",
exam_filter = exam_clause_str,
intermediate_fields = ADMIN_DASHBOARD_QP_FIELDS.split(", ").map(|field| format!("filtered.{}", field)).collect::<Vec<String>>().join(", ")
)
}
pub const ADMIN_DASHBOARD_QP_FIELDS: &str = "id, filelink, from_library, course_code, course_name, year, semester, exam, note, upload_timestamp, approve_status";
pub const SEARCH_QP_FIELDS: &str =
"id, filelink, from_library, course_code, course_name, year, semester, exam, note";
pub const INSERT_NEW_QP: &str = "INSERT INTO iqps (course_code, course_name, year, exam, semester, note, filelink, from_library) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING id";
pub const UPDATE_FILELINK: &str = "UPDATE iqps SET filelink=$2 WHERE id=$1";
pub const INSERT_NEW_LIBRARY_QP: &str = "INSERT INTO iqps (course_code, course_name, year, exam, semester, note, filelink, from_library, approve_status) VALUES ($1, $2, $3, $4, $5, $6, $7, true, $8) RETURNING id";