

-- Create blockchain watermarks table for local verification records
CREATE TABLE IF NOT EXISTS blockchain_watermarks (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    watermark_hash VARCHAR(64) NOT NULL COMMENT 'SHA256 hash of watermark data',
    transaction_hash VARCHAR(128) NOT NULL UNIQUE COMMENT 'Blockchain transaction hash',
    watermark_data TEXT NOT NULL COMMENT 'JSON encoded watermark metadata',
    network VARCHAR(50) NOT NULL DEFAULT 'ethereum' COMMENT 'Blockchain network used',
    block_number BIGINT NULL COMMENT 'Block number where transaction was mined',
    gas_used INT NULL COMMENT 'Gas used for the transaction',
    transaction_fee DECIMAL(18,8) NULL COMMENT 'Transaction fee paid',
    confirmation_status ENUM('pending', 'confirmed', 'failed') DEFAULT 'pending' COMMENT 'Transaction confirmation status',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_watermark_hash (watermark_hash),
    INDEX idx_transaction_hash (transaction_hash),
    INDEX idx_network_status (network, confirmation_status),
    INDEX idx_created_at (created_at)
) COMMENT='Local records of blockchain watermark registrations for verification';

-- Create proof of ownership table
CREATE TABLE IF NOT EXISTS proof_of_ownership (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    proof_id VARCHAR(32) NOT NULL UNIQUE COMMENT 'Unique proof identifier',
    media_id BIGINT NOT NULL COMMENT 'Associated media file ID',
    owner_user_id BIGINT NOT NULL COMMENT 'Owner user ID',
    owner_username VARCHAR(255) NOT NULL COMMENT 'Owner username at time of proof',
    blockchain_transaction VARCHAR(128) NOT NULL COMMENT 'Blockchain transaction hash',
    proof_hash VARCHAR(64) NOT NULL COMMENT 'SHA256 hash of proof data',
    proof_data TEXT NOT NULL COMMENT 'JSON encoded proof metadata',
    network VARCHAR(50) NOT NULL DEFAULT 'ethereum' COMMENT 'Blockchain network used',
    ownership_timestamp INT NOT NULL COMMENT 'Unix timestamp of ownership claim',
    digital_signature VARCHAR(64) NOT NULL COMMENT 'Digital signature of proof data',
    verification_status ENUM('pending', 'verified', 'disputed', 'revoked') DEFAULT 'pending' COMMENT 'Proof verification status',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_proof_id (proof_id),
    INDEX idx_media_owner (media_id, owner_user_id),
    INDEX idx_blockchain_transaction (blockchain_transaction),
    INDEX idx_proof_hash (proof_hash),
    INDEX idx_verification_status (verification_status),
    INDEX idx_ownership_timestamp (ownership_timestamp)
) COMMENT='Proof of ownership records with blockchain verification';

-- Create immutable records table for watermark applications
CREATE TABLE IF NOT EXISTS immutable_records (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    record_id VARCHAR(32) NOT NULL UNIQUE COMMENT 'Unique record identifier',
    record_type VARCHAR(50) NOT NULL COMMENT 'Type of record (watermark_application, ownership_transfer, etc.)',
    blockchain_transaction VARCHAR(128) NOT NULL COMMENT 'Blockchain transaction hash',
    record_hash VARCHAR(64) NOT NULL COMMENT 'SHA256 hash of record data',
    record_data TEXT NOT NULL COMMENT 'JSON encoded record metadata',
    network VARCHAR(50) NOT NULL DEFAULT 'ethereum' COMMENT 'Blockchain network used',
    applied_timestamp INT NOT NULL COMMENT 'Unix timestamp when record was created',
    integrity_hash VARCHAR(64) NOT NULL COMMENT 'Integrity verification hash',
    tamper_detection_score INT DEFAULT 100 COMMENT 'Tamper detection score (0-100)',
    verification_count INT DEFAULT 0 COMMENT 'Number of times record has been verified',
    last_verification_at TIMESTAMP NULL COMMENT 'Last verification timestamp',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_record_id (record_id),
    INDEX idx_record_type (record_type),
    INDEX idx_blockchain_transaction (blockchain_transaction),
    INDEX idx_record_hash (record_hash),
    INDEX idx_applied_timestamp (applied_timestamp),
    INDEX idx_tamper_score (tamper_detection_score),
    INDEX idx_verification_count (verification_count)
) COMMENT='Immutable records of watermark applications and ownership changes';

-- Create blockchain network status tracking table
CREATE TABLE IF NOT EXISTS blockchain_network_status (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    network VARCHAR(50) NOT NULL COMMENT 'Blockchain network name',
    rpc_url VARCHAR(500) NOT NULL COMMENT 'RPC endpoint URL',
    chain_id INT NOT NULL COMMENT 'Network chain ID',
    latest_block BIGINT NULL COMMENT 'Latest block number',
    block_time_avg DECIMAL(8,2) NULL COMMENT 'Average block time in seconds',
    gas_price_gwei DECIMAL(12,2) NULL COMMENT 'Current gas price in Gwei',
    network_congestion ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium' COMMENT 'Network congestion level',
    connection_status ENUM('connected', 'disconnected', 'error') DEFAULT 'disconnected' COMMENT 'Connection status',
    last_connection_test TIMESTAMP NULL COMMENT 'Last connection test timestamp',
    error_message TEXT NULL COMMENT 'Last error message if connection failed',
    uptime_percentage DECIMAL(5,2) DEFAULT 0.00 COMMENT 'Network uptime percentage',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_network (network),
    INDEX idx_connection_status (connection_status),
    INDEX idx_network_congestion (network_congestion),
    INDEX idx_last_connection_test (last_connection_test)
) COMMENT='Blockchain network status and monitoring data';

-- Insert default network status records
INSERT INTO blockchain_network_status (network, rpc_url, chain_id, network_congestion, connection_status) VALUES
('ethereum', 'https://mainnet.infura.io/v3/', 1, 'medium', 'disconnected'),
('polygon', 'https://polygon-rpc.com/', 137, 'low', 'disconnected'),
('bsc', 'https://bsc-dataseed.binance.org/', 56, 'low', 'disconnected'),
('avalanche', 'https://api.avax.network/ext/bc/C/rpc', 43114, 'low', 'disconnected'),
('hyperledger', 'custom', 0, 'low', 'disconnected')
ON DUPLICATE KEY UPDATE 
    rpc_url = VALUES(rpc_url),
    chain_id = VALUES(chain_id),
    updated_at = NOW();

-- Create blockchain transaction costs tracking table
CREATE TABLE IF NOT EXISTS blockchain_transaction_costs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    network VARCHAR(50) NOT NULL COMMENT 'Blockchain network name',
    transaction_type ENUM('watermark_registration', 'proof_generation', 'verification', 'ownership_transfer') NOT NULL COMMENT 'Type of transaction',
    gas_limit INT NOT NULL COMMENT 'Gas limit used',
    gas_price_gwei DECIMAL(12,2) NOT NULL COMMENT 'Gas price in Gwei',
    gas_used INT NOT NULL COMMENT 'Actual gas used',
    transaction_fee_eth DECIMAL(18,8) NOT NULL COMMENT 'Transaction fee in ETH equivalent',
    transaction_fee_usd DECIMAL(10,2) NULL COMMENT 'Transaction fee in USD (if available)',
    block_number BIGINT NULL COMMENT 'Block number where transaction was mined',
    confirmation_time_seconds INT NULL COMMENT 'Time to confirmation in seconds',
    success BOOLEAN DEFAULT true COMMENT 'Whether transaction was successful',
    error_message TEXT NULL COMMENT 'Error message if transaction failed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_network_type (network, transaction_type),
    INDEX idx_created_at (created_at),
    INDEX idx_success (success),
    INDEX idx_block_number (block_number)
) COMMENT='Historical blockchain transaction costs for analytics';

-- Create blockchain API credentials audit table
CREATE TABLE IF NOT EXISTS blockchain_api_audit (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    admin_user_id BIGINT NOT NULL COMMENT 'Admin user who made the change',
    action ENUM('api_key_updated', 'network_changed', 'wallet_updated', 'settings_modified') NOT NULL COMMENT 'Type of action performed',
    old_values TEXT NULL COMMENT 'JSON of old configuration values',
    new_values TEXT NULL COMMENT 'JSON of new configuration values',
    network VARCHAR(50) NOT NULL COMMENT 'Affected blockchain network',
    ip_address VARCHAR(45) NOT NULL COMMENT 'IP address of admin user',
    user_agent TEXT NULL COMMENT 'User agent string',
    success BOOLEAN DEFAULT true COMMENT 'Whether the action was successful',
    error_message TEXT NULL COMMENT 'Error message if action failed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_admin_user (admin_user_id),
    INDEX idx_action (action),
    INDEX idx_network (network),
    INDEX idx_created_at (created_at),
    INDEX idx_success (success)
) COMMENT='Audit trail for blockchain configuration changes';
    END IF;
    
    -- Validate gas limit
    IF NEW.blockchain_gas_limit < 21000 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Gas limit must be at least 21000';
    END IF;
    
    -- Validate gas price
    IF NEW.blockchain_gas_price NOT IN ('auto', 'fast', 'standard', 'slow') AND NEW.blockchain_gas_price NOT REGEXP '^[0-9]+(\.[0-9]+)?$' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Gas price must be auto, fast, standard, slow, or a numeric value';
    END IF;
    
    -- Validate confirmation blocks
    IF NEW.blockchain_confirmation_blocks < 1 OR NEW.blockchain_confirmation_blocks > 100 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Confirmation blocks must be between 1 and 100';
    END IF;
    
    -- Validate wallet address format for EVM networks
    IF NEW.blockchain_network IN ('ethereum', 'polygon', 'bsc', 'avalanche') AND 
       NEW.blockchain_wallet_address IS NOT NULL AND 
       NEW.blockchain_wallet_address != '' AND
       NEW.blockchain_wallet_address NOT REGEXP '^0x[a-fA-F0-9]{40}$' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid wallet address format for EVM network. Must be 0x followed by 40 hex characters';
    END IF;
    
    -- Validate contract address format for EVM networks
    IF NEW.blockchain_network IN ('ethereum', 'polygon', 'bsc', 'avalanche') AND 
       NEW.blockchain_contract_address IS NOT NULL AND 
       NEW.blockchain_contract_address != '' AND
       NEW.blockchain_contract_address NOT REGEXP '^0x[a-fA-F0-9]{40}$' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid contract address format for EVM network. Must be 0x followed by 40 hex characters';
    END IF;
END//

DELIMITER ;

-- Create views for blockchain analytics
CREATE OR REPLACE VIEW blockchain_watermark_analytics AS
SELECT 
    bw.network,
    COUNT(*) as total_registrations,
    COUNT(CASE WHEN bw.confirmation_status = 'confirmed' THEN 1 END) as confirmed_registrations,
    COUNT(CASE WHEN bw.confirmation_status = 'pending' THEN 1 END) as pending_registrations,
    COUNT(CASE WHEN bw.confirmation_status = 'failed' THEN 1 END) as failed_registrations,
    AVG(bw.gas_used) as avg_gas_used,
    AVG(bw.transaction_fee) as avg_transaction_fee,
    MIN(bw.created_at) as first_registration,
    MAX(bw.created_at) as latest_registration,
    COUNT(DISTINCT DATE(bw.created_at)) as active_days
FROM blockchain_watermarks bw
GROUP BY bw.network;

CREATE OR REPLACE VIEW proof_of_ownership_analytics AS
SELECT 
    po.network,
    COUNT(*) as total_proofs,
    COUNT(CASE WHEN po.verification_status = 'verified' THEN 1 END) as verified_proofs,
    COUNT(CASE WHEN po.verification_status = 'pending' THEN 1 END) as pending_proofs,
    COUNT(CASE WHEN po.verification_status = 'disputed' THEN 1 END) as disputed_proofs,
    COUNT(CASE WHEN po.verification_status = 'revoked' THEN 1 END) as revoked_proofs,
    COUNT(DISTINCT po.owner_user_id) as unique_owners,
    AVG(UNIX_TIMESTAMP(po.created_at) - po.ownership_timestamp) as avg_registration_delay_seconds
FROM proof_of_ownership po
GROUP BY po.network;

CREATE OR REPLACE VIEW blockchain_network_performance AS
SELECT 
    bns.network,
    bns.connection_status,
    bns.network_congestion,
    bns.latest_block,
    bns.block_time_avg,
    bns.gas_price_gwei,
    bns.uptime_percentage,
    bns.last_connection_test,
    COALESCE(bwa.total_registrations, 0) as total_watermarks,
    COALESCE(poa.total_proofs, 0) as total_proofs
FROM blockchain_network_status bns
LEFT JOIN blockchain_watermark_analytics bwa ON bns.network = bwa.network
LEFT JOIN proof_of_ownership_analytics poa ON bns.network = poa.network;