package repository import ( "database/sql" ) // Link types — mirror of the resource_links CHECK (migration 000021). // All types are symmetric : stored once with from_id < to_id (canonical // order), read from either end. version_of is intentionally absent : // versions live INSIDE a resource (resource_versions, 000018). const ( LinkSameScan = "same_scan" LinkDuplicateOf = "duplicate_of" LinkSameIssuer = "same_issuer" LinkSameReference = "same_reference" ) // LinkRow is one edge viewed from the queried resource. type LinkRow struct { ID string OtherID string Type string Confidence float64 CreatedBy string OtherName string OtherType string OtherSize int64 } // canonicalPair orders an unordered pair for symmetric storage. func canonicalPair(a, b string) (string, string) { if a < b { return a, b } return b, a } // ListLinks returns the live edges touching resourceID (either direction), // joined with the other end's identity. Trashed others are excluded. // No ownership check — the caller scopes (owner-only in S5). func (r *Resources) ListLinks(resourceID string) ([]LinkRow, error) { rows, err := r.DB.Query( `SELECT l.id, CASE WHEN l.from_id = $1 THEN l.to_id ELSE l.from_id END AS other_id, l.link_type, l.confidence, l.created_by, o.name, o.type, COALESCE(o.size_bytes, 0) FROM resource_links l JOIN resources o ON o.resource_id = CASE WHEN l.from_id = $1 THEN l.to_id ELSE l.from_id END WHERE (l.from_id = $1 OR l.to_id = $1) AND o.deleted_at IS NULL ORDER BY l.confidence DESC, o.name ASC`, resourceID, ) if err != nil { return nil, err } defer rows.Close() out := make([]LinkRow, 0) for rows.Next() { var row LinkRow if err := rows.Scan(&row.ID, &row.OtherID, &row.Type, &row.Confidence, &row.CreatedBy, &row.OtherName, &row.OtherType, &row.OtherSize); err != nil { return nil, err } out = append(out, row) } return out, rows.Err() } // ensureAutoLink inserts a symmetric auto edge (canonical order). Any // existing row — auto or user verdict — blocks recreation (an invalidated // edge never comes back by itself). func (r *Resources) ensureAutoLink(from, to, linkType string, confidence float64) error { a, b := canonicalPair(from, to) id, err := NewIDErr() if err != nil { return err } _, err = r.DB.Exec( `INSERT INTO resource_links (id, from_id, to_id, link_type, confidence, created_by) VALUES ($1, $2, $3, $4, $5, 'auto') ON CONFLICT (from_id, to_id, link_type) DO NOTHING`, id, a, b, linkType, clamp01(confidence), ) return err } // SetUserVerdict records an explicit verdict (created_by='user') : confirm → // confidence 1.0, invalidate → 0.0. Upserts over any previous row (auto or // older verdict) — the latest human word wins. func (r *Resources) SetUserVerdict(one, other, linkType string, confirmed bool) error { a, b := canonicalPair(one, other) conf := 0.0 if confirmed { conf = 1.0 } id, err := NewIDErr() if err != nil { return err } _, err = r.DB.Exec( `INSERT INTO resource_links (id, from_id, to_id, link_type, confidence, created_by) VALUES ($1, $2, $3, $4, $5, 'user') ON CONFLICT (from_id, to_id, link_type) DO UPDATE SET confidence = EXCLUDED.confidence, created_by = 'user'`, id, a, b, linkType, conf, ) return err } // pruneAutoLinks drops auto edges of the symmetric types touching resourceID // whose pair is no longer a candidate (facts/session edited). keep holds // canonical "a\x00b\x00type" keys to preserve. User verdicts are never pruned. func (r *Resources) pruneAutoLinks(resourceID string, keep map[string]bool) error { rows, err := r.DB.Query( `SELECT id, from_id, to_id, link_type FROM resource_links WHERE (from_id = $1 OR to_id = $1) AND created_by = 'auto'`, resourceID, ) if err != nil { return err } var stale []string for rows.Next() { var id, a, b, typ string if err := rows.Scan(&id, &a, &b, &typ); err != nil { rows.Close() return err } if !keep[a+"\x00"+b+"\x00"+typ] { stale = append(stale, id) } } rows.Close() if err := rows.Err(); err != nil { return err } for _, id := range stale { if _, err := r.DB.Exec(`DELETE FROM resource_links WHERE id = $1`, id); err != nil { return err } } return nil } // linkCandidate is one deterministic pair found by reconciliation. type linkCandidate struct { other string typ string confidence float64 } // ReconcileLinks recomputes the deterministic auto edges of a live owned // resource (same_scan, duplicate_of, same_issuer, same_reference), creates // missing ones and prunes stale auto ones. User verdicts are preserved and // block recreation. Idempotent — safe to call on every read (GET links). func (r *Resources) ReconcileLinks(ownerID, resourceID string) error { var session sql.NullString var hash sql.NullString var size int64 err := r.DB.QueryRow( `SELECT COALESCE(scan_session_id, ''), COALESCE(content_hash, ''), COALESCE(size_bytes, 0) FROM resources WHERE resource_id = $1 AND user_id = $2 AND deleted_at IS NULL`, resourceID, ownerID, ).Scan(&session, &hash, &size) if err == sql.ErrNoRows { return ErrNotFound } if err != nil { return err } var cands []linkCandidate // same_scan : same opaque session, same user, live, not self. if session.Valid && session.String != "" { rows, err := r.DB.Query( `SELECT resource_id FROM resources WHERE user_id = $1 AND scan_session_id = $2 AND resource_id != $3 AND deleted_at IS NULL`, ownerID, session.String, resourceID, ) if err != nil { return err } for rows.Next() { var other string if err := rows.Scan(&other); err != nil { rows.Close() return err } cands = append(cands, linkCandidate{other, LinkSameScan, 1.0}) } rows.Close() if err := rows.Err(); err != nil { return err } } // duplicate_of : same content hash (non-empty), size > 0, same user, live. if hash.Valid && hash.String != "" && size > 0 { rows, err := r.DB.Query( `SELECT resource_id FROM resources WHERE user_id = $1 AND content_hash = $2 AND size_bytes > 0 AND resource_id != $3 AND deleted_at IS NULL`, ownerID, hash.String, resourceID, ) if err != nil { return err } for rows.Next() { var other string if err := rows.Scan(&other); err != nil { rows.Close() return err } cands = append(cands, linkCandidate{other, LinkDuplicateOf, 1.0}) } rows.Close() if err := rows.Err(); err != nil { return err } } // same_issuer / same_reference : shared fact values (case-insensitive // for issuers, exact for reference codes), any origin (user corrections // count — they ARE the household truth). Effective facts only : un fait // auto est ignoré quand un fait user du même type existe sur la même // ressource (miroir de `displayFacts` côté mobile — sinon une correction // laisserait l'arête auto périmée). for _, typ := range []string{"issuer", "reference"} { linkTyp := LinkSameIssuer if typ == "reference" { linkTyp = LinkSameReference } rows, err := r.DB.Query( `SELECT DISTINCT o.resource_id, LEAST(f.confidence, g.confidence) FROM resource_facts f JOIN resource_facts g ON g.fact_type = f.fact_type AND g.resource_id != f.resource_id AND CASE WHEN f.fact_type = 'issuer' THEN LOWER(g.value_text) = LOWER(f.value_text) ELSE g.value_text = f.value_text END JOIN resources o ON o.resource_id = g.resource_id WHERE f.resource_id = $1 AND f.fact_type = $2 AND f.value_text IS NOT NULL AND (f.origin = 'user' OR NOT EXISTS ( SELECT 1 FROM resource_facts u WHERE u.resource_id = f.resource_id AND u.fact_type = f.fact_type AND u.origin = 'user')) AND (g.origin = 'user' OR NOT EXISTS ( SELECT 1 FROM resource_facts u WHERE u.resource_id = g.resource_id AND u.fact_type = g.fact_type AND u.origin = 'user')) AND o.user_id = $3 AND o.deleted_at IS NULL`, resourceID, typ, ownerID, ) if err != nil { return err } for rows.Next() { var other string var conf float64 if err := rows.Scan(&other, &conf); err != nil { rows.Close() return err } cands = append(cands, linkCandidate{other, linkTyp, conf}) } rows.Close() if err := rows.Err(); err != nil { return err } } keep := make(map[string]bool, len(cands)) for _, c := range cands { a, b := canonicalPair(resourceID, c.other) keep[a+"\x00"+b+"\x00"+c.typ] = true if err := r.ensureAutoLink(resourceID, c.other, c.typ, c.confidence); err != nil { return err } } return r.pruneAutoLinks(resourceID, keep) } // DuplicateGroup is one set of byte-identical live files (B2 screen). type DuplicateGroup struct { ContentHash string Count int TotalBytes int64 ReclaimableBytes int64 // total - biggest member (garder un seul) Files []DuplicateFile } // DuplicateFile is one member of a duplicate group, newest first. type DuplicateFile struct { ID string Name string Size int64 UpdatedAt int64 // ms epoch } // ListDuplicateGroups returns the owner's byte-identical file sets // (content_hash non-empty, size > 0, live only), biggest groups first. func (r *Resources) ListDuplicateGroups(ownerID string) ([]DuplicateGroup, error) { rows, err := r.DB.Query( `SELECT content_hash, COUNT(*), COALESCE(SUM(size_bytes), 0) FROM resources WHERE user_id = $1 AND type = 'file' AND deleted_at IS NULL AND content_hash IS NOT NULL AND content_hash <> '' AND size_bytes > 0 GROUP BY content_hash HAVING COUNT(*) > 1 ORDER BY SUM(size_bytes) DESC`, ownerID, ) if err != nil { return nil, err } defer rows.Close() out := []DuplicateGroup{} for rows.Next() { var g DuplicateGroup if err := rows.Scan(&g.ContentHash, &g.Count, &g.TotalBytes); err != nil { return nil, err } members, err := r.DB.Query( `SELECT resource_id, name, COALESCE(size_bytes, 0), (EXTRACT(EPOCH FROM updated_at) * 1000)::bigint FROM resources WHERE user_id = $1 AND type = 'file' AND deleted_at IS NULL AND content_hash = $2 ORDER BY updated_at DESC`, ownerID, g.ContentHash, ) if err != nil { return nil, err } var biggest int64 for members.Next() { var f DuplicateFile if err := members.Scan(&f.ID, &f.Name, &f.Size, &f.UpdatedAt); err != nil { members.Close() return nil, err } if f.Size > biggest { biggest = f.Size } g.Files = append(g.Files, f) } members.Close() if err := members.Err(); err != nil { return nil, err } g.ReclaimableBytes = g.TotalBytes - biggest out = append(out, g) } if err := rows.Err(); err != nil { return nil, err } return out, nil }