-- ============================================================================
-- Import/Export Configurations
-- ============================================================================
-- This file contains all import/export configurations for the system.
-- Configurations define how data should be imported from or exported to
-- various file formats (CSV, Excel, JSON, XML).
--
-- All new import/export configurations should be added to this file.
-- DO NOT create the table here - it's defined in TAFDB.pgsql
--
-- Configuration Structure:
-- - name: Unique identifier for the configuration
-- - description: Human-readable description
-- - type: 'import', 'export', or 'both'
-- - table_name: Target database table
-- - config: JSON object with mapping, transformation, and validation rules
--
-- See docs/ImportExportService.md for detailed documentation
-- ============================================================================

-- ============================================================================
-- Products Catalog (POS)
-- ============================================================================
-- Import and export product catalog including all product details
-- Used by: /api/pos/products/export and /api/pos/products/import
-- ============================================================================
INSERT INTO import_export_configs (name, description, type, table_name, config) VALUES 
(
  'products_catalog',
  'Import and export product catalog including all product details',
  'both',
  'products',
  '{
    "table": "products",
    "primary_key": "product_id",
    "filename": "products_catalog",
    "columns": [
      "product_id",
      "name",
      "description",
      "price",
      "quantity",
      "barcode",
      "brand",
      "product_number",
      "category_id",
      "tax_rates"
    ],
    "field_mappings": {
      "Product ID": "product_id",
      "Name": "name",
      "Description": "description",
      "Price": "price",
      "Quantity": "quantity",
      "Barcode": "barcode",
      "Brand": "brand",
      "Product Number": "product_number",
      "Category": "category_id",
      "VAT Rate": {"column": "tax_rates", "json_field": "vat"},
      "STT Rate": {"column": "tax_rates", "json_field": "stt"}
    },
    "field_labels": {
      "product_id": "Product ID",
      "name": "Name",
      "description": "Description",
      "price": "Price",
      "quantity": "Quantity",
      "barcode": "Barcode",
      "brand": "Brand",
      "product_number": "Product Number"
    },
    "transforms": {
      "price": {
        "type": "number",
        "decimals": 2
      },
      "quantity": {
        "type": "number",
        "decimals": 0
      },
      "category_id": {
        "type": "lookup",
        "table": "product_categories",
        "key_column": "name",
        "value_column": "category_id"
      }
    },
    "export_transforms": {
      "price": {
        "type": "number",
        "decimals": 2
      },
      "category_id": {
        "type": "lookup",
        "table": "product_categories",
        "key_column": "category_id",
        "display_column": "name"
      },
      "tax_rates": {
        "type": "json_decode"
      }
    },
    "validations": {
      "name": {
        "required": true,
        "max_length": 255
      },
      "price": {
        "required": false
      }
    },
    "unique_fields": ["barcode", "product_number"],
    "update_existing": true,
    "filters": {
      "isDeleted": false
    },
    "joins": [
      {
        "type": "left",
        "table": "product_categories",
        "first": "products.category_id",
        "operator": "=",
        "second": "product_categories.category_id"
      }
    ],
    "order_by": {
      "name": "ASC"
    }
  }'
) ON CONFLICT (name) DO UPDATE SET
  description = EXCLUDED.description,
  type = EXCLUDED.type,
  table_name = EXCLUDED.table_name,
  config = EXCLUDED.config,
  updatedAt = now();

-- ============================================================================
-- Restaurant Products
-- ============================================================================
-- Import and export restaurant products including ingredients
-- Used by restaurant/POS modules for menu management
-- ============================================================================
INSERT INTO import_export_configs (name, description, type, table_name, config) VALUES 
(
  'restaurant_products',
  'Import and export restaurant products including ingredients',
  'both',
  'products',
  '{
    "table": "products",
    "primary_key": "product_id",
    "filename": "restaurant_products",
    "columns": [
      "product_id",
      "name",
      "description",
      "price",
      "quantity",
      "barcode",
      "brand",
      "product_number",
      "category_id",
      "tax_rates"
    ],
    "field_mappings": {
      "Product ID": "product_id",
      "Name": "name",
      "Description": "description",
      "Price": "price",
      "Quantity": "quantity",
      "Barcode": "barcode",
      "Brand": "brand",
      "Product Number": "product_number",
      "Category": "category_id",
      "VAT Rate": {"column": "tax_rates", "json_field": "vat"},
      "STT Rate": {"column": "tax_rates", "json_field": "stt"}
    },
    "field_labels": {
      "product_id": "Product ID",
      "name": "Name",
      "description": "Description",
      "price": "Price",
      "quantity": "Quantity",
      "barcode": "Barcode",
      "brand": "Brand",
      "product_number": "Product Number"
    },
    "transforms": {
      "price": {
        "type": "number",
        "decimals": 2
      },
      "quantity": {
        "type": "number",
        "decimals": 0
      },
      "category_id": {
        "type": "lookup",
        "table": "product_categories",
        "key_column": "name",
        "value_column": "category_id"
      }
    },
    "export_transforms": {
      "price": {
        "type": "number",
        "decimals": 2
      },
      "category_id": {
        "type": "lookup",
        "table": "product_categories",
        "key_column": "category_id",
        "display_column": "name"
      }
    },
    "validations": {
      "name": {
        "required": true,
        "max_length": 255
      }
    },
    "unique_fields": ["barcode"],
    "update_existing": true,
    "filters": {
      "isDeleted": false
    }
  }'
) ON CONFLICT (name) DO UPDATE SET
  description = EXCLUDED.description,
  type = EXCLUDED.type,
  table_name = EXCLUDED.table_name,
  config = EXCLUDED.config,
  updatedAt = now();

-- ============================================================================
-- ADD NEW CONFIGURATIONS BELOW THIS LINE
-- ============================================================================
-- Template for new configurations:
--
-- INSERT INTO import_export_configs (name, description, type, table_name, config) VALUES 
-- (
--   'your_config_name',
--   'Description of what this imports/exports',
--   'both',  -- or 'import' or 'export'
--   'table_name',
--   '{
--     "table": "table_name",
--     "primary_key": "id",
--     "filename": "export_filename",
--     "columns": ["col1", "col2"],
--     "field_mappings": {
--       "CSV Header": "db_column"
--     },
--     "validations": {
--       "col1": {"required": true}
--     },
--     "filters": {
--       "isDeleted": false
--     }
--   }'
-- ) ON CONFLICT (name) DO UPDATE SET
--   description = EXCLUDED.description,
--   type = EXCLUDED.type,
--   table_name = EXCLUDED.table_name,
--   config = EXCLUDED.config,
--   updatedAt = now();
--
-- ============================================================================
