Unique Product CSV Import

Description

The Unique Product CSV Import feature enables users to upload CSV files containing product data with unique identification codes for batch processing and tracking. This system creates a single temporary wishlist from the CSV data and supports importing products from multiple e-commerce platforms (Amazon, Rakuten) while maintaining unique product groupings for advanced analysis and tracking capabilities.

Key characteristics of the unique product import system:

Single Wishlist Creation: Creates one temporary wishlist per CSV file based on first row
Bulk CSV import with comprehensive validation
Unique code-based product tracking and grouping
Multi-platform support (Amazon ASIN, Rakuten ID, JAN codes)
Duplicate detection and error handling
Temporary wishlist integration with summary unique product creation
Import history and error tracking for audit and debugging

Note: This feature imports products into temporary wishlists with unique tracking codes. For standard temp wishlist CSV import, see CSV Import/Export.

Recent Features

Enhanced Unique Product Tracking (2025-07-07)

Summary Unique Products: Added temp_summary_unique_products table for unique code tracking
Relationship Mapping: Link products to unique codes via temp_summary_unique_product_id
Batch Processing: Efficient handling of large datasets with unique code grouping
Error Management: Comprehensive error tracking with detailed validation messages

Activity Diagram

---
config:
  theme: base
  layout: dagre
  flowchart:
    curve: linear
    htmlLabels: true
  themeVariables:
    edgeLabelBackground: "transparent"
---
flowchart TB
    %% Main components
    UserRequest[User CSV Upload]
    CSVFile[CSV File]
    TempDatabase[(Temp Database)]
    
    subgraph Controllers
        UniqueProductController[UniqueProductWishlistController]
    end
    
    subgraph Services
        UniqueProductImportService(UniqueProductImportService)
        TempSummaryService(TempSummaryUniqueProductService)
    end
    
    subgraph Models
        TempWishlist[[TempWishlistToGroup]]
        TempWishlistProduct[[TempWishlistProduct]]
        TempSummaryUniqueProduct[[TempSummaryUniqueProduct]]
        ImportHistory[[ImportHistory]]
        ImportError[[ImportError]]
    end
    
    subgraph Middleware
        AuthMiddleware{AuthMiddleware}
        GroupMiddleware{GroupMiddleware}
    end
    
    UserRequest --- Step1[
        <div style='text-align: center'>
            <span style='display: inline-block; background-color: #6699cc !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>1</span>
            <p style='margin-top: 8px'>Authentication & Group Validation</p>
        </div>
    ]
    Step1 --> AuthMiddleware
    AuthMiddleware --> GroupMiddleware

    UniqueProductController --- Step2[
        <div style='text-align: center'>
            <span style='display: inline-block; background-color: #6699cc !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>2</span>
            <p style='margin-top: 8px'>File Upload & Validation</p>
        </div>
    ]
    Step2 --> CSVFile

    UniqueProductImportService --- Step3[
        <div style='text-align: center'>
            <span style='display: inline-block; background-color: #99cc66 !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>3</span>
            <p style='margin-top: 8px'>CSV Processing & Data Validation</p>
        </div>
    ]
    Step3 --> CSVFile

    UniqueProductImportService --- Step4[
        <div style='text-align: center'>
            <span style='display: inline-block; background-color: #99cc66 !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>4</span>
            <p style='margin-top: 8px'>Single Wishlist Creation & Unique Code Processing</p>
        </div>
    ]
    Step4 --> TempSummaryService

    TempSummaryService --- Step5[
        <div style='text-align: center'>
            <span style='display: inline-block; background-color: #cc66cc !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>5</span>
            <p style='margin-top: 8px'>Database Storage</p>
        </div>
    ]
    Step5 --> TempDatabase

    %% Service to Model relationships
    UniqueProductImportService -.-> TempWishlist
    UniqueProductImportService -.-> TempWishlistProduct
    UniqueProductImportService -.-> ImportHistory
    UniqueProductImportService -.-> ImportError
    TempSummaryService -.-> TempSummaryUniqueProduct
    
    %% Model to Database relationships
    TempWishlist -.-> TempDatabase
    TempWishlistProduct -.-> TempDatabase
    TempSummaryUniqueProduct -.-> TempDatabase
    ImportHistory -.-> TempDatabase
    ImportError -.-> TempDatabase
    
    %% Styling
    style TempDatabase fill:#ffe6cc,stroke:#ff9900,stroke-width:2px
    style CSVFile fill:#fcd9d9,stroke:#cc3333,stroke-width:2px
    style Controllers fill:#e6f3ff
    style Services fill:#f0f8e6
    style Models fill:#fff0f5
    style Middleware fill:#f5f0ff
    style Step1 fill:transparent,stroke:transparent,stroke-width:1px
    style Step2 fill:transparent,stroke:transparent,stroke-width:1px
    style Step3 fill:transparent,stroke:transparent,stroke-width:1px
    style Step4 fill:transparent,stroke:transparent,stroke-width:1px
    style Step5 fill:transparent,stroke:transparent,stroke-width:1px

API Endpoints

1. Import Unique Product CSV

API: Import Unique Product CSV

Endpoint: POST /api/v1/general/unique-product-upload/import
Purpose: Upload and process unique product CSV file
Request: multipart/form-data with csv_file field
Response: Import summary with success/error details
Authorization: Required (user authentication + group access)

2. Import History List

API: Get Import History List

Endpoint: GET /api/v1/general/temp-wishlist-to-group/import-history/list
Purpose: Get paginated list of import histories (filtered by type=2 for unique product)
Parameters: per_page, page, type (ImportHistoryType::UniqueProduct = 2)
Response: Paginated import history records
Authorization: Required

3. Import History Details

API: Get Import History Details

Endpoint: GET /api/v1/general/temp-wishlist-to-group/import-history/{id}
Purpose: Get specific import history details
Response: Detailed import history record with metadata
Authorization: Required

4. Download Error CSV

API: Download Error CSV

Endpoint: GET /api/v1/general/temp-wishlist-to-group/import-history/{id}/download-errors
Purpose: Download CSV file containing import errors for specific history
Response: CSV file with error details
Authorization: Required

5. Import Error List

API: Get Import Error List

Endpoint: GET /api/v1/general/temp-wishlist-to-group/import-history/{id}/import-error/list
Purpose: Get paginated list of import errors for specific history
Parameters: per_page, page
Response: Paginated import error records
Authorization: Required

Note: Import history and error management APIs are shared with standard temp wishlist import, differentiated by ImportHistoryType enum (1=Standard, 2=UniqueProduct).

Database Schema

Database Related Tables & Fields

Database: gb_console

erDiagram
    temp_wishlist_to_groups {
        bigint id PK
        bigint user_id "User who created the temp wishlist"
        bigint group_id "Group ownership for access control"
        string name "Temp wishlist name from CSV header"
        string slug "Auto-generated unique identifier"
    }
    
    temp_summary_unique_products {
        bigint id PK
        bigint temp_wishlist_to_group_id FK "Reference to temp_wishlist_to_groups"
        string code "Unique identifier code for product grouping"
    }
    
    temp_wishlist_products {
        bigint id PK
        bigint temp_wishlist_to_group_id FK "Reference to temp_wishlist_to_groups"
        bigint temp_summary_unique_product_id FK "Reference to temp_summary_unique_products"
        string input "Product identifier (ASIN, Rakuten ID, or JAN)"
        string input_type "Type: 'asin', 'rakuten_id', 'jan'"
        string product_url "Auto-generated product URL"
        bigint mall_id "Mall identifier (Amazon or Rakuten)"
        string pair_id "Row identifier from CSV (row_1, row_2, etc.)"
        string unique_code "Unique code from CSV for grouping"
    }
    
    import_histories {
        bigint id PK
        bigint user_id "User who performed the import"
        bigint group_id "Group context for the import"
        string file_name "Original CSV filename"
        integer type "1=Standard, 2=UniqueProduct (ImportHistoryType enum)"
        integer status "Import status: success, failed, partial"
    }
    
    import_errors {
        bigint id PK
        bigint import_history_id FK "Reference to import_histories"
        integer error_line "CSV row number where error occurred"
        string header "Field header that caused the error"
        string value "Field value that caused the error"
        text error_messages "JSON encoded array of error messages"
    }

    temp_wishlist_to_groups ||--o{ temp_summary_unique_products : has
    temp_summary_unique_products ||--o{ temp_wishlist_products : has
    temp_wishlist_to_groups ||--o{ temp_wishlist_products : has
    import_histories ||--o{ import_errors : has

CSV Format Specification

Required Headers

The CSV file must contain the following headers in exact order:

Header (Translation Key)	Japanese Header	Description	Example	Validation
`データセット名` (`general.csv_import.headers.temp_wishlist_name`)	データセット名	Name for the temporary wishlist	"スマートフォン分析"	Required, max 50 chars, no emojis
`楽天ID` (`general.csv_import.headers.rakuten_id`)	楽天ID	Rakuten product identifier	"abc123def456"	Optional, Rakuten ID format
`JANコード` (`general.csv_import.headers.jan`)	JANコード	JAN (Japanese Article Number)	"4901234567890"	Optional, JAN format
`ASIN` (`general.csv_import.headers.asin`)	ASIN	Amazon Standard Identification Number	"B08N5WRWNW"	Optional, Amazon ASIN format
`独自ID` (`general.csv_import.headers.unique_id`)	独自ID	Unique identifier for product grouping	"GROUP_001"	Required, string format

Sample CSV Format

データセット名,独自ID,楽天ID,JANコード,ASIN
テストウィッシュリスト1,PROD001,netbaby:404201,4901234567890,
,PROD002,biccamera:4961310162719,4901234567892,B08N5WRWNX
,PROD003,,,B08N5WRWNY

Validation Rules

File-Level Validation

File Type: Must be .csv format
File Size: Maximum file size limit (configurable)
Character Encoding: UTF-8 encoding support

Row-Level Validation

データセット名 (temp_wishlist_name): Required in first row only (used for single wishlist creation), max 50 characters, no emoji characters
Product IDs: At least one product ID (楽天ID, JANコード, or ASIN) must be provided per row
独自ID (unique_id): Required for all rows, used for product grouping and tracking
Duplicate Detection: Same product ID within the same temp wishlist is flagged as duplicate

Important: The データセット名 from the first row only is used to create the single temporary wishlist. All subsequent rows are products added to this single wishlist.

Case Documentation

Case 1: Successful CSV Import

API: Import Unique Product CSV

Description

User uploads a properly formatted CSV file with unique product data, and the system successfully processes all rows creating temp wishlist products and summary unique products.

Sequence Diagram

sequenceDiagram
    participant Client
    participant Controller as UniqueProductWishlistController
    participant Service as UniqueProductImportService
    participant SummaryService as TempSummaryUniqueProductService
    participant TempDB as Temp Database
    participant FileSystem as File System
    
    Note over Client,FileSystem: POST /api/v1/general/unique-product-upload/import
    
    rect rgb(200, 255, 200)
    Note right of Client: Happy Case - CSV Import
    
    Client->>Controller: POST /import (multipart/form-data with csv_file)
    
    rect rgb(200, 230, 255)
    Note right of Controller: File Processing
    Controller->>Service: handle(request)
    Service->>FileSystem: Read uploaded CSV file
    FileSystem-->>Service: CSV data rows
    Service->>Service: validateFirstRow(firstRow)
    end
    
    rect rgb(200, 255, 255)
    Note right of Service: Data Processing & Validation
    Service->>Service: Create import history record
    
    loop For each CSV row
        Service->>Service: validateCSVRowWithErrors(row, rowNumber)
        Service->>Service: Extract product IDs and unique codes
        Service->>Service: Check for duplicates
    end
    end
    
    rect rgb(255, 230, 200)
    Note right of Service: Database Operations
    Service->>Service: transactionBegin()
    Service->>TempDB: Create or update temp_wishlist_to_groups
    
    Service->>SummaryService: createSummaryRecordsFromCodes(tempWishlistId, uniqueCodes)
    SummaryService->>TempDB: INSERT INTO temp_summary_unique_products
    
    Service->>TempDB: Batch INSERT temp_wishlist_products with relationships
    Service->>Service: transactionCommit()
    end
    
    Service-->>Controller: Success result with import summary
    Controller-->>Client: 200 OK with success message
    end
    
    rect rgb(255, 200, 200)
    Note right of Client: Error Handling
    
    alt Validation Errors
        rect rgb(255, 230, 230)
        Service->>TempDB: INSERT INTO import_errors
        Service-->>Controller: Partial success with error details
        Controller-->>Client: 200 OK with warnings
        end
    else File Processing Error
        rect rgb(255, 230, 230)
        Service-->>Controller: Error result
        Controller-->>Client: 400 Bad Request
        end
    else Database Error
        rect rgb(255, 230, 230)
        Service->>Service: transactionRollback()
        Service-->>Controller: Error result
        Controller-->>Client: 500 Internal Server Error
        end
    end
    end

Steps

Step 1: File Upload & Initial Validation

Description: User uploads CSV file via multipart form data
Request: POST /api/v1/general/unique-product-upload/import
Validation: File type (.csv), file size limits, accessibility
Authorization: User must be authenticated and have group access

Step 2: CSV Structure Validation

Description: Validate CSV headers and first row data
Action: Check required headers presence and first row content
Validation Rules:
- データセット名: Required, max 50 characters, no emojis
- Headers must match expected Japanese format exactly
Error Handling: Return validation error if structure is invalid

Step 3: Row-by-Row Data Processing

Description: Process each CSV row with comprehensive validation
Action: Extract product IDs, validate data formats, check for duplicates
Processing Logic:
- At least one product ID (楽天ID, JANコード, ASIN) required per row
- Unique code extraction and validation
- Duplicate detection within the dataset
Error Tracking: Record detailed errors for each problematic row

Step 4: Single Temp Wishlist Creation

Description: Create one temporary wishlist from CSV first row only
Action: Use データセット名 from first row to create single temp_wishlist_to_groups record
Key Fields: user_id, group_id, name (from first row only), auto-generated slug
Behavior: All products from all CSV rows are added to this single wishlist

Step 5: Summary Unique Product Creation

Description: Create summary records for unique codes
Action: Call TempSummaryUniqueProductService::createSummaryRecordsFromCodes()
Processing: Extract unique codes, filter duplicates, batch insert
Result: Created temp_summary_unique_products records for linking

Step 6: Product Data Storage

Description: Store individual product records with relationships to the single wishlist
Action: Batch insert temp_wishlist_products with proper relationships
Key Relationships:
- Link to single temp_wishlist_to_groups via temp_wishlist_to_group_id
- Link to temp_summary_unique_products via temp_summary_unique_product_id
Data Mapping: Convert product IDs to proper input_type and mall_id
Result: All products from all CSV rows stored under one temporary wishlist

Step 7: Import History & Error Recording

Description: Record import operation details and any errors
Action: Create import_history record and insert error details if any
Import Summary: Total rows processed, successful imports, error count
Error Details: Row number, field name, specific error message

Error Handling

Log: Import errors logged to application log and import_errors table
Error Detail:

Status Code	Error Message	Description
400	"Invalid file format. Please upload a CSV file."	File type validation failed
400	"CSV file is empty or missing required headers."	Structure validation failed
422	"Validation failed for row X: field_name is required."	Data validation errors
500	"Database error during import process."	Database operation failed

Case 2: Partial Success with Validation Errors

API: Import Unique Product CSV

Description

CSV import completes with some rows processed successfully while others contain validation errors. The system records detailed error information while processing valid rows.

Key Behavior

Partial Processing: Valid rows are processed and stored
Error Recording: Invalid rows are logged with detailed error messages
Transaction Safety: Database rollback only occurs on critical failures
User Feedback: Clear indication of successful vs. failed rows

Important Features

1. Single Wishlist with Unique Code Tracking System

Purpose: Create one temporary wishlist with unique product grouping and advanced analytics through unique identification codes.

Implementation:

Single Wishlist: One CSV file creates exactly one temporary wishlist (from first row データセット名)
Each product row in CSV must include a unique_id for grouping within that single wishlist
temp_summary_unique_products table stores unique codes for the single temp wishlist
temp_wishlist_products link to summary records via temp_summary_unique_product_id
Supports analysis and tracking of product groups across different platforms within the single wishlist

2. Multi-Platform Product Support

Supported Platforms:

Amazon: ASIN format validation and URL generation
Rakuten: Rakuten ID format handling with proper mall association
JAN Codes: Japanese Article Number support for both platforms

Data Mapping:

$productIdMap = [
    '楽天ID' => [
        'input_type' => InputType::RakutenId->value, // 'rakuten_id'
        'mall_id' => $rakutenMall->id
    ],
    'JANコード' => [
        'input_type' => InputType::Jan->value, // 'jan'
        'mall_id' => $rakutenMall->id
    ],
    'ASIN' => [
        'input_type' => InputType::Asin->value, // 'asin'
        'mall_id' => $amazonMall->id
    ],
];

3. Comprehensive Error Management

Error Tracking Features:

Row-level error recording with specific field identification
Detailed error messages for user feedback
Import history maintenance for audit trails
Partial success handling with detailed reporting

Error Categories:

Validation Errors: Data format, required fields, character limits
Duplicate Detection: Same product ID within temp wishlist
Business Logic Errors: Invalid product IDs, mall associations
System Errors: Database failures, file processing issues

4. Efficient Batch Processing

Performance Optimizations:

FastExcel library for efficient CSV reading
Batch insertion for large datasets
Transaction management for data consistency
Memory-efficient processing for large files

Processing Flow:

Stream CSV data without loading entire file into memory
Validate and collect data in batches
Perform bulk database operations
Link relationships efficiently using batch queries

Additional Notes

File Processing Considerations

Memory Management: Uses streaming approach for large CSV files
Character Encoding: Supports UTF-8 encoding for international characters
File Cleanup: Temporary files are cleaned up after processing
Error Recovery: Graceful handling of malformed CSV structures

Data Integrity Features

Unique Constraints: Prevents duplicate unique codes within temp wishlist
Foreign Key Relationships: Maintains referential integrity across tables
Transaction Safety: All-or-nothing approach for critical operations
Audit Trail: Complete import history with error details

Integration with Temp Wishlist System

Seamless Integration: Works with existing temp wishlist infrastructure
Conversion Ready: Data structure supports conversion to production wishlists
Group Permissions: Respects user group permissions and access control
Quota Management: Integrates with subscription and quota systems

Performance and Scalability

Batch Operations: Efficient handling of large datasets
Index Optimization: Database indexes for fast lookup operations
Memory Efficiency: Streaming processing to handle large files
Error Limitation: Reasonable error recording limits to prevent memory issues

CSV Import Specification Summary

⚠️ Critical Behavior:

One CSV File = One Temporary Wishlist
The データセット名 from the first row only determines the wishlist name
All products from all CSV rows are imported into this single temporary wishlist
Multiple CSV imports create multiple separate temporary wishlists
Unique codes track product groupings within each individual temporary wishlist

07.unique-product-csv-import

Unique Product CSV Import

Description

Recent Features

Enhanced Unique Product Tracking (2025-07-07)

Activity Diagram

API Endpoints

1. Import Unique Product CSV

2. Import History List

3. Import History Details

4. Download Error CSV

5. Import Error List

Database Schema

Database Related Tables & Fields

CSV Format Specification

Required Headers

Sample CSV Format

Validation Rules

File-Level Validation

Row-Level Validation

Case Documentation

Case 1: Successful CSV Import

Description

Sequence Diagram

Steps

Error Handling

Case 2: Partial Success with Validation Errors

Description

Key Behavior

Important Features

1. Single Wishlist with Unique Code Tracking System

2. Multi-Platform Product Support

3. Comprehensive Error Management

4. Efficient Batch Processing

Additional Notes

File Processing Considerations

Data Integrity Features

Integration with Temp Wishlist System

Performance and Scalability

CSV Import Specification Summary