import sqlite3
import os

# ----------------------------
# 1. Create project folders
# ----------------------------
folders = [
    "data",
    "data/raw_satellites",
    "data/raw_satellites/china",
    "data/processed",
    "logs"
]

for folder in folders:
    os.makedirs(folder, exist_ok=True)

print("Folders created ✅")

# ----------------------------
# 2. Create SQLite database
# ----------------------------
db_path = "data/satellites.db"
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

print("Database created ✅")

# ----------------------------
# 3. Create tables
# ----------------------------

# Countries table
cursor.execute("""
CREATE TABLE IF NOT EXISTS countries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    country TEXT,
    country_code TEXT,
    country_url TEXT
)
""")

# Categories table
cursor.execute("""
CREATE TABLE IF NOT EXISTS categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    country TEXT,
    category TEXT,
    category_url TEXT
)
""")

# Satellite index table (MOST IMPORTANT)
cursor.execute("""
CREATE TABLE IF NOT EXISTS satellites (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    country TEXT,
    category TEXT,
    satellite_name TEXT,
    satellite_url TEXT,
    scraped INTEGER DEFAULT 0
)
""")

conn.commit()
conn.close()

print("All tables created ✅")
print("Database ready 🚀")