import { Migration } from '../migrate.js'

export const up: Migration['up'] = async (db) => {
  // Blockchain transactions table
  await db.query(`
    CREATE TABLE IF NOT EXISTS blockchain_transactions (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      transaction_hash VARCHAR(255) UNIQUE NOT NULL,
      block_number BIGINT,
      contract_address VARCHAR(255),
      contract_name VARCHAR(100),
      function_name VARCHAR(100),
      from_address VARCHAR(255),
      to_address VARCHAR(255),
      status VARCHAR(50) NOT NULL CHECK (status IN ('PENDING', 'CONFIRMED', 'FAILED')),
      gas_used BIGINT,
      data JSONB,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      confirmed_at TIMESTAMP WITH TIME ZONE
    )
  `)

  // Resource blockchain tracking
  await db.query(`
    CREATE TABLE IF NOT EXISTS resource_blockchain (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      resource_id UUID NOT NULL REFERENCES resource_inventory(id) ON DELETE CASCADE,
      transaction_id UUID REFERENCES blockchain_transactions(id) ON DELETE SET NULL,
      blockchain_record JSONB,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      UNIQUE(resource_id, transaction_id)
    )
  `)

  // Indexes
  await db.query(`CREATE INDEX IF NOT EXISTS idx_blockchain_transactions_hash ON blockchain_transactions(transaction_hash)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_blockchain_transactions_contract ON blockchain_transactions(contract_address)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_blockchain_transactions_status ON blockchain_transactions(status)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_resource_blockchain_resource ON resource_blockchain(resource_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_resource_blockchain_transaction ON resource_blockchain(transaction_id)`)
}

export const down: Migration['down'] = async (db) => {
  await db.query(`DROP INDEX IF EXISTS idx_resource_blockchain_transaction`)
  await db.query(`DROP INDEX IF EXISTS idx_resource_blockchain_resource`)
  await db.query(`DROP INDEX IF EXISTS idx_blockchain_transactions_status`)
  await db.query(`DROP INDEX IF EXISTS idx_blockchain_transactions_contract`)
  await db.query(`DROP INDEX IF EXISTS idx_blockchain_transactions_hash`)
  await db.query(`DROP TABLE IF EXISTS resource_blockchain`)
  await db.query(`DROP TABLE IF EXISTS blockchain_transactions`)
}

