use super::UrlRepo;
use crate::domain::QualityGateResult;
use chrono::{DateTime, Utc};
impl UrlRepo {
pub async fn set_watched(&self, id: i64, watched: bool) -> anyhow::Result<()> {
sqlx::query("UPDATE urls SET is_watched = $2, watched_at = CASE WHEN $2 = 1 THEN CURRENT_TIMESTAMP ELSE watched_at END WHERE id = $1")
.bind(id).bind(watched).execute(&self.pool).await?;
Ok(())
}
pub async fn persist_seo_scan(&self, id: i64, gate: &QualityGateResult) -> anyhow::Result<()> {
let status = if !gate.passed {
"FAIL"
} else if gate.warnings.is_empty() {
"PASS"
} else {
"WARN"
};
sqlx::query(
r#"
UPDATE urls
SET
http_status = $1,
last_checked_at = CURRENT_TIMESTAMP,
page_title = $2,
canonical_url = $3,
meta_description = $4,
h1_content = $5,
seo_status = $6,
seo_issue = $7,
h1_count = $8,
has_nofollow = $9,
ai_blocked_bots = $10,
has_opengraph = $11,
has_twitter_card = $12,
schema_types = $13,
response_time_ms = $14,
payload_bytes = $15,
has_viewport = $16,
html_lang = $17,
images_missing_alt = $18,
seo_warnings = $19,
updated_at = CURRENT_TIMESTAMP
WHERE id = $20
"#,
)
.bind(gate.http_status)
.bind(gate.page_title.as_deref())
.bind(gate.canonical_url.as_deref())
.bind(gate.meta_description.as_deref())
.bind(gate.h1_content.as_deref())
.bind(status)
.bind(gate.block_reason.as_deref())
.bind(i64::try_from(gate.h1_count).unwrap_or(i64::MAX))
.bind(gate.has_nofollow)
.bind({
let names = gate.ai_directives.blocked_names();
(!names.is_empty()).then(|| names.join(","))
})
.bind(
gate.opengraph.title.is_some()
|| gate.opengraph.description.is_some()
|| gate.opengraph.image.is_some()
|| gate.opengraph.og_type.is_some()
|| gate.opengraph.url.is_some()
|| gate.opengraph.site_name.is_some(),
)
.bind(gate.twitter_card.card.is_some())
.bind({
let types = gate.schema_types();
(!types.is_empty()).then(|| types.join(","))
})
.bind(gate.response_time_ms)
.bind(gate.payload_bytes)
.bind(gate.has_viewport)
.bind(gate.html_lang.as_deref())
.bind(i64::try_from(gate.images_missing_alt).unwrap_or(i64::MAX))
.bind((!gate.warnings.is_empty()).then(|| gate.warnings.join("\n")))
.bind(id)
.execute(&self.pool)
.await?;
Ok(())
}
pub async fn apply_gsc_inspection(
&self,
id: i64,
index_status: &str,
coverage_state: Option<&str>,
last_crawled_at: Option<DateTime<Utc>>,
) -> anyhow::Result<()> {
sqlx::query(
r#"
UPDATE urls
SET
gsc_index_status = $2,
gsc_coverage_state = $3,
gsc_last_crawled_at = $4,
gsc_inspected_at = CURRENT_TIMESTAMP,
updated_at = CURRENT_TIMESTAMP
WHERE id = $1
"#,
)
.bind(id)
.bind(index_status)
.bind(coverage_state)
.bind(last_crawled_at)
.execute(&self.pool)
.await?;
Ok(())
}
pub async fn apply_bing_inspection(
&self,
id: i64,
index_status: &str,
coverage_state: Option<&str>,
last_crawled_at: Option<DateTime<Utc>>,
) -> anyhow::Result<()> {
sqlx::query(
r#"
UPDATE urls
SET
bing_index_status = $2,
bing_coverage_state = $3,
bing_last_crawled_at = $4,
bing_inspected_at = CURRENT_TIMESTAMP,
updated_at = CURRENT_TIMESTAMP
WHERE id = $1
"#,
)
.bind(id)
.bind(index_status)
.bind(coverage_state)
.bind(last_crawled_at)
.execute(&self.pool)
.await?;
Ok(())
}
pub async fn apply_submit_outcome(
&self,
id: i64,
bing_status: Option<&str>,
bing_error: Option<&str>,
google_status: Option<&str>,
google_error: Option<&str>,
) -> anyhow::Result<()> {
sqlx::query(
r#"
UPDATE urls
SET
bing_status = COALESCE($2, bing_status),
bing_submitted_at = CASE WHEN $2 = 'SUBMITTED' THEN CURRENT_TIMESTAMP ELSE bing_submitted_at END,
bing_error = $3,
google_status = COALESCE($4, google_status),
google_submitted_at = CASE WHEN $4 = 'SUBMITTED' THEN CURRENT_TIMESTAMP ELSE google_submitted_at END,
google_error = $5,
updated_at = CURRENT_TIMESTAMP
WHERE id = $1
"#,
)
.bind(id)
.bind(bing_status)
.bind(bing_error)
.bind(google_status)
.bind(google_error)
.execute(&self.pool)
.await?;
Ok(())
}
}