Merge pull request 'release/1.3' (#116) from release/1.3 into master

Reviewed-on: #116

.env.sample

@@ -1,3 +1,23 @@
+# ================================================================================================
+# BrickTracker Configuration File
+# ================================================================================================
+#
+# FILE LOCATION (v1.3+):
+# ----------------------
+# This file can be placed in two locations:
+#   1. data/.env (RECOMMENDED) - Included in data volume backup, settings persist via admin panel
+#   2. .env (root) - Backward compatible
+#
+# Priority: data/.env > .env (root)
+#
+# The application automatically detects and uses the correct location at runtime.
+#
+# For Docker:
+#   - Recommended: Place this file as data/.env (included in data volume)
+#   - Backward compatible: Keep as .env in root (add "env_file: .env" to compose.yaml)
+#
+# ================================================================================================
+#
 # Note on *_DEFAULT_ORDER
 # If set, it will append a direct ORDER BY <whatever you set> to the SQL query
 # while listing objects. You can look at the structure of the SQLite database to
@@ -41,11 +61,11 @@
 # Default: false
 # BK_BRICKLINK_LINKS=true
 
-# Optional: Path to the database.
+# Optional: Path to the database, relative to '/app/' folder
 # Useful if you need it mounted in a Docker volume. Keep in mind that it will not
 # do any check on the existence of the path, or if it is dangerous.
-# Default: ./app.db
-# BK_DATABASE_PATH=/var/lib/bricktracker/app.db
+# Default: data/app.db
+# BK_DATABASE_PATH=data/app.db
 
 # Optional: Format of the timestamp added to the database file when downloading it
 # Check https://docs.python.org/3/library/time.html#time.strftime for format details
@@ -61,10 +81,6 @@
 # Default: 25
 # BK_DEFAULT_TABLE_PER_PAGE=50
 
-# Optional: Maximum length for description text in badges before truncating with ellipsis
-# Default: 15
-# BK_DESCRIPTION_BADGE_MAX_LENGTH=15
-
 # Optional: if set up, will add a CORS allow origin restriction to the socket.
 # Default:
 # Legacy name: DOMAIN_NAME
@@ -90,9 +106,9 @@
 # Default: .pdf
 # BK_INSTRUCTIONS_ALLOWED_EXTENSIONS=.pdf, .docx, .png
 
-# Optional: Folder where to store the instructions, relative to the '/app/static/' folder
-# Default: instructions
-# BK_INSTRUCTIONS_FOLDER=/var/lib/bricktracker/instructions/
+# Optional: Folder where to store the instructions, relative to '/app/' folder
+# Default: data/instructions
+# BK_INSTRUCTIONS_FOLDER=data/instructions
 
 # Optional: Hide the 'Add' entry from the menu. Does not disable the route.
 # Default: false
@@ -122,20 +138,6 @@
 # Default: false
 # BK_HIDE_ALL_MINIFIGURES=true
 
-# Optional: Disable the individual/loose minifigures system. This hides all individual
-# minifigure UI elements and prevents adding new individual minifigures. The routes remain
-# accessible so existing individual minifigures can still be viewed. Users who only track
-# set-based minifigures can use this to simplify the interface. Does not disable the route.
-# Default: false
-# BK_DISABLE_INDIVIDUAL_MINIFIGURES=false
-
-# Optional: Disable the individual/loose parts system. This hides all individual part UI
-# elements and prevents adding new individual parts (parts not associated with any set).
-# The routes remain accessible so existing individual parts can still be viewed. Users who
-# only track set-based parts can use this to simplify the interface. Does not disable the route.
-# Default: false
-# BK_DISABLE_INDIVIDUAL_PARTS=false
-
 # Optional: Hide the 'Parts' entry from the menu. Does not disable the route.
 # Default: false
 # BK_HIDE_ALL_PARTS=true
@@ -178,19 +180,24 @@
 # BK_HIDE_WISHES=true
 
 # Optional: Change the default order of minifigures. By default ordered by insertion order.
-# Note: Minifigures are queried from a combined view that merges both set-based and individual minifigures.
-# Therefore, column references should use the "combined" table alias.
 # Useful column names for this option are:
-# - "combined"."figure": minifigure ID (fig-xxxxx)
-# - "combined"."number": minifigure ID as an integer (xxxxx)
-# - "combined"."name": minifigure name
-# - "combined"."rowid": insertion order (for both set and individual minifigures)
-# Default: "combined"."name" ASC
-# BK_MINIFIGURES_DEFAULT_ORDER="combined"."name" ASC
+# - "rebrickable_minifigures"."figure": minifigure ID (e.g., "fig-001234")
+# - "rebrickable_minifigures"."number": minifigure ID as an integer (e.g., 1234)
+# - "rebrickable_minifigures"."name": minifigure name
+# - "rebrickable_minifigures"."number_of_parts": number of parts in the minifigure
+# - "bricktracker_minifigures"."quantity": quantity owned
+# - "total_missing": number of missing parts (composite field)
+# - "total_damaged": number of damaged parts (composite field)
+# - "total_quantity": total quantity across all sets (composite field)
+# - "total_sets": number of sets containing this minifigure (composite field)
+# Default: "rebrickable_minifigures"."name" ASC
+# Examples:
+# BK_MINIFIGURES_DEFAULT_ORDER="rebrickable_minifigures"."number" DESC
+# BK_MINIFIGURES_DEFAULT_ORDER="total_missing" DESC, "rebrickable_minifigures"."name" ASC
 
-# Optional: Folder where to store the minifigures images, relative to the '/app/static/' folder
-# Default: minifigs
-# BK_MINIFIGURES_FOLDER=minifigures
+# Optional: Folder where to store the minifigures images, relative to '/app/' folder
+# Default: data/minifigures
+# BK_MINIFIGURES_FOLDER=data/minifigures
 
 # Optional: Disable threading on the task executed by the socket.
 # You should not need to change this parameter unless you are debugging something with the
@@ -199,20 +206,27 @@
 # BK_NO_THREADED_SOCKET=true
 
 # Optional: Change the default order of parts. By default ordered by insertion order.
-# Note: Parts are queried from a combined view that merges both set-based and individual minifigure parts.
-# Some columns use the "combined" table alias for fields from the merged view.
 # Useful column names for this option are:
-# - "combined"."part": part number
-# - "combined"."spare": part is a spare part (use "combined" not "bricktracker_parts")
+# - "bricktracker_parts"."part": part number (e.g., "3001")
+# - "bricktracker_parts"."spare": part is a spare part (0 or 1)
+# - "bricktracker_parts"."quantity": quantity of this part
+# - "bricktracker_parts"."missing": number of missing parts
+# - "bricktracker_parts"."damaged": number of damaged parts
 # - "rebrickable_parts"."name": part name
 # - "rebrickable_parts"."color_name": part color name
-# - "total_missing": number of missing parts
-# Default: "rebrickable_parts"."name" ASC, "rebrickable_parts"."color_name" ASC, "combined"."spare" ASC
+# - "total_missing": total missing across all sets (composite field)
+# - "total_damaged": total damaged across all sets (composite field)
+# - "total_quantity": total quantity across all sets (composite field)
+# - "total_sets": number of sets containing this part (composite field)
+# - "total_minifigures": number of minifigures with this part (composite field)
+# Default: "rebrickable_parts"."name" ASC, "rebrickable_parts"."color_name" ASC, "bricktracker_parts"."spare" ASC
+# Examples:
 # BK_PARTS_DEFAULT_ORDER="total_missing" DESC, "rebrickable_parts"."name" ASC
+# BK_PARTS_DEFAULT_ORDER="rebrickable_parts"."color_name" ASC, "rebrickable_parts"."name" ASC
 
-# Optional: Folder where to store the parts images, relative to the '/app/static/' folder
-# Default: parts
-# BK_PARTS_FOLDER=parts
+# Optional: Folder where to store the parts images, relative to '/app/' folder
+# Default: data/parts
+# BK_PARTS_FOLDER=data/parts
 
 # Optional: Enable server-side pagination for individual pages (recommended for large collections)
 # When enabled, pages use server-side pagination with configurable page sizes
@@ -270,9 +284,12 @@
 
 # Optional: Change the default order of purchase locations. By default ordered by insertion order.
 # Useful column names for this option are:
-# - "bricktracker_metadata_purchase_locations"."name" ASC: storage name
+# - "bricktracker_metadata_purchase_locations"."name": purchase location name
+# - "bricktracker_metadata_purchase_locations"."rowid": insertion order (special column)
 # Default: "bricktracker_metadata_purchase_locations"."name" ASC
-# BK_PURCHASE_LOCATION_DEFAULT_ORDER="bricktracker_metadata_purchase_locations"."name" ASC
+# Examples:
+# BK_PURCHASE_LOCATION_DEFAULT_ORDER="bricktracker_metadata_purchase_locations"."name" DESC
+# BK_PURCHASE_LOCATION_DEFAULT_ORDER="bricktracker_metadata_purchase_locations"."rowid" DESC
 
 # Optional: Shuffle the lists on the front page.
 # Default: false
@@ -288,23 +305,23 @@
 
 # Optional: URL of the image representing a missing image in Rebrickable
 # Default: https://rebrickable.com/static/img/nil.png
-# BK_REBRICKABLE_IMAGE_NIL=
+# BK_REBRICKABLE_IMAGE_NIL=https://rebrickable.com/static/img/nil.png
 
 # Optional: URL of the image representing a missing minifigure image in Rebrickable
 # Default: https://rebrickable.com/static/img/nil_mf.jpg
-# BK_REBRICKABLE_IMAGE_NIL_MINIFIGURE=
+# BK_REBRICKABLE_IMAGE_NIL_MINIFIGURE=https://rebrickable.com/static/img/nil_mf.jpg
 
 # Optional: Pattern of the link to Rebrickable for a minifigure. Will be passed to Python .format()
 # Default: https://rebrickable.com/minifigs/{figure}
-# BK_REBRICKABLE_LINK_MINIFIGURE_PATTERN=
+# BK_REBRICKABLE_LINK_MINIFIGURE_PATTERN=https://rebrickable.com/minifigs/{figure}
 
 # Optional: Pattern of the link to Rebrickable for a part. Will be passed to Python .format()
 # Default: https://rebrickable.com/parts/{part}/_/{color}
-# BK_REBRICKABLE_LINK_PART_PATTERN=
+# BK_REBRICKABLE_LINK_PART_PATTERN=https://rebrickable.com/parts/{part}/_/{color}
 
 # Optional: Pattern of the link to Rebrickable for instructions. Will be passed to Python .format()
 # Default: https://rebrickable.com/instructions/{path}
-# BK_REBRICKABLE_LINK_INSTRUCTIONS_PATTERN=
+# BK_REBRICKABLE_LINK_INSTRUCTIONS_PATTERN=https://rebrickable.com/instructions/{path}
 
 # Optional: User-Agent to use when querying Rebrickable and Peeron outside of the Rebrick python library
 # Default: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36
@@ -350,27 +367,33 @@
 # Default: https://docs.google.com/spreadsheets/d/1rlYfEXtNKxUOZt2Mfv0H17DvK7bj6Pe0CuYwq6ay8WA/gviz/tq?tqx=out:csv&sheet=Sorted%20by%20Retirement%20Date
 # BK_RETIRED_SETS_FILE_URL=
 
-# Optional: Path to the unofficial retired sets lists
+# Optional: Path to the unofficial retired sets lists, relative to '/app/' folder
 # You can name it whatever you want, but content has to be a CSV
-# Default: ./retired_sets.csv
-# BK_RETIRED_SETS_PATH=/var/lib/bricktracker/retired_sets.csv
+# Default: data/retired_sets.csv
+# BK_RETIRED_SETS_PATH=data/retired_sets.csv
 
 # Optional: Change the default order of sets. By default ordered by insertion order.
 # Useful column names for this option are:
-# - "rebrickable_sets"."set": set number as a string
-# - "rebrickable_sets"."number": the number part of set as an integer
-# - "rebrickable_sets"."version": the version part of set as an integer
+# - "rebrickable_sets"."set": set number as a string (e.g., "10255-1")
+# - "rebrickable_sets"."number": the number part of set as text (e.g., "10255")
+# - "rebrickable_sets"."version": the version part of set as an integer (e.g., 1)
 # - "rebrickable_sets"."name": set name
 # - "rebrickable_sets"."year": set release year
 # - "rebrickable_sets"."number_of_parts": set number of parts
-# - "total_missing": number of missing parts
-# - "total_minifigures": number of minifigures
+# - "bricktracker_sets"."purchase_date": purchase date (as REAL/Julian day)
+# - "bricktracker_sets"."purchase_price": purchase price
+# - "total_missing": number of missing parts (composite field)
+# - "total_damaged": number of damaged parts (composite field)
+# - "total_minifigures": number of minifigures (composite field)
 # Default: "rebrickable_sets"."number" DESC, "rebrickable_sets"."version" ASC
-# BK_SETS_DEFAULT_ORDER="rebrickable_sets"."year" ASC
+# Examples:
+# BK_SETS_DEFAULT_ORDER="rebrickable_sets"."year" DESC, "rebrickable_sets"."name" ASC
+# BK_SETS_DEFAULT_ORDER="rebrickable_sets"."number_of_parts" DESC
+# BK_SETS_DEFAULT_ORDER="total_missing" DESC, "rebrickable_sets"."year" ASC
 
-# Optional: Folder where to store the sets images, relative to the '/app/static/' folder
-# Default: sets
-# BK_SETS_FOLDER=sets
+# Optional: Folder where to store the sets images, relative to '/app/' folder
+# Default: data/sets
+# BK_SETS_FOLDER=data/sets
 
 # Optional: Enable set consolidation/grouping on the main sets page
 # When enabled, multiple copies of the same set are grouped together showing instance count
@@ -390,10 +413,14 @@
 # Default: true
 # BK_SHOW_SETS_DUPLICATE_FILTER=true
 
-# Optional: Skip saving or displaying spare parts
+# Optional: Skip importing spare parts when downloading sets from Rebrickable
 # Default: false
 # BK_SKIP_SPARE_PARTS=true
 
+# Optional: Hide spare parts from parts lists (spare parts must still be in database)
+# Default: false
+# BK_HIDE_SPARE_PARTS=true
+
 # Optional: Namespace of the Socket.IO socket
 # Default: bricksocket
 # BK_SOCKET_NAMESPACE=customsocket
@@ -404,18 +431,21 @@
 
 # Optional: Change the default order of storages. By default ordered by insertion order.
 # Useful column names for this option are:
-# - "bricktracker_metadata_storages"."name" ASC: storage name
+# - "bricktracker_metadata_storages"."name": storage name
+# - "bricktracker_metadata_storages"."rowid": insertion order (special column)
 # Default: "bricktracker_metadata_storages"."name" ASC
-# BK_STORAGE_DEFAULT_ORDER="bricktracker_metadata_storages"."name" ASC
+# Examples:
+# BK_STORAGE_DEFAULT_ORDER="bricktracker_metadata_storages"."name" DESC
+# BK_STORAGE_DEFAULT_ORDER="bricktracker_metadata_storages"."rowid" DESC
 
 # Optional: URL to the themes.csv.gz on Rebrickable
 # Default: https://cdn.rebrickable.com/media/downloads/themes.csv.gz
 # BK_THEMES_FILE_URL=
 
-# Optional: Path to the themes file
+# Optional: Path to the themes file, relative to '/app/' folder
 # You can name it whatever you want, but content has to be a CSV
-# Default: ./themes.csv
-# BK_THEMES_PATH=/var/lib/bricktracker/themes.csv
+# Default: data/themes.csv
+# BK_THEMES_PATH=data/themes.csv
 
 # Optional: Timezone to use to display datetimes
 # Check your system for available timezone/TZ values
@@ -427,14 +457,19 @@
 # Default: false
 # BK_USE_REMOTE_IMAGES=true
 
-# Optional: Change the default order of sets. By default ordered by insertion order.
+# Optional: Change the default order of wishlist sets. By default ordered by insertion order.
 # Useful column names for this option are:
-# - "bricktracker_wishes"."set": set number as a string
+# - "bricktracker_wishes"."set": set number as a string (e.g., "10255-1")
 # - "bricktracker_wishes"."name": set name
 # - "bricktracker_wishes"."year": set release year
 # - "bricktracker_wishes"."number_of_parts": set number of parts
+# - "bricktracker_wishes"."theme_id": theme ID
+# - "bricktracker_wishes"."rowid": insertion order (special column)
 # Default: "bricktracker_wishes"."rowid" DESC
-# BK_WISHES_DEFAULT_ORDER="bricktracker_wishes"."set" DESC
+# Examples:
+# BK_WISHES_DEFAULT_ORDER="bricktracker_wishes"."year" DESC, "bricktracker_wishes"."name" ASC
+# BK_WISHES_DEFAULT_ORDER="bricktracker_wishes"."number_of_parts" DESC
+# BK_WISHES_DEFAULT_ORDER="bricktracker_wishes"."set" ASC
 
 # Optional: Show collection growth charts on the statistics page
 # Default: true
@@ -444,3 +479,8 @@
 # When true, all sections start expanded. When false, all sections start collapsed.
 # Default: true
 # BK_STATISTICS_DEFAULT_EXPANDED=false
+
+# Optional: Enable dark mode by default
+# When true, the application starts in dark mode. 
+# Default: false
+# BK_DARK_MODE=true

.gitignore

@@ -17,6 +17,7 @@ static/sets/
 
 # IDE
 .vscode/
+*.code-workspace
 
 # Temporary
 *.csv
@@ -33,7 +34,4 @@ vitepress/
 
 # Local data
 offline/
-TODO.md
-run-local.sh
-test-server.sh
 data/

CHANGELOG.md

@@ -1,17 +1,160 @@
 # Changelog
 
-## Unreleased
+## 1.3
 
-### 1.3
+### Breaking Changes 
 
-- Add individual pagination control system per entity type
-  - `BK_SETS_SERVER_SIDE_PAGINATION`: Enable/disable pagination for sets
-  - `BK_PARTS_SERVER_SIDE_PAGINATION`: Enable/disable pagination for parts
-  - `BK_MINIFIGURES_SERVER_SIDE_PAGINATION`: Enable/disable pagination for minifigures
-  - Device-specific pagination sizes (desktop/mobile) for each entity type
-  - Supports search, filtering, and sorting in both server-side and client-side modes
-  - Consolidated duplicate code across parts.js, problems.js, and minifigures.js
-  - Created shared functions in collapsible-state.js for common operations
+#### Data Folder Consolidation
+
+> **Warning** 
+> **BREAKING CHANGE**: Version 1.3 consolidates all user data into a single `data/` folder for easier backup and volume mapping.
+
+- **Path handling**: All relative paths are now resolved relative to the application root (`/app` in Docker)
+  - Example: `data/app.db` → `/app/data/app.db`
+
+- **New default paths** (automatically used for new installations):
+  - Database: `data/app.db` (was: `app.db` in root)
+  - Configuration: `data/.env` (was: `.env` in root) - *optional, backward compatible*
+  - CSV files: `data/*.csv` (was: `*.csv` in root)
+  - Images/PDFs: `data/{sets,parts,minifigures,instructions}/` (was: `static/*`)
+
+- **Configuration file (.env) location**:
+  - New recommended location: `data/.env` (included in data volume, settings persist)
+  - Backward compatible: `.env` in root still works (requires volume mount for admin panel persistence)
+  - Priority: `data/.env` > `.env` (automatic detection, no migration required)
+
+- **Migration options**:
+  1. **Migrate to new structure** (recommended - single volume for all data including .env)
+  2. **Keep current setup** (backward compatible - old paths continue to work)
+
+See [Migration Guide](docs/migration_guide.md) for detailed instructions
+
+#### Default Minifigures Folder Change
+
+> **Warning**
+> **BREAKING CHANGE**: Default minifigures folder path changed from `minifigs` to `minifigures`
+
+- **Impact**: Users who relied on the default `BK_MINIFIGURES_FOLDER` value (without explicitly setting it) will need to either:
+  1. Set `BK_MINIFIGURES_FOLDER=minifigs` in their environment to maintain existing behavior, or
+  2. Rename their existing `minifigs` folder to `minifigures`
+- **No impact**: Users who already have `BK_MINIFIGURES_FOLDER` explicitly configured
+- Improved consistency across documentation and Docker configurations
+
+### New Features
+
+- **Live Settings changes**
+  - Added live environment variable configuration management system
+    - Configuration Management interface in admin panel with live preview and badge system
+    - **Live settings**: Can be changed without application restart (menu visibility, table display, pagination, features)
+    - **Static settings**: Require restart but can be edited and saved to .env file (authentication, server, database, API keys)
+    - Advanced badge system showing value status: True/False for booleans, Set/Default/Unset for other values, Changed indicator
+    - Live API endpoints: `/admin/api/config/update` for immediate changes, `/admin/api/config/update-static` for .env updates
+    - Form pre-population with current values and automatic page reload after successful live updates
+  - Fixed environment variable lock detection in admin configuration panel
+    - Resolved bug where all variables appeared "locked" after saving live settings
+    - Lock detection now correctly identifies only Docker environment variables set before .env loading
+    - Variables set via Docker's `environment:` directive remain properly locked
+    - Variables from data/.env or root .env are correctly shown as editable
+  - Added configuration persistence warning in admin panel
+    - Warning banner shows when using .env in root (non-persistent)
+    - Success banner shows when using data/.env (persistent)
+    - Provides migration instructions directly in the UI
+- **Spare Parts**
+  - Added spare parts control options
+    - `BK_SKIP_SPARE_PARTS`: Skip importing spare parts when downloading sets from Rebrickable (parts not saved to database)
+    - `BK_HIDE_SPARE_PARTS`: Hide spare parts from all parts lists (parts must still be in database)
+    - Both options are live-changeable in admin configuration panel
+    - Options can be used independently or together for flexible spare parts management
+    - Affects all parts displays: /parts page, set details accordion, minifigure parts, and problem parts
+- **Pagination**
+  - Added individual pagination control system per entity type
+    - `BK_SETS_SERVER_SIDE_PAGINATION`: Enable/disable pagination for sets
+    - `BK_PARTS_SERVER_SIDE_PAGINATION`: Enable/disable pagination for parts
+    - `BK_MINIFIGURES_SERVER_SIDE_PAGINATION`: Enable/disable pagination for minifigures
+    - Device-specific pagination sizes (desktop/mobile) for each entity type
+    - Supports search, filtering, and sorting in both server-side and client-side modes
+- **Peeron Instructions**
+  - Added Peeron instructions integration
+    - Full image caching system with automatic thumbnail generation
+    - Optimized HTTP calls by downloading full images once and generating thumbnails locally
+    - Automatic cache cleanup after PDF generation to save disk space
+- **Parts checkmark**
+  - Added parts checking/inventory system
+    - New "Checked" column in parts tables for tracking inventory progress
+    - Checkboxes to mark parts as verified during set walkthrough
+    - `BK_HIDE_TABLE_CHECKED_PARTS`: Environment variable to hide checked column
+- **Set Consolidation**
+  - Added set consolidation/grouping functionality
+    - Automatic grouping of duplicate sets on main sets page
+    - Shows instance count with stack icon badge (e.g., "3 copies")
+    - Expandable drawer interface to view all set copies individually
+    - Full set cards for each instance with all badges, statuses, and functionality
+    - `BK_SETS_CONSOLIDATION`: Environment variable to enable/disable consolidation (default: false)
+    - Backwards compatible - when disabled, behaves exactly like original individual view
+    - Improved theme filtering: handles duplicate theme names correctly
+    - Fixed set number sorting: proper numeric sorting in both ascending and descending order
+    - Mixed status indicators for consolidated sets: three-state checkboxes (unchecked/partial/checked) with count badges
+      - Template logic handles three states: none (0/2), all (2/2), partial (1/2) with visual indicators
+      - Purple overlay styling for partial states, disabled checkboxes for read-only consolidated status display
+      - Individual sets maintain full interactive checkbox functionality
+- **Statistics**
+  - Added comprehensive statistics system (#91)
+    - New Statistics page with collection analytics
+    - Financial overview: total cost, average price, price range, investment tracking
+    - Collection metrics: total sets, unique sets, parts count, minifigures count
+    - Theme distribution statistics with clickable drill-down to filtered sets
+    - Storage location statistics showing sets per location with value calculations
+    - Purchase location analytics with spending patterns and date ranges
+    - Problem tracking: missing and damaged parts statistics
+    - Clickable numbers throughout statistics that filter to relevant sets
+    - `BK_HIDE_STATISTICS`: Environment variable to hide statistics menu item
+    - Year-based analytics: Sets by release year and purchases by year
+      - Sets by Release Year: Shows collection distribution across LEGO release years
+      - Purchases by Year: Tracks spending patterns and acquisition timeline
+      - Year summary with peak collection/spending years and timeline insights
+    - Enhanced statistics interface and functionality
+      - Collapsible sections: All statistics sections have clickable headers to expand/collapse
+      - Collection growth charts: Line charts showing sets, parts, and minifigures over time
+      - Configuration options: `BK_STATISTICS_SHOW_CHARTS` and `BK_STATISTICS_DEFAULT_EXPANDED` environment variables
+- **Admin Page Section Expansion**
+  - Added configurable admin page section expansion
+    - `BK_ADMIN_DEFAULT_EXPANDED_SECTIONS`: Environment variable to specify which sections expand by default
+    - Accepts comma-separated list of section names (e.g., "database,theme,instructions")
+    - Valid sections: authentication, instructions, image, theme, retired, metadata, owner, purchase_location, status, storage, tag, database
+    - URL parameters take priority over configuration (e.g., `?open_database=1`)
+    - Database section expanded by default to maintain original behavior
+    - Smart metadata handling: sub-section expansion automatically expands parent metadata section
+- **Duplicate Sets filter**
+  - Added duplicate sets filter functionality
+    - New filter button on Sets page to show only duplicate/consolidated sets
+    - `BK_SHOW_SETS_DUPLICATE_FILTER`: Environment variable to show/hide the filter button (default: true)
+    - Works with both server-side and client-side pagination modes
+    - Consolidated mode: Shows sets that have multiple instances
+    - Non-consolidated mode: Shows sets that appear multiple times in collection
+- **Bricklink Links**
+  - Added BrickLink links for sets
+    - BrickLink badge links now appear on set cards and set details pages alongside Rebrickable links
+    - `BK_BRICKLINK_LINK_SET_PATTERN`: New environment variable for BrickLink set URL pattern (default: https://www.bricklink.com/v2/catalog/catalogitem.page?S={set_num})
+    - Controlled by existing `BK_BRICKLINK_LINKS` environment variable
+- **Dark Mode** 
+  - Added dark mode support
+    - `BK_DARK_MODE`: Environment variable to enable dark mode theme (default: false)
+    - Uses Bootstrap 5.3's native dark mode with `data-bs-theme` attribute
+    - Live-changeable via Admin > Live Settings
+    - Setting persists across sessions via .env file
+- **Alphanumetic Set Number**
+  - Added alphanumeric set number support
+    - Database schema change: Set number column changed from INTEGER to TEXT
+    - Supports LEGO promotional and special edition sets with letters in their numbers
+    - Examples: "McDR6US-1", "COMCON035-1", "EG00021-1"
+    
+### Improvements 
+
+- Improved WebSocket/Socket.IO reliability for mobile devices
+  - Changed connection strategy to polling-first with automatic WebSocket upgrade
+  - Increased connection timeout to 30 seconds for slow mobile networks
+  - Added ping/pong keepalive settings (30s timeout, 25s interval)
+  - Improved server-side connection logging with user agent and transport details
 - Fixed dynamic sort icons across all pages
   - Sort icons now properly toggle between ascending/descending states
 - Improved DataTable integration
@@ -23,76 +166,7 @@
   - Preserves selection state during dropdown consolidation
   - Consistent search behavior (instant for client-side, Enter key for server-side)
   - Mobile-friendly pagination navigation
-- Add Peeron instructions integration
-  - Full image caching system with automatic thumbnail generation
-  - Optimized HTTP calls by downloading full images once and generating thumbnails locally
-  - Automatic cache cleanup after PDF generation to save disk space
-- Add parts checking/inventory system
-  - New "Checked" column in parts tables for tracking inventory progress
-  - Checkboxes to mark parts as verified during set walkthrough
-  - `BK_HIDE_TABLE_CHECKED_PARTS`: Environment variable to hide checked column
-- Add set consolidation/grouping functionality
-  - Automatic grouping of duplicate sets on main sets page
-  - Shows instance count with stack icon badge (e.g., "3 copies")
-  - Expandable drawer interface to view all set copies individually
-  - Full set cards for each instance with all badges, statuses, and functionality
-  - `BK_SETS_CONSOLIDATION`: Environment variable to enable/disable consolidation (default: false)
-  - Backwards compatible - when disabled, behaves exactly like original individual view
-  - Improved theme filtering: handles duplicate theme names correctly
-  - Fixed set number sorting: proper numeric sorting in both ascending and descending order
-  - Mixed status indicators for consolidated sets: three-state checkboxes (unchecked/partial/checked) with count badges
-    - Template logic handles three states: none (0/2), all (2/2), partial (1/2) with visual indicators
-    - Purple overlay styling for partial states, disabled checkboxes for read-only consolidated status display
-    - Individual sets maintain full interactive checkbox functionality
-- Add comprehensive statistics system (#91)
-  - New Statistics page with collection analytics
-  - Financial overview: total cost, average price, price range, investment tracking
-  - Collection metrics: total sets, unique sets, parts count, minifigures count
-  - Theme distribution statistics with clickable drill-down to filtered sets
-  - Storage location statistics showing sets per location with value calculations
-  - Purchase location analytics with spending patterns and date ranges
-  - Problem tracking: missing and damaged parts statistics
-  - Clickable numbers throughout statistics that filter to relevant sets
-  - `BK_HIDE_STATISTICS`: Environment variable to hide statistics menu item
-  - Year-based analytics: Sets by release year and purchases by year
-    - Sets by Release Year: Shows collection distribution across LEGO release years
-    - Purchases by Year: Tracks spending patterns and acquisition timeline
-    - Year summary with peak collection/spending years and timeline insights
-  - Enhanced statistics interface and functionality
-    - Collapsible sections: All statistics sections have clickable headers to expand/collapse
-    - Collection growth charts: Line charts showing sets, parts, and minifigures over time
-    - Configuration options: `BK_STATISTICS_SHOW_CHARTS` and `BK_STATISTICS_DEFAULT_EXPANDED` environment variables
-- Add configurable admin page section expansion
-  - `BK_ADMIN_DEFAULT_EXPANDED_SECTIONS`: Environment variable to specify which sections expand by default
-  - Accepts comma-separated list of section names (e.g., "database,theme,instructions")
-  - Valid sections: authentication, instructions, image, theme, retired, metadata, owner, purchase_location, status, storage, tag, database
-  - URL parameters take priority over configuration (e.g., `?open_database=1`)
-  - Database section expanded by default to maintain original behavior
-  - Smart metadata handling: sub-section expansion automatically expands parent metadata section
-- Add duplicate sets filter functionality
-  - New filter button on Sets page to show only duplicate/consolidated sets
-  - `BK_SHOW_SETS_DUPLICATE_FILTER`: Environment variable to show/hide the filter button (default: true)
-  - Works with both server-side and client-side pagination modes
-  - Consolidated mode: Shows sets that have multiple instances
-  - Non-consolidated mode: Shows sets that appear multiple times in collection
-- Add BrickLink links for sets
-  - BrickLink badge links now appear on set cards and set details pages alongside Rebrickable links
-  - `BK_BRICKLINK_LINK_SET_PATTERN`: New environment variable for BrickLink set URL pattern (default: https://www.bricklink.com/v2/catalog/catalogitem.page?S={set_num})
-  - Controlled by existing `BK_BRICKLINK_LINKS` environment variable
-- Add live environment variable configuration management system
-  - Configuration Management interface in admin panel with live preview and badge system
-  - Live settings: Can be changed without application restart (menu visibility, table display, pagination, features)
-  - Static settings: Require restart but can be edited and saved to .env file (authentication, server, database, API keys)
-  - Advanced badge system showing value status: True/False for booleans, Set/Default/Unset for other values, Changed indicator
-  - Live API endpoints: `/admin/api/config/update` for immediate changes, `/admin/api/config/update-static` for .env updates
-  - Form pre-population with current values and automatic page reload after successful live updates
-- **BREAKING CHANGE**: Default minifigures folder path changed from `minifigs` to `minifigures`
-  - Impact: Users who relied on the default `BK_MINIFIGURES_FOLDER` value (without explicitly setting it) will need to either:
-    1. Set `BK_MINIFIGURES_FOLDER=minifigs` in their environment to maintain existing behavior, or
-    2. Rename their existing `minifigs` folder to `minifigures`
-  - No impact: Users who already have `BK_MINIFIGURES_FOLDER` explicitly configured
-  - Improved consistency across documentation and Docker configurations
-- Add performance optimization
+- Added performance optimization
   - SQLite WAL Mode:
     - Increased cache size to 10,000 pages (~40MB) for faster query execution
     - Set temp_store to memory for accelerated temporary operations
@@ -108,8 +182,29 @@
   - Statistics Query Optimization:
     - Replaced separate subqueries with efficient CTEs (Common Table Expressions)
     - Consolidated aggregations for set, part, minifigure, and financial statistics
+  - Added default image handling for sets without images
+    - Sets with null/missing images from Rebrickable API now display placeholder image
+    - Automatic fallback to nil.png from parts folder for set previews
+    - Copy of nil placeholder saved as set image for consistent display across all routes
+    - Prevents errors when downloading sets that have no set_img_url in API response
+  - Fixed instructions download from Rebrickable
+    - Replaced cloudscraper with standard requests library
+    - Resolves 403 Forbidden errors when downloading instruction PDFs
+  - Fixed instructions display and URL generation
+    - Fixed "Open PDF" button links to use correct data route
+    - Corrected path resolution for data/instructions folder
+    - Fixed instruction listing page to scan correct folder location
+    - Fixed Peeron PDF creation to use correct data folder path
+  - Fixed foreign key constraint error when adding sets
+    - Rebrickable set is now inserted before BrickTracker set to satisfy FK constraints
+    - Resolves "FOREIGN KEY constraint failed" error when adding sets
+  - Fixed atomic transaction handling for set downloads
+    - All database operations during set addition now use deferred execution
+    - Ensures all-or-nothing behavior: if any part fails (set info, parts, minifigs), nothing is committed
+    - Prevents partial set additions that would leave the database in an inconsistent state
+    - Metadata updates (owners, tags) now defer until final commit
     
-### 1.2.4
+## 1.2.4
 
 > **Warning**
 > To use the new BrickLink color parameter in URLs, update your `.env` file:

Dockerfile

@@ -2,13 +2,16 @@ FROM python:3-slim
 
 WORKDIR /app
 
+# Copy requirements first (so pip install can be cached)
+COPY requirements.txt .
+
+# Python library requirements
+RUN pip install --no-cache-dir -r requirements.txt
+
 # Bricktracker
 COPY . .
 
-# Fix line endings and set executable permissions for entrypoint script
-RUN sed -i 's/\r$//' entrypoint.sh && chmod +x entrypoint.sh
-
-# Python library requirements
-RUN pip --no-cache-dir install -r requirements.txt
+# Set executable permissions for entrypoint script
+RUN chmod +x entrypoint.sh
 
 ENTRYPOINT ["./entrypoint.sh"]

README.md

@@ -1,3 +1,5 @@
+<img src="static/brick.png" height="100" width="100">
+
 # BrickTracker
 
 A web application for organizing and tracking LEGO sets, parts, and minifigures. Uses the Rebrickable API to fetch LEGO data and allows users to track missing pieces and collection status.
@@ -18,17 +20,13 @@ A web application for organizing and tracking LEGO sets, parts, and minifigures.
 
 Use the provided [compose.yaml](compose.yaml) file.
 
-See [Quickstart](docs/quickstart.md) to get up and running right away.
+See [Quick Start](https://bricktracker.baerentsen.space/quick-start) to get up and running right away.
 
-See [Setup](docs/setup.md) for a more setup guide.
-
-## Usage
-
-See [first steps](docs/first-steps.md).
+See [Walk Through](https://bricktracker.baerentsen.space/tutorial-first-steps) for a more detailed guide.
 
 ## Documentation
 
 Most of the pages should be self explanatory to use.
-However, you can find more specific documentation in the [documentation](docs/DOCS.md).
+However, you can find more specific documentation in the [documentation](https://bricktracker.baerentsen.space/whatis).
 
-You can find screenshots of the application in the [overview](docs/overview.md) documentation file.
+You can find screenshots of the application in the [overview](https://bricktracker.baerentsen.space/overview) documentation.

bricktracker/app.py

@@ -1,6 +1,8 @@
 import logging
+import os
 import sys
 import time
+from pathlib import Path
 from zoneinfo import ZoneInfo
 
 from flask import current_app, Flask, g
@@ -25,12 +27,11 @@ from bricktracker.views.admin.status import admin_status_page
 from bricktracker.views.admin.storage import admin_storage_page
 from bricktracker.views.admin.tag import admin_tag_page
 from bricktracker.views.admin.theme import admin_theme_page
+from bricktracker.views.data import data_page
 from bricktracker.views.error import error_404
 from bricktracker.views.index import index_page
 from bricktracker.views.instructions import instructions_page
 from bricktracker.views.login import login_page
-from bricktracker.views.individual_minifigure import individual_minifigure_page
-from bricktracker.views.individual_part import individual_part_page
 from bricktracker.views.minifigure import minifigure_page
 from bricktracker.views.part import part_page
 from bricktracker.views.set import set_page
@@ -38,42 +39,64 @@ from bricktracker.views.statistics import statistics_page
 from bricktracker.views.storage import storage_page
 from bricktracker.views.wish import wish_page
 
-logger = logging.getLogger(__name__)
 
+def load_env_file() -> None:
+    """Load .env file into os.environ with priority: data/.env > .env (root)
 
-def _validate_config(app: Flask) -> None:
+    Also stores which BK_ variables were set via Docker environment (before loading .env)
+    so we can detect locked variables in the admin panel.
     """
-    Validate application configuration and log warnings for potential issues.
-    """
-    # Check if both individual features are disabled
-    if app.config.get('DISABLE_INDIVIDUAL_PARTS') and app.config.get('DISABLE_INDIVIDUAL_MINIFIGURES'):
-        logger.warning(
-            'Both DISABLE_INDIVIDUAL_PARTS and DISABLE_INDIVIDUAL_MINIFIGURES are enabled. '
-            'Users will not be able to track standalone parts or minifigures.'
-        )
+    import json
 
-    # Check if Rebrickable API key is missing
-    if not app.config.get('REBRICKABLE_API_KEY'):
-        logger.warning(
-            'REBRICKABLE_API_KEY is not set. You will not be able to fetch data from Rebrickable API. '
-            'Please set this in your .env file or environment variables.'
-        )
+    data_env = Path('data/.env')
+    root_env = Path('.env')
 
-    # Check authentication configuration
-    if not app.config.get('AUTHENTICATION_PASSWORD') and not app.config.get('AUTHENTICATION_KEY'):
-        logger.info(
-            'No authentication configured (AUTHENTICATION_PASSWORD or AUTHENTICATION_KEY). '
-            'Admin features will be accessible without login.'
-        )
+    # Store which BK_ variables were already in environment BEFORE loading .env
+    # These are "locked" (set via Docker's environment: directive)
+    docker_env_vars = {k: v for k, v in os.environ.items() if k.startswith('BK_')}
+
+    # Store this in a way the admin panel can access it
+    # We'll use an environment variable to store the JSON list of locked var names
+    os.environ['_BK_DOCKER_ENV_VARS'] = json.dumps(list(docker_env_vars.keys()))
+
+    env_file = None
+    if data_env.exists():
+        env_file = data_env
+        logging.info(f"Loading environment from: {data_env}")
+    elif root_env.exists():
+        env_file = root_env
+        logging.info(f"Loading environment from: {root_env} (consider migrating to data/.env)")
+
+    if env_file:
+        # Simple .env parser (no external dependencies needed)
+        with open(env_file, 'r', encoding='utf-8') as f:
+            for line in f:
+                line = line.strip()
+                # Skip comments and empty lines
+                if not line or line.startswith('#'):
+                    continue
+                # Parse key=value
+                if '=' in line:
+                    key, value = line.split('=', 1)
+                    key = key.strip()
+                    value = value.strip()
+                    # Remove quotes if present
+                    if value.startswith('"') and value.endswith('"'):
+                        value = value[1:-1]
+                    elif value.startswith("'") and value.endswith("'"):
+                        value = value[1:-1]
+                    # Only set if not already in environment (environment variables take precedence)
+                    if key not in os.environ:
+                        os.environ[key] = value
 
 
 def setup_app(app: Flask) -> None:
+    # Load .env file before configuration (if not already loaded by Docker Compose)
+    load_env_file()
+
     # Load the configuration
     BrickConfigurationList(app)
 
-    # Validate configuration
-    _validate_config(app)
-
     # Set the logging level
     if app.config['DEBUG']:
         logging.basicConfig(
@@ -110,11 +133,10 @@ def setup_app(app: Flask) -> None:
 
     # Register app routes
     app.register_blueprint(add_page)
+    app.register_blueprint(data_page)
     app.register_blueprint(index_page)
     app.register_blueprint(instructions_page)
     app.register_blueprint(login_page)
-    app.register_blueprint(individual_minifigure_page)
-    app.register_blueprint(individual_part_page)
     app.register_blueprint(minifigure_page)
     app.register_blueprint(part_page)
     app.register_blueprint(set_page)

bricktracker/config.py

@@ -13,19 +13,16 @@ CONFIG: Final[list[dict[str, Any]]] = [
     {'n': 'BRICKLINK_LINK_PART_PATTERN', 'd': 'https://www.bricklink.com/v2/catalog/catalogitem.page?P={part}&C={color}'},  # noqa: E501
     {'n': 'BRICKLINK_LINK_SET_PATTERN', 'd': 'https://www.bricklink.com/v2/catalog/catalogitem.page?S={set_num}'},  # noqa: E501
     {'n': 'BRICKLINK_LINKS', 'c': bool},
-    {'n': 'DATABASE_PATH', 'd': './app.db'},
+    {'n': 'DATABASE_PATH', 'd': 'data/app.db'},
     {'n': 'DATABASE_TIMESTAMP_FORMAT', 'd': '%Y-%m-%d-%H-%M-%S'},
     {'n': 'DEBUG', 'c': bool},
     {'n': 'DEFAULT_TABLE_PER_PAGE', 'd': 25, 'c': int},
-    {'n': 'DESCRIPTION_BADGE_MAX_LENGTH', 'd': 15, 'c': int},
-    {'n': 'DISABLE_INDIVIDUAL_MINIFIGURES', 'c': bool},
-    {'n': 'DISABLE_INDIVIDUAL_PARTS', 'c': bool},
     {'n': 'DOMAIN_NAME', 'e': 'DOMAIN_NAME', 'd': ''},
     {'n': 'FILE_DATETIME_FORMAT', 'd': '%d/%m/%Y, %H:%M:%S'},
     {'n': 'HOST', 'd': '0.0.0.0'},
     {'n': 'INDEPENDENT_ACCORDIONS', 'c': bool},
     {'n': 'INSTRUCTIONS_ALLOWED_EXTENSIONS', 'd': ['.pdf'], 'c': list},  # noqa: E501
-    {'n': 'INSTRUCTIONS_FOLDER', 'd': 'instructions', 's': True},
+    {'n': 'INSTRUCTIONS_FOLDER', 'd': 'data/instructions'},
     {'n': 'HIDE_ADD_SET', 'c': bool},
     {'n': 'HIDE_ADD_BULK_SET', 'c': bool},
     {'n': 'HIDE_ADMIN', 'c': bool},
@@ -42,16 +39,16 @@ CONFIG: Final[list[dict[str, Any]]] = [
     {'n': 'HIDE_TABLE_MISSING_PARTS', 'c': bool},
     {'n': 'HIDE_TABLE_CHECKED_PARTS', 'c': bool},
     {'n': 'HIDE_WISHES', 'c': bool},
-    {'n': 'MINIFIGURES_DEFAULT_ORDER', 'd': '"combined"."name" ASC'},  # noqa: E501
-    {'n': 'MINIFIGURES_FOLDER', 'd': 'minifigures', 's': True},
+    {'n': 'MINIFIGURES_DEFAULT_ORDER', 'd': '"rebrickable_minifigures"."name" ASC'},  # noqa: E501
+    {'n': 'MINIFIGURES_FOLDER', 'd': 'data/minifigures'},
     {'n': 'MINIFIGURES_PAGINATION_SIZE_DESKTOP', 'd': 10, 'c': int},
     {'n': 'MINIFIGURES_PAGINATION_SIZE_MOBILE', 'd': 5, 'c': int},
     {'n': 'MINIFIGURES_SERVER_SIDE_PAGINATION', 'c': bool},
     {'n': 'NO_THREADED_SOCKET', 'c': bool},
     {'n': 'PARTS_SERVER_SIDE_PAGINATION', 'c': bool},
     {'n': 'SETS_SERVER_SIDE_PAGINATION', 'c': bool},
-    {'n': 'PARTS_DEFAULT_ORDER', 'd': '"rebrickable_parts"."name" ASC, "rebrickable_parts"."color_name" ASC, "combined"."spare" ASC'},  # noqa: E501
-    {'n': 'PARTS_FOLDER', 'd': 'parts', 's': True},
+    {'n': 'PARTS_DEFAULT_ORDER', 'd': '"rebrickable_parts"."name" ASC, "rebrickable_parts"."color_name" ASC, "bricktracker_parts"."spare" ASC'},  # noqa: E501
+    {'n': 'PARTS_FOLDER', 'd': 'data/parts'},
     {'n': 'PARTS_PAGINATION_SIZE_DESKTOP', 'd': 10, 'c': int},
     {'n': 'PARTS_PAGINATION_SIZE_MOBILE', 'd': 5, 'c': int},
     {'n': 'PROBLEMS_PAGINATION_SIZE_DESKTOP', 'd': 10, 'c': int},
@@ -80,22 +77,24 @@ CONFIG: Final[list[dict[str, Any]]] = [
     {'n': 'REBRICKABLE_LINKS', 'e': 'LINKS', 'c': bool},
     {'n': 'REBRICKABLE_PAGE_SIZE', 'd': 100, 'c': int},
     {'n': 'RETIRED_SETS_FILE_URL', 'd': 'https://docs.google.com/spreadsheets/d/1rlYfEXtNKxUOZt2Mfv0H17DvK7bj6Pe0CuYwq6ay8WA/gviz/tq?tqx=out:csv&sheet=Sorted%20by%20Retirement%20Date'},  # noqa: E501
-    {'n': 'RETIRED_SETS_PATH', 'd': './retired_sets.csv'},
+    {'n': 'RETIRED_SETS_PATH', 'd': 'data/retired_sets.csv'},
     {'n': 'SETS_DEFAULT_ORDER', 'd': '"rebrickable_sets"."number" DESC, "rebrickable_sets"."version" ASC'},  # noqa: E501
-    {'n': 'SETS_FOLDER', 'd': 'sets', 's': True},
+    {'n': 'SETS_FOLDER', 'd': 'data/sets'},
     {'n': 'SETS_CONSOLIDATION', 'd': False, 'c': bool},
     {'n': 'SHOW_GRID_FILTERS', 'c': bool},
     {'n': 'SHOW_GRID_SORT', 'c': bool},
     {'n': 'SHOW_SETS_DUPLICATE_FILTER', 'd': True, 'c': bool},
     {'n': 'SKIP_SPARE_PARTS', 'c': bool},
+    {'n': 'HIDE_SPARE_PARTS', 'c': bool},
     {'n': 'SOCKET_NAMESPACE', 'd': 'bricksocket'},
     {'n': 'SOCKET_PATH', 'd': '/bricksocket/'},
     {'n': 'STORAGE_DEFAULT_ORDER', 'd': '"bricktracker_metadata_storages"."name" ASC'},  # noqa: E501
     {'n': 'THEMES_FILE_URL', 'd': 'https://cdn.rebrickable.com/media/downloads/themes.csv.gz'},  # noqa: E501
-    {'n': 'THEMES_PATH', 'd': './themes.csv'},
+    {'n': 'THEMES_PATH', 'd': 'data/themes.csv'},
     {'n': 'TIMEZONE', 'd': 'Etc/UTC'},
     {'n': 'USE_REMOTE_IMAGES', 'c': bool},
     {'n': 'WISHES_DEFAULT_ORDER', 'd': '"bricktracker_wishes"."rowid" DESC'},
     {'n': 'STATISTICS_SHOW_CHARTS', 'd': True, 'c': bool},
     {'n': 'STATISTICS_DEFAULT_EXPANDED', 'd': True, 'c': bool},
+    {'n': 'DARK_MODE', 'c': bool},
 ]

bricktracker/config_manager.py

@@ -10,7 +10,6 @@ logger = logging.getLogger(__name__)
 LIVE_CHANGEABLE_VARS: Final[List[str]] = [
     'BK_BRICKLINK_LINKS',
     'BK_DEFAULT_TABLE_PER_PAGE',
-    'BK_DESCRIPTION_BADGE_MAX_LENGTH',
     'BK_INDEPENDENT_ACCORDIONS',
     'BK_HIDE_ADD_SET',
     'BK_HIDE_ADD_BULK_SET',
@@ -47,12 +46,14 @@ LIVE_CHANGEABLE_VARS: Final[List[str]] = [
     'BK_SHOW_GRID_SORT',
     'BK_SHOW_SETS_DUPLICATE_FILTER',
     'BK_SKIP_SPARE_PARTS',
+    'BK_HIDE_SPARE_PARTS',
     'BK_USE_REMOTE_IMAGES',
     'BK_PEERON_DOWNLOAD_DELAY',
     'BK_PEERON_MIN_IMAGE_SIZE',
     'BK_REBRICKABLE_PAGE_SIZE',
     'BK_STATISTICS_SHOW_CHARTS',
     'BK_STATISTICS_DEFAULT_EXPANDED',
+    'BK_DARK_MODE',
     # Default ordering and formatting
     'BK_INSTRUCTIONS_ALLOWED_EXTENSIONS',
     'BK_MINIFIGURES_DEFAULT_ORDER',
@@ -84,8 +85,6 @@ RESTART_REQUIRED_VARS: Final[List[str]] = [
     'BK_AUTHENTICATION_KEY',
     'BK_DATABASE_PATH',
     'BK_DEBUG',
-    'BK_DISABLE_INDIVIDUAL_MINIFIGURES',
-    'BK_DISABLE_INDIVIDUAL_PARTS',
     'BK_DOMAIN_NAME',
     'BK_HOST',
     'BK_PORT',
@@ -110,7 +109,20 @@ class ConfigManager:
     """Manages live configuration updates for BrickTracker"""
 
     def __init__(self):
-        self.env_file_path = Path('.env')
+        # Check for .env in data folder first (v1.3+), fallback to root (backward compatibility)
+        data_env = Path('data/.env')
+        root_env = Path('.env')
+
+        if data_env.exists():
+            self.env_file_path = data_env
+            logger.info("Using configuration file: data/.env")
+        elif root_env.exists():
+            self.env_file_path = root_env
+            logger.info("Using configuration file: .env (consider migrating to data/.env)")
+        else:
+            # Default to data/.env for new installations
+            self.env_file_path = data_env
+            logger.info("Configuration file will be created at: data/.env")
 
     def get_current_config(self) -> Dict[str, Any]:
         """Get current configuration values for live-changeable variables"""
@@ -176,7 +188,7 @@ class ConfigManager:
             else:
                 return []
         # Integer variables (pagination sizes, delays, etc.) - Check BEFORE boolean check
-        if any(keyword in var_name.lower() for keyword in ['_size', '_page', 'delay', 'min_', 'per_page', 'page_size', '_length']):
+        if any(keyword in var_name.lower() for keyword in ['_size', '_page', 'delay', 'min_', 'per_page', 'page_size']):
             try:
                 return int(value)
             except (ValueError, TypeError):
@@ -205,6 +217,8 @@ class ConfigManager:
     def _update_env_file(self, var_name: str, value: Any) -> None:
         """Update the .env file with new value"""
         if not self.env_file_path.exists():
+            # Ensure parent directory exists
+            self.env_file_path.parent.mkdir(parents=True, exist_ok=True)
             self.env_file_path.touch()
 
         # Read current .env content
@@ -307,9 +321,11 @@ class ConfigManager:
             'BK_SETS_CONSOLIDATION': 'Enable set consolidation/grouping functionality',
             'BK_SHOW_GRID_FILTERS': 'Show filter options on grids by default',
             'BK_SHOW_GRID_SORT': 'Show sort options on grids by default',
-            'BK_SKIP_SPARE_PARTS': 'Skip spare parts when importing sets',
+            'BK_SKIP_SPARE_PARTS': 'Skip importing spare parts when downloading sets from Rebrickable',
+            'BK_HIDE_SPARE_PARTS': 'Hide spare parts from parts lists (spare parts must still be in database)',
             'BK_USE_REMOTE_IMAGES': 'Use remote images from Rebrickable CDN instead of local',
             'BK_STATISTICS_SHOW_CHARTS': 'Show collection growth charts on statistics page',
-            'BK_STATISTICS_DEFAULT_EXPANDED': 'Expand all statistics sections by default'
+            'BK_STATISTICS_DEFAULT_EXPANDED': 'Expand all statistics sections by default',
+            'BK_DARK_MODE': 'Enable dark mode theme'
         }
         return help_text.get(var_name, 'No help available for this variable')

bricktracker/configuration.py

@@ -60,7 +60,7 @@ class BrickConfiguration(object):
             if self.cast == bool and isinstance(value, str):
                 value = value.lower() in ('true', 'yes', '1')
 
-            # Static path fixup
+            # Static path fixup (legacy - only for paths with s: True flag)
             if self.static_path and isinstance(value, str):
                 value = os.path.normpath(value)
 
@@ -70,6 +70,10 @@ class BrickConfiguration(object):
                 # Remove static prefix
                 value = value.removeprefix('static/')
 
+            # Normalize regular paths (not marked as static)
+            elif not self.static_path and isinstance(value, str) and ('FOLDER' in self.name or 'PATH' in self.name):
+                value = os.path.normpath(value)
+
         # Type casting
         if self.cast is not None:
             self.value = self.cast(value)

bricktracker/individual_minifigure.py

@@ -1,500 +0,0 @@
-import logging
-import traceback
-from typing import Any, Self, TYPE_CHECKING
-from uuid import uuid4
-
-from flask import current_app, url_for
-
-from .exceptions import NotFoundException, DatabaseException, ErrorException
-from .parser import parse_minifig
-from .rebrickable import Rebrickable
-from .rebrickable_minifigure import RebrickableMinifigure
-from .set_owner_list import BrickSetOwnerList
-from .set_purchase_location_list import BrickSetPurchaseLocationList
-from .set_storage_list import BrickSetStorageList
-from .set_tag_list import BrickSetTagList
-from .sql import BrickSQL
-
-if TYPE_CHECKING:
-    from .socket import BrickSocket
-
-logger = logging.getLogger(__name__)
-
-
-# Individual minifigure (not associated with a set)
-class IndividualMinifigure(RebrickableMinifigure):
-    # Queries
-    select_query: str = 'individual_minifigure/select/by_id'
-    light_query: str = 'individual_minifigure/select/light'
-    insert_query: str = 'individual_minifigure/insert'
-
-    # Delete a individual minifigure
-    def delete(self, /) -> None:
-        BrickSQL().executescript(
-            'individual_minifigure/delete/individual_minifigure',
-            id=self.fields.id
-        )
-
-    # Import a individual minifigure into the database
-    def download(self, socket: 'BrickSocket', data: dict[str, Any], /) -> bool:
-        # Load the minifigure
-        if not self.load(socket, data, from_download=True):
-            return False
-
-        try:
-            # Insert into the database
-            socket.auto_progress(
-                message='Minifigure {figure}: inserting into database'.format(
-                    figure=self.fields.figure
-                ),
-                increment_total=True,
-            )
-
-            # Generate an UUID for self
-            self.fields.id = str(uuid4())
-
-            # Save the storage
-            storage = BrickSetStorageList.get(
-                data.get('storage', ''),
-                allow_none=True
-            )
-            self.fields.storage = storage.fields.id if storage else None
-
-            # Save the purchase location
-            purchase_location = BrickSetPurchaseLocationList.get(
-                data.get('purchase_location', ''),
-                allow_none=True
-            )
-            self.fields.purchase_location = purchase_location.fields.id if purchase_location else None
-
-            # Save quantity and description
-            self.fields.quantity = int(data.get('quantity', 1))
-            self.fields.description = data.get('description', '')
-
-            # IMPORTANT: Insert rebrickable minifigure FIRST
-            # bricktracker_individual_minifigures has FK to rebrickable_minifigures
-            self.insert_rebrickable_loose()
-
-            # Now insert into bricktracker_individual_minifigures
-            # Use no_defer=True to ensure the insert happens before we insert parts
-            # (parts have a foreign key constraint on this id)
-            self.insert(commit=False, no_defer=True)
-
-            # Save the owners
-            owners: list[str] = list(data.get('owners', []))
-            for id in owners:
-                owner = BrickSetOwnerList.get(id)
-                owner.update_individual_minifigure_state(self, state=True)
-
-            # Save the tags
-            tags: list[str] = list(data.get('tags', []))
-            for id in tags:
-                tag = BrickSetTagList.get(id)
-                tag.update_individual_minifigure_state(self, state=True)
-
-            # Load the parts (elements) for this minifigure
-            if not self.download_parts(socket):
-                return False
-
-            # Commit the transaction to the database
-            socket.auto_progress(
-                message='Minifigure {figure}: writing to the database'.format(
-                    figure=self.fields.figure
-                ),
-                increment_total=True,
-            )
-
-            BrickSQL().commit()
-
-            # Info
-            logger.info('Minifigure {figure}: imported (id: {id})'.format(
-                figure=self.fields.figure,
-                id=self.fields.id,
-            ))
-
-            # Complete
-            socket.complete(
-                message='Minifigure {figure}: imported (<a href="{url}">Go to the minifigure</a>)'.format(
-                    figure=self.fields.figure,
-                    url=self.url()
-                ),
-                download=True
-            )
-
-        except Exception as e:
-            socket.fail(
-                message='Error while importing minifigure {figure}: {error}'.format(
-                    figure=self.fields.figure,
-                    error=e,
-                )
-            )
-
-            logger.debug(traceback.format_exc())
-
-            return False
-
-        return True
-
-    # Download parts (elements) for this individual minifigure
-    def download_parts(self, socket: 'BrickSocket', /) -> bool:
-        """Download minifigure parts using get_minifig_elements()"""
-        try:
-            # Check if we have cached parts data from load()
-            if hasattr(self, '_cached_parts_response'):
-                response = self._cached_parts_response
-                logger.debug('Using cached parts data from load()')
-            else:
-                # Need to fetch parts data
-                socket.auto_progress(
-                    message='Minifigure {figure}: loading parts from Rebrickable'.format(
-                        figure=self.fields.figure
-                    ),
-                    increment_total=True,
-                )
-
-                logger.debug('rebrick.lego.get_minifig_elements("{figure}")'.format(
-                    figure=self.fields.figure,
-                ))
-
-                # Load parts data from Rebrickable API
-                import json
-                from rebrick import lego
-
-                parameters = {
-                    'api_key': current_app.config['REBRICKABLE_API_KEY'],
-                    'page_size': current_app.config['REBRICKABLE_PAGE_SIZE'],
-                }
-
-                response = json.loads(lego.get_minifig_elements(
-                    self.fields.figure,
-                    **parameters
-                ).read())
-
-            socket.auto_progress(
-                message='Minifigure {figure}: saving parts to database'.format(
-                    figure=self.fields.figure
-                ),
-            )
-
-            # Insert each part into individual_minifigure_parts table
-            from .rebrickable_part import RebrickablePart
-
-            if 'results' in response:
-                logger.debug(f'Processing {len(response["results"])} parts for minifigure {self.fields.figure}')
-
-                for idx, result in enumerate(response['results']):
-                    part_num = result['part']['part_num']
-                    color_id = result['color']['id']
-
-                    logger.debug(
-                        f'Part {idx+1}/{len(response["results"])}: {part_num} '
-                        f'(color: {color_id}, quantity: {result["quantity"]})'
-                    )
-
-                    # Insert rebrickable part data first
-                    part_data = RebrickablePart.from_rebrickable(result)
-                    logger.debug(f'Rebrickable part data keys: {list(part_data.keys())}')
-
-                    # Insert into rebrickable_parts if not exists
-                    BrickSQL().execute(
-                        'rebrickable/part/insert',
-                        parameters=part_data,
-                        commit=False,
-                    )
-
-                    # Download part image if not using remote images
-                    if not current_app.config['USE_REMOTE_IMAGES']:
-                        # Create a RebrickablePart instance for image download
-                        from .set import BrickSet
-                        try:
-                            part_instance = RebrickablePart(record=part_data)
-                            from .rebrickable_image import RebrickableImage
-                            RebrickableImage(
-                                BrickSet(),  # Dummy set
-                                minifigure=self,
-                                part=part_instance,
-                            ).download()
-                        except Exception as e:
-                            logger.warning(
-                                f'Could not download image for part {part_num}: {e}'
-                            )
-
-                    # Insert into bricktracker_individual_minifigure_parts
-                    individual_part_params = {
-                        'id': self.fields.id,
-                        'part': part_num,
-                        'color': color_id,
-                        'spare': result.get('is_spare', False),
-                        'quantity': result['quantity'],
-                        'element': result.get('element_id'),
-                        'rebrickable_inventory': result['id'],
-                    }
-                    logger.debug(f'Individual part params: {individual_part_params}')
-
-                    BrickSQL().execute(
-                        'individual_minifigure/part/insert',
-                        parameters=individual_part_params,
-                        commit=False,
-                    )
-
-                logger.debug(f'Successfully inserted all {len(response["results"])} parts')
-            else:
-                logger.warning(f'No results in parts response for minifigure {self.fields.figure}')
-
-            # Clean up cached data
-            if hasattr(self, '_cached_parts_response'):
-                delattr(self, '_cached_parts_response')
-
-            return True
-
-        except Exception as e:
-            socket.fail(
-                message='Error loading parts for minifigure {figure}: {error}'.format(
-                    figure=self.fields.figure,
-                    error=e,
-                )
-            )
-            logger.debug(traceback.format_exc())
-            return False
-
-    # Insert the individual minifigure from Rebrickable
-    def insert_rebrickable_loose(self, /) -> None:
-        """Insert rebrickable minifigure data (without set association)"""
-        # Insert the Rebrickable minifigure to the database
-        # Note: We override the parent's insert_rebrickable since we don't have a brickset
-        from .rebrickable_image import RebrickableImage
-
-        # Explicitly build parameters for rebrickable_minifigures insert
-        params = {
-            'figure': self.fields.figure,
-            'number': self.fields.number,
-            'name': self.fields.name,
-            'image': self.fields.image,
-            'number_of_parts': self.fields.number_of_parts,
-        }
-
-        BrickSQL().execute(
-            RebrickableMinifigure.insert_query,
-            parameters=params,
-            commit=False,
-        )
-
-        # Download image locally if not using remote images
-        if not current_app.config['USE_REMOTE_IMAGES']:
-            # Create a dummy BrickSet for RebrickableImage
-            # RebrickableImage checks minifigure first before set, so this works
-            from .set import BrickSet
-            try:
-                RebrickableImage(
-                    BrickSet(),  # Dummy set - not used since minifigure takes priority
-                    minifigure=self,
-                ).download()
-                logger.debug(f'Downloaded image for individual minifigure {self.fields.figure}')
-            except Exception as e:
-                logger.warning(
-                    f'Could not download image for individual minifigure {self.fields.figure}: {e}'
-                )
-
-    # Load the minifigure from Rebrickable
-    def load(
-        self,
-        socket: 'BrickSocket',
-        data: dict[str, Any],
-        /,
-        *,
-        from_download=False,
-    ) -> bool:
-        # Reset the progress
-        socket.progress_count = 0
-        socket.progress_total = 2
-
-        try:
-            # Check if individual minifigures are disabled
-            from flask import current_app
-            if current_app.config.get('DISABLE_INDIVIDUAL_MINIFIGURES', False):
-                raise ErrorException(
-                    'Individual minifigures system is disabled. '
-                    'Only set-based minifigures can be added.'
-                )
-
-            socket.auto_progress(message='Parsing minifigure number')
-            figure = parse_minifig(str(data['figure']))
-
-            socket.auto_progress(
-                message='Minifigure {figure}: loading from Rebrickable'.format(
-                    figure=figure,
-                ),
-            )
-
-            logger.debug('rebrick.lego.get_minifig_elements("{figure}")'.format(
-                figure=figure,
-            ))
-
-            # Load from Rebrickable using get_minifig_elements
-            # This gives us both minifigure info and parts in one call
-            import json
-            from rebrick import lego
-
-            parameters = {
-                'api_key': current_app.config['REBRICKABLE_API_KEY'],
-                'page_size': current_app.config['REBRICKABLE_PAGE_SIZE'],
-            }
-
-            response = json.loads(lego.get_minifig_elements(
-                figure,
-                **parameters
-            ).read())
-
-            # Extract minifigure info from the first part's metadata
-            if 'results' in response and len(response['results']) > 0:
-                first_part = response['results'][0]
-
-                # Build minifigure data from the response
-                self.fields.figure = first_part['set_num']
-                self.fields.number_of_parts = response['count']
-
-                # We need to fetch the proper name and image from get_minifig()
-                # This is a small additional call but gives us the proper minifigure data
-                try:
-                    # get_minifig() only needs api_key, not page_size
-                    minifig_params = {
-                        'api_key': current_app.config['REBRICKABLE_API_KEY']
-                    }
-                    minifig_response = json.loads(lego.get_minifig(
-                        figure,
-                        **minifig_params
-                    ).read())
-                    self.fields.name = minifig_response.get('name', f"Minifigure {figure}")
-
-                    # Use the minifig image from get_minifig() - this is the assembled minifig
-                    self.fields.image = minifig_response.get('set_img_url')
-
-                    # Extract number from figure (e.g., fig-005997 -> 5997)
-                    try:
-                        self.fields.number = int(figure.split('-')[1])
-                    except:
-                        self.fields.number = 0
-
-                except Exception as e:
-                    logger.warning(f'Could not fetch minifigure name: {e}')
-                    self.fields.name = f"Minifigure {figure}"
-                    # Try to extract number anyway
-                    try:
-                        self.fields.number = int(figure.split('-')[1])
-                    except:
-                        self.fields.number = 0
-
-                    # Fallback: try to extract image from first part with element_id
-                    self.fields.image = None
-                    for result in response['results']:
-                        if result.get('element_id') and result['part'].get('part_img_url'):
-                            self.fields.image = result['part']['part_img_url']
-                            break
-
-                # Store the parts data for later use in download
-                self._cached_parts_response = response
-            else:
-                raise NotFoundException(f'Minifigure {figure} has no parts in Rebrickable')
-
-            socket.emit('MINIFIGURE_LOADED', self.short(
-                from_download=from_download
-            ))
-
-            if not from_download:
-                socket.complete(
-                    message='Minifigure {figure}: loaded from Rebrickable'.format(
-                        figure=self.fields.figure
-                    )
-                )
-
-            return True
-
-        except Exception as e:
-            # Check if this is the "disabled" error - if so, show cleaner message
-            error_msg = str(e)
-            if 'Individual minifigures system is disabled' in error_msg:
-                socket.fail(message=error_msg)
-            else:
-                socket.fail(
-                    message='Could not load the minifigure from Rebrickable: {error}. Data: {data}'.format(
-                        error=error_msg,
-                        data=data,
-                    )
-                )
-
-            if not isinstance(e, (NotFoundException, ErrorException)):
-                logger.debug(traceback.format_exc())
-
-        return False
-
-    # Return a short form of the minifigure
-    def short(self, /, *, from_download: bool = False) -> dict[str, Any]:
-        return {
-            'download': from_download,
-            'image': self.url_for_image(),
-            'name': self.fields.name,
-            'figure': self.fields.figure,
-        }
-
-    # Select a individual minifigure by ID
-    def select_by_id(self, id: str, /) -> Self:
-        # Save the ID parameter
-        self.fields.id = id
-
-        # Import status list here to get metadata columns
-        from .set_status_list import BrickSetStatusList
-
-        # Pass metadata columns to the query with correct table names for individual minifigures
-        context = {
-            'owners': ', ' + BrickSetOwnerList.as_columns(table='bricktracker_individual_minifigure_owners') if BrickSetOwnerList.list() else '',
-            'statuses': ', ' + BrickSetStatusList.as_columns(table='bricktracker_individual_minifigure_statuses', all=True) if BrickSetStatusList.list(all=True) else '',
-            'tags': ', ' + BrickSetTagList.as_columns(table='bricktracker_individual_minifigure_tags') if BrickSetTagList.list() else '',
-        }
-
-        if not self.select(**context):
-            raise NotFoundException(
-                'Individual minifigure with ID {id} was not found in the database'.format(
-                    id=id,
-                ),
-            )
-
-        return self
-
-    # URL to this individual minifigure instance
-    def url(self, /) -> str:
-        return url_for('individual_minifigure.details', id=self.fields.id)
-
-    # String representation for debugging
-    def __repr__(self, /) -> str:
-        """String representation for debugging"""
-        figure = getattr(self.fields, 'figure', 'unknown')
-        name = getattr(self.fields, 'name', 'Unknown')
-        qty = getattr(self.fields, 'quantity', 0)
-        return f'<IndividualMinifigure {figure} "{name}" qty:{qty}>'
-
-    # URL for updating quantity
-    def url_for_quantity(self, /) -> str:
-        return url_for('individual_minifigure.update_quantity', id=self.fields.id)
-
-    # URL for updating description
-    def url_for_description(self, /) -> str:
-        return url_for('individual_minifigure.update_description', id=self.fields.id)
-
-    # Parts
-    def generic_parts(self, /):
-        from .part_list import BrickPartList
-        return BrickPartList().from_individual_minifigure(self)
-
-    # Override from_rebrickable to handle minifigure data
-    @staticmethod
-    def from_rebrickable(data: dict[str, Any], /, **_) -> dict[str, Any]:
-        # Extracting number
-        number = int(str(data['set_num'])[5:])
-
-        return {
-            'figure': str(data['set_num']),
-            'number': int(number),
-            'name': str(data['set_name']),
-            'image': data.get('set_img_url'),
-            'number_of_parts': int(data.get('num_parts', 0)),
-        }

bricktracker/individual_minifigure_list.py

@@ -1,77 +0,0 @@
-import logging
-from typing import Self
-
-from .individual_minifigure import IndividualMinifigure
-from .record_list import BrickRecordList
-
-logger = logging.getLogger(__name__)
-
-
-# Individual minifigures list
-class IndividualMinifigureList(BrickRecordList[IndividualMinifigure]):
-    # Queries
-    instances_by_figure_query: str = 'individual_minifigure/select/instances_by_figure'
-    using_storage_query: str = 'individual_minifigure/list/using_storage'
-    without_storage_query: str = 'individual_minifigure/list/without_storage'
-
-    def __init__(self, /):
-        super().__init__()
-
-    # Load all individual instances of a specific minifigure figure
-    def instances_by_figure(self, figure: str, /) -> Self:
-        # Save the figure parameter
-        self.fields.figure = figure
-
-        # Import metadata lists to get columns
-        from .set_owner_list import BrickSetOwnerList
-        from .set_status_list import BrickSetStatusList
-        from .set_tag_list import BrickSetTagList
-
-        # Prepare context with metadata columns
-        context = {
-            'owners': BrickSetOwnerList.as_columns(table='bricktracker_individual_minifigure_owners') if BrickSetOwnerList.list() else 'NULL AS "no_owners"',
-            'statuses': BrickSetStatusList.as_columns(table='bricktracker_individual_minifigure_statuses', all=True) if BrickSetStatusList.list(all=True) else 'NULL AS "no_statuses"',
-            'tags': BrickSetTagList.as_columns(table='bricktracker_individual_minifigure_tags') if BrickSetTagList.list() else 'NULL AS "no_tags"',
-        }
-
-        # Load the instances from the database
-        self.list(override_query=self.instances_by_figure_query, **context)
-
-        return self
-
-    # Load all individual minifigures using a specific storage
-    def using_storage(self, storage: 'BrickSetStorage', /) -> Self:
-        # Save the storage parameter
-        self.fields.storage = storage.fields.id
-
-        # Load the minifigures from the database
-        self.list(override_query=self.using_storage_query)
-
-        return self
-
-    # Load all individual minifigures without storage
-    def without_storage(self, /) -> Self:
-        # Load minifigures with no storage
-        self.list(override_query=self.without_storage_query)
-
-        return self
-
-    # Base individual minifigure list
-    def list(
-        self,
-        /,
-        *,
-        override_query: str | None = None,
-        order: str | None = None,
-        limit: int | None = None,
-        **context,
-    ) -> None:
-        # Load the individual minifigures from the database
-        for record in super().select(
-            override_query=override_query,
-            order=order,
-            limit=limit,
-            **context
-        ):
-            individual_minifigure = IndividualMinifigure(record=record)
-            self.records.append(individual_minifigure)

bricktracker/individual_part.py

@@ -1,700 +0,0 @@
-import logging
-import os
-import traceback
-from typing import Any, Self, TYPE_CHECKING
-from urllib.parse import urlparse
-from uuid import uuid4
-
-from flask import current_app, url_for
-import requests
-from shutil import copyfileobj
-
-from .exceptions import NotFoundException, DatabaseException, ErrorException
-from .record import BrickRecord
-from .set_owner_list import BrickSetOwnerList
-from .set_purchase_location_list import BrickSetPurchaseLocationList
-from .set_storage_list import BrickSetStorageList
-from .set_tag_list import BrickSetTagList
-from .sql import BrickSQL
-
-if TYPE_CHECKING:
-    from .socket import BrickSocket
-
-logger = logging.getLogger(__name__)
-
-
-# Individual part (standalone, not associated with a set or minifigure)
-class IndividualPart(BrickRecord):
-    # Queries
-    select_query: str = 'individual_part/select/by_id'
-    insert_query: str = 'individual_part/insert'
-    update_query: str = 'individual_part/update'
-
-    def __init__(
-        self,
-        /,
-        *,
-        record: Any | None = None
-    ):
-        super().__init__()
-
-        # Ingest the record if it has one
-        if record is not None:
-            self.ingest(record)
-
-    # Select a specific individual part by UUID
-    def select_by_id(self, id: str, /) -> Self:
-        self.fields.id = id
-        if not self.select(override_query=self.select_query):
-            raise NotFoundException(
-                'Individual part with id "{id}" not found'.format(id=id)
-            )
-        return self
-
-    # Delete an individual part
-    def delete(self, /) -> None:
-        sql = BrickSQL()
-        sql.executescript(
-            'individual_part/delete',
-            id=self.fields.id
-        )
-        sql.commit()
-
-    # Generate HTML ID for form elements
-    def html_id(self, prefix: str | None = None, /) -> str:
-        """Generate HTML ID for form elements"""
-        components: list[str] = ['individual-part']
-
-        if prefix is not None:
-            components.append(prefix)
-
-        components.append(self.fields.part)
-        components.append(str(self.fields.color))
-        components.append(self.fields.id)
-
-        return '-'.join(components)
-
-    # URL for quantity update
-    def url_for_quantity(self, /) -> str:
-        """URL for updating quantity"""
-        return url_for('individual_part.update_quantity', id=self.fields.id)
-
-    # URL for description update
-    def url_for_description(self, /) -> str:
-        """URL for updating description"""
-        return url_for('individual_part.update_description', id=self.fields.id)
-
-    # URL for problem (missing/damaged) update
-    def url_for_problem(self, problem_type: str, /) -> str:
-        """URL for updating problem counts (missing/damaged)"""
-        if problem_type == 'missing':
-            return url_for('individual_part.update_missing', id=self.fields.id)
-        elif problem_type == 'damaged':
-            return url_for('individual_part.update_damaged', id=self.fields.id)
-        else:
-            raise ValueError(f'Invalid problem type: {problem_type}')
-
-    # URL for checked status update
-    def url_for_checked(self, /) -> str:
-        """URL for updating checked status"""
-        return url_for('individual_part.update_checked', id=self.fields.id)
-
-    # URL for this part's detail page
-    def url(self, /) -> str:
-        """URL for this part's detail page"""
-        return url_for('individual_part.details', id=self.fields.id)
-
-    # String representation for debugging
-    def __repr__(self, /) -> str:
-        """String representation for debugging"""
-        part_id = getattr(self.fields, 'part', 'unknown')
-        color_id = getattr(self.fields, 'color', 'unknown')
-        qty = getattr(self.fields, 'quantity', 0)
-        return f'<IndividualPart {part_id} color:{color_id} qty:{qty}>'
-
-    # Get or fetch color information from rebrickable_colors table
-    @staticmethod
-    def get_or_fetch_color(color_id: int, /) -> dict[str, Any] | None:
-        """
-        Get color information from cache table, or fetch from API if not cached.
-        Returns dict with: name, rgb, is_trans, bricklink_color_id, bricklink_color_name
-        """
-        sql = BrickSQL()
-
-        # Check if color exists in cache
-        check_query = """
-            SELECT "color_id", "name", "rgb", "is_trans",
-                   "bricklink_color_id", "bricklink_color_name"
-            FROM "rebrickable_colors"
-            WHERE "color_id" = :color_id
-        """
-        sql.cursor.execute(check_query, {'color_id': color_id})
-        result = sql.cursor.fetchone()
-
-        if result:
-            # Color found in cache
-            return {
-                'color_id': result[0],
-                'name': result[1],
-                'rgb': result[2],
-                'is_trans': result[3],
-                'bricklink_color_id': result[4],
-                'bricklink_color_name': result[5]
-            }
-
-        # Color not in cache, fetch from API
-        try:
-            import rebrick
-            import json
-
-            rebrick.init(current_app.config['REBRICKABLE_API_KEY'])
-            color_response = rebrick.lego.get_color(color_id)
-            color_data = json.loads(color_response.read())
-
-            # Extract BrickLink color info
-            bricklink_color_id = None
-            bricklink_color_name = None
-
-            if 'external_ids' in color_data and 'BrickLink' in color_data['external_ids']:
-                bricklink_info = color_data['external_ids']['BrickLink']
-                if 'ext_ids' in bricklink_info and bricklink_info['ext_ids']:
-                    bricklink_color_id = bricklink_info['ext_ids'][0]
-                if 'ext_descrs' in bricklink_info and bricklink_info['ext_descrs']:
-                    bricklink_color_name = bricklink_info['ext_descrs'][0][0] if bricklink_info['ext_descrs'][0] else None
-
-            # Store in cache
-            insert_query = """
-                INSERT OR REPLACE INTO "rebrickable_colors" (
-                    "color_id", "name", "rgb", "is_trans",
-                    "bricklink_color_id", "bricklink_color_name"
-                ) VALUES (
-                    :color_id, :name, :rgb, :is_trans,
-                    :bricklink_color_id, :bricklink_color_name
-                )
-            """
-            sql.cursor.execute(insert_query, {
-                'color_id': color_data['id'],
-                'name': color_data['name'],
-                'rgb': color_data.get('rgb'),
-                'is_trans': color_data.get('is_trans', False),
-                'bricklink_color_id': bricklink_color_id,
-                'bricklink_color_name': bricklink_color_name
-            })
-            sql.connection.commit()
-
-            logger.info(f'Cached color {color_id} ({color_data["name"]}) with BrickLink ID {bricklink_color_id}')
-
-            return {
-                'color_id': color_data['id'],
-                'name': color_data['name'],
-                'rgb': color_data.get('rgb'),
-                'is_trans': color_data.get('is_trans', False),
-                'bricklink_color_id': bricklink_color_id,
-                'bricklink_color_name': bricklink_color_name
-            }
-
-        except Exception as e:
-            logger.warning(f'Could not fetch color {color_id} from API: {e}')
-            return None
-
-    # Download image for this part
-    def download_image(self, image_url: str, /) -> None:
-        if not image_url:
-            return
-
-        # Create image_id from URL
-        image_id, _ = os.path.splitext(os.path.basename(urlparse(image_url).path))
-
-        if not image_id:
-            return
-
-        # Build path
-        parts_folder = current_app.config['PARTS_FOLDER']
-        extension = 'jpg'  # Everything is saved as jpg
-        path = os.path.join(
-            current_app.static_folder,  # type: ignore
-            parts_folder,
-            f'{image_id}.{extension}'
-        )
-
-        # Avoid downloading if file exists
-        if os.path.exists(path):
-            return
-
-        # Create directory if it doesn't exist
-        os.makedirs(os.path.dirname(path), exist_ok=True)
-
-        # Download the image
-        try:
-            response = requests.get(image_url, stream=True)
-            if response.ok:
-                with open(path, 'wb') as f:
-                    copyfileobj(response.raw, f)
-                logger.info(f'Downloaded image for part {self.fields.part} color {self.fields.color} to {path}')
-        except Exception as e:
-            logger.warning(f'Could not download image for part {self.fields.part} color {self.fields.color}: {e}')
-
-    # Load available colors for a part
-    def load_colors(self, socket: 'BrickSocket', data: dict[str, Any], /) -> bool:
-        # Check if individual parts are disabled
-        if current_app.config.get('DISABLE_INDIVIDUAL_PARTS', False):
-            socket.fail(message='Individual parts system is disabled.')
-            return False
-
-        try:
-            # Extract part number
-            part_num = str(data.get('part', '')).strip()
-
-            if not part_num:
-                raise ErrorException('Part number is required')
-
-            # Fetch available colors from Rebrickable
-            import rebrick
-            import json
-
-            rebrick.init(current_app.config['REBRICKABLE_API_KEY'])
-
-            # Setup progress tracking
-            socket.progress_count = 0
-            socket.progress_total = 2  # Fetch part info + fetch colors
-
-            try:
-                # Get part information for the name
-                socket.auto_progress(message='Fetching part information')
-                part_response = rebrick.lego.get_part(part_num)
-                part_data = json.loads(part_response.read())
-                part_name = part_data.get('name', part_num)
-
-                # Get all available colors for this part
-                socket.auto_progress(message='Fetching available colors')
-                colors_response = rebrick.lego.get_part_colors(part_num)
-                colors_data = json.loads(colors_response.read())
-
-                # Extract the results
-                colors = colors_data.get('results', [])
-
-                if not colors:
-                    raise ErrorException(f'No colors found for part {part_num}')
-
-                # Download images locally if USE_REMOTE_IMAGES is False
-                if not current_app.config.get('USE_REMOTE_IMAGES', False):
-                    # Add image downloads to progress
-                    socket.progress_total += len(colors)
-
-                    for color in colors:
-                        image_url = color.get('part_img_url', '')
-                        if image_url:
-                            socket.auto_progress(message=f'Downloading image for {color.get("color_name", "color")}')
-                            try:
-                                self.download_image(image_url)
-                            except Exception as e:
-                                logger.warning(f'Could not download image for part {part_num} color {color.get("color_id")}: {e}')
-
-                # Emit the part colors loaded event
-                logger.info(f'Emitting {len(colors)} colors for part {part_num} ({part_name})')
-
-                socket.emit(
-                    'PART_COLORS_LOADED',
-                    {
-                        'part': part_num,
-                        'part_name': part_name,
-                        'colors': colors,
-                        'count': len(colors)
-                    }
-                )
-
-                logger.info(f'Successfully loaded {len(colors)} colors for part {part_num}')
-                return True
-
-            except Exception as e:
-                error_msg = str(e)
-
-                # Provide helpful error message for printed/decorated parts
-                if '404' in error_msg or 'Not Found' in error_msg:
-                    # Check if this might be a printed part (has letters/pattern code)
-                    base_part = ''.join(c for c in part_num if c.isdigit())
-
-                    if base_part and base_part != part_num:
-                        raise ErrorException(
-                            f'Part {part_num} not found in Rebrickable. This appears to be a printed/decorated part. '
-                            f'Try searching for the base part number: {base_part}'
-                        )
-                    else:
-                        raise ErrorException(
-                            f'Part {part_num} not found in Rebrickable. '
-                            f'Please verify the part number is correct.'
-                        )
-                else:
-                    raise ErrorException(
-                        f'Could not fetch colors for part {part_num}: {error_msg}'
-                    )
-
-        except Exception as e:
-            error_msg = str(e)
-            socket.fail(message=f'Could not load part colors: {error_msg}')
-
-            if not isinstance(e, (NotFoundException, ErrorException)):
-                logger.debug(traceback.format_exc())
-
-            return False
-
-    # Add a new individual part
-    def add(self, socket: 'BrickSocket', data: dict[str, Any], /) -> bool:
-        # Check if individual parts are disabled
-        if current_app.config.get('DISABLE_INDIVIDUAL_PARTS', False):
-            socket.fail(message='Individual parts system is disabled.')
-            return False
-
-        try:
-            # Reset progress
-            socket.progress_count = 0
-            socket.progress_total = 3
-
-            socket.auto_progress(message='Validating part and color')
-
-            # Extract data
-            part_num = str(data.get('part', '')).strip()
-            color_id = int(data.get('color', 0))
-            quantity = int(data.get('quantity', 1))
-
-            if not part_num:
-                raise ErrorException('Part number is required')
-            if color_id <= 0:
-                raise ErrorException('Valid color ID is required')
-            if quantity <= 0:
-                raise ErrorException('Quantity must be greater than 0')
-
-            # Check if color info was pre-loaded (from load_colors)
-            color_data = data.get('color_info', None)
-            part_name = data.get('part_name', None)
-
-            # Validate part+color exists in rebrickable_parts
-            # If not, fetch from Rebrickable or use pre-loaded data and insert
-            sql = BrickSQL()
-            check_query = """
-                SELECT COUNT(*) FROM "rebrickable_parts"
-                WHERE "part" = :part AND "color_id" = :color_id
-            """
-            sql.cursor.execute(check_query, {'part': part_num, 'color_id': color_id})
-            exists = sql.cursor.fetchone()[0] > 0
-
-            # Store image URL for downloading later
-            image_url = None
-
-            if not exists:
-                # Fetch full color information (with BrickLink mapping)
-                socket.auto_progress(message='Fetching color information')
-                full_color_info = IndividualPart.get_or_fetch_color(color_id)
-
-                # If we have pre-loaded color data, use it; otherwise fetch from Rebrickable
-                if color_data and part_name:
-                    # Use pre-loaded data from get_part_colors() response
-                    socket.auto_progress(message='Using cached part info')
-
-                    image_url = color_data.get('part_img_url', '')
-
-                    # Insert into rebrickable_parts using the pre-loaded data
-                    insert_part_query = """
-                        INSERT OR IGNORE INTO "rebrickable_parts" (
-                            "part", "color_id", "color_name", "color_rgb", "color_transparent",
-                            "bricklink_color_id", "bricklink_color_name",
-                            "name", "image", "url"
-                        ) VALUES (
-                            :part, :color_id, :color_name, :color_rgb, :color_transparent,
-                            :bricklink_color_id, :bricklink_color_name,
-                            :name, :image, :url
-                        )
-                    """
-                    sql.cursor.execute(insert_part_query, {
-                        'part': part_num,
-                        'color_id': color_id,
-                        'color_name': color_data.get('color_name', ''),
-                        'color_rgb': full_color_info.get('rgb') if full_color_info else None,
-                        'color_transparent': full_color_info.get('is_trans') if full_color_info else None,
-                        'bricklink_color_id': full_color_info.get('bricklink_color_id') if full_color_info else None,
-                        'bricklink_color_name': full_color_info.get('bricklink_color_name') if full_color_info else None,
-                        'name': part_name,
-                        'image': image_url,
-                        'url': f'https://rebrickable.com/parts/{part_num}/'
-                    })
-                else:
-                    # Fetch from Rebrickable (fallback for old workflow)
-                    socket.auto_progress(message='Fetching part info from Rebrickable')
-                    import rebrick
-                    import json
-
-                    # Initialize rebrick with API key
-                    rebrick.init(current_app.config['REBRICKABLE_API_KEY'])
-
-                    try:
-                        # Get part information
-                        part_info = json.loads(rebrick.lego.get_part(part_num).read())
-
-                        # Get color information (this also caches it in rebrickable_colors)
-                        # full_color_info already fetched above, but get again to be sure
-                        if not full_color_info:
-                            full_color_info = IndividualPart.get_or_fetch_color(color_id)
-
-                        # Get part+color specific info (for the image)
-                        part_color_info = json.loads(rebrick.lego.get_part_color(part_num, color_id).read())
-
-                        # Get image URL
-                        image_url = part_color_info.get('part_img_url', part_info.get('part_img_url', ''))
-
-                        # Insert into rebrickable_parts with BrickLink color info
-                        insert_part_query = """
-                            INSERT OR IGNORE INTO "rebrickable_parts" (
-                                "part", "color_id", "color_name", "color_rgb", "color_transparent",
-                                "bricklink_color_id", "bricklink_color_name",
-                                "name", "image", "url"
-                            ) VALUES (
-                                :part, :color_id, :color_name, :color_rgb, :color_transparent,
-                                :bricklink_color_id, :bricklink_color_name,
-                                :name, :image, :url
-                            )
-                        """
-                        sql.cursor.execute(insert_part_query, {
-                            'part': part_info['part_num'],
-                            'color_id': full_color_info['color_id'] if full_color_info else color_id,
-                            'color_name': full_color_info['name'] if full_color_info else '',
-                            'color_rgb': full_color_info['rgb'] if full_color_info else None,
-                            'color_transparent': full_color_info['is_trans'] if full_color_info else None,
-                            'bricklink_color_id': full_color_info.get('bricklink_color_id') if full_color_info else None,
-                            'bricklink_color_name': full_color_info.get('bricklink_color_name') if full_color_info else None,
-                            'name': part_info['name'],
-                            'image': image_url,
-                            'url': part_info['part_url']
-                        })
-
-                    except Exception as e:
-                        error_msg = str(e)
-
-                        # Provide helpful error message for printed/decorated parts
-                        if '404' in error_msg or 'Not Found' in error_msg:
-                            base_part = ''.join(c for c in part_num if c.isdigit())
-
-                            if base_part and base_part != part_num:
-                                raise ErrorException(
-                                    f'Part {part_num} with color {color_id} not found in Rebrickable. '
-                                    f'This appears to be a printed/decorated part. '
-                                    f'Try using the base part number: {base_part}'
-                                )
-                            else:
-                                raise ErrorException(
-                                    f'Part {part_num} with color {color_id} not found in Rebrickable. '
-                                    f'Please verify the part number is correct.'
-                                )
-                        else:
-                            raise ErrorException(
-                                f'Part {part_num} with color {color_id} not found in Rebrickable: {error_msg}'
-                            )
-            else:
-                # Part already exists in rebrickable_parts, get the image URL
-                sql.cursor.execute(
-                    'SELECT "image" FROM "rebrickable_parts" WHERE "part" = :part AND "color_id" = :color_id',
-                    {'part': part_num, 'color_id': color_id}
-                )
-                result = sql.cursor.fetchone()
-                if result and result[0]:
-                    image_url = result[0]
-
-            # Generate UUID and insert individual part
-            socket.auto_progress(message='Adding part to collection')
-            part_id = str(uuid4())
-
-            # Get storage and purchase location
-            storage = BrickSetStorageList.get(
-                data.get('storage', ''),
-                allow_none=True
-            )
-            purchase_location = BrickSetPurchaseLocationList.get(
-                data.get('purchase_location', ''),
-                allow_none=True
-            )
-
-            # Set fields
-            self.fields.id = part_id
-            self.fields.part = part_num
-            self.fields.color = color_id
-            self.fields.quantity = quantity
-            self.fields.missing = 0
-            self.fields.damaged = 0
-            self.fields.checked = 0
-            self.fields.description = data.get('description', '')
-            self.fields.storage = storage.fields.id if storage else None
-            self.fields.purchase_location = purchase_location.fields.id if purchase_location else None
-            self.fields.purchase_date = data.get('purchase_date', None)
-            self.fields.purchase_price = data.get('purchase_price', None)
-
-            # Insert into database
-            self.insert(commit=False, no_defer=True)
-
-            # Save owners
-            owners: list[str] = list(data.get('owners', []))
-            for owner_id in owners:
-                owner = BrickSetOwnerList.get(owner_id)
-                owner.update_individual_part_state(self, state=True)
-
-            # Save tags
-            tags: list[str] = list(data.get('tags', []))
-            for tag_id in tags:
-                tag = BrickSetTagList.get(tag_id)
-                tag.update_individual_part_state(self, state=True)
-
-            # Commit
-            sql.connection.commit()
-
-            # Download image if we have a URL
-            if image_url:
-                try:
-                    self.download_image(image_url)
-                except Exception as e:
-                    # Don't fail the whole operation if image download fails
-                    logger.warning(f'Could not download image for part {part_num} color {color_id}: {e}')
-
-            # Get color name for success message
-            color_name = 'Unknown'
-            if color_data and color_data.get('color_name'):
-                color_name = color_data.get('color_name')
-            elif full_color_info and full_color_info.get('name'):
-                color_name = full_color_info.get('name')
-
-            # Generate link to part details page
-            part_url = url_for('part.details', part=part_num, color=color_id)
-
-            socket.complete(
-                message=f'Successfully added part {part_num} in {color_name} (<a href="{part_url}">View details</a>)'
-            )
-            return True
-
-        except Exception as e:
-            error_msg = str(e)
-            if 'Individual parts system is disabled' in error_msg:
-                socket.fail(message=error_msg)
-            else:
-                socket.fail(
-                    message=f'Could not add individual part: {error_msg}'
-                )
-
-            if not isinstance(e, (NotFoundException, ErrorException)):
-                logger.debug(traceback.format_exc())
-
-            return False
-
-    # Update a field
-    def update_field(self, field: str, value: Any, /) -> Self:
-        setattr(self.fields, field, value)
-
-        # Use a specific update query for each field
-        sql = BrickSQL()
-        update_query = f"""
-            UPDATE "bricktracker_individual_parts"
-            SET "{field}" = :value
-            WHERE "id" = :id
-        """
-        sql.cursor.execute(update_query, {
-            'id': self.fields.id,
-            'value': value
-        })
-        sql.commit()
-
-        return self
-
-    # Update problem count (missing/damaged)
-    def update_problem(self, problem: str, data: dict[str, Any], /) -> int:
-        # Handle both 'value' key and 'amount' key
-        amount: str | int = data.get('value', data.get('amount', ''))  # type: ignore
-
-        # We need a positive integer
-        try:
-            if amount == '':
-                amount = 0
-
-            amount = int(amount)
-
-            if amount < 0:
-                amount = 0
-        except Exception:
-            raise ErrorException(f'"{amount}" is not a valid integer')
-
-        if amount < 0:
-            raise ErrorException('Cannot set a negative amount')
-
-        setattr(self.fields, problem, amount)
-
-        BrickSQL().execute_and_commit(
-            f'individual_part/update/{problem}',
-            parameters={
-                'id': self.fields.id,
-                problem: amount
-            }
-        )
-
-        return amount
-
-    # Update checked status
-    def update_checked(self, data: dict[str, Any], /) -> bool:
-        # Handle both direct 'checked' key and changer.js 'value' key format
-        if data:
-            checked = data.get('checked', data.get('value', False))
-        else:
-            checked = False
-
-        checked = bool(checked)
-        self.fields.checked = 1 if checked else 0
-
-        BrickSQL().execute_and_commit(
-            'individual_part/update/checked',
-            parameters={
-                'id': self.fields.id,
-                'checked': self.fields.checked
-            }
-        )
-
-        return checked
-
-    # URL methods
-    def url(self, /) -> str:
-        return url_for('individual_part.details', id=self.fields.id)
-
-    def url_for_quantity(self, /) -> str:
-        return url_for('individual_part.update_quantity', id=self.fields.id)
-
-    def url_for_description(self, /) -> str:
-        return url_for('individual_part.update_description', id=self.fields.id)
-
-    def url_for_problem(self, problem: str, /) -> str:
-        if problem == 'missing':
-            return url_for('individual_part.update_missing', id=self.fields.id)
-        elif problem == 'damaged':
-            return url_for('individual_part.update_damaged', id=self.fields.id)
-        return ''
-
-    def url_for_checked(self, /) -> str:
-        return url_for('individual_part.update_checked', id=self.fields.id)
-
-    def url_for_delete(self, /) -> str:
-        return url_for('individual_part.delete_part', id=self.fields.id)
-
-    def url_for_image(self, /) -> str:
-        # Check if we should use remote images
-        if current_app.config.get('USE_REMOTE_IMAGES', False):
-            # Return remote URL directly
-            if hasattr(self.fields, 'image') and self.fields.image:
-                return self.fields.image
-            else:
-                return current_app.config.get('REBRICKABLE_IMAGE_NIL', '')
-        else:
-            # Use local images
-            from .rebrickable_image import RebrickableImage
-
-            if hasattr(self.fields, 'image') and self.fields.image:
-                # Extract image_id from URL
-                image_id, _ = os.path.splitext(os.path.basename(urlparse(self.fields.image).path))
-
-                if image_id:
-                    # Return local static URL using RebrickableImage helper
-                    return RebrickableImage.static_url(image_id, 'PARTS_FOLDER')
-
-            # Fallback to nil image
-            return RebrickableImage.static_url(RebrickableImage.nil_name(), 'PARTS_FOLDER')

bricktracker/individual_part_list.py

@@ -1,93 +0,0 @@
-import logging
-from typing import Self, TYPE_CHECKING
-
-from .record_list import BrickRecordList
-from .individual_part import IndividualPart
-
-if TYPE_CHECKING:
-    from .set_storage import BrickSetStorage
-
-logger = logging.getLogger(__name__)
-
-
-# List of individual parts
-class IndividualPartList(BrickRecordList):
-    # Queries
-    list_query: str = 'individual_part/list/all'
-    by_part_query: str = 'individual_part/list/by_part'
-    by_color_query: str = 'individual_part/list/by_color'
-    by_part_and_color_query: str = 'individual_part/list/by_part_and_color'
-    by_storage_query: str = 'individual_part/list/by_storage'
-    using_storage_query: str = 'individual_part/list/using_storage'
-    without_storage_query: str = 'individual_part/list/without_storage'
-    problem_query: str = 'individual_part/list/problem'
-
-    # Get all individual parts
-    def all(self, /) -> Self:
-        self.list(override_query=self.list_query)
-        return self
-
-    # Get individual parts by part number
-    def by_part(self, part: str, /) -> Self:
-        self.fields.part = part
-        self.list(override_query=self.by_part_query)
-        return self
-
-    # Get individual parts by color
-    def by_color(self, color_id: int, /) -> Self:
-        self.fields.color = color_id
-        self.list(override_query=self.by_color_query)
-        return self
-
-    # Get individual parts by part number and color
-    def by_part_and_color(self, part: str, color_id: int, /) -> Self:
-        self.fields.part = part
-        self.fields.color = color_id
-        self.list(override_query=self.by_part_and_color_query)
-        return self
-
-    # Get individual parts by storage location
-    def by_storage(self, storage: 'BrickSetStorage', /) -> Self:
-        self.fields.storage = storage.fields.id
-        self.list(override_query=self.by_storage_query)
-        return self
-
-    # Get individual parts using a specific storage location
-    def using_storage(self, storage: 'BrickSetStorage', /) -> Self:
-        self.fields.storage = storage.fields.id
-        self.list(override_query=self.using_storage_query)
-        return self
-
-    # Get individual parts without storage
-    def without_storage(self, /) -> Self:
-        self.list(override_query=self.without_storage_query)
-        return self
-
-    # Get individual parts with problems (missing or damaged)
-    def with_problems(self, /) -> Self:
-        self.list(override_query=self.problem_query)
-        return self
-
-    # Base individual part list
-    def list(
-        self,
-        /,
-        *,
-        override_query: str | None = None,
-        order: str | None = None,
-        limit: int | None = None,
-        **context,
-    ) -> None:
-        # Load the individual parts from the database
-        for record in super().select(
-            override_query=override_query,
-            order=order,
-            limit=limit,
-            **context
-        ):
-            individual_part = IndividualPart(record=record)
-            self.records.append(individual_part)
-
-    # Set the record class
-    def set_record_class(self, /) -> None:
-        self.record_class = IndividualPart

bricktracker/individual_part_lot.py

@@ -1,340 +0,0 @@
-import logging
-import traceback
-from typing import Any, Self, TYPE_CHECKING
-from uuid import uuid4
-
-from flask import url_for
-
-from .exceptions import NotFoundException, DatabaseException, ErrorException
-from .individual_part import IndividualPart
-from .record import BrickRecord, format_timestamp
-from .set_owner_list import BrickSetOwnerList
-from .set_purchase_location_list import BrickSetPurchaseLocationList
-from .set_storage_list import BrickSetStorageList
-from .set_tag_list import BrickSetTagList
-from .sql import BrickSQL
-
-if TYPE_CHECKING:
-    from .socket import BrickSocket
-
-logger = logging.getLogger(__name__)
-
-
-# Individual part lot (collection/batch of individual parts added together)
-class IndividualPartLot(BrickRecord):
-    # Queries
-    select_query: str = 'individual_part_lot/select/by_id'
-    insert_query: str = 'individual_part_lot/insert'
-
-    def __init__(
-        self,
-        /,
-        *,
-        record: Any | None = None
-    ):
-        super().__init__()
-
-        # Ingest the record if it has one
-        if record is not None:
-            self.ingest(record)
-
-    # Select a specific lot by UUID
-    def select_by_id(self, id: str, /) -> Self:
-        self.fields.id = id
-        if not self.select(override_query=self.select_query):
-            raise NotFoundException(
-                'Individual part lot with id "{id}" not found'.format(id=id)
-            )
-        return self
-
-    # Delete a lot and all its parts
-    def delete(self, /) -> None:
-        BrickSQL().executescript(
-            'individual_part_lot/delete',
-            id=self.fields.id
-        )
-
-    # Get the URL for this lot
-    def url(self, /) -> str:
-        return url_for('individual_part.lot_details', lot_id=self.fields.id)
-
-    # String representation for debugging
-    def __repr__(self, /) -> str:
-        """String representation for debugging"""
-        name = getattr(self.fields, 'name', 'Unnamed') or 'Unnamed'
-        lot_id = getattr(self.fields, 'id', 'unknown')
-        # Try to get part_count if available (from optimized query)
-        part_count = getattr(self.fields, 'part_count', '?')
-        return f'<IndividualPartLot "{name}" ({part_count} parts) id:{lot_id[:8]}...>'
-
-    # Format created date
-    def created_date_formatted(self, /) -> str:
-        """Format the created date for display"""
-        return format_timestamp(self.fields.created_date)
-
-    # Format purchase date
-    def purchase_date_formatted(self, /) -> str:
-        """Format the purchase date for display"""
-        return format_timestamp(self.fields.purchase_date)
-
-    # Get all parts in this lot
-    def parts(self, /) -> list['IndividualPart']:
-        """Get all individual parts that belong to this lot"""
-        sql = BrickSQL()
-        parts_data = sql.fetchall('individual_part_lot/list/parts', lot_id=self.fields.id)
-
-        # Convert to list of IndividualPart objects using ingest()
-        return [IndividualPart(record=record) for record in parts_data]
-
-    # Create a new lot with parts from cart
-    def create(self, socket: 'BrickSocket', data: dict[str, Any], /) -> bool:
-        """
-        Create a new individual part lot with multiple parts.
-
-        Expected data format:
-        {
-            'cart': [
-                {
-                    'part': '3001',
-                    'part_name': 'Brick 2 x 4',
-                    'color_id': 1,
-                    'color_name': 'White',
-                    'quantity': 10,
-                    'color_info': {...}
-                },
-                ...
-            ],
-            'name': 'Optional lot name',
-            'description': 'Optional lot description',
-            'storage': 'storage_id',
-            'purchase_location': 'purchase_location_id',
-            'purchase_date': timestamp,
-            'purchase_price': 0.0,
-            'owners': ['owner_id1', ...],
-            'tags': ['tag_id1', ...]
-        }
-        """
-        try:
-            # Validate cart data
-            cart = data.get('cart', [])
-            if not cart or not isinstance(cart, list):
-                raise ErrorException('Cart is empty or invalid')
-
-            socket.auto_progress(
-                message=f'Creating lot with {len(cart)} parts',
-                increment_total=True
-            )
-
-            # Generate UUID for the lot
-            lot_id = str(uuid4())
-            self.fields.id = lot_id
-
-            # Set lot metadata
-            self.fields.name = data.get('name', None)
-            self.fields.description = data.get('description', None)
-            self.fields.created_date = datetime.now().timestamp()
-
-            # Get storage
-            storage = BrickSetStorageList.get(
-                data.get('storage', ''),
-                allow_none=True
-            )
-            self.fields.storage = storage.fields.id if storage else None
-
-            # Get purchase location
-            purchase_location = BrickSetPurchaseLocationList.get(
-                data.get('purchase_location', ''),
-                allow_none=True
-            )
-            self.fields.purchase_location = purchase_location.fields.id if purchase_location else None
-
-            # Set purchase info
-            self.fields.purchase_date = data.get('purchase_date', None)
-            self.fields.purchase_price = data.get('purchase_price', None)
-
-            # Insert the lot record
-            socket.auto_progress(
-                message='Inserting lot into database',
-                increment_total=True
-            )
-            self.insert(commit=False)
-
-            # Save owners
-            owners: list[str] = list(data.get('owners', []))
-            for owner_id in owners:
-                owner = BrickSetOwnerList.get(owner_id)
-                # Insert into junction table
-                sql = BrickSQL()
-                sql.cursor.execute(
-                    'INSERT INTO "bricktracker_individual_part_lot_owners" ("id") VALUES (:id)',
-                    {'id': lot_id}
-                )
-
-            # Save tags
-            tags: list[str] = list(data.get('tags', []))
-            for tag_id in tags:
-                tag = BrickSetTagList.get(tag_id)
-                # Insert into junction table
-                sql = BrickSQL()
-                sql.cursor.execute(
-                    'INSERT INTO "bricktracker_individual_part_lot_tags" ("id") VALUES (:id)',
-                    {'id': lot_id}
-                )
-
-            # Add all parts from cart
-            socket.auto_progress(
-                message=f'Adding {len(cart)} parts to lot',
-                increment_total=True
-            )
-
-            for idx, cart_item in enumerate(cart):
-                part_num = cart_item.get('part')
-                color_id = cart_item.get('color_id')
-                quantity = cart_item.get('quantity', 1)
-                color_info = cart_item.get('color_info', {})
-
-                socket.auto_progress(
-                    message=f'Adding part {idx + 1}/{len(cart)}: {part_num} in {cart_item.get("color_name", "unknown color")}',
-                    increment_total=True
-                )
-
-                # Create individual part with lot_id
-                part_uuid = str(uuid4())
-
-                # Use the add method but with lot_id
-                # We need to insert the part with the lot_id
-                sql = BrickSQL()
-
-                # First ensure the part exists in rebrickable_parts
-                IndividualPart.get_or_fetch_color(color_id)
-
-                # Insert the part with lot_id (NO individual metadata - inherited from lot)
-                insert_query = """
-                    INSERT INTO "bricktracker_individual_parts" (
-                        "id",
-                        "part",
-                        "color",
-                        "quantity",
-                        "missing",
-                        "damaged",
-                        "checked",
-                        "description",
-                        "storage",
-                        "purchase_location",
-                        "purchase_date",
-                        "purchase_price",
-                        "lot_id"
-                    ) VALUES (
-                        :id,
-                        :part,
-                        :color,
-                        :quantity,
-                        0,
-                        0,
-                        0,
-                        NULL,
-                        NULL,
-                        NULL,
-                        NULL,
-                        NULL,
-                        :lot_id
-                    )
-                """
-
-                sql.cursor.execute(insert_query, {
-                    'id': part_uuid,
-                    'part': part_num,
-                    'color': color_id,
-                    'quantity': quantity,
-                    'lot_id': lot_id
-                })
-
-                # Ensure part data is in rebrickable_parts
-                try:
-                    # Check if part exists
-                    check_query = """
-                        SELECT COUNT(*) FROM "rebrickable_parts"
-                        WHERE "part" = :part AND "color_id" = :color_id
-                    """
-                    sql.cursor.execute(check_query, {'part': part_num, 'color_id': color_id})
-                    exists = sql.cursor.fetchone()[0] > 0
-
-                    if not exists:
-                        # Insert part data
-                        part_name = cart_item.get('part_name', '')
-                        color_name = cart_item.get('color_name', '')
-
-                        insert_part_query = """
-                            INSERT OR IGNORE INTO "rebrickable_parts" (
-                                "part",
-                                "name",
-                                "color_id",
-                                "color_name",
-                                "color_rgb",
-                                "color_transparent",
-                                "image",
-                                "url",
-                                "bricklink_color_id",
-                                "bricklink_color_name"
-                            ) VALUES (
-                                :part,
-                                :name,
-                                :color_id,
-                                :color_name,
-                                :color_rgb,
-                                :color_transparent,
-                                :image,
-                                :url,
-                                :bricklink_color_id,
-                                :bricklink_color_name
-                            )
-                        """
-
-                        sql.cursor.execute(insert_part_query, {
-                            'part': part_num,
-                            'name': part_name,
-                            'color_id': color_id,
-                            'color_name': color_name,
-                            'color_rgb': color_info.get('rgb', ''),
-                            'color_transparent': color_info.get('is_trans', False),
-                            'image': color_info.get('part_img_url', ''),
-                            'url': f'https://rebrickable.com/parts/{part_num}/',
-                            'bricklink_color_id': color_info.get('bricklink_color_id', None),
-                            'bricklink_color_name': color_info.get('bricklink_color_name', None)
-                        })
-                except Exception as e:
-                    logger.warning(f'Could not ensure part data for {part_num}/{color_id}: {e}')
-
-            # Commit all changes
-            socket.auto_progress(
-                message='Committing changes to database',
-                increment_total=True
-            )
-            sql.commit()
-
-            socket.auto_progress(
-                message=f'Lot created successfully with {len(cart)} parts',
-                increment_total=True
-            )
-
-            # Complete with success message and lot URL
-            lot_url = self.url()
-            socket.complete(
-                message=f'Successfully created lot with {len(cart)} parts. <a href="{lot_url}">View lot</a>',
-                data={
-                    'lot_id': lot_id,
-                    'lot_url': lot_url
-                }
-            )
-
-            return True
-
-        except ErrorException as e:
-            socket.fail(message=str(e))
-            logger.error(f'Error creating lot: {e}')
-            return False
-        except Exception as e:
-            socket.fail(message=f'Unexpected error creating lot: {str(e)}')
-            logger.error(f'Unexpected error creating lot: {e}')
-            logger.error(traceback.format_exc())
-            return False

bricktracker/individual_part_lot_list.py

@@ -1,45 +0,0 @@
-import logging
-from typing import Self, TYPE_CHECKING
-
-from .record_list import BrickRecordList
-from .individual_part_lot import IndividualPartLot
-
-if TYPE_CHECKING:
-    from .set_storage import BrickSetStorage
-
-logger = logging.getLogger(__name__)
-
-
-# List of individual part lots
-class IndividualPartLotList(BrickRecordList):
-    # Queries
-    list_query: str = 'individual_part_lot/list/all'
-
-    # Get all individual part lots
-    def all(self, /) -> Self:
-        self.list(override_query=self.list_query)
-        return self
-
-    # Base individual part lot list
-    def list(
-        self,
-        /,
-        *,
-        override_query: str | None = None,
-        order: str | None = None,
-        limit: int | None = None,
-        **context,
-    ) -> None:
-        # Load the individual part lots from the database
-        for record in super().select(
-            override_query=override_query,
-            order=order,
-            limit=limit,
-            **context
-        ):
-            lot = IndividualPartLot(record=record)
-            self.records.append(lot)
-
-    # Set the record class
-    def set_record_class(self, /) -> None:
-        self.record_class = IndividualPartLot

bricktracker/instructions.py

@@ -13,7 +13,6 @@ import requests
 from werkzeug.datastructures import FileStorage
 from werkzeug.utils import secure_filename
 import re
-import cloudscraper
 
 from .exceptions import ErrorException, DownloadException
 if TYPE_CHECKING:
@@ -106,12 +105,34 @@ class BrickInstructions(object):
                     message=f'File {self.filename} already exists, skipped - <a href="{pdf_url}" target="_blank" class="btn btn-sm btn-primary ms-2"><i class="ri-external-link-line"></i> Open PDF</a>'
                 )
 
-            # Fetch PDF via cloudscraper (to bypass Cloudflare)
-            scraper = cloudscraper.create_scraper()
-            scraper.headers.update({
-                "User-Agent": current_app.config['REBRICKABLE_USER_AGENT']
+            # Use plain requests instead of cloudscraper
+            session = requests.Session()
+            session.headers.update({
+                'User-Agent': current_app.config['REBRICKABLE_USER_AGENT'],
+                'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,*/*;q=0.8',
+                'Accept-Language': 'en-US,en;q=0.5',
+                'DNT': '1',
+                'Connection': 'keep-alive',
+                'Upgrade-Insecure-Requests': '1',
+                'Sec-Fetch-Dest': 'document',
+                'Sec-Fetch-Mode': 'navigate',
+                'Sec-Fetch-Site': 'same-origin',
+                'Cache-Control': 'max-age=0'
             })
-            resp = scraper.get(path, stream=True)
+
+            # Visit the set's instructions listing page first to establish session cookies
+            set_number = None
+            if self.rebrickable:
+                set_number = self.rebrickable.fields.set
+            elif self.set:
+                set_number = self.set
+
+            if set_number:
+                instructions_page = f"https://rebrickable.com/instructions/{set_number}/"
+                session.get(instructions_page)
+                session.headers.update({"Referer": instructions_page})
+
+            resp = session.get(path, stream=True, allow_redirects=True)
             if not resp.ok:
                 raise DownloadException(f"Failed to download: HTTP {resp.status_code}")
 
@@ -172,11 +193,16 @@ class BrickInstructions(object):
         if filename is None:
             filename = self.filename
 
-        return os.path.join(
-            current_app.static_folder,  # type: ignore
-            current_app.config['INSTRUCTIONS_FOLDER'],
-            filename
-        )
+        folder = current_app.config['INSTRUCTIONS_FOLDER']
+
+        # If folder is absolute, use it directly
+        # Otherwise, make it relative to app root (not static folder)
+        if os.path.isabs(folder):
+            base_path = folder
+        else:
+            base_path = os.path.join(current_app.root_path, folder)
+
+        return os.path.join(base_path, filename)
 
     # Rename an instructions file
     def rename(self, filename: str, /) -> None:
@@ -217,10 +243,16 @@ class BrickInstructions(object):
 
         folder: str = current_app.config['INSTRUCTIONS_FOLDER']
 
-        # Compute the path
-        path = os.path.join(folder, self.filename)
-
-        return url_for('static', filename=path)
+        # Determine which route to use based on folder path
+        # If folder contains 'data' (new structure), use data route
+        # Otherwise use static route (legacy)
+        if 'data' in folder:
+            return url_for('data.serve_data_file', folder='instructions', filename=self.filename)
+        else:
+            # Legacy: folder is relative to static/
+            folder_clean = folder.removeprefix('static/')
+            path = os.path.join(folder_clean, self.filename)
+            return url_for('static', filename=path)
 
     # Return the icon depending on the extension
     def icon(self, /) -> str:
@@ -237,20 +269,33 @@ class BrickInstructions(object):
     @staticmethod
     def find_instructions(set: str, /) -> list[Tuple[str, str]]:
         """
-        Scrape Rebrickable’s HTML and return a list of
+        Scrape Rebrickable's HTML and return a list of
         (filename_slug, download_url). Duplicate slugs get _1, _2, …
         """
         page_url = f"https://rebrickable.com/instructions/{set}/"
         logger.debug(f"[find_instructions] fetching HTML from {page_url!r}")
 
-        # Solve Cloudflare’s challenge
-        scraper = cloudscraper.create_scraper()
-        scraper.headers.update({'User-Agent': current_app.config['REBRICKABLE_USER_AGENT']})
-        resp = scraper.get(page_url)
+        # Use plain requests instead of cloudscraper
+        session = requests.Session()
+        session.headers.update({
+            'User-Agent': current_app.config['REBRICKABLE_USER_AGENT'],
+            'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,*/*;q=0.8',
+            'Accept-Language': 'en-US,en;q=0.5',
+            'DNT': '1',
+            'Connection': 'keep-alive',
+            'Upgrade-Insecure-Requests': '1',
+            'Sec-Fetch-Dest': 'document',
+            'Sec-Fetch-Mode': 'navigate',
+            'Sec-Fetch-Site': 'none',
+            'Cache-Control': 'max-age=0'
+        })
+
+        resp = session.get(page_url)
         if not resp.ok:
             raise ErrorException(f'Failed to load instructions page for {set}. HTTP {resp.status_code}')
 
         soup = BeautifulSoup(resp.content, 'html.parser')
+        # Match download links with or without query parameters (e.g., ?cfe=timestamp&cfk=key)
         link_re = re.compile(r'^/instructions/\d+/.+/download/')
 
         raw: list[tuple[str, str]] = []
@@ -263,8 +308,10 @@ class BrickInstructions(object):
             alt_text = img['alt'].removeprefix('LEGO Building Instructions for ') # type: ignore
             slug = re.sub(r'[^A-Za-z0-9]+', '-', alt_text).strip('-')
 
-            # Build the absolute download URL
+            # Build the absolute download URL - this preserves query parameters
+            # BeautifulSoup's a['href'] includes the full href with ?cfe=...&cfk=... params
             download_url = urljoin('https://rebrickable.com', a['href']) # type: ignore
+            logger.debug(f"[find_instructions] Found download link: {download_url}")
             raw.append((slug, download_url))
 
         if not raw:

bricktracker/instructions_list.py

@@ -36,11 +36,14 @@ class BrickInstructionsList(object):
 
             # Try to list the files in the instruction folder
             try:
-                # Make a folder relative to static
-                folder: str = os.path.join(
-                    current_app.static_folder,  # type: ignore
-                    current_app.config['INSTRUCTIONS_FOLDER'],
-                )
+                folder_config: str = current_app.config['INSTRUCTIONS_FOLDER']
+
+                # If folder is absolute, use it directly
+                # Otherwise, make it relative to app root (not static folder)
+                if os.path.isabs(folder_config):
+                    folder = folder_config
+                else:
+                    folder = os.path.join(current_app.root_path, folder_config)
 
                 for file in os.scandir(folder):
                     instruction = BrickInstructions(file)

bricktracker/metadata.py

@@ -9,8 +9,6 @@ from .exceptions import DatabaseException, ErrorException, NotFoundException
 from .record import BrickRecord
 from .sql import BrickSQL
 if TYPE_CHECKING:
-    from .individual_minifigure import IndividualMinifigure
-    from .individual_part import IndividualPart
     from .set import BrickSet
 
 logger = logging.getLogger(__name__)
@@ -20,24 +18,16 @@ logger = logging.getLogger(__name__)
 class BrickMetadata(BrickRecord):
     kind: str
 
-    # Endpoints (optional, not all metadata types use all of these)
-    set_state_endpoint: str = ''
-    individual_minifigure_state_endpoint: str = ''
-    individual_minifigure_value_endpoint: str = ''
-    individual_part_state_endpoint: str = ''
-    individual_part_value_endpoint: str = ''
+    # Set state endpoint
+    set_state_endpoint: str
 
     # Queries
     delete_query: str
     insert_query: str
     select_query: str
     update_field_query: str
-    update_set_state_query: str = ''
-    update_set_value_query: str = ''
-    update_individual_minifigure_state_query: str = ''
-    update_individual_minifigure_value_query: str = ''
-    update_individual_part_state_query: str = ''
-    update_individual_part_value_query: str = ''
+    update_set_state_query: str
+    update_set_value_query: str
 
     def __init__(
         self,
@@ -116,36 +106,6 @@ class BrickMetadata(BrickRecord):
             metadata_id=self.fields.id
         )
 
-    # URL to change the selected state of this metadata item for an individual minifigure
-    def url_for_individual_minifigure_state(self, id: str, /) -> str:
-        return url_for(
-            self.individual_minifigure_state_endpoint,
-            id=id,
-            metadata_id=self.fields.id
-        )
-
-    # URL to change the value for an individual minifigure
-    def url_for_individual_minifigure_value(self, id: str, /) -> str:
-        return url_for(
-            self.individual_minifigure_value_endpoint,
-            id=id
-        )
-
-    # URL to change the selected state of this metadata item for an individual part
-    def url_for_individual_part_state(self, id: str, /) -> str:
-        return url_for(
-            self.individual_part_state_endpoint,
-            id=id,
-            metadata_id=self.fields.id
-        )
-
-    # URL to change the value for an individual part
-    def url_for_individual_part_value(self, id: str, /) -> str:
-        return url_for(
-            self.individual_part_value_endpoint,
-            id=id
-        )
-
     # Select a specific metadata (with an id)
     def select_specific(self, id: str, /) -> Self:
         # Save the parameters to the fields
@@ -215,40 +175,6 @@ class BrickMetadata(BrickRecord):
 
         return value
 
-    # Generic method to update state for any entity type
-    def _update_entity_state(
-        self,
-        entity_type: str,
-        entity_id: str,
-        entity_name: str,
-        query: str,
-        /,
-        *,
-        json: Any | None = None,
-        state: Any | None = None
-    ) -> Any:
-        """Generic state update logic for sets, minifigures, and parts"""
-        if state is None and json is not None:
-            state = json.get('value', False)
-
-        parameters = self.sql_parameters()
-        parameters['id'] = entity_id
-        parameters['state'] = state
-
-        rows, _ = BrickSQL().execute_and_commit(
-            query,
-            parameters=parameters,
-            name=self.as_column(),
-        )
-
-        if rows != 1:
-            raise DatabaseException(f'Could not update the {self.kind} "{self.fields.name}" state for {entity_type} {entity_name} ({entity_id})')
-
-        # Info
-        logger.info(f'{self.kind.capitalize()} "{self.fields.name}" state changed to "{state}" for {entity_type} {entity_name} ({entity_id})')
-
-        return state
-
     # Update the selected state of this metadata item for a set
     def update_set_state(
         self,
@@ -258,86 +184,33 @@ class BrickMetadata(BrickRecord):
         json: Any | None = None,
         state: Any | None = None
     ) -> Any:
-        return self._update_entity_state(
-            'set',
-            brickset.fields.id,
-            brickset.fields.set,
+        if state is None and json is not None:
+            state = json.get('value', False)
+
+        parameters = self.sql_parameters()
+        parameters['set_id'] = brickset.fields.id
+        parameters['state'] = state
+
+        rows, _ = BrickSQL().execute(
             self.update_set_state_query,
-            json=json,
-            state=state
+            parameters=parameters,
+            defer=True,
+            name=self.as_column(),
         )
 
-    # Check if this metadata has a specific individual minifigure
-    def has_individual_minifigure(
-        self,
-        individual_minifigure: 'IndividualMinifigure',
-        /,
-    ) -> bool:
-        """Check if this owner/tag/status is assigned to a individual minifigure"""
-        # Determine the table name based on metadata type
-        table_name = f'bricktracker_individual_minifigure_{self.kind}s'
-        column_name = f'{self.kind}_{self.fields.id}'
+        # Note: rows will be -1 when deferred, so we can't validate here
+        # Validation will happen at final commit in set.py
 
-        # Query to check if the relationship exists using raw SQL
-        sql = BrickSQL()
-        query = f'SELECT COUNT(*) as count FROM "{table_name}" WHERE "id" = ? AND "{column_name}" = 1'
-        result = sql.cursor.execute(query, (individual_minifigure.fields.id,)).fetchone()
+        # Info
+        logger.info('{kind} "{name}" state changed to "{state}" for set {set} ({id})'.format(  # noqa: E501
+            kind=self.kind,
+            name=self.fields.name,
+            state=state,
+            set=brickset.fields.set,
+            id=brickset.fields.id,
+        ))
 
-        return result and result['count'] > 0
-
-    # Update the selected state of this metadata item for a individual minifigure
-    def update_individual_minifigure_state(
-        self,
-        individual_minifigure: 'IndividualMinifigure',
-        /,
-        *,
-        json: Any | None = None,
-        state: Any | None = None
-    ) -> Any:
-        return self._update_entity_state(
-            'individual minifigure',
-            individual_minifigure.fields.id,
-            individual_minifigure.fields.figure,
-            self.update_individual_minifigure_state_query,
-            json=json,
-            state=state
-        )
-
-    # Check if this metadata has a specific individual part
-    def has_individual_part(
-        self,
-        individual_part: 'IndividualPart',
-        /,
-    ) -> bool:
-        """Check if this owner/tag/status is assigned to an individual part"""
-        # Determine the table name based on metadata type
-        table_name = f'bricktracker_individual_part_{self.kind}s'
-        column_name = f'{self.kind}_{self.fields.id}'
-
-        # Query to check if the relationship exists using raw SQL
-        sql = BrickSQL()
-        query = f'SELECT COUNT(*) as count FROM "{table_name}" WHERE "id" = ? AND "{column_name}" = 1'
-        result = sql.cursor.execute(query, (individual_part.fields.id,)).fetchone()
-
-        return result and result['count'] > 0
-
-    # Update the selected state of this metadata item for an individual part
-    def update_individual_part_state(
-        self,
-        individual_part: 'IndividualPart',
-        /,
-        *,
-        json: Any | None = None,
-        state: Any | None = None
-    ) -> Any:
-        return self._update_entity_state(
-            'individual part',
-            individual_part.fields.id,
-            f'{individual_part.fields.part} color {individual_part.fields.color}',
-            self.update_individual_part_state_query,
-            json=json,
-            state=state
-        )
+        return state
 
     # Update the selected value of this metadata item for a set
     def update_set_value(

bricktracker/metadata_list.py

@@ -39,10 +39,9 @@ class BrickMetadataList(BrickRecordList[T]):
     # Queries
     select_query: str
 
-    # List-specific endpoints (for operations on the list itself)
-    set_state_endpoint: str = ''
-    set_value_endpoint: str = ''
-    individual_minifigure_value_endpoint: str = ''
+    # Set endpoints
+    set_state_endpoint: str
+    set_value_endpoint: str
 
     def __init__(
         self,
@@ -100,15 +99,12 @@ class BrickMetadataList(BrickRecordList[T]):
 
     # Return the items as columns for a select
     @classmethod
-    def as_columns(cls, /, table: str | None = None, **kwargs) -> str:
+    def as_columns(cls, /, **kwargs) -> str:
         new = cls.new()
 
-        # Use provided table name or default to class table
-        table_name = table if table is not None else cls.table
-
         return ', '.join([
             '"{table}"."{column}"'.format(
-                table=table_name,
+                table=cls.table,
                 column=record.as_column(),
             )
             for record
@@ -188,11 +184,3 @@ class BrickMetadataList(BrickRecordList[T]):
             cls.set_value_endpoint,
             id=id,
         )
-
-    # URL to change the selected value of this metadata item for an individual minifigure
-    @classmethod
-    def url_for_individual_minifigure_value(cls, id: str, /) -> str:
-        return url_for(
-            cls.individual_minifigure_value_endpoint,
-            id=id,
-        )

bricktracker/minifigure_list.py

@@ -43,6 +43,19 @@ class BrickMinifigureList(BrickRecordList[BrickMinifigure]):
 
         return self
 
+    # Load all minifigures with problems filter
+    def all_filtered(self, /, problems_filter: str = 'all', theme_id: str = 'all', year: str = 'all') -> Self:
+        context = {}
+        if problems_filter and problems_filter != 'all':
+            context['problems_filter'] = problems_filter
+        if theme_id and theme_id != 'all':
+            context['theme_id'] = theme_id
+        if year and year != 'all':
+            context['year'] = year
+
+        self.list(override_query=self.all_query, **context)
+        return self
+
     # Load all minifigures by owner
     def all_by_owner(self, owner_id: str | None = None, /) -> Self:
         # Save the owner_id parameter
@@ -53,10 +66,31 @@ class BrickMinifigureList(BrickRecordList[BrickMinifigure]):
 
         return self
 
+    # Load all minifigures by owner with problems filter
+    def all_by_owner_filtered(self, /, owner_id: str | None = None, problems_filter: str = 'all', theme_id: str = 'all', year: str = 'all') -> Self:
+        # Save the owner_id parameter
+        self.fields.owner_id = owner_id
+
+        context = {}
+        if problems_filter and problems_filter != 'all':
+            context['problems_filter'] = problems_filter
+        if theme_id and theme_id != 'all':
+            context['theme_id'] = theme_id
+        if year and year != 'all':
+            context['year'] = year
+
+        # Load the minifigures from the database
+        self.list(override_query=self.all_by_owner_query, **context)
+
+        return self
+
     # Load minifigures with pagination support
     def all_filtered_paginated(
         self,
         owner_id: str | None = None,
+        problems_filter: str = 'all',
+        theme_id: str = 'all',
+        year: str = 'all',
         search_query: str | None = None,
         page: int = 1,
         per_page: int = 50,
@@ -74,15 +108,23 @@ class BrickMinifigureList(BrickRecordList[BrickMinifigure]):
         if search_query:
             filter_context['search_query'] = search_query
 
+        if problems_filter and problems_filter != 'all':
+            filter_context['problems_filter'] = problems_filter
+
+        if theme_id and theme_id != 'all':
+            filter_context['theme_id'] = theme_id
+
+        if year and year != 'all':
+            filter_context['year'] = year
+
         # Field mapping for sorting
         field_mapping = {
-            'name': '"combined"."name"',
-            'parts': '"combined"."number_of_parts"',
+            'name': '"rebrickable_minifigures"."name"',
+            'parts': '"rebrickable_minifigures"."number_of_parts"',
             'quantity': '"total_quantity"',
             'missing': '"total_missing"',
             'damaged': '"total_damaged"',
-            'sets': '"total_sets"',
-            'individual': '"total_individual"'
+            'sets': '"total_sets"'
         }
 
         # Use the base pagination method
@@ -113,7 +155,7 @@ class BrickMinifigureList(BrickRecordList[BrickMinifigure]):
         if current_app.config['RANDOM']:
             order = 'RANDOM()'
         else:
-            order = '"combined"."rowid" DESC'
+            order = '"bricktracker_minifigures"."rowid" DESC'
 
         self.list(override_query=self.last_query, order=order, limit=limit)
 

bricktracker/parser.py

@@ -5,58 +5,29 @@ from .exceptions import ErrorException
 def parse_set(set: str, /) -> str:
     number, _, version = set.partition('-')
 
-    # Making sure both are integers
+    # Set number can be alphanumeric (e.g., "McDR6US", "10312", "COMCON035")
+    # Just validate it's not empty
+    if not number or number.strip() == '':
+        raise ErrorException('Set number cannot be empty')
+
+    # Clean up the number (trim whitespace)
+    number = number.strip()
+
+    # Version defaults to 1 if not provided
     if version == '':
-        version = 1
+        version = '1'
 
+    # Version must be a positive integer
     try:
-        number = int(number)
-    except Exception:
-        raise ErrorException('Number "{number}" is not a number'.format(
-            number=number,
-        ))
-
-    try:
-        version = int(version)
+        version_int = int(version)
     except Exception:
         raise ErrorException('Version "{version}" is not a number'.format(
             version=version,
         ))
 
-    # Make sure both are positive
-    if number < 0:
-        raise ErrorException('Number "{number}" should be positive'.format(
-            number=number,
-        ))
-
-    if version < 0:
-        raise ErrorException('Version "{version}" should be positive'.format(  # noqa: E501
+    if version_int < 0:
+        raise ErrorException('Version "{version}" should be positive'.format(
             version=version,
         ))
 
-    return '{number}-{version}'.format(number=number, version=version)
-
-
-# Make sense of string supposed to contain a minifigure ID
-def parse_minifig(figure: str, /) -> str:
-    # Minifigure format is typically fig-XXXXXX
-    # We'll accept with or without the 'fig-' prefix
-    figure = figure.strip()
-
-    if not figure.startswith('fig-'):
-        # Try to add the prefix if it's just numbers
-        if figure.isdigit():
-            figure = 'fig-{figure}'.format(figure=figure.zfill(6))
-        else:
-            raise ErrorException('Minifigure "{figure}" must start with "fig-"'.format(
-                figure=figure,
-            ))
-
-    # Validate format: fig-XXXXXX where X can be digits or letters
-    parts = figure.split('-')
-    if len(parts) != 2 or parts[0] != 'fig':
-        raise ErrorException('Invalid minifigure format "{figure}". Expected format: fig-XXXXXX'.format(
-            figure=figure,
-        ))
-
-    return figure
+    return '{number}-{version}'.format(number=number, version=version_int)

bricktracker/part.py

@@ -181,18 +181,7 @@ class BrickPart(RebrickablePart):
 
     # Compute the url for updating checked state
     def url_for_checked(self, /) -> str:
-        # Check if this is an individual minifigure (has minifigure with id field, no brickset)
-        if self.minifigure is not None and hasattr(self.minifigure.fields, 'id') and self.brickset is None:
-            # Individual minifigure part
-            return url_for(
-                'individual_minifigure.checked_part',
-                id=self.minifigure.fields.id,
-                part=self.fields.part,
-                color=self.fields.color,
-                spare=self.fields.spare,
-            )
-
-        # Set-based part (with or without minifigure)
+        # Different URL for a minifigure part
         if self.minifigure is not None:
             figure = self.minifigure.fields.figure
         else:
@@ -239,19 +228,7 @@ class BrickPart(RebrickablePart):
 
     # Compute the url for problematic part
     def url_for_problem(self, problem: str, /) -> str:
-        # Check if this is an individual minifigure (has minifigure with id field, no brickset)
-        if self.minifigure is not None and hasattr(self.minifigure.fields, 'id') and self.brickset is None:
-            # Individual minifigure part
-            return url_for(
-                'individual_minifigure.problem_part',
-                id=self.minifigure.fields.id,
-                part=self.fields.part,
-                color=self.fields.color,
-                spare=self.fields.spare,
-                problem=problem,
-            )
-
-        # Set-based part (with or without minifigure)
+        # Different URL for a minifigure part
         if self.minifigure is not None:
             figure = self.minifigure.fields.figure
         else:
@@ -266,82 +243,3 @@ class BrickPart(RebrickablePart):
             spare=self.fields.spare,
             problem=problem,
         )
-
-    # Select a specific part from an individual minifigure
-    def select_specific_individual_minifigure(
-        self,
-        minifigure: 'BrickMinifigure',
-        part: str,
-        color: int,
-        spare: int,
-        /,
-    ) -> Self:
-        # Save the parameters to the fields
-        self.minifigure = minifigure
-        self.fields.id = minifigure.fields.id
-        self.fields.part = part
-        self.fields.color = color
-        self.fields.spare = spare
-
-        if not self.select(override_query='individual_minifigure/part/select/specific'):
-            raise NotFoundException(
-                'Part {part} with color {color} (spare: {spare}) from individual minifigure {figure} ({id}) was not found in the database'.format(
-                    part=self.fields.part,
-                    color=self.fields.color,
-                    spare=self.fields.spare,
-                    figure=self.minifigure.fields.figure,
-                    id=self.minifigure.fields.id,
-                ),
-            )
-
-        return self
-
-    # Update a problematic part for individual minifigure
-    def update_problem_individual_minifigure(self, problem: str, json: Any | None, /) -> int:
-        amount: str | int = json.get('value', '')  # type: ignore
-
-        # We need a positive integer
-        try:
-            if amount == '':
-                amount = 0
-
-            amount = int(amount)
-
-            if amount < 0:
-                amount = 0
-        except Exception:
-            raise ErrorException('"{amount}" is not a valid integer'.format(
-                amount=amount
-            ))
-
-        if amount < 0:
-            raise ErrorException('Cannot set a negative amount')
-
-        setattr(self.fields, problem, amount)
-
-        BrickSQL().execute_and_commit(
-            'individual_minifigure/part/update/{problem}'.format(problem=problem),
-            parameters=self.sql_parameters()
-        )
-
-        return amount
-
-    # Update checked state for individual minifigure part
-    def update_checked_individual_minifigure(self, json: Any | None, /) -> bool:
-        # Handle both direct 'checked' key and changer.js 'value' key format
-        if json:
-            checked = json.get('checked', json.get('value', False))
-        else:
-            checked = False
-
-        checked = bool(checked)
-
-        # Update the field
-        self.fields.checked = checked
-
-        BrickSQL().execute_and_commit(
-            'individual_minifigure/part/update/checked',
-            parameters=self.sql_parameters()
-        )
-
-        return checked

bricktracker/part_list.py

@@ -25,7 +25,6 @@ class BrickPartList(BrickRecordList[BrickPart]):
     all_query: str = 'part/list/all'
     all_by_owner_query: str = 'part/list/all_by_owner'
     different_color_query = 'part/list/with_different_color'
-    individual_minifigure_query: str = 'individual_minifigure/part/list/from_instance'
     last_query: str = 'part/list/last'
     minifigure_query: str = 'part/list/from_minifigure'
     problem_query: str = 'part/list/problem'
@@ -58,8 +57,8 @@ class BrickPartList(BrickRecordList[BrickPart]):
 
         return self
 
-    # Load all parts with filters (owner and/or color)
-    def all_filtered(self, owner_id: str | None = None, color_id: str | None = None, /) -> Self:
+    # Load all parts with filters (owner, color, theme, year)
+    def all_filtered(self, owner_id: str | None = None, color_id: str | None = None, theme_id: str | None = None, year: str | None = None, /) -> Self:
         # Save the filter parameters
         if owner_id is not None:
             self.fields.owner_id = owner_id
@@ -74,8 +73,13 @@ class BrickPartList(BrickRecordList[BrickPart]):
 
         # Prepare context for query
         context = {}
-        if current_app.config.get('SKIP_SPARE_PARTS', False):
+        # Hide spare parts from display if configured
+        if current_app.config.get('HIDE_SPARE_PARTS', False):
             context['skip_spare_parts'] = True
+        if theme_id and theme_id != 'all':
+            context['theme_id'] = theme_id
+        if year and year != 'all':
+            context['year'] = year
 
         # Load the parts from the database
         self.list(override_query=query, **context)
@@ -87,6 +91,8 @@ class BrickPartList(BrickRecordList[BrickPart]):
         self,
         owner_id: str | None = None,
         color_id: str | None = None,
+        theme_id: str | None = None,
+        year: str | None = None,
         search_query: str | None = None,
         page: int = 1,
         per_page: int = 50,
@@ -103,9 +109,14 @@ class BrickPartList(BrickRecordList[BrickPart]):
 
         if color_id and color_id != 'all':
             filter_context['color_id'] = color_id
+        if theme_id and theme_id != 'all':
+            filter_context['theme_id'] = theme_id
+        if year and year != 'all':
+            filter_context['year'] = year
         if search_query:
             filter_context['search_query'] = search_query
-        if current_app.config.get('SKIP_SPARE_PARTS', False):
+        # Hide spare parts from display if configured
+        if current_app.config.get('HIDE_SPARE_PARTS', False):
             filter_context['skip_spare_parts'] = True
 
         # Field mapping for sorting
@@ -194,8 +205,13 @@ class BrickPartList(BrickRecordList[BrickPart]):
         self.brickset = brickset
         self.minifigure = minifigure
 
+        # Prepare context for hiding spare parts if configured
+        context = {}
+        if current_app.config.get('HIDE_SPARE_PARTS', False):
+            context['skip_spare_parts'] = True
+
         # Load the parts from the database
-        self.list()
+        self.list(**context)
 
         return self
 
@@ -208,22 +224,13 @@ class BrickPartList(BrickRecordList[BrickPart]):
         # Save the  minifigure
         self.minifigure = minifigure
 
+        # Prepare context for hiding spare parts if configured
+        context = {}
+        if current_app.config.get('HIDE_SPARE_PARTS', False):
+            context['skip_spare_parts'] = True
+
         # Load the parts from the database
-        self.list(override_query=self.minifigure_query)
-
-        return self
-
-    # Load parts from an individual minifigure instance
-    def from_individual_minifigure(
-        self,
-        minifigure: 'BrickMinifigure',
-        /,
-    ) -> Self:
-        # Save the minifigure
-        self.minifigure = minifigure
-
-        # Load the parts from the database using the instance-specific query
-        self.list(override_query=self.individual_minifigure_query)
+        self.list(override_query=self.minifigure_query, **context)
 
         return self
 
@@ -253,7 +260,7 @@ class BrickPartList(BrickRecordList[BrickPart]):
 
         return self
 
-    def problem_filtered(self, owner_id: str | None = None, color_id: str | None = None, /) -> Self:
+    def problem_filtered(self, owner_id: str | None = None, color_id: str | None = None, theme_id: str | None = None, year: str | None = None, storage_id: str | None = None, tag_id: str | None = None, /) -> Self:
         # Save the filter parameters for client-side filtering
         if owner_id is not None:
             self.fields.owner_id = owner_id
@@ -266,7 +273,16 @@ class BrickPartList(BrickRecordList[BrickPart]):
             context['owner_id'] = owner_id
         if color_id and color_id != 'all':
             context['color_id'] = color_id
-        if current_app.config.get('SKIP_SPARE_PARTS', False):
+        if theme_id and theme_id != 'all':
+            context['theme_id'] = theme_id
+        if year and year != 'all':
+            context['year'] = year
+        if storage_id and storage_id != 'all':
+            context['storage_id'] = storage_id
+        if tag_id and tag_id != 'all':
+            context['tag_id'] = tag_id
+        # Hide spare parts from display if configured
+        if current_app.config.get('HIDE_SPARE_PARTS', False):
             context['skip_spare_parts'] = True
 
         # Load the problematic parts from the database
@@ -278,6 +294,10 @@ class BrickPartList(BrickRecordList[BrickPart]):
         self,
         owner_id: str | None = None,
         color_id: str | None = None,
+        theme_id: str | None = None,
+        year: str | None = None,
+        storage_id: str | None = None,
+        tag_id: str | None = None,
         search_query: str | None = None,
         page: int = 1,
         per_page: int = 50,
@@ -290,9 +310,18 @@ class BrickPartList(BrickRecordList[BrickPart]):
             filter_context['owner_id'] = owner_id
         if color_id and color_id != 'all':
             filter_context['color_id'] = color_id
+        if theme_id and theme_id != 'all':
+            filter_context['theme_id'] = theme_id
+        if year and year != 'all':
+            filter_context['year'] = year
+        if storage_id and storage_id != 'all':
+            filter_context['storage_id'] = storage_id
+        if tag_id and tag_id != 'all':
+            filter_context['tag_id'] = tag_id
         if search_query:
             filter_context['search_query'] = search_query
-        if current_app.config.get('SKIP_SPARE_PARTS', False):
+        # Hide spare parts from display if configured
+        if current_app.config.get('HIDE_SPARE_PARTS', False):
             filter_context['skip_spare_parts'] = True
 
         # Field mapping for sorting
@@ -321,11 +350,9 @@ class BrickPartList(BrickRecordList[BrickPart]):
     def sql_parameters(self, /) -> dict[str, Any]:
         parameters: dict[str, Any] = super().sql_parameters()
 
-        # Set id - prioritize brickset, then check minifigure
+        # Set id
         if self.brickset is not None:
             parameters['id'] = self.brickset.fields.id
-        elif self.minifigure is not None and hasattr(self.minifigure.fields, 'id'):
-            parameters['id'] = self.minifigure.fields.id
 
         # Use the minifigure number if present,
         if self.minifigure is not None:
@@ -394,7 +421,13 @@ class BrickPartList(BrickRecordList[BrickPart]):
 
             # Process each part
             number_of_parts: int = 0
+            skip_spares = current_app.config.get('SKIP_SPARE_PARTS', False)
+
             for part in inventory:
+                # Skip spare parts if configured
+                if skip_spares and part.fields.spare:
+                    continue
+
                 # Count the number of parts for minifigures
                 if minifigure is not None:
                     number_of_parts += part.fields.quantity

bricktracker/peeron_instructions.py

@@ -7,7 +7,6 @@ from typing import Any, NamedTuple, TYPE_CHECKING
 from urllib.parse import urljoin
 
 from bs4 import BeautifulSoup
-import cloudscraper
 from flask import current_app, url_for
 import requests
 
@@ -53,12 +52,12 @@ def get_peeron_scan_url(set_number: str, version_number: str):
 
 
 def create_peeron_scraper():
-    """Create a cloudscraper instance configured for Peeron"""
-    scraper = cloudscraper.create_scraper()
-    scraper.headers.update({
+    """Create a requests session configured for Peeron"""
+    session = requests.Session()
+    session.headers.update({
         "User-Agent": get_peeron_user_agent()
     })
-    return scraper
+    return session
 
 
 def get_peeron_cache_dir():

bricktracker/peeron_pdf.py

@@ -4,7 +4,6 @@ import tempfile
 import time
 from typing import Any, TYPE_CHECKING
 
-import cloudscraper
 from flask import current_app
 from PIL import Image
 
@@ -188,10 +187,15 @@ class PeeronPDF(object):
     # Get target file path
     def _get_target_path(self, /) -> str:
         """Get the full path where the PDF should be saved"""
-        instructions_folder = os.path.join(
-            current_app.static_folder,  # type: ignore
-            current_app.config['INSTRUCTIONS_FOLDER']
-        )
+        folder = current_app.config['INSTRUCTIONS_FOLDER']
+
+        # If folder is absolute, use it directly
+        # Otherwise, make it relative to app root (not static folder)
+        if os.path.isabs(folder):
+            instructions_folder = folder
+        else:
+            instructions_folder = os.path.join(current_app.root_path, folder)
+
         return os.path.join(instructions_folder, self.filename)
 
     # Create BrickInstructions instance for the generated PDF

bricktracker/rebrickable_image.py

@@ -53,6 +53,23 @@ class RebrickableImage(object):
         if os.path.exists(path):
             return
 
+        # Check if the original image field is null - copy nil placeholder instead
+        if self.part is not None and self.part.fields.image is None:
+            return
+        if self.minifigure is not None and self.minifigure.fields.image is None:
+            return
+        if self.set.fields.image is None:
+            # Copy nil.png from parts folder to sets folder with set number as filename
+            parts_folder = current_app.config['PARTS_FOLDER']
+            if not os.path.isabs(parts_folder):
+                parts_folder = os.path.join(current_app.root_path, parts_folder)
+            nil_source = os.path.join(parts_folder, f"{RebrickableImage.nil_name()}.{self.extension}")
+
+            if os.path.exists(nil_source):
+                import shutil
+                shutil.copy2(nil_source, path)
+            return
+
         url = self.url()
         if url is None:
             return
@@ -96,9 +113,16 @@ class RebrickableImage(object):
 
     # Return the path depending on the objects provided
     def path(self, /) -> str:
+        folder = self.folder()
+        # If folder is an absolute path (starts with /), use it directly
+        # Otherwise, make it relative to app root (current_app.root_path)
+        if folder.startswith('/'):
+            base_path = folder
+        else:
+            base_path = os.path.join(current_app.root_path, folder)
+
         return os.path.join(
-            current_app.static_folder,  # type: ignore
-            self.folder(),
+            base_path,
             '{id}.{ext}'.format(id=self.id(), ext=self.extension),
         )
 
@@ -116,7 +140,11 @@ class RebrickableImage(object):
             else:
                 return self.minifigure.fields.image
 
-        return self.set.fields.image
+        # Handle set images - use nil placeholder if image is null
+        if self.set.fields.image is None:
+            return current_app.config['REBRICKABLE_IMAGE_NIL']
+        else:
+            return self.set.fields.image
 
     # Return the name of the nil image file
     @staticmethod
@@ -152,10 +180,21 @@ class RebrickableImage(object):
         # _, extension = os.path.splitext(self.part_img_url)
         extension = '.jpg'
 
-        # Compute the path
-        path = os.path.join(folder, '{name}{ext}'.format(
-            name=name,
-            ext=extension,
-        ))
-
-        return url_for('static', filename=path)
+        # Determine which route to use based on folder path
+        # If folder contains 'data' (new structure), use data route
+        # Otherwise use static route (legacy - relative paths like 'parts', 'sets')
+        if 'data' in folder:
+            # Extract the folder type from the folder_name config key
+            # E.g., 'PARTS_FOLDER' -> 'parts', 'SETS_FOLDER' -> 'sets'
+            folder_type = folder_name.replace('_FOLDER', '').lower()
+            filename = '{name}{ext}'.format(name=name, ext=extension)
+            return url_for('data.serve_data_file', folder=folder_type, filename=filename)
+        else:
+            # Legacy: folder is relative to static/ (e.g., 'parts' or 'static/parts')
+            # Strip 'static/' prefix if present to avoid double /static/ in URL
+            folder_clean = folder.removeprefix('static/')
+            path = os.path.join(folder_clean, '{name}{ext}'.format(
+                name=name,
+                ext=extension,
+            ))
+            return url_for('static', filename=path)

bricktracker/rebrickable_part.py

@@ -114,19 +114,7 @@ class RebrickablePart(BrickRecord):
             if self.fields.image is None:
                 file = RebrickableImage.nil_name()
             else:
-                # Use image_id if available, otherwise extract from image URL
-                if hasattr(self.fields, 'image_id') and self.fields.image_id:
-                    file = self.fields.image_id
-                else:
-                    # Extract image_id from URL on-the-fly
-                    from urllib.parse import urlparse
-                    import os
-                    image_id, _ = os.path.splitext(
-                        os.path.basename(
-                            urlparse(self.fields.image).path
-                        )
-                    )
-                    file = image_id if image_id else RebrickableImage.nil_name()
+                file = self.fields.image_id
 
             return RebrickableImage.static_url(file, 'PARTS_FOLDER')
         else:
@@ -216,48 +204,6 @@ class RebrickablePart(BrickRecord):
                         if len(bricklink_data['ext_descrs']) > 0 and len(bricklink_data['ext_descrs'][0]) > 0:
                             record['bricklink_color_name'] = bricklink_data['ext_descrs'][0][0]
 
-        # Cache color information in rebrickable_colors table for future lookups
-        # This builds the translation table automatically as sets are imported
-        if 'color' in data:
-            try:
-                from .sql import BrickSQL
-                sql = BrickSQL()
-
-                # Check if color already exists in cache
-                check_query = """
-                    SELECT COUNT(*) FROM "rebrickable_colors"
-                    WHERE "color_id" = :color_id
-                """
-                sql.cursor.execute(check_query, {'color_id': record['color_id']})
-                exists = sql.cursor.fetchone()[0] > 0
-
-                if not exists:
-                    # Insert color into cache
-                    insert_query = """
-                        INSERT OR IGNORE INTO "rebrickable_colors" (
-                            "color_id", "name", "rgb", "is_trans",
-                            "bricklink_color_id", "bricklink_color_name"
-                        ) VALUES (
-                            :color_id, :name, :rgb, :is_trans,
-                            :bricklink_color_id, :bricklink_color_name
-                        )
-                    """
-                    sql.cursor.execute(insert_query, {
-                        'color_id': record['color_id'],
-                        'name': record['color_name'],
-                        'rgb': record['color_rgb'],
-                        'is_trans': record['color_transparent'],
-                        'bricklink_color_id': record['bricklink_color_id'],
-                        'bricklink_color_name': record['bricklink_color_name']
-                    })
-                    # Commit is handled by parent transaction
-
-            except Exception as e:
-                # Don't fail part import if color caching fails
-                import logging
-                logger = logging.getLogger(__name__)
-                logger.debug(f'Could not cache color {record["color_id"]}: {e}')
-
         # Extract BrickLink part number if available
         if 'part' in data and 'external_ids' in data['part']:
             part_external_ids = data['part']['external_ids']
@@ -280,7 +226,7 @@ class RebrickablePart(BrickRecord):
                 )
             )
 
-            if image_id is not None and image_id != '':
+            if image_id is not None or image_id != '':
                 record['image_id'] = image_id
 
         return record

bricktracker/rebrickable_set.py

@@ -155,9 +155,18 @@ class RebrickableSet(BrickRecord):
 
     # Return a short form of the Rebrickable set
     def short(self, /, *, from_download: bool = False) -> dict[str, Any]:
+        # Use nil image URL if set image is null
+        image_url = self.fields.image
+        if image_url is None:
+            # Return path to nil.png from parts folder
+            image_url = RebrickableImage.static_url(
+                RebrickableImage.nil_name(),
+                'PARTS_FOLDER'
+            )
+
         return {
             'download': from_download,
-            'image': self.fields.image,
+            'image': image_url,
             'name': self.fields.name,
             'set': self.fields.set,
         }
@@ -196,17 +205,18 @@ class RebrickableSet(BrickRecord):
     @staticmethod
     def from_rebrickable(data: dict[str, Any], /, **_) -> dict[str, Any]:
         # Extracting version and number
+        # Note: number can be alphanumeric (e.g., "McDR6US", "COMCON035")
         number, _, version = str(data['set_num']).partition('-')
 
         return {
             'set': str(data['set_num']),
-            'number': int(number),
+            'number': str(number),  # Keep as string to support alphanumeric sets
             'version': int(version),
             'name': str(data['name']),
             'year': int(data['year']),
             'theme_id': int(data['theme_id']),
             'number_of_parts': int(data['num_parts']),
-            'image': str(data['set_img_url']),
+            'image': str(data['set_img_url']) if data['set_img_url'] is not None else None,
             'url': str(data['set_url']),
             'last_modified': str(data['last_modified_dt']),
         }

bricktracker/record.py

@@ -1,4 +1,3 @@
-from datetime import datetime
 from sqlite3 import Row
 from typing import Any, ItemsView
 
@@ -6,24 +5,6 @@ from .fields import BrickRecordFields
 from .sql import BrickSQL
 
 
-def format_timestamp(timestamp: float | None, format_key: str = 'PURCHASE_DATE_FORMAT') -> str:
-    """
-    Format a timestamp for display.
-
-    Args:
-        timestamp: Unix timestamp (float) or None
-        format_key: Config key for date format string
-
-    Returns:
-        Formatted date string or empty string if timestamp is None
-    """
-    if timestamp is not None:
-        from flask import current_app
-        time = datetime.fromtimestamp(timestamp)
-        return time.strftime(current_app.config.get(format_key, '%Y/%m/%d'))
-    return ''
-
-
 # SQLite record
 class BrickRecord(object):
     select_query: str

bricktracker/set.py

@@ -59,6 +59,10 @@ class BrickSet(RebrickableSet):
             # Generate an UUID for self
             self.fields.id = str(uuid4())
 
+            # Insert the rebrickable set into database FIRST
+            # This must happen before inserting bricktracker_sets due to FK constraint
+            self.insert_rebrickable()
+
             if not refresh:
                 # Save the storage
                 storage = BrickSetStorageList.get(
@@ -74,7 +78,8 @@ class BrickSet(RebrickableSet):
                 )
                 self.fields.purchase_location = purchase_location.fields.id
 
-                # Insert into database
+                # Insert into database (deferred - will execute at final commit)
+                # All operations are atomic - if anything fails, nothing is committed
                 self.insert(commit=False)
 
                 # Save the owners
@@ -91,9 +96,6 @@ class BrickSet(RebrickableSet):
                     tag = BrickSetTagList.get(id)
                     tag.update_set_state(self, state=True)
 
-            # Insert the rebrickable set into database
-            self.insert_rebrickable()
-
             # Load the inventory
             if not BrickPartList.download(socket, self, refresh=refresh):
                 return False

bricktracker/set_list.py

@@ -36,7 +36,6 @@ class BrickSetList(BrickRecordList[BrickSet]):
     using_minifigure_query: str = 'set/list/using_minifigure'
     using_part_query: str = 'set/list/using_part'
     using_storage_query: str = 'set/list/using_storage'
-    without_storage_query: str = 'set/list/without_storage'
 
     def __init__(self, /):
         super().__init__()
@@ -671,17 +670,10 @@ class BrickSetList(BrickRecordList[BrickSet]):
 
         return self
 
-    def without_storage(self, /) -> Self:
-        # Load sets with no storage
-        self.list(override_query=self.without_storage_query)
-
-        return self
-
 
 # Helper to build the metadata lists
 def set_metadata_lists(
-    as_class: bool = False,
-    hardcoded_statuses_only: bool = False
+    as_class: bool = False
 ) -> dict[
     str,
     Union[
@@ -693,20 +685,9 @@ def set_metadata_lists(
         list[BrickSetTag]
     ]
 ]:
-    # Get all statuses
-    all_statuses = BrickSetStatusList.list(all=True)
-
-    # Filter to only hardcoded statuses if requested (for individual minifigures)
-    if hardcoded_statuses_only:
-        hardcoded_status_ids = ['minifigures_collected', 'set_checked', 'set_collected']
-        statuses = [s for s in all_statuses if s.fields.id in hardcoded_status_ids]
-    else:
-        statuses = all_statuses
-
     return {
         'brickset_owners': BrickSetOwnerList.list(),
         'brickset_purchase_locations': BrickSetPurchaseLocationList.list(as_class=as_class),  # noqa: E501
-        'brickset_statuses': statuses,
         'brickset_storages': BrickSetStorageList.list(as_class=as_class),
         'brickset_tags': BrickSetTagList.list(),
     }

bricktracker/set_owner.py

@@ -5,10 +5,8 @@ from .metadata import BrickMetadata
 class BrickSetOwner(BrickMetadata):
     kind: str = 'owner'
 
-    # Endpoints
+    # Set state endpoint
     set_state_endpoint: str = 'set.update_owner'
-    individual_minifigure_state_endpoint: str = 'individual_minifigure.update_owner'
-    individual_part_state_endpoint: str = 'individual_part.update_owner'
 
     # Queries
     delete_query: str = 'set/metadata/owner/delete'
@@ -16,5 +14,3 @@ class BrickSetOwner(BrickMetadata):
     select_query: str = 'set/metadata/owner/select'
     update_field_query: str = 'set/metadata/owner/update/field'
     update_set_state_query: str = 'set/metadata/owner/update/state'
-    update_individual_minifigure_state_query: str = 'individual_minifigure/metadata/owner/update/state'
-    update_individual_part_state_query: str = 'individual_part/metadata/owner/update/state'

bricktracker/set_owner_list.py

@@ -15,9 +15,6 @@ class BrickSetOwnerList(BrickMetadataList[BrickSetOwner]):
     # Queries
     select_query = 'set/metadata/owner/list'
 
-    # Endpoints
-    set_state_endpoint: str = 'set.update_owner'
-
     # Instantiate the list with the proper class
     @classmethod
     def new(cls, /, *, force: bool = False) -> Self:

bricktracker/set_purchase_location.py

@@ -5,13 +5,9 @@ from .metadata import BrickMetadata
 class BrickSetPurchaseLocation(BrickMetadata):
     kind: str = 'purchase location'
 
-    # Endpoints
-    individual_minifigure_value_endpoint: str = 'individual_minifigure.update_purchase_location'
-
     # Queries
     delete_query: str = 'set/metadata/purchase_location/delete'
     insert_query: str = 'set/metadata/purchase_location/insert'
     select_query: str = 'set/metadata/purchase_location/select'
     update_field_query: str = 'set/metadata/purchase_location/update/field'
     update_set_value_query: str = 'set/metadata/purchase_location/update/value'
-    update_individual_minifigure_value_query: str = 'individual_minifigure/metadata/purchase_location/update/value'

bricktracker/set_purchase_location_list.py

@@ -22,9 +22,6 @@ class BrickSetPurchaseLocationList(
     # Set value endpoint
     set_value_endpoint: str = 'set.update_purchase_location'
 
-    # Individual minifigure value endpoint
-    individual_minifigure_value_endpoint: str = 'individual_minifigure.update_purchase_location'
-
     # Load all purchase locations
     @classmethod
     def all(cls, /) -> Self:

bricktracker/set_status.py

@@ -7,10 +7,8 @@ from .metadata import BrickMetadata
 class BrickSetStatus(BrickMetadata):
     kind: str = 'status'
 
-    # Endpoints
+    # Set state endpoint
     set_state_endpoint: str = 'set.update_status'
-    individual_minifigure_state_endpoint: str = 'individual_minifigure.update_status'
-    individual_part_state_endpoint: str = 'individual_part.update_status'
 
     # Queries
     delete_query: str = 'set/metadata/status/delete'
@@ -18,8 +16,6 @@ class BrickSetStatus(BrickMetadata):
     select_query: str = 'set/metadata/status/select'
     update_field_query: str = 'set/metadata/status/update/field'
     update_set_state_query: str = 'set/metadata/status/update/state'
-    update_individual_minifigure_state_query: str = 'individual_minifigure/metadata/status/update/state'
-    update_individual_part_state_query: str = 'individual_part/metadata/status/update/state'
 
     # Grab data from a form
     def from_form(self, form: dict[str, str], /) -> Self:

bricktracker/set_status_list.py

@@ -15,9 +15,6 @@ class BrickSetStatusList(BrickMetadataList[BrickSetStatus]):
     # Queries
     select_query = 'set/metadata/status/list'
 
-    # Endpoints
-    set_state_endpoint: str = 'set.update_status'
-
     # Filter the list of set status
     def filter(self, all: bool = False) -> list[BrickSetStatus]:
         return [

bricktracker/set_storage.py

@@ -7,16 +7,12 @@ from flask import url_for
 class BrickSetStorage(BrickMetadata):
     kind: str = 'storage'
 
-    # Endpoints
-    individual_minifigure_value_endpoint: str = 'individual_minifigure.update_storage'
-
     # Queries
     delete_query: str = 'set/metadata/storage/delete'
     insert_query: str = 'set/metadata/storage/insert'
     select_query: str = 'set/metadata/storage/select'
     update_field_query: str = 'set/metadata/storage/update/field'
     update_set_value_query: str = 'set/metadata/storage/update/value'
-    update_individual_minifigure_value_query: str = 'individual_minifigure/metadata/storage/update/value'
 
     # Self url
     def url(self, /) -> str:

bricktracker/set_storage_list.py

@@ -20,9 +20,6 @@ class BrickSetStorageList(BrickMetadataList[BrickSetStorage]):
     # Set value endpoint
     set_value_endpoint: str = 'set.update_storage'
 
-    # Individual minifigure value endpoint
-    individual_minifigure_value_endpoint: str = 'individual_minifigure.update_storage'
-
     # Load all storages
     @classmethod
     def all(cls, /) -> Self:

bricktracker/set_tag.py

@@ -5,10 +5,8 @@ from .metadata import BrickMetadata
 class BrickSetTag(BrickMetadata):
     kind: str = 'tag'
 
-    # Endpoints
+    # Set state endpoint
     set_state_endpoint: str = 'set.update_tag'
-    individual_minifigure_state_endpoint: str = 'individual_minifigure.update_tag'
-    individual_part_state_endpoint: str = 'individual_part.update_tag'
 
     # Queries
     delete_query: str = 'set/metadata/tag/delete'
@@ -16,5 +14,3 @@ class BrickSetTag(BrickMetadata):
     select_query: str = 'set/metadata/tag/select'
     update_field_query: str = 'set/metadata/tag/update/field'
     update_set_state_query: str = 'set/metadata/tag/update/state'
-    update_individual_minifigure_state_query: str = 'individual_minifigure/metadata/tag/update/state'
-    update_individual_part_state_query: str = 'individual_part/metadata/tag/update/state'

bricktracker/set_tag_list.py

@@ -15,9 +15,6 @@ class BrickSetTagList(BrickMetadataList[BrickSetTag]):
     # Queries
     select_query: str = 'set/metadata/tag/list'
 
-    # Endpoints
-    set_state_endpoint: str = 'set.update_tag'
-
     # Instantiate the list with the proper class
     @classmethod
     def new(cls, /, *, force: bool = False) -> Self:

bricktracker/socket.py

@@ -18,21 +18,13 @@ logger = logging.getLogger(__name__)
 MESSAGES: Final[dict[str, str]] = {
     'COMPLETE': 'complete',
     'CONNECT': 'connect',
-    'CREATE_LOT': 'create_lot',
     'DISCONNECT': 'disconnect',
     'DOWNLOAD_INSTRUCTIONS': 'download_instructions',
     'DOWNLOAD_PEERON_PAGES': 'download_peeron_pages',
     'FAIL': 'fail',
-    'IMPORT_MINIFIGURE': 'import_minifigure',
     'IMPORT_SET': 'import_set',
-    'LOAD_MINIFIGURE': 'load_minifigure',
-    'LOAD_PART': 'load_part',
-    'LOAD_PART_COLORS': 'load_part_colors',
     'LOAD_PEERON_PAGES': 'load_peeron_pages',
     'LOAD_SET': 'load_set',
-    'MINIFIGURE_LOADED': 'minifigure_loaded',
-    'PART_COLORS_LOADED': 'part_colors_loaded',
-    'PART_LOADED': 'part_loaded',
     'PROGRESS': 'progress',
     'SET_LOADED': 'set_loaded',
 }
@@ -83,6 +75,9 @@ class BrickSocket(object):
             **kwargs,
             path=app.config['SOCKET_PATH'],
             async_mode='gevent',
+            # Ping/pong settings for mobile network resilience
+            ping_timeout=30,  # Wait 30s for pong response before disconnecting
+            ping_interval=25,  # Send ping every 25s to keep connection alive
         )
 
         # Store the socket in the app config
@@ -94,9 +89,23 @@ class BrickSocket(object):
             self.connected()
 
         @self.socket.on(MESSAGES['DISCONNECT'], namespace=self.namespace)
-        def disconnect() -> None:
+        def disconnect(reason=None) -> None:
             self.disconnected()
 
+        @self.socket.on('connect_error', namespace=self.namespace)
+        def connect_error(data) -> None:
+            logger.error(f'Socket CONNECT_ERROR: {data}')
+
+        @self.socket.on_error(namespace=self.namespace)
+        def error_handler(e) -> None:
+            logger.error(f'Socket ERROR: {e}')
+            try:
+                user_agent = request.headers.get('User-Agent', 'unknown')
+                remote_addr = request.remote_addr
+                logger.error(f'Socket ERROR details: ip={remote_addr}, ua={user_agent[:80]}...')
+            except Exception:
+                pass
+
         @self.socket.on(MESSAGES['DOWNLOAD_INSTRUCTIONS'], namespace=self.namespace)  # noqa: E501
         @authenticated_socket(self)
         def download_instructions(data: dict[str, Any], /) -> None:
@@ -215,57 +224,6 @@ class BrickSocket(object):
 
             BrickSet().load(self, data)
 
-        @self.socket.on(MESSAGES['IMPORT_MINIFIGURE'], namespace=self.namespace)
-        @rebrickable_socket(self)
-        def import_minifigure(data: dict[str, Any], /) -> None:
-            logger.debug('Socket: IMPORT_MINIFIGURE={data} (from: {fr})'.format(
-                data=data,
-                fr=request.sid,  # type: ignore
-            ))
-
-            from .individual_minifigure import IndividualMinifigure
-            IndividualMinifigure().download(self, data)
-
-        @self.socket.on(MESSAGES['LOAD_MINIFIGURE'], namespace=self.namespace)
-        def load_minifigure(data: dict[str, Any], /) -> None:
-            logger.debug('Socket: LOAD_MINIFIGURE={data} (from: {fr})'.format(
-                data=data,
-                fr=request.sid,  # type: ignore
-            ))
-
-            from .individual_minifigure import IndividualMinifigure
-            IndividualMinifigure().load(self, data)
-
-        @self.socket.on(MESSAGES['LOAD_PART'], namespace=self.namespace)
-        def load_part(data: dict[str, Any], /) -> None:
-            logger.debug('Socket: LOAD_PART={data} (from: {fr})'.format(
-                data=data,
-                fr=request.sid,  # type: ignore
-            ))
-
-            from .individual_part import IndividualPart
-            IndividualPart().add(self, data)
-
-        @self.socket.on(MESSAGES['LOAD_PART_COLORS'], namespace=self.namespace)
-        def load_part_colors(data: dict[str, Any], /) -> None:
-            logger.debug('Socket: LOAD_PART_COLORS={data} (from: {fr})'.format(
-                data=data,
-                fr=request.sid,  # type: ignore
-            ))
-
-            from .individual_part import IndividualPart
-            IndividualPart().load_colors(self, data)
-
-        @self.socket.on(MESSAGES['CREATE_LOT'], namespace=self.namespace)
-        @rebrickable_socket(self)
-        def create_lot(data: dict[str, Any], /) -> None:
-            logger.debug('Socket: CREATE_LOT (from: {fr})'.format(
-                fr=request.sid,  # type: ignore
-            ))
-
-            from .individual_part_lot import IndividualPartLot
-            IndividualPartLot().create(self, data)
-
     # Update the progress auto-incrementing
     def auto_progress(
         self,
@@ -291,13 +249,32 @@ class BrickSocket(object):
 
     # Socket is connected
     def connected(self, /) -> Tuple[str, int]:
-        logger.debug('Socket: client connected')
+        # Get detailed connection info for debugging
+        try:
+            sid = request.sid  # type: ignore
+            transport = request.environ.get('HTTP_UPGRADE', 'polling')
+            user_agent = request.headers.get('User-Agent', 'unknown')
+            remote_addr = request.remote_addr
+
+            # Check if it's likely a mobile device
+            is_mobile = any(x in user_agent.lower() for x in ['iphone', 'ipad', 'android', 'mobile'])
+
+            logger.info(
+                f'Socket CONNECTED: sid={sid}, transport={transport}, '
+                f'ip={remote_addr}, mobile={is_mobile}, ua={user_agent[:80]}...'
+            )
+        except Exception as e:
+            logger.warning(f'Socket connected but failed to get details: {e}')
 
         return '', 301
 
     # Socket is disconnected
     def disconnected(self, /) -> None:
-        logger.debug('Socket: client disconnected')
+        try:
+            sid = request.sid  # type: ignore
+            logger.info(f'Socket DISCONNECTED: sid={sid}')
+        except Exception as e:
+            logger.info(f'Socket disconnected (sid unavailable): {e}')
 
     # Emit a message through the socket
     def emit(self, name: str, *arg, all=False) -> None:

bricktracker/sql/individual_minifigure/delete/loose_minifigure.sql

@@ -1,19 +0,0 @@
--- Delete individual minifigure parts
-DELETE FROM "bricktracker_individual_minifigure_parts"
-WHERE "id" = :id;
-
--- Delete individual minifigure owners
-DELETE FROM "bricktracker_individual_minifigure_owners"
-WHERE "id" = :id;
-
--- Delete individual minifigure tags
-DELETE FROM "bricktracker_individual_minifigure_tags"
-WHERE "id" = :id;
-
--- Delete individual minifigure statuses
-DELETE FROM "bricktracker_individual_minifigure_statuses"
-WHERE "id" = :id;
-
--- Delete the individual minifigure itself
-DELETE FROM "bricktracker_individual_minifigures"
-WHERE "id" = :id;

bricktracker/sql/individual_minifigure/insert.sql

@@ -1,15 +0,0 @@
-INSERT OR IGNORE INTO "bricktracker_individual_minifigures" (
-    "id",
-    "figure",
-    "quantity",
-    "description",
-    "storage",
-    "purchase_location"
-) VALUES (
-    :id,
-    :figure,
-    :quantity,
-    :description,
-    :storage,
-    :purchase_location
-)

bricktracker/sql/individual_minifigure/list/using_storage.sql

@@ -1,48 +0,0 @@
--- Get all individual minifigure instances for a specific storage location
-SELECT
-    "bricktracker_individual_minifigures"."id",
-    "bricktracker_individual_minifigures"."figure",
-    "bricktracker_individual_minifigures"."quantity",
-    "bricktracker_individual_minifigures"."description",
-    "bricktracker_individual_minifigures"."storage",
-    "bricktracker_individual_minifigures"."purchase_location",
-    "rebrickable_minifigures"."number",
-    "rebrickable_minifigures"."name",
-    "rebrickable_minifigures"."image",
-    "rebrickable_minifigures"."number_of_parts",
-    "storage_meta"."name" AS "storage_name",
-    "purchase_meta"."name" AS "purchase_location_name",
-    IFNULL("problem_join"."total_missing", 0) AS "total_missing",
-    IFNULL("problem_join"."total_damaged", 0) AS "total_damaged"
-FROM "bricktracker_individual_minifigures"
-
-INNER JOIN "rebrickable_minifigures"
-ON "bricktracker_individual_minifigures"."figure" = "rebrickable_minifigures"."figure"
-
-LEFT JOIN "bricktracker_metadata_storages" AS "storage_meta"
-ON "bricktracker_individual_minifigures"."storage" = "storage_meta"."id"
-
-LEFT JOIN "bricktracker_metadata_purchase_locations" AS "purchase_meta"
-ON "bricktracker_individual_minifigures"."purchase_location" = "purchase_meta"."id"
-
-LEFT JOIN (
-    SELECT
-        "bricktracker_individual_minifigure_parts"."id",
-        SUM("bricktracker_individual_minifigure_parts"."missing") AS "total_missing",
-        SUM("bricktracker_individual_minifigure_parts"."damaged") AS "total_damaged"
-    FROM "bricktracker_individual_minifigure_parts"
-    GROUP BY "bricktracker_individual_minifigure_parts"."id"
-) "problem_join"
-ON "bricktracker_individual_minifigures"."id" = "problem_join"."id"
-
-WHERE "bricktracker_individual_minifigures"."storage" IS NOT DISTINCT FROM :storage
-
-{% if order %}
-ORDER BY {{ order }}
-{% else %}
-ORDER BY "bricktracker_individual_minifigures"."rowid" DESC
-{% endif %}
-
-{% if limit %}
-LIMIT {{ limit }}
-{% endif %}

bricktracker/sql/individual_minifigure/list/without_storage.sql

@@ -1,48 +0,0 @@
--- Get all individual minifigure instances without storage
-SELECT
-    "bricktracker_individual_minifigures"."id",
-    "bricktracker_individual_minifigures"."figure",
-    "bricktracker_individual_minifigures"."quantity",
-    "bricktracker_individual_minifigures"."description",
-    "bricktracker_individual_minifigures"."storage",
-    "bricktracker_individual_minifigures"."purchase_location",
-    "rebrickable_minifigures"."number",
-    "rebrickable_minifigures"."name",
-    "rebrickable_minifigures"."image",
-    "rebrickable_minifigures"."number_of_parts",
-    "storage_meta"."name" AS "storage_name",
-    "purchase_meta"."name" AS "purchase_location_name",
-    IFNULL("problem_join"."total_missing", 0) AS "total_missing",
-    IFNULL("problem_join"."total_damaged", 0) AS "total_damaged"
-FROM "bricktracker_individual_minifigures"
-
-INNER JOIN "rebrickable_minifigures"
-ON "bricktracker_individual_minifigures"."figure" = "rebrickable_minifigures"."figure"
-
-LEFT JOIN "bricktracker_metadata_storages" AS "storage_meta"
-ON "bricktracker_individual_minifigures"."storage" = "storage_meta"."id"
-
-LEFT JOIN "bricktracker_metadata_purchase_locations" AS "purchase_meta"
-ON "bricktracker_individual_minifigures"."purchase_location" = "purchase_meta"."id"
-
-LEFT JOIN (
-    SELECT
-        "bricktracker_individual_minifigure_parts"."id",
-        SUM("bricktracker_individual_minifigure_parts"."missing") AS "total_missing",
-        SUM("bricktracker_individual_minifigure_parts"."damaged") AS "total_damaged"
-    FROM "bricktracker_individual_minifigure_parts"
-    GROUP BY "bricktracker_individual_minifigure_parts"."id"
-) "problem_join"
-ON "bricktracker_individual_minifigures"."id" = "problem_join"."id"
-
-WHERE "bricktracker_individual_minifigures"."storage" IS NULL
-
-{% if order %}
-ORDER BY {{ order }}
-{% else %}
-ORDER BY "bricktracker_individual_minifigures"."rowid" DESC
-{% endif %}
-
-{% if limit %}
-LIMIT {{ limit }}
-{% endif %}

bricktracker/sql/individual_minifigure/metadata/owner/update/state.sql

@@ -1,10 +0,0 @@
-INSERT INTO "bricktracker_individual_minifigure_owners" (
-    "id",
-    "{{name}}"
-) VALUES (
-    :id,
-    :state
-)
-ON CONFLICT("id")
-DO UPDATE SET "{{name}}" = :state
-WHERE "bricktracker_individual_minifigure_owners"."id" IS NOT DISTINCT FROM :id

bricktracker/sql/individual_minifigure/metadata/status/update/state.sql

@@ -1,10 +0,0 @@
-INSERT INTO "bricktracker_individual_minifigure_statuses" (
-    "id",
-    "{{name}}"
-) VALUES (
-    :id,
-    :state
-)
-ON CONFLICT("id")
-DO UPDATE SET "{{name}}" = :state
-WHERE "bricktracker_individual_minifigure_statuses"."id" IS NOT DISTINCT FROM :id

bricktracker/sql/individual_minifigure/metadata/tag/update/state.sql

@@ -1,10 +0,0 @@
-INSERT INTO "bricktracker_individual_minifigure_tags" (
-    "id",
-    "{{name}}"
-) VALUES (
-    :id,
-    :state
-)
-ON CONFLICT("id")
-DO UPDATE SET "{{name}}" = :state
-WHERE "bricktracker_individual_minifigure_tags"."id" IS NOT DISTINCT FROM :id

bricktracker/sql/individual_minifigure/part/insert.sql

@@ -1,23 +0,0 @@
-INSERT OR IGNORE INTO "bricktracker_individual_minifigure_parts" (
-    "id",
-    "part",
-    "color",
-    "spare",
-    "quantity",
-    "element",
-    "rebrickable_inventory",
-    "missing",
-    "damaged",
-    "checked"
-) VALUES (
-    :id,
-    :part,
-    :color,
-    :spare,
-    :quantity,
-    :element,
-    :rebrickable_inventory,
-    0,
-    0,
-    0
-)

bricktracker/sql/individual_minifigure/part/list/from_instance.sql

@@ -1,38 +0,0 @@
--- Query parts for a specific individual minifigure instance
-SELECT
-    "bricktracker_individual_minifigure_parts"."id",
-    "bricktracker_individual_minifigures"."figure",
-    "bricktracker_individual_minifigure_parts"."part",
-    "bricktracker_individual_minifigure_parts"."color",
-    "bricktracker_individual_minifigure_parts"."spare",
-    "bricktracker_individual_minifigure_parts"."quantity",
-    "bricktracker_individual_minifigure_parts"."element",
-    "bricktracker_individual_minifigure_parts"."missing" AS "total_missing",
-    "bricktracker_individual_minifigure_parts"."damaged" AS "total_damaged",
-    "bricktracker_individual_minifigure_parts"."checked",
-    "rebrickable_parts"."color_name",
-    "rebrickable_parts"."color_rgb",
-    "rebrickable_parts"."color_transparent",
-    "rebrickable_parts"."bricklink_color_id",
-    "rebrickable_parts"."bricklink_color_name",
-    "rebrickable_parts"."bricklink_part_num",
-    "rebrickable_parts"."name",
-    "rebrickable_parts"."image",
-    "rebrickable_parts"."image_id",
-    "rebrickable_parts"."url",
-    "rebrickable_parts"."print",
-    NULL AS "total_quantity",
-    NULL AS "total_spare",
-    NULL AS "total_sets",
-    NULL AS "total_minifigures"
-FROM "bricktracker_individual_minifigure_parts"
-INNER JOIN "bricktracker_individual_minifigures"
-ON "bricktracker_individual_minifigure_parts"."id" = "bricktracker_individual_minifigures"."id"
-INNER JOIN "rebrickable_parts"
-ON "bricktracker_individual_minifigure_parts"."part" = "rebrickable_parts"."part"
-AND "bricktracker_individual_minifigure_parts"."color" = "rebrickable_parts"."color_id"
-WHERE "bricktracker_individual_minifigure_parts"."id" IS NOT DISTINCT FROM :id
-
-{% if order %}
-ORDER BY {{ order | replace('"combined"', '"bricktracker_individual_minifigure_parts"') | replace('"bricktracker_parts"', '"bricktracker_individual_minifigure_parts"') }}
-{% endif %}

bricktracker/sql/individual_minifigure/part/select/specific.sql

@@ -1,33 +0,0 @@
--- Select a specific part from an individual minifigure instance
-SELECT
-    "bricktracker_individual_minifigure_parts"."id",
-    "bricktracker_individual_minifigures"."figure",
-    "bricktracker_individual_minifigure_parts"."part",
-    "bricktracker_individual_minifigure_parts"."color",
-    "bricktracker_individual_minifigure_parts"."spare",
-    "bricktracker_individual_minifigure_parts"."quantity",
-    "bricktracker_individual_minifigure_parts"."element",
-    "bricktracker_individual_minifigure_parts"."missing",
-    "bricktracker_individual_minifigure_parts"."damaged",
-    "bricktracker_individual_minifigure_parts"."checked",
-    "rebrickable_parts"."color_name",
-    "rebrickable_parts"."color_rgb",
-    "rebrickable_parts"."color_transparent",
-    "rebrickable_parts"."bricklink_color_id",
-    "rebrickable_parts"."bricklink_color_name",
-    "rebrickable_parts"."bricklink_part_num",
-    "rebrickable_parts"."name",
-    "rebrickable_parts"."image",
-    "rebrickable_parts"."image_id",
-    "rebrickable_parts"."url",
-    "rebrickable_parts"."print"
-FROM "bricktracker_individual_minifigure_parts"
-INNER JOIN "bricktracker_individual_minifigures"
-ON "bricktracker_individual_minifigure_parts"."id" = "bricktracker_individual_minifigures"."id"
-INNER JOIN "rebrickable_parts"
-ON "bricktracker_individual_minifigure_parts"."part" = "rebrickable_parts"."part"
-AND "bricktracker_individual_minifigure_parts"."color" = "rebrickable_parts"."color_id"
-WHERE "bricktracker_individual_minifigure_parts"."id" IS NOT DISTINCT FROM :id
-AND "bricktracker_individual_minifigure_parts"."part" IS NOT DISTINCT FROM :part
-AND "bricktracker_individual_minifigure_parts"."color" IS NOT DISTINCT FROM :color
-AND "bricktracker_individual_minifigure_parts"."spare" IS NOT DISTINCT FROM :spare

bricktracker/sql/individual_minifigure/part/update/checked.sql

@@ -1,6 +0,0 @@
-UPDATE "bricktracker_individual_minifigure_parts"
-SET "checked" = :checked
-WHERE "bricktracker_individual_minifigure_parts"."id" IS NOT DISTINCT FROM :id
-AND "bricktracker_individual_minifigure_parts"."part" IS NOT DISTINCT FROM :part
-AND "bricktracker_individual_minifigure_parts"."color" IS NOT DISTINCT FROM :color
-AND "bricktracker_individual_minifigure_parts"."spare" IS NOT DISTINCT FROM :spare

bricktracker/sql/individual_minifigure/part/update/damaged.sql

@@ -1,6 +0,0 @@
-UPDATE "bricktracker_individual_minifigure_parts"
-SET "damaged" = :damaged
-WHERE "bricktracker_individual_minifigure_parts"."id" IS NOT DISTINCT FROM :id
-AND "bricktracker_individual_minifigure_parts"."part" IS NOT DISTINCT FROM :part
-AND "bricktracker_individual_minifigure_parts"."color" IS NOT DISTINCT FROM :color
-AND "bricktracker_individual_minifigure_parts"."spare" IS NOT DISTINCT FROM :spare

bricktracker/sql/individual_minifigure/part/update/missing.sql

@@ -1,6 +0,0 @@
-UPDATE "bricktracker_individual_minifigure_parts"
-SET "missing" = :missing
-WHERE "bricktracker_individual_minifigure_parts"."id" IS NOT DISTINCT FROM :id
-AND "bricktracker_individual_minifigure_parts"."part" IS NOT DISTINCT FROM :part
-AND "bricktracker_individual_minifigure_parts"."color" IS NOT DISTINCT FROM :color
-AND "bricktracker_individual_minifigure_parts"."spare" IS NOT DISTINCT FROM :spare

bricktracker/sql/individual_minifigure/select/by_id.sql

@@ -1,35 +0,0 @@
--- Get a specific individual minifigure instance by ID
-SELECT
-    "bricktracker_individual_minifigures"."id",
-    "bricktracker_individual_minifigures"."figure",
-    "bricktracker_individual_minifigures"."quantity",
-    "bricktracker_individual_minifigures"."description",
-    "bricktracker_individual_minifigures"."storage",
-    "bricktracker_individual_minifigures"."purchase_location",
-    "rebrickable_minifigures"."number",
-    "rebrickable_minifigures"."name",
-    "rebrickable_minifigures"."image",
-    "rebrickable_minifigures"."number_of_parts",
-    "storage_meta"."name" AS "storage_name",
-    "purchase_meta"."name" AS "purchase_location_name"{{ owners }}{{ statuses }}{{ tags }}
-FROM "bricktracker_individual_minifigures"
-
-INNER JOIN "rebrickable_minifigures"
-ON "bricktracker_individual_minifigures"."figure" = "rebrickable_minifigures"."figure"
-
-LEFT JOIN "bricktracker_metadata_storages" AS "storage_meta"
-ON "bricktracker_individual_minifigures"."storage" = "storage_meta"."id"
-
-LEFT JOIN "bricktracker_metadata_purchase_locations" AS "purchase_meta"
-ON "bricktracker_individual_minifigures"."purchase_location" = "purchase_meta"."id"
-
-LEFT JOIN "bricktracker_individual_minifigure_owners"
-ON "bricktracker_individual_minifigures"."id" IS NOT DISTINCT FROM "bricktracker_individual_minifigure_owners"."id"
-
-LEFT JOIN "bricktracker_individual_minifigure_statuses"
-ON "bricktracker_individual_minifigures"."id" IS NOT DISTINCT FROM "bricktracker_individual_minifigure_statuses"."id"
-
-LEFT JOIN "bricktracker_individual_minifigure_tags"
-ON "bricktracker_individual_minifigures"."id" IS NOT DISTINCT FROM "bricktracker_individual_minifigure_tags"."id"
-
-WHERE "bricktracker_individual_minifigures"."id" = :id

bricktracker/sql/individual_minifigure/select/instances_by_figure.sql

@@ -1,52 +0,0 @@
--- Get all individual minifigure instances for a specific figure
-SELECT
-    "bricktracker_individual_minifigures"."id",
-    "bricktracker_individual_minifigures"."figure",
-    "bricktracker_individual_minifigures"."quantity",
-    "bricktracker_individual_minifigures"."description",
-    "bricktracker_individual_minifigures"."storage",
-    "bricktracker_individual_minifigures"."purchase_location",
-    "rebrickable_minifigures"."number",
-    "rebrickable_minifigures"."name",
-    "rebrickable_minifigures"."image",
-    "rebrickable_minifigures"."number_of_parts",
-    "storage_meta"."name" AS "storage_name",
-    "purchase_meta"."name" AS "purchase_location_name",
-    {{ owners }},
-    {{ statuses }},
-    {{ tags }},
-    IFNULL("problem_join"."total_missing", 0) AS "total_missing",
-    IFNULL("problem_join"."total_damaged", 0) AS "total_damaged"
-FROM "bricktracker_individual_minifigures"
-
-INNER JOIN "rebrickable_minifigures"
-ON "bricktracker_individual_minifigures"."figure" = "rebrickable_minifigures"."figure"
-
-LEFT JOIN "bricktracker_metadata_storages" AS "storage_meta"
-ON "bricktracker_individual_minifigures"."storage" = "storage_meta"."id"
-
-LEFT JOIN "bricktracker_metadata_purchase_locations" AS "purchase_meta"
-ON "bricktracker_individual_minifigures"."purchase_location" = "purchase_meta"."id"
-
-LEFT JOIN "bricktracker_individual_minifigure_owners"
-ON "bricktracker_individual_minifigures"."id" = "bricktracker_individual_minifigure_owners"."id"
-
-LEFT JOIN "bricktracker_individual_minifigure_statuses"
-ON "bricktracker_individual_minifigures"."id" = "bricktracker_individual_minifigure_statuses"."id"
-
-LEFT JOIN "bricktracker_individual_minifigure_tags"
-ON "bricktracker_individual_minifigures"."id" = "bricktracker_individual_minifigure_tags"."id"
-
-LEFT JOIN (
-    SELECT
-        "bricktracker_individual_minifigure_parts"."id",
-        SUM("bricktracker_individual_minifigure_parts"."missing") AS "total_missing",
-        SUM("bricktracker_individual_minifigure_parts"."damaged") AS "total_damaged"
-    FROM "bricktracker_individual_minifigure_parts"
-    GROUP BY "bricktracker_individual_minifigure_parts"."id"
-) "problem_join"
-ON "bricktracker_individual_minifigures"."id" = "problem_join"."id"
-
-WHERE "bricktracker_individual_minifigures"."figure" = :figure
-
-ORDER BY "bricktracker_individual_minifigures"."rowid" DESC

bricktracker/sql/individual_minifigure/update.sql

@@ -1,7 +0,0 @@
-UPDATE "bricktracker_individual_minifigures"
-SET
-    "quantity" = :quantity,
-    "description" = :description,
-    "storage" = :storage,
-    "purchase_location" = :purchase_location
-WHERE "id" = :id

bricktracker/sql/individual_part/delete.sql

@@ -1,13 +0,0 @@
--- Delete metadata first (foreign keys with CASCADE will handle this, but being explicit)
-DELETE FROM "bricktracker_individual_part_owners"
-WHERE "id" = '{{ id }}';
-
-DELETE FROM "bricktracker_individual_part_tags"
-WHERE "id" = '{{ id }}';
-
-DELETE FROM "bricktracker_individual_part_statuses"
-WHERE "id" = '{{ id }}';
-
--- Delete the individual part itself
-DELETE FROM "bricktracker_individual_parts"
-WHERE "id" = '{{ id }}';

bricktracker/sql/individual_part/insert.sql

@@ -1,27 +0,0 @@
-INSERT INTO "bricktracker_individual_parts" (
-    "id",
-    "part",
-    "color",
-    "quantity",
-    "missing",
-    "damaged",
-    "checked",
-    "description",
-    "storage",
-    "purchase_location",
-    "purchase_date",
-    "purchase_price"
-) VALUES (
-    :id,
-    :part,
-    :color,
-    :quantity,
-    :missing,
-    :damaged,
-    :checked,
-    :description,
-    :storage,
-    :purchase_location,
-    :purchase_date,
-    :purchase_price
-)

bricktracker/sql/individual_part/list/all.sql

@@ -1,30 +0,0 @@
-SELECT
-    "bricktracker_individual_parts"."id",
-    "bricktracker_individual_parts"."part",
-    "bricktracker_individual_parts"."color",
-    "bricktracker_individual_parts"."quantity",
-    "bricktracker_individual_parts"."missing",
-    "bricktracker_individual_parts"."damaged",
-    "bricktracker_individual_parts"."checked",
-    "bricktracker_individual_parts"."description",
-    "bricktracker_individual_parts"."storage",
-    "bricktracker_individual_parts"."purchase_location",
-    "bricktracker_individual_parts"."purchase_date",
-    "bricktracker_individual_parts"."purchase_price",
-    "rebrickable_parts"."name",
-    "rebrickable_parts"."color_name",
-    "rebrickable_parts"."color_rgb",
-    "rebrickable_parts"."color_transparent",
-    "rebrickable_parts"."image",
-    "rebrickable_parts"."url",
-    "bricktracker_metadata_storages"."name" AS "storage_name",
-    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
-FROM "bricktracker_individual_parts"
-INNER JOIN "rebrickable_parts"
-    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
-    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
-LEFT JOIN "bricktracker_metadata_storages"
-    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
-LEFT JOIN "bricktracker_metadata_purchase_locations"
-    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
-ORDER BY "bricktracker_individual_parts"."part", "bricktracker_individual_parts"."color"

bricktracker/sql/individual_part/list/by_color.sql

@@ -1,31 +0,0 @@
-SELECT
-    "bricktracker_individual_parts"."id",
-    "bricktracker_individual_parts"."part",
-    "bricktracker_individual_parts"."color",
-    "bricktracker_individual_parts"."quantity",
-    "bricktracker_individual_parts"."missing",
-    "bricktracker_individual_parts"."damaged",
-    "bricktracker_individual_parts"."checked",
-    "bricktracker_individual_parts"."description",
-    "bricktracker_individual_parts"."storage",
-    "bricktracker_individual_parts"."purchase_location",
-    "bricktracker_individual_parts"."purchase_date",
-    "bricktracker_individual_parts"."purchase_price",
-    "rebrickable_parts"."name",
-    "rebrickable_parts"."color_name",
-    "rebrickable_parts"."color_rgb",
-    "rebrickable_parts"."color_transparent",
-    "rebrickable_parts"."image",
-    "rebrickable_parts"."url",
-    "bricktracker_metadata_storages"."name" AS "storage_name",
-    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
-FROM "bricktracker_individual_parts"
-INNER JOIN "rebrickable_parts"
-    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
-    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
-LEFT JOIN "bricktracker_metadata_storages"
-    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
-LEFT JOIN "bricktracker_metadata_purchase_locations"
-    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
-WHERE "bricktracker_individual_parts"."color" = :color
-ORDER BY "bricktracker_individual_parts"."part"

bricktracker/sql/individual_part/list/by_part.sql

@@ -1,31 +0,0 @@
-SELECT
-    "bricktracker_individual_parts"."id",
-    "bricktracker_individual_parts"."part",
-    "bricktracker_individual_parts"."color",
-    "bricktracker_individual_parts"."quantity",
-    "bricktracker_individual_parts"."missing",
-    "bricktracker_individual_parts"."damaged",
-    "bricktracker_individual_parts"."checked",
-    "bricktracker_individual_parts"."description",
-    "bricktracker_individual_parts"."storage",
-    "bricktracker_individual_parts"."purchase_location",
-    "bricktracker_individual_parts"."purchase_date",
-    "bricktracker_individual_parts"."purchase_price",
-    "rebrickable_parts"."name",
-    "rebrickable_parts"."color_name",
-    "rebrickable_parts"."color_rgb",
-    "rebrickable_parts"."color_transparent",
-    "rebrickable_parts"."image",
-    "rebrickable_parts"."url",
-    "bricktracker_metadata_storages"."name" AS "storage_name",
-    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
-FROM "bricktracker_individual_parts"
-INNER JOIN "rebrickable_parts"
-    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
-    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
-LEFT JOIN "bricktracker_metadata_storages"
-    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
-LEFT JOIN "bricktracker_metadata_purchase_locations"
-    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
-WHERE "bricktracker_individual_parts"."part" = :part
-ORDER BY "bricktracker_individual_parts"."color"

bricktracker/sql/individual_part/list/by_part_and_color.sql

@@ -1,32 +0,0 @@
-SELECT
-    "bricktracker_individual_parts"."id",
-    "bricktracker_individual_parts"."part",
-    "bricktracker_individual_parts"."color",
-    "bricktracker_individual_parts"."quantity",
-    "bricktracker_individual_parts"."missing",
-    "bricktracker_individual_parts"."damaged",
-    "bricktracker_individual_parts"."checked",
-    "bricktracker_individual_parts"."description",
-    "bricktracker_individual_parts"."storage",
-    "bricktracker_individual_parts"."purchase_location",
-    "bricktracker_individual_parts"."purchase_date",
-    "bricktracker_individual_parts"."purchase_price",
-    "rebrickable_parts"."name",
-    "rebrickable_parts"."color_name",
-    "rebrickable_parts"."color_rgb",
-    "rebrickable_parts"."color_transparent",
-    "rebrickable_parts"."image",
-    "rebrickable_parts"."url",
-    "bricktracker_metadata_storages"."name" AS "storage_name",
-    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
-FROM "bricktracker_individual_parts"
-INNER JOIN "rebrickable_parts"
-    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
-    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
-LEFT JOIN "bricktracker_metadata_storages"
-    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
-LEFT JOIN "bricktracker_metadata_purchase_locations"
-    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
-WHERE "bricktracker_individual_parts"."part" = :part
-    AND "bricktracker_individual_parts"."color" = :color
-ORDER BY "bricktracker_individual_parts"."id"

bricktracker/sql/individual_part/list/by_storage.sql

@@ -1,31 +0,0 @@
-SELECT
-    "bricktracker_individual_parts"."id",
-    "bricktracker_individual_parts"."part",
-    "bricktracker_individual_parts"."color",
-    "bricktracker_individual_parts"."quantity",
-    "bricktracker_individual_parts"."missing",
-    "bricktracker_individual_parts"."damaged",
-    "bricktracker_individual_parts"."checked",
-    "bricktracker_individual_parts"."description",
-    "bricktracker_individual_parts"."storage",
-    "bricktracker_individual_parts"."purchase_location",
-    "bricktracker_individual_parts"."purchase_date",
-    "bricktracker_individual_parts"."purchase_price",
-    "rebrickable_parts"."name",
-    "rebrickable_parts"."color_name",
-    "rebrickable_parts"."color_rgb",
-    "rebrickable_parts"."color_transparent",
-    "rebrickable_parts"."image",
-    "rebrickable_parts"."url",
-    "bricktracker_metadata_storages"."name" AS "storage_name",
-    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
-FROM "bricktracker_individual_parts"
-INNER JOIN "rebrickable_parts"
-    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
-    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
-LEFT JOIN "bricktracker_metadata_storages"
-    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
-LEFT JOIN "bricktracker_metadata_purchase_locations"
-    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
-WHERE "bricktracker_individual_parts"."storage" = :storage
-ORDER BY "bricktracker_individual_parts"."part", "bricktracker_individual_parts"."color"

bricktracker/sql/individual_part/list/problem.sql

@@ -1,32 +0,0 @@
-SELECT
-    "bricktracker_individual_parts"."id",
-    "bricktracker_individual_parts"."part",
-    "bricktracker_individual_parts"."color",
-    "bricktracker_individual_parts"."quantity",
-    "bricktracker_individual_parts"."missing",
-    "bricktracker_individual_parts"."damaged",
-    "bricktracker_individual_parts"."checked",
-    "bricktracker_individual_parts"."description",
-    "bricktracker_individual_parts"."storage",
-    "bricktracker_individual_parts"."purchase_location",
-    "bricktracker_individual_parts"."purchase_date",
-    "bricktracker_individual_parts"."purchase_price",
-    "rebrickable_parts"."name",
-    "rebrickable_parts"."color_name",
-    "rebrickable_parts"."color_rgb",
-    "rebrickable_parts"."color_transparent",
-    "rebrickable_parts"."image",
-    "rebrickable_parts"."url",
-    "bricktracker_metadata_storages"."name" AS "storage_name",
-    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
-FROM "bricktracker_individual_parts"
-INNER JOIN "rebrickable_parts"
-    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
-    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
-LEFT JOIN "bricktracker_metadata_storages"
-    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
-LEFT JOIN "bricktracker_metadata_purchase_locations"
-    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
-WHERE "bricktracker_individual_parts"."missing" > 0
-   OR "bricktracker_individual_parts"."damaged" > 0
-ORDER BY "bricktracker_individual_parts"."part", "bricktracker_individual_parts"."color"

bricktracker/sql/individual_part/list/using_storage.sql

@@ -1,31 +0,0 @@
-SELECT
-    "bricktracker_individual_parts"."id",
-    "bricktracker_individual_parts"."part",
-    "bricktracker_individual_parts"."color",
-    "bricktracker_individual_parts"."quantity",
-    "bricktracker_individual_parts"."missing",
-    "bricktracker_individual_parts"."damaged",
-    "bricktracker_individual_parts"."checked",
-    "bricktracker_individual_parts"."description",
-    "bricktracker_individual_parts"."storage",
-    "bricktracker_individual_parts"."purchase_location",
-    "bricktracker_individual_parts"."purchase_date",
-    "bricktracker_individual_parts"."purchase_price",
-    "rebrickable_parts"."name",
-    "rebrickable_parts"."color_name",
-    "rebrickable_parts"."color_rgb",
-    "rebrickable_parts"."color_transparent",
-    "rebrickable_parts"."image",
-    "rebrickable_parts"."url",
-    "bricktracker_metadata_storages"."name" AS "storage_name",
-    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
-FROM "bricktracker_individual_parts"
-INNER JOIN "rebrickable_parts"
-    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
-    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
-LEFT JOIN "bricktracker_metadata_storages"
-    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
-LEFT JOIN "bricktracker_metadata_purchase_locations"
-    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
-WHERE "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM :storage
-ORDER BY "bricktracker_individual_parts"."part", "bricktracker_individual_parts"."color"

bricktracker/sql/individual_part/list/without_storage.sql

@@ -1,31 +0,0 @@
-SELECT
-    "bricktracker_individual_parts"."id",
-    "bricktracker_individual_parts"."part",
-    "bricktracker_individual_parts"."color",
-    "bricktracker_individual_parts"."quantity",
-    "bricktracker_individual_parts"."missing",
-    "bricktracker_individual_parts"."damaged",
-    "bricktracker_individual_parts"."checked",
-    "bricktracker_individual_parts"."description",
-    "bricktracker_individual_parts"."storage",
-    "bricktracker_individual_parts"."purchase_location",
-    "bricktracker_individual_parts"."purchase_date",
-    "bricktracker_individual_parts"."purchase_price",
-    "rebrickable_parts"."name",
-    "rebrickable_parts"."color_name",
-    "rebrickable_parts"."color_rgb",
-    "rebrickable_parts"."color_transparent",
-    "rebrickable_parts"."image",
-    "rebrickable_parts"."url",
-    "bricktracker_metadata_storages"."name" AS "storage_name",
-    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
-FROM "bricktracker_individual_parts"
-INNER JOIN "rebrickable_parts"
-    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
-    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
-LEFT JOIN "bricktracker_metadata_storages"
-    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
-LEFT JOIN "bricktracker_metadata_purchase_locations"
-    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
-WHERE "bricktracker_individual_parts"."storage" IS NULL
-ORDER BY "bricktracker_individual_parts"."part", "bricktracker_individual_parts"."color"

bricktracker/sql/individual_part/metadata/owner/update/state.sql

@@ -1,10 +0,0 @@
-INSERT INTO "bricktracker_individual_part_owners" (
-    "id",
-    "{{name}}"
-) VALUES (
-    :id,
-    :state
-)
-ON CONFLICT("id")
-DO UPDATE SET "{{name}}" = :state
-WHERE "bricktracker_individual_part_owners"."id" IS NOT DISTINCT FROM :id

bricktracker/sql/individual_part/metadata/status/update/state.sql

@@ -1,10 +0,0 @@
-INSERT INTO "bricktracker_individual_part_statuses" (
-    "id",
-    "{{name}}"
-) VALUES (
-    :id,
-    :state
-)
-ON CONFLICT("id")
-DO UPDATE SET "{{name}}" = :state
-WHERE "bricktracker_individual_part_statuses"."id" IS NOT DISTINCT FROM :id

bricktracker/sql/individual_part/metadata/tag/update/state.sql

@@ -1,10 +0,0 @@
-INSERT INTO "bricktracker_individual_part_tags" (
-    "id",
-    "{{name}}"
-) VALUES (
-    :id,
-    :state
-)
-ON CONFLICT("id")
-DO UPDATE SET "{{name}}" = :state
-WHERE "bricktracker_individual_part_tags"."id" IS NOT DISTINCT FROM :id

bricktracker/sql/individual_part/select/by_id.sql

@@ -1,31 +0,0 @@
-SELECT
-    "bricktracker_individual_parts"."id",
-    "bricktracker_individual_parts"."part",
-    "bricktracker_individual_parts"."color",
-    "bricktracker_individual_parts"."quantity",
-    "bricktracker_individual_parts"."missing",
-    "bricktracker_individual_parts"."damaged",
-    "bricktracker_individual_parts"."checked",
-    "bricktracker_individual_parts"."description",
-    "bricktracker_individual_parts"."storage",
-    "bricktracker_individual_parts"."purchase_location",
-    "bricktracker_individual_parts"."purchase_date",
-    "bricktracker_individual_parts"."purchase_price",
-    "bricktracker_individual_parts"."lot_id",
-    "rebrickable_parts"."name",
-    "rebrickable_parts"."color_name",
-    "rebrickable_parts"."color_rgb",
-    "rebrickable_parts"."color_transparent",
-    "rebrickable_parts"."image",
-    "rebrickable_parts"."url",
-    "bricktracker_metadata_storages"."name" AS "storage_name",
-    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
-FROM "bricktracker_individual_parts"
-INNER JOIN "rebrickable_parts"
-    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
-    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
-LEFT JOIN "bricktracker_metadata_storages"
-    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
-LEFT JOIN "bricktracker_metadata_purchase_locations"
-    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
-WHERE "bricktracker_individual_parts"."id" = :id

bricktracker/sql/individual_part/update.sql

@@ -1,3 +0,0 @@
-UPDATE "bricktracker_individual_parts"
-SET "{{ field }}" = :value
-WHERE "id" = :id

bricktracker/sql/individual_part/update/checked.sql

@@ -1,3 +0,0 @@
-UPDATE "bricktracker_individual_parts"
-SET "checked" = :checked
-WHERE "id" = :id

bricktracker/sql/individual_part/update/damaged.sql

@@ -1,3 +0,0 @@
-UPDATE "bricktracker_individual_parts"
-SET "damaged" = :damaged
-WHERE "id" = :id

bricktracker/sql/individual_part/update/missing.sql

@@ -1,3 +0,0 @@
-UPDATE "bricktracker_individual_parts"
-SET "missing" = :missing
-WHERE "id" = :id

bricktracker/sql/individual_part/update_full.sql

@@ -1,9 +0,0 @@
-UPDATE "bricktracker_individual_parts"
-SET
-    "quantity" = :quantity,
-    "description" = :description,
-    "storage" = :storage,
-    "purchase_location" = :purchase_location,
-    "purchase_date" = :purchase_date,
-    "purchase_price" = :purchase_price
-WHERE "id" = :id

bricktracker/sql/individual_part_lot/delete.sql

@@ -1,15 +0,0 @@
--- Delete all individual parts associated with this lot
-DELETE FROM "bricktracker_individual_parts"
-WHERE "lot_id" = :id;
-
--- Delete lot owners
-DELETE FROM "bricktracker_individual_part_lot_owners"
-WHERE "id" = :id;
-
--- Delete lot tags
-DELETE FROM "bricktracker_individual_part_lot_tags"
-WHERE "id" = :id;
-
--- Delete the lot itself
-DELETE FROM "bricktracker_individual_part_lots"
-WHERE "id" = :id;

bricktracker/sql/individual_part_lot/insert.sql

@@ -1,19 +0,0 @@
-INSERT INTO "bricktracker_individual_part_lots" (
-    "id",
-    "name",
-    "description",
-    "created_date",
-    "storage",
-    "purchase_location",
-    "purchase_date",
-    "purchase_price"
-) VALUES (
-    :id,
-    :name,
-    :description,
-    :created_date,
-    :storage,
-    :purchase_location,
-    :purchase_date,
-    :purchase_price
-)

bricktracker/sql/individual_part_lot/list/all.sql

@@ -1,21 +0,0 @@
-SELECT
-    "bricktracker_individual_part_lots"."id",
-    "bricktracker_individual_part_lots"."name",
-    "bricktracker_individual_part_lots"."description",
-    "bricktracker_individual_part_lots"."created_date",
-    "bricktracker_individual_part_lots"."storage",
-    "bricktracker_individual_part_lots"."purchase_location",
-    "bricktracker_individual_part_lots"."purchase_date",
-    "bricktracker_individual_part_lots"."purchase_price",
-    "bricktracker_metadata_storages"."name" AS "storage_name",
-    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name",
-    COUNT("bricktracker_individual_parts"."id") AS "part_count"
-FROM "bricktracker_individual_part_lots"
-LEFT JOIN "bricktracker_metadata_storages"
-    ON "bricktracker_individual_part_lots"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
-LEFT JOIN "bricktracker_metadata_purchase_locations"
-    ON "bricktracker_individual_part_lots"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
-LEFT JOIN "bricktracker_individual_parts"
-    ON "bricktracker_individual_part_lots"."id" = "bricktracker_individual_parts"."lot_id"
-GROUP BY "bricktracker_individual_part_lots"."id"
-ORDER BY "bricktracker_individual_part_lots"."created_date" DESC

bricktracker/sql/individual_part_lot/list/parts.sql

@@ -1,26 +0,0 @@
-SELECT
-    "bricktracker_individual_parts"."id",
-    "bricktracker_individual_parts"."part",
-    "bricktracker_individual_parts"."color",
-    "bricktracker_individual_parts"."quantity",
-    "bricktracker_individual_parts"."missing",
-    "bricktracker_individual_parts"."damaged",
-    "bricktracker_individual_parts"."checked",
-    "bricktracker_individual_parts"."description",
-    "bricktracker_individual_parts"."storage",
-    "bricktracker_individual_parts"."purchase_location",
-    "bricktracker_individual_parts"."purchase_date",
-    "bricktracker_individual_parts"."purchase_price",
-    "bricktracker_individual_parts"."lot_id",
-    "rebrickable_parts"."name",
-    "rebrickable_parts"."color_name",
-    "rebrickable_parts"."color_rgb",
-    "rebrickable_parts"."color_transparent",
-    "rebrickable_parts"."image",
-    "rebrickable_parts"."url"
-FROM "bricktracker_individual_parts"
-INNER JOIN "rebrickable_parts"
-    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
-    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
-WHERE "bricktracker_individual_parts"."lot_id" = :lot_id
-ORDER BY "rebrickable_parts"."name" ASC, "bricktracker_individual_parts"."color" ASC

bricktracker/sql/individual_part_lot/select/by_id.sql

@@ -1,17 +0,0 @@
-SELECT
-    "bricktracker_individual_part_lots"."id",
-    "bricktracker_individual_part_lots"."name",
-    "bricktracker_individual_part_lots"."description",
-    "bricktracker_individual_part_lots"."created_date",
-    "bricktracker_individual_part_lots"."storage",
-    "bricktracker_individual_part_lots"."purchase_location",
-    "bricktracker_individual_part_lots"."purchase_date",
-    "bricktracker_individual_part_lots"."purchase_price",
-    "bricktracker_metadata_storages"."name" AS "storage_name",
-    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
-FROM "bricktracker_individual_part_lots"
-LEFT JOIN "bricktracker_metadata_storages"
-    ON "bricktracker_individual_part_lots"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
-LEFT JOIN "bricktracker_metadata_purchase_locations"
-    ON "bricktracker_individual_part_lots"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
-WHERE "bricktracker_individual_part_lots"."id" = :id

bricktracker/sql/migrations/0019.sql

@@ -1,4 +1,4 @@
--- Migration 0019: Performance optimization indexes
+-- description: Performance optimization indexes
 
 -- High-impact composite index for problem parts aggregation
 -- Used in set listings, statistics, and problem reports

bricktracker/sql/migrations/0020.sql

@@ -1,132 +1,58 @@
--- description: Add individual minifigures and individual parts tables
+-- description: Change set number column from INTEGER to TEXT to support alphanumeric set numbers
 
--- Individual minifigures table - tracks individual minifigures not associated with sets
-CREATE TABLE IF NOT EXISTS "bricktracker_individual_minifigures" (
-    "id" TEXT NOT NULL,
-    "figure" TEXT NOT NULL,
-    "quantity" INTEGER NOT NULL DEFAULT 1,
-    "description" TEXT,
-    "storage" TEXT, -- Storage bin location
-    "purchase_date" REAL, -- Purchase date
-    "purchase_location" TEXT, -- Purchase location
-    "purchase_price" REAL, -- Purchase price
-    PRIMARY KEY("id"),
-    FOREIGN KEY("figure") REFERENCES "rebrickable_minifigures"("figure"),
-    FOREIGN KEY("storage") REFERENCES "bricktracker_metadata_storages"("id"),
-    FOREIGN KEY("purchase_location") REFERENCES "bricktracker_metadata_purchase_locations"("id")
+-- Temporarily disable foreign key constraints for this migration
+-- This is necessary because we're recreating a table that other tables reference
+-- We verify integrity at the end to ensure safety
+PRAGMA foreign_keys=OFF;
+
+BEGIN TRANSACTION;
+
+-- Create new table with TEXT number column
+CREATE TABLE "rebrickable_sets_new" (
+    "set" TEXT NOT NULL,
+    "number" TEXT NOT NULL,
+    "version" INTEGER NOT NULL,
+    "name" TEXT NOT NULL,
+    "year" INTEGER NOT NULL,
+    "theme_id" INTEGER NOT NULL,
+    "number_of_parts" INTEGER NOT NULL,
+    "image" TEXT,
+    "url" TEXT,
+    "last_modified" TEXT,
+    PRIMARY KEY("set")
 );
 
--- Individual minifigure statuses
-CREATE TABLE IF NOT EXISTS "bricktracker_individual_minifigure_statuses" (
-    "id" TEXT NOT NULL,
-    "status_minifigures_collected" BOOLEAN NOT NULL DEFAULT 0,
-    "status_set_checked" BOOLEAN NOT NULL DEFAULT 0,
-    "status_set_collected" BOOLEAN NOT NULL DEFAULT 0,
-    PRIMARY KEY("id"),
-    FOREIGN KEY("id") REFERENCES "bricktracker_individual_minifigures"("id")
-);
+-- Copy all data from old table to new table
+-- Cast INTEGER number to TEXT explicitly
+INSERT INTO "rebrickable_sets_new"
+SELECT
+    "set",
+    CAST("number" AS TEXT),
+    "version",
+    "name",
+    "year",
+    "theme_id",
+    "number_of_parts",
+    "image",
+    "url",
+    "last_modified"
+FROM "rebrickable_sets";
 
--- Individual minifigure owners
-CREATE TABLE IF NOT EXISTS "bricktracker_individual_minifigure_owners" (
-    "id" TEXT NOT NULL,
-    PRIMARY KEY("id"),
-    FOREIGN KEY("id") REFERENCES "bricktracker_individual_minifigures"("id")
-);
+-- Drop old table
+DROP TABLE "rebrickable_sets";
 
--- Individual minifigure tags
-CREATE TABLE IF NOT EXISTS "bricktracker_individual_minifigure_tags" (
-    "id" TEXT NOT NULL,
-    PRIMARY KEY("id"),
-    FOREIGN KEY("id") REFERENCES "bricktracker_individual_minifigures"("id")
-);
+-- Rename new table to original name
+ALTER TABLE "rebrickable_sets_new" RENAME TO "rebrickable_sets";
 
--- Parts table for individual minifigures - tracks constituent parts
-CREATE TABLE IF NOT EXISTS "bricktracker_individual_minifigure_parts" (
-    "id" TEXT NOT NULL,
-    "part" TEXT NOT NULL,
-    "color" INTEGER NOT NULL,
-    "spare" BOOLEAN NOT NULL,
-    "quantity" INTEGER NOT NULL,
-    "element" INTEGER,
-    "rebrickable_inventory" INTEGER NOT NULL,
-    "missing" INTEGER NOT NULL DEFAULT 0,
-    "damaged" INTEGER NOT NULL DEFAULT 0,
-    "checked" BOOLEAN DEFAULT 0,
-    PRIMARY KEY("id", "part", "color", "spare"),
-    FOREIGN KEY("id") REFERENCES "bricktracker_individual_minifigures"("id"),
-    FOREIGN KEY("part", "color") REFERENCES "rebrickable_parts"("part", "color_id")
-);
+-- Recreate the index
+CREATE INDEX IF NOT EXISTS idx_rebrickable_sets_number_version
+ON rebrickable_sets(number, version);
 
--- Individual parts table - tracks individual parts not associated with sets
-CREATE TABLE IF NOT EXISTS "bricktracker_individual_parts" (
-    "id" TEXT NOT NULL,
-    "part" TEXT NOT NULL,
-    "color" INTEGER NOT NULL,
-    "quantity" INTEGER NOT NULL DEFAULT 1,
-    "description" TEXT,
-    "storage" TEXT, -- Storage bin location
-    "purchase_date" REAL, -- Purchase date
-    "purchase_location" TEXT, -- Purchase location
-    "purchase_price" REAL, -- Purchase price
-    PRIMARY KEY("id"),
-    FOREIGN KEY("part", "color") REFERENCES "rebrickable_parts"("part", "color_id"),
-    FOREIGN KEY("storage") REFERENCES "bricktracker_metadata_storages"("id"),
-    FOREIGN KEY("purchase_location") REFERENCES "bricktracker_metadata_purchase_locations"("id")
-);
+-- Verify foreign key integrity before committing
+-- This ensures we haven't broken any references
+PRAGMA foreign_key_check;
 
--- Individual part owners
-CREATE TABLE IF NOT EXISTS "bricktracker_individual_part_owners" (
-    "id" TEXT NOT NULL,
-    PRIMARY KEY("id"),
-    FOREIGN KEY("id") REFERENCES "bricktracker_individual_parts"("id")
-);
+COMMIT;
 
--- Individual part tags
-CREATE TABLE IF NOT EXISTS "bricktracker_individual_part_tags" (
-    "id" TEXT NOT NULL,
-    PRIMARY KEY("id"),
-    FOREIGN KEY("id") REFERENCES "bricktracker_individual_parts"("id")
-);
-
--- Individual part statuses
-CREATE TABLE IF NOT EXISTS "bricktracker_individual_part_statuses" (
-    "id" TEXT NOT NULL,
-    "status_minifigures_collected" BOOLEAN NOT NULL DEFAULT 0,
-    "status_set_checked" BOOLEAN NOT NULL DEFAULT 0,
-    "status_set_collected" BOOLEAN NOT NULL DEFAULT 0,
-    PRIMARY KEY("id"),
-    FOREIGN KEY("id") REFERENCES "bricktracker_individual_parts"("id")
-);
-
--- Indexes for individual minifigures
-CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_minifigures_figure
-ON bricktracker_individual_minifigures(figure);
-
-CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_minifigures_storage
-ON bricktracker_individual_minifigures(storage);
-
-CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_minifigures_purchase_location
-ON bricktracker_individual_minifigures(purchase_location);
-
-CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_minifigures_purchase_date
-ON bricktracker_individual_minifigures(purchase_date);
-
--- Indexes for individual minifigure parts
-CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_minifigure_parts_id_missing_damaged
-ON bricktracker_individual_minifigure_parts(id, missing, damaged);
-
-CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_minifigure_parts_part_color
-ON bricktracker_individual_minifigure_parts(part, color);
-
--- Indexes for individual parts
-CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_parts_part_color
-ON bricktracker_individual_parts(part, color);
-
-CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_parts_storage
-ON bricktracker_individual_parts(storage);
-
-CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_parts_purchase_location
-ON bricktracker_individual_parts(purchase_location);
-
-CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_parts_purchase_date
-ON bricktracker_individual_parts(purchase_date);
+-- Re-enable foreign key constraints
+PRAGMA foreign_keys=ON;

bricktracker/sql/migrations/0021.sql

@@ -1,15 +0,0 @@
--- description: Populate missing image_id values in rebrickable_parts from image URLs
--- Extract image_id from image URL for records with 'elements/' path
--- Note: The url_for_image() method now handles extraction on-the-fly for missing values,
--- so this migration only needs to handle the common case to improve performance
-
--- For images with 'elements/' in the path, extract the element ID (e.g., 300126 from .../elements/300126.jpg)
-UPDATE "rebrickable_parts"
-SET "image_id" = SUBSTR(
-    "image",
-    INSTR("image", 'elements/') + 9,
-    INSTR(SUBSTR("image", INSTR("image", 'elements/') + 9), '.') - 1
-)
-WHERE "image" IS NOT NULL
-  AND ("image_id" IS NULL OR "image_id" = '')
-  AND "image" LIKE '%elements/%';

bricktracker/sql/migrations/0022.sql

@@ -1,16 +0,0 @@
--- description: Create rebrickable_colors translation table for BrickLink color ID mapping
--- This table caches color information from Rebrickable API to avoid repeated API calls
--- and provides mapping between Rebrickable and BrickLink color IDs
-
-CREATE TABLE IF NOT EXISTS "rebrickable_colors" (
-    "color_id" INTEGER PRIMARY KEY,
-    "name" TEXT NOT NULL,
-    "rgb" TEXT,
-    "is_trans" BOOLEAN,
-    "bricklink_color_id" INTEGER,
-    "bricklink_color_name" TEXT
-);
-
--- Create index for faster lookups
-CREATE INDEX IF NOT EXISTS "idx_rebrickable_colors_bricklink"
-ON "rebrickable_colors"("bricklink_color_id");

bricktracker/sql/migrations/0023.sql

@@ -1,54 +0,0 @@
--- description: Add individual part lots system for bulk/cart adding of parts
-
-BEGIN TRANSACTION;
-
--- Create individual part lots table
-CREATE TABLE IF NOT EXISTS "bricktracker_individual_part_lots" (
-    "id" TEXT NOT NULL PRIMARY KEY,
-    "name" TEXT,
-    "description" TEXT,
-    "created_date" REAL NOT NULL,
-    "storage" TEXT,
-    "purchase_location" TEXT,
-    "purchase_date" REAL,
-    "purchase_price" REAL,
-    FOREIGN KEY("storage") REFERENCES "bricktracker_metadata_storages"("id") ON DELETE SET NULL,
-    FOREIGN KEY("purchase_location") REFERENCES "bricktracker_metadata_purchase_locations"("id") ON DELETE SET NULL
-);
-
--- Create index for faster lookups
-CREATE INDEX IF NOT EXISTS "idx_individual_part_lots_created_date"
-ON "bricktracker_individual_part_lots"("created_date");
-
--- Add missing/damaged/checked fields to individual parts table
-ALTER TABLE "bricktracker_individual_parts"
-ADD COLUMN "missing" INTEGER NOT NULL DEFAULT 0;
-
-ALTER TABLE "bricktracker_individual_parts"
-ADD COLUMN "damaged" INTEGER NOT NULL DEFAULT 0;
-
-ALTER TABLE "bricktracker_individual_parts"
-ADD COLUMN "checked" BOOLEAN NOT NULL DEFAULT 0;
-
--- Add lot_id column to individual parts table
-ALTER TABLE "bricktracker_individual_parts"
-ADD COLUMN "lot_id" TEXT;
-
-CREATE INDEX IF NOT EXISTS "idx_individual_parts_lot_id"
-ON "bricktracker_individual_parts"("lot_id");
-
--- Create lot owners junction table
-CREATE TABLE IF NOT EXISTS "bricktracker_individual_part_lot_owners" (
-    "id" TEXT NOT NULL,
-    PRIMARY KEY("id"),
-    FOREIGN KEY("id") REFERENCES "bricktracker_individual_part_lots"("id") ON DELETE CASCADE
-);
-
--- Create lot tags junction table
-CREATE TABLE IF NOT EXISTS "bricktracker_individual_part_lot_tags" (
-    "id" TEXT NOT NULL,
-    PRIMARY KEY("id"),
-    FOREIGN KEY("id") REFERENCES "bricktracker_individual_part_lots"("id") ON DELETE CASCADE
-);
-
-COMMIT;

bricktracker/sql/migrations/0024.sql

@@ -1,13 +0,0 @@
--- description: Add missing indexes for individual part lots optimization
-
-BEGIN TRANSACTION;
-
--- Add storage index for lots table (for filtering by storage)
-CREATE INDEX IF NOT EXISTS "idx_individual_part_lots_storage"
-ON "bricktracker_individual_part_lots"("storage");
-
--- Add purchase location index for lots table (for filtering by purchase location)
-CREATE INDEX IF NOT EXISTS "idx_individual_part_lots_purchase_location"
-ON "bricktracker_individual_part_lots"("purchase_location");
-
-COMMIT;

bricktracker/sql/minifigure/base/base.sql

@@ -1,11 +1,10 @@
--- Combined query for both set-based and individual minifigures
 SELECT
-    "combined"."quantity",
-    "combined"."figure",
-    "combined"."number",
-    "combined"."number_of_parts",
-    "combined"."name",
-    "combined"."image",
+    "bricktracker_minifigures"."quantity",
+    "rebrickable_minifigures"."figure",
+    "rebrickable_minifigures"."number",
+    "rebrickable_minifigures"."number_of_parts",
+    "rebrickable_minifigures"."name",
+    "rebrickable_minifigures"."image",
     {% block total_missing %}
     NULL AS "total_missing", -- dummy for order: total_missing
     {% endblock %}
@@ -16,44 +15,12 @@ SELECT
     NULL AS "total_quantity", -- dummy for order: total_quantity
     {% endblock %}
     {% block total_sets %}
-    NULL AS "total_sets", -- dummy for order: total_sets
+    NULL AS "total_sets" -- dummy for order: total_sets
     {% endblock %}
-    {% block total_individual %}
-    NULL AS "total_individual" -- dummy for order: total_individual
-    {% endblock %}
-FROM (
-    -- Set-based minifigures
-    SELECT
-        "bricktracker_minifigures"."id",
-        "bricktracker_minifigures"."quantity",
-        "rebrickable_minifigures"."figure",
-        "rebrickable_minifigures"."number",
-        "rebrickable_minifigures"."number_of_parts",
-        "rebrickable_minifigures"."name",
-        "rebrickable_minifigures"."image",
-        "bricktracker_minifigures"."rowid" AS "rowid",
-        'set' AS "source_type"
-    FROM "bricktracker_minifigures"
-    INNER JOIN "rebrickable_minifigures"
-    ON "bricktracker_minifigures"."figure" IS NOT DISTINCT FROM "rebrickable_minifigures"."figure"
+FROM "bricktracker_minifigures"
 
-    UNION ALL
-
-    -- Individual minifigures
-    SELECT
-        "bricktracker_individual_minifigures"."id",
-        "bricktracker_individual_minifigures"."quantity",
-        "rebrickable_minifigures"."figure",
-        "rebrickable_minifigures"."number",
-        "rebrickable_minifigures"."number_of_parts",
-        "rebrickable_minifigures"."name",
-        "rebrickable_minifigures"."image",
-        "bricktracker_individual_minifigures"."rowid" AS "rowid",
-        'individual' AS "source_type"
-    FROM "bricktracker_individual_minifigures"
-    INNER JOIN "rebrickable_minifigures"
-    ON "bricktracker_individual_minifigures"."figure" IS NOT DISTINCT FROM "rebrickable_minifigures"."figure"
-) AS "combined"
+INNER JOIN "rebrickable_minifigures"
+ON "bricktracker_minifigures"."figure" IS NOT DISTINCT FROM "rebrickable_minifigures"."figure"
 
 {% block join %}{% endblock %}
 
@@ -61,6 +28,8 @@ FROM (
 
 {% block group %}{% endblock %}
 
+{% block having %}{% endblock %}
+
 {% if order %}
 ORDER BY {{ order }}
 {% endif %}

bricktracker/sql/minifigure/list/all.sql

@@ -9,22 +9,24 @@ SUM(IFNULL("problem_join"."total_damaged", 0)) AS "total_damaged",
 {% endblock %}
 
 {% block total_quantity %}
-SUM(IFNULL("combined"."quantity", 0)) AS "total_quantity",
+SUM(IFNULL("bricktracker_minifigures"."quantity", 0)) AS "total_quantity",
 {% endblock %}
 
 {% block total_sets %}
-SUM(CASE WHEN "combined"."source_type" = 'set' THEN 1 ELSE 0 END) AS "total_sets",
-{% endblock %}
-
-{% block total_individual %}
-SUM(CASE WHEN "combined"."source_type" = 'individual' THEN 1 ELSE 0 END) AS "total_individual"
+IFNULL(COUNT("bricktracker_minifigures"."id"), 0) AS "total_sets"
 {% endblock %}
 
 {% block join %}
+{% if theme_id or year %}
+-- Join with sets for theme/year filtering
+INNER JOIN "bricktracker_sets" AS "filter_sets"
+ON "bricktracker_minifigures"."id" IS NOT DISTINCT FROM "filter_sets"."id"
+INNER JOIN "rebrickable_sets" AS "filter_rs"
+ON "filter_sets"."set" IS NOT DISTINCT FROM "filter_rs"."set"
+{% endif %}
+
 -- LEFT JOIN + SELECT to avoid messing the total
--- Combine parts from both set-based and individual minifigures
 LEFT JOIN (
-    -- Set-based minifigure parts
     SELECT
         "bricktracker_parts"."id",
         "bricktracker_parts"."figure",
@@ -35,33 +37,38 @@ LEFT JOIN (
     GROUP BY
         "bricktracker_parts"."id",
         "bricktracker_parts"."figure"
-
-    UNION ALL
-
-    -- Individual minifigure parts
-    SELECT
-        "bricktracker_individual_minifigure_parts"."id",
-        "combined"."figure",
-        SUM("bricktracker_individual_minifigure_parts"."missing") AS "total_missing",
-        SUM("bricktracker_individual_minifigure_parts"."damaged") AS "total_damaged"
-    FROM "bricktracker_individual_minifigure_parts"
-    INNER JOIN "bricktracker_individual_minifigures" ON "bricktracker_individual_minifigure_parts"."id" = "bricktracker_individual_minifigures"."id"
-    INNER JOIN "rebrickable_minifigures" AS "combined" ON "bricktracker_individual_minifigures"."figure" = "combined"."figure"
-    GROUP BY
-        "bricktracker_individual_minifigure_parts"."id",
-        "combined"."figure"
 ) "problem_join"
-ON "combined"."id" IS NOT DISTINCT FROM "problem_join"."id"
-AND "combined"."figure" IS NOT DISTINCT FROM "problem_join"."figure"
+ON "bricktracker_minifigures"."id" IS NOT DISTINCT FROM "problem_join"."id"
+AND "rebrickable_minifigures"."figure" IS NOT DISTINCT FROM "problem_join"."figure"
 {% endblock %}
 
 {% block where %}
+WHERE 1=1
+{% if theme_id and theme_id != 'all' %}
+AND "filter_rs"."theme_id" = {{ theme_id }}
+{% endif %}
+{% if year and year != 'all' %}
+AND "filter_rs"."year" = {{ year }}
+{% endif %}
 {% if search_query %}
-WHERE (LOWER("combined"."name") LIKE LOWER('%{{ search_query }}%'))
+AND (LOWER("rebrickable_minifigures"."name") LIKE LOWER('%{{ search_query }}%'))
+{% endif %}
+{% endblock %}
+
+{% block having %}
+{% if problems_filter %}
+HAVING 1=1
+{% if problems_filter == 'missing' %}
+AND SUM(IFNULL("problem_join"."total_missing", 0)) > 0
+{% elif problems_filter == 'damaged' %}
+AND SUM(IFNULL("problem_join"."total_damaged", 0)) > 0
+{% elif problems_filter == 'both' %}
+AND SUM(IFNULL("problem_join"."total_missing", 0)) > 0 AND SUM(IFNULL("problem_join"."total_damaged", 0)) > 0
+{% endif %}
 {% endif %}
 {% endblock %}
 
 {% block group %}
 GROUP BY
-    "combined"."figure"
+    "rebrickable_minifigures"."figure"
 {% endblock %}

bricktracker/sql/minifigure/list/all_by_owner.sql

@@ -10,53 +10,35 @@ SUM(IFNULL("problem_join"."total_damaged", 0)) AS "total_damaged",
 
 {% block total_quantity %}
 {% if owner_id and owner_id != 'all' %}
-SUM(CASE
-    WHEN "combined"."source_type" = 'set' AND "set_owners"."owner_{{ owner_id }}" = 1 THEN IFNULL("combined"."quantity", 0)
-    WHEN "combined"."source_type" = 'individual' AND "individual_owners"."owner_{{ owner_id }}" = 1 THEN IFNULL("combined"."quantity", 0)
-    ELSE 0
-END) AS "total_quantity",
+SUM(CASE WHEN "bricktracker_set_owners"."owner_{{ owner_id }}" = 1 THEN IFNULL("bricktracker_minifigures"."quantity", 0) ELSE 0 END) AS "total_quantity",
 {% else %}
-SUM(IFNULL("combined"."quantity", 0)) AS "total_quantity",
+SUM(IFNULL("bricktracker_minifigures"."quantity", 0)) AS "total_quantity",
 {% endif %}
 {% endblock %}
 
 {% block total_sets %}
 {% if owner_id and owner_id != 'all' %}
-SUM(CASE
-    WHEN "combined"."source_type" = 'set' AND "set_owners"."owner_{{ owner_id }}" = 1 THEN 1
-    ELSE 0
-END) AS "total_sets",
+COUNT(CASE WHEN "bricktracker_set_owners"."owner_{{ owner_id }}" = 1 THEN "bricktracker_minifigures"."id" ELSE NULL END) AS "total_sets"
 {% else %}
-SUM(CASE WHEN "combined"."source_type" = 'set' THEN 1 ELSE 0 END) AS "total_sets",
-{% endif %}
-{% endblock %}
-
-{% block total_individual %}
-{% if owner_id and owner_id != 'all' %}
-SUM(CASE
-    WHEN "combined"."source_type" = 'individual' AND "individual_owners"."owner_{{ owner_id }}" = 1 THEN 1
-    ELSE 0
-END) AS "total_individual"
-{% else %}
-SUM(CASE WHEN "combined"."source_type" = 'individual' THEN 1 ELSE 0 END) AS "total_individual"
+COUNT("bricktracker_minifigures"."id") AS "total_sets"
 {% endif %}
 {% endblock %}
 
 {% block join %}
--- Join with set owners for set-based minifigures
-LEFT JOIN "bricktracker_sets"
-ON "combined"."id" = "bricktracker_sets"."id" AND "combined"."source_type" = 'set'
+-- Join with sets to get owner information
+INNER JOIN "bricktracker_sets"
+ON "bricktracker_minifigures"."id" IS NOT DISTINCT FROM "bricktracker_sets"."id"
 
-LEFT JOIN "bricktracker_set_owners" AS "set_owners"
-ON "bricktracker_sets"."id" = "set_owners"."id"
+-- Join with rebrickable sets for theme/year filtering
+INNER JOIN "rebrickable_sets"
+ON "bricktracker_sets"."set" IS NOT DISTINCT FROM "rebrickable_sets"."set"
 
--- Join with individual minifigure owners for individual minifigures
-LEFT JOIN "bricktracker_individual_minifigure_owners" AS "individual_owners"
-ON "combined"."id" = "individual_owners"."id" AND "combined"."source_type" = 'individual'
+-- Left join with set owners (using dynamic columns)
+LEFT JOIN "bricktracker_set_owners"
+ON "bricktracker_sets"."id" IS NOT DISTINCT FROM "bricktracker_set_owners"."id"
 
 -- LEFT JOIN + SELECT to avoid messing the total
 LEFT JOIN (
-    -- Set-based minifigure parts
     SELECT
         "bricktracker_parts"."id",
         "bricktracker_parts"."figure",
@@ -69,47 +51,31 @@ LEFT JOIN (
         {% endif %}
     FROM "bricktracker_parts"
     INNER JOIN "bricktracker_sets" AS "parts_sets"
-    ON "bricktracker_parts"."id" = "parts_sets"."id"
+    ON "bricktracker_parts"."id" IS NOT DISTINCT FROM "parts_sets"."id"
     LEFT JOIN "bricktracker_set_owners" AS "owner_parts"
-    ON "parts_sets"."id" = "owner_parts"."id"
+    ON "parts_sets"."id" IS NOT DISTINCT FROM "owner_parts"."id"
     WHERE "bricktracker_parts"."figure" IS NOT NULL
     GROUP BY
         "bricktracker_parts"."id",
         "bricktracker_parts"."figure"
-
-    UNION ALL
-
-    -- Individual minifigure parts
-    SELECT
-        "bricktracker_individual_minifigure_parts"."id",
-        "bricktracker_individual_minifigures"."figure",
-        {% if owner_id and owner_id != 'all' %}
-        SUM(CASE WHEN "owner_individual"."owner_{{ owner_id }}" = 1 THEN "bricktracker_individual_minifigure_parts"."missing" ELSE 0 END) AS "total_missing",
-        SUM(CASE WHEN "owner_individual"."owner_{{ owner_id }}" = 1 THEN "bricktracker_individual_minifigure_parts"."damaged" ELSE 0 END) AS "total_damaged"
-        {% else %}
-        SUM("bricktracker_individual_minifigure_parts"."missing") AS "total_missing",
-        SUM("bricktracker_individual_minifigure_parts"."damaged") AS "total_damaged"
-        {% endif %}
-    FROM "bricktracker_individual_minifigure_parts"
-    INNER JOIN "bricktracker_individual_minifigures"
-    ON "bricktracker_individual_minifigure_parts"."id" = "bricktracker_individual_minifigures"."id"
-    LEFT JOIN "bricktracker_individual_minifigure_owners" AS "owner_individual"
-    ON "bricktracker_individual_minifigures"."id" = "owner_individual"."id"
-    GROUP BY
-        "bricktracker_individual_minifigure_parts"."id",
-        "bricktracker_individual_minifigures"."figure"
 ) "problem_join"
-ON "combined"."id" = "problem_join"."id"
-AND "combined"."figure" = "problem_join"."figure"
+ON "bricktracker_minifigures"."id" IS NOT DISTINCT FROM "problem_join"."id"
+AND "rebrickable_minifigures"."figure" IS NOT DISTINCT FROM "problem_join"."figure"
 {% endblock %}
 
 {% block where %}
 {% set conditions = [] %}
 {% if owner_id and owner_id != 'all' %}
-  {% set _ = conditions.append('(("combined"."source_type" = \'set\' AND "set_owners"."owner_' ~ owner_id ~ '" = 1) OR ("combined"."source_type" = \'individual\' AND "individual_owners"."owner_' ~ owner_id ~ '" = 1))') %}
+  {% set _ = conditions.append('"bricktracker_set_owners"."owner_' ~ owner_id ~ '" = 1') %}
+{% endif %}
+{% if theme_id and theme_id != 'all' %}
+  {% set _ = conditions.append('"rebrickable_sets"."theme_id" = ' ~ theme_id) %}
+{% endif %}
+{% if year and year != 'all' %}
+  {% set _ = conditions.append('"rebrickable_sets"."year" = ' ~ year) %}
 {% endif %}
 {% if search_query %}
-  {% set _ = conditions.append('(LOWER("combined"."name") LIKE LOWER(\'%' ~ search_query ~ '%\'))') %}
+  {% set _ = conditions.append('(LOWER("rebrickable_minifigures"."name") LIKE LOWER(\'%' ~ search_query ~ '%\'))') %}
 {% endif %}
 {% if conditions %}
 WHERE {{ conditions | join(' AND ') }}
@@ -118,5 +84,18 @@ WHERE {{ conditions | join(' AND ') }}
 
 {% block group %}
 GROUP BY
-    "combined"."figure"
+    "rebrickable_minifigures"."figure"
+{% endblock %}
+
+{% block having %}
+{% if problems_filter %}
+HAVING 1=1
+{% if problems_filter == 'missing' %}
+AND SUM(IFNULL("problem_join"."total_missing", 0)) > 0
+{% elif problems_filter == 'damaged' %}
+AND SUM(IFNULL("problem_join"."total_damaged", 0)) > 0
+{% elif problems_filter == 'both' %}
+AND SUM(IFNULL("problem_join"."total_missing", 0)) > 0 AND SUM(IFNULL("problem_join"."total_damaged", 0)) > 0
+{% endif %}
+{% endif %}
 {% endblock %}

bricktracker/sql/minifigure/list/damaged_part.sql

@@ -1,59 +1,28 @@
 {% extends 'minifigure/base/base.sql' %}
 
 {% block total_damaged %}
-SUM("parts_combined"."damaged") AS "total_damaged",
+SUM("bricktracker_parts"."damaged") AS "total_damaged",
 {% endblock %}
 
 {% block join %}
--- Join with parts from both set-based and individual minifigures
-LEFT JOIN (
-    SELECT
-        "bricktracker_parts"."id",
-        "bricktracker_parts"."figure",
-        "bricktracker_parts"."damaged"
-    FROM "bricktracker_parts"
-
-    UNION ALL
-
-    SELECT
-        "bricktracker_individual_minifigure_parts"."id",
-        "bricktracker_individual_minifigures"."figure",
-        "bricktracker_individual_minifigure_parts"."damaged"
-    FROM "bricktracker_individual_minifigure_parts"
-    INNER JOIN "bricktracker_individual_minifigures"
-    ON "bricktracker_individual_minifigure_parts"."id" = "bricktracker_individual_minifigures"."id"
-) AS "parts_combined"
-ON "combined"."id" IS NOT DISTINCT FROM "parts_combined"."id"
-AND "combined"."figure" IS NOT DISTINCT FROM "parts_combined"."figure"
+LEFT JOIN "bricktracker_parts"
+ON "bricktracker_minifigures"."id" IS NOT DISTINCT FROM "bricktracker_parts"."id"
+AND "rebrickable_minifigures"."figure" IS NOT DISTINCT FROM "bricktracker_parts"."figure"
 {% endblock %}
 
 {% block where %}
-WHERE "combined"."figure" IN (
-    -- Find figures with damaged parts from both sources
-    SELECT "figure"
-    FROM (
-        SELECT "bricktracker_parts"."figure"
-        FROM "bricktracker_parts"
-        WHERE "bricktracker_parts"."part" IS NOT DISTINCT FROM :part
-        AND "bricktracker_parts"."color" IS NOT DISTINCT FROM :color
-        AND "bricktracker_parts"."figure" IS NOT NULL
-        AND "bricktracker_parts"."damaged" > 0
-
-        UNION
-
-        SELECT "bricktracker_individual_minifigures"."figure"
-        FROM "bricktracker_individual_minifigure_parts"
-        INNER JOIN "bricktracker_individual_minifigures"
-        ON "bricktracker_individual_minifigure_parts"."id" = "bricktracker_individual_minifigures"."id"
-        WHERE "bricktracker_individual_minifigure_parts"."part" IS NOT DISTINCT FROM :part
-        AND "bricktracker_individual_minifigure_parts"."color" IS NOT DISTINCT FROM :color
-        AND "bricktracker_individual_minifigure_parts"."damaged" > 0
-    ) AS "damaged_figures"
-    GROUP BY "figure"
+WHERE "rebrickable_minifigures"."figure" IN (
+    SELECT "bricktracker_parts"."figure"
+    FROM "bricktracker_parts"
+    WHERE "bricktracker_parts"."part" IS NOT DISTINCT FROM :part
+    AND "bricktracker_parts"."color" IS NOT DISTINCT FROM :color
+    AND "bricktracker_parts"."figure" IS NOT NULL
+    AND "bricktracker_parts"."damaged" > 0
+    GROUP BY "bricktracker_parts"."figure"
 )
 {% endblock %}
 
 {% block group %}
 GROUP BY
-    "combined"."figure"
+    "rebrickable_minifigures"."figure"
 {% endblock %}

bricktracker/sql/minifigure/list/from_set.sql

@@ -1,5 +1,5 @@
 {% extends 'minifigure/base/base.sql' %}
 
 {% block where %}
-WHERE "combined"."id" IS NOT DISTINCT FROM :id AND "combined"."source_type" = 'set'
+WHERE "bricktracker_minifigures"."id" IS NOT DISTINCT FROM :id
 {% endblock %}

bricktracker/sql/minifigure/list/last.sql

@@ -1,40 +1,21 @@
 {% extends 'minifigure/base/base.sql' %}
 
 {% block total_missing %}
-SUM("parts_combined"."missing") AS "total_missing",
+SUM("bricktracker_parts"."missing") AS "total_missing",
 {% endblock %}
 
 {% block total_damaged %}
-SUM("parts_combined"."damaged") AS "total_damaged",
+SUM("bricktracker_parts"."damaged") AS "total_damaged",
 {% endblock %}
 
 {% block join %}
--- Join with parts from both set-based and individual minifigures
-LEFT JOIN (
-    SELECT
-        "bricktracker_parts"."id",
-        "bricktracker_parts"."figure",
-        "bricktracker_parts"."missing",
-        "bricktracker_parts"."damaged"
-    FROM "bricktracker_parts"
-
-    UNION ALL
-
-    SELECT
-        "bricktracker_individual_minifigure_parts"."id",
-        "bricktracker_individual_minifigures"."figure",
-        "bricktracker_individual_minifigure_parts"."missing",
-        "bricktracker_individual_minifigure_parts"."damaged"
-    FROM "bricktracker_individual_minifigure_parts"
-    INNER JOIN "bricktracker_individual_minifigures"
-    ON "bricktracker_individual_minifigure_parts"."id" = "bricktracker_individual_minifigures"."id"
-) AS "parts_combined"
-ON "combined"."id" IS NOT DISTINCT FROM "parts_combined"."id"
-AND "combined"."figure" IS NOT DISTINCT FROM "parts_combined"."figure"
+LEFT JOIN "bricktracker_parts"
+ON "bricktracker_minifigures"."id" IS NOT DISTINCT FROM "bricktracker_parts"."id"
+AND "rebrickable_minifigures"."figure" IS NOT DISTINCT FROM "bricktracker_parts"."figure"
 {% endblock %}
 
 {% block group %}
 GROUP BY
-    "combined"."figure",
-    "combined"."id"
+    "rebrickable_minifigures"."figure",
+    "bricktracker_minifigures"."id"
 {% endblock %}

bricktracker/sql/minifigure/list/missing_part.sql

@@ -1,59 +1,28 @@
 {% extends 'minifigure/base/base.sql' %}
 
 {% block total_missing %}
-SUM("parts_combined"."missing") AS "total_missing",
+SUM("bricktracker_parts"."missing") AS "total_missing",
 {% endblock %}
 
 {% block join %}
--- Join with parts from both set-based and individual minifigures
-LEFT JOIN (
-    SELECT
-        "bricktracker_parts"."id",
-        "bricktracker_parts"."figure",
-        "bricktracker_parts"."missing"
-    FROM "bricktracker_parts"
-
-    UNION ALL
-
-    SELECT
-        "bricktracker_individual_minifigure_parts"."id",
-        "bricktracker_individual_minifigures"."figure",
-        "bricktracker_individual_minifigure_parts"."missing"
-    FROM "bricktracker_individual_minifigure_parts"
-    INNER JOIN "bricktracker_individual_minifigures"
-    ON "bricktracker_individual_minifigure_parts"."id" = "bricktracker_individual_minifigures"."id"
-) AS "parts_combined"
-ON "combined"."id" IS NOT DISTINCT FROM "parts_combined"."id"
-AND "combined"."figure" IS NOT DISTINCT FROM "parts_combined"."figure"
+LEFT JOIN "bricktracker_parts"
+ON "bricktracker_minifigures"."id" IS NOT DISTINCT FROM "bricktracker_parts"."id"
+AND "rebrickable_minifigures"."figure" IS NOT DISTINCT FROM "bricktracker_parts"."figure"
 {% endblock %}
 
 {% block where %}
-WHERE "combined"."figure" IN (
-    -- Find figures with missing parts from both sources
-    SELECT "figure"
-    FROM (
-        SELECT "bricktracker_parts"."figure"
-        FROM "bricktracker_parts"
-        WHERE "bricktracker_parts"."part" IS NOT DISTINCT FROM :part
-        AND "bricktracker_parts"."color" IS NOT DISTINCT FROM :color
-        AND "bricktracker_parts"."figure" IS NOT NULL
-        AND "bricktracker_parts"."missing" > 0
-
-        UNION
-
-        SELECT "bricktracker_individual_minifigures"."figure"
-        FROM "bricktracker_individual_minifigure_parts"
-        INNER JOIN "bricktracker_individual_minifigures"
-        ON "bricktracker_individual_minifigure_parts"."id" = "bricktracker_individual_minifigures"."id"
-        WHERE "bricktracker_individual_minifigure_parts"."part" IS NOT DISTINCT FROM :part
-        AND "bricktracker_individual_minifigure_parts"."color" IS NOT DISTINCT FROM :color
-        AND "bricktracker_individual_minifigure_parts"."missing" > 0
-    ) AS "missing_figures"
-    GROUP BY "figure"
+WHERE "rebrickable_minifigures"."figure" IN (
+    SELECT "bricktracker_parts"."figure"
+    FROM "bricktracker_parts"
+    WHERE "bricktracker_parts"."part" IS NOT DISTINCT FROM :part
+    AND "bricktracker_parts"."color" IS NOT DISTINCT FROM :color
+    AND "bricktracker_parts"."figure" IS NOT NULL
+    AND "bricktracker_parts"."missing" > 0
+    GROUP BY "bricktracker_parts"."figure"
 )
 {% endblock %}
 
 {% block group %}
 GROUP BY
-    "combined"."figure"
+    "rebrickable_minifigures"."figure"
 {% endblock %}

bricktracker/sql/minifigure/list/using_part.sql

@@ -1,34 +1,21 @@
 {% extends 'minifigure/base/base.sql' %}
 
 {% block total_quantity %}
-SUM("combined"."quantity") AS "total_quantity",
+SUM("bricktracker_minifigures"."quantity") AS "total_quantity",
 {% endblock %}
 
 {% block where %}
-WHERE "combined"."figure" IN (
-    -- Find figures from both set-based and individual minifigure parts
-    SELECT "figure"
-    FROM (
-        SELECT "bricktracker_parts"."figure"
-        FROM "bricktracker_parts"
-        WHERE "bricktracker_parts"."part" IS NOT DISTINCT FROM :part
-        AND "bricktracker_parts"."color" IS NOT DISTINCT FROM :color
-        AND "bricktracker_parts"."figure" IS NOT NULL
-
-        UNION
-
-        SELECT "bricktracker_individual_minifigures"."figure"
-        FROM "bricktracker_individual_minifigure_parts"
-        INNER JOIN "bricktracker_individual_minifigures"
-        ON "bricktracker_individual_minifigure_parts"."id" = "bricktracker_individual_minifigures"."id"
-        WHERE "bricktracker_individual_minifigure_parts"."part" IS NOT DISTINCT FROM :part
-        AND "bricktracker_individual_minifigure_parts"."color" IS NOT DISTINCT FROM :color
-    ) AS "parts_figures"
-    GROUP BY "figure"
+WHERE "rebrickable_minifigures"."figure" IN (
+    SELECT "bricktracker_parts"."figure"
+    FROM "bricktracker_parts"
+    WHERE "bricktracker_parts"."part" IS NOT DISTINCT FROM :part
+    AND "bricktracker_parts"."color" IS NOT DISTINCT FROM :color
+    AND "bricktracker_parts"."figure" IS NOT NULL
+    GROUP BY "bricktracker_parts"."figure"
 )
 {% endblock %}
 
 {% block group %}
 GROUP BY
-    "combined"."figure"
+    "rebrickable_minifigures"."figure"
 {% endblock %}

bricktracker/sql/minifigure/select/generic.sql

@@ -9,22 +9,16 @@ IFNULL("problem_join"."total_damaged", 0) AS "total_damaged",
 {% endblock %}
 
 {% block total_quantity %}
-SUM(IFNULL("combined"."quantity", 0)) AS "total_quantity",
+SUM(IFNULL("bricktracker_minifigures"."quantity", 0)) AS "total_quantity",
 {% endblock %}
 
 {% block total_sets %}
-IFNULL(COUNT(DISTINCT "combined"."id"), 0) AS "total_sets",
-{% endblock %}
-
-{% block total_individual %}
-IFNULL(COUNT(DISTINCT "combined"."id"), 0) AS "total_individual"
+IFNULL(COUNT(DISTINCT "bricktracker_minifigures"."id"), 0) AS "total_sets"
 {% endblock %}
 
 {% block join %}
 -- LEFT JOIN + SELECT to avoid messing the total
--- Combine parts from both set-based and individual minifigures
 LEFT JOIN (
-    -- Set-based minifigure parts
     SELECT
         "bricktracker_parts"."figure",
         SUM("bricktracker_parts"."missing") AS "total_missing",
@@ -32,27 +26,15 @@ LEFT JOIN (
     FROM "bricktracker_parts"
     WHERE "bricktracker_parts"."figure" IS NOT DISTINCT FROM :figure
     GROUP BY "bricktracker_parts"."figure"
-
-    UNION ALL
-
-    -- Individual minifigure parts
-    SELECT
-        "bricktracker_individual_minifigures"."figure",
-        SUM("bricktracker_individual_minifigure_parts"."missing") AS "total_missing",
-        SUM("bricktracker_individual_minifigure_parts"."damaged") AS "total_damaged"
-    FROM "bricktracker_individual_minifigure_parts"
-    INNER JOIN "bricktracker_individual_minifigures" ON "bricktracker_individual_minifigure_parts"."id" = "bricktracker_individual_minifigures"."id"
-    WHERE "bricktracker_individual_minifigures"."figure" IS NOT DISTINCT FROM :figure
-    GROUP BY "bricktracker_individual_minifigures"."figure"
 ) "problem_join"
-ON "combined"."figure" IS NOT DISTINCT FROM "problem_join"."figure"
+ON "rebrickable_minifigures"."figure" IS NOT DISTINCT FROM "problem_join"."figure"
 {% endblock %}
 
 {% block where %}
-WHERE "combined"."figure" IS NOT DISTINCT FROM :figure
+WHERE "rebrickable_minifigures"."figure" IS NOT DISTINCT FROM :figure
 {% endblock %}
 
 {% block group %}
 GROUP BY
-    "combined"."figure"
+    "rebrickable_minifigures"."figure"
 {% endblock %}

bricktracker/sql/minifigure/select/specific.sql

@@ -1,7 +1,6 @@
 {% extends 'minifigure/base/base.sql' %}
 
 {% block where %}
-WHERE "combined"."id" IS NOT DISTINCT FROM :id
-AND "combined"."figure" IS NOT DISTINCT FROM :figure
-AND "combined"."source_type" = 'set'
+WHERE "bricktracker_minifigures"."id" IS NOT DISTINCT FROM :id
+AND "rebrickable_minifigures"."figure" IS NOT DISTINCT FROM :figure
 {% endblock %}

bricktracker/sql/minifigure/themes/list.sql

@@ -0,0 +1,16 @@
+-- Get distinct themes from minifigures' sets
+SELECT DISTINCT
+    "rebrickable_sets"."theme_id",
+    COUNT(DISTINCT "bricktracker_minifigures"."figure") as "minifigure_count"
+FROM "bricktracker_minifigures"
+INNER JOIN "bricktracker_sets"
+ON "bricktracker_minifigures"."id" IS NOT DISTINCT FROM "bricktracker_sets"."id"
+INNER JOIN "rebrickable_sets"
+ON "bricktracker_sets"."set" IS NOT DISTINCT FROM "rebrickable_sets"."set"
+{% if owner_id and owner_id != 'all' %}
+INNER JOIN "bricktracker_set_owners"
+ON "bricktracker_sets"."id" IS NOT DISTINCT FROM "bricktracker_set_owners"."id"
+WHERE "bricktracker_set_owners"."owner_{{ owner_id }}" = 1
+{% endif %}
+GROUP BY "rebrickable_sets"."theme_id"
+ORDER BY "rebrickable_sets"."theme_id" ASC

bricktracker/sql/minifigure/years/list.sql

@@ -0,0 +1,16 @@
+-- Get distinct years from minifigures' sets
+SELECT DISTINCT
+    "rebrickable_sets"."year",
+    COUNT(DISTINCT "bricktracker_minifigures"."figure") as "minifigure_count"
+FROM "bricktracker_minifigures"
+INNER JOIN "bricktracker_sets"
+ON "bricktracker_minifigures"."id" IS NOT DISTINCT FROM "bricktracker_sets"."id"
+INNER JOIN "rebrickable_sets"
+ON "bricktracker_sets"."set" IS NOT DISTINCT FROM "rebrickable_sets"."set"
+{% if owner_id and owner_id != 'all' %}
+INNER JOIN "bricktracker_set_owners"
+ON "bricktracker_sets"."id" IS NOT DISTINCT FROM "bricktracker_set_owners"."id"
+WHERE "bricktracker_set_owners"."owner_{{ owner_id }}" = 1
+{% endif %}
+GROUP BY "rebrickable_sets"."year"
+ORDER BY "rebrickable_sets"."year" DESC