-- =====================================================
-- magazine_articles 테이블에 source_article_id 컬럼 추가
-- 전문 분석 기사가 원본 기사를 참조하도록
-- =====================================================

USE ahforla6;

-- source_article_id 컬럼 추가 (이미 있으면 무시)
ALTER TABLE magazine_articles 
ADD COLUMN IF NOT EXISTS source_article_id INT DEFAULT NULL COMMENT '원본 기사 ID (전문분석 기사인 경우)' AFTER id;

-- 외래키 추가
ALTER TABLE magazine_articles
ADD CONSTRAINT fk_source_article 
FOREIGN KEY (source_article_id) REFERENCES magazine_articles(id) 
ON DELETE SET NULL;

-- 인덱스 추가
CREATE INDEX IF NOT EXISTS idx_source_article_id ON magazine_articles(source_article_id);

-- 기존 전문분석 기사에 대한 관계 설정 (선택사항)
-- 제목에서 원본 기사를 찾아 연결
UPDATE magazine_articles ma1
JOIN magazine_articles ma2 
ON ma1.title LIKE CONCAT('[전문분석] %', ma2.title, '%')
OR ma1.title LIKE CONCAT('[심층분석] %', ma2.title, '%')
SET ma1.source_article_id = ma2.id
WHERE ma1.source_article_id IS NULL
AND ma1.author = 'AI 보험전문기자';

-- 확인
SELECT 
    ma1.id as professional_article_id,
    ma1.title as professional_title,
    ma2.id as source_article_id,
    ma2.title as source_title
FROM magazine_articles ma1
LEFT JOIN magazine_articles ma2 ON ma1.source_article_id = ma2.id
WHERE ma1.author = 'AI 보험전문기자'
LIMIT 10;

-- 통계
SELECT 
    '전체 기사' as type,
    COUNT(*) as count
FROM magazine_articles
WHERE status = 'published'
UNION ALL
SELECT 
    'AI 크롤링 기사' as type,
    COUNT(*) as count
FROM magazine_articles
WHERE collection_method = 'ai_crawling'
AND title NOT LIKE '[전문분석]%'
AND title NOT LIKE '[심층분석]%'
UNION ALL
SELECT 
    'AI 전문분석 기사' as type,
    COUNT(*) as count
FROM magazine_articles
WHERE author = 'AI 보험전문기자'
UNION ALL
SELECT 
    '원본 연결된 전문기사' as type,
    COUNT(*) as count
FROM magazine_articles
WHERE author = 'AI 보험전문기자'
AND source_article_id IS NOT NULL;

