import sqlite3
import csv

### Run this before running the website 
# This script imports data from a CSV file into a SQLite database. 

# Path to the CSV file
# csv_file_path = 'posts.csv'
csv_file_path = 'tweet_embeds_500.csv'

# Connect to the SQLite database
conn = sqlite3.connect('./db/posts.db')
cursor = conn.cursor()

# Create the posts table if it doesn't exist
cursor.execute('''
    CREATE TABLE IF NOT EXISTS posts (
        id TEXT PRIMARY KEY,
        embed_html TEXT,
        rating_count INTEGER DEFAULT 0) 
    ''')

# Read the CSV file and insert data into the posts table
with open(csv_file_path, newline='', encoding='utf-8') as csvfile:
    reader = csv.DictReader(csvfile)
    for row in reader:
        cursor.execute('''
            INSERT OR REPLACE INTO posts (id, embed_html)
            VALUES (?, ?)
        ''', (row['id'], row['embed_html']))

# Commit changes and close the connection
conn.commit()
conn.close()

print("Data from CSV has been successfully inserted into posts.db.")