문서로 비즈니스 활동의 컨텍스트 계층 활성화

-- ── Step 1  |  Parse annual reports ──────────────────────────────────────────
-- Note: Setting the refresh mode to "incremental" guarantees that each incoming document is processed only once.

CREATE OR REPLACE DYNAMIC TABLE DT_ESR_PARSED
  TARGET_LAG = DOWNSTREAM  WAREHOUSE = SNOWADHOC  REFRESH_MODE = INCREMENTAL
AS
SELECT
  SPLIT_PART(SPLIT_PART(fl.RELATIVE_PATH, '/', -1), '.', 1) AS COMPANY,
  fl.RELATIVE_PATH,
  AI_PARSE_DOCUMENT(
    TO_FILE('@ESR_DOCS_STAGE', fl.RELATIVE_PATH),
    {'mode': 'LAYOUT'}
  ) AS RAW_PARSE
FROM ESR_FILE_LOG fl
WHERE fl.RELATIVE_PATH ILIKE '%.pdf';


-- ── Step 2  |  Extract structured fields ─────────────────────────────────────

CREATE OR REPLACE DYNAMIC TABLE DT_CONS_EXTRACTED
  TARGET_LAG = DOWNSTREAM  WAREHOUSE = SNOWADHOC  REFRESH_MODE = INCREMENTAL
AS
WITH full_text AS (
  SELECT COMPANY, RELATIVE_PATH,
    LISTAGG(f.value:content::STRING, '\n') WITHIN GROUP (ORDER BY f.index) AS DOC_TEXT
  FROM DT_ESR_PARSED, LATERAL FLATTEN(input => RAW_PARSE:pages) f
  GROUP BY COMPANY, RELATIVE_PATH
)
SELECT
  COMPANY, RELATIVE_PATH,
  AI_EXTRACT(
    text => LEFT(DOC_TEXT, 120000),
    responseFormat => {'schema': {'type': 'object', 'properties': {
      'title':              {'type': 'string', 'description': 'Company name and fiscal year'},
      'fiscal_year':        {'type': 'string', 'description': '4-digit fiscal year'},
      'revenue':            {'type': 'string', 'description': 'Total revenue with currency'},
      'strategy_themes':    {'type': 'array',  'description': 'Top strategic priorities'},
      'growth_initiatives': {'type': 'array',  'description': 'Key growth programs and investments'},
      'headwinds':          {'type': 'array',  'description': 'Key challenges and risks'}
    }}}
  ) AS RAW_EXTRACT
FROM full_text;

-- ── Step 3  |  Summarize + embed each company ─────────────────────────────────

CREATE OR REPLACE DYNAMIC TABLE DT_CONS_EMBEDDED
  TARGET_LAG = DOWNSTREAM  WAREHOUSE = SNOWADHOC  REFRESH_MODE = INCREMENTAL
AS
WITH summarised AS (
  SELECT COMPANY, RELATIVE_PATH, RAW_EXTRACT,
    AI_COMPLETE(
      'claude-sonnet-4-5',
      PROMPT('Summarise this company''s annual report into 4 JSON fields:
              strategy, financial_performance, outlook, differentiation.\n\n{0}',
        'company: '   || COALESCE(RAW_EXTRACT:response:title::STRING, COMPANY) || '\n' ||
        'themes: '    || COALESCE(ARRAY_TO_STRING(RAW_EXTRACT:response:strategy_themes::ARRAY,    '; '), '') || '\n' ||
        'headwinds: ' || COALESCE(ARRAY_TO_STRING(RAW_EXTRACT:response:headwinds::ARRAY,          '; '), '')
      ),
      response_format => {'type': 'json', 'schema': {'type': 'object', 'properties': {
        'strategy':              {'type': 'string'},
        'financial_performance': {'type': 'string'},
        'outlook':               {'type': 'string'},
        'differentiation':       {'type': 'string'}
      }}}
    ) AS SUMMARY_JSON
  FROM DT_CONS_EXTRACTED
)
SELECT
  COMPANY, RELATIVE_PATH,
  RAW_EXTRACT:response:title::STRING          AS TITLE,
  RAW_EXTRACT:response:fiscal_year::STRING    AS FISCAL_YEAR,
  RAW_EXTRACT:response:revenue::STRING        AS REVENUE,
  SUMMARY_JSON:strategy::STRING               AS S_STRATEGY,
  SUMMARY_JSON:financial_performance::STRING  AS S_FINANCIAL_PERFORMANCE,
  SUMMARY_JSON:outlook::STRING                AS S_OUTLOOK,
  SUMMARY_JSON:differentiation::STRING        AS S_DIFFERENTIATION,
  TRIM(CONCAT_WS(' ',
    COALESCE(SUMMARY_JSON:strategy::STRING, ''),
    COALESCE(SUMMARY_JSON:outlook::STRING, ''),
    COALESCE(SUMMARY_JSON:differentiation::STRING, '')
  ))                                          AS SUMMARY_TEXT,
  AI_EMBED('snowflake-arctic-embed-l-v2.0', TRIM(CONCAT_WS(' ',
    COALESCE(SUMMARY_JSON:strategy::STRING, ''),
    COALESCE(SUMMARY_JSON:outlook::STRING, ''),
    COALESCE(SUMMARY_JSON:differentiation::STRING, '')
  )))                                         AS SUMMARY_VEC
FROM summarised;


-- ── Step 4  |  Discover strategic themes across the corpus ────────────────────

CREATE OR REPLACE TABLE CONS_THEMES AS
WITH themes AS (
  SELECT AI_COMPLETE(
    'claude-sonnet-4-5',
    PROMPT('Identify 4-5 distinct strategic themes that organise these companies.
            Each needs a short name and one-sentence description.\n\n{0}',
      LISTAGG(COMPANY || ': ' || S_STRATEGY, '\n')),
    response_format => {'type': 'json', 'schema': {'type': 'object', 'properties': {
      'themes': {'type': 'array', 'items': {'type': 'object', 'properties': {
        'name':        {'type': 'string'},
        'description': {'type': 'string'}
      }}}
    }}}
  ) AS RAW FROM DT_CONS_EMBEDDED
)
SELECT
  f.index                                                           AS THEME_ID,
  f.value:name::STRING                                              AS THEME_NAME,
  f.value:description::STRING                                       AS THEME_DESC,
  AI_EMBED('snowflake-arctic-embed-l-v2.0',
    f.value:name::STRING || ': ' || f.value:description::STRING)    AS THEME_VEC
FROM themes, LATERAL FLATTEN(input => RAW:themes) f;