-- ============================================================================
-- POS Transaction Import/Export Configurations
-- ============================================================================
-- Configurations for importing and exporting POS transaction data
-- Tables: sales_receipts, lineitems_payments
-- ============================================================================

-- Configuration: Complete Transaction Export (Advanced - Full data with JSONB)
-- This configuration exports complete POS transaction data including all fields,
-- line items (as JSONB), and payments (as JSONB). Best for complete data backup
-- or advanced users who need all fields.
INSERT INTO import_export_configs (name, description, type, table_name, config)
VALUES (
  'pos_transactions_complete',
  'Import/Export complete POS transaction data including all line items and payments (advanced format)',
  'both',
  'sales_receipts',
  '{
    "table": "sales_receipts",
    "primary_key": "sales_receipt_id",
    "columns": [
      "sales_receipt_id",
      "org_id",
      "type",
      "invoiceNumber",
      "requestedBy",
      "sdcDateTime",
      "invoiceCounter",
      "invoiceCounterExtension",
      "taxItems",
      "verificationUrl",
      "verificationQRCode",
      "journal",
      "messages",
      "signedBy",
      "encryptedInternalData",
      "signature",
      "totalCounter",
      "transactionTypeCounter",
      "totalAmount",
      "taxGroupRevision",
      "businessName",
      "tin",
      "locationName",
      "address",
      "district",
      "mrc",
      "transactiontype",
      "createdAt"
    ],
    "required_columns": ["invoiceNumber", "type", "totalAmount", "org_id"],
    "unique_fields": ["invoiceNumber", "org_id"],
    "joins": [
      {
        "type": "left",
        "table": "lineitems_payments",
        "first": "sales_receipts.sales_receipt_id",
        "second": "lineitems_payments.sales_receipt_id",
        "operator": "="
      }
    ],
    "additional_columns": [
      {
        "name": "line_items",
        "source": "lineitems_payments.line_items",
        "type": "jsonb"
      },
      {
        "name": "payments",
        "source": "lineitems_payments.payments",
        "type": "jsonb"
      }
    ],
    "export_transforms": {
      "sdcDateTime": {
        "type": "date",
        "format": "Y-m-d H:i:s"
      },
      "createdAt": {
        "type": "date",
        "format": "Y-m-d H:i:s"
      },
      "line_items": {
        "type": "json_decode",
        "pretty_print": true
      },
      "payments": {
        "type": "json_decode",
        "pretty_print": true
      },
      "taxItems": {
        "type": "json_decode",
        "pretty_print": true
      }
    },
    "import_transforms": {
      "sdcDateTime": {
        "type": "datetime",
        "format": "Y-m-d H:i:s",
        "allow_null": true
      },
      "createdAt": {
        "type": "datetime",
        "format": "Y-m-d H:i:s",
        "allow_null": true
      },
      "line_items": {
        "type": "json",
        "validate_structure": {
          "required_fields": ["product_id", "quantity", "unit_price"],
          "array_structure": true
        }
      },
      "payments": {
        "type": "json",
        "validate_structure": {
          "required_fields": ["method", "amount"],
          "array_structure": true
        }
      },
      "taxItems": {
        "type": "json",
        "allow_null": true
      },
      "totalAmount": {
        "type": "decimal"
      },
      "totalCounter": {
        "type": "integer",
        "allow_null": true
      },
      "transactionTypeCounter": {
        "type": "integer",
        "allow_null": true
      },
      "taxGroupRevision": {
        "type": "integer",
        "allow_null": true
      }
    },
    "field_labels": {
      "sales_receipt_id": "Receipt ID",
      "org_id": "Organization ID",
      "type": "Type",
      "invoiceNumber": "Invoice Number",
      "requestedBy": "Requested By",
      "sdcDateTime": "SDC Date/Time",
      "invoiceCounter": "Invoice Counter",
      "invoiceCounterExtension": "Invoice Counter Extension",
      "taxItems": "Tax Items (JSON)",
      "verificationUrl": "Verification URL",
      "verificationQRCode": "Verification QR Code",
      "journal": "Journal",
      "messages": "Messages",
      "signedBy": "Signed By",
      "encryptedInternalData": "Encrypted Internal Data",
      "signature": "Signature",
      "totalCounter": "Total Counter",
      "transactionTypeCounter": "Transaction Type Counter",
      "totalAmount": "Total Amount",
      "taxGroupRevision": "Tax Group Revision",
      "businessName": "Business Name",
      "tin": "TIN",
      "locationName": "Location Name",
      "address": "Address",
      "district": "District",
      "mrc": "MRC",
      "transactiontype": "Transaction Type",
      "createdAt": "Created At",
      "line_items": "Line Items (JSON)",
      "payments": "Payments (JSON)"
    },
    "export_options": {
      "include_json_fields": true,
      "pretty_print_json": true,
      "include_verification_data": true
    },
    "import_options": {
      "parse_json_fields": true,
      "create_lineitems_record": true,
      "validate_product_references": true,
      "update_existing": true
    },
    "validation_rules": {
      "totalAmount": {
        "type": "numeric",
        "min": 0,
        "required": true
      },
      "invoiceNumber": {
        "type": "string",
        "required": true,
        "max_length": 255
      },
      "type": {
        "type": "string",
        "required": true
      },
      "line_items": {
        "type": "json_array",
        "min_items": 1,
        "item_validation": {
          "product_id": {
            "type": "uuid",
            "required": true
          },
          "quantity": {
            "type": "numeric",
            "min": 0,
            "required": true
          },
          "unit_price": {
            "type": "numeric",
            "min": 0,
            "required": true
          }
        }
      }
    }
  }'
) ON CONFLICT (name) DO UPDATE SET
  config = EXCLUDED.config,
  description = EXCLUDED.description,
  type = EXCLUDED.type,
  table_name = EXCLUDED.table_name;

-- Configuration: Line Items Only (For bulk line item updates)
-- This configuration allows importing/exporting only line items and payments
-- for existing receipts. Useful for bulk corrections or updates.
INSERT INTO import_export_configs (name, description, type, table_name, config)
VALUES (
  'pos_line_items',
  'Import/Export only POS line items and payments linked to existing receipts',
  'both',
  'lineitems_payments',
  '{
    "table": "lineitems_payments",
    "primary_key": "sales_receipt_id",
    "columns": [
      "sales_receipt_id",
      "org_id",
      "line_items",
      "payments"
    ],
    "required_columns": ["sales_receipt_id"],
    "unique_fields": ["sales_receipt_id"],
    "joins": [
      {
        "type": "left",
        "table": "sales_receipts",
        "first": "lineitems_payments.sales_receipt_id",
        "second": "sales_receipts.sales_receipt_id",
        "operator": "="
      }
    ],
    "additional_columns": [
      {
        "name": "invoiceNumber",
        "source": "sales_receipts.invoiceNumber",
        "type": "text"
      },
      {
        "name": "totalAmount",
        "source": "sales_receipts.totalAmount",
        "type": "numeric"
      }
    ],
    "export_transforms": {
      "line_items": {
        "type": "json_decode",
        "pretty_print": true
      },
      "payments": {
        "type": "json_decode",
        "pretty_print": true
      }
    },
    "import_transforms": {
      "line_items": {
        "type": "json",
        "validate_structure": {
          "array_structure": true,
          "required_fields": ["product_id", "quantity", "unit_price"]
        }
      },
      "payments": {
        "type": "json",
        "validate_structure": {
          "array_structure": true,
          "required_fields": ["method", "amount"]
        }
      }
    },
    "field_labels": {
      "sales_receipt_id": "Receipt ID",
      "org_id": "Organization ID",
      "line_items": "Line Items (JSON)",
      "payments": "Payments (JSON)",
      "invoiceNumber": "Invoice Number",
      "totalAmount": "Total Amount"
    },
    "validation_rules": {
      "sales_receipt_id": {
        "type": "exists",
        "table": "sales_receipts",
        "column": "sales_receipt_id",
        "error_message": "Receipt ID must exist in sales_receipts table"
      },
      "line_items": {
        "type": "json_array",
        "min_items": 1
      }
    }
  }'
) ON CONFLICT (name) DO UPDATE SET
  config = EXCLUDED.config,
  description = EXCLUDED.description,
  type = EXCLUDED.type,
  table_name = EXCLUDED.table_name;

-- ============================================================================
-- Example Line Items JSON Structure
-- ============================================================================
-- Line items should follow this structure:
-- [
--   {
--     "product_id": "0194a8b2-1234-7123-8123-123456789abc",
--     "product_name": "Widget A",
--     "quantity": 2,
--     "unit_price": 50.00,
--     "total": 100.00,
--     "tax_rate": "G",
--     "discount": 0
--   },
--   {
--     "product_id": "0194a8b2-5678-7123-8123-123456789def",
--     "product_name": "Widget B",
--     "quantity": 1,
--     "unit_price": 25.50,
--     "total": 25.50,
--     "tax_rate": "G",
--     "discount": 0
--   }
-- ]

-- ============================================================================
-- Example Payments JSON Structure
-- ============================================================================
-- Payments should follow this structure:
-- [
--   {
--     "method": "Cash",
--     "amount": 100.00,
--     "reference": ""
--   },
--   {
--     "method": "MPaisa",
--     "amount": 25.50,
--     "reference": "MP-12345"
--   }
-- ]

COMMENT ON TABLE import_export_configs IS 'Import/export configurations for POS transactions stored in sales_receipts and lineitems_payments tables';
