swagger: '2.0'
info:
  description: >-
    DATA Tables API

  version: "5.0"
  title: DATA Tables
  license:
    name: Apache 2.0
    url: 'http://www.apache.org/licenses/LICENSE-2.0.html'
basePath: /index.php/api/
tags:
  - name: Tables
    description: Data Tables
x-tagGroups:
  - name: Tables 
    tags:
      - Tables
schemes:
  - http
  - https
paths:
  /tables:
      get:
        tags:
          - Tables
        summary: List all tables
        description: Returns a list of all tables
        operationId: listTables
        consumes:
          - application/json
        produces:
          - application/json
        responses:
          '200':
            description: successful operation            
        security:
          - ApiKeyAuth: []
  /tables/info/{dbId}/{tableId}:
      get:
        tags:
          - Tables
        summary: Get table info
        description: Returns table definition
        operationId: tableInfo
        consumes:
          - application/json
        produces:
          - application/json
        parameters:
          - in: "path"
            name: "dbId"
            type: string
            format: string
            required: true
          - in: "path"
            name: "tableId"
            type: string
            format: string
            required: true
        responses:
          '200':
            description: successful operation
            schema:
              $ref: "#/definitions/TableInfoResponse"
        security:
          - ApiKeyAuth: []
  /tables/data/{dbId}/{tableId}:
      get:
        tags:
          - Tables
        summary: Get table data
        description: Get table data
        operationId: getTableData
        consumes:
          - application/json
        produces:
          - application/json
        parameters:
          - in: "path"
            name: "dbId"
            type: string
            format: string
            required: true
          - in: "path"
            name: "tableId"
            type: string
            format: string
            required: true
          - in: "path"
            name: "limit"
            type: integer
            required: true
          - in: "path"
            name: "offset"
            type: integer
            required: false
          - in: "query"
            name: "indicator"
            type: integer
            required: false      
          - in: "query"
            name: "<name of feature>"
            type: string
            required: false 
          - in: "query"
            name: "fields"
            type: string
            description: A comma seperated list of fields to output
            required: false                      
          - in: "query"
            name: "format"
            type: string
            description: Output format - json, csv
            required: false

        responses:
          '200':
            description: successful operation
        security:
          - ApiKeyAuth: []       
  
  /tables/create_table:
      post:
        tags:
          - Tables
        summary: Create new table
        description: Create new table type
        operationId: createTableType
        consumes:
          - application/json
        produces:
          - application/json
        parameters:
          - in: "body"
            name: "body"
            required: true
            schema:
              $ref: 'table_type-schema.json'
        responses:
          '200':
            description: successful operation
            schema:
              $ref: 'table_type-schema.json'
        security:
          - ApiKeyAuth: [] 
                   
  /tables/upload/{dbId}/{tableId}:
      post:
        tags:
          - Tables
        summary: Upload CSV file
        description: Upload CSV or ZIP file and create/update table definition. This is the first step in the import workflow, it only uploads the file and prepares the table structure. Use the `/import` endpoint to actually process and import the data into the database. For ZIP files, the CSV file must match the ZIP filename (e.g., `data.zip` must contain `data.csv`).
        operationId: uploadCsv
        consumes:
          - multipart/form-data
        produces:
          - application/json
        parameters:
          - in: "path"
            name: "dbId"
            description: Database ID
            type: string
            required: true
          - in: "path"
            name: "tableId"
            description: Table ID
            type: string
            required: true  
          - in: "formData"
            name: "file"
            type: file
            description: "CSV or ZIP file containing CSV. For ZIP files: CSV filename must match ZIP filename (e.g., data.zip → data.csv). Supported CSV extensions: .csv, .txt, .CSV, .TXT"
            required: true
          - in: "formData"
            name: "title"
            type: string
            description: "Table title"
            required: false
          - in: "formData"
            name: "description"
            type: string
            description: "Table description"
            required: false
          - in: "formData"
            name: "indicators"
            type: string
            description: "JSON string of indicators"
            required: false
          - in: "formData"
            name: "feature_1"
            type: string
            description: "Feature 1 (JSON string)"
            required: false
          - in: "formData"
            name: "feature_2"
            type: string
            description: "Feature 2 (JSON string)"
            required: false
        responses:
          '200':
            description: successful operation
            schema:
              $ref: "#/definitions/UploadResponse"
          '400':
            description: "Upload error"
            schema:
              type: object
              properties:
                status:
                  type: string
                  example: "failed"
                message:
                  type: string
                  examples:
                    zip_extraction_failed: "Failed to unzip file: filename.zip"
                    csv_not_found: "CSV file not found in ZIP"
                    file_upload_failed: "File upload failed"
        security:
          - ApiKeyAuth: []

  /tables/import/{dbId}/{tableId}:
      post:
        tags:
          - Tables
        summary: Import CSV data
        description: Import CSV data in chunks for large files. Requires that a file has been uploaded using the `/upload` endpoint first. Automatically resumes from last byte position. Use `status=true` to check import progress without processing data.
        operationId: importCsv
        consumes:
          - application/json
        produces:
          - application/json
        parameters:
          - in: "path"
            name: "dbId"
            description: Database ID
            type: string
            required: true
          - in: "path"
            name: "tableId"
            description: Table ID
            type: string
            required: true
          - in: "body"
            name: "body"
            description: "Import parameters"
            required: false
            schema:
              type: object
              properties:
                status:
                  type: boolean
                  description: "If true, returns only import status without processing data (default: false)"
                  default: false
                max_time:
                  type: integer
                  description: "Maximum execution time in seconds (default: 30, min: 10, max: 120). Processes as many rows as possible within this time."
                  minimum: 10
                  maximum: 120
                  default: 30
                delimiter:
                  type: string
                  description: "CSV delimiter - comma, tab, semicolon (default: comma)"
                  enum: ["comma", "tab", "semicolon", ",", ";"]
                  default: "comma"
        responses:
          '200':
            description: successful operation
            schema:
              $ref: "#/definitions/ImportResponse"
          '400':
            description: "Validation error - table has data or data inconsistency"
            schema:
              type: object
              properties:
                status:
                  type: string
                  example: "failed"
                message:
                  type: string
                  example: "Table already contains 1000 rows. Use DELETE /api/tables/{dbId}/{tableId} to clear data first."
                action_required:
                  type: string
                  enum: ["delete_data_first", "reset_import"]
                  description: "Action to take to resolve the error"
        security:
          - ApiKeyAuth: []

  /tables/{dbId}/{tableId}:
      delete:
        tags:
          - Tables
        summary: Delete table data
        description: Delete table data and optionally table definition.
        operationId: deleteTable
        consumes:
          - application/json
        produces:
          - application/json
        parameters:
          - in: "path"
            name: "dbId"
            description: Database ID
            type: string
            required: true
          - in: "path"
            name: "tableId"
            description: Table ID
            type: string
            required: true
          - in: "body"
            name: "body"
            description: "Delete options"
            required: false
            schema:
              type: object
              properties:
                delete_definition:
                  type: boolean
                  description: "Delete table definition as well (default: false)"
                  default: false
        responses:
          '200':
            description: successful operation
            schema:
              $ref: "#/definitions/DeleteResponse"
        security:
          - ApiKeyAuth: []
  
  /tables/insert/{dbId}/{tableId}:      
        post:
          tags:
            - Tables
          summary: Insert table data
          description: Insert rows of data as JSON object
          operationId: postTable
          consumes:
            - application/json
          produces:
            - application/json
          parameters:
            - in: "path"
              name: "dbId"
              type: string
              format: string
              required: true
            - in: "path"
              name: "tableId"
              type: string
              format: string
              required: true
            - in: "body"
              name: "body"
              description: "Table rows as JSON object"
              required: true
              schema:
                type: object
          responses:
            '200':
              description: successful operation
          security:
            - ApiKeyAuth: []  


  /tables/indexes/{dbId}/{tableId}:
      get:
        tags:
          - Tables
        summary: Get table indexes
        description: Returns a list of table indexes
        operationId: tableIndexes
        consumes:
          - application/json
        produces:
          - application/json
        parameters:
          - in: "path"
            name: "dbId"
            type: string
            format: string
            required: true
          - in: "path"
            name: "tableId"
            type: string
            format: string
            required: true
        responses:
          '200':
            description: successful operation
        security:
          - ApiKeyAuth: []   
      post:
        tags:
          - Tables
        summary: Create table index
        description: Create a new index
        operationId: tableIndex
        consumes:
          - application/json
        produces:
          - application/json
        parameters:
          - in: "path"
            name: "dbId"
            type: string
            format: string
            required: true
          - in: "path"
            name: "tableId"
            type: string
            format: string
            required: true
          - in: body
            name: index_fields
            description: A comma seperated list of fields for creating new index e.g. `state, district, urbrur`
            schema:
              type: object
              required:
                - index_fields
              properties:
                index_fields:
                  type: string                
        responses:
          '200':
            description: successful operation
        security:
          - ApiKeyAuth: []
      delete:
        tags:
          - Tables
        summary: Delete table index
        description: Delete a table index - `tables/indexes/{dbId}/{tableId}/{indexName}`
        operationId: tableIndexesDelete
        consumes:
          - application/json
        produces:
          - application/json
        parameters:
          - in: "path"
            name: "dbId"
            type: string
            format: string
            required: true
          - in: "path"
            name: "tableId"
            type: string
            format: string
            required: true
          - in: "path"
            name: "indexName"
            type: string
            format: string
            required: true  
        responses:
          '200':
            description: successful operation
        security:
          - ApiKeyAuth: []  
  /tables/text_index/{dbId}/{tableId}:
      post:
        tags:
          - Tables
        summary: Create fulltext index
        description: Create a fulltext index for table. Only one text index can be created per table.
        operationId: tableTextIndex
        consumes:
          - application/json
        produces:
          - application/json
        parameters:
          - in: "path"
            name: "dbId"
            type: string
            format: string
            required: true
          - in: "path"
            name: "tableId"
            type: string
            format: string
            required: true
          - in: body
            name: index_fields
            description: A comma seperated list of fields for creating new index e.g. `state, district, urbrur`
            schema:
              type: object
              required:
                - index_fields
              properties:
                index_fields:
                  type: string                
        responses:
          '200':
            description: successful operation
        security:
          - ApiKeyAuth: []
  
  /tables/attach_to_study:
      post:
        tags:
          - Tables
        summary: Attach table to a study
        description: Attach table to a study
        operationId: tableAttachToStudy
        consumes:
          - application/json
        produces:
          - application/json
        parameters:
          - in: body
            schema:
              type: object
              required:
                - db_id
                - table_id
                - sid
                - title
              properties:
                db_id:
                  type: string                
                table_id:
                  type: string
                sid:
                  type: string
                  description: Study ID
                title:
                  type: string
                  description: Dataset title
        responses:
          '200':
            description: successful operation
        security:
          - ApiKeyAuth: []  
  /tables/detach_from_study:
      post:
        tags:
          - Tables
        summary: Remove table from study
        description: Dettach table from study
        operationId: tableDettachFromStudy
        consumes:
          - application/json
        produces:
          - application/json
        parameters:
          - in: body
            schema:
              type: object
              required:
                - db_id
                - table_id
                - sid
                - title
              properties:
                db_id:
                  type: string                
                table_id:
                  type: string
                sid:
                  type: string
                  description: Study ID
                title:
                  type: string
                  description: Dataset title
        responses:
          '200':
            description: successful operation
        security:
          - ApiKeyAuth: []
  /tables/list_by_study/{sid}:
      post:
        tags:
          - Tables
        summary: List tables by study
        description: Get a list of tables attached to a study
        operationId: tableListByStudy
        consumes:
          - application/json
        produces:
          - application/json
        parameters:
          - in: url
            name: sid
            type: string
            description: Study ID
            required: true
        responses:
          '200':
            description: successful operation
        security:
          - ApiKeyAuth: []    
  
###############################################################
securityDefinitions:
  ApiKeyAuth:
    type: apiKey
    name: X-API-KEY
    in: header
definitions:
  CsvImport:
    type: object
    properties:
      db_id:
        type: string
      table_id:
        type: string
      file_path:
        type: string
        description: File name for uploaded CSV file
        example: "table-name.csv"
      delimiter:
        type: string  
        description: CSV delimiter - default is Comma(,)
        example: "tab"
    required:
      - db_id
      - table_id
      - file_path 
    xml:
      name: CsvImport

  Table:
    type: object
    properties:
      id:
        type: integer
        format: int64
        readOnly: true
      table_id:
        type: string
        required: true
        title: "Table unique IDNo"        
      title:
        type: string 
        required: true
        description: Table Title 
      description:
        required: true
        type: string 
        description: A short description
      unit_observation:
        type: string 
        required: true
        description: Unit of observation
      features:
        type: array
        required: true
        description: Features
        items:
          type: string
    xml:
      name: table

  TableInfoResponse:
    type: object
    properties:
      status:
        type: string
        example: "success"
      result:
        type: object
        properties:
          count:
            type: integer
            description: "Total number of rows in the table"
          metadata:
            type: object
            properties:
              _id:
                type: string
                description: "Table definition ID"
              title:
                type: string
                description: "Table title"
              description:
                type: string
                description: "Table description"
              table_id:
                type: string
                description: "Table ID"
              csv_file_path:
                type: string
                description: "Path to CSV file"
              delimiter:
                type: string
                description: "CSV delimiter"
              created_at:
                type: string
                format: date-time
              updated_at:
                type: string
                format: date-time
              db_id:
                type: string
                description: "Database ID"
              csv_uploaded_at:
                type: string
                format: date-time
      
  
  ApiResponse:
    type: object
    properties:
      code:
        type: integer
        format: int32
      type:
        type: string
      message:
        type: string

  UploadResponse:
    type: object
    properties:
      status:
        type: string
        example: "success"
      file_path:
        type: string
        description: "Partial file path for import"
      action:
        type: string
        enum: ["created", "updated"]
      csv_uploaded_at:
        type: string
        format: date-time
      import_status:
        type: string
        example: "ready"
      links:
        type: object
        properties:
          import:
            type: string
            description: "Import endpoint URL"
      message:
        type: string

  ZipFileRequirements:
    type: object
    description: "Requirements for ZIP files containing CSV data"
    properties:
      filename_matching:
        type: string
        description: "CSV filename must match ZIP filename (without extension)"
        example: "data.zip must contain data.csv"
      supported_extensions:
        type: array
        items:
          type: string
        description: "Supported CSV file extensions"
        example: [".csv", ".txt", ".CSV", ".TXT"]
      file_structure:
        type: string
        description: "ZIP file structure requirements"
        example: "ZIP must contain exactly one CSV file at root level"
      examples:
        type: object
        properties:
          valid:
            type: object
            properties:
              zip_file: 
                type: string
                example: "report.zip"
              csv_file:
                type: string
                example: "report.csv"
          invalid:
            type: object
            properties:
              zip_file:
                type: string
                example: "data.zip"
              csv_file:
                type: string
                example: "other.csv"
              reason:
                type: string
                example: "Filename mismatch"

  ImportResponse:
    type: object
    properties:
      status:
        type: string
        example: "success"
      csv_info:
        type: object
        properties:
          csv_file_path:
            type: string
            description: "Path to the CSV file being imported"
            example: "mydb/mytable/data.csv"
          csv_uploaded_at:
            type: string
            format: date-time
            description: "When the CSV file was uploaded"
          file_size:
            type: integer
            description: "Total file size in bytes"
            example: 524288000
          file_size_mb:
            type: number
            format: float
            description: "Total file size in megabytes"
            example: 500.0
      batch:
        type: object
        description: "Information about the current batch processed"
        properties:
          rows_processed:
            type: integer
            description: "Number of rows processed in this batch"
            example: 45000
          byte_offset_start:
            type: integer
            description: "File byte position where this batch started"
            example: 12500000
          byte_offset_end:
            type: integer
            description: "File byte position where this batch ended (resume point for next batch)"
            example: 15750000
          execution_time_seconds:
            type: number
            format: float
            description: "Execution time for this batch in seconds"
            example: 29.8
          execution_time_formatted:
            type: string
            description: "Human-readable execution time"
            example: "00h:00m:29s"
      progress:
        type: object
        description: "Overall import progress information"
        properties:
          total_rows_processed:
            type: integer
            description: "Total cumulative rows imported so far"
            example: 135000
          progress_percent:
            type: number
            format: float
            description: "Import completion percentage (based on file size)"
            example: 25.5
          import_status:
            type: string
            enum: ["ready", "in_progress", "completed"]
            description: "Current import status"
          has_more:
            type: boolean
            description: "Whether there are more batches to process"
            example: true
      next:
        type: object
        properties:
          byte_offset:
            type: integer
            description: "Byte offset for next batch (resume point)"
            example: 15750000
          endpoint:
            type: string
            description: "API endpoint to call for next batch"
            example: "http://example.com/api/tables/import/mydb/mytable"
          message:
            type: string
            example: "Call this endpoint again to continue import"
        description: "Only present if has_more is true. Contains information for resuming import."

  DeleteResponse:
    type: object
    properties:
      status:
        type: string
        example: "success"
      data_deleted:
        type: integer
        description: "Number of data rows deleted"
      definition_deleted:
        type: integer
        description: "Number of definitions deleted (0 or 1)"
      import_progress_reset:
        type: boolean
        example: true
      message:
        type: string