Build: Migrate the App to SQLite

import anthropic
from dotenv import load_dotenv
from database import Database

load_dotenv()
client = anthropic.Anthropic()
db = Database("prompt_vault.db")


def generate_prompt_text(shot, platform):
    message = client.messages.create(
        model="claude-sonnet-4-6",
        max_tokens=300,
        system="You are an expert AI video prompt engineer.",
        messages=[{
            "role": "user",
            "content": f"Write a production-ready {platform} AI video prompt for: {shot}. "
                       f"Include camera, lighting, mood. Under 100 words. Return only the prompt."
        }]
    )
    return message.content[0].text


print("=== DVP Prompt Vault ===\n")

while True:
    all_prompts = db.get_all()
    print(f"Library: {len(all_prompts)} prompts")
    print("1. View all  2. Generate  3. Search  4. Filter  5. Delete by ID  6. Stats  7. Quit")
    choice = input("\nChoose: ")

    if choice == "1":
        if not all_prompts:
            print("\n  (empty)\n")
        else:
            for row in all_prompts:
                # row is a sqlite3.Row — access by column name
                print(f"  #{row['id']} [{row['platform']}] {row['shot']}")
                preview = row['prompt'][:60] + "..." if len(row['prompt']) > 60 else row['prompt']
                print(f"     {preview}\n")

    elif choice == "2":
        shot = input("Describe your shot: ")
        platform = input("Platform (Kling / Runway / Veo): ")
        valid = ["Kling", "Runway", "Veo"]
        if platform.strip().title() not in valid:
            print(f"  Unknown platform. Choose: {', '.join(valid)}\n")
            continue
        platform = platform.strip().title()
        try:
            text = generate_prompt_text(shot, platform)
            print(f"\n{text}\n")
            if input("Save? (y/n): ").lower() == "y":
                new_id = db.add_prompt(platform, shot, text)
                print(f"Saved as #{new_id}!\n")
        except Exception as e:
            print(f"  API error: {e}\n")

    elif choice == "3":
        query = input("Search: ")
        results = db.search(query)
        print(f"\n  {len(results)} match{'es' if len(results) != 1 else ''}")
        for row in results:
            print(f"    #{row['id']} [{row['platform']}] {row['shot']}")
        print()

    elif choice == "4":
        platform = input("Filter by platform: ")
        results = db.get_by_platform(platform)
        print(f"\n  {len(results)} {platform} prompts")
        for row in results:
            print(f"    #{row['id']} {row['shot']}")
        print()

    elif choice == "5":
        prompt_id = input("Delete prompt ID # (or 'cancel'): ")
        if prompt_id.lower() != "cancel":
            try:
                if db.delete(int(prompt_id)):
                    print(f"  Deleted #{prompt_id}\n")
                else:
                    print(f"  No prompt with ID {prompt_id}\n")
            except ValueError:
                print("  Enter a number.\n")

    elif choice == "6":
        counts = db.count_by_platform()
        total = sum(counts.values())
        print(f"\n  Total: {total} prompts")
        for platform, count in counts.items():
            print(f"    {platform}: {count}")
        print()

    elif choice == "7":
        db.close()
        print("Bye!")
        break