<#
Deploy Upright DB Schema for Appwrite 1.8.1 (TablesDB)
- Writes appwrite.config.json
- Runs: appwrite push tables --force

Usage example:
.\deploy-upright-schema.ps1 `
  -ProjectId "YOUR_PROJECT_ID" `
  -Endpoint "https://YOUR_HOST_OR_REGION/v1" `
  -DatabaseId "upright" `
  -DatabaseName "upright"
#>

param(
  [Parameter(Mandatory=$true)][string]$ProjectId,
  [Parameter(Mandatory=$true)][string]$Endpoint,
  [Parameter(Mandatory=$true)][string]$DatabaseId,
  [Parameter(Mandatory=$false)][string]$DatabaseName = "upright"
)

$ErrorActionPreference = "Stop"

function New-IndexObject {
  param(
    [Parameter(Mandatory=$true)][string]$Key,
    [Parameter(Mandatory=$true)][ValidateSet("key","unique","fulltext")][string]$Type,
    [Parameter(Mandatory=$true)][string[]]$Attributes
  )
  return @{
    key = $Key
    type = $Type
    attributes = $Attributes
  }
}

function New-ColumnString {
  param(
    [Parameter(Mandatory=$true)][string]$Key,
    [int]$Size = 255,
    [bool]$Required = $false,
    [bool]$Array = $false,
    $Default = $null
  )
  return @{
    key = $Key
    type = "string"
    status = "available"
    error = ""
    required = $Required
    array = $Array
    size = $Size
    default = $Default
  }
}

function New-ColumnUrl {
  param(
    [Parameter(Mandatory=$true)][string]$Key,
    [int]$Size = 2048,
    [bool]$Required = $false,
    [bool]$Array = $false,
    $Default = $null
  )
  # In config, url is usually type "string" with format "url"
  return @{
    key = $Key
    type = "string"
    format = "url"
    status = "available"
    error = ""
    required = $Required
    array = $Array
    size = $Size
    default = $Default
  }
}

function New-ColumnDatetime {
  param(
    [Parameter(Mandatory=$true)][string]$Key,
    [bool]$Required = $false,
    [bool]$Array = $false,
    $Default = $null
  )
  return @{
    key = $Key
    type = "datetime"
    status = "available"
    error = ""
    required = $Required
    array = $Array
    default = $Default
  }
}

function New-ColumnBoolean {
  param(
    [Parameter(Mandatory=$true)][string]$Key,
    [bool]$Required = $false,
    [bool]$Array = $false,
    $Default = $null
  )
  return @{
    key = $Key
    type = "boolean"
    status = "available"
    error = ""
    required = $Required
    array = $Array
    default = $Default
  }
}

function New-ColumnInteger {
  param(
    [Parameter(Mandatory=$true)][string]$Key,
    [bool]$Required = $false,
    [bool]$Array = $false,
    $Default = $null
  )
  return @{
    key = $Key
    type = "integer"
    status = "available"
    error = ""
    required = $Required
    array = $Array
    default = $Default
  }
}

function New-ColumnFloat {
  param(
    [Parameter(Mandatory=$true)][string]$Key,
    [bool]$Required = $false,
    [bool]$Array = $false,
    $Default = $null
  )
  return @{
    key = $Key
    type = "float"
    status = "available"
    error = ""
    required = $Required
    array = $Array
    default = $Default
  }
}

function New-ColumnEnumString {
  param(
    [Parameter(Mandatory=$true)][string]$Key,
    [Parameter(Mandatory=$true)][string[]]$Elements,
    [int]$Size = 50,
    [bool]$Required = $false,
    [bool]$Array = $false,
    $Default = $null
  )
  # Enum in Appwrite config is typically string with format "enum" and "elements"
  return @{
    key = $Key
    type = "string"
    format = "enum"
    elements = $Elements
    status = "available"
    error = ""
    required = $Required
    array = $Array
    size = $Size
    default = $Default
  }
}

# Tables
$usersTableId = "users"
$accountsTableId = "accounts"
$productsTableId = "products"
$productDetailsTableId = "product_details"

$tables = @()

# users
$tables += @{
  '$id' = $usersTableId
  '$permissions' = @(
    'create("any")','read("any")','update("any")','delete("any")'
  )
  databaseId = $DatabaseId
  name = "users"
  enabled = $true
  rowSecurity = $false
  columns = @(
    New-ColumnDatetime -Key "user_created_at"
    New-ColumnDatetime -Key "user_updated_at"
  )
  indexes = @()
}

# accounts
$tables += @{
  '$id' = $accountsTableId
  '$permissions' = @(
    'create("any")','read("any")','update("any")','delete("any")'
  )
  databaseId = $DatabaseId
  name = "accounts"
  enabled = $true
  rowSecurity = $false
  columns = @(
    New-ColumnString -Key "account_owner_user_id" -Size 128 -Required $false
    New-ColumnBoolean -Key "account_team" -Required $true -Default $false

    New-ColumnEnumString -Key "account_platform" -Elements @("amazon","ebay") -Size 20 -Required $true
    New-ColumnString -Key "account_platform_account_id" -Size 128 -Required $true
    New-ColumnString -Key "account_platform_market" -Size 50 -Required $true

    New-ColumnString -Key "account_shop_name" -Size 256 -Required $false
    New-ColumnUrl -Key "account_url" -Size 2048 -Required $false

    New-ColumnEnumString -Key "account_status" -Elements @("active","unknown","disabled") -Size 20 -Required $false

    New-ColumnDatetime -Key "account_created_at"
    New-ColumnDatetime -Key "account_updated_at"
  )
  indexes = @(
    (New-IndexObject -Key "uniq_platform_market_accountid" -Type "unique" -Attributes @(
      "account_platform","account_platform_market","account_platform_account_id"
    )),
    (New-IndexObject -Key "idx_owner_user" -Type "key" -Attributes @("account_owner_user_id")),
    (New-IndexObject -Key "idx_team" -Type "key" -Attributes @("account_team"))
  )
}

# products (sparscan + vollscan in einem)
$tables += @{
  '$id' = $productsTableId
  '$permissions' = @(
    'create("any")','read("any")','update("any")','delete("any")'
  )
  databaseId = $DatabaseId
  name = "products"
  enabled = $true
  rowSecurity = $false
  columns = @(
    New-ColumnString -Key "product_account_id" -Size 36 -Required $true

    New-ColumnEnumString -Key "product_platform" -Elements @("amazon","ebay") -Size 20 -Required $true
    New-ColumnString -Key "product_platform_market" -Size 50 -Required $true
    New-ColumnString -Key "product_platform_product_id" -Size 128 -Required $true

    New-ColumnUrl -Key "product_url" -Size 2048 -Required $true
    New-ColumnString -Key "product_title" -Size 512 -Required $false

    New-ColumnEnumString -Key "product_condition" -Elements @(
      "new","used_like_new","used_good","used_ok","parts","unknown"
    ) -Size 30 -Required $false -Default "unknown"

    New-ColumnEnumString -Key "product_status" -Elements @(
      "active","ended","unknown"
    ) -Size 20 -Required $false -Default "unknown"

    # volatile (sparscan)
    New-ColumnFloat -Key "product_price" -Required $false
    New-ColumnString -Key "product_currency" -Size 10 -Required $false
    New-ColumnInteger -Key "product_quantity_available" -Required $false
    New-ColumnInteger -Key "product_quantity_sold" -Required $false
    New-ColumnInteger -Key "product_watch_count" -Required $false
    New-ColumnInteger -Key "product_in_carts_count" -Required $false
    New-ColumnDatetime -Key "product_last_seen_at" -Required $false

    # scan bookkeeping
    New-ColumnDatetime -Key "product_first_fullscan_at" -Required $false
    New-ColumnDatetime -Key "product_last_fullscan_at" -Required $false
    New-ColumnDatetime -Key "product_last_sparscan_at" -Required $false
    New-ColumnInteger -Key "product_details_version" -Required $false
  )
  indexes = @(
    (New-IndexObject -Key "idx_product_account" -Type "key" -Attributes @("product_account_id")),
    (New-IndexObject -Key "uniq_account_platform_product" -Type "unique" -Attributes @(
      "product_account_id","product_platform_product_id"
    )),
    (New-IndexObject -Key "idx_platform_market" -Type "key" -Attributes @(
      "product_platform","product_platform_market"
    )),
    (New-IndexObject -Key "idx_status" -Type "key" -Attributes @("product_status")),
    (New-IndexObject -Key "ft_title" -Type "fulltext" -Attributes @("product_title"))
  )
}

# product_details (schwer)
# WICHTIG: Alle Attribute verwenden "product_detail_" Präfix (Singular) nicht "details_"
$tables += @{
  '$id' = $productDetailsTableId
  '$permissions' = @(
    'create("any")','read("any")','update("any")','delete("any")'
  )
  databaseId = $DatabaseId
  name = "product_details"
  enabled = $true
  rowSecurity = $false
  columns = @(
    New-ColumnString -Key "product_id" -Size 36 -Required $true

    New-ColumnString -Key "product_detail_category_path" -Size 512 -Required $false
    New-ColumnString -Key "product_detail_brand" -Size 128 -Required $false
    New-ColumnString -Key "product_detail_mpn" -Size 128 -Required $false
    New-ColumnString -Key "product_detail_gtin" -Size 128 -Required $false

    New-ColumnString -Key "product_detail_description_text" -Size 20000 -Required $false

    New-ColumnUrl -Key "product_detail_image_urls" -Size 2048 -Required $false -Array $true

    New-ColumnString -Key "product_detail_item_specifics_json" -Size 20000 -Required $false
    New-ColumnBoolean -Key "product_detail_has_variations" -Required $false
    New-ColumnString -Key "product_detail_variations_json" -Size 20000 -Required $false

    New-ColumnDatetime -Key "product_detail_last_updated_at" -Required $false
  )
  indexes = @(
    (New-IndexObject -Key "uniq_product_id" -Type "unique" -Attributes @("product_id"))
  )
}

# appwrite.config.json root
$config = @{
  projectId = $ProjectId
  endpoint  = $Endpoint
  tablesDB  = @(
    @{
      '$id' = $DatabaseId
      name  = $DatabaseName
      enabled = $true
    }
  )
  tables = $tables
}

$configPath = Join-Path -Path (Get-Location) -ChildPath "appwrite.config.json"
($config | ConvertTo-Json -Depth 20) | Set-Content -Path $configPath -Encoding UTF8

Write-Host "Wrote: $configPath"
Write-Host "Now pushing schema via: appwrite push tables --force"

# Push schema (requires: appwrite login, appwrite init project)
& appwrite push tables --force