import sqlite3

def init():
    conn = sqlite3.connect("data.db")
    cursor = conn.cursor()
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        username TEXT NOT NULL,
        password TEXT NOT NULL
    );
    """)
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS posts (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        author TEXT NOT NULL,
        content TEXT NOT NULL
    );
    """)

    conn.commit()
    conn.close()

def create_user(username, password):
    conn = sqlite3.connect("data.db")
    cursor = conn.cursor()
    cursor.execute("INSERT INTO users (username, password) VALUES (?, ?)", (username, password))
    conn.commit()
    conn.close()

def check_user(username, password):
    conn = sqlite3.connect("data.db")
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE username = ? AND password = ?", (username, password))
    data = cursor.fetchone()
    conn.close()
    return data

def get_post(post_id):
    conn = sqlite3.connect("data.db")
    conn.row_factory = sqlite3.Row

    cursor = conn.cursor()
    cursor.execute("SELECT * FROM posts WHERE id = ?", (post_id,))
    data = cursor.fetchone()

    conn.close()
    return data

def get_posts():
    conn = sqlite3.connect("data.db")
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM posts")
    data = cursor.fetchall()
    conn.close()
    return data

def get_posts_search(q):
    conn = sqlite3.connect("data.db")
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM posts WHERE title LIKE ?", (f"%{q}%",))
    data = cursor.fetchall()
    conn.close()
    return data

def create_post(title, author, content):
    conn = sqlite3.connect("data.db")
    cursor = conn.cursor()
    cursor.execute("INSERT INTO posts (title, author, content) VALUES (?, ?, ?)", (title, author, content))
    post_id = cursor.lastrowid
    conn.commit()
    conn.close()
    return post_id

def update_post(post_id, title, author, content):
    conn = sqlite3.connect("data.db")
    cursor = conn.cursor()
    # 기존 글이 쓴 사람이 author인지 검증
    cursor.execute("SELECT * FROM posts WHERE id = ?", (post_id,))
    post = cursor.fetchone()
    if post and post[2] != author:
        return "You are not the author of this post"
    cursor.execute("UPDATE posts SET title = ?, content = ? WHERE id = ?", (title, content, post_id))
    conn.commit()
    conn.close()

def delete_post(post_id):
    conn = sqlite3.connect("data.db")
    cursor = conn.cursor()
    cursor.execute("DELETE FROM posts WHERE id = ?", (post_id,))
    conn.commit()
    conn.close()