use crate::models::{Content, ContentLinkMetadata};
use sqlx::PgPool;
use sqlx::types::Json;
use std::sync::Arc;
use systemprompt_identifiers::{CategoryId, ContentId, LocaleCode, SourceId};
pub(super) async fn find_by_id(
pool: &Arc<PgPool>,
id: &ContentId,
) -> Result<Option<Content>, sqlx::Error> {
sqlx::query_as!(
Content,
r#"
SELECT id as "id: ContentId", slug,
locale as "locale: LocaleCode",
title, description, body, author,
published_at, keywords, kind, image,
category_id as "category_id: CategoryId",
source_id as "source_id: SourceId",
version_hash, public, COALESCE(links, '[]'::jsonb) as "links!: Json<Vec<ContentLinkMetadata>>",
updated_at
FROM markdown_content
WHERE id = $1
"#,
id.as_str()
)
.fetch_optional(&**pool)
.await
}
pub(super) async fn find_by_slug(
pool: &Arc<PgPool>,
slug: &str,
locale: &LocaleCode,
) -> Result<Option<Content>, sqlx::Error> {
sqlx::query_as!(
Content,
r#"
SELECT id as "id: ContentId", slug,
locale as "locale: LocaleCode",
title, description, body, author,
published_at, keywords, kind, image,
category_id as "category_id: CategoryId",
source_id as "source_id: SourceId",
version_hash, public, COALESCE(links, '[]'::jsonb) as "links!: Json<Vec<ContentLinkMetadata>>",
updated_at
FROM markdown_content
WHERE slug = $1 AND locale = $2
"#,
slug,
locale.as_str()
)
.fetch_optional(&**pool)
.await
}
pub(super) async fn find_by_source_and_slug(
pool: &Arc<PgPool>,
source_id: &SourceId,
slug: &str,
locale: &LocaleCode,
) -> Result<Option<Content>, sqlx::Error> {
sqlx::query_as!(
Content,
r#"
SELECT id as "id: ContentId", slug,
locale as "locale: LocaleCode",
title, description, body, author,
published_at, keywords, kind, image,
category_id as "category_id: CategoryId",
source_id as "source_id: SourceId",
version_hash, public, COALESCE(links, '[]'::jsonb) as "links!: Json<Vec<ContentLinkMetadata>>",
updated_at
FROM markdown_content
WHERE source_id = $1 AND slug = $2 AND locale = $3
"#,
source_id.as_str(),
slug,
locale.as_str()
)
.fetch_optional(&**pool)
.await
}
pub(super) async fn list(
pool: &Arc<PgPool>,
limit: i64,
offset: i64,
) -> Result<Vec<Content>, sqlx::Error> {
sqlx::query_as!(
Content,
r#"
SELECT id as "id: ContentId", slug,
locale as "locale: LocaleCode",
title, description, body, author,
published_at, keywords, kind, image,
category_id as "category_id: CategoryId",
source_id as "source_id: SourceId",
version_hash, public, COALESCE(links, '[]'::jsonb) as "links!: Json<Vec<ContentLinkMetadata>>",
updated_at
FROM markdown_content
ORDER BY published_at DESC
LIMIT $1 OFFSET $2
"#,
limit,
offset
)
.fetch_all(&**pool)
.await
}
pub(super) async fn list_by_source(
pool: &Arc<PgPool>,
source_id: &SourceId,
locale: &LocaleCode,
) -> Result<Vec<Content>, sqlx::Error> {
sqlx::query_as!(
Content,
r#"
SELECT id as "id: ContentId", slug,
locale as "locale: LocaleCode",
title, description, body, author,
published_at, keywords, kind, image,
category_id as "category_id: CategoryId",
source_id as "source_id: SourceId",
version_hash, public, COALESCE(links, '[]'::jsonb) as "links!: Json<Vec<ContentLinkMetadata>>",
updated_at
FROM markdown_content
WHERE source_id = $1 AND locale = $2
ORDER BY published_at DESC
"#,
source_id.as_str(),
locale.as_str()
)
.fetch_all(&**pool)
.await
}
pub(super) async fn list_by_source_limited(
pool: &Arc<PgPool>,
source_id: &SourceId,
locale: &LocaleCode,
limit: i64,
) -> Result<Vec<Content>, sqlx::Error> {
sqlx::query_as!(
Content,
r#"
SELECT id as "id: ContentId", slug,
locale as "locale: LocaleCode",
title, description, body, author,
published_at, keywords, kind, image,
category_id as "category_id: CategoryId",
source_id as "source_id: SourceId",
version_hash, public, COALESCE(links, '[]'::jsonb) as "links!: Json<Vec<ContentLinkMetadata>>",
updated_at
FROM markdown_content
WHERE source_id = $1 AND locale = $2
ORDER BY published_at DESC
LIMIT $3
"#,
source_id.as_str(),
locale.as_str(),
limit
)
.fetch_all(&**pool)
.await
}
pub(super) async fn list_all(
pool: &Arc<PgPool>,
limit: i64,
offset: i64,
) -> Result<Vec<Content>, sqlx::Error> {
sqlx::query_as!(
Content,
r#"
SELECT id as "id: ContentId", slug,
locale as "locale: LocaleCode",
title, description, body, author,
published_at, keywords, kind, image,
category_id as "category_id: CategoryId",
source_id as "source_id: SourceId",
version_hash, public, COALESCE(links, '[]'::jsonb) as "links!: Json<Vec<ContentLinkMetadata>>",
updated_at
FROM markdown_content
ORDER BY published_at DESC
LIMIT $1 OFFSET $2
"#,
limit,
offset
)
.fetch_all(&**pool)
.await
}
pub(super) async fn category_exists(
pool: &Arc<PgPool>,
category_id: &CategoryId,
) -> Result<bool, sqlx::Error> {
let result = sqlx::query_scalar!(
r#"SELECT EXISTS(SELECT 1 FROM markdown_categories WHERE id = $1) as "exists!""#,
category_id.as_str()
)
.fetch_one(&**pool)
.await?;
Ok(result)
}
pub(super) async fn list_sources_by_slug(
pool: &Arc<PgPool>,
slug: &str,
locale: &LocaleCode,
) -> Result<Vec<SourceId>, sqlx::Error> {
let rows: Vec<String> = sqlx::query_scalar!(
r#"
SELECT source_id as "source_id!"
FROM markdown_content
WHERE slug = $1 AND locale = $2
ORDER BY source_id
"#,
slug,
locale.as_str()
)
.fetch_all(&**pool)
.await?;
Ok(rows.into_iter().map(SourceId::new).collect())
}
pub(super) async fn list_slugs_with_locales_by_source(
pool: &Arc<PgPool>,
source_id: &SourceId,
) -> Result<Vec<(String, LocaleCode)>, sqlx::Error> {
let rows = sqlx::query!(
r#"
SELECT slug, locale as "locale: LocaleCode"
FROM markdown_content
WHERE source_id = $1 AND public = true
ORDER BY slug, locale
"#,
source_id.as_str()
)
.fetch_all(&**pool)
.await?;
Ok(rows.into_iter().map(|r| (r.slug, r.locale)).collect())
}