【前編】BigQuery ベクトル検索で作る「類似稟議2,000件の検出 ＆ 稟議起案ガイド生成」基盤 ― 分析はDWHに任せる①（データ整備＆BQML最適化編）

CREATE SCHEMA IF NOT EXISTS `gemini-demo01.ringi_ai_demo`
OPTIONS (
  location = 'US',
  description = '稟議決裁ワークフロー 類似案件ベクトル検索＆起案ガイド生成デモ（2,000件）'
);

-- ============================================================================
-- Step 1: 稟議決裁ワークフロー過去案件テーブル（2,000件）の作成とデータ投入
-- ============================================================================
CREATE OR REPLACE TABLE `gemini-demo01.ringi_ai_demo.past_ringi_records` AS
WITH
-- ① ベンチマーク検証用の代表10案件（表記ゆれの真の類似案件4件 ＋ 紛らわしいハードネガティブ6件）
golden_records AS (
  SELECT * FROM UNNEST([
    STRUCT(
      'RNG-2026-0001' AS ringi_id,
      '01_生成AI・SaaS導入' AS category,
      '第一営業本部' AS department,
      DATE '2026-09-15' AS applied_date,
      'フィールドセールス向けAI音声文字起こし・SFA自動連携SaaS契約' AS title,
      '外勤営業担当者が訪問商談後に毎回1時間かけて手入力している商談記録およびSFA（顧客管理）への活動履歴登録を、音声認識と大規模言語モデル（LLM）による自動要約で省力化する。' AS background_purpose,
      '営業部員120名の商談後事務作業を1日45分削減（月間1,800時間相当）。データ入力漏れゼロ化による案件受注率3%向上、投資回収期間4.5ヶ月。' AS expected_roi,
      1500000 AS budget_amount_jpy,
      'クラウドサービス利用料（販管費・ソフトウェア / OPEX）' AS account_item,
      '部門長決裁 ＋ 情報セキュリティ審査 ＋ AIガバナンス委員会' AS approval_route,
      '承認済' AS final_status,
      '【AIガバナンス・法務審査ガイド】顧客の商談音声データを外部LLMへ送信するため、「入力データのAIモデル学習オプトアウト条項（Zero Data Retention）」の契約書明記と、クラウドセキュリティチェックシート（別紙3）の添付が必須。' AS reviewer_guidance
    ),
    STRUCT(
      'RNG-2026-0002',
      '01_生成AI・SaaS導入',
      'インサイドセールス部',
      DATE '2026-08-22',
      'インサイドセールス商談要約LLMアシスタントおよびCRM自動起票ツール導入',
      'オンライン商談（Google Meet / Zoom）の録画・発話データから顧客の課題・BANT情報・ネクストアクションをAIが自動抽出し、CRMの商談オブジェクトへ自動反映するSaaSを導入したい。',
      'インサイドセールス60名の架電・オンライン商談後の記録作成工数を70%削減。1日あたりの有効商談数を1.4倍へ引き上げ。',
      1450000,
      'クラウドサービス利用料（販管費・ソフトウェア / OPEX）',
      '部門長決裁 ＋ 情報セキュリティ審査 ＋ AIガバナンス委員会',
      '承認済',
      '【経理・情シス審査ガイド】SaaS月額利用料（120万円）は「クラウドサービス利用料（販管費）」、初期プロンプト調整・CRM連携設定費（25万円）は「業務委託費」に科目を分けて明細起票すること。'
    ),
    STRUCT(
      'RNG-2026-0003',
      '01_生成AI・SaaS導入',
      '第二営業本部',
      DATE '2026-07-10',
      '営業トーク解析・自動CRM記録クラウドサービス利用申請（エンタープライズ営業向け）',
      '法人営業の商談内容テキスト化および顧客管理システムへの議事メモ転記を自動化するクラウドAIアシスタントを契約し、営業現場の残業時間削減とナレッジ共有を実現する。',
      '営業担当者100名あたり月間1,500時間の報告書作成工数を削減。ハイパフォーマーの商談トーク可視化による成約率向上。',
      1600000,
      'クラウドサービス利用料（販管費・ソフトウェア / OPEX）',
      '部門長決裁 ＋ 情報セキュリティ審査 ＋ AIガバナンス委員会',
      '条件付承認',
      '【情報セキュリティ条件付承認ガイド】SSO（SAML 2.0）連携および監査ログ保存期間1年以上のプラン選択を条件として承認。個人情報（PII）マスキング機能の有効化画面キャプチャを検収時に提出すること。'
    ),
    STRUCT(
      'RNG-2026-0004',
      '01_生成AI・SaaS導入',
      '第一営業本部',
      DATE '2026-05-18',
      '全社営業部員向け生成AI議事録・案件サマリー自動生成ツール導入（差戻し・再起案事例）',
      '商談後の議事録作成とCRM入力負荷が高いため、生成AIによる自動文字起こし・要約SaaSを導入して営業生産性を高めたい。',
      '営業1名あたり週3時間の事務工数削減を見込む。',
      1500000,
      'クラウドサービス利用料（販管費・ソフトウェア / OPEX）',
      '部門長決裁 ＋ 情報セキュリティ審査 ＋ AIガバナンス委員会',
      '差戻し（再起案で承認）',
      '【初回差戻し理由（要注意）】①初回起案時に「ソフトウェア資産（無形固定資産）」で申請されたが、SaaS利用契約のため「クラウドサービス利用料（販管費）」へ修正が必要。②AIガバナンス委員会の事前審査（外部送信データの学習非利用確認）が未添付のため差戻し。'
    ),
    -- ▼ ここから紛らわしいハードネガティブ（キーワードは重複するが意味・勘定科目が異なる案件）
    STRUCT(
      'RNG-2026-0005',
      '06_オフィス環境・ファシリティ・什器',
      '第一営業本部',
      DATE '2026-09-01',
      '第一営業本部 商談会議室向け大型モニター・防音ブースおよびオフィス什器刷新',
      '営業部門のオンライン商談増加に伴い、周囲の雑音を防ぐ個室型防音フォンブース2台および会議室用65インチ4Kモニター・エルゴノミクスチェアを整備する。',
      'オンライン商談時の音声品質向上および営業フロアの会議室不足解消。耐用年数5年での減価償却。',
      1500000,
      '器具備品（有形固定資産 / CAPEX・減価償却対象）',
      '部門長決裁 ＋ 総務ファシリティ審査 ＋ 経理固定資産登録',
      '承認済',
      '【経理固定資産ガイド】1点20万円以上の防音ブースおよび大型モニターは「器具備品（有形固定資産）」として資産計上が必須。3社相見積書およびオフィスレイアウト変更届を添付すること。'
    ),
    STRUCT(
      'RNG-2026-0006',
      '04_基幹システム・ERP改修',
      '情報システム部',
      DATE '2026-08-12',
      '基幹CRMデータベースサーバー老朽化に伴うOracle DB保守ライセンス更新およびストレージ増設',
      '全社営業部門が利用するオンプレミス連携用CRM中継データベースサーバーの保守期限到来に伴い、年間サポート契約の更新とSSDストレージ増設を実施する。',
      '基幹CRMシステムの稼働率99.95%維持および月末バッチ処理の遅延防止。',
      1500000,
      'システム保守運用費（販管費・保守料）',
      '情報システム部長決裁 ＋ 購買部ベンダ審査',
      '承認済',
      '【購買審査ガイド】既存ベンダとの年間保守契約更新のため単独随意契約理由書を添付。前回契約単価からの値上げ率（3%以内）の妥当性確認表を添えること。'
    ),
    STRUCT(
      'RNG-2026-0007',
      '08_研究開発・PoC・ハードウェア調達',
      '製造技術部',
      DATE '2026-07-25',
      '工場製造ライン外観検査AIカメラおよびエッジ推論用GPU端末の調達',
      '精密部品組立ラインにおける微細な傷・打痕検査を自動化するため、高解像度産業用カメラとエッジAI画像推論ボックス（NVIDIA Jetson搭載）をラインへ設置する。',
      '目視検査員の工数を年間2,400時間削減し、不良品流出率を0.01%以下に抑制。',
      1550000,
      '機械装置・工具器具備品（製造原価・有形固定資産 / CAPEX）',
      '工場長決裁 ＋ 生産技術審査 ＋ 安全衛生委員会',
      '承認済',
      '【製造経理ガイド】工場ラインへの物理組み込み機器のため販管費ではなく「製造原価（機械装置）」での固定資産計上となる。工場レイアウト図と検収テスト基準書を添付すること。'
    ),
    STRUCT(
      'RNG-2026-0008',
      '03_セキュリティ・ゼロトラスト',
      '情報システム部',
      DATE '2026-06-30',
      '全社営業モバイル端末向けゼロトラストEDR・SASEセキュリティライセンス増設',
      '営業部門の社外リモートワーク拡大に伴い、貸与ノートPCおよびスマートフォンからの社内CRMアクセスを保護するSASE/EDRエージェントを追加調達する。',
      'マルウェア感染および不正アクセスによる顧客情報漏洩リスクの極小化、SOC監視工数の40%削減。',
      1480000,
      '通信費・セキュリティ利用料（販管費）',
      'CISO（最高情報セキュリティ責任者）決裁 ＋ 情報システム部長承認',
      '承認済',
      '【CISO審査ガイド】全社セキュリティ標準ポリシー準拠案件。契約期間（年間一括払い）に応じた前払費用計上スケジュールを経理部へ共有すること。'
    ),
    STRUCT(
      'RNG-2026-0009',
      '05_マーケティング・展示会・広告',
      'マーケティング本部',
      DATE '2026-09-05',
      '秋のDX・AIEXPO展示会ブース出展および営業リード獲得デジタル広告キャンペーン',
      '新規エンタープライズ顧客の商談リードを獲得するため、大型展示会への小間出展および連動するWebターゲティング広告を実施し、CRMへ見込み客リストを供給する。',
      '有効名刺・MQLリード獲得600件、うち商談化60件（想定パイプライン売上9,000万円）。',
      1500000,
      '広告宣伝費（販管費）',
      'マーケティング本部長決裁 ＋ 法務景表法チェック',
      '承認済',
      '【マーケ・法務ガイド】配布パンフレットおよび広告バナー内の「No.1」「業界初」等の表記について根拠データの法務審査を完了させること。勘定科目は「広告宣伝費」。'
    ),
    STRUCT(
      'RNG-2026-0010',
      '07_外部コンサル・業務委託契約',
      'DX推進部',
      DATE '2026-08-01',
      '営業プロセス標準化および次世代CRM定着化に向けた業務改革（BPR）コンサルティング委託',
      '営業本部ごとの商談管理ルールのバラつきを解消するため、外部コンサルティングファームに業務フロー棚卸しとKPI設計支援を3ヶ月間業務委託する。',
      '全社営業KPIの統一および案件フォーキャスト精度の25%向上。',
      1500000,
      '支払手数料・業務委託費（販管費）',
      'DX推進部長決裁 ＋ 法務準委任契約審査 ＋ 購買相見積審査',
      '承認済',
      '【法務・購買ガイド】準委任契約（善管注意義務）のため、成果物定義ではなく「月間稼働時間幅（精算幅140〜180h）」と「業務完了報告書の検収基準」を契約書に明記すること。偽装請負防止チェックリスト必須。'
    )
  ])
),
-- ② 8カテゴリ×多様な業務テーマの組み合わせで生成する1,990件のリアルな過去稟議データ
categories AS (
  SELECT * FROM UNNEST([
    STRUCT(0 AS cat_idx, '01_生成AI・SaaS導入' AS category, 'クラウドサービス利用料（販管費・ソフトウェア / OPEX）' AS account_item, '部門長決裁 ＋ 情報セキュリティ審査 ＋ AIガバナンス委員会' AS approval_route,
      ['カスタマーサポート問い合わせ自動回答RAGチャットボット導入', '法務契約書レビューAIアシスタントSaaSライセンス契約', '社内規程・マニュアル横断検索エンタープライズAI基盤導入', 'エンジニア向けAIコーディング支援ツール（コード補完）導入', '人事採用面接スケジュール調整・候補者スカウトAIツール契約'] AS titles,
      ['問い合わせ対応やドキュメント確認にかかる手作業工数を削減し、生成AIによる一次回答ドラフト生成で業務スピードを向上させる。', '属人化している専門文書チェックやナレッジ検索をAIアシスタントで標準化し、担当者の残業時間削減と品質均一化を図る。', '複数システムに散在する社内データを横断検索・要約できるクラウドSaaSを導入し、情報探索時間を大幅に短縮する。'] AS purposes,
      ['担当部門40名の月間作業時間を320時間削減。初期導入後5ヶ月での投資回収を見込む。', '一次対応リードタイムを平均4時間から15分へ短縮し、顧客満足度（CSAT）を12ポイント改善。', '外注費および残業代の削減により年間480万円のコスト削減効果を試算。'] AS rois,
      ['【AIガバナンス・情シス審査ガイド】外部クラウドAIへ社内データを送信するため、学習オプトアウト（Zero Data Retention）設定の確認証跡とセキュリティチェックシート（別紙3）を必ず添付すること。', '【経理審査ガイド】月額・年額のSaaSサブスクリプション利用料は「クラウドサービス利用料（販管費）」で起票し、初期導入コンサル費用が含まれる場合は「業務委託費」と明細を分けること。'] AS guidances
    ),
    STRUCT(1, '02_クラウド基盤・インフラ増強', 'クラウドインフラ利用料・通信費（販管費 / 原価）', '情報システム部長決裁 ＋ クラウドCoEアーキテクチャ審査',
      ['Google Cloud BigQueryデータウェアハウス分析基盤の予約スロット増強', '本番Kubernetesクラスタ増設およびマルチリージョンDR構成構築', '全社データレイクハウス移行に伴うCloud Storage・ETLパイプライン整備', 'Webサービス負荷増大に対応するCDNキャッシュおよびロードバランサ増強', '開発・検証環境向けクラウドサンドボックス自動払い出し基盤の構築'],
      ['データ分析クエリおよびバッチ処理の増加に伴い、ピーク時のスロット競合を解消してリアルタイム分析のSLAを確保する。', '事業拡大に伴うトラフィック増加とBCP（事業継続計画）要件に対応するため、クラウドインフラの冗長化とキャパシティ拡張を行う。', '老朽化したオンプレミスDWHからフルマネージドクラウド基盤へ移行し、インフラ運用保守工数とハードウェア更改コストを削減する。'],
      ['夜間バッチ処理時間を4時間から35分へ短縮。コミットメント割引（CUD）適用によりオンデマンド比28%の単価削減。', 'システム目標復旧時間（RTO）を24時間から1時間以内へ短縮し、重大障害時の機会損失リスクを回避。', 'インフラ運用エンジニアの保守工数を月間120時間削減し、新規サービス開発へのシフトを実現。'],
      ['【クラウドCoE審査ガイド】1年または3年のコミットメント割引（CUD）を適用する場合は、解約不可期間のリスク試算表と月次予算配賦計画を添付すること。', '【情シス・経理ガイド】本番環境インフラ費はサービス原価、社内分析基盤は販管費（システム利用料）へ按分比率を明記して起票すること。']
    ),
    STRUCT(2, '03_セキュリティ・ゼロトラスト', '通信費・セキュリティ利用料（販管費）', 'CISO（最高情報セキュリティ責任者）決裁 ＋ 情報システム部長承認',
      ['全社ID管理基盤（IdP）のパスキー・多要素認証（MFA）強化ライセンス更新', 'クラウド設定ミス検知（CSPM）および脆弱性自動スキャンツール導入', '特権ID管理（PAM）および本番環境アクセス監査ログ保全システム導入', 'サプライチェーン攻撃対策に向けた外部公開資産（ASM）継続監視サービス契約', '標的型メール攻撃訓練および従業員セキュリティeラーニング基盤の年間契約'],
      ['高度化するフィッシング攻撃および認証情報窃取に対抗するため、ゼロトラスト原則に基づく常時検証とエンドポイント保護を強化する。', 'マルチクラウド環境の権限設定ミスや公開設定リスクを自動検知し、インシデント発生前のプロアクティブな是正体制を構築する。', '金融・エンタープライズ顧客からのセキュリティ監査要件（SOC2 / ISO27001）に対応するため、特権操作ログの改ざん防止と証跡管理を徹底する。'],
      ['セキュリティインシデント調査の初動時間（MTTR）を75%短縮。外部監査対応の証跡収集工数を年間400時間削減。', '不正ログイン試行のブロック率99.9%達成およびアカウント乗っ取り被害ゼロの継続。', '脆弱性診断の手動実施コストを年間300万円圧縮し、日次自動スキャンによる常時監視へ移行。'],
      ['【CISO審査ガイド】全社セキュリティポリシー第4条に基づく必須統制案件。SOC監視チームとのアラート連携フロー図を添付すること。', '【法務・情シスガイド】セキュリティログの海外リージョン保管有無を確認し、国内リージョン（東京/大阪）指定オプションを選択していることを明記すること。']
    ),
    STRUCT(3, '04_基幹システム・ERP改修', 'ソフトウェア資産計上（無形固定資産 / CAPEX）またはシステム改修費', '経営会議（または管掌役員）決裁 ＋ 情報システム部アーキテクチャ審査 ＋ 経理部資産判定',
      ['会計ERPシステム インボイス・電子帳簿保存法ワークフロー連携モジュール改修', '販売管理システムと倉庫管理システム（WMS）のリアルタイム出荷API連携開発', '購買稟議ワークフローシステムにおける予算実績自動突合・アラート機能追加開発', '人事給与システムと勤怠管理SaaSのマスターデータ自動同期バッチ改修', 'サブスクリプション契約管理システムの従量課金自動請求エンジン追加開発'],
      ['法改正対応および手作業によるCSVエクスポート・二重入力を廃止し、基幹システム間のデータ連携を完全自動化して決算早期化を実現する。', '事業モデルの拡大に伴い既存ERPの標準機能では対応できない特殊計上ロジックをアドオン改修し、経理・営業事務の手戻りをなくす。', '月次決算時の伝票突合および予算超過チェックをシステム制御に組み込み、内部統制（J-SOX）上の統制不備リスクを排除する。'],
      ['月次決算の締め日数を5営業日から3営業日へ短縮。経理・営業事務のCSV手作業工数を年間1,800時間削減。', '請求書発行ミスおよび出荷指示遅延をゼロ化し、監査法人からのIT統制指摘事項を100%解消。', 'システム耐用年数5年での償却を前提とし、3年累計で投資額の180%の業務工数削減効果を達成。'],
      ['【経理固定資産・J-SOXガイド】新機能追加（自社利用ソフトウェアの価値増加）に該当するため、100万円以上は「ソフトウェア仮勘定（無形固定資産 / CAPEX）」として起票すること（単なるバグ修正・保守のみ販管費）。要件定義書と検収基準書が必須。', '【情シス審査ガイド】基幹ERPへのアドオン改修は将来のバージョンアップ影響度を評価したFIT&GAP分析表を添付すること。']
    ),
    STRUCT(4, '05_マーケティング・展示会・広告', '広告宣伝費（販管費）', 'マーケティング本部長決裁 ＋ 法務景表法・商標チェック',
      ['業界特化型カンファレンス協賛（プラチナスポンサー）および基調講演登壇', '製造・金融業界ターゲット向けアカウントベースドマーケティング（ABM）Web広告出稿', '導入事例ホワイトペーパー制作およびビジネスメディアタイアップ記事掲載', '既存顧客向けユーザー会（年次フォーラム）会場手配・ライブ配信運営委託', '製品紹介3Dプロモーション動画制作およびYouTube・タクシー広告配信'],
      ['重点ターゲット業界の決裁者層（CxO・DX推進部門長）に対するブランド認知度向上と、エンタープライズ商談パイプラインの創出を図る。', '競合他社との差別化ポイントを実際の顧客成功事例を通じて訴求し、資料請求（MQL）から有効商談（SQL）への転換率を引き上げる。', '展示会・オンラインウェビナーを連動させたオムニチャネル施策により、四半期リード獲得目標を達成する。'],
      ['獲得リード450件、有効商談化45件、想定受注金額6,750万円（CPA単価およびROAS 450%目標）。', '指名検索数（ブランド検索ボリューム）の前年同期比135%達成および商談成約リードタイムの15%短縮。', '既存顧客のチャーンレート（解約率）1.2%低減およびアップセル案件20件の創出。'],
      ['【法務・広報審査ガイド】広告クリエイティブおよび講演資料に含まれる市場シェア・効果数値には必ず出典・調査期間を明記し、景品表示法チェック完了証跡を添付すること。', '【経理審査ガイド】イベント開催日または広告掲載完了月が費用計上基準（発生主義）となるため、前払金と検収月のスケジュールを明記すること。']
    ),
    STRUCT(5, '06_オフィス環境・ファシリティ・什器', '器具備品（有形固定資産 / CAPEX）または消耗品費', '部門長決裁 ＋ 総務ファシリティ審査 ＋ 経理固定資産登録',
      ['ハイブリッドワーク対応フリーアドレス執務デスク・高機能チェア増設', '役員会議室およびセミナールーム向けWeb会議用マイクスピーカー・大型ディスプレイ導入', '社員リフレッシュエリア兼集中ワークブースの造作および内装レイアウト改修', '研究開発フロア向け入退室生体認証セキュリティゲートおよび防犯カメラ増設', '拠点サテライトオフィス新設に伴う複合機・Wi-Fiアクセスポイント・什器一式調達'],
      ['出社とリモートワークを併用するハイブリッド勤務体制において、Web会議の音漏れ防止と部門横断コラボレーションの活性化を両立するオフィス環境を整備する。', '人員増加に伴う座席不足を解消するとともに、人間工学に基づいた什器導入により従業員の健康維持とエンゲージメント向上を図る。', '物理セキュリティ基準の引き上げに伴い、機密エリアへの入退室履歴の厳格管理と老朽設備の更新を実施する。'],
      ['会議室予約の取りづらさを解消し、従業員エンゲージメント調査の「職場環境満足度」を18ポイント改善。', 'サテライト拠点活用による営業移動時間の月間300時間削減。5年定額法での減価償却。', '設備故障による業務停止リスクの解消および省エネ機器への更新による電力コスト15%削減。'],
      ['【経理・総務ガイド】取得価額が1単位20万円以上の什器・機器は「器具備品（有形固定資産）」、内装工事は「建物附属設備」として資産計上すること。必ず3社以上の相見積書を添付すること。', '【総務ファシリティガイド】ビル管理会社への工事届出および消防法上の通路幅・感知器移設有無の事前確認シートを添付すること。']
    ),
    STRUCT(6, '07_外部コンサル・業務委託契約', '支払手数料・業務委託費（販管費）', '部門長決裁 ＋ 法務準委任/請負契約審査 ＋ 購買部相見積審査',
      ['中長期DX戦略策定およびAI活用ロードマップ設計アドバイザリー業務委託', '新規事業立ち上げに伴う市場調査・競合ベンチマーク分析レポート作成委託', 'データガバナンス体制構築および個人情報保護規程改定に向けた専門コンサル委託', 'グローバル拠点展開に向けた現地法規制・税務コンプライアンス調査委託', 'クラウドネイティブ開発標準化およびSRE内製化支援に伴う技術アドバイザリー契約'],
      ['社内だけでは知見が不足している専門領域について、外部の専門コンサルタント・有識者の知見を活用し、プロジェクトの立ち上げ期間を大幅に短縮する。', '客観的な第三者視点での業務プロセス診断とベンチマーク評価を取り入れ、投資対効果の高い施策へ経営資源を集中させる。', '法規制対応や高度なアーキテクチャ設計における手戻りリスクを防ぎ、社内メンバーへのスキルトランスファー（内製化）を並行して進める。'],
      ['社内独力での調査・設計と比較してプロジェクト期間を6ヶ月から2ヶ月へ短縮。手戻り防止による想定コスト回避効果1,200万円。', '社内コアメンバーへのナレッジ移管により、次年度以降の外部委託コストを50%削減。', '経営会議への意思決定インプット精度向上および新規事業の初期立ち上げ成功率の改善。'],
      ['【法務・購買ガイド】「請負契約（成果物完成責任）」か「準委任契約（稼働・善管注意義務）」かを明確に区分し、業務仕様書（SOW）に検収条件と指揮命令系統（偽装請負防止）を明記すること。', '【購買審査ガイド】100万円以上の外部委託は原則3社コンペ（相見積）が必要。特命発注（1社単独）の場合は「単独随意契約理由書（様式B）」を必ず添付すること。']
    ),
    STRUCT(7, '08_研究開発・PoC・ハードウェア調達', '研究開発費（販管費）または機械装置・工具器具備品（CAPEX）', 'R&D部門長決裁 ＋ 知財法務審査 ＋ 安全環境審査',
      ['次世代マルチモーダルAIモデル検証用GPUワークステーションおよび検証機材調達', 'スマートファクトリーIoTセンサーネットワーク実証実験（PoC）機器およびゲートウェイ調達', '自律走行AMR（協働搬送ロボット）倉庫内導線テスト用試作機レンタルおよび計測器導入', '高精度3Dスキャナおよびデジタルツインシミュレーション検証用ハードウェア調達', '新素材耐久性テスト向け恒温恒湿試験チャンバーおよび自動計測ロガー導入'],
      ['次期主力製品・新規技術の実用化に向け、実環境に近いラボ環境でアルゴリズムの推論速度・精度およびハードウェア耐久性の実証実験（PoC）を行う。', '本格量産・全社展開の前に小規模なプロトタイプ検証を実施し、技術的ボトルネックの早期特定と特許出願に向けた実験データを取得する。', '従来手作業で行っていた計測・データ収集プロセスを自動化し、研究開発サイクル（仮説・実験・評価）のスループットを倍増させる。'],
      ['PoC検証期間を従来の3ヶ月から4週間へ短縮。量産移行後の設計手戻りコスト（想定2,500万円）を未然に防止。', '新規技術に関する特許出願2件の実験データ取得および製品化リードタイムの30%短縮。', '試験データの自動収集による研究員の計測工数月間160時間削減。'],
      ['【経理・R&D審査ガイド】試作・実験専用で他用途へ転用できない機材やPoC委託費は「研究開発費」、1年以上継続使用する汎用測定器・サーバーは「工具器具備品（固定資産）」として区分計上すること。', '【知財・法務ガイド】外部大学や共同研究先と実験データを共有する場合は、共同研究契約書（NDAおよび知的財産帰属条項）の締結番号を稟議書に記載すること。']
    )
  ])
),
departments AS (
  SELECT dept_name, dept_idx FROM UNNEST([
    '第一営業本部', '第二営業本部', 'DX推進部', '情報システム部',
    'マーケティング本部', '経理財務部', '人事総務部', '製造技術部', 'カスタマーサクセス部'
  ]) AS dept_name WITH OFFSET AS dept_idx
),
statuses AS (
  SELECT status_name, st_idx FROM UNNEST([
    '承認済', '承認済', '承認済', '条件付承認', '差戻し（再起案で承認）'
  ]) AS status_name WITH OFFSET AS st_idx
),
generated_1990 AS (
  SELECT
    FORMAT('RNG-%d-%04d', 2024 + MOD(n, 3), n) AS ringi_id,
    c.category,
    d.dept_name AS department,
    DATE_ADD(DATE '2024-04-01', INTERVAL MOD(n * 17, 940) DAY) AS applied_date,
    CONCAT(
      c.titles[OFFSET(MOD(n, 5))],
      '（', d.dept_name, '・第', CAST(MOD(n, 4) + 1 AS STRING), '四半期案件 #', CAST(n AS STRING), '）'
    ) AS title,
    CONCAT(
      '【', d.dept_name, ' 起案背景】',
      c.purposes[OFFSET(MOD(n, 3))],
      ' 対象拠点・スコープ：拠点コード-', CAST(MOD(n, 12) + 1 AS STRING),
      '、対象ユーザー数：約', CAST((MOD(n, 15) + 2) * 10 AS STRING), '名規模。'
    ) AS background_purpose,
    CONCAT(
      c.rois[OFFSET(MOD(n, 3))],
      '（試算ケースID: ROI-', CAST(n AS STRING), '）'
    ) AS expected_roi,
    (30 + MOD(n * 37, 470)) * 10000 AS budget_amount_jpy,
    c.account_item,
    c.approval_route,
    s.status_name AS final_status,
    CONCAT(
      c.guidances[OFFSET(MOD(n, 2))],
      CASE
        WHEN s.status_name = '差戻し（再起案で承認）' THEN ' ※初回申請時に添付証跡および費用按分明細の記載漏れがあり差戻し、修正後に再起案で承認。'
        WHEN s.status_name = '条件付承認' THEN ' ※四半期ごとの効果測定レポート提出を条件として承認。'
        ELSE ' ※事前審査チェックリスト完備によりストレート承認。'
      END
    ) AS reviewer_guidance
  FROM UNNEST(GENERATE_ARRAY(11, 2000)) AS n
  JOIN categories c ON c.cat_idx = MOD(n, 8)
  JOIN departments d ON d.dept_idx = MOD(n * 3, 9)
  JOIN statuses s ON s.st_idx = MOD(n * 7, 5)
)
SELECT * FROM golden_records
UNION ALL
SELECT * FROM generated_1990;

-- ============================================================================
-- Step 2: Vertex AI 最新GAモデル gemini-embedding-001 のリモートモデル登録
-- ============================================================================
CREATE OR REPLACE MODEL `gemini-demo01.ringi_ai_demo.gemini_embedding_model`
REMOTE WITH CONNECTION DEFAULT
OPTIONS (
  endpoint = 'gemini-embedding-001'
);

-- ============================================================================
-- Step 3: 合成コンテキスト列（content）の構築と2,000件一括Embedding生成
-- ============================================================================
CREATE OR REPLACE TABLE `gemini-demo01.ringi_ai_demo.past_ringi_embeddings` AS
SELECT
  ringi_id,
  category,
  department,
  applied_date,
  title,
  background_purpose,
  expected_roi,
  budget_amount_jpy,
  account_item,
  approval_route,
  final_status,
  reviewer_guidance,
  content AS search_content,
  embedding,
  ARRAY_LENGTH(embedding) AS embedding_dims,
  status AS embedding_status
FROM AI.GENERATE_EMBEDDING(
  MODEL `gemini-demo01.ringi_ai_demo.gemini_embedding_model`,
  (
    SELECT
      *,
      CONCAT(
        '【カテゴリ】', category, '\n',
        '【起案部門】', department, '\n',
        '【稟議件名】', title, '\n',
        '【起案背景・目的】', background_purpose, '\n',
        '【定量効果・ROI】', expected_roi
      ) AS content
    FROM `gemini-demo01.ringi_ai_demo.past_ringi_records`
  ),
  STRUCT(
    'RETRIEVAL_DOCUMENT' AS task_type,
    768 AS output_dimensionality
  )
);

-- ============================================================================
-- 検証クエリ①：投入した2,000件の稟議データ＆768次元Embedding生成結果のカテゴリ別集計
-- ============================================================================
SELECT
  category,
  COUNT(*) AS record_count,
  COUNTIF(embedding_dims = 768) AS valid_768d_vectors,
  FORMAT("¥%'d", CAST(AVG(budget_amount_jpy) AS INT64)) AS avg_budget_jpy,
  ANY_VALUE(account_item) AS standard_account_item,
  ANY_VALUE(approval_route) AS required_approval_route
FROM `gemini-demo01.ringi_ai_demo.past_ringi_embeddings`
GROUP BY category
ORDER BY category;

-- ============================================================================
-- Step 4: VECTOR_SEARCH とコサイン類似度変換をカプセル化したテーブル関数（TVF）の作成
-- ※ VECTOR_SEARCH の top_k にはリテラル定数（10）を指定し、呼び出し側で LIMIT 制御する
-- ============================================================================
CREATE OR REPLACE TABLE FUNCTION `gemini-demo01.ringi_ai_demo.fn_search_similar_ringi`(
  query_text STRING
) AS (
  SELECT
    ROUND(1 - distance, 4) AS similarity_score,
    ROUND(distance, 4) AS cosine_distance,
    base.ringi_id,
    base.category,
    base.department,
    base.applied_date,
    base.title,
    base.budget_amount_jpy,
    base.account_item,
    base.approval_route,
    base.final_status,
    base.reviewer_guidance,
    base.background_purpose,
    base.expected_roi
  FROM VECTOR_SEARCH(
    TABLE `gemini-demo01.ringi_ai_demo.past_ringi_embeddings`,
    'embedding',
    (
      SELECT embedding
      FROM AI.GENERATE_EMBEDDING(
        MODEL `gemini-demo01.ringi_ai_demo.gemini_embedding_model`,
        (SELECT query_text AS content),
        STRUCT(
          'RETRIEVAL_QUERY' AS task_type,
          768 AS output_dimensionality
        )
      )
    ),
    top_k => 10,
    distance_type => 'COSINE'
  )
);

-- ============================================================================
-- 検証クエリ②：2,000件の過去稟議から類似案件をリアルタイム検索（TVF 1行呼び出し）
-- ============================================================================
SELECT
  similarity_score,
  ringi_id,
  final_status,
  title,
  account_item,
  approval_route,
  SUBSTR(reviewer_guidance, 1, 52) AS reviewer_guidance_head
FROM `gemini-demo01.ringi_ai_demo.fn_search_similar_ringi`(
  '営業部門で商談の議事録作成やCRM入力を自動化する生成AIツールを導入したい。月額150万円想定'
)
ORDER BY similarity_score DESC
LIMIT 5;

-- ============================================================================
-- 検証クエリ③：「表記ゆれの類似案件（正解4件）」vs「キーワード重複ノイズ（6件）」の識別検証
-- ============================================================================
SELECT
  CASE
    WHEN base.ringi_id IN ('RNG-2026-0001', 'RNG-2026-0002', 'RNG-2026-0003', 'RNG-2026-0004')
      THEN '✅ 正解（表記ゆれ類似）'
    ELSE '❌ ノイズ（同部門・同金額の別案件）'
  END AS judge,
  ROUND(1 - distance, 4) AS similarity_score,
  ROUND(distance, 4) AS cosine_distance,
  base.ringi_id,
  base.category,
  base.title,
  base.account_item
FROM VECTOR_SEARCH(
  (
    SELECT * FROM `gemini-demo01.ringi_ai_demo.past_ringi_embeddings`
    WHERE ringi_id BETWEEN 'RNG-2026-0001' AND 'RNG-2026-0010'
  ),
  'embedding',
  (
    SELECT embedding
    FROM AI.GENERATE_EMBEDDING(
      MODEL `gemini-demo01.ringi_ai_demo.gemini_embedding_model`,
      (SELECT '営業部門で商談の議事録作成やCRM入力を自動化する生成AIツールを導入したい。月額150万円想定' AS content),
      STRUCT('RETRIEVAL_QUERY' AS task_type, 768 AS output_dimensionality)
    )
  ),
  top_k => 10,
  distance_type => 'COSINE'
)
ORDER BY similarity_score DESC;