"""JSONSchema for Proper Ingestion workflow."""

schemas = {
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "description": "Schemas of the MySQL tables and corresponding CSV files",
    "properties": {
        "stock": {
            "type": "object",
            "description": "Stock ESSN table/CSV file",
            "sql_options": {
                "load_data_replace": "true",
                "ingest_to_mysql_mask": "Stock_ESSN",
                "table_name": "proper_stock_essn",
                "csv_file_to_validate": "Stock_ESSN",
                "columns_names": {
                    "type": "array",
                    "items": [
                        "catalogue_number",
                        "on_hand",
                        "allocated",
                        "faulty",
                        "consignment",
                        "available",
                        "label_code",
                        "ean"
                    ]
                },
                "csv_file_header": {
                    "type": "array",
                    "items": [
                        "CatNumber",
                        "OnHand",
                        "Allocated",
                        "Faulty",
                        "Consignment",
                        "Available",
                        "LabelCode",
                        "EAN"
                    ]
                }
            },
            "row_schema": {
                "type": "object",
                "properties": {
                    "CatNumber": {
                        "type": "string"
                    },
                    "OnHand": {
                        "type": "string",
                        "pattern": "^-{0,1}[0-9]+$"
                    },
                    "Allocated": {
                        "type": "string",
                        "pattern": "^-{0,1}[0-9]+$"
                    },
                    "Faulty": {
                        "type": "string",
                        "pattern": "^[0-9]+$"
                    },
                    "Consignment": {
                        "type": "string",
                        "pattern": "^-{0,1}[0-9]+$"
                    },
                    "Available": {
                        "type": "string",
                        "pattern": "^-{0,1}[0-9]+$"
                    },
                    "LabelCode": {
                        "type": "string",
                        "minLength": 3,
                        "maxLength": 6
                    },
                    "EAN": {
                        "type": "string",
                        "minLength": 13
                    }
                }
            }
        },
        "goodsin": {
            "type": "object",
            "description": "GoodsIn ESSN table/CSV file",
            "sql_options": {
                "snowflake_table_name": "proper_goodsin_essn",
                "extend_csv_before_ingestion_to_sf": "True",
                "s3_path_schema_template":
                    "s3://{bucket}/ProperIncoming/snowflake_schema/"
                    "{proper_feed_type}_schema.json",
                "csv_file_to_validate": "GoodsIn_ESSN",
                "csv_file_header": {
                    "type": "array",
                    "items": [
                        "Supplier",
                        "OrderNumber",
                        "DateRec",
                        "DateComplete",
                        "CatNumber",
                        "Artist",
                        "Title",
                        "LabelCode",
                        "LabelName",
                        "SubLabel",
                        "Received"
                    ]
                }
            },
            "row_schema": {
                "type": "object",
                "properties": {
                    "Supplier": {
                        "type": "string"
                    },
                    "OrderNumber": {
                        "type": "string"
                    },
                    "DateRec": {
                        "type": "string",
                        "minLength": 0
                    },
                    "DateComplete": {
                        "type": "string",
                        "pattern": r"^\d{4}-\d{2}-\d{2}$"
                    },
                    "CatNumber": {
                        "type": "string"
                    },
                    "Artist": {
                        "type": "string"
                    },
                    "Title": {
                        "type": "string"
                    },
                    "LabelCode": {
                        "type": "string",
                        "minLength": 4,
                        "maxLength": 6
                    },
                    "LabelName": {
                        "type": "string"
                    },
                    "SubLabel": {
                        "type": "string"
                    },
                    "Received": {
                        "type": "string",
                        "pattern": "^[0-9]+$"
                    }
                }
            }
        },
        "shortages": {
            "type": "object",
            "description": "Shortages ESSN table/CSV file",
            "sql_options": {
                "snowflake_table_name": "proper_shortages_essn",
                "extend_csv_before_ingestion_to_sf": "True",
                "s3_path_schema_template":
                    "s3://{bucket}/ProperIncoming/snowflake_schema/"
                    "{proper_feed_type}_schema.json",
                "csv_file_to_validate": "Shortages_ESSN",
                "csv_file_header": {
                    "type": "array",
                    "items": [
                        "AccountCode",
                        "ShopNumer",
                        "AccountName",
                        "CustomerId",
                        "CatNumber",
                        "Barcode",
                        "LabelCode",
                        "Ordered",
                        "Shipped",
                        "DateDespatched",
                        "CustomerRef",
                        "OrderNumber",
                        "Category"
                    ]
                }
            },
            "row_schema": {
                "type": "object",
                "properties": {
                    "AccountCode": {
                        "type": "string"
                    },
                    "ShopNumer": {
                        "type": "string",
                        "pattern": "^[0-9]+$"
                    },
                    "AccountName": {
                        "type": "string"
                    },
                    "CustomerId": {
                        "type": "string",
                        "pattern": "^[0-9]+$"
                    },
                    "CatNumber": {
                        "type": "string"
                    },
                    "Barcode": {
                        "type": "string"
                    },
                    "LabelCode": {
                        "type": "string",
                        "minLength": 4,
                        "maxLength": 6
                    },
                    "Ordered": {
                        "type": "string",
                        "pattern": "^[0-9]+$"
                    },
                    "Shipped": {
                        "type": "string",
                        "pattern": "^[0-9]+$"
                    },
                    "DateDespatched": {
                        "type": "string",
                        "pattern": r"^\d{4}-\d{2}-\d{2}$"
                    },
                    "CustomerRef": {
                        "type": "string",
                        "required": "true"
                    },
                    "OrderNumber": {
                        "type": "string"
                    },
                    "Category": {
                        "type": "string"
                    }
                }
            }
        },
        "sales": {
            "type": "object",
            "description": "Sales ESSN CSV file",
            "sql_options": {
                "csv_file_to_validate": "Sales_ESSN",
                "csv_file_header": {
                    "type": "array",
                    "items": [
                        "CatNumber",
                        "Barcode",
                        "StoreCode",
                        "StoreNumber",
                        "StoreID",
                        "OrderNumber",
                        "LineId",
                        "InvoiceNumber",
                        "InvoiceDate",
                        "Units",
                        "DealerPrice",
                        "InvoicePrice",
                        "DiscountPercent",
                        "OrderType",
                        "VatCode",
                        "CustomerRef",
                        "ExchangeRate",
                        "CurrencyCode",
                        "AccountName",
                        "CountryCode"
                    ]
                }
            },
            "row_schema": {
                "type": "object",
                "properties": {
                    "CatNumber": {
                        "type": "string"
                    },
                    "Barcode": {
                        "type": "string"
                    },
                    "StoreCode": {
                        "type": "string"
                    },
                    "StoreNumber": {
                        "type": "string",
                        "pattern": "^[0-9]+$"
                    },
                    "StoreID": {
                        "type": "string",
                        "pattern": "^[0-9]+$"
                    },
                    "OrderNumber": {
                        "type": "string"
                    },
                    "LineId": {
                        "type": "string"
                    },
                    "InvoiceNumber": {
                        "type": "string"
                    },
                    "InvoiceDate": {
                        "type": "string",
                        "pattern": r"^\d{4}-\d{2}-\d{2}$"
                    },
                    "Units": {
                        "type": "string",
                        "pattern": "^[0-9]+$"
                    },
                    "DealerPrice": {
                        "type": "string",
                        "pattern": r"^\d+$|^\d+.{0,1}\d+$|^.\d+$"
                    },
                    "InvoicePrice": {
                        "type": "string",
                        "pattern": r"^\d+$|^\d{0,30}.{0,1}\d+$|^.\d+$"
                    },
                    "DiscountPercent": {
                        "type": "string",
                        "pattern": r"^\d+$|^\d+.{0,1}\d+$|^.\d+$"
                    },
                    "OrderType": {
                        "type": "string",
                        "pattern": "^ORDER$|^RETURN$"
                    },
                    "VatCode": {
                        "type": "string",
                        "pattern": "^[0-9]+$"
                    },
                    "CustomerRef": {
                        "type": "string"
                    },
                    "ExchangeRate": {
                        "type": "string",
                        "pattern": r"^\d+$|^\d+.{0,1}\d+$|^.\d+$"
                    },
                    "CurrencyCode": {
                        "type": "string",
                        "maxLength": 3
                    },
                    "AccountName": {
                        "type": "string",
                        "maxLength": 30
                    },
                    "CountryCode": {
                        "type": "string",
                        "maxLength": 3
                    }
                }
            }
        }
    }
}