Unified API layer for applications

# Create a comprehensive application schema
gibson modify "Create an application with users, posts, comments, and tags. Users can create posts, posts can have multiple tags, and users can comment on posts"

# Generate Pydantic schemas for API validation
gibson code schemas

# Generate SQLAlchemy models for database operations
gibson code models

# Generate all Python code
gibson code base

-- Users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    bio TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Posts table
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    user_id INT NOT NULL,
    published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Comments table
CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT NOT NULL,
    user_id INT NOT NULL,
    post_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

-- Tags table
CREATE TABLE tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

-- Post-tags relationship
CREATE TABLE post_tags (
    post_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id) REFERENCES tags(id)
);

from pydantic import BaseModel, EmailStr
from datetime import datetime
from typing import Optional, List

class User(BaseModel):
    id: Optional[int] = None
    username: str
    email: EmailStr
    bio: Optional[str] = None
    created_at: Optional[datetime] = None

class Post(BaseModel):
    id: Optional[int] = None
    title: str
    content: str
    user_id: int
    published: bool = False
    created_at: Optional[datetime] = None

class Comment(BaseModel):
    id: Optional[int] = None
    content: str
    user_id: int
    post_id: int
    created_at: Optional[datetime] = None

class Tag(BaseModel):
    id: Optional[int] = None
    name: str
    description: Optional[str] = None

class PostTag(BaseModel):
    post_id: int
    tag_id: int

from sqlalchemy import Column, Integer, String, Text, Boolean, DateTime, ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from datetime import datetime

Base = declarative_base()

# Association table for many-to-many relationship
post_tags = Table('post_tags', Base.metadata,
    Column('post_id', Integer, ForeignKey('posts.id'), primary_key=True),
    Column('tag_id', Integer, ForeignKey('tags.id'), primary_key=True)
)

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), unique=True, nullable=False)
    email = Column(String(255), unique=True, nullable=False)
    bio = Column(Text)
    created_at = Column(DateTime, default=datetime.utcnow)

    posts = relationship("Post", back_populates="user")
    comments = relationship("Comment", back_populates="user")

class Post(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(255), nullable=False)
    content = Column(Text, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    published = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    user = relationship("User", back_populates="posts")
    comments = relationship("Comment", back_populates="post")
    tags = relationship("Tag", secondary=post_tags, back_populates="posts")

class Comment(Base):
    __tablename__ = 'comments'

    id = Column(Integer, primary_key=True, autoincrement=True)
    content = Column(Text, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    post_id = Column(Integer, ForeignKey('posts.id'), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    user = relationship("User", back_populates="comments")
    post = relationship("Post", back_populates="comments")

class Tag(Base):
    __tablename__ = 'tags'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), unique=True, nullable=False)
    description = Column(Text)

    posts = relationship("Post", secondary=post_tags, back_populates="tags")

import requests

# Create a new user
user_data = {
    "username": "john_doe",
    "email": "john@example.com",
    "bio": "Software developer"
}
response = requests.post("https://api.gibsonai.com/v1/-/users", json=user_data)
new_user = response.json()

# Get all posts with pagination
response = requests.get("https://api.gibsonai.com/v1/-/posts?page=1&limit=10")
posts = response.json()

# Create a new post
post_data = {
    "title": "My First Post",
    "content": "This is my first blog post!",
    "user_id": new_user["id"],
    "published": True
}
response = requests.post("https://api.gibsonai.com/v1/-/posts", json=post_data)
new_post = response.json()

# Add tags to the post
tag_data = {"name": "technology", "description": "Tech-related posts"}
response = requests.post("https://api.gibsonai.com/v1/-/tags", json=tag_data)
tag = response.json()

# Associate tag with post
post_tag_data = {"post_id": new_post["id"], "tag_id": tag["id"]}
response = requests.post("https://api.gibsonai.com/v1/-/post-tags", json=post_tag_data)

# Complex queries using text-to-SQL
query = """
SELECT p.title, p.content, u.username, COUNT(c.id) as comment_count
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.published = true
GROUP BY p.id, p.title, p.content, u.username
ORDER BY comment_count DESC
LIMIT 10
"""

response = requests.post("https://api.gibsonai.com/v1/-/query", json={"query": query})
popular_posts = response.json()

from sqlalchemy import create_engine, func
from sqlalchemy.orm import sessionmaker

# Use connection string from GibsonAI UI
engine = create_engine("your-connection-string-from-ui")
Session = sessionmaker(bind=engine)
session = Session()

# Complex queries using SQLAlchemy
popular_posts = session.query(
    Post.title,
    Post.content,
    User.username,
    func.count(Comment.id).label('comment_count')
).join(User).outerjoin(Comment).filter(
    Post.published
).group_by(Post.id).order_by(
    func.count(Comment.id).desc()
).limit(10).all()

# Query with relationships
user_with_posts = session.query(User).filter(
    User.username == 'john_doe'
).first()
user_posts = user_with_posts.posts

# Filter posts by published status with pagination
response = requests.get("https://api.gibsonai.com/v1/-/posts", params={
    "published": "true",
    "page": 1,
    "limit": 5,
    "sort": "created_at",
    "order": "desc"
})
filtered_posts = response.json()

# Filter users by username pattern
response = requests.get("https://api.gibsonai.com/v1/-/users", params={
    "username__like": "john%"
})
matching_users = response.json()

# Get user with their posts and comments
user_id = 1
response = requests.get(f"https://api.gibsonai.com/v1/-/users/{user_id}", params={
    "include": "posts,comments"
})
user_with_relations = response.json()

# Get posts with their tags
response = requests.get("https://api.gibsonai.com/v1/-/posts", params={
    "include": "tags,user"
})
posts_with_tags = response.json()

-- Generated from: "Show me the most popular tags by post count"
SELECT
    t.name,
    t.description,
    COUNT(pt.post_id) as post_count
FROM tags t
LEFT JOIN post_tags pt ON t.id = pt.tag_id
GROUP BY t.id, t.name, t.description
ORDER BY post_count DESC
LIMIT 10;

-- Generated from: "Find users with the highest engagement"
SELECT
    u.username,
    u.email,
    COUNT(DISTINCT p.id) as post_count,
    COUNT(DISTINCT c.id) as comment_count,
    COUNT(DISTINCT p.id) + COUNT(DISTINCT c.id) as total_activity
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
LEFT JOIN comments c ON u.id = c.user_id
GROUP BY u.id, u.username, u.email
ORDER BY total_activity DESC
LIMIT 10;