PortfolioItem Model

from sqlalchemy import Column, Integer, String, Text, DateTime, Boolean, ForeignKey
from sqlalchemy.orm import relationship

class PortfolioItem(Base):
    __tablename__ = "portfolio_items"

CREATE TABLE portfolio_items (
    id INTEGER PRIMARY KEY,
    
    -- Relationships (all nullable)
    artist_id INTEGER,
    user_id INTEGER,
    client_id INTEGER,
    session_id INTEGER,
    transaction_id INTEGER,
    
    -- File paths
    path VARCHAR NOT NULL,
    thumb_path VARCHAR,
    
    -- Description
    caption TEXT,
    
    -- Categorization
    style VARCHAR,
    body_area VARCHAR,
    color_mode VARCHAR,
    fresh_or_healed VARCHAR,
    
    -- Display settings
    is_public BOOLEAN NOT NULL DEFAULT 1,
    is_cover BOOLEAN NOT NULL DEFAULT 0,
    
    -- Metadata
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (artist_id) REFERENCES artists(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (session_id) REFERENCES sessions(id),
    FOREIGN KEY (transaction_id) REFERENCES transactions(id)
);

from data.models.portfolio import PortfolioItem
from datetime import datetime

# Add photo after completing a session
portfolio_item = PortfolioItem(
    artist_id=1,
    user_id=2,
    client_id=5,
    session_id=10,
    path="/uploads/portfolio/2024/03/tattoo_001.jpg",
    thumb_path="/uploads/portfolio/2024/03/thumbs/tattoo_001.jpg",
    caption="Japanese dragon sleeve - first session",
    style="Japanese",
    body_area="sleeve",
    color_mode="color",
    fresh_or_healed="fresh",
    is_public=True,
    is_cover=False
)
db.add(portfolio_item)
db.commit()

# Add a general portfolio image not tied to a specific session
portfolio_item = PortfolioItem(
    artist_id=1,
    path="/uploads/portfolio/general/flash_design_01.jpg",
    thumb_path="/uploads/portfolio/general/thumbs/flash_design_01.jpg",
    caption="Available flash design",
    style="Traditional",
    color_mode="color",
    is_public=True
)
db.add(portfolio_item)
db.commit()

# Get all public portfolio items for an artist
artist_id = 1

portfolio = db.query(PortfolioItem).filter(
    PortfolioItem.artist_id == artist_id,
    PortfolioItem.is_public == True
).order_by(PortfolioItem.created_at.desc()).all()

for item in portfolio:
    print(f"{item.style or 'Uncategorized'} - {item.caption or 'No caption'}")

# Get featured/cover images
cover_images = db.query(PortfolioItem).filter(
    PortfolioItem.is_cover == True,
    PortfolioItem.is_public == True
).all()

# Get all Japanese style tattoos
japanese_work = db.query(PortfolioItem).filter(
    PortfolioItem.style == "Japanese",
    PortfolioItem.is_public == True
).all()

for item in japanese_work:
    if item.artist:
        print(f"By {item.artist.name}: {item.caption}")

# Get all sleeve tattoos
sleeves = db.query(PortfolioItem).filter(
    PortfolioItem.body_area == "sleeve",
    PortfolioItem.is_public == True
).order_by(PortfolioItem.created_at.desc()).all()

# Get all portfolio images for a specific client
client_id = 5

client_tattoos = db.query(PortfolioItem).filter(
    PortfolioItem.client_id == client_id
).order_by(PortfolioItem.created_at).all()

print(f"Client's tattoo history:")
for item in client_tattoos:
    print(f"  {item.created_at.strftime('%Y-%m-%d')} - {item.caption}")
    if item.session:
        print(f"    Session with {item.session.artist.name}")

# Make an image private
item = db.query(PortfolioItem).filter(PortfolioItem.id == 1).first()
item.is_public = False
db.commit()

# Make it public again
item.is_public = True
db.commit()

# Set a new cover image (unset previous covers for the artist)
artist_id = 1
new_cover_id = 10

# Remove existing cover flags for this artist
db.query(PortfolioItem).filter(
    PortfolioItem.artist_id == artist_id,
    PortfolioItem.is_cover == True
).update({"is_cover": False})

# Set new cover
item = db.query(PortfolioItem).filter(PortfolioItem.id == new_cover_id).first()
item.is_cover = True
db.commit()

# Search by caption
search_term = "dragon"

results = db.query(PortfolioItem).filter(
    PortfolioItem.caption.ilike(f"%{search_term}%"),
    PortfolioItem.is_public == True
).all()

def get_filtered_portfolio(artist_id=None, style=None, body_area=None, 
                           color_mode=None, fresh_or_healed=None):
    query = db.query(PortfolioItem).filter(
        PortfolioItem.is_public == True
    )
    
    if artist_id:
        query = query.filter(PortfolioItem.artist_id == artist_id)
    if style:
        query = query.filter(PortfolioItem.style == style)
    if body_area:
        query = query.filter(PortfolioItem.body_area == body_area)
    if color_mode:
        query = query.filter(PortfolioItem.color_mode == color_mode)
    if fresh_or_healed:
        query = query.filter(PortfolioItem.fresh_or_healed == fresh_or_healed)
    
    return query.order_by(PortfolioItem.created_at.desc()).all()