#!/usr/bin/env python3
import argparse
import json
import sqlite3
from decimal import Decimal, ROUND_HALF_UP
from pathlib import Path

TWOPLACES = Decimal('0.01')


def money(value):
    if value is None or value == '':
        return Decimal('0.00')
    text = str(value).strip().replace('$', '').replace(',', '')
    if text.startswith('(') and text.endswith(')'):
        text = '-' + text[1:-1]
    return Decimal(text).quantize(TWOPLACES, rounding=ROUND_HALF_UP)


def fmt(value: Decimal) -> str:
    return str(value.quantize(TWOPLACES, rounding=ROUND_HALF_UP))


def ensure_schema(conn):
    conn.executescript('''
    CREATE TABLE IF NOT EXISTS purchase_orders (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      order_id TEXT UNIQUE,
      merchant TEXT,
      order_date TEXT,
      fulfillment_type TEXT,
      order_total TEXT,
      item_subtotal TEXT,
      adjustment_total TEXT,
      reconciliation_delta TEXT,
      synced_at TEXT DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS purchase_items (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      order_id TEXT,
      item_name TEXT,
      quantity TEXT,
      unit_price TEXT,
      item_total TEXT,
      FOREIGN KEY (order_id) REFERENCES purchase_orders(order_id)
    );
    CREATE TABLE IF NOT EXISTS purchase_adjustments (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      order_id TEXT,
      adjustment_name TEXT,
      adjustment_amount TEXT,
      adjustment_type TEXT,
      FOREIGN KEY (order_id) REFERENCES purchase_orders(order_id)
    );
    CREATE TABLE IF NOT EXISTS purchase_sync_log (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      synced_at TEXT DEFAULT (datetime('now')),
      merchant TEXT,
      orders_found INTEGER,
      items_written INTEGER,
      status TEXT,
      message TEXT
    );
    CREATE TABLE IF NOT EXISTS purchase_order_exceptions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      run_id TEXT NOT NULL,
      merchant TEXT NOT NULL,
      order_id TEXT NOT NULL,
      route_family TEXT,
      order_date TEXT,
      visible_order_total TEXT,
      extracted_subtotal TEXT,
      reconciliation_delta TEXT,
      failure_reason TEXT NOT NULL,
      failure_detail TEXT,
      detail_url TEXT,
      artifact_refs_json TEXT,
      status TEXT NOT NULL DEFAULT 'open',
      retry_count INTEGER NOT NULL DEFAULT 0,
      last_retry_at TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE INDEX IF NOT EXISTS idx_purchase_order_exceptions_merchant_status_created
      ON purchase_order_exceptions (merchant, status, created_at);
    CREATE INDEX IF NOT EXISTS idx_purchase_order_exceptions_order
      ON purchase_order_exceptions (order_id);
    ''')


def normalize_order(raw, merchant_override=None):
    merchant = merchant_override or raw.get('merchant') or 'Ralphs'
    items = raw.get('items', [])
    adjustments = raw.get('adjustments', [])
    item_subtotal = sum((money(item.get('item_total')) for item in items), Decimal('0.00'))
    adjustment_total = sum((money(adj.get('adjustment_amount')) for adj in adjustments), Decimal('0.00'))
    order_total = money(raw.get('order_total'))
    delta = order_total - item_subtotal - adjustment_total
    return {
        'order_id': str(raw['order_id']).strip(),
        'merchant': merchant,
        'order_date': str(raw.get('order_date', '')).strip(),
        'fulfillment_type': str(raw.get('fulfillment_type', '')).strip(),
        'order_total': fmt(order_total),
        'item_subtotal': fmt(item_subtotal),
        'adjustment_total': fmt(adjustment_total),
        'reconciliation_delta': fmt(delta),
        'items': [
            {
                'name': str(item.get('item_name', '')).strip(),
                'qty': str(item.get('quantity', '')).strip(),
                'unit_price': fmt(money(item.get('unit_price'))),
                'item_total': fmt(money(item.get('item_total'))),
            }
            for item in items
        ],
        'adjustments': [
            {
                'name': str(adj.get('adjustment_name', '')).strip(),
                'amount': fmt(money(adj.get('adjustment_amount'))),
                'type': str(adj.get('adjustment_type', '')).strip(),
            }
            for adj in adjustments
        ],
    }


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument('--db-path', required=True)
    parser.add_argument('--input-json', required=True)
    parser.add_argument('--merchant', default='Ralphs')
    args = parser.parse_args()

    payload = json.loads(Path(args.input_json).read_text(encoding='utf-8'))
    orders = [normalize_order(order, merchant_override=args.merchant) for order in payload.get('orders', [])]

    conn = sqlite3.connect(args.db_path)
    ensure_schema(conn)
    cur = conn.cursor()

    orders_inserted = 0
    items_written = 0
    adjustments_written = 0

    for order in orders:
        cur.execute(
            'INSERT OR IGNORE INTO purchase_orders '
            '(order_id, merchant, order_date, fulfillment_type, order_total, item_subtotal, adjustment_total, reconciliation_delta, synced_at) '
            'VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime(\'now\'))',
            (
                order['order_id'], order['merchant'], order['order_date'], order['fulfillment_type'],
                order['order_total'], order['item_subtotal'], order['adjustment_total'], order['reconciliation_delta']
            )
        )
        inserted = cur.rowcount > 0
        if inserted:
            orders_inserted += 1
            for item in order['items']:
                cur.execute(
                    'INSERT INTO purchase_items (order_id, item_name, quantity, unit_price, item_total) VALUES (?, ?, ?, ?, ?)',
                    (order['order_id'], item['name'], item['qty'], item['unit_price'], item['item_total'])
                )
                items_written += 1
            for adj in order['adjustments']:
                cur.execute(
                    'INSERT INTO purchase_adjustments (order_id, adjustment_name, adjustment_amount, adjustment_type) VALUES (?, ?, ?, ?)',
                    (order['order_id'], adj['name'], adj['amount'], adj['type'])
                )
                adjustments_written += 1

    message = f"{args.merchant} sync complete. {len(orders)} orders seen, {orders_inserted} inserted, {items_written} items, {adjustments_written} adjustments."
    cur.execute(
        'INSERT INTO purchase_sync_log (merchant, orders_found, items_written, status, message) VALUES (?, ?, ?, ?, ?)',
        (args.merchant, len(orders), items_written, 'complete', message)
    )
    conn.commit()
    conn.close()

    print(json.dumps({
        'merchant': args.merchant,
        'orders_seen': len(orders),
        'orders_inserted': orders_inserted,
        'items_written': items_written,
        'adjustments_written': adjustments_written,
        'status': 'complete'
    }))


if __name__ == '__main__':
    main()