# ============================================================================
# SPARQL QUERIES FOR DATABASE SCHEMA GENERATION
# 15+ queries to transform RDF ontology into SQL DDL
# ============================================================================

PREFIX ggen: <http://ggen.io/ontology/database#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

# ----------------------------------------------------------------------------
# Query 1: Generate CREATE TABLE statements
# ----------------------------------------------------------------------------
# @query create_tables
# @description Generate CREATE TABLE DDL for all tables
# @output SQL CREATE TABLE statements

SELECT ?tableName ?columnDefs ?constraints
WHERE {
  ?table a ggen:Table ;
         ggen:tableName ?tableName .

  # Aggregate columns
  {
    SELECT ?table (GROUP_CONCAT(?columnDef; separator=",\n  ") AS ?columnDefs)
    WHERE {
      ?table ggen:hasColumn ?column .
      ?column ggen:columnName ?columnName ;
              ggen:hasDataType ?dataType .
      ?dataType ggen:dataTypeName ?typeName .

      OPTIONAL { ?column ggen:maxLength ?maxLength }
      OPTIONAL { ?column ggen:precision ?precision }
      OPTIONAL { ?column ggen:scale ?scale }
      OPTIONAL { ?column ggen:isNullable ?nullable }
      OPTIONAL { ?column ggen:defaultValue ?default }
      OPTIONAL { ?column ggen:isAutoIncrement ?autoInc }

      BIND(CONCAT(
        ?columnName, " ",
        ?typeName,
        IF(BOUND(?maxLength), CONCAT("(", STR(?maxLength), ")"), ""),
        IF(BOUND(?precision) && BOUND(?scale), CONCAT("(", STR(?precision), ",", STR(?scale), ")"), ""),
        IF(BOUND(?nullable) && !?nullable, " NOT NULL", ""),
        IF(BOUND(?autoInc) && ?autoInc, " AUTO_INCREMENT", ""),
        IF(BOUND(?default), CONCAT(" DEFAULT ", ?default), "")
      ) AS ?columnDef)
    }
    GROUP BY ?table
  }

  # Aggregate constraints
  OPTIONAL {
    SELECT ?table (GROUP_CONCAT(?constraintDef; separator=",\n  ") AS ?constraints)
    WHERE {
      ?table ggen:hasConstraint ?constraint .
      ?constraint ggen:constraintName ?constraintName .

      # Primary Key
      OPTIONAL {
        ?constraint a ggen:PrimaryKey ;
                    ggen:constrainsColumn ?pkColumn .
        ?pkColumn ggen:columnName ?pkColumnName .
        BIND(CONCAT("CONSTRAINT ", ?constraintName, " PRIMARY KEY (", ?pkColumnName, ")") AS ?pkDef)
      }

      # Foreign Key
      OPTIONAL {
        ?constraint a ggen:ForeignKey ;
                    ggen:constrainsColumn ?fkColumn ;
                    ggen:referencesTable ?refTable ;
                    ggen:referencesColumn ?refColumn .
        ?fkColumn ggen:columnName ?fkColumnName .
        ?refTable ggen:tableName ?refTableName .
        ?refColumn ggen:columnName ?refColumnName .
        OPTIONAL { ?constraint ggen:onDelete ?onDelete }
        OPTIONAL { ?constraint ggen:onUpdate ?onUpdate }

        BIND(CONCAT(
          "CONSTRAINT ", ?constraintName,
          " FOREIGN KEY (", ?fkColumnName, ")",
          " REFERENCES ", ?refTableName, "(", ?refColumnName, ")",
          IF(BOUND(?onDelete), CONCAT(" ON DELETE ", ?onDelete), ""),
          IF(BOUND(?onUpdate), CONCAT(" ON UPDATE ", ?onUpdate), "")
        ) AS ?fkDef)
      }

      # Unique Constraint
      OPTIONAL {
        ?constraint a ggen:UniqueConstraint ;
                    ggen:constrainsColumn ?uqColumn .
        ?uqColumn ggen:columnName ?uqColumnName .
        BIND(CONCAT("CONSTRAINT ", ?constraintName, " UNIQUE (", ?uqColumnName, ")") AS ?uqDef)
      }

      # Check Constraint
      OPTIONAL {
        ?constraint a ggen:CheckConstraint ;
                    ggen:checkExpression ?checkExpr .
        BIND(CONCAT("CONSTRAINT ", ?constraintName, " CHECK (", ?checkExpr, ")") AS ?checkDef)
      }

      BIND(COALESCE(?pkDef, ?fkDef, ?uqDef, ?checkDef) AS ?constraintDef)
    }
    GROUP BY ?table
  }
}
ORDER BY ?tableName

# ----------------------------------------------------------------------------
# Query 2: Generate CREATE INDEX statements
# ----------------------------------------------------------------------------
# @query create_indexes
# @description Generate CREATE INDEX DDL for all indexes

SELECT ?indexName ?indexType ?tableName ?columnList ?isUnique ?whereClause
WHERE {
  ?index a ?indexClass ;
         ggen:indexName ?indexName ;
         ggen:indexesColumn ?column .

  ?column ^ggen:hasColumn ?table .
  ?table ggen:tableName ?tableName .
  ?column ggen:columnName ?columnName .

  OPTIONAL { ?index ggen:isUnique ?isUnique }
  OPTIONAL { ?index ggen:whereClause ?whereClause }
  OPTIONAL { ?index ggen:indexMethod ?method }

  # Determine index type
  BIND(
    IF(?indexClass = ggen:BTreeIndex, "BTREE",
    IF(?indexClass = ggen:HashIndex, "HASH",
    IF(?indexClass = ggen:GINIndex, "GIN",
    IF(?indexClass = ggen:GiSTIndex, "GIST",
    COALESCE(?method, "BTREE"))))) AS ?indexType
  )

  # Aggregate columns for composite indexes
  {
    SELECT ?index (GROUP_CONCAT(?columnName; separator=", ") AS ?columnList)
    WHERE {
      ?index ggen:indexesColumn ?col .
      ?col ggen:columnName ?columnName .
    }
    GROUP BY ?index
  }
}
ORDER BY ?tableName ?indexName

# ----------------------------------------------------------------------------
# Query 3: Generate ALTER TABLE migration statements
# ----------------------------------------------------------------------------
# @query alter_table_migrations
# @description Generate ALTER TABLE statements for migrations

SELECT ?migrationId ?tableName ?alterStatement
WHERE {
  ?migration a ggen:Migration ;
             ggen:migrationId ?migrationId ;
             ggen:upScript ?upScript .

  # Extract ALTER TABLE statements from migration scripts
  FILTER(CONTAINS(?upScript, "ALTER TABLE"))

  # Parse table name and statement
  BIND(REPLACE(?upScript, ".*ALTER TABLE\\s+(\\w+)\\s+(.+);.*", "$1") AS ?tableName)
  BIND(REPLACE(?upScript, ".*ALTER TABLE\\s+\\w+\\s+(.+);.*", "$1") AS ?alterStatement)
}
ORDER BY ?migrationId

# ----------------------------------------------------------------------------
# Query 4: Generate trigger definitions
# ----------------------------------------------------------------------------
# @query create_triggers
# @description Generate CREATE TRIGGER DDL

SELECT ?triggerName ?tableName ?timing ?event ?level ?function ?condition
WHERE {
  ?trigger a ggen:Trigger ;
           ggen:triggerName ?triggerName ;
           ggen:triggerTiming ?timing ;
           ggen:triggerEvent ?event ;
           ggen:triggerLevel ?level ;
           ggen:triggerFunction ?function .

  ?trigger ^ggen:hasTrigger ?table .
  ?table ggen:tableName ?tableName .

  OPTIONAL { ?trigger ggen:triggerCondition ?condition }
}
ORDER BY ?tableName ?triggerName

# ----------------------------------------------------------------------------
# Query 5: Generate foreign key relationships
# ----------------------------------------------------------------------------
# @query foreign_key_relationships
# @description List all foreign key relationships for documentation

SELECT ?sourceTable ?sourceColumn ?targetTable ?targetColumn ?onDelete ?onUpdate
WHERE {
  ?fk a ggen:ForeignKey ;
      ggen:constrainsColumn ?srcColumn ;
      ggen:referencesTable ?tgtTable ;
      ggen:referencesColumn ?tgtColumn .

  ?srcColumn ^ggen:hasColumn ?srcTable ;
             ggen:columnName ?sourceColumn .
  ?srcTable ggen:tableName ?sourceTable .

  ?tgtTable ggen:tableName ?targetTable .
  ?tgtColumn ggen:columnName ?targetColumn .

  OPTIONAL { ?fk ggen:onDelete ?onDelete }
  OPTIONAL { ?fk ggen:onUpdate ?onUpdate }
}
ORDER BY ?sourceTable ?sourceColumn

# ----------------------------------------------------------------------------
# Query 6: Generate seed data inserts
# ----------------------------------------------------------------------------
# @query generate_seed_data
# @description Generate INSERT statements for seed data

SELECT ?tableName ?insertStatement
WHERE {
  ?table a ggen:Table ;
         ggen:tableName ?tableName ;
         ggen:hasColumn ?column .

  ?column ggen:columnName ?columnName ;
          ggen:defaultValue ?defaultValue .

  # Aggregate columns and values
  {
    SELECT ?table
           (GROUP_CONCAT(?columnName; separator=", ") AS ?columns)
           (GROUP_CONCAT(?defaultValue; separator=", ") AS ?values)
    WHERE {
      ?table ggen:hasColumn ?col .
      ?col ggen:columnName ?columnName ;
           ggen:defaultValue ?defaultValue .
    }
    GROUP BY ?table
  }

  BIND(CONCAT("INSERT INTO ", ?tableName, " (", ?columns, ") VALUES (", ?values, ");") AS ?insertStatement)
}
ORDER BY ?tableName

# ----------------------------------------------------------------------------
# Query 7: List all tables with metadata
# ----------------------------------------------------------------------------
# @query list_tables
# @description List all tables with column count and constraints

SELECT ?tableName ?comment (COUNT(DISTINCT ?column) AS ?columnCount) (COUNT(DISTINCT ?constraint) AS ?constraintCount)
WHERE {
  ?table a ggen:Table ;
         ggen:tableName ?tableName .

  OPTIONAL { ?table ggen:tableComment ?comment }
  OPTIONAL { ?table ggen:hasColumn ?column }
  OPTIONAL { ?table ggen:hasConstraint ?constraint }
}
GROUP BY ?tableName ?comment
ORDER BY ?tableName

# ----------------------------------------------------------------------------
# Query 8: Generate DROP TABLE statements (for down migrations)
# ----------------------------------------------------------------------------
# @query drop_tables
# @description Generate DROP TABLE DDL in correct order (reverse FK dependencies)

SELECT ?tableName ?dropStatement
WHERE {
  ?table a ggen:Table ;
         ggen:tableName ?tableName .

  # Tables with no foreign keys referencing them should be dropped first
  OPTIONAL {
    ?fk a ggen:ForeignKey ;
        ggen:referencesTable ?table .
  }

  BIND(CONCAT("DROP TABLE IF EXISTS ", ?tableName, " CASCADE;") AS ?dropStatement)
}
ORDER BY DESC(?tableName)

# ----------------------------------------------------------------------------
# Query 9: Generate column definitions with comments
# ----------------------------------------------------------------------------
# @query column_definitions
# @description List all columns with full metadata

SELECT ?tableName ?columnName ?dataType ?nullable ?default ?comment
WHERE {
  ?table a ggen:Table ;
         ggen:tableName ?tableName ;
         ggen:hasColumn ?column .

  ?column ggen:columnName ?columnName ;
          ggen:hasDataType ?dt .
  ?dt ggen:dataTypeName ?dataType .

  OPTIONAL { ?column ggen:isNullable ?nullable }
  OPTIONAL { ?column ggen:defaultValue ?default }
  OPTIONAL { ?column ggen:columnComment ?comment }
}
ORDER BY ?tableName ?columnName

# ----------------------------------------------------------------------------
# Query 10: Generate PostgreSQL-specific DDL (JSONB, arrays)
# ----------------------------------------------------------------------------
# @query postgresql_specific
# @description Generate PostgreSQL-specific features

SELECT ?tableName ?feature ?definition
WHERE {
  ?table a ggen:Table ;
         ggen:tableName ?tableName ;
         ggen:hasColumn ?column .

  ?column ggen:columnName ?columnName ;
          ggen:hasDataType ?dt .
  ?dt ggen:postgresqlType ?pgType .

  # JSONB columns
  FILTER(?pgType = "JSONB" || ?pgType = "JSON" || CONTAINS(?pgType, "[]"))

  BIND(
    IF(?pgType = "JSONB", "JSONB column with GIN index",
    IF(CONTAINS(?pgType, "[]"), "Array column",
    "JSON column")) AS ?feature
  )

  BIND(CONCAT(?columnName, " ", ?pgType) AS ?definition)
}
ORDER BY ?tableName ?columnName

# ----------------------------------------------------------------------------
# Query 11: Generate migration version tracking table
# ----------------------------------------------------------------------------
# @query migration_version_table
# @description Generate schema_migrations table for version tracking

SELECT ?ddl
WHERE {
  BIND("""
CREATE TABLE IF NOT EXISTS schema_migrations (
  version VARCHAR(255) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  execution_time_ms INTEGER
);

CREATE INDEX idx_schema_migrations_applied_at ON schema_migrations(applied_at);
""" AS ?ddl)
}

# ----------------------------------------------------------------------------
# Query 12: Generate unique constraints
# ----------------------------------------------------------------------------
# @query unique_constraints
# @description List all unique constraints

SELECT ?tableName ?constraintName ?columns
WHERE {
  ?table a ggen:Table ;
         ggen:tableName ?tableName ;
         ggen:hasConstraint ?constraint .

  ?constraint a ggen:UniqueConstraint ;
              ggen:constraintName ?constraintName .

  {
    SELECT ?constraint (GROUP_CONCAT(?columnName; separator=", ") AS ?columns)
    WHERE {
      ?constraint ggen:constrainsColumn ?col .
      ?col ggen:columnName ?columnName .
    }
    GROUP BY ?constraint
  }
}
ORDER BY ?tableName ?constraintName

# ----------------------------------------------------------------------------
# Query 13: Generate check constraints
# ----------------------------------------------------------------------------
# @query check_constraints
# @description List all CHECK constraints

SELECT ?tableName ?constraintName ?expression
WHERE {
  ?table a ggen:Table ;
         ggen:tableName ?tableName ;
         ggen:hasConstraint ?constraint .

  ?constraint a ggen:CheckConstraint ;
              ggen:constraintName ?constraintName ;
              ggen:checkExpression ?expression .
}
ORDER BY ?tableName ?constraintName

# ----------------------------------------------------------------------------
# Query 14: Generate full-text search indexes
# ----------------------------------------------------------------------------
# @query fulltext_indexes
# @description Generate full-text search index DDL

SELECT ?indexName ?tableName ?columns ?config
WHERE {
  ?index a ggen:FullTextIndex ;
         ggen:indexName ?indexName .

  ?index ^ggen:hasIndex ?table .
  ?table ggen:tableName ?tableName .

  {
    SELECT ?index (GROUP_CONCAT(?columnName; separator=", ") AS ?columns)
    WHERE {
      ?index ggen:indexesColumn ?col .
      ?col ggen:columnName ?columnName .
    }
    GROUP BY ?index
  }

  BIND("english" AS ?config)  # Default to English text search config
}
ORDER BY ?tableName ?indexName

# ----------------------------------------------------------------------------
# Query 15: Generate composite indexes
# ----------------------------------------------------------------------------
# @query composite_indexes
# @description List composite indexes (multi-column)

SELECT ?indexName ?tableName ?columns (COUNT(?column) AS ?columnCount)
WHERE {
  ?index ggen:indexName ?indexName ;
         ggen:indexesColumn ?column .

  ?column ^ggen:hasColumn ?table .
  ?table ggen:tableName ?tableName .
  ?column ggen:columnName ?columnName .

  {
    SELECT ?index (GROUP_CONCAT(?columnName; separator=", ") AS ?columns)
    WHERE {
      ?index ggen:indexesColumn ?col .
      ?col ggen:columnName ?columnName .
    }
    GROUP BY ?index
  }
}
GROUP BY ?indexName ?tableName ?columns
HAVING (COUNT(?column) > 1)
ORDER BY ?tableName ?indexName

# ----------------------------------------------------------------------------
# Query 16: Data type mapping (XSD to SQL)
# ----------------------------------------------------------------------------
# @query data_type_mapping
# @description Map XSD types to database-specific types

SELECT ?xsdType ?postgresqlType ?mysqlType ?sqliteType
WHERE {
  ?dataType a ggen:DataType ;
            ggen:xsdType ?xsdType .

  OPTIONAL { ?dataType ggen:postgresqlType ?postgresqlType }
  OPTIONAL { ?dataType ggen:mysqlType ?mysqlType }
  OPTIONAL { ?dataType ggen:sqliteType ?sqliteType }
}
ORDER BY ?xsdType
