sciagent/database/crud_examples.sql

-- =============================================================================
-- CRUD PATTERNS — Sáng kiến application system
-- =============================================================================

-- =============================================================================
-- CREATE: Submit a new application with multiple authors (atomic)
-- =============================================================================
BEGIN;
  -- Set audit context
  SELECT set_config('my.user_id', '42', true);

  -- 1. Main record
  INSERT INTO applications(code, title, registration_year, status, purpose,
                           is_technical_solution, primary_unit_id, created_by)
  VALUES ('SK-2025-007',
          'Hệ thống tự động điền hồ sơ sáng kiến',
          2025, 'DRAFT',
          'Tự động hoá việc điền các mẫu số 01–04',
          TRUE, 2, 42)
  RETURNING application_id \gset

  -- 2. Authors (defer contribution-sum check until COMMIT)
  SET CONSTRAINTS trg_contribution_total DEFERRED;
  INSERT INTO application_authors(application_id, user_id, contribution_pct, role, display_order) VALUES
    (:application_id, 42, 60.00, 'PRIMARY',    1),
    (:application_id, 13, 25.00, 'CO_AUTHOR',  2),
    (:application_id, 27, 15.00, 'CO_AUTHOR',  3);

  -- 3. Orgs that tested it
  INSERT INTO application_adopters(application_id, org_name, address, field) VALUES
    (:application_id, 'Phòng KHCN', '217 Hồng Bàng, Q.5', 'Cải cách hành chính');
COMMIT;


-- =============================================================================
-- READ: Dashboard — paginated list with filters
-- =============================================================================
SELECT * FROM v_application_summary
 WHERE registration_year = 2025
   AND status = ANY(ARRAY['UNDER_REVIEW','EVALUATED']::text[])
   AND title ILIKE '%động vật%'              -- uses trigram index
 ORDER BY avg_score DESC NULLS LAST, submitted_at DESC
 LIMIT 20 OFFSET 0;

-- Read: full application with nested data (app layer usually does this as N queries
-- or one JSON aggregate — here's the aggregate version)
SELECT jsonb_build_object(
  'application', to_jsonb(a.*),
  'authors',     (SELECT jsonb_agg(jsonb_build_object(
                    'user_id', u.user_id,
                    'name',    u.full_name,
                    'pct',     aa.contribution_pct,
                    'role',    aa.role
                  ) ORDER BY aa.display_order)
                  FROM application_authors aa
                  JOIN users u USING (user_id)
                  WHERE aa.application_id = a.application_id),
  'evaluations',(SELECT jsonb_agg(to_jsonb(e.*))
                  FROM evaluations e WHERE e.application_id = a.application_id),
  'attachments',(SELECT jsonb_agg(to_jsonb(att.*))
                  FROM attachments att WHERE att.application_id = a.application_id)
) AS document
FROM applications a
WHERE a.application_id = 1 AND a.deleted_at IS NULL;

-- Full-text search (Vietnamese-friendly; combine with unaccent for better recall)
SELECT application_id, code, title
  FROM applications
 WHERE to_tsvector('simple', title || ' ' || coalesce(introduction,''))
    @@ plainto_tsquery('simple', 'đạo đức động vật')
 ORDER BY registration_year DESC
 LIMIT 10;


-- =============================================================================
-- UPDATE: Progress an application through the workflow
-- =============================================================================
-- Submit (DRAFT → SUBMITTED). Triggers populate submitted_at automatically.
UPDATE applications SET status = 'SUBMITTED' WHERE application_id = 7;

-- Assign to review panel
UPDATE applications SET status = 'UNDER_REVIEW' WHERE application_id = 7;

-- Upsert an evaluation (same evaluator re-scores)
INSERT INTO evaluations (application_id, evaluator_id, novelty_score, effectiveness_score, conclusion)
VALUES (7, 99, 32, 48, 'Đề nghị công nhận')
ON CONFLICT (application_id, evaluator_id)
DO UPDATE SET
    novelty_score       = EXCLUDED.novelty_score,
    effectiveness_score = EXCLUDED.effectiveness_score,
    conclusion          = EXCLUDED.conclusion,
    evaluated_at        = NOW();

-- Update JSONB field: patch a single effectiveness sub-field
UPDATE applications
   SET effectiveness = effectiveness || jsonb_build_object(
       'economic',
       'Tiết kiệm ~30% thời gian xét duyệt'
   )
 WHERE application_id = 7;

-- Partial update (PATCH-style) — only update provided fields. The app layer
-- generates SET clauses from the non-null fields in the request body.
UPDATE applications
   SET title   = COALESCE($1, title),
       purpose = COALESCE($2, purpose),
       updated_at = NOW()
 WHERE application_id = $3 AND deleted_at IS NULL
RETURNING *;


-- =============================================================================
-- DELETE: Soft delete + restore
-- =============================================================================
-- Soft delete
UPDATE applications SET deleted_at = NOW() WHERE application_id = 7;

-- Restore
UPDATE applications SET deleted_at = NULL WHERE application_id = 7;

-- Hard delete (only for drafts, cascades to authors/evaluations/etc.)
DELETE FROM applications
 WHERE application_id = 7
   AND status = 'DRAFT';


-- =============================================================================
-- ANALYTICS: Materialized-view refresh (run nightly via cron/pgAgent)
-- =============================================================================
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_annual_stats;

-- Leaderboard: top-scoring approved innovations
SELECT code, title, avg_score
  FROM v_application_summary
 WHERE status = 'APPROVED'
 ORDER BY avg_score DESC
 LIMIT 10;


-- =============================================================================
-- REVIEW JSON: Persist / retrieve ReviewPanel bundle
-- =============================================================================
BEGIN;
  SELECT set_config('my.user_id', '42', true);

  -- Latest app version number for this application
  WITH v AS (
    SELECT COALESCE(MAX(document_version), 0) + 1 AS next_ver
      FROM application_review_documents
     WHERE application_id = 7
  )
  INSERT INTO application_review_documents(
      application_id, case_id, document_version, official_bieu_mau, template_data, full_bundle, created_by
  )
  SELECT
      7,
      'CASE-2026-0007',
      v.next_ver,
      $${"TRANG BÌA":{"Tên sáng kiến (Tiếng Việt)":"Ví dụ"}}$$::jsonb,
      $${"initiativeName":"Ví dụ"}$$::jsonb,
      $${"meta":{"caseId":"CASE-2026-0007"}}$$::jsonb,
      42
  FROM v;
COMMIT;

-- Load latest ReviewPanel bundle by case id
SELECT *
  FROM application_review_documents
 WHERE case_id = 'CASE-2026-0007'
 ORDER BY document_version DESC, created_at DESC
 LIMIT 1;