-- ============================================================
-- Trading AI - PostgreSQL 테이블 초기화
-- NAS (192.168.0.36:32770) 에서 실행
-- ============================================================

-- 뉴스 분석 결과 테이블
CREATE TABLE IF NOT EXISTS news_analysis (
    id              SERIAL PRIMARY KEY,
    title           TEXT NOT NULL,
    url             TEXT DEFAULT '',
    source          VARCHAR(100) DEFAULT '',
    published_at    TIMESTAMP WITH TIME ZONE,
    hash            VARCHAR(16) UNIQUE NOT NULL,
    
    -- AI 분석 결과
    sentiment       VARCHAR(10) DEFAULT '중립',     -- 호재/악재/중립
    intensity       INTEGER DEFAULT 0,              -- 1~5
    primary_stock   VARCHAR(10) DEFAULT '',          -- 주요 종목코드
    affected_stocks JSONB DEFAULT '[]'::jsonb,       -- 영향 종목 목록
    reason          TEXT DEFAULT '',                  -- 판단 근거
    investment_action VARCHAR(20) DEFAULT '관망',     -- 매수관심/매도관심/관망
    
    -- 형태소 분석 결과
    keywords        JSONB DEFAULT '[]'::jsonb,
    stock_names     JSONB DEFAULT '[]'::jsonb,
    stock_codes     JSONB DEFAULT '[]'::jsonb,
    
    -- 메타데이터
    similar_count   INTEGER DEFAULT 0,
    analyzed_at     TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 인덱스
CREATE INDEX IF NOT EXISTS idx_news_hash ON news_analysis(hash);
CREATE INDEX IF NOT EXISTS idx_news_sentiment ON news_analysis(sentiment);
CREATE INDEX IF NOT EXISTS idx_news_primary_stock ON news_analysis(primary_stock);
CREATE INDEX IF NOT EXISTS idx_news_published_at ON news_analysis(published_at DESC);
CREATE INDEX IF NOT EXISTS idx_news_analyzed_at ON news_analysis(analyzed_at DESC);
CREATE INDEX IF NOT EXISTS idx_news_intensity ON news_analysis(intensity DESC);
CREATE INDEX IF NOT EXISTS idx_news_investment_action ON news_analysis(investment_action);

-- 종목별 빠른 조회용 GIN 인덱스
CREATE INDEX IF NOT EXISTS idx_news_affected_stocks ON news_analysis USING GIN (affected_stocks);
CREATE INDEX IF NOT EXISTS idx_news_stock_codes ON news_analysis USING GIN (stock_codes);

-- 유용한 뷰: 최근 호재/악재 요약
CREATE OR REPLACE VIEW v_recent_signals AS
SELECT 
    sentiment,
    intensity,
    title,
    primary_stock,
    affected_stocks,
    investment_action,
    reason,
    published_at,
    analyzed_at
FROM news_analysis
WHERE sentiment IN ('호재', '악재')
  AND analyzed_at >= NOW() - INTERVAL '24 hours'
ORDER BY intensity DESC, analyzed_at DESC;

-- 기술적 분석 결과 테이블
CREATE TABLE IF NOT EXISTS stock_technical (
    id          SERIAL PRIMARY KEY,
    stock_code  VARCHAR(10) UNIQUE NOT NULL,
    stock_name  VARCHAR(100) DEFAULT '',
    price       INTEGER DEFAULT 0,
    ma5         FLOAT DEFAULT 0,
    ma20        FLOAT DEFAULT 0,
    ma60        FLOAT DEFAULT 0,
    ma120       FLOAT DEFAULT 0,
    rsi         FLOAT DEFAULT 50,
    macd        FLOAT DEFAULT 0,
    macd_signal FLOAT DEFAULT 0,
    macd_hist   FLOAT DEFAULT 0,
    bb_upper    FLOAT DEFAULT 0,
    bb_mid      FLOAT DEFAULT 0,
    bb_lower    FLOAT DEFAULT 0,
    pct_b       FLOAT DEFAULT 0.5,
    stoch_k     FLOAT DEFAULT 50,
    stoch_d     FLOAT DEFAULT 50,
    vol_ratio   FLOAT DEFAULT 1,
    tech_score  FLOAT DEFAULT 0,
    signal      VARCHAR(10) DEFAULT '관망',
    signals     JSONB DEFAULT '[]'::jsonb,
    targets     JSONB DEFAULT '{}'::jsonb,
    analyzed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ta_score  ON stock_technical(tech_score DESC);
CREATE INDEX IF NOT EXISTS idx_ta_signal ON stock_technical(signal);
CREATE INDEX IF NOT EXISTS idx_ta_code   ON stock_technical(stock_code);

-- 유용한 뷰: 종목별 뉴스 카운트
CREATE OR REPLACE VIEW v_stock_news_count AS
SELECT 
    primary_stock,
    sentiment,
    COUNT(*) as count,
    AVG(intensity) as avg_intensity,
    MAX(analyzed_at) as latest_at
FROM news_analysis
WHERE primary_stock != ''
  AND analyzed_at >= NOW() - INTERVAL '7 days'
GROUP BY primary_stock, sentiment
ORDER BY count DESC;

-- DART 기업 정보 테이블
CREATE TABLE IF NOT EXISTS dart_corps (
    stock_code  VARCHAR(10)  PRIMARY KEY,
    corp_code   VARCHAR(20)  NOT NULL,
    corp_name   VARCHAR(200) NOT NULL,
    modify_date VARCHAR(20)  DEFAULT '',
    is_active   BOOLEAN      DEFAULT false
);

CREATE INDEX IF NOT EXISTS idx_dart_corps_active   ON dart_corps(is_active);
CREATE INDEX IF NOT EXISTS idx_dart_corps_corp_code ON dart_corps(corp_code);

-- DART 재무제표 테이블 (버핏 가치지표 포함)
CREATE TABLE IF NOT EXISTS dart_financials (
    id                 SERIAL PRIMARY KEY,
    stock_code         VARCHAR(10)  NOT NULL,
    corp_code          VARCHAR(20),
    corp_name          VARCHAR(200),
    bsns_year          VARCHAR(4)   NOT NULL,
    reprt_code         VARCHAR(10)  NOT NULL,
    reprt_name         VARCHAR(50),
    revenue            BIGINT       DEFAULT 0,
    operating_profit   BIGINT       DEFAULT 0,
    net_income         BIGINT       DEFAULT 0,
    total_assets       BIGINT       DEFAULT 0,
    total_liabilities  BIGINT       DEFAULT 0,
    total_equity       BIGINT       DEFAULT 0,
    operating_cashflow BIGINT       DEFAULT 0,
    roe                FLOAT        DEFAULT 0,
    operating_margin   FLOAT        DEFAULT 0,
    net_margin         FLOAT        DEFAULT 0,
    debt_ratio         FLOAT        DEFAULT 0,
    revenue_growth     FLOAT        DEFAULT 0,
    fcf_ratio          FLOAT        DEFAULT 0,
    collected_at       TIMESTAMP    DEFAULT NOW(),
    UNIQUE(stock_code, bsns_year, reprt_code)
);

CREATE INDEX IF NOT EXISTS idx_fin_stock ON dart_financials(stock_code);
CREATE INDEX IF NOT EXISTS idx_fin_year  ON dart_financials(bsns_year DESC);
CREATE INDEX IF NOT EXISTS idx_fin_roe   ON dart_financials(roe DESC);