259 lines
11 KiB
Python
259 lines
11 KiB
Python
import enum
|
|
import uuid
|
|
from datetime import datetime
|
|
from sqlalchemy import Column, String, Integer, BigInteger, Boolean, Enum as SQLEnum, Text, JSON, DateTime, ForeignKey
|
|
from sqlalchemy.orm import relationship
|
|
from app.core.database.db_session import Base
|
|
|
|
def generate_uuid():
|
|
return str(uuid.uuid4())
|
|
|
|
class BatchTypeEnum(str, enum.Enum):
|
|
PRODUCTS = "PRODUCTS"
|
|
VARIANTS = "VARIANTS"
|
|
MEDIA = "MEDIA"
|
|
INVENTORY = "INVENTORY"
|
|
CRM_LEADS = "CRM_LEADS"
|
|
REPAIR_CATALOG = "REPAIR_CATALOG"
|
|
CUSTOMERS = "CUSTOMERS"
|
|
WAREHOUSES = "WAREHOUSES"
|
|
|
|
class ImportModeEnum(str, enum.Enum):
|
|
CREATE_ONLY = "CREATE_ONLY"
|
|
UPDATE_EXISTING = "UPDATE_EXISTING"
|
|
UPSERT = "UPSERT"
|
|
SKIP_EXISTING = "SKIP_EXISTING"
|
|
|
|
class BatchStatusEnum(str, enum.Enum):
|
|
PENDING = "PENDING"
|
|
VALIDATING = "VALIDATING"
|
|
READY = "READY"
|
|
PROCESSING = "PROCESSING"
|
|
COMPLETED = "COMPLETED"
|
|
FAILED = "FAILED"
|
|
ROLLED_BACK = "ROLLED_BACK"
|
|
|
|
class JobStatusEnum(str, enum.Enum):
|
|
QUEUED = "QUEUED"
|
|
VALIDATING = "VALIDATING"
|
|
RUNNING = "RUNNING"
|
|
CANCELLING = "CANCELLING"
|
|
CANCELLED = "CANCELLED"
|
|
FAILED = "FAILED"
|
|
COMPLETED = "COMPLETED"
|
|
|
|
class PhaseEnum(str, enum.Enum):
|
|
UPLOAD = "UPLOAD"
|
|
VALIDATE = "VALIDATE"
|
|
DRY_RUN = "DRY_RUN"
|
|
MASTER_DATA = "MASTER_DATA"
|
|
PRODUCTS = "PRODUCTS"
|
|
VARIANTS = "VARIANTS"
|
|
MEDIA_PROCESS = "MEDIA_PROCESS"
|
|
MEDIA_LINK = "MEDIA_LINK"
|
|
VERIFY = "VERIFY"
|
|
COMPLETED = "COMPLETED"
|
|
|
|
class MediaItemStatusEnum(str, enum.Enum):
|
|
PENDING = "PENDING"
|
|
PROCESSING = "PROCESSING"
|
|
STORED = "STORED"
|
|
REGISTERED = "REGISTERED"
|
|
FAILED = "FAILED"
|
|
|
|
class RetryStatusEnum(str, enum.Enum):
|
|
UNRESOLVED = "UNRESOLVED"
|
|
RETRYING = "RETRYING"
|
|
RETRIED = "RETRIED"
|
|
RESOLVED = "RESOLVED"
|
|
SKIPPED = "SKIPPED"
|
|
PERMANENT_FAILURE = "PERMANENT_FAILURE"
|
|
|
|
class ErrorSeverityEnum(str, enum.Enum):
|
|
ERROR = "ERROR"
|
|
WARNING = "WARNING"
|
|
|
|
class MediaSourceTypeEnum(str, enum.Enum):
|
|
MEDIA_JSON = "MEDIA_JSON"
|
|
FOLDER_PATH = "FOLDER_PATH"
|
|
|
|
class MigrationBatch(Base):
|
|
__tablename__ = "migration_batches"
|
|
|
|
id = Column(String(36), primary_key=True, default=generate_uuid)
|
|
batch_type = Column(SQLEnum(BatchTypeEnum), nullable=False, default=BatchTypeEnum.PRODUCTS)
|
|
user_id = Column(String(36), nullable=False)
|
|
import_mode = Column(SQLEnum(ImportModeEnum), nullable=False, default=ImportModeEnum.UPSERT)
|
|
status = Column(SQLEnum(BatchStatusEnum), nullable=False, default=BatchStatusEnum.PENDING)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
completed_at = Column(DateTime, nullable=True)
|
|
|
|
jobs = relationship("MigrationJob", back_populates="batch", cascade="all, delete-orphan")
|
|
snapshots = relationship("MigrationSnapshot", back_populates="batch", cascade="all, delete-orphan")
|
|
|
|
class MappingConfig(Base):
|
|
__tablename__ = "mapping_configs"
|
|
|
|
id = Column(String(36), primary_key=True, default=generate_uuid)
|
|
name = Column(String(100), nullable=False)
|
|
entity_type = Column(String(50), nullable=False)
|
|
column_maps = Column(JSON, nullable=False)
|
|
created_by = Column(String(36), nullable=False)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
|
|
class MediaGroup(Base):
|
|
__tablename__ = "media_groups"
|
|
|
|
id = Column(String(36), primary_key=True, default=generate_uuid)
|
|
media_key = Column(String(255), unique=True, nullable=False, index=True)
|
|
source_type = Column(SQLEnum(MediaSourceTypeEnum), nullable=False, default=MediaSourceTypeEnum.FOLDER_PATH)
|
|
brand_name = Column(String(100), nullable=True)
|
|
model_name = Column(String(100), nullable=True)
|
|
variant_tag = Column(String(100), nullable=True)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
|
|
media_assets = relationship("MediaAsset", back_populates="group", cascade="all, delete-orphan")
|
|
|
|
class MediaAsset(Base):
|
|
__tablename__ = "media_library"
|
|
|
|
id = Column(String(36), primary_key=True, default=generate_uuid)
|
|
media_group_id = Column(String(36), ForeignKey("media_groups.id", ondelete="CASCADE"), nullable=False)
|
|
original_filename = Column(String(255), nullable=False)
|
|
stored_filename = Column(String(255), nullable=False)
|
|
mime_type = Column(String(50), nullable=False)
|
|
file_size_bytes = Column(BigInteger, nullable=False)
|
|
width = Column(Integer, nullable=False, default=0)
|
|
height = Column(Integer, nullable=False, default=0)
|
|
sha256_checksum = Column(String(64), unique=True, nullable=False, index=True) # Global unique SHA-256 asset
|
|
cdn_url = Column(String(512), nullable=False)
|
|
thumbnail_url = Column(String(512), nullable=False)
|
|
storage_path = Column(String(512), nullable=True)
|
|
exif_metadata = Column(JSON, nullable=True)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
|
|
group = relationship("MediaGroup", back_populates="media_assets")
|
|
|
|
class MigrationJob(Base):
|
|
__tablename__ = "migration_jobs"
|
|
|
|
id = Column(String(36), primary_key=True, default=generate_uuid)
|
|
batch_id = Column(String(36), ForeignKey("migration_batches.id", ondelete="CASCADE"), nullable=False)
|
|
job_type = Column(String(50), nullable=False, default="PRODUCTS")
|
|
is_dry_run = Column(Boolean, nullable=False, default=False)
|
|
file_name = Column(String(255), nullable=False, default="")
|
|
file_format = Column(String(10), nullable=False, default="CSV")
|
|
|
|
status = Column(SQLEnum(JobStatusEnum), nullable=False, default=JobStatusEnum.QUEUED, index=True)
|
|
current_phase = Column(SQLEnum(PhaseEnum), nullable=False, default=PhaseEnum.UPLOAD, index=True)
|
|
|
|
# Worker Lease & Lock
|
|
worker_id = Column(String(64), nullable=True, index=True)
|
|
locked_at = Column(DateTime, nullable=True)
|
|
heartbeat_at = Column(DateTime, nullable=True)
|
|
lease_version = Column(BigInteger, nullable=False, default=1)
|
|
|
|
# Cancellation
|
|
cancel_requested_at = Column(DateTime, nullable=True)
|
|
|
|
# Phase Progress
|
|
current_batch = Column(Integer, nullable=False, default=0)
|
|
total_batches = Column(Integer, nullable=False, default=0)
|
|
last_successful_batch = Column(Integer, nullable=False, default=0)
|
|
|
|
# Overall Progress
|
|
processed_records = Column(Integer, nullable=False, default=0)
|
|
total_records = Column(Integer, nullable=False, default=0)
|
|
successful_records = Column(Integer, nullable=False, default=0)
|
|
failed_records = Column(Integer, nullable=False, default=0)
|
|
warning_records = Column(Integer, nullable=False, default=0)
|
|
retry_count = Column(Integer, nullable=False, default=0)
|
|
|
|
# Baseline Expectations (recorded during validation phase)
|
|
expected_products = Column(Integer, nullable=False, default=0)
|
|
expected_variants = Column(Integer, nullable=False, default=0)
|
|
expected_media_items = Column(Integer, nullable=False, default=0)
|
|
expected_media_links = Column(Integer, nullable=False, default=0)
|
|
|
|
# Timestamps & Errors
|
|
started_at = Column(DateTime, nullable=True)
|
|
completed_at = Column(DateTime, nullable=True)
|
|
failed_at = Column(DateTime, nullable=True)
|
|
finished_at = Column(DateTime, nullable=True)
|
|
error_message = Column(Text, nullable=True)
|
|
|
|
batch = relationship("MigrationBatch", back_populates="jobs")
|
|
checkpoints = relationship("MigrationJobCheckpoint", back_populates="job", cascade="all, delete-orphan")
|
|
media_items = relationship("MigrationMediaItem", back_populates="job", cascade="all, delete-orphan")
|
|
errors = relationship("MigrationError", back_populates="job", cascade="all, delete-orphan")
|
|
|
|
class MigrationJobCheckpoint(Base):
|
|
__tablename__ = "migration_job_checkpoints"
|
|
|
|
id = Column(String(36), primary_key=True, default=generate_uuid)
|
|
job_id = Column(String(36), ForeignKey("migration_jobs.id", ondelete="CASCADE"), nullable=False, index=True)
|
|
phase = Column(SQLEnum(PhaseEnum), nullable=False, index=True)
|
|
last_successful_batch = Column(Integer, nullable=False, default=0)
|
|
total_batches = Column(Integer, nullable=False, default=0)
|
|
processed_records = Column(Integer, nullable=False, default=0)
|
|
failed_records = Column(Integer, nullable=False, default=0)
|
|
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
|
|
|
|
job = relationship("MigrationJob", back_populates="checkpoints")
|
|
|
|
class MigrationMediaItem(Base):
|
|
__tablename__ = "migration_media_items"
|
|
|
|
id = Column(String(36), primary_key=True, default=generate_uuid)
|
|
job_id = Column(String(36), ForeignKey("migration_jobs.id", ondelete="CASCADE"), nullable=False, index=True)
|
|
batch_number = Column(Integer, nullable=False, default=0)
|
|
file_name = Column(String(255), nullable=False)
|
|
archive_name = Column(String(255), nullable=True)
|
|
zip_entry_path = Column(String(512), nullable=True)
|
|
media_key = Column(String(255), nullable=False, index=True)
|
|
sha256 = Column(String(64), nullable=True, index=True) # NOT unique (allows multiple source references to map to 1 MediaAsset)
|
|
storage_path = Column(String(512), nullable=True)
|
|
status = Column(SQLEnum(MediaItemStatusEnum), nullable=False, default=MediaItemStatusEnum.PENDING, index=True)
|
|
error = Column(Text, nullable=True)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
|
|
job = relationship("MigrationJob", back_populates="media_items")
|
|
|
|
class MigrationError(Base):
|
|
__tablename__ = "migration_errors"
|
|
|
|
id = Column(String(36), primary_key=True, default=generate_uuid)
|
|
job_id = Column(String(36), ForeignKey("migration_jobs.id", ondelete="CASCADE"), nullable=False, index=True)
|
|
batch_number = Column(Integer, nullable=False, default=0)
|
|
row_number = Column(Integer, nullable=False)
|
|
sku = Column(String(100), nullable=True)
|
|
product_name = Column(String(255), nullable=True)
|
|
file_name = Column(String(255), nullable=True)
|
|
entity_type = Column(String(50), nullable=True)
|
|
entity_id = Column(String(36), nullable=True)
|
|
phase = Column(String(50), nullable=False, default="VALIDATE")
|
|
error_type = Column(String(100), nullable=False, default="DATA_ERROR")
|
|
severity = Column(SQLEnum(ErrorSeverityEnum), nullable=False, default=ErrorSeverityEnum.ERROR)
|
|
field_name = Column(String(100), nullable=True)
|
|
error_message = Column(Text, nullable=False)
|
|
suggested_fix = Column(Text, nullable=True)
|
|
retry_status = Column(SQLEnum(RetryStatusEnum), nullable=False, default=RetryStatusEnum.UNRESOLVED)
|
|
attempt_count = Column(Integer, nullable=False, default=1)
|
|
raw_row_data = Column(JSON, nullable=True)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
resolved_at = Column(DateTime, nullable=True)
|
|
|
|
job = relationship("MigrationJob", back_populates="errors")
|
|
|
|
class MigrationSnapshot(Base):
|
|
__tablename__ = "migration_snapshots"
|
|
|
|
id = Column(String(36), primary_key=True, default=generate_uuid)
|
|
batch_id = Column(String(36), ForeignKey("migration_batches.id", ondelete="CASCADE"), nullable=False)
|
|
entity_table = Column(String(50), nullable=False)
|
|
entity_id = Column(String(36), nullable=False)
|
|
action_taken = Column(String(20), nullable=False)
|
|
previous_state = Column(JSON, nullable=True)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
|
|
batch = relationship("MigrationBatch", back_populates="snapshots")
|