"""initial schema Revision ID: 0001_initial Revises: Create Date: 2026-01-23 11:50:00.000000 """ from alembic import op import sqlalchemy as sa from sqlalchemy.dialects import postgresql revision = "0001_initial" down_revision = None branch_labels = None depends_on = None def upgrade() -> None: op.create_table( "ingest_run", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("source", sa.String(length=32), nullable=False), sa.Column("file_name", sa.String(length=255)), sa.Column("file_hash", sa.String(length=128), unique=True), sa.Column("started_at", sa.DateTime(timezone=True)), sa.Column("finished_at", sa.DateTime(timezone=True)), sa.Column("status", sa.String(length=32)), sa.Column("row_count", sa.Integer()), sa.Column("notes", sa.Text()), ) op.create_table( "raw_feed_row", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("ingest_run_id", sa.Integer(), sa.ForeignKey("ingest_run.id"), nullable=False), sa.Column("row_num", sa.Integer(), nullable=False), sa.Column("sku", sa.String(length=128)), sa.Column("payload", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("errors", postgresql.JSONB(astext_type=sa.Text())), ) op.create_table( "product_snapshot", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("sku", sa.String(length=128), nullable=False), sa.Column("source", sa.String(length=32), nullable=False), sa.Column("load_id", sa.Integer(), sa.ForeignKey("ingest_run.id"), nullable=False), sa.Column("valid_from", sa.DateTime(timezone=True)), sa.Column("valid_to", sa.DateTime(timezone=True)), sa.Column("name", sa.Text()), sa.Column("description", sa.Text()), sa.Column("short_description", sa.Text()), sa.Column("product_type", sa.String(length=64)), sa.Column("attribute_set_code", sa.String(length=64)), sa.Column("categories_raw", sa.Text()), sa.Column("base_image", sa.Text()), sa.Column("additional_images", sa.Text()), sa.Column("variant_of", sa.String(length=128)), sa.Column("variant_list", sa.Text()), sa.Column("attrs", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("row_hash", sa.String(length=64), nullable=False), ) op.create_index("ix_product_snapshot_sku", "product_snapshot", ["sku"]) op.create_table( "sku_price_snapshot", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("sku", sa.String(length=128), nullable=False), sa.Column("load_id", sa.Integer(), sa.ForeignKey("ingest_run.id"), nullable=False), sa.Column("valid_from", sa.DateTime(timezone=True)), sa.Column("valid_to", sa.DateTime(timezone=True)), sa.Column("price", sa.Numeric(12, 4)), sa.Column("msrp", sa.Numeric(12, 4)), sa.Column("qty", sa.Numeric(14, 4)), sa.Column("is_in_stock", sa.Boolean()), sa.Column("source_payload", postgresql.JSONB(astext_type=sa.Text())), ) op.create_index("ix_sku_price_snapshot_sku", "sku_price_snapshot", ["sku"]) op.create_table( "attribute_def", sa.Column("attribute_code", sa.String(length=128), primary_key=True), sa.Column("frontend_label", sa.String(length=255)), sa.Column("type", sa.String(length=64)), sa.Column("is_required", sa.Boolean(), default=False), sa.Column("allows_free_text", sa.Boolean(), default=False), sa.Column("updated_at", sa.DateTime(timezone=True)), ) op.create_table( "attribute_option", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column( "attribute_code", sa.String(length=128), sa.ForeignKey("attribute_def.attribute_code"), nullable=False, ), sa.Column("option_value", sa.Text(), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True)), ) op.create_unique_constraint("uq_attr_option", "attribute_option", ["attribute_code", "option_value"]) op.create_table( "attribute_discovery", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("ingest_run_id", sa.Integer(), sa.ForeignKey("ingest_run.id"), nullable=False), sa.Column("sku", sa.String(length=128)), sa.Column("attribute_code", sa.String(length=128), nullable=False), sa.Column("new_value", sa.Text(), nullable=False), sa.Column("source", sa.String(length=64)), sa.Column("first_seen_at", sa.DateTime(timezone=True)), sa.Column("status", sa.String(length=32)), sa.Column("notes", sa.Text()), ) op.create_table( "export_job", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("requested_at", sa.DateTime(timezone=True)), sa.Column("as_of_timestamp", sa.DateTime(timezone=True), nullable=False), sa.Column("plytix_run_id", sa.Integer(), sa.ForeignKey("ingest_run.id")), sa.Column("netsuite_run_id", sa.Integer(), sa.ForeignKey("ingest_run.id")), sa.Column("status", sa.String(length=32)), sa.Column("artifact_path", sa.Text()), sa.Column("stats", postgresql.JSONB(astext_type=sa.Text())), ) op.create_table( "export_row", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("export_job_id", sa.Integer(), sa.ForeignKey("export_job.id"), nullable=False), sa.Column("sku", sa.String(length=128)), sa.Column("row_payload", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("quarantine_reason", sa.Text()), ) def downgrade() -> None: op.drop_table("export_row") op.drop_table("export_job") op.drop_table("attribute_discovery") op.drop_constraint("uq_attr_option", "attribute_option", type_="unique") op.drop_table("attribute_option") op.drop_table("attribute_def") op.drop_index("ix_sku_price_snapshot_sku", table_name="sku_price_snapshot") op.drop_table("sku_price_snapshot") op.drop_index("ix_product_snapshot_sku", table_name="product_snapshot") op.drop_table("product_snapshot") op.drop_table("raw_feed_row") op.drop_table("ingest_run")