

CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    author_id INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id INTEGER,
    author TEXT NOT NULL,
    content TEXT NOT NULL,  -- 취약: XSS 가능
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    product_id INTEGER,
    amount INTEGER,
    status TEXT DEFAULT 'pending'
);

-- WEB-20: login_attempts 테이블 (Rate Limiting 실습)
CREATE TABLE IF NOT EXISTS login_attempts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ip TEXT NOT NULL,
    username TEXT NOT NULL,
    attempted_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO posts (title, content, author_id) VALUES
    ('서버 점검 결과', '2024년 4월 점검 결과 이상 없음', 1),
    ('보안 공지', '패스워드를 주기적으로 변경하세요', 1);

-- 주문 데이터 (WEB-14 IDOR 실습용)
INSERT INTO orders (user_id, product_id, amount, status) VALUES
    (1, 1, 500000, '완료'),
    (1, 2, 800000, '완료'),
    (2, 1, 500000, '배송중'),
    (2, 3, 2000000, '결제대기'),
    (3, 2, 800000, '완료');
