-- Add blockchain tables to main schema if not already present
-- This migration ensures blockchain tables exist in the main schema.sql

-- Blockchain transactions table
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
CREATE TABLE IF NOT EXISTS resource_blockchain (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  resource_id UUID NOT NULL,
  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)
);

-- Add foreign key constraint if resource_inventory exists
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'resource_inventory') THEN
    ALTER TABLE resource_blockchain 
    ADD CONSTRAINT fk_resource_blockchain_inventory 
    FOREIGN KEY (resource_id) REFERENCES resource_inventory(id) ON DELETE CASCADE;
  ELSIF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'resources') THEN
    ALTER TABLE resource_blockchain 
    ADD CONSTRAINT fk_resource_blockchain_resources 
    FOREIGN KEY (resource_id) REFERENCES resources(id) ON DELETE CASCADE;
  END IF;
END $$;

-- Indexes
CREATE INDEX IF NOT EXISTS idx_blockchain_transactions_hash ON blockchain_transactions(transaction_hash);
CREATE INDEX IF NOT EXISTS idx_blockchain_transactions_contract ON blockchain_transactions(contract_address);
CREATE INDEX IF NOT EXISTS idx_blockchain_transactions_status ON blockchain_transactions(status);
CREATE INDEX IF NOT EXISTS idx_resource_blockchain_resource ON resource_blockchain(resource_id);
CREATE INDEX IF NOT EXISTS idx_resource_blockchain_transaction ON resource_blockchain(transaction_id);

