defmodule DaProductApp.MigrationHelpers do
  @moduledoc """
  Helper functions for MySQL migrations with auto-increment support.
  
  This module provides MySQL-specific migration helpers that extend Ecto's
  migration capabilities with MySQL's unique features like AUTO_INCREMENT,
  specific index types, charset management, and advanced column options.
  
  ## Key Features
  
  - Auto-increment table creation
  - MySQL-specific index types (FULLTEXT, SPATIAL)
  - Foreign key constraints with proper MySQL syntax
  - Character set and collation management
  - Advanced column manipulation
  - Secure SQL generation with proper escaping
  
  ## Usage
  
      defmodule MyApp.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_mysql_index(:users, [:email], type: "UNIQUE")
          setup_mysql_table_charset(:users)
        end
      end
  """
  
  import Ecto.Migration

  # Valid MySQL index types
  @valid_index_types ["UNIQUE", "FULLTEXT", "SPATIAL"]
  
  # Valid MySQL charset names
  @valid_charsets ["utf8", "utf8mb4", "latin1", "ascii"]
  
  # Valid MySQL storage engines
  @valid_engines ["InnoDB", "MyISAM", "Memory", "Archive"]

  @doc """
  Creates a table with auto-increment primary key for MySQL.
  
  This function creates a table with a BIGINT AUTO_INCREMENT primary key,
  which is the recommended approach for MySQL tables in Phoenix applications.
  
  ## Options
  
  - `:engine` - Storage engine (default: "InnoDB")
  - `:charset` - Character set (default: "utf8mb4")
  - `:collation` - Collation (default: "utf8mb4_unicode_ci")
  - All standard Ecto table options
  
  ## Examples
  
      create_auto_increment_table(:users) do
        add :name, :string, null: false
        add :email, :string, null: false
      end
      
      create_auto_increment_table(:logs, engine: "MyISAM") do
        add :message, :text
        add :level, :string
      end
  """
  def create_auto_increment_table(table_name, opts \\ [], do: block) do
    # Validate table name
    validate_identifier!(table_name, "table name")
    
    # Extract MySQL-specific options
    {mysql_opts, ecto_opts} = extract_mysql_options(opts)
    
    create table(table_name, [primary_key: false] ++ ecto_opts) do
      add :id, :bigint, primary_key: true, null: false, comment: "Auto-increment primary key"
      block.()
      timestamps()
    end
    
    # Add auto-increment to the primary key
    execute("ALTER TABLE `#{table_name}` MODIFY COLUMN id BIGINT AUTO_INCREMENT")
    
    # Apply MySQL-specific table options
    apply_mysql_table_options(table_name, mysql_opts)
  end

  @doc """
  Adds a foreign key with proper indexing for MySQL
  """
  def add_foreign_key_with_constraint(table, column, references_table, opts \\ []) do
    add column, references(references_table, on_delete: :delete_all), opts
    create index(table, [column])
  end

  @doc """
  Sets up proper charset and collation for a table in MySQL.
  
  This function converts an existing table to use the specified character set
  and collation. This is important for proper Unicode support and text handling.
  
  ## Parameters
  
  - `table_name` - The name of the table to modify
  - `charset` - Character set (default: "utf8mb4")
  - `collation` - Collation (default: "utf8mb4_unicode_ci")
  
  ## Examples
  
      # Use defaults (utf8mb4)
      setup_mysql_table_charset(:users)
      
      # Specify custom charset
      setup_mysql_table_charset(:legacy_data, "utf8", "utf8_general_ci")
  """
  def setup_mysql_table_charset(table_name, charset \\ "utf8mb4", collation \\ "utf8mb4_unicode_ci") do
    # Validate inputs
    validate_identifier!(table_name, "table name")
    validate_charset!(charset)
    validate_collation!(collation, charset)
    
    execute("ALTER TABLE `#{table_name}` CONVERT TO CHARACTER SET #{charset} COLLATE #{collation}")
  end

  @doc """
  Creates an index with MySQL-specific options
  
  ## Options
  
  - `:name` - Custom index name (defaults to generated name)
  - `:type` - Index type: "UNIQUE", "FULLTEXT", "SPATIAL", etc.
  - `:length` - Column length specification (map of column -> length)
  - `:using` - Index method: "BTREE", "HASH"
  - `:comment` - Index comment
  
  ## Examples
  
      # Basic index
      create_mysql_index(:users, [:email])
      
      # Unique index
      create_mysql_index(:users, [:email], type: "UNIQUE")
      
      # Fulltext index
      create_mysql_index(:posts, [:title, :content], type: "FULLTEXT")
      
      # Index with length specification
      create_mysql_index(:users, [:name], length: %{name: 50})
      
      # Composite index with custom name
      create_mysql_index(:users, [:status, :created_at], name: "idx_user_status_created")
  """
  def create_mysql_index(table, columns, opts \\ []) when is_list(columns) do
    # Validate inputs
    if Enum.empty?(columns) do
      raise ArgumentError, "At least one column must be specified for index"
    end
    
    # Generate index name if not provided
    default_name = "#{table}_#{Enum.join(columns, "_")}_index"
    index_name = opts[:name] || default_name
    
    # Get options
    index_type = opts[:type]
    length_spec = opts[:length] || %{}
    using_method = opts[:using]
    comment = opts[:comment]
    
    # Validate index type for MySQL
    if index_type && index_type not in @valid_index_types do
      # For standard Ecto index types, use Ecto's create index
      create index(table, columns, name: index_name, unique: index_type == "UNIQUE")
    else
      # Build column specification with length if provided
      columns_sql = 
        columns
        |> Enum.map(fn col ->
          col_str = "`#{col}`"
          case Map.get(length_spec, col) do
            nil -> col_str
            length when is_integer(length) -> "#{col_str}(#{length})"
            _ -> col_str
          end
        end)
        |> Enum.join(", ")
      
      # Build the CREATE INDEX statement
      sql_parts = [
        "CREATE",
        index_type,
        "INDEX",
        "`#{index_name}`",
        "ON",
        "`#{table}`",
        "(#{columns_sql})"
      ]
      
      # Add USING clause if specified
      sql_parts = if using_method do
        sql_parts ++ ["USING", using_method]
      else
        sql_parts
      end
      
      # Add COMMENT clause if specified
      sql_parts = if comment do
        sql_parts ++ ["COMMENT", "'#{String.replace(comment, "'", "\\'")}'" ]
      else
        sql_parts
      end
      
      # Execute the SQL
      sql = sql_parts |> Enum.reject(&is_nil/1) |> Enum.join(" ")
      execute(sql)
    end
  end

  @doc """
  Drops a MySQL index safely
  """
  def drop_mysql_index(table, index_name) do
    execute("DROP INDEX `#{index_name}` ON `#{table}`")
  end

  @doc """
  Creates a foreign key constraint with proper MySQL syntax
  """
  def add_mysql_foreign_key(table, column, referenced_table, referenced_column \\ :id, opts \\ []) do
    constraint_name = opts[:name] || "fk_#{table}_#{column}"
    on_delete = opts[:on_delete] || :restrict
    on_update = opts[:on_update] || :restrict
    
    # Convert Ecto actions to MySQL syntax
    delete_action = case on_delete do
      :restrict -> "RESTRICT"
      :cascade -> "CASCADE"
      :nullify -> "SET NULL"
      :delete_all -> "CASCADE"
      _ -> "RESTRICT"
    end
    
    update_action = case on_update do
      :restrict -> "RESTRICT"
      :cascade -> "CASCADE"
      :nullify -> "SET NULL"
      _ -> "RESTRICT"
    end
    
    execute("""
    ALTER TABLE `#{table}` 
    ADD CONSTRAINT `#{constraint_name}` 
    FOREIGN KEY (`#{column}`) 
    REFERENCES `#{referenced_table}`(`#{referenced_column}`) 
    ON DELETE #{delete_action} 
    ON UPDATE #{update_action}
    """)
    
    # Create index for foreign key if it doesn't exist
    create index(table, [column], name: "idx_#{table}_#{column}")
  end

  @doc """
  Drops a foreign key constraint
  """
  def drop_mysql_foreign_key(table, constraint_name) do
    execute("ALTER TABLE `#{table}` DROP FOREIGN KEY `#{constraint_name}`")
  end

  @doc """
  Adds a column with MySQL-specific options
  """
  def add_mysql_column(table, column_name, type, opts \\ []) do
    default_value = opts[:default]
    null = Keyword.get(opts, :null, true)
    comment = opts[:comment]
    after_column = opts[:after]
    
    # Build column definition
    base_def = "`#{column_name}` #{mysql_type_to_sql(type)}"
    
    # Add NULL/NOT NULL
    null_def = base_def <> if null, do: " NULL", else: " NOT NULL"
    
    # Add DEFAULT
    default_def = if default_value do
      null_def <> " DEFAULT '#{default_value}'"
    else
      null_def
    end
    
    # Add COMMENT
    comment_def = if comment do
      default_def <> " COMMENT '#{String.replace(comment, "'", "\\'")}'"
    else
      default_def
    end
    
    # Add AFTER clause for positioning
    final_def = if after_column do
      comment_def <> " AFTER `#{after_column}`"
    else
      comment_def
    end
    
    execute("ALTER TABLE `#{table}` ADD COLUMN #{final_def}")
  end

  @doc """
  Modifies a column with MySQL-specific syntax
  """
  def modify_mysql_column(table, column_name, type, opts \\ []) do
    null = Keyword.get(opts, :null, true)
    default_value = opts[:default]
    comment = opts[:comment]
    
    # Build column definition
    base_def = "`#{column_name}` #{mysql_type_to_sql(type)}"
    
    # Add NULL/NOT NULL
    null_def = base_def <> if null, do: " NULL", else: " NOT NULL"
    
    # Add DEFAULT
    default_def = if default_value do
      null_def <> " DEFAULT '#{default_value}'"
    else
      null_def
    end
    
    # Add COMMENT
    final_def = if comment do
      default_def <> " COMMENT '#{String.replace(comment, "'", "\\'")}'"
    else
      default_def
    end
    
    execute("ALTER TABLE `#{table}` MODIFY COLUMN #{final_def}")
  end

  # Private helper to convert Ecto types to MySQL SQL types
  defp mysql_type_to_sql(type) do
    case type do
      # String types
      :string -> "VARCHAR(255)"
      :text -> "TEXT"
      {:string, length} when length <= 255 -> "VARCHAR(#{length})"
      {:string, length} when length <= 65535 -> "TEXT"
      {:string, length} when length <= 16777215 -> "MEDIUMTEXT"
      {:string, _length} -> "LONGTEXT"
      
      # Numeric types
      :integer -> "INT"
      :bigint -> "BIGINT"
      :smallint -> "SMALLINT"
      :tinyint -> "TINYINT"
      :boolean -> "BOOLEAN"
      :decimal -> "DECIMAL(10,2)"
      {:decimal, precision, scale} -> "DECIMAL(#{precision},#{scale})"
      :float -> "FLOAT"
      :double -> "DOUBLE"
      
      # Date and time types
      :date -> "DATE"
      :time -> "TIME"
      :datetime -> "DATETIME"
      :timestamp -> "TIMESTAMP"
      :naive_datetime -> "DATETIME"
      :utc_datetime -> "DATETIME"
      
      # Binary types
      :binary -> "BLOB"
      :uuid -> "CHAR(36)"
      
      # JSON (MySQL 5.7+)
      :map -> "JSON"
      
      # Fallback
      _ -> to_string(type)
    end
  end

  # Private validation functions
  defp validate_identifier!(identifier, type) when is_atom(identifier) do
    validate_identifier!(to_string(identifier), type)
  end
  
  defp validate_identifier!(identifier, type) when is_binary(identifier) do
    # Check for valid MySQL identifier format
    if not Regex.match?(~r/^[a-zA-Z_][a-zA-Z0-9_]*$/, identifier) do
      raise ArgumentError, "Invalid #{type}: #{identifier}. Must start with letter or underscore and contain only letters, numbers, and underscores."
    end
    
    # Check length (MySQL limit is 64 characters)
    if String.length(identifier) > 64 do
      raise ArgumentError, "#{type} too long: #{identifier}. MySQL identifiers must be 64 characters or less."
    end
  end
  
  defp validate_charset!(charset) do
    if charset not in @valid_charsets do
      raise ArgumentError, "Invalid charset: #{charset}. Valid charsets: #{Enum.join(@valid_charsets, ", ")}"
    end
  end
  
  defp validate_collation!(collation, charset) do
    # Basic validation - should start with charset name
    if not String.starts_with?(collation, charset) do
      raise ArgumentError, "Collation #{collation} is not compatible with charset #{charset}"
    end
  end
  
  defp extract_mysql_options(opts) do
    mysql_keys = [:engine, :charset, :collation]
    {mysql_opts, ecto_opts} = Keyword.split(opts, mysql_keys)
    
    # Set defaults
    mysql_opts = Keyword.merge([
      engine: "InnoDB",
      charset: "utf8mb4", 
      collation: "utf8mb4_unicode_ci"
    ], mysql_opts)
    
    {mysql_opts, ecto_opts}
  end
  
  defp apply_mysql_table_options(table_name, opts) do
    engine = opts[:engine]
    charset = opts[:charset]
    collation = opts[:collation]
    
    # Validate engine
    if engine not in @valid_engines do
      raise ArgumentError, "Invalid storage engine: #{engine}. Valid engines: #{Enum.join(@valid_engines, ", ")}"
    end
    
    # Apply engine
    execute("ALTER TABLE `#{table_name}` ENGINE = #{engine}")
    
    # Apply charset and collation
    if charset != "utf8mb4" or collation != "utf8mb4_unicode_ci" do
      setup_mysql_table_charset(table_name, charset, collation)
    end
  end

  @doc """
  Creates a table optimized for logging with partitioning support.
  
  This creates a table specifically designed for high-volume logging
  with optional date-based partitioning.
  
  ## Options
  
  - `:partition_by` - Partition by :date, :month, or :year
  - `:partition_column` - Column to partition on (default: :created_at)
  - All standard create_auto_increment_table options
  
  ## Examples
  
      create_log_table(:audit_logs, partition_by: :month) do
        add :user_id, :bigint
        add :action, :string
        add :details, :text
      end
  """
  def create_log_table(table_name, opts \\ [], do: block) do
    partition_by = opts[:partition_by]
    partition_column = opts[:partition_column] || :created_at
    
    # Remove partition options from table creation
    table_opts = Keyword.drop(opts, [:partition_by, :partition_column])
    
    create_auto_increment_table(table_name, table_opts, do: block)
    
    # Add partitioning if specified
    if partition_by do
      add_table_partitioning(table_name, partition_by, partition_column)
    end
  end
  
  defp add_table_partitioning(table_name, partition_by, column) do
    partition_expr = case partition_by do
      :date -> "TO_DAYS(#{column})"
      :month -> "YEAR(#{column}) * 100 + MONTH(#{column})"
      :year -> "YEAR(#{column})"
      _ -> raise ArgumentError, "Invalid partition type: #{partition_by}"
    end
    
    execute("""
    ALTER TABLE `#{table_name}` 
    PARTITION BY RANGE (#{partition_expr}) (
      PARTITION p_default VALUES LESS THAN MAXVALUE
    )
    """)
  end

  @doc """
  Adds a JSON column with proper MySQL JSON type and indexing support.
  
  ## Examples
  
      add_json_column(:users, :preferences)
      add_json_column(:products, :metadata, null: false, default: "{}")
  """
  def add_json_column(table, column_name, opts \\ []) do
    default_value = opts[:default] || "NULL"
    null = Keyword.get(opts, :null, true)
    
    null_clause = if null, do: "NULL", else: "NOT NULL"
    
    execute("""
    ALTER TABLE `#{table}` 
    ADD COLUMN `#{column_name}` JSON #{null_clause} DEFAULT (#{default_value})
    """)
  end
end