-- SQL is not case-sensitive for keywords, but convention is UPPERCASE
-- Single-line comment: --
-- Multi-line: /* ... */

-- Create a table
CREATE TABLE students (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    name    TEXT    NOT NULL,
    score   REAL    DEFAULT 0.0,
    grade   TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Insert rows
INSERT INTO students (name, score, grade) VALUES ('Alice', 92.5, 'A');
INSERT INTO students (name, score, grade) VALUES ('Bob',   78.0, 'C');
INSERT INTO students (name, score, grade) VALUES ('Charlie',85.5, 'B');

-- Sample tables
-- students: id, name, score, dept_id
-- departments: id, name, building

-- INNER JOIN — only rows that match in BOTH tables
SELECT s.name, s.score, d.name AS department
FROM   students     s
JOIN   departments  d ON s.dept_id = d.id;

-- LEFT JOIN — all students, even those with no department
SELECT s.name, s.score, d.name AS department
FROM   students     s
LEFT JOIN departments d ON s.dept_id = d.id;
-- d.name will be NULL for students with no matching dept_id

-- RIGHT JOIN (SQLite doesn't support — use LEFT JOIN with tables swapped)

-- Self-join — join a table to itself
-- Find all students who scored higher than Alice
SELECT b.name, b.score
FROM   students a
JOIN   students b ON b.score > a.score
WHERE  a.name = 'Alice';

pip install fastapi uvicorn[standard]

# Minimal FastAPI app — save as main.py
from fastapi import FastAPI

app = FastAPI(title="My AI API", version="1.0.0")

@app.get("/")
def root():
    return {"message": "AI API is running"}

@app.get("/health")
def health():
    return {"status": "ok"}

# Run the server
# uvicorn main:app --reload
# Open http://127.0.0.1:8000/docs  ← interactive Swagger UI
# Open http://127.0.0.1:8000/redoc ← alternative docs

from pydantic import BaseModel, Field, field_validator
from typing import Optional, List
from datetime import datetime

# Basic model — define schema with type annotations
class Student(BaseModel):
    name:  str
    score: float
    grade: Optional[str] = None

s = Student(name="Alice", score=92.5)
print(s.model_dump())          # {"name":"Alice","score":92.5,"grade":null}
print(s.model_dump_json())     # JSON string

# Validation — Pydantic raises ValidationError on wrong types
try:
    bad = Student(name="Bob", score="not-a-number")
except Exception as e:
    print(e)   # score: Input should be a valid number

# Field — add constraints and documentation
class LLMRequest(BaseModel):
    model:       str   = Field(default="claude-3-5-sonnet-20241022")
    prompt:      str   = Field(min_length=1, max_length=100000)
    max_tokens:  int   = Field(default=1024, ge=1, le=8192)
    temperature: float = Field(default=0.7, ge=0.0, le=2.0)
    tags: List[str]    = Field(default_factory=list)

# Custom validator
class RegistrationForm(BaseModel):
    username: str
    email:    str
    age:      int

    @field_validator('email')
    @classmethod
    def email_must_contain_at(cls, v: str) -> str:
        if '@' not in v:
            raise ValueError('must be a valid email address')
        return v.lower()

    @field_validator('age')
    @classmethod
    def age_must_be_adult(cls, v: int) -> int:
        if v < 18:
            raise ValueError('must be 18 or older')
        return v

from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from typing import Optional, List
import sqlite3
from contextlib import contextmanager

app = FastAPI(title="Student API")

# Database connection context manager
@contextmanager
def get_db():
    conn = sqlite3.connect("students.db")
    conn.row_factory = sqlite3.Row   # access cols by name: row["name"]
    try:
        yield conn
    finally:
        conn.close()

# Create table on startup
@app.on_event("startup")
def startup():
    with get_db() as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS students (
                id    INTEGER PRIMARY KEY AUTOINCREMENT,
                name  TEXT    NOT NULL,
                score REAL    NOT NULL,
                grade TEXT
            )
        """)
        conn.commit()

# Pydantic models
class StudentCreate(BaseModel):
    name:  str
    score: float
    grade: Optional[str] = None

class StudentResponse(BaseModel):
    id:    int
    name:  str
    score: float
    grade: Optional[str]

# POST /students — create
@app.post("/students", response_model=StudentResponse, status_code=201)
def create_student(student: StudentCreate):
    with get_db() as conn:
        cursor = conn.execute(
            "INSERT INTO students (name, score, grade) VALUES (?, ?, ?)",
            (student.name, student.score, student.grade)
        )
        conn.commit()
        row = conn.execute(
            "SELECT * FROM students WHERE id = ?", (cursor.lastrowid,)
        ).fetchone()
    return dict(row)

# GET /students — list all
@app.get("/students", response_model=List[StudentResponse])
def list_students(min_score: float = 0.0):
    with get_db() as conn:
        rows = conn.execute(
            "SELECT * FROM students WHERE score >= ? ORDER BY score DESC",
            (min_score,)
        ).fetchall()
    return [dict(row) for row in rows]

# GET /students/{id} — get one
@app.get("/students/{student_id}", response_model=StudentResponse)
def get_student(student_id: int):
    with get_db() as conn:
        row = conn.execute(
            "SELECT * FROM students WHERE id = ?", (student_id,)
        ).fetchone()
    if not row:
        raise HTTPException(status_code=404, detail="Student not found")
    return dict(row)

# DELETE /students/{id}
@app.delete("/students/{student_id}", status_code=204)
def delete_student(student_id: int):
    with get_db() as conn:
        result = conn.execute(
            "DELETE FROM students WHERE id = ?", (student_id,)
        )
        conn.commit()
    if result.rowcount == 0:
        raise HTTPException(status_code=404, detail="Student not found")