-- ============================================================================
-- Migration: Add Import/Export System
-- ============================================================================
-- This migration file is for EXISTING databases only.
-- For NEW databases, the table is already in TAFDB.pgsql
--
-- Purpose: Adds the import_export_configs table to existing databases
-- 
-- Usage:
--   psql -U postgres -d your_database -f migrations/add_import_export_configs.sql
--   psql -U postgres -d your_database -f sql/import_export.sql
--
-- Note: All import/export configurations should be defined in sql/import_export.sql
-- ============================================================================

CREATE TABLE IF NOT EXISTS import_export_configs (
  config_id         uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  name              text NOT NULL UNIQUE,
  description       text,
  type              text NOT NULL CHECK (type IN ('import', 'export', 'both')),
  table_name        text NOT NULL,
  config            jsonb NOT NULL,
  org_id            uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  created_by        uuid REFERENCES users(user_id) ON DELETE SET NULL,
  isDeleted         BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt         timestamptz NOT NULL DEFAULT now(),
  createdAt         timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_import_export_configs_name ON import_export_configs(name) WHERE isDeleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_import_export_configs_type ON import_export_configs(type) WHERE isDeleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_import_export_configs_table ON import_export_configs(table_name) WHERE isDeleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_import_export_configs_org ON import_export_configs(org_id) WHERE isDeleted = FALSE;

-- Example configuration for products import/export
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 NOTHING;

-- Example configuration for restaurant products (same as products)
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 NOTHING;

COMMENT ON TABLE import_export_configs IS 'Stores configurations for import/export operations on various tables';
COMMENT ON COLUMN import_export_configs.config IS 'JSON configuration including field mappings, transformations, validations, etc.';
