-- =====================================================
-- BLOCKCHAIN WATERMARK SYSTEM TABLES
-- Multi-blockchain network support for tamper-proof watermarking
-- =====================================================

-- Blockchain watermarks table for storing watermark registrations
CREATE TABLE IF NOT EXISTS `blockchain_watermarks` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `watermark_hash` varchar(64) NOT NULL COMMENT 'SHA-256 hash of watermark data',
  `transaction_hash` varchar(128) NOT NULL UNIQUE COMMENT 'Blockchain transaction hash',
  `network` varchar(50) NOT NULL DEFAULT 'ethereum' COMMENT 'Blockchain network used',
  `user_id` bigint(20) unsigned NOT NULL COMMENT 'User who registered the watermark',
  `media_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Associated media ID',
  `file_hash` varchar(64) NOT NULL COMMENT 'SHA-256 hash of original file',
  `watermark_data` longtext NOT NULL COMMENT 'JSON watermark metadata',
  `registration_cost` decimal(18,8) DEFAULT 0.00000000 COMMENT 'Cost of blockchain registration',
  `gas_used` bigint(20) DEFAULT NULL COMMENT 'Gas used for transaction',
  `block_number` bigint(20) DEFAULT NULL COMMENT 'Block number of transaction',
  `confirmation_blocks` int(11) DEFAULT 6 COMMENT 'Required confirmation blocks',
  `confirmed_at` timestamp NULL DEFAULT NULL COMMENT 'When transaction was confirmed',
  `status` enum('pending','confirmed','failed','expired') DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_transaction_hash` (`transaction_hash`),
  KEY `idx_watermark_hash` (`watermark_hash`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_media_id` (`media_id`),
  KEY `idx_file_hash` (`file_hash`),
  KEY `idx_network` (`network`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Blockchain watermark registrations';

-- Immutable records table for tamper-proof watermark applications
CREATE TABLE IF NOT EXISTS `immutable_records` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `record_id` varchar(32) NOT NULL UNIQUE COMMENT 'Unique record identifier',
  `record_type` varchar(50) NOT NULL DEFAULT 'watermark_application' COMMENT 'Type of immutable record',
  `blockchain_transaction` varchar(128) NOT NULL COMMENT 'Blockchain transaction hash',
  `record_hash` varchar(64) NOT NULL COMMENT 'SHA-256 hash of record data',
  `record_data` longtext NOT NULL COMMENT 'JSON record metadata',
  `network` varchar(50) NOT NULL DEFAULT 'ethereum' COMMENT 'Blockchain network used',
  `user_id` bigint(20) unsigned NOT NULL COMMENT 'User who created the record',
  `media_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Associated media ID',
  `applied_timestamp` bigint(20) NOT NULL COMMENT 'Unix timestamp of application',
  `integrity_hash` varchar(64) NOT NULL COMMENT 'Integrity verification hash',
  `verification_url` varchar(500) DEFAULT NULL COMMENT 'Public verification URL',
  `status` enum('active','archived','disputed') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_record_id` (`record_id`),
  KEY `idx_record_type` (`record_type`),
  KEY `idx_blockchain_transaction` (`blockchain_transaction`),
  KEY `idx_record_hash` (`record_hash`),
  KEY `idx_network` (`network`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_media_id` (`media_id`),
  KEY `idx_applied_timestamp` (`applied_timestamp`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Immutable blockchain records';

-- Blockchain transaction logs for monitoring and debugging
CREATE TABLE IF NOT EXISTS `blockchain_transaction_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `transaction_hash` varchar(128) NOT NULL COMMENT 'Blockchain transaction hash',
  `network` varchar(50) NOT NULL COMMENT 'Blockchain network',
  `operation_type` varchar(50) NOT NULL COMMENT 'Type of operation (register, verify, etc.)',
  `user_id` bigint(20) unsigned NOT NULL COMMENT 'User who initiated transaction',
  `gas_limit` bigint(20) DEFAULT NULL COMMENT 'Gas limit set for transaction',
  `gas_used` bigint(20) DEFAULT NULL COMMENT 'Actual gas used',
  `gas_price` varchar(50) DEFAULT NULL COMMENT 'Gas price used',
  `transaction_fee` decimal(18,8) DEFAULT NULL COMMENT 'Total transaction fee',
  `block_number` bigint(20) DEFAULT NULL COMMENT 'Block number',
  `block_hash` varchar(66) DEFAULT NULL COMMENT 'Block hash',
  `transaction_index` int(11) DEFAULT NULL COMMENT 'Transaction index in block',
  `nonce` bigint(20) DEFAULT NULL COMMENT 'Transaction nonce',
  `from_address` varchar(42) DEFAULT NULL COMMENT 'From wallet address',
  `to_address` varchar(42) DEFAULT NULL COMMENT 'To contract/wallet address',
  `value` decimal(18,8) DEFAULT 0.00000000 COMMENT 'Transaction value',
  `input_data` text DEFAULT NULL COMMENT 'Transaction input data',
  `logs` longtext DEFAULT NULL COMMENT 'Transaction logs JSON',
  `receipt` longtext DEFAULT NULL COMMENT 'Transaction receipt JSON',
  `status` enum('pending','success','failed','dropped') DEFAULT 'pending',
  `error_message` text DEFAULT NULL COMMENT 'Error message if failed',
  `confirmations` int(11) DEFAULT 0 COMMENT 'Number of confirmations',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_transaction_hash` (`transaction_hash`),
  KEY `idx_network` (`network`),
  KEY `idx_operation_type` (`operation_type`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_block_number` (`block_number`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Blockchain transaction monitoring logs';

-- Add blockchain configuration columns to admin_settings table if they don't exist
ALTER TABLE `admin_settings` 
ADD COLUMN IF NOT EXISTS `blockchain_network` varchar(50) DEFAULT 'ethereum' AFTER `allow_vault`,
ADD COLUMN IF NOT EXISTS `blockchain_api_key` varchar(255) DEFAULT NULL AFTER `blockchain_network`,
ADD COLUMN IF NOT EXISTS `blockchain_api_secret` varchar(255) DEFAULT NULL AFTER `blockchain_api_key`,
ADD COLUMN IF NOT EXISTS `blockchain_contract_address` varchar(42) DEFAULT NULL AFTER `blockchain_api_secret`,
ADD COLUMN IF NOT EXISTS `blockchain_wallet_address` varchar(42) DEFAULT NULL AFTER `blockchain_contract_address`,
ADD COLUMN IF NOT EXISTS `blockchain_private_key` varchar(64) DEFAULT NULL AFTER `blockchain_wallet_address`,
ADD COLUMN IF NOT EXISTS `blockchain_gas_limit` bigint(20) DEFAULT 21000 AFTER `blockchain_private_key`,
ADD COLUMN IF NOT EXISTS `blockchain_gas_price` varchar(20) DEFAULT 'auto' AFTER `blockchain_gas_limit`,
ADD COLUMN IF NOT EXISTS `blockchain_confirmation_blocks` int(11) DEFAULT 6 AFTER `blockchain_gas_price`,
ADD COLUMN IF NOT EXISTS `blockchain_proof_enabled` tinyint(1) DEFAULT 1 AFTER `blockchain_confirmation_blocks`,
ADD COLUMN IF NOT EXISTS `blockchain_auto_register` tinyint(1) DEFAULT 0 AFTER `blockchain_proof_enabled`,
ADD COLUMN IF NOT EXISTS `blockchain_cost_per_registration` decimal(10,8) DEFAULT 0.00100000 AFTER `blockchain_auto_register`;

-- Create indexes for blockchain configuration columns
ALTER TABLE `admin_settings` 
ADD INDEX IF NOT EXISTS `idx_blockchain_network` (`blockchain_network`),
ADD INDEX IF NOT EXISTS `idx_blockchain_auto_register` (`blockchain_auto_register`),
ADD INDEX IF NOT EXISTS `idx_blockchain_proof_enabled` (`blockchain_proof_enabled`);

-- Create views for blockchain watermark analytics
CREATE OR REPLACE VIEW `blockchain_watermark_analytics` AS
SELECT 
    bw.network,
    COUNT(*) as total_registrations,
    COUNT(CASE WHEN bw.status = 'confirmed' THEN 1 END) as confirmed_registrations,
    COUNT(CASE WHEN bw.status = 'pending' THEN 1 END) as pending_registrations,
    COUNT(CASE WHEN bw.status = 'failed' THEN 1 END) as failed_registrations,
    AVG(bw.registration_cost) as avg_registration_cost,
    SUM(bw.registration_cost) as total_registration_cost,
    AVG(bw.gas_used) as avg_gas_used,
    SUM(bw.gas_used) as total_gas_used,
    COUNT(DISTINCT bw.user_id) as unique_users,
    MIN(bw.created_at) as first_registration,
    MAX(bw.created_at) as latest_registration
FROM blockchain_watermarks bw
GROUP BY bw.network;

-- Create stored procedures for blockchain operations
DELIMITER $$

CREATE PROCEDURE IF NOT EXISTS GetBlockchainWatermarkStats(
    IN network_filter VARCHAR(50),
    IN date_from DATE,
    IN date_to DATE
)
BEGIN
    SELECT 
        network,
        DATE(created_at) as registration_date,
        COUNT(*) as daily_registrations,
        COUNT(CASE WHEN status = 'confirmed' THEN 1 END) as confirmed_count,
        AVG(registration_cost) as avg_cost,
        SUM(registration_cost) as total_cost
    FROM blockchain_watermarks
    WHERE (network_filter IS NULL OR network = network_filter)
    AND (date_from IS NULL OR DATE(created_at) >= date_from)
    AND (date_to IS NULL OR DATE(created_at) <= date_to)
    GROUP BY network, DATE(created_at)
    ORDER BY registration_date DESC, network;
END$$

CREATE PROCEDURE IF NOT EXISTS CleanupExpiredProofs()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE proof_id_var VARCHAR(32);
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    START TRANSACTION;
    
    OPEN proof_cursor;
    
    cleanup_loop: LOOP
        FETCH proof_cursor INTO proof_id_var;
        IF done THEN
            LEAVE cleanup_loop;
        END IF;
        
    END LOOP;
    
    CLOSE proof_cursor;
    COMMIT;
    
    SELECT ROW_COUNT() as expired_proofs_count;
END$$

DELIMITER ;

-- Create triggers for automatic logging
DELIMITER $$

CREATE TRIGGER IF NOT EXISTS blockchain_watermark_status_log
AFTER UPDATE ON blockchain_watermarks
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO blockchain_transaction_logs (
            transaction_hash, network, operation_type, user_id, 
            gas_used, block_number, status, created_at
        ) VALUES (
            NEW.transaction_hash, NEW.network, 'status_update', NEW.user_id,
            NEW.gas_used, NEW.block_number, NEW.status, NOW()
        );
    END IF;
END$$

DELIMITER ;

-- =====================================================
-- SUMMARY
-- =====================================================
-- ✅ blockchain_watermarks: Core watermark registrations with multi-network support
-- ✅ proof_of_ownership: Tamper-proof ownership records with verification codes
-- ✅ immutable_records: Immutable watermark application records
-- ✅ blockchain_network_configs: Network configuration management
-- ✅ blockchain_transaction_logs: Comprehensive transaction monitoring
-- ✅ admin_settings: Extended with blockchain configuration columns
-- ✅ Analytics views: Real-time blockchain watermark analytics
-- ✅ Stored procedures: Automated statistics and cleanup operations
-- ✅ Triggers: Automatic logging and status tracking
-- =====================================================

SELECT 'BLOCKCHAIN WATERMARK SYSTEM TABLES CREATED SUCCESSFULLY' as status;