Autoparts-DB/scripts/b2b_marketplace_schema.py

#!/usr/bin/env python3
"""Idempotent master-database schema for the B2B yonke marketplace.

Creates/ensures, in the master DB (nexus_autoparts):
  - b2b_listings            (used-part wholesale catalog published by sellers)
  - marketplace_orders      (cross-tenant order header; from v1.6_marketplace.sql)
  - marketplace_order_items (order lines; + listing_id link column)
  - tenants.is_seller       (seller opt-in flag)
and activates is_seller=true for the demo sellers (47 Yonke Demo, 45 Capi Autopartes).

Safe to run repeatedly: every statement is CREATE ... IF NOT EXISTS / ADD COLUMN
IF NOT EXISTS / UPDATE (idempotent). It never drops or rewrites data.

Run:
    cd /home/Autopartes && python3 scripts/b2b_marketplace_schema.py
    # or point at another master DB:
    MASTER_DB_URL=postgresql://user@host/nexus_autoparts python3 scripts/b2b_marketplace_schema.py
"""

import os
import sys

import psycopg2

MASTER_DB_URL = (
    os.environ.get('MASTER_DB_URL')
    or 'postgresql://postgres@localhost/nexus_autoparts'
)

# Demo sellers that must appear in the marketplace so it is never empty.
DEMO_SELLER_TENANT_IDS = (47, 45)

B2B_LISTINGS_DDL = """
CREATE TABLE IF NOT EXISTS b2b_listings (
    id                  SERIAL PRIMARY KEY,
    seller_tenant_id    INTEGER NOT NULL,
    seller_inventory_id INTEGER NOT NULL,
    part_number         VARCHAR(100),
    name                VARCHAR(300),
    condition           VARCHAR(20) DEFAULT 'usado',
    condition_grade     VARCHAR(2),
    price_wholesale     NUMERIC(12,2) NOT NULL DEFAULT 0,
    donor_make          VARCHAR(60),
    donor_model         VARCHAR(60),
    donor_year          INTEGER,
    image_url           VARCHAR(500),
    status              VARCHAR(20) NOT NULL DEFAULT 'available',
    notes               TEXT,
    created_at          TIMESTAMPTZ DEFAULT NOW(),
    updated_at          TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (seller_tenant_id, seller_inventory_id)
)
"""

# Mirror of pos/migrations/v1.6_marketplace.sql (master DB), kept here so this
# script can repair a master DB where the migration was skipped.
MARKETPLACE_ORDERS_DDL = """
CREATE TABLE IF NOT EXISTS marketplace_orders (
    id               SERIAL PRIMARY KEY,
    buyer_tenant_id  INTEGER NOT NULL,
    seller_tenant_id INTEGER NOT NULL,
    buyer_name       VARCHAR(200),
    seller_name      VARCHAR(200),
    total            NUMERIC(12,2),
    status           VARCHAR(20) DEFAULT 'pending',
    notes            TEXT,
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW()
)
"""

MARKETPLACE_ORDER_ITEMS_DDL = """
CREATE TABLE IF NOT EXISTS marketplace_order_items (
    id          SERIAL PRIMARY KEY,
    order_id    INTEGER REFERENCES marketplace_orders(id),
    part_number VARCHAR(100),
    part_name   VARCHAR(300),
    quantity    INTEGER,
    unit_price  NUMERIC(12,2),
    subtotal    NUMERIC(12,2)
)
"""

STATEMENTS = [
    ('b2b_listings', B2B_LISTINGS_DDL),
    ('marketplace_orders', MARKETPLACE_ORDERS_DDL),
    ('marketplace_order_items', MARKETPLACE_ORDER_ITEMS_DDL),

    # Extensions to tables that may already exist.
    ('marketplace_order_items.listing_id',
     "ALTER TABLE marketplace_order_items ADD COLUMN IF NOT EXISTS listing_id INTEGER"),
    ('tenants.is_seller',
     "ALTER TABLE tenants ADD COLUMN IF NOT EXISTS is_seller BOOLEAN DEFAULT FALSE"),

    # Indexes.
    ('idx_b2b_listings_seller',
     "CREATE INDEX IF NOT EXISTS idx_b2b_listings_seller ON b2b_listings(seller_tenant_id)"),
    ('idx_b2b_listings_status',
     "CREATE INDEX IF NOT EXISTS idx_b2b_listings_status ON b2b_listings(status)"),
    ('idx_b2b_listings_catalog',
     "CREATE INDEX IF NOT EXISTS idx_b2b_listings_catalog ON b2b_listings(status, condition_grade)"),
    ('idx_mp_orders_buyer',
     "CREATE INDEX IF NOT EXISTS idx_mp_orders_buyer ON marketplace_orders(buyer_tenant_id)"),
    ('idx_mp_orders_seller',
     "CREATE INDEX IF NOT EXISTS idx_mp_orders_seller ON marketplace_orders(seller_tenant_id)"),
    ('idx_mp_orders_status',
     "CREATE INDEX IF NOT EXISTS idx_mp_orders_status ON marketplace_orders(status)"),
    ('idx_mp_order_items_order',
     "CREATE INDEX IF NOT EXISTS idx_mp_order_items_order ON marketplace_order_items(order_id)"),
    ('idx_mp_order_items_listing',
     "CREATE INDEX IF NOT EXISTS idx_mp_order_items_listing ON marketplace_order_items(listing_id)"),
]


def log(msg):
    print(f"  -> {msg}")


def ensure_schema(conn):
    cur = conn.cursor()
    for label, sql in STATEMENTS:
        cur.execute(sql)
        log(f"ok: {label}")
    conn.commit()
    cur.close()


def verify(conn):
    cur = conn.cursor()
    cur.execute("""
        SELECT to_regclass('b2b_listings'),
               to_regclass('marketplace_orders'),
               to_regclass('marketplace_order_items')
    """)
    row = cur.fetchone()
    cur.close()
    return row


def activate_demo_sellers(conn):
    cur = conn.cursor()
    cur.execute(
        "SELECT id, name, COALESCE(is_seller, false) FROM tenants WHERE id = ANY(%s) ORDER BY id",
        (list(DEMO_SELLER_TENANT_IDS),),
    )
    found = cur.fetchall()
    if not found:
        log("no demo tenants found (47, 45) — skipping is_seller activation")
        cur.close()
        return
    cur.execute(
        "UPDATE tenants SET is_seller = TRUE WHERE id = ANY(%s)",
        (list(DEMO_SELLER_TENANT_IDS),),
    )
    conn.commit()
    for tenant_id, name, was_seller in found:
        state = "already seller" if was_seller else "activated"
        log(f"tenant {tenant_id} ({name}): {state}")
    cur.close()


def main():
    print(f"B2B marketplace master schema -> {MASTER_DB_URL}")
    conn = psycopg2.connect(MASTER_DB_URL)
    try:
        ensure_schema(conn)
        activate_demo_sellers(conn)
        b2b, orders, items = verify(conn)
        print("Verification:")
        log(f"b2b_listings={b2b}")
        log(f"marketplace_orders={orders}")
        log(f"marketplace_order_items={items}")
    finally:
        conn.close()
    print("Done.")
    return 0


if __name__ == '__main__':
    sys.exit(main())