import sqlite3
import os

DB_PATH = os.environ.get("DB_PATH", os.path.join(os.path.dirname(__file__), "shop.db"))

def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_db()
    c = conn.cursor()

    c.execute("""
        CREATE TABLE IF NOT EXISTS stickers (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            character TEXT NOT NULL,
            tier TEXT NOT NULL,
            price_cents INTEGER NOT NULL,
            stock INTEGER,  -- NULL = unlimited
            sold INTEGER DEFAULT 0,
            image_path TEXT NOT NULL,
            description TEXT
        )
    """)

    c.execute("""
        CREATE TABLE IF NOT EXISTS purchases (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            discord_user_id TEXT NOT NULL,
            sticker_id TEXT NOT NULL,
            stripe_session_id TEXT UNIQUE,
            status TEXT DEFAULT 'pending',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    c.execute("""
        CREATE TABLE IF NOT EXISTS inventory (
            discord_user_id TEXT NOT NULL,
            sticker_id TEXT NOT NULL,
            acquired_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (discord_user_id, sticker_id)
        )
    """)

    conn.commit()
    conn.close()
    print("Database initialized.")

def seed_stickers():
    stickers = [
        # Tien - Musical (Standard & Limited)
        ("tien-default",    "Tien",         "Tien", "standard",  99,   None, "/Musical/Tien.jpeg",          "The jade elephant himself."),
        ("tien-dance",      "Tien Dancing", "Tien", "limited",   299,  100,  "/Musical/Tien-dance.jpeg",    "Tien full of joy!"),
        ("tien-harmonica",  "Tien Harmonica","Tien","standard",  99,   None, "/Musical/Tien-harmonica.jpeg","Playing his favourite tune."),
        ("tien-playing",    "Tien Playing", "Tien", "standard",  99,   None, "/Musical/Tien-playing.jpeg",  "Focused and creative."),
        ("tien-sitting",    "Tien Chilling","Tien", "standard",  99,   None, "/Musical/Tien-sitting.jpeg",  "Just vibing."),
        ("tien-surprised",  "Tien Surprised","Tien","limited",   299,  100,  "/Musical/Tien-surprised.jpeg","!!!!"),
        ("tien-walking",    "Tien Walking", "Tien", "standard",  99,   None, "/Musical/Tien-walking.jpeg",  "On the move."),
        ("tien-nobg",       "Tien (Clean)", "Tien", "collector", 499,  50,   "/Musical/Tien-nobackground.jpeg","Transparent background."),
        # Nishang - Delicate
        ("nishang-default", "Nishang",      "Nishang","standard",99,   None, "/Delicate/Nishang.jpeg",      "Luminous and graceful."),
        ("nishang-original","Nishang Classic","Nishang","limited",299, 100,  "/Delicate/Nishang-original.jpeg","The original render."),
        # Xiaohan - Ancient
        ("xiaohan-default", "Xiaohan",      "Xiaohan","standard",99,  None, "/Ancient/Xiaohan.jpeg",        "The ancient dragon guide."),
        ("xiaohan-cliff",   "Xiaohan Cliff","Xiaohan","limited", 299,  100,  "/Ancient/Xiaohan-cliff.jpeg",  "Epic cliff scene."),
        ("xiaohan-clouds",  "Xiaohan Clouds","Xiaohan","limited",299,  100,  "/Ancient/Xiaohan-clouds.jpeg", "Among the clouds."),
        ("xiaohan-valley",  "Xiaohan Valley","Xiaohan","collector",499,50,   "/Ancient/Xiaohan-valley.jpeg", "The great valley."),
        ("xiaohan-nobg",    "Xiaohan (Clean)","Xiaohan","collector",499,50,  "/Ancient/Xiaohan-nobackground.jpeg","Transparent background."),
        # Shi Yin
        ("shiyin-default",  "Shi Yin",      "Shi Yin","limited", 299,  100,  "/Shi Yin/Shi Yin.jpeg",        "Mysterious Shi Yin."),
        # Eternal Path Logo - Ultra Rare
        ("eternal-logo",    "Eternal Path Logo","Eternal Path Media","ultra_rare",1499,10,
         "/brand/Eternal Path Brand.jpeg","Ultra rare — only 10 exist."),
    ]

    conn = get_db()
    c = conn.cursor()
    base = os.path.join(os.path.dirname(os.path.dirname(__file__)), "stickers", "assets")

    for s in stickers:
        sid, name, char, tier, price, stock, rel_path, desc = s
        full_path = os.path.join(base, rel_path.lstrip("/"))
        c.execute("""
            INSERT OR IGNORE INTO stickers
            (id, name, character, tier, price_cents, stock, image_path, description)
            VALUES (?,?,?,?,?,?,?,?)
        """, (sid, name, char, tier, price, stock, full_path, desc))

    conn.commit()
    conn.close()
    print("Stickers seeded.")

if __name__ == "__main__":
    init_db()
    seed_stickers()