# MySQL Configuration for DaProductApp

This project has been configured to use MySQL with auto-increment IDs. Below are the key configurations and features implemented.

## Key Changes Made

### 1. Database Adapter Configuration
- **Repository**: Updated `lib/da_product_app/repo.ex` to use `Ecto.Adapters.MyXQL`
- **Development**: Added MySQL adapter and charset configuration in `config/dev.exs`
- **Test**: Added MySQL adapter configuration in `config/test.exs`
- **Production**: Enhanced MySQL configuration in `config/runtime.exs`

### 2. Auto-Increment Primary Keys
- **Migration Configuration**: Updated `config/config.exs` to use auto-increment for primary keys
- **Helper Module**: Created `DaProductApp.MigrationHelpers` for MySQL-specific migrations
- **Sample Schema**: Created `DaProductApp.Accounts.User` with proper auto-increment configuration

### 3. MySQL-Specific Settings
- **Charset**: Set to `utf8mb4` for full Unicode support
- **Collation**: Set to `utf8mb4_unicode_ci`
- **SQL Mode**: Configured for strict mode with proper error handling
- **Connection Pooling**: Optimized for MySQL connections

## Database Configuration

### Development Environment
```elixir
config :da_product_app, DaProductApp.Repo,
  adapter: Ecto.Adapters.MyXQL,
  username: "root",
  password: "dataaegis123",
  hostname: "localhost",
  database: "lic_project",
  charset: "utf8mb4",
  collation: "utf8mb4_unicode_ci",
  pool_size: 10
```

### Production Environment
Uses `DATABASE_URL` environment variable with MySQL-specific options.

## Auto-Increment Setup

### 1. Schema Configuration
```elixir
defmodule YourApp.Schema do
  use Ecto.Schema
  
  @primary_key {:id, :id, autogenerate: true}
  @derive {Phoenix.Param, key: :id}
  
  schema "table_name" do
    # your fields
    timestamps()
  end
end
```

### 2. Migration Helper Usage
```elixir
defmodule YourApp.Repo.Migrations.CreateTable do
  use Ecto.Migration
  import DaProductApp.MigrationHelpers

  def up do
    create_auto_increment_table(:table_name) do
      add :field1, :string, null: false
      add :field2, :integer
      # more fields...
    end
    
    create unique_index(:table_name, [:field1])
    setup_mysql_table_charset(:table_name)
  end

  def down do
    drop table(:table_name)
  end
end
```

## Setup Instructions

### 1. Database Setup
```bash
# Run the MySQL setup script
./setup_mysql.sh

# Or manually create databases
mysql -u root -p -e "CREATE DATABASE lic_project CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -u root -p -e "CREATE DATABASE lic_project_testingteam CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
```

### 2. Phoenix Setup
```bash
# Install dependencies
mix deps.get

# Create and migrate database
mix ecto.create
mix ecto.migrate

# Start the server
mix phx.server
```

## Key Features

### 1. Auto-Increment Primary Keys
- All tables use `BIGINT AUTO_INCREMENT` primary keys
- Proper configuration for Ecto schema integration
- Consistent ID generation across all models

### 2. MySQL Optimization
- UTF8MB4 charset for full Unicode support
- Proper collation for international data
- Strict SQL mode for data integrity
- Optimized connection pooling

### 3. Migration Helpers
- `create_auto_increment_table/2`: Creates tables with auto-increment PKs
- `add_foreign_key_with_constraint/4`: Adds foreign keys with proper indexing
- `setup_mysql_table_charset/3`: Sets proper charset and collation
- `create_mysql_index/3`: Creates MySQL-optimized indexes with advanced options
- `add_mysql_foreign_key/6`: Creates foreign key constraints with MySQL syntax
- `add_mysql_column/4`: Adds columns with MySQL-specific options
- `modify_mysql_column/4`: Modifies columns with MySQL-specific syntax

### 4. Oban Configuration
- Uses `Oban.Engines.Dolphin` (MySQL-compatible engine)
- Proper job queue configuration
- Background job processing support

## Sample Usage

### Creating a new model:
```elixir
# Generate the model
mix phx.gen.context Accounts User users name:string email:string:unique

# The generated migration will need to be updated to use the helper:
defmodule DaProductApp.Repo.Migrations.CreateUsers do
  use Ecto.Migration
  import DaProductApp.MigrationHelpers

  def change do
    create_auto_increment_table(:users) do
      add :name, :string, null: false
      add :email, :string, null: false
    end

    create unique_index(:users, [:email])
    setup_mysql_table_charset(:users)
  end
end
```

## Environment Variables

For production deployment, set these environment variables:
```bash
DATABASE_URL="ecto://user:password@host:3306/database_name"
SECRET_KEY_BASE="your_secret_key_base"
PHX_HOST="your_domain.com"
PORT="4000"
POOL_SIZE="10"
```

## Troubleshooting

### Common Issues:

1. **Connection refused**: Ensure MySQL service is running
2. **Authentication errors**: Verify username/password in config files
3. **Charset issues**: Ensure databases are created with utf8mb4 charset
4. **Migration errors**: Use the provided migration helpers for auto-increment

### Useful Commands:
```bash
# Check MySQL connection
mysql -u root -pdataaegis123 -e "SELECT 1"

# Verify database charset
mysql -u root -pdataaegis123 -e "SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME LIKE 'lic_project%';"

# Reset database
mix ecto.reset

# Check migration status
mix ecto.migrations
```

## Next Steps

1. Create your application-specific schemas using the provided helpers
2. Configure any additional MySQL-specific settings as needed
3. Set up proper backup and monitoring for production
4. Consider implementing database connection pooling optimization
5. Add proper error handling and logging for database operations

Your Phoenix application is now fully configured for MySQL with auto-increment IDs!