John, Sara & Eve

Appearance

データベース設計 / Database Schema ​

このドキュメントでは、John, Sara & Eve プロジェクトで使用する SQLite データベースの全テーブル定義を記載します。

This document defines the complete SQLite database schema used in the John, Sara & Eve project.

PRAGMA 設定 / PRAGMA Settings ​

アプリケーション起動時に以下の PRAGMA を実行してください。

Execute the following PRAGMA statements at application startup.

sql
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;
PRAGMA説明 / Description
journal_mode=WAL書き込み中でも読み取りが可能な Write-Ahead Logging を有効化 / Enables Write-Ahead Logging for concurrent reads during writes
foreign_keys=ON外部キー制約を有効化（SQLite はデフォルト OFF）/ Enables foreign key constraints (SQLite defaults to OFF)

テーブル一覧 / Table List ​

#テーブル名 / Table説明 / Description
1charactersキャラクターステータス / Character status
2episodesエピソード記憶 / Episode memory
3household家計・部屋状態（シングルトン）/ Household & room state (singleton)
4owned_items所持アイテム / Owned items
5food_stock食料在庫 / Food stock
6relationshipsキャラクター間関係 / Inter-character relationships
7tips投げ銭ログ / Tip log
8comments視聴者コメント / Viewer comments
9action_log行動ログ / Action log
10mood_log気分ログ / Mood log

1. characters — キャラクターステータス / Character Status ​

各 AI キャラクター（John, Sara, Eve）の現在ステータスを保持します。name を主キーとするため、1 キャラ 1 行です。

Stores the current status of each AI character (John, Sara, Eve). Uses name as the primary key — one row per character.

sql
CREATE TABLE characters (
    name              TEXT    PRIMARY KEY,
    age               INTEGER NOT NULL,
    job               TEXT    NOT NULL,
    hp                INTEGER NOT NULL DEFAULT 100,
    hunger            INTEGER NOT NULL DEFAULT 50,
    mood              INTEGER NOT NULL DEFAULT 50,
    energy            INTEGER NOT NULL DEFAULT 100,
    stress            INTEGER NOT NULL DEFAULT 0,
    love_partner      INTEGER NOT NULL DEFAULT 50,
    love_pet          INTEGER NOT NULL DEFAULT 50,
    cooking           INTEGER NOT NULL DEFAULT 10,
    cleaning          INTEGER NOT NULL DEFAULT 10,
    charisma          INTEGER NOT NULL DEFAULT 10,
    creativity        INTEGER NOT NULL DEFAULT 10,
    updated_at        TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

2. episodes — エピソード記憶 / Episode Memory ​

キャラクターが体験したイベントをエピソード記憶として蓄積します。related_characters と tags は JSON 配列として格納します。

Stores events experienced by characters as episodic memory. related_characters and tags are stored as JSON arrays.

sql
CREATE TABLE episodes (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    character_name      TEXT    NOT NULL,
    timestamp           TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    game_day            INTEGER NOT NULL,
    event               TEXT    NOT NULL,
    event_en            TEXT    NOT NULL,
    emotion             TEXT    NOT NULL,
    emotion_intensity   REAL    NOT NULL DEFAULT 0.5,
    related_characters  TEXT    NOT NULL DEFAULT '[]',   -- JSON array
    tags                TEXT    NOT NULL DEFAULT '[]',   -- JSON array
    importance          REAL    NOT NULL DEFAULT 0.5,
    FOREIGN KEY (character_name) REFERENCES characters(name)
);

3. household — 家計・部屋状態 / Household & Room State ​

家計と部屋の状態を管理するシングルトンテーブルです。id = 1 の 1 行のみ使用します。

Singleton table managing household finances and room state. Only one row with id = 1 is used.

sql
CREATE TABLE household (
    id                  INTEGER PRIMARY KEY CHECK (id = 1),
    balance             INTEGER NOT NULL DEFAULT 0,
    total_tips          INTEGER NOT NULL DEFAULT 0,
    total_tips_youtube  INTEGER NOT NULL DEFAULT 0,
    total_tips_tiktok   INTEGER NOT NULL DEFAULT 0,
    room_type           TEXT    NOT NULL DEFAULT '1K',
    room_cleanliness    INTEGER NOT NULL DEFAULT 50,
    room_comfort        INTEGER NOT NULL DEFAULT 50,
    updated_at          TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

4. owned_items — 所持アイテム / Owned Items ​

投げ銭や購入で獲得したアイテムの一覧です。

List of items acquired through tips or purchases.

sql
CREATE TABLE owned_items (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    item_id         TEXT    NOT NULL,
    name_ja         TEXT    NOT NULL,
    name_en         TEXT    NOT NULL,
    category        TEXT    NOT NULL,
    acquired_at     TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    acquired_from   TEXT    NOT NULL   -- platform: 'youtube', 'tiktok', 'system'
);

5. food_stock — 食料在庫 / Food Stock ​

冷蔵庫・食料庫の在庫を管理します。expires_game_day が NULL の場合は無期限です。

Manages refrigerator and pantry inventory. expires_game_day of NULL means no expiration.

sql
CREATE TABLE food_stock (
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    item_name         TEXT    NOT NULL,
    quantity          INTEGER NOT NULL DEFAULT 1,
    expires_game_day  INTEGER NULL
);

6. relationships — キャラクター間関係 / Inter-Character Relationships ​

キャラクター同士の関係性パラメータを管理します。char_a < char_b の正規化を推奨します。

Manages relationship parameters between characters. Normalization with char_a < char_b is recommended.

sql
CREATE TABLE relationships (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    char_a          TEXT    NOT NULL,
    char_b          TEXT    NOT NULL,
    love_level      INTEGER NOT NULL DEFAULT 50,
    trust_level     INTEGER NOT NULL DEFAULT 50,
    bond_level      INTEGER NOT NULL DEFAULT 50,
    fight_count     INTEGER NOT NULL DEFAULT 0,
    makeup_count    INTEGER NOT NULL DEFAULT 0,
    last_fight      TEXT    NULL,
    last_date       TEXT    NULL,
    FOREIGN KEY (char_a) REFERENCES characters(name),
    FOREIGN KEY (char_b) REFERENCES characters(name),
    UNIQUE (char_a, char_b)
);

7. tips — 投げ銭ログ / Tip Log ​

各プラットフォームからの投げ銭を記録します。amount_lc はゲーム内通貨（LC: Life Coin）への換算値です。

Records tips from each platform. amount_lc is the converted value in game currency (LC: Life Coin).

sql
CREATE TABLE tips (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    platform        TEXT    NOT NULL,           -- 'youtube', 'tiktok'
    amount_raw      REAL    NOT NULL,
    currency        TEXT    NOT NULL DEFAULT 'JPY',
    amount_lc       INTEGER NOT NULL,
    user_name       TEXT    NOT NULL,
    message         TEXT    NULL,
    item_triggered  TEXT    NULL,
    created_at      TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

8. comments — 視聴者コメント / Viewer Comments ​

配信中の視聴者コメントを保存・処理します。processed フラグで処理済みかどうかを管理します。

Stores and processes viewer comments during streams. The processed flag tracks processing status.

sql
CREATE TABLE comments (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    platform        TEXT    NOT NULL,           -- 'youtube', 'tiktok'
    user_name       TEXT    NOT NULL,
    message         TEXT    NOT NULL,
    language        TEXT    NOT NULL DEFAULT 'ja',
    category        TEXT    NOT NULL DEFAULT 'general',
    processed       INTEGER NOT NULL DEFAULT 0, -- 0=未処理, 1=処理済
    response        TEXT    NULL,
    created_at      TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

9. action_log — 行動ログ / Action Log ​

キャラクターが実行した行動とセリフを時系列で記録します。

Records character actions and dialogue in chronological order.

sql
CREATE TABLE action_log (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    character_name  TEXT    NOT NULL,
    action          TEXT    NOT NULL,
    target          TEXT    NULL,
    location        TEXT    NOT NULL DEFAULT 'room',
    dialogue_ja     TEXT    NULL,
    dialogue_en     TEXT    NULL,
    emotion         TEXT    NOT NULL DEFAULT 'neutral',
    game_day        INTEGER NOT NULL,
    created_at      TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (character_name) REFERENCES characters(name)
);

10. mood_log — 気分ログ / Mood Log ​

キャラクターの気分パラメータを定期的にスナップショットとして記録します。

Periodically records snapshots of character mood parameters.

sql
CREATE TABLE mood_log (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    character_name  TEXT    NOT NULL,
    mood            INTEGER NOT NULL,
    energy          INTEGER NOT NULL,
    stress          INTEGER NOT NULL,
    hunger          INTEGER NOT NULL,
    game_day        INTEGER NOT NULL,
    created_at      TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (character_name) REFERENCES characters(name)
);

インデックス設計 / Index Design ​

クエリパフォーマンスを向上させるため、以下のインデックスを作成します。

The following indexes are created to improve query performance.

sql
-- episodes: キャラクター名 + ゲーム日で検索
CREATE INDEX idx_episodes_character_day
    ON episodes (character_name, game_day);

-- episodes: 重要度で降順ソート（重要な記憶の検索用）
CREATE INDEX idx_episodes_importance
    ON episodes (importance DESC);

-- owned_items: カテゴリ別検索
CREATE INDEX idx_owned_items_category
    ON owned_items (category);

-- food_stock: 賞味期限でソート（期限切れチェック用）
CREATE INDEX idx_food_stock_expires
    ON food_stock (expires_game_day);

-- relationships: キャラクターペア検索
CREATE INDEX idx_relationships_chars
    ON relationships (char_a, char_b);

-- tips: プラットフォーム別 + 日時
CREATE INDEX idx_tips_platform_created
    ON tips (platform, created_at);

-- tips: 金額（LC）降順（高額投げ銭の検索用）
CREATE INDEX idx_tips_amount_lc
    ON tips (amount_lc DESC);

-- comments: 未処理コメント検索
CREATE INDEX idx_comments_unprocessed
    ON comments (processed, created_at)
    WHERE processed = 0;

-- comments: プラットフォーム別
CREATE INDEX idx_comments_platform
    ON comments (platform, created_at);

-- action_log: キャラクター + ゲーム日
CREATE INDEX idx_action_log_character_day
    ON action_log (character_name, game_day);

-- action_log: 日時順
CREATE INDEX idx_action_log_created
    ON action_log (created_at);

-- mood_log: キャラクター + ゲーム日
CREATE INDEX idx_mood_log_character_day
    ON mood_log (character_name, game_day);

初期データ / Initial Data ​

キャラクター初期ステータス / Initial Character Status ​

sql
INSERT INTO characters (name, age, job, hp, hunger, mood, energy, stress, love_partner, love_pet, cooking, cleaning, charisma, creativity)
VALUES
    ('John', 28, 'YouTuber',  100, 50, 50, 100, 0, 50, 50, 15, 8,  20, 25),
    ('Sara', 26, 'Illustrator', 100, 50, 50, 100, 0, 50, 50, 25, 20, 15, 30),
    ('Eve',   2, 'Cat',       100, 50, 70,  80, 0,  0, 80,  0,  0,  0, 50);

ステータス説明 / Status Description:

キャラ / Char特徴 / Trait
John料理は普通、掃除苦手、カリスマ高め、クリエイティビティ高い / Average cooking, poor cleaning, higher charisma, high creativity
Sara料理上手、掃除得意、カリスマ普通、クリエイティビティ最高 / Good at cooking and cleaning, average charisma, highest creativity
Eve猫なので料理・掃除は 0、気分屋（mood 高め）、クリエイティビティ高い（予測不能な行動）/ Cat — no cooking/cleaning, moody (higher mood), high creativity (unpredictable behavior)

家計初期値 / Initial Household State ​

sql
INSERT INTO household (id, balance, total_tips, total_tips_youtube, total_tips_tiktok, room_type, room_cleanliness, room_comfort)
VALUES (1, 50000, 0, 0, 0, '1K', 60, 40);
  • 初期残高 50,000 LC（生活費 1 ヶ月分相当）/ Initial balance: 50,000 LC (approx. 1 month of living expenses)
  • 部屋タイプ: 1K（最小）/ Room type: 1K (smallest)

関係性初期値 / Initial Relationships ​

sql
INSERT INTO relationships (char_a, char_b, love_level, trust_level, bond_level, fight_count, makeup_count)
VALUES
    ('John', 'Sara', 60, 55, 50, 0, 0),
    ('Eve',  'John', 0,  40, 45, 0, 0),
    ('Eve',  'Sara', 0,  45, 50, 0, 0);
  • John ⇔ Sara: 恋人同士（love_level 初期値高め）/ Romantic partners (higher initial love_level)
  • Eve ⇔ John / Sara: ペットと飼い主（love_level = 0、信頼・絆で関係を表現）/ Pet and owners (love_level = 0, relationship expressed through trust & bond)

ER 図 / ER Diagram ​

┌──────────────┐       ┌──────────────────┐
│  characters  │◄──┐   │    episodes      │
│──────────────│   │   │──────────────────│
│ name (PK)    │   ├───│ character_name   │
│ age          │   │   │ game_day         │
│ job          │   │   │ event / event_en │
│ hp, hunger...│   │   │ emotion          │
│ cooking...   │   │   │ importance       │
└──────────────┘   │   └──────────────────┘
       │  │        │
       │  │        │   ┌──────────────────┐
       │  │        ├───│   action_log     │
       │  │        │   │──────────────────│
       │  │        │   │ character_name   │
       │  │        │   │ action, target   │
       │  │        │   │ dialogue_ja/en   │
       │  │        │   └──────────────────┘
       │  │        │
       │  │        │   ┌──────────────────┐
       │  │        └───│   mood_log       │
       │  │            │──────────────────│
       │  │            │ character_name   │
       │  │            │ mood, energy...  │
       │  │            └──────────────────┘
       │  │
       │  └────────┐   ┌──────────────────┐
       │           ├───│  relationships   │
       └───────────┘   │──────────────────│
                       │ char_a, char_b   │
                       │ love, trust...   │
                       └──────────────────┘

┌──────────────┐  ┌──────────────┐  ┌──────────────┐
│  household   │  │  owned_items │  │  food_stock  │
│──────────────│  │──────────────│  │──────────────│
│ id=1 (single)│  │ item_id      │  │ item_name    │
│ balance      │  │ name_ja/en   │  │ quantity     │
│ room_type    │  │ category     │  │ expires_day  │
└──────────────┘  └──────────────┘  └──────────────┘

┌──────────────┐  ┌──────────────┐
│    tips      │  │   comments   │
│──────────────│  │──────────────│
│ platform     │  │ platform     │
│ amount_lc    │  │ message      │
│ user_name    │  │ processed    │
└──────────────┘  └──────────────┘

マイグレーション戦略 / Migration Strategy ​

方針 / Policy ​

SQLite はスキーマ変更の機能が限定的（ALTER TABLE で列の追加のみ可能、列の削除・型変更は不可）なため、以下の戦略を採用します。

SQLite has limited schema migration capabilities (ALTER TABLE only supports adding columns — not dropping or modifying). Therefore, we adopt the following strategy.

バージョン管理 / Version Management ​

sql
-- アプリケーション内でスキーマバージョンを管理
PRAGMA user_version;           -- 現在のバージョンを取得
PRAGMA user_version = 1;       -- バージョンを設定

アプリケーション起動時に PRAGMA user_version を確認し、必要なマイグレーションを順次実行します。

At application startup, check PRAGMA user_version and execute required migrations sequentially.

マイグレーション手順 / Migration Procedure ​

  1. 列の追加 / Adding Columns

    sql
    ALTER TABLE characters ADD COLUMN new_skill INTEGER NOT NULL DEFAULT 0;

  2. 列の削除・型変更 / Dropping Columns or Changing Types

    • 新しいテーブルを作成 → データをコピー → 旧テーブルを削除 → リネーム
    sql
    BEGIN TRANSACTION;
CREATE TABLE characters_new ( ... );
INSERT INTO characters_new SELECT ... FROM characters;
DROP TABLE characters;
ALTER TABLE characters_new RENAME TO characters;
COMMIT;

  3. バックアップ / Backup

    • マイグレーション前に .backup コマンドまたはファイルコピーでバックアップを取得
    • Before migration, create a backup using .backup command or file copy

マイグレーションファイル命名規則 / Migration File Naming Convention ​

migrations/
  001_initial_schema.sql
  002_add_character_skills.sql
  003_add_tips_currency.sql

各ファイルには PRAGMA user_version = N; を含め、冪等性を確保するため CREATE TABLE IF NOT EXISTS / CREATE INDEX IF NOT EXISTS を使用します。

Each file includes PRAGMA user_version = N; and uses CREATE TABLE IF NOT EXISTS / CREATE INDEX IF NOT EXISTS for idempotency.

全テーブル一括作成 SQL / Complete Schema SQL ​

以下は全テーブル・インデックスを一括作成する SQL です。初期セットアップに使用してください。

Below is the complete SQL for creating all tables and indexes. Use it for initial setup.

sql
-- ==============================================
-- John, Sara & Eve - Database Schema v1
-- ==============================================

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;
PRAGMA user_version = 1;

-- 1. characters
CREATE TABLE IF NOT EXISTS characters (
    name              TEXT    PRIMARY KEY,
    age               INTEGER NOT NULL,
    job               TEXT    NOT NULL,
    hp                INTEGER NOT NULL DEFAULT 100,
    hunger            INTEGER NOT NULL DEFAULT 50,
    mood              INTEGER NOT NULL DEFAULT 50,
    energy            INTEGER NOT NULL DEFAULT 100,
    stress            INTEGER NOT NULL DEFAULT 0,
    love_partner      INTEGER NOT NULL DEFAULT 50,
    love_pet          INTEGER NOT NULL DEFAULT 50,
    cooking           INTEGER NOT NULL DEFAULT 10,
    cleaning          INTEGER NOT NULL DEFAULT 10,
    charisma          INTEGER NOT NULL DEFAULT 10,
    creativity        INTEGER NOT NULL DEFAULT 10,
    updated_at        TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 2. episodes
CREATE TABLE IF NOT EXISTS episodes (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    character_name      TEXT    NOT NULL,
    timestamp           TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    game_day            INTEGER NOT NULL,
    event               TEXT    NOT NULL,
    event_en            TEXT    NOT NULL,
    emotion             TEXT    NOT NULL,
    emotion_intensity   REAL    NOT NULL DEFAULT 0.5,
    related_characters  TEXT    NOT NULL DEFAULT '[]',
    tags                TEXT    NOT NULL DEFAULT '[]',
    importance          REAL    NOT NULL DEFAULT 0.5,
    FOREIGN KEY (character_name) REFERENCES characters(name)
);

-- 3. household
CREATE TABLE IF NOT EXISTS household (
    id                  INTEGER PRIMARY KEY CHECK (id = 1),
    balance             INTEGER NOT NULL DEFAULT 0,
    total_tips          INTEGER NOT NULL DEFAULT 0,
    total_tips_youtube  INTEGER NOT NULL DEFAULT 0,
    total_tips_tiktok   INTEGER NOT NULL DEFAULT 0,
    room_type           TEXT    NOT NULL DEFAULT '1K',
    room_cleanliness    INTEGER NOT NULL DEFAULT 50,
    room_comfort        INTEGER NOT NULL DEFAULT 50,
    updated_at          TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 4. owned_items
CREATE TABLE IF NOT EXISTS owned_items (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    item_id         TEXT    NOT NULL,
    name_ja         TEXT    NOT NULL,
    name_en         TEXT    NOT NULL,
    category        TEXT    NOT NULL,
    acquired_at     TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    acquired_from   TEXT    NOT NULL
);

-- 5. food_stock
CREATE TABLE IF NOT EXISTS food_stock (
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    item_name         TEXT    NOT NULL,
    quantity          INTEGER NOT NULL DEFAULT 1,
    expires_game_day  INTEGER NULL
);

-- 6. relationships
CREATE TABLE IF NOT EXISTS relationships (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    char_a          TEXT    NOT NULL,
    char_b          TEXT    NOT NULL,
    love_level      INTEGER NOT NULL DEFAULT 50,
    trust_level     INTEGER NOT NULL DEFAULT 50,
    bond_level      INTEGER NOT NULL DEFAULT 50,
    fight_count     INTEGER NOT NULL DEFAULT 0,
    makeup_count    INTEGER NOT NULL DEFAULT 0,
    last_fight      TEXT    NULL,
    last_date       TEXT    NULL,
    FOREIGN KEY (char_a) REFERENCES characters(name),
    FOREIGN KEY (char_b) REFERENCES characters(name),
    UNIQUE (char_a, char_b)
);

-- 7. tips
CREATE TABLE IF NOT EXISTS tips (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    platform        TEXT    NOT NULL,
    amount_raw      REAL    NOT NULL,
    currency        TEXT    NOT NULL DEFAULT 'JPY',
    amount_lc       INTEGER NOT NULL,
    user_name       TEXT    NOT NULL,
    message         TEXT    NULL,
    item_triggered  TEXT    NULL,
    created_at      TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 8. comments
CREATE TABLE IF NOT EXISTS comments (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    platform        TEXT    NOT NULL,
    user_name       TEXT    NOT NULL,
    message         TEXT    NOT NULL,
    language        TEXT    NOT NULL DEFAULT 'ja',
    category        TEXT    NOT NULL DEFAULT 'general',
    processed       INTEGER NOT NULL DEFAULT 0,
    response        TEXT    NULL,
    created_at      TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 9. action_log
CREATE TABLE IF NOT EXISTS action_log (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    character_name  TEXT    NOT NULL,
    action          TEXT    NOT NULL,
    target          TEXT    NULL,
    location        TEXT    NOT NULL DEFAULT 'room',
    dialogue_ja     TEXT    NULL,
    dialogue_en     TEXT    NULL,
    emotion         TEXT    NOT NULL DEFAULT 'neutral',
    game_day        INTEGER NOT NULL,
    created_at      TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (character_name) REFERENCES characters(name)
);

-- 10. mood_log
CREATE TABLE IF NOT EXISTS mood_log (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    character_name  TEXT    NOT NULL,
    mood            INTEGER NOT NULL,
    energy          INTEGER NOT NULL,
    stress          INTEGER NOT NULL,
    hunger          INTEGER NOT NULL,
    game_day        INTEGER NOT NULL,
    created_at      TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (character_name) REFERENCES characters(name)
);

-- ==============================================
-- Indexes
-- ==============================================

CREATE INDEX IF NOT EXISTS idx_episodes_character_day
    ON episodes (character_name, game_day);

CREATE INDEX IF NOT EXISTS idx_episodes_importance
    ON episodes (importance DESC);

CREATE INDEX IF NOT EXISTS idx_owned_items_category
    ON owned_items (category);

CREATE INDEX IF NOT EXISTS idx_food_stock_expires
    ON food_stock (expires_game_day);

CREATE INDEX IF NOT EXISTS idx_relationships_chars
    ON relationships (char_a, char_b);

CREATE INDEX IF NOT EXISTS idx_tips_platform_created
    ON tips (platform, created_at);

CREATE INDEX IF NOT EXISTS idx_tips_amount_lc
    ON tips (amount_lc DESC);

CREATE INDEX IF NOT EXISTS idx_comments_unprocessed
    ON comments (processed, created_at)
    WHERE processed = 0;

CREATE INDEX IF NOT EXISTS idx_comments_platform
    ON comments (platform, created_at);

CREATE INDEX IF NOT EXISTS idx_action_log_character_day
    ON action_log (character_name, game_day);

CREATE INDEX IF NOT EXISTS idx_action_log_created
    ON action_log (created_at);

CREATE INDEX IF NOT EXISTS idx_mood_log_character_day
    ON mood_log (character_name, game_day);

-- ==============================================
-- Initial Data
-- ==============================================

INSERT INTO characters (name, age, job, hp, hunger, mood, energy, stress, love_partner, love_pet, cooking, cleaning, charisma, creativity)
VALUES
    ('John', 28, 'YouTuber',    100, 50, 50, 100, 0, 50, 50, 15, 8,  20, 25),
    ('Sara', 26, 'Illustrator', 100, 50, 50, 100, 0, 50, 50, 25, 20, 15, 30),
    ('Eve',   2, 'Cat',         100, 50, 70,  80, 0,  0, 80,  0,  0,  0, 50);

INSERT INTO household (id, balance, total_tips, total_tips_youtube, total_tips_tiktok, room_type, room_cleanliness, room_comfort)
VALUES (1, 50000, 0, 0, 0, '1K', 60, 40);

INSERT INTO relationships (char_a, char_b, love_level, trust_level, bond_level, fight_count, makeup_count)
VALUES
    ('John', 'Sara', 60, 55, 50, 0, 0),
    ('Eve',  'John',  0, 40, 45, 0, 0),
    ('Eve',  'Sara',  0, 45, 50, 0, 0);