defmodule DaProductApp.Repo.Migrations.CreatePosTransactionTables do
  @moduledoc """
  Create POS transaction tables that match the Java entity structure.
  
  This migration creates:
  - pos_transaction (completed transactions)
  - pos_temp_transaction (processing/pending transactions) 
  - acquirer_terminal (terminal configurations)
  - acquirer_terminal_stan (STAN sequence management)
  """
  
  use Ecto.Migration
  
  def up do
    # Create pos_transaction table (completed transactions)
    create table(:pos_transaction, primary_key: false) do
      add :id, :bigint, primary_key: true, null: false
      # Terminal and Merchant Information (Source side)
      add :s_tid, :string, size: 8, null: false, comment: "Source Terminal ID"
      add :s_mid, :string, size: 15, comment: "Source Merchant ID"
      add :s_tid_stan, :string, size: 6, comment: "Source STAN"
      add :s_tid_invoiceno, :string, size: 6, comment: "Source Invoice Number"
      add :s_tid_batchno, :string, size: 6, comment: "Source Batch Number"
      
      # Backend/Acquirer Information (Backend side)
      add :b_tid, :string, size: 8, comment: "Backend Terminal ID"
      add :b_mid, :string, size: 15, comment: "Backend Merchant ID"
      add :acquirer_id, :integer, null: false, comment: "Foreign key to acquirer"
      add :b_tid_stan, :string, size: 6, comment: "Backend STAN"
      add :b_tid_invoiceno, :string, size: 6, comment: "Backend Invoice Number" 
      add :b_tid_batchno, :string, size: 6, comment: "Backend Batch Number"
      add :b_tid_date, :string, size: 8, comment: "Backend Date (YYYYMMDD)"
      add :b_tid_time, :string, size: 6, comment: "Backend Time (HHMMSS)"
      
      # Transaction Details
      add :entry_mode, :string, size: 3, comment: "Entry mode"
      add :condition_code, :string, size: 2, comment: "POS condition code"
      add :currency_code, :string, size: 3, comment: "Currency code"
      add :mti, :string, size: 4, null: false, comment: "Message Type Indicator"
      add :proc_code, :string, size: 6, null: false, comment: "Processing code"
      
      # Amount Information (matches Java BigDecimal precision)
      add :total_amount, :decimal, precision: 12, scale: 2, comment: "Total amount"
      add :auth_amount, :decimal, precision: 12, scale: 2, comment: "Authorized amount"
      add :cash_amount, :decimal, precision: 12, scale: 2, comment: "Cash back amount"
      add :tip_amount, :decimal, precision: 12, scale: 2, comment: "Tip amount"
      
      # Transaction Response Information
      add :approval_code, :string, size: 6, comment: "Approval code"
      add :reference_no, :string, size: 12, comment: "Reference number"
      add :response_code, :string, size: 2, comment: "Response code"
      add :response_message, :string, size: 100, comment: "Response message"
      
      # Card and Security Information
      add :mcc_code, :string, size: 4, comment: "Merchant Category Code"
      add :encrypted_track2, :string, size: 256, comment: "Encrypted Track 2"
      add :encrypted_expiry, :string, size: 4, comment: "Encrypted expiry"
      add :encrypted_pan, :string, size: 256, comment: "Encrypted PAN"
      add :masked_card_no, :string, size: 19, comment: "Masked card number"
      add :pan_seq, :string, size: 3, comment: "PAN sequence number"
      add :emv_data, :text, comment: "EMV chip data"
      
      # Acquirer Network Information
      add :acquirer_reference_no, :string, size: 23, comment: "Acquirer reference"
      add :scheme_reference_no, :string, size: 23, comment: "Scheme reference"
      
      # Metadata and Audit
      add :metadata, :text, comment: "JSON metadata"
      
      timestamps()
    end
    
    # Add auto-increment to the primary key
    execute("ALTER TABLE `pos_transaction` MODIFY COLUMN id BIGINT AUTO_INCREMENT")
    execute("ALTER TABLE `pos_transaction` ENGINE = InnoDB")
    execute("ALTER TABLE `pos_transaction` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
    
    # Create indexes for pos_transaction
    create index(:pos_transaction, [:s_tid, :s_tid_stan], name: "idx_pos_trans_source")
    create index(:pos_transaction, [:b_tid, :b_tid_stan], name: "idx_pos_trans_backend") 
    create index(:pos_transaction, [:acquirer_id])
    create index(:pos_transaction, [:mti, :proc_code])
    create index(:pos_transaction, [:inserted_at])
    create index(:pos_transaction, [:reference_no])
    create index(:pos_transaction, [:approval_code])
    
    # Create pos_temp_transaction table (processing transactions)
    create table(:pos_temp_transaction, primary_key: false) do
      add :id, :bigint, primary_key: true, null: false
      # Same fields as pos_transaction for processing state
      add :s_tid, :string, size: 8, null: false, comment: "Source Terminal ID"
      add :s_mid, :string, size: 15, comment: "Source Merchant ID"
      add :s_tid_stan, :string, size: 6, comment: "Source STAN"
      add :s_tid_invoiceno, :string, size: 6, comment: "Source Invoice Number"
      add :s_tid_batchno, :string, size: 6, comment: "Source Batch Number"
      
      add :b_tid, :string, size: 8, comment: "Backend Terminal ID"
      add :b_mid, :string, size: 15, comment: "Backend Merchant ID"
      add :acquirer_id, :integer, null: false, comment: "Foreign key to acquirer"
      add :b_tid_stan, :string, size: 6, comment: "Backend STAN"
      add :b_tid_invoiceno, :string, size: 6, comment: "Backend Invoice Number"
      add :b_tid_batchno, :string, size: 6, comment: "Backend Batch Number"
      add :b_tid_date, :string, size: 8, comment: "Backend Date (YYYYMMDD)"
      add :b_tid_time, :string, size: 6, comment: "Backend Time (HHMMSS)"
      
      add :entry_mode, :string, size: 3, comment: "Entry mode"
      add :condition_code, :string, size: 2, comment: "POS condition code"
      add :currency_code, :string, size: 3, comment: "Currency code"
      add :mti, :string, size: 4, null: false, comment: "Message Type Indicator"
      add :proc_code, :string, size: 6, null: false, comment: "Processing code"
      
      add :total_amount, :decimal, precision: 12, scale: 2, comment: "Total amount"
      add :auth_amount, :decimal, precision: 12, scale: 2, comment: "Authorized amount"
      add :cash_amount, :decimal, precision: 12, scale: 2, comment: "Cash back amount"
      add :tip_amount, :decimal, precision: 12, scale: 2, comment: "Tip amount"
      
      add :mcc_code, :string, size: 4, comment: "Merchant Category Code"
      add :encrypted_track2, :string, size: 256, comment: "Encrypted Track 2"
      add :encrypted_expiry, :string, size: 4, comment: "Encrypted expiry"
      add :encrypted_pan, :string, size: 256, comment: "Encrypted PAN"
      add :masked_card_no, :string, size: 19, comment: "Masked card number"
      add :pan_seq, :string, size: 3, comment: "PAN sequence number"
      add :emv_data, :text, comment: "EMV chip data"
      
      # Transaction status and processing fields
      add :status, :string, size: 20, null: false, comment: "Processing status"
      add :retry_count, :integer, default: 0, comment: "Retry attempts"
      add :error_message, :string, size: 255, comment: "Error description"
      add :timeout_at, :utc_datetime, comment: "Transaction timeout"
      add :original_message, :text, comment: "Original ISO message"
      add :acquirer_reference_no, :string, size: 23, comment: "Acquirer reference"
      
      add :metadata, :text, comment: "JSON metadata"
      
      timestamps()
    end
    
    # Add auto-increment to the primary key
    execute("ALTER TABLE `pos_temp_transaction` MODIFY COLUMN id BIGINT AUTO_INCREMENT")
    execute("ALTER TABLE `pos_temp_transaction` ENGINE = InnoDB")
    execute("ALTER TABLE `pos_temp_transaction` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
    
    # Create indexes for pos_temp_transaction
    create index(:pos_temp_transaction, [:s_tid, :s_tid_stan], name: "idx_pos_temp_source")
    create index(:pos_temp_transaction, [:status])
    create index(:pos_temp_transaction, [:acquirer_id])
    create index(:pos_temp_transaction, [:timeout_at])
    create index(:pos_temp_transaction, [:inserted_at])
    
    # Create acquirer_terminal table
    create table(:acquirer_terminal, primary_key: false) do
      add :id, :bigint, primary_key: true, null: false
      # Terminal Identification
      add :tid, :string, size: 8, null: false, comment: "Terminal ID"
      add :mid, :string, size: 15, comment: "Merchant ID"
      add :acquirer_id, :integer, null: false, comment: "Foreign key to acquirer"
      
      # Terminal Configuration
      add :terminal_name, :string, size: 50, comment: "Terminal name"
      add :terminal_type, :string, size: 10, comment: "Terminal type"
      add :terminal_location, :string, size: 100, comment: "Terminal location"
      add :currency_code, :string, size: 3, comment: "Default currency"
      add :country_code, :string, size: 3, comment: "Country code"
      add :mcc_code, :string, size: 4, comment: "Merchant Category Code"
      
      # Network Configuration
      add :nii, :string, size: 3, comment: "Network Identification Number"
      add :fiid, :string, size: 11, comment: "Financial Institution ID"
      add :application_type, :string, size: 2, comment: "Application type"
      
      # Batch Management
      add :current_batch_no, :string, size: 6, comment: "Current batch number"
      add :batch_date, :string, size: 8, comment: "Batch date"
      add :batch_time, :string, size: 6, comment: "Batch time"
      add :max_batch_size, :integer, comment: "Max transactions per batch"
      add :auto_settlement, :boolean, default: true, comment: "Auto settlement"
      add :settlement_time, :string, size: 4, comment: "Settlement time"
      
      # Security Configuration
      add :key_set_id, :string, size: 10, comment: "Key set ID"
      add :pin_key, :string, size: 32, comment: "PIN key (encrypted)"
      add :mac_key, :string, size: 32, comment: "MAC key (encrypted)"
      add :data_key, :string, size: 32, comment: "Data key (encrypted)"
      
      # Transaction Limits
      add :max_transaction_amount, :decimal, precision: 12, scale: 2
      add :daily_limit_amount, :decimal, precision: 12, scale: 2
      add :daily_transaction_count, :integer, default: 0
      add :max_daily_transactions, :integer
      
      # Terminal Status and Control
      add :status, :string, size: 15, null: false, comment: "Terminal status"
      add :last_settlement_date, :string, size: 8, comment: "Last settlement date"
      add :last_settlement_time, :string, size: 6, comment: "Last settlement time"
      add :last_logon_date, :string, size: 8, comment: "Last logon date"
      add :last_logon_time, :string, size: 6, comment: "Last logon time"
      
      # Capability Configuration
      add :supports_emv, :boolean, default: true
      add :supports_contactless, :boolean, default: true
      add :supports_pin, :boolean, default: true
      add :supports_signature, :boolean, default: true
      add :supports_cash_back, :boolean, default: false
      add :supports_tip, :boolean, default: false
      add :supports_partial_auth, :boolean, default: false
      
      # Network and Metadata
      add :network_config, :text, comment: "Network config JSON"
      add :metadata, :text, comment: "Additional metadata JSON"
      add :created_by, :string, size: 50
      add :updated_by, :string, size: 50
      
      timestamps()
    end
    
    # Add auto-increment to the primary key
    execute("ALTER TABLE `acquirer_terminal` MODIFY COLUMN id BIGINT AUTO_INCREMENT")
    execute("ALTER TABLE `acquirer_terminal` ENGINE = InnoDB")
    execute("ALTER TABLE `acquirer_terminal` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
    
    # Create unique constraint and indexes for acquirer_terminal
    create unique_index(:acquirer_terminal, [:tid, :acquirer_id], name: "unique_tid_per_acquirer")
    create index(:acquirer_terminal, [:acquirer_id])
    create index(:acquirer_terminal, [:status])
    create index(:acquirer_terminal, [:mid])
    
    # Create acquirer_terminal_stan table
    create table(:acquirer_terminal_stan, primary_key: false) do
      add :id, :bigint, primary_key: true, null: false
      # Terminal Identification
      add :tid, :string, size: 8, null: false, comment: "Terminal ID"
      add :acquirer_id, :integer, null: false, comment: "Acquirer ID"
      
      # STAN Management
      add :current_stan, :string, size: 6, null: false, comment: "Current STAN"
      add :stan_date, :string, size: 8, null: false, comment: "STAN date"
      add :last_used_stan, :string, size: 6, comment: "Last used STAN"
      add :last_reset_date, :string, size: 8, comment: "Last reset date"
      add :last_reset_time, :string, size: 6, comment: "Last reset time"
      
      # STAN Configuration
      add :max_stan_value, :integer, default: 999999, comment: "Max STAN value"
      add :auto_reset_daily, :boolean, default: true, comment: "Auto reset daily"
      add :reset_time, :string, size: 4, default: "0000", comment: "Reset time"
      
      # STAN Statistics
      add :daily_transaction_count, :integer, default: 0
      add :total_transaction_count, :integer, default: 0
      add :last_transaction_date, :string, size: 8
      add :last_transaction_time, :string, size: 6
      
      # Rollover and Recovery
      add :rollover_count, :integer, default: 0
      add :recovery_mode, :boolean, default: false
      add :duplicate_stan_count, :integer, default: 0
      
      # Status and Control
      add :status, :string, size: 15, null: false, comment: "STAN status"
      add :lock_reason, :string, size: 100
      add :locked_at, :utc_datetime
      add :locked_by, :string, size: 50
      
      add :metadata, :text, comment: "Additional metadata JSON"
      
      timestamps()
    end
    
    # Add auto-increment to the primary key
    execute("ALTER TABLE `acquirer_terminal_stan` MODIFY COLUMN id BIGINT AUTO_INCREMENT")
    execute("ALTER TABLE `acquirer_terminal_stan` ENGINE = InnoDB")
    execute("ALTER TABLE `acquirer_terminal_stan` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
    
    # Create unique constraint and indexes for acquirer_terminal_stan
    create unique_index(:acquirer_terminal_stan, [:tid, :acquirer_id], name: "unique_stan_per_terminal")
    create index(:acquirer_terminal_stan, [:stan_date])
    create index(:acquirer_terminal_stan, [:status])
    

  end
  
  def down do
    drop table(:acquirer_terminal_stan)
    drop table(:acquirer_terminal)
    drop table(:pos_temp_transaction)
    drop table(:pos_transaction)
  end
end