-- 취약 웹앱 데이터베이스 초기화

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    password TEXT NOT NULL,  -- 취약: 평문 저장
    email TEXT,
    role TEXT DEFAULT 'user',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

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 products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL,
    description TEXT,
    secret_code TEXT  -- 취약: 민감 정보 노출
);

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 users (username, password, email, role) VALUES
    ('admin', 'admin123', 'admin@foruslab.local', 'admin'),
    ('user1', 'password1', 'user1@foruslab.local', 'user'),
    ('user2', 'qwerty', 'user2@foruslab.local', 'user'),
    ('guest', 'guest', 'guest@foruslab.local', 'guest');

INSERT INTO products (name, price, description, secret_code) VALUES
    ('서버 점검 패키지', 500000, '기본 인프라 점검', 'SECRET-KEY-7823'),
    ('웹 취약점 진단', 800000, '웹 애플리케이션 전체 진단', 'API-TOKEN-9921'),
    ('종합 보안 감사', 2000000, '인프라+웹 종합', 'ADMIN-PASS-HASH-4421');

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, '완료');
