Janady13/missionmatch / internal/platform/database/dbgen/sam.sql.go

1// Code generated by sqlc. DO NOT EDIT. 2// versions: 3// sqlc v1.31.1 4// source: sam.sql 5 6package dbgen 7 8import ( 9 "context" 10 "time" 11 12 "github.com/jackc/pgx/v5/pgtype" 13) 14 15const getSamOpportunity = `-- name: GetSamOpportunity :one 16SELECT notice_id, title, solicitation_number, department, full_parent_path, sub_agency, naics_code, psc_code, set_aside_code, set_aside, notice_type, base_type, posted_date, response_deadline, active, pop_city, pop_state, pop_zip, ui_link, description_url, resource_links, poc, raw, source, synced_at, provenance FROM sam_opportunities WHERE notice_id = $1 17` 18 19func (q *Queries) GetSamOpportunity(ctx context.Context, noticeID string) (SamOpportunity, error) { 20 row := q.db.QueryRow(ctx, getSamOpportunity, noticeID) 21 var i SamOpportunity 22 err := row.Scan( 23 &i.NoticeID, 24 &i.Title, 25 &i.SolicitationNumber, 26 &i.Department, 27 &i.FullParentPath, 28 &i.SubAgency, 29 &i.NaicsCode, 30 &i.PscCode, 31 &i.SetAsideCode, 32 &i.SetAside, 33 &i.NoticeType, 34 &i.BaseType, 35 &i.PostedDate, 36 &i.ResponseDeadline, 37 &i.Active, 38 &i.PopCity, 39 &i.PopState, 40 &i.PopZip, 41 &i.UiLink, 42 &i.DescriptionUrl, 43 &i.ResourceLinks, 44 &i.Poc, 45 &i.Raw, 46 &i.Source, 47 &i.SyncedAt, 48 &i.Provenance, 49 ) 50 return i, err 51} 52 53const listSamOpportunities = `-- name: ListSamOpportunities :many 54SELECT notice_id, title, solicitation_number, department, full_parent_path, sub_agency, naics_code, psc_code, set_aside_code, set_aside, notice_type, base_type, posted_date, response_deadline, active, pop_city, pop_state, pop_zip, ui_link, description_url, resource_links, poc, raw, source, synced_at, provenance FROM sam_opportunities 55WHERE ($1::text[] IS NULL OR naics_code = ANY($1::text[])) 56 AND ($2::text[] IS NULL OR upper(set_aside_code) = ANY($2::text[])) 57 AND ($3::text[] IS NULL OR upper(pop_state) = ANY($3::text[])) 58 AND ($4::text[] IS NULL OR upper(department) = ANY($4::text[])) 59 AND ($5::text[] IS NULL OR upper(sub_agency) = ANY($5::text[])) 60 AND ($6::text IS NULL OR position(lower($6::text) in lower(title)) > 0) 61 AND (NOT $7::boolean OR active = true) 62ORDER BY (response_deadline IS NULL OR response_deadline < now()), response_deadline ASC, posted_date DESC NULLS LAST, notice_id 63LIMIT $8::int 64` 65 66type ListSamOpportunitiesParams struct { 67 Naics []string `json:"naics"` 68 SetAsides []string `json:"set_asides"` 69 States []string `json:"states"` 70 Departments []string `json:"departments"` 71 SubAgencies []string `json:"sub_agencies"` 72 Q *string `json:"q"` 73 ActiveOnly bool `json:"active_only"` 74 Lim int32 `json:"lim"` 75} 76 77// All filters are optional (NULL array / NULL scalar = "no filter"). active_only defaults true at the 78// handler. Soonest-closing first, then most-recently-posted. 79// Text equality filters are case-insensitive (upper() both sides; the service upper-cases the params) 80// so casing drift from SAM's path text can't silently zero out results [review COR-3]. The title search 81// is a literal substring (position()), NOT a LIKE pattern, so q's %/_/\ are matched literally and a 82// trailing backslash can't 500 the request [review COR-1]. Past-deadline / null-deadline notices sort 83// to the bottom so the feed leads with what's still open [review COR-2]. 84func (q *Queries) ListSamOpportunities(ctx context.Context, arg ListSamOpportunitiesParams) ([]SamOpportunity, error) { 85 rows, err := q.db.Query(ctx, listSamOpportunities, 86 arg.Naics, 87 arg.SetAsides, 88 arg.States, 89 arg.Departments, 90 arg.SubAgencies, 91 arg.Q, 92 arg.ActiveOnly, 93 arg.Lim, 94 ) 95 if err != nil { 96 return nil, err 97 } 98 defer rows.Close() 99 items := []SamOpportunity{} 100 for rows.Next() { 101 var i SamOpportunity 102 if err := rows.Scan( 103 &i.NoticeID, 104 &i.Title, 105 &i.SolicitationNumber, 106 &i.Department, 107 &i.FullParentPath, 108 &i.SubAgency, 109 &i.NaicsCode, 110 &i.PscCode, 111 &i.SetAsideCode, 112 &i.SetAside, 113 &i.NoticeType, 114 &i.BaseType, 115 &i.PostedDate, 116 &i.ResponseDeadline, 117 &i.Active, 118 &i.PopCity, 119 &i.PopState, 120 &i.PopZip, 121 &i.UiLink, 122 &i.DescriptionUrl, 123 &i.ResourceLinks, 124 &i.Poc, 125 &i.Raw, 126 &i.Source, 127 &i.SyncedAt, 128 &i.Provenance, 129 ); err != nil { 130 return nil, err 131 } 132 items = append(items, i) 133 } 134 if err := rows.Err(); err != nil { 135 return nil, err 136 } 137 return items, nil 138} 139 140const samSyncMeta = `-- name: SamSyncMeta :one 141SELECT COUNT(*)::bigint AS total, MAX(synced_at) AS last_synced FROM sam_opportunities 142` 143 144type SamSyncMetaRow struct { 145 Total int64 `json:"total"` 146 LastSynced interface{} `json:"last_synced"` 147} 148 149func (q *Queries) SamSyncMeta(ctx context.Context) (SamSyncMetaRow, error) { 150 row := q.db.QueryRow(ctx, samSyncMeta) 151 var i SamSyncMetaRow 152 err := row.Scan(&i.Total, &i.LastSynced) 153 return i, err 154} 155 156const upsertSamOpportunity = `-- name: UpsertSamOpportunity :exec 157 158INSERT INTO sam_opportunities ( 159 notice_id, title, solicitation_number, department, full_parent_path, sub_agency, 160 naics_code, psc_code, set_aside_code, set_aside, notice_type, base_type, 161 posted_date, response_deadline, active, pop_city, pop_state, pop_zip, 162 ui_link, description_url, resource_links, poc, raw, source, synced_at 163) VALUES ( 164 $1, $2, $3, $4, $5, $6, 165 $7, $8, $9, $10, $11, $12, 166 $13, $14, $15, $16, $17, $18, 167 $19, $20, $21, $22, $23, $24, now() 168) 169ON CONFLICT (notice_id) DO UPDATE SET 170 title = EXCLUDED.title, 171 solicitation_number = EXCLUDED.solicitation_number, 172 department = EXCLUDED.department, 173 full_parent_path = EXCLUDED.full_parent_path, 174 sub_agency = EXCLUDED.sub_agency, 175 naics_code = EXCLUDED.naics_code, 176 psc_code = EXCLUDED.psc_code, 177 set_aside_code = EXCLUDED.set_aside_code, 178 set_aside = EXCLUDED.set_aside, 179 notice_type = EXCLUDED.notice_type, 180 base_type = EXCLUDED.base_type, 181 posted_date = EXCLUDED.posted_date, 182 response_deadline = EXCLUDED.response_deadline, 183 active = EXCLUDED.active, 184 pop_city = EXCLUDED.pop_city, 185 pop_state = EXCLUDED.pop_state, 186 pop_zip = EXCLUDED.pop_zip, 187 ui_link = EXCLUDED.ui_link, 188 description_url = EXCLUDED.description_url, 189 resource_links = EXCLUDED.resource_links, 190 poc = EXCLUDED.poc, 191 raw = EXCLUDED.raw, 192 source = EXCLUDED.source, 193 synced_at = now() 194` 195 196type UpsertSamOpportunityParams struct { 197 NoticeID string `json:"notice_id"` 198 Title string `json:"title"` 199 SolicitationNumber string `json:"solicitation_number"` 200 Department string `json:"department"` 201 FullParentPath string `json:"full_parent_path"` 202 SubAgency string `json:"sub_agency"` 203 NaicsCode string `json:"naics_code"` 204 PscCode string `json:"psc_code"` 205 SetAsideCode string `json:"set_aside_code"` 206 SetAside string `json:"set_aside"` 207 NoticeType string `json:"notice_type"` 208 BaseType string `json:"base_type"` 209 PostedDate pgtype.Date `json:"posted_date"` 210 ResponseDeadline *time.Time `json:"response_deadline"` 211 Active bool `json:"active"` 212 PopCity string `json:"pop_city"` 213 PopState string `json:"pop_state"` 214 PopZip string `json:"pop_zip"` 215 UiLink string `json:"ui_link"` 216 DescriptionUrl string `json:"description_url"` 217 ResourceLinks []byte `json:"resource_links"` 218 Poc []byte `json:"poc"` 219 Raw []byte `json:"raw"` 220 Source string `json:"source"` 221} 222 223// SAM.gov opportunities cache. Global public catalog (no organization_id, outside RLS): reads run 224// straight off the pool, writes come from the daily sync (cmd/sam-sync) as the migrator role. 225func (q *Queries) UpsertSamOpportunity(ctx context.Context, arg UpsertSamOpportunityParams) error { 226 _, err := q.db.Exec(ctx, upsertSamOpportunity, 227 arg.NoticeID, 228 arg.Title, 229 arg.SolicitationNumber, 230 arg.Department, 231 arg.FullParentPath, 232 arg.SubAgency, 233 arg.NaicsCode, 234 arg.PscCode, 235 arg.SetAsideCode, 236 arg.SetAside, 237 arg.NoticeType, 238 arg.BaseType, 239 arg.PostedDate, 240 arg.ResponseDeadline, 241 arg.Active, 242 arg.PopCity, 243 arg.PopState, 244 arg.PopZip, 245 arg.UiLink, 246 arg.DescriptionUrl, 247 arg.ResourceLinks, 248 arg.Poc, 249 arg.Raw, 250 arg.Source, 251 ) 252 return err 253} 254