-- =====================================================
-- 보험뉴스 매거진 데이터베이스 설계
-- AI 자동 수집 + 수동 업로드 시스템
-- =====================================================

USE ahforla6;

-- =====================================================
-- 1. 매거진 카테고리 테이블
-- =====================================================
CREATE TABLE IF NOT EXISTS magazine_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL COMMENT '카테고리명',
    slug VARCHAR(100) NOT NULL UNIQUE COMMENT 'URL 슬러그',
    description TEXT COMMENT '카테고리 설명',
    icon VARCHAR(50) DEFAULT 'fas fa-newspaper' COMMENT '아이콘 클래스',
    color VARCHAR(20) DEFAULT '#3b82f6' COMMENT '테마 색상',
    sort_order INT DEFAULT 0 COMMENT '정렬 순서',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='매거진 카테고리';

-- =====================================================
-- 2. 매거진 기사 테이블
-- =====================================================
CREATE TABLE IF NOT EXISTS magazine_articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    
    -- 기사 기본 정보
    title VARCHAR(500) NOT NULL COMMENT '기사 제목',
    summary TEXT COMMENT '기사 요약',
    content LONGTEXT NOT NULL COMMENT '기사 내용',
    
    -- 메타 정보
    author VARCHAR(100) COMMENT '작성자',
    source_url VARCHAR(1000) COMMENT '원본 URL',
    source_site VARCHAR(100) COMMENT '출처 사이트',
    featured_image VARCHAR(500) COMMENT '대표 이미지 URL',
    
    -- AI 처리 정보
    ai_summary TEXT COMMENT 'AI 자동 요약',
    ai_tags JSON COMMENT 'AI 추출 태그',
    ai_category_confidence DECIMAL(5,4) DEFAULT 0 COMMENT 'AI 카테고리 신뢰도',
    
    -- 수집 방식
    collection_method ENUM('manual', 'ai_crawling') NOT NULL DEFAULT 'manual' COMMENT '수집 방식',
    crawl_date DATETIME COMMENT '크롤링 날짜',
    
    -- 게시 정보
    publish_date DATETIME NOT NULL COMMENT '게시 날짜',
    views INT DEFAULT 0 COMMENT '조회수',
    likes INT DEFAULT 0 COMMENT '좋아요 수',
    
    -- 상태 관리
    status ENUM('draft', 'published', 'featured', 'archived') DEFAULT 'draft' COMMENT '게시 상태',
    is_featured BOOLEAN DEFAULT FALSE COMMENT '추천 기사 여부',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (category_id) REFERENCES magazine_categories(id) ON DELETE CASCADE,
    
    INDEX idx_category (category_id),
    INDEX idx_publish_date (publish_date),
    INDEX idx_status (status),
    INDEX idx_featured (is_featured),
    INDEX idx_views (views),
    INDEX idx_collection_method (collection_method),
    FULLTEXT idx_search (title, summary, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='매거진 기사';

-- =====================================================
-- 3. 매거진 태그 테이블
-- =====================================================
CREATE TABLE IF NOT EXISTS magazine_tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE COMMENT '태그명',
    slug VARCHAR(100) NOT NULL UNIQUE COMMENT 'URL 슬러그',
    color VARCHAR(20) DEFAULT '#6b7280' COMMENT '태그 색상',
    usage_count INT DEFAULT 0 COMMENT '사용 횟수',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='매거진 태그';

-- =====================================================
-- 4. 기사-태그 관계 테이블
-- =====================================================
CREATE TABLE IF NOT EXISTS magazine_article_tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    article_id INT NOT NULL,
    tag_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (article_id) REFERENCES magazine_articles(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES magazine_tags(id) ON DELETE CASCADE,
    
    UNIQUE KEY uk_article_tag (article_id, tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='기사-태그 관계';

-- =====================================================
-- 5. 사용자 인터랙션 테이블
-- =====================================================
CREATE TABLE IF NOT EXISTS magazine_interactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    article_id INT NOT NULL,
    interaction_type ENUM('view', 'like', 'bookmark', 'share') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES magazine_articles(id) ON DELETE CASCADE,
    
    INDEX idx_user_article (user_id, article_id),
    INDEX idx_interaction_type (interaction_type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='사용자 인터랙션';

-- =====================================================
-- 6. AI 크롤링 설정 테이블
-- =====================================================
CREATE TABLE IF NOT EXISTS magazine_crawl_sources (
    id INT PRIMARY KEY AUTO_INCREMENT,
    site_name VARCHAR(100) NOT NULL COMMENT '사이트명',
    base_url VARCHAR(500) NOT NULL COMMENT '기본 URL',
    rss_url VARCHAR(500) COMMENT 'RSS URL',
    crawl_selector VARCHAR(500) COMMENT 'CSS 셀렉터',
    category_mapping JSON COMMENT '카테고리 매핑',
    
    -- 크롤링 설정
    crawl_interval INT DEFAULT 3600 COMMENT '크롤링 간격(초)',
    max_articles_per_crawl INT DEFAULT 10 COMMENT '회당 최대 수집 기사',
    
    -- 상태
    is_active BOOLEAN DEFAULT TRUE,
    last_crawl_at DATETIME COMMENT '마지막 크롤링 시간',
    last_crawl_count INT DEFAULT 0 COMMENT '마지막 수집 기사 수',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI 크롤링 소스';

-- =====================================================
-- 기본 데이터 입력
-- =====================================================

-- 매거진 카테고리 기본 데이터
INSERT IGNORE INTO magazine_categories (name, slug, description, icon, color, sort_order) VALUES
('업계 동향', 'industry-trends', '보험업계 최신 동향 및 이슈', 'fas fa-chart-line', '#3b82f6', 1),
('정책 변화', 'policy-changes', '금융당국 정책 및 규제 변화', 'fas fa-gavel', '#dc2626', 2),
('신상품 정보', 'new-products', '새로 출시되는 보험상품 소개', 'fas fa-gift', '#059669', 3),
('시장 분석', 'market-analysis', '보험시장 통계 및 분석 리포트', 'fas fa-analytics', '#7c3aed', 4),
('영업 팁', 'sales-tips', 'FC를 위한 영업 노하우 및 팁', 'fas fa-lightbulb', '#ea580c', 5),
('법률 정보', 'legal-info', '보험 관련 법률 및 판례', 'fas fa-balance-scale', '#1f2937', 6),
('인터뷰', 'interviews', '업계 인사 인터뷰 및 성공 사례', 'fas fa-microphone', '#be185d', 7);

-- AI 크롤링 소스 기본 데이터
INSERT IGNORE INTO magazine_crawl_sources (site_name, base_url, rss_url, crawl_interval, max_articles_per_crawl, category_mapping) VALUES
('보험신문', 'https://www.insnews.co.kr/', 'https://www.insnews.co.kr/rss/allArticle.xml', 3600, 10, 
 '{"업계동향": 1, "정책": 2, "상품": 3}'),
('보험매일', 'https://www.fins.co.kr/', 'https://www.fins.co.kr/rss/allArticle.xml', 3600, 10,
 '{"종합뉴스": 1, "생명보험": 1, "손해보험": 1, "GA": 1, "상품": 3, "보험정책": 2}');

-- 기본 태그 데이터
INSERT IGNORE INTO magazine_tags (name, slug, color) VALUES
('생명보험', 'life-insurance', '#3b82f6'),
('손해보험', 'non-life-insurance', '#dc2626'),
('GA', 'ga', '#059669'),
('신상품', 'new-product', '#ea580c'),
('정책변화', 'policy-change', '#7c3aed'),
('시장분석', 'market-analysis', '#1f2937'),
('영업팁', 'sales-tip', '#be185d'),
('규제', 'regulation', '#6b7280'),
('통계', 'statistics', '#0891b2'),
('트렌드', 'trend', '#c2410c');

-- =====================================================
-- 뷰 생성
-- =====================================================

-- 매거진 기사 조회용 뷰
CREATE OR REPLACE VIEW v_magazine_articles AS
SELECT 
    ma.id,
    ma.title,
    ma.summary,
    ma.ai_summary,
    ma.author,
    ma.source_site,
    ma.featured_image,
    ma.publish_date,
    ma.views,
    ma.likes,
    ma.status,
    ma.is_featured,
    ma.collection_method,
    mc.name as category_name,
    mc.slug as category_slug,
    mc.color as category_color,
    GROUP_CONCAT(mt.name) as tags
FROM magazine_articles ma
JOIN magazine_categories mc ON ma.category_id = mc.id
LEFT JOIN magazine_article_tags mat ON ma.id = mat.article_id
LEFT JOIN magazine_tags mt ON mat.tag_id = mt.id
WHERE ma.status = 'published'
GROUP BY ma.id
ORDER BY ma.publish_date DESC;

-- 인기 기사 조회용 뷰
CREATE OR REPLACE VIEW v_popular_articles AS
SELECT 
    ma.*,
    mc.name as category_name,
    mc.color as category_color,
    (ma.views * 0.7 + ma.likes * 0.3) as popularity_score
FROM magazine_articles ma
JOIN magazine_categories mc ON ma.category_id = mc.id
WHERE ma.status = 'published'
ORDER BY popularity_score DESC;
