-- Sistema Blockchain para Certificados de Experiencias
-- Database schema for blockchain-backed experience certificates

-- Certificados digitales de experiencias
CREATE TABLE experience_certificates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    certificate_hash VARCHAR(64) UNIQUE NOT NULL, -- SHA-256 hash único del certificado
    blockchain_tx_hash VARCHAR(66), -- Hash de transacción en blockchain (con prefijo 0x)
    blockchain_address VARCHAR(42), -- Dirección del contrato inteligente
    token_id BIGINT UNSIGNED, -- ID del NFT si aplica
    
    usuario_id INT NOT NULL,
    producto_id INT NOT NULL,
    reserva_id INT,
    ar_experience_id INT,
    
    tipo_certificado ENUM('completion', 'participation', 'achievement', 'nft_souvenir') NOT NULL,
    nivel_certificado ENUM('bronze', 'silver', 'gold', 'platinum') DEFAULT 'bronze',
    
    titulo VARCHAR(300) NOT NULL,
    descripcion TEXT,
    credenciales JSON, -- Datos específicos del logro/certificado
    
    fecha_emision TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_experiencia DATE NOT NULL,
    duracion_experiencia INT, -- Duración en minutos
    ubicacion_gps JSON, -- Coordenadas donde se completó
    
    metadatos_blockchain JSON, -- Datos almacenados en blockchain
    ipfs_hash VARCHAR(59), -- Hash de IPFS para metadatos extendidos
    imagen_certificado_url VARCHAR(500), -- URL de imagen del certificado
    qr_code_url VARCHAR(500), -- QR para verificación
    
    verificado_blockchain BOOLEAN DEFAULT FALSE,
    estado ENUM('pending', 'minting', 'minted', 'failed') DEFAULT 'pending',
    intentos_blockchain INT DEFAULT 0,
    ultimo_intento TIMESTAMP NULL,
    error_blockchain TEXT,
    
    compartido BOOLEAN DEFAULT FALSE,
    compartido_redes JSON, -- Redes sociales donde se compartió
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (producto_id) REFERENCES productos(id) ON DELETE CASCADE,
    FOREIGN KEY (reserva_id) REFERENCES reservas(id) ON DELETE SET NULL,
    FOREIGN KEY (ar_experience_id) REFERENCES ar_experiences(id) ON DELETE SET NULL,
    
    INDEX idx_usuario (usuario_id),
    INDEX idx_producto (producto_id),
    INDEX idx_hash (certificate_hash),
    INDEX idx_blockchain_tx (blockchain_tx_hash),
    INDEX idx_tipo (tipo_certificado),
    INDEX idx_estado (estado),
    INDEX idx_fecha_emision (fecha_emision)
);

-- Plantillas para certificados
CREATE TABLE certificate_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    descripcion TEXT,
    
    tipo_certificado ENUM('completion', 'participation', 'achievement', 'nft_souvenir') NOT NULL,
    categoria_producto VARCHAR(100), -- Categoría de productos que usan esta plantilla
    nivel_requerido ENUM('bronze', 'silver', 'gold', 'platinum') DEFAULT 'bronze',
    
    plantilla_html TEXT, -- Template HTML del certificado
    plantilla_css TEXT, -- Estilos CSS
    campos_dinamicos JSON, -- Campos que se reemplazan dinámicamente
    
    imagen_fondo_url VARCHAR(500),
    logo_url VARCHAR(500),
    fuentes_tipografia JSON,
    colores_tema JSON,
    
    dimensiones JSON, -- Ancho, alto, DPI
    formato_salida ENUM('png', 'pdf', 'svg') DEFAULT 'png',
    
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_tipo (tipo_certificado),
    INDEX idx_categoria (categoria_producto),
    INDEX idx_activo (activo)
);

-- Reglas para obtener certificados
CREATE TABLE certificate_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    descripcion TEXT,
    
    producto_id INT,
    categoria_id INT,
    ar_experience_id INT,
    
    tipo_certificado ENUM('completion', 'participation', 'achievement', 'nft_souvenir') NOT NULL,
    nivel_certificado ENUM('bronze', 'silver', 'gold', 'platinum') DEFAULT 'bronze',
    
    condiciones JSON, -- Condiciones que deben cumplirse
    puntos_requeridos INT DEFAULT 0,
    experiencias_minimas INT DEFAULT 1,
    calificacion_minima DECIMAL(2,1) DEFAULT 0.0,
    tiempo_minimo INT, -- Tiempo mínimo en la experiencia (minutos)
    
    automatico BOOLEAN DEFAULT TRUE, -- Si se otorga automáticamente
    requiere_validacion BOOLEAN DEFAULT FALSE,
    
    recompensas JSON, -- Recompensas adicionales (descuentos, puntos, etc.)
    es_nft BOOLEAN DEFAULT FALSE, -- Si se convierte en NFT
    precio_mint DECIMAL(10,4) DEFAULT 0, -- Costo para convertir en NFT
    
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (producto_id) REFERENCES productos(id) ON DELETE CASCADE,
    FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON DELETE CASCADE,
    FOREIGN KEY (ar_experience_id) REFERENCES ar_experiences(id) ON DELETE CASCADE,
    
    INDEX idx_producto (producto_id),
    INDEX idx_categoria (categoria_id),
    INDEX idx_tipo (tipo_certificado),
    INDEX idx_automatico (automatico),
    INDEX idx_activo (activo)
);

-- Blockchain networks y configuraciones
CREATE TABLE blockchain_networks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    chain_id INT NOT NULL UNIQUE,
    network_name VARCHAR(100) NOT NULL,
    
    rpc_url VARCHAR(500) NOT NULL,
    explorer_url VARCHAR(500),
    currency_symbol VARCHAR(10) NOT NULL,
    currency_decimals TINYINT DEFAULT 18,
    
    contract_address VARCHAR(42), -- Dirección del contrato de certificados
    contract_abi JSON, -- ABI del contrato
    
    gas_price_gwei DECIMAL(10,4) DEFAULT 20.0,
    gas_limit INT DEFAULT 200000,
    
    activo BOOLEAN DEFAULT TRUE,
    es_testnet BOOLEAN DEFAULT FALSE,
    configuracion JSON, -- Configuración adicional
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_activo (activo),
    INDEX idx_testnet (es_testnet)
);

-- Transacciones blockchain
CREATE TABLE blockchain_transactions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    certificate_id INT NOT NULL,
    network_id INT NOT NULL,
    
    tx_hash VARCHAR(66), -- Hash de transacción
    block_number BIGINT UNSIGNED,
    block_hash VARCHAR(66),
    tx_index INT,
    
    from_address VARCHAR(42) NOT NULL,
    to_address VARCHAR(42) NOT NULL,
    contract_address VARCHAR(42),
    
    gas_used INT,
    gas_price_wei BIGINT UNSIGNED,
    transaction_fee_wei BIGINT UNSIGNED,
    
    estado ENUM('pending', 'confirmed', 'failed') DEFAULT 'pending',
    confirmaciones INT DEFAULT 0,
    
    datos_transaccion JSON, -- Datos específicos de la transacción
    error_mensaje TEXT,
    
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    confirmed_at TIMESTAMP NULL,
    
    FOREIGN KEY (certificate_id) REFERENCES experience_certificates(id) ON DELETE CASCADE,
    FOREIGN KEY (network_id) REFERENCES blockchain_networks(id) ON DELETE CASCADE,
    
    INDEX idx_certificate (certificate_id),
    INDEX idx_tx_hash (tx_hash),
    INDEX idx_estado (estado),
    INDEX idx_timestamp (timestamp)
);

-- Wallet de usuarios para blockchain
CREATE TABLE user_wallets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT NOT NULL UNIQUE,
    
    wallet_address VARCHAR(42) UNIQUE NOT NULL,
    encrypted_private_key TEXT, -- Clave privada encriptada (opcional, para custodial wallets)
    wallet_type ENUM('custodial', 'non_custodial', 'external') DEFAULT 'custodial',
    
    provider VARCHAR(50), -- MetaMask, WalletConnect, etc.
    network_id INT,
    
    balance_wei BIGINT UNSIGNED DEFAULT 0,
    nonce INT DEFAULT 0,
    
    activo BOOLEAN DEFAULT TRUE,
    verificado BOOLEAN DEFAULT FALSE,
    fecha_verificacion TIMESTAMP NULL,
    
    configuracion JSON,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (network_id) REFERENCES blockchain_networks(id) ON DELETE SET NULL,
    
    INDEX idx_usuario (usuario_id),
    INDEX idx_address (wallet_address),
    INDEX idx_activo (activo)
);

-- Verificaciones de certificados
CREATE TABLE certificate_verifications (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    certificate_hash VARCHAR(64) NOT NULL,
    verificador_ip VARCHAR(45),
    verificador_user_agent TEXT,
    
    metodo_verificacion ENUM('qr_scan', 'hash_lookup', 'blockchain_query', 'api_call') NOT NULL,
    resultado_verificacion ENUM('valid', 'invalid', 'expired', 'revoked') NOT NULL,
    
    datos_verificacion JSON, -- Detalles específicos de la verificación
    ubicacion_gps JSON, -- Ubicación donde se verificó
    
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (certificate_hash) REFERENCES experience_certificates(certificate_hash) ON DELETE CASCADE,
    
    INDEX idx_certificate_hash (certificate_hash),
    INDEX idx_timestamp (timestamp),
    INDEX idx_resultado (resultado_verificacion)
);

-- Colecciones de NFTs
CREATE TABLE nft_collections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    descripcion TEXT,
    
    contract_address VARCHAR(42) UNIQUE NOT NULL,
    network_id INT NOT NULL,
    
    symbol VARCHAR(20) NOT NULL,
    total_supply INT DEFAULT 0,
    max_supply INT,
    
    metadata_base_uri VARCHAR(500),
    
    royalty_percentage DECIMAL(5,2) DEFAULT 0.00, -- Porcentaje de royalties
    royalty_recipient VARCHAR(42), -- Dirección que recibe royalties
    
    floor_price DECIMAL(18,8) DEFAULT 0,
    volume_traded DECIMAL(20,8) DEFAULT 0,
    
    activo BOOLEAN DEFAULT TRUE,
    featured BOOLEAN DEFAULT FALSE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (network_id) REFERENCES blockchain_networks(id) ON DELETE CASCADE,
    
    INDEX idx_contract (contract_address),
    INDEX idx_network (network_id),
    INDEX idx_featured (featured)
);

-- Marketplace para NFTs de certificados
CREATE TABLE nft_marketplace_listings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    certificate_id INT NOT NULL,
    seller_wallet_id INT NOT NULL,
    
    precio_listing DECIMAL(18,8) NOT NULL,
    currency_symbol VARCHAR(10) DEFAULT 'ETH',
    precio_usd DECIMAL(12,2), -- Precio equivalente en USD
    
    tipo_venta ENUM('fixed_price', 'auction', 'dutch_auction') DEFAULT 'fixed_price',
    fecha_inicio TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_fin TIMESTAMP,
    
    mejor_oferta DECIMAL(18,8) DEFAULT 0,
    oferente_wallet VARCHAR(42),
    
    estado ENUM('active', 'sold', 'cancelled', 'expired') DEFAULT 'active',
    
    comision_marketplace DECIMAL(5,2) DEFAULT 2.5, -- Porcentaje de comisión
    
    metadatos_listing JSON,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (certificate_id) REFERENCES experience_certificates(id) ON DELETE CASCADE,
    FOREIGN KEY (seller_wallet_id) REFERENCES user_wallets(id) ON DELETE CASCADE,
    
    INDEX idx_certificate (certificate_id),
    INDEX idx_seller (seller_wallet_id),
    INDEX idx_estado (estado),
    INDEX idx_precio (precio_listing)
);

-- Ofertas para NFTs
CREATE TABLE nft_offers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    listing_id INT NOT NULL,
    oferente_wallet_id INT NOT NULL,
    
    precio_oferta DECIMAL(18,8) NOT NULL,
    currency_symbol VARCHAR(10) DEFAULT 'ETH',
    
    expiration_date TIMESTAMP NOT NULL,
    estado ENUM('active', 'accepted', 'rejected', 'expired', 'cancelled') DEFAULT 'active',
    
    tx_hash VARCHAR(66), -- Hash de transacción si fue aceptada
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (listing_id) REFERENCES nft_marketplace_listings(id) ON DELETE CASCADE,
    FOREIGN KEY (oferente_wallet_id) REFERENCES user_wallets(id) ON DELETE CASCADE,
    
    INDEX idx_listing (listing_id),
    INDEX idx_oferente (oferente_wallet_id),
    INDEX idx_estado (estado),
    INDEX idx_expiration (expiration_date)
);

-- Estadísticas de blockchain y NFTs
CREATE TABLE blockchain_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    network_id INT NOT NULL,
    
    fecha DATE NOT NULL,
    
    certificados_emitidos INT DEFAULT 0,
    nfts_creados INT DEFAULT 0,
    transacciones_exitosas INT DEFAULT 0,
    transacciones_fallidas INT DEFAULT 0,
    
    gas_total_usado BIGINT UNSIGNED DEFAULT 0,
    costo_total_wei BIGINT UNSIGNED DEFAULT 0,
    costo_total_usd DECIMAL(12,2) DEFAULT 0,
    
    ventas_nft INT DEFAULT 0,
    volumen_ventas_wei BIGINT UNSIGNED DEFAULT 0,
    volumen_ventas_usd DECIMAL(12,2) DEFAULT 0,
    
    precio_promedio_gas_gwei DECIMAL(10,4) DEFAULT 0,
    tiempo_confirmacion_promedio INT DEFAULT 0, -- Segundos
    
    FOREIGN KEY (network_id) REFERENCES blockchain_networks(id) ON DELETE CASCADE,
    
    UNIQUE KEY unique_network_date (network_id, fecha),
    INDEX idx_fecha (fecha)
);

-- Insertar redes blockchain principales
INSERT INTO blockchain_networks (nombre, chain_id, network_name, rpc_url, explorer_url, currency_symbol, currency_decimals, es_testnet) VALUES
('Ethereum Mainnet', 1, 'ethereum', 'https://mainnet.infura.io/v3/YOUR_PROJECT_ID', 'https://etherscan.io', 'ETH', 18, FALSE),
('Polygon Mainnet', 137, 'polygon', 'https://polygon-rpc.com', 'https://polygonscan.com', 'MATIC', 18, FALSE),
('BSC Mainnet', 56, 'binance-smart-chain', 'https://bsc-dataseed.binance.org', 'https://bscscan.com', 'BNB', 18, FALSE),
('Ethereum Goerli', 5, 'goerli', 'https://goerli.infura.io/v3/YOUR_PROJECT_ID', 'https://goerli.etherscan.io', 'ETH', 18, TRUE),
('Polygon Mumbai', 80001, 'polygon-mumbai', 'https://rpc-mumbai.maticvigil.com', 'https://mumbai.polygonscan.com', 'MATIC', 18, TRUE);

-- Insertar plantillas de certificados
INSERT INTO certificate_templates (nombre, descripcion, tipo_certificado, categoria_producto, plantilla_html, campos_dinamicos, imagen_fondo_url, dimensiones, formato_salida) VALUES
('Certificado Tikal Completo', 'Certificado de finalización completa del tour por Tikal', 'completion', 'arqueologia', 
'<div class="certificate"><h1>{{titulo}}</h1><p>Se otorga a {{usuario_nombre}} por completar exitosamente la experiencia {{producto_nombre}} el {{fecha_experiencia}}.</p></div>',
'["usuario_nombre", "producto_nombre", "fecha_experiencia", "duracion", "calificacion"]',
'/assets/certificates/backgrounds/tikal_bg.jpg',
'{"width": 1200, "height": 800, "dpi": 300}',
'png'),

('Certificado AR Maya', 'Certificado de experiencia de realidad aumentada maya', 'achievement', 'arqueologia',
'<div class="ar-certificate"><h1>{{titulo}}</h1><p>{{usuario_nombre}} ha completado la experiencia de Realidad Aumentada "{{ar_experience_name}}" con una calificación de {{calificacion}} estrellas.</p></div>',
'["usuario_nombre", "ar_experience_name", "calificacion", "ubicacion", "fecha_experiencia"]',
'/assets/certificates/backgrounds/ar_maya_bg.jpg',
'{"width": 1200, "height": 800, "dpi": 300}',
'png'),

('NFT Souvenir Semuc Champey', 'NFT coleccionable de Semuc Champey', 'nft_souvenir', 'naturaleza',
'<div class="nft-certificate"><h1>{{titulo}}</h1><p>NFT Único - {{usuario_nombre}} visitó {{producto_nombre}} el {{fecha_experiencia}}. Coordenadas: {{latitud}}, {{longitud}}</p></div>',
'["usuario_nombre", "producto_nombre", "fecha_experiencia", "latitud", "longitud", "hora_visita"]',
'/assets/certificates/backgrounds/semuc_nft_bg.jpg',
'{"width": 1080, "height": 1080, "dpi": 300}',
'png');

-- Insertar reglas para certificados
INSERT INTO certificate_rules (nombre, descripcion, producto_id, tipo_certificado, nivel_certificado, condiciones, puntos_requeridos, experiencias_minimas, calificacion_minima, tiempo_minimo, automatico, es_nft) VALUES
('Completar Tour Tikal', 'Certificado por completar el tour completo de Tikal', 1, 'completion', 'gold', 
'{"completar_tour": true, "visitar_templos": 4, "tiempo_minimo": 240}', 100, 1, 4.0, 240, TRUE, FALSE),

('Maestro AR Maya', 'Completar 3 experiencias AR diferentes', NULL, 'achievement', 'platinum',
'{"ar_experiences_completed": 3, "calificacion_promedio": 4.5}', 300, 3, 4.5, NULL, TRUE, TRUE),

('Explorador Semuc Champey', 'Certificado por explorar todas las pozas de Semuc Champey', 3, 'completion', 'silver',
'{"completar_sendero": true, "tomar_fotos": 5, "tiempo_exploracion": 180}', 80, 1, 3.5, 180, TRUE, FALSE),

('Coleccionista NFT Maya', 'Crear tu primer NFT de experiencia maya', NULL, 'nft_souvenir', 'bronze',
'{"crear_primer_nft": true}', 50, 1, 3.0, 60, FALSE, TRUE);

-- Insertar colección NFT principal
INSERT INTO nft_collections (nombre, descripcion, contract_address, network_id, symbol, max_supply, metadata_base_uri, royalty_percentage, royalty_recipient, featured) VALUES
('Mayan World Experience Certificates', 'Certificados NFT de experiencias auténticas en el Mundo Maya', '0x1234567890123456789012345678901234567890', 2, 'MWEC', 10000, 'https://api.mayanworldtravelagency.com/nft/metadata/', 5.00, '0x1234567890123456789012345678901234567890', TRUE);

-- Insertar datos demo de certificados
INSERT INTO experience_certificates (certificate_hash, usuario_id, producto_id, reserva_id, tipo_certificado, nivel_certificado, titulo, descripcion, fecha_experiencia, duracion_experiencia, ubicacion_gps, metadatos_blockchain, estado) VALUES
('a1b2c3d4e5f6789012345678901234567890123456789012345678901234567890', 1, 1, 1, 'completion', 'gold', 
'Certificado de Exploración Completa - Tikal', 'Ha completado exitosamente el tour arqueológico completo de Tikal, visitando los principales templos y plazas ceremoniales.', 
'2024-01-15', 300, '{"lat": 17.2221, "lng": -89.6233, "altitude": 230}', 
'{"achievement": "complete_tikal_tour", "temples_visited": 6, "distance_walked": "5.2km", "photos_taken": 23}', 'minted'),

('b2c3d4e5f6789012345678901234567890123456789012345678901234567890a1', 2, 2, 2, 'achievement', 'platinum', 
'Maestro de Realidad Aumentada Maya', 'Ha completado múltiples experiencias de AR y demostrado un conocimiento excepcional de la cultura maya.',
'2024-01-20', 180, '{"lat": 17.0567, "lng": -89.3942, "altitude": 180}',
'{"ar_experiences": 4, "knowledge_score": 95, "interactive_elements": 12}', 'minted'),

('c3d4e5f6789012345678901234567890123456789012345678901234567890a1b2', 1, 3, 3, 'nft_souvenir', 'silver',
'NFT Exclusivo - Semuc Champey Paradise', 'NFT único que certifica la visita a las paradisiacas pozas de Semuc Champey en una fecha específica.',
'2024-02-01', 240, '{"lat": 15.5400, "lng": -89.9800, "altitude": 350}',
'{"pools_visited": 7, "water_temperature": "24°C", "weather": "sunny", "companions": 2}', 'minted');