Frknrg/RgReport

1"""2One-time setup script: Creates required tables in Supabase and GCP PostgreSQL.3Run once before deploying the new architecture.4 5Usage:6    python create_supabase_tables.py7"""8import psycopg29import os10 11# --- Supabase Config ---12SUPABASE_CONFIG = {13    "host": os.environ.get("SUPABASE_HOST", "aws-1-eu-north-1.pooler.supabase.com"),14    "port": 5432,15    "database": os.environ.get("SUPABASE_DB", "postgres"),16    "user": os.environ.get("SUPABASE_USER", "postgres.wawaztdbewvxugcfkzcw"),17    "password": os.environ.get("SUPABASE_PASSWORD", "f2kDElCfRDChelbh"),18    "connect_timeout": 30,19    "options": "-c statement_timeout=60000",20}21 22# --- GCP PostgreSQL Config ---23GCP_CONFIG = {24    "host": os.environ.get("GCP_HOST", "34.59.102.138"),25    "port": 5432,26    "database": os.environ.get("GCP_DB", "postgres"),27    "user": os.environ.get("GCP_USER", "postgres"),28    "password": os.environ.get("GCP_PASSWORD", "KutezIt3862!"),29    "connect_timeout": 30,30}31 32# ─── Supabase Tables ─────────────────────────────────────────────────────────33 34SUPABASE_DDL = """35-- Returns data (replaces return_cache.parquet)36CREATE TABLE IF NOT EXISTS report_returns (37    "Type"             TEXT,38    "Channel"          TEXT,39    "Status"           TEXT,40    "MarketplaceID"    TEXT,41    "SKU"              TEXT,42    "Gold Karat"       NUMERIC,43    "Refund Status"    TEXT,44    "Reason"           TEXT,45    "Return Warehouse" TEXT,46    "Date"             TIMESTAMPTZ,47    "Total Price"      NUMERIC,48    "Send Weight"      NUMERIC49);50 51-- Index for common queries52CREATE INDEX IF NOT EXISTS idx_report_returns_date   ON report_returns ("Date");53CREATE INDEX IF NOT EXISTS idx_report_returns_type   ON report_returns ("Type");54CREATE INDEX IF NOT EXISTS idx_report_returns_channel ON report_returns ("Channel");55 56-- Materialized view for fast country filter (avoids full table DISTINCT scan)57CREATE MATERIALIZED VIEW IF NOT EXISTS mv_countries AS58SELECT DISTINCT ship_to->>'country' AS country59FROM shipstation_orders60WHERE ship_to->>'country' IS NOT NULL61ORDER BY 1;62 63CREATE UNIQUE INDEX IF NOT EXISTS idx_mv_countries ON mv_countries (country);64"""65 66# ─── GCP PostgreSQL Tables ───────────────────────────────────────────────────67 68GCP_DDL = """69-- Deliveries data (replaces deliveries_cache.parquet)70-- Stored in same DB as ups_tracking so JOIN can happen in SQL71CREATE TABLE IF NOT EXISTS report_deliveries (72    id               TEXT,73    market_place_id  TEXT,74    bill_of_lading_no TEXT,75    order_date       DATE,76    ready_date       DATE,77    dispatch_date    DATE,78    delivery_date    DATE,79    invoice_status   TEXT,80    delivery_status  TEXT,81    country          TEXT,82    state            TEXT,83    city             TEXT,84    report_channel   TEXT,85    carrier_code     TEXT86);87 88CREATE INDEX IF NOT EXISTS idx_report_deliveries_order_date     ON report_deliveries (order_date);89CREATE INDEX IF NOT EXISTS idx_report_deliveries_bill_of_lading ON report_deliveries (bill_of_lading_no);90CREATE INDEX IF NOT EXISTS idx_report_deliveries_channel        ON report_deliveries (report_channel);91"""92 93 94def run_ddl(config, ddl, db_name):95    print(f"\n{'='*50}")96    print(f"Connecting to {db_name}...")97    try:98        conn = psycopg2.connect(**config)99        conn.autocommit = True100        cursor = conn.cursor()101 102        # Execute each statement separately103        statements = [s.strip() for s in ddl.split(";") if s.strip()]104        for stmt in statements:105            try:106                cursor.execute(stmt)107                print(f"  ✅ {stmt[:70].replace(chr(10), ' ')}...")108            except Exception as e:109                print(f"  ⚠️  {stmt[:70].replace(chr(10), ' ')}... → {e}")110 111        cursor.close()112        conn.close()113        print(f"✅ {db_name} setup complete.")114    except Exception as e:115        print(f"❌ {db_name} connection failed: {e}")116 117 118if __name__ == "__main__":119    run_ddl(SUPABASE_CONFIG, SUPABASE_DDL, "Supabase")120    run_ddl(GCP_CONFIG, GCP_DDL, "GCP PostgreSQL")121    print("\n🎉 All tables ready.")122