name: "database/mysql"
version: "1.0.0"
description: "MySQL database connection and management module"
author: "MCP Backend Generator"
license: "MIT"

triggers:
  - "database"
  - "mysql"
  - "db"
  - "connection"
  - "migration"
  - "schema"

entry_points:
  - name: "setup_mysql"
    description: "Configure MySQL connection and create database structure"
    file: "init.js"
    function: "setupMySQL"

dependencies:
  npm:
    - "mysql2@^3.6.0"
    - "@types/mysql2@^3.0.0"
    - "prisma@^5.0.0"
    - "@prisma/client@^5.0.0"
  system:
    - "mysql"

environment_variables:
  required:
    - DATABASE_URL
    - MYSQL_HOST
    - MYSQL_PORT
    - MYSQL_DATABASE
    - MYSQL_USER
    - MYSQL_PASSWORD
  optional:
    - MYSQL_SSL
    - MYSQL_POOL_SIZE
    - MYSQL_TIMEOUT
    - MYSQL_CHARSET

features:
  - "Connection pooling"
  - "Migration management"
  - "Schema validation"
  - "Query optimization"
  - "Transaction support"
  - "SSL/TLS encryption"
  - "Health checks"
  - "Backup utilities"

automation:
  setup:
    - "Create database connection"
    - "Run initial migrations"
    - "Validate schema"
    - "Setup connection pool"
  migration:
    - "Generate migration files"
    - "Apply pending migrations"
    - "Rollback migrations"
  backup:
    - "Create database backup"
    - "Restore from backup"
    - "Schedule automated backups"

generated_files:
  - "config/database.ts"
  - "prisma/schema.prisma"
  - "migrations/"
  - "seeds/"
  - "types/database.ts"
  - "utils/connection.ts"
  - "utils/migration.ts"
  - "utils/backup.ts"

schema_tables:
  users:
    columns:
      - { name: "id", type: "VARCHAR(36)", primary: true }
      - { name: "email", type: "VARCHAR(255)", unique: true }
      - { name: "password_hash", type: "VARCHAR(255)" }
      - { name: "first_name", type: "VARCHAR(100)" }
      - { name: "last_name", type: "VARCHAR(100)" }
      - { name: "role", type: "VARCHAR(50)", default: "user" }
      - { name: "is_active", type: "BOOLEAN", default: true }
      - { name: "email_verified", type: "BOOLEAN", default: false }
      - { name: "created_at", type: "TIMESTAMP", default: "CURRENT_TIMESTAMP" }
      - { name: "updated_at", type: "TIMESTAMP", default: "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" }
    indexes:
      - { columns: ["email"], unique: true }
      - { columns: ["role"] }
      - { columns: ["created_at"] }

  categories:
    columns:
      - { name: "id", type: "VARCHAR(36)", primary: true }
      - { name: "name", type: "VARCHAR(100)", unique: true }
      - { name: "description", type: "TEXT" }
      - { name: "parent_id", type: "VARCHAR(36)", foreign_key: "categories.id" }
      - { name: "is_active", type: "BOOLEAN", default: true }
      - { name: "created_at", type: "TIMESTAMP", default: "CURRENT_TIMESTAMP" }
      - { name: "updated_at", type: "TIMESTAMP", default: "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" }
    indexes:
      - { columns: ["name"], unique: true }
      - { columns: ["parent_id"] }

  products:
    columns:
      - { name: "id", type: "VARCHAR(36)", primary: true }
      - { name: "name", type: "VARCHAR(200)" }
      - { name: "description", type: "TEXT" }
      - { name: "sku", type: "VARCHAR(100)", unique: true }
      - { name: "price", type: "DECIMAL(10,2)" }
      - { name: "category_id", type: "VARCHAR(36)", foreign_key: "categories.id" }
      - { name: "stock_quantity", type: "INT", default: 0 }
      - { name: "is_active", type: "BOOLEAN", default: true }
      - { name: "created_at", type: "TIMESTAMP", default: "CURRENT_TIMESTAMP" }
      - { name: "updated_at", type: "TIMESTAMP", default: "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" }
    indexes:
      - { columns: ["sku"], unique: true }
      - { columns: ["category_id"] }
      - { columns: ["price"] }
      - { columns: ["stock_quantity"] }

  inventory_movements:
    columns:
      - { name: "id", type: "VARCHAR(36)", primary: true }
      - { name: "product_id", type: "VARCHAR(36)", foreign_key: "products.id" }
      - { name: "movement_type", type: "VARCHAR(20)" }
      - { name: "quantity", type: "INT" }
      - { name: "reference", type: "VARCHAR(100)" }
      - { name: "notes", type: "TEXT" }
      - { name: "created_at", type: "TIMESTAMP", default: "CURRENT_TIMESTAMP" }
    indexes:
      - { columns: ["product_id"] }
      - { columns: ["movement_type"] }
      - { columns: ["created_at"] }

integrations:
  - name: "auth"
    description: "User authentication and authorization"
  - name: "crud"
    description: "Automatic CRUD generation for database tables"
  - name: "logging"
    description: "Database query logging and monitoring"
  - name: "cache"
    description: "Query result caching"

examples:
  - name: "basic_connection"
    description: "Basic MySQL connection setup"
    file: "examples/basic-connection.js"
  - name: "migration_example"
    description: "Database migration example"
    file: "examples/migration.js"
  - name: "query_optimization"
    description: "Query optimization techniques"
    file: "examples/optimization.js"