-- RNPL (Reserve Now Pay Later) System Database Schema
USE travel_mvp;

-- Extender tabla de reservas para RNPL
ALTER TABLE reservas 
ADD COLUMN payment_method ENUM('immediate', 'rnpl', 'partial') DEFAULT 'immediate' AFTER metodo_pago,
ADD COLUMN payment_status ENUM('pending', 'authorized', 'captured', 'failed', 'refunded') DEFAULT 'pending' AFTER payment_method,
ADD COLUMN payment_due_date DATETIME NULL AFTER payment_status,
ADD COLUMN payment_reminder_sent BOOLEAN DEFAULT FALSE AFTER payment_due_date,
ADD COLUMN rnpl_hold_amount DECIMAL(10,2) DEFAULT 0 AFTER payment_reminder_sent,
ADD COLUMN payment_processor ENUM('stripe', 'paypal', 'manual') DEFAULT 'stripe' AFTER rnpl_hold_amount;

-- Tabla para transacciones de pago
CREATE TABLE payment_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reserva_id INT NOT NULL,
    transaction_type ENUM('authorization', 'capture', 'refund', 'void') NOT NULL,
    payment_intent_id VARCHAR(255), -- Stripe Payment Intent ID
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    status ENUM('pending', 'succeeded', 'failed', 'canceled') DEFAULT 'pending',
    processor_response JSON, -- Respuesta completa del procesador
    failure_reason TEXT,
    processed_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (reserva_id) REFERENCES reservas(id) ON DELETE CASCADE,
    INDEX idx_reserva_type (reserva_id, transaction_type),
    INDEX idx_payment_intent (payment_intent_id),
    INDEX idx_status (status),
    INDEX idx_processed_at (processed_at)
);

-- Tabla para recordatorios automáticos
CREATE TABLE payment_reminders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reserva_id INT NOT NULL,
    reminder_type ENUM('initial', '24h', '12h', '2h', 'overdue') NOT NULL,
    scheduled_at DATETIME NOT NULL,
    sent_at DATETIME NULL,
    status ENUM('pending', 'sent', 'failed', 'canceled') DEFAULT 'pending',
    email_sent BOOLEAN DEFAULT FALSE,
    whatsapp_sent BOOLEAN DEFAULT FALSE,
    push_sent BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (reserva_id) REFERENCES reservas(id) ON DELETE CASCADE,
    INDEX idx_scheduled (scheduled_at, status),
    INDEX idx_reserva (reserva_id),
    INDEX idx_type_status (reminder_type, status)
);

-- Tabla para configuración RNPL
CREATE TABLE rnpl_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insertar configuraciones por defecto
INSERT INTO rnpl_settings (setting_key, setting_value, description) VALUES
('rnpl_enabled', 'true', 'Habilitar sistema RNPL globalmente'),
('payment_window_hours', '48', 'Horas antes del tour para completar pago'),
('hold_amount_percentage', '10', 'Porcentaje del total a autorizar como hold'),
('reminder_24h_enabled', 'true', 'Enviar recordatorio 24h antes'),
('reminder_12h_enabled', 'true', 'Enviar recordatorio 12h antes'),
('reminder_2h_enabled', 'true', 'Enviar recordatorio 2h antes'),
('auto_cancel_overdue', 'true', 'Cancelar automáticamente reservas vencidas'),
('grace_period_hours', '2', 'Horas de gracia después del vencimiento'),
('min_advance_booking_hours', '72', 'Mínimo tiempo de anticipación para RNPL');

-- Agregar índices para optimización
ALTER TABLE reservas ADD INDEX idx_payment_method (payment_method);
ALTER TABLE reservas ADD INDEX idx_payment_due (payment_due_date, payment_status);
ALTER TABLE reservas ADD INDEX idx_rnpl_pending (payment_method, payment_status, payment_due_date);

-- Vista para reservas RNPL pendientes
CREATE OR REPLACE VIEW rnpl_pending_payments AS
SELECT 
    r.id,
    r.codigo_reserva,
    r.cliente_nombre,
    r.cliente_email,
    r.cliente_telefono,
    r.fecha_salida,
    r.precio_final,
    r.rnpl_hold_amount,
    r.payment_due_date,
    r.payment_reminder_sent,
    p.nombre as producto_nombre,
    TIMESTAMPDIFF(HOUR, NOW(), r.payment_due_date) as hours_remaining,
    CASE 
        WHEN r.payment_due_date < NOW() THEN 'overdue'
        WHEN TIMESTAMPDIFF(HOUR, NOW(), r.payment_due_date) <= 2 THEN 'urgent'
        WHEN TIMESTAMPDIFF(HOUR, NOW(), r.payment_due_date) <= 12 THEN 'soon'
        ELSE 'ok'
    END as urgency_level
FROM reservas r
JOIN productos p ON r.producto_id = p.id
WHERE r.payment_method = 'rnpl' 
    AND r.payment_status = 'pending'
    AND r.estado IN ('pendiente', 'confirmada');

-- Trigger para crear recordatorios automáticos
DELIMITER $$
CREATE TRIGGER create_rnpl_reminders
AFTER UPDATE ON reservas
FOR EACH ROW
BEGIN
    IF NEW.payment_method = 'rnpl' 
       AND NEW.payment_due_date IS NOT NULL 
       AND OLD.payment_due_date IS NULL THEN
       
        -- Recordatorio 24h antes
        INSERT INTO payment_reminders (reserva_id, reminder_type, scheduled_at)
        VALUES (NEW.id, '24h', DATE_SUB(NEW.payment_due_date, INTERVAL 24 HOUR));
        
        -- Recordatorio 12h antes
        INSERT INTO payment_reminders (reserva_id, reminder_type, scheduled_at)
        VALUES (NEW.id, '12h', DATE_SUB(NEW.payment_due_date, INTERVAL 12 HOUR));
        
        -- Recordatorio 2h antes
        INSERT INTO payment_reminders (reserva_id, reminder_type, scheduled_at)
        VALUES (NEW.id, '2h', DATE_SUB(NEW.payment_due_date, INTERVAL 2 HOUR));
    END IF;
END$$
DELIMITER ;