Implement online check-in system with signed links and stay workflow

[dgpaci]

Objective

Implement an online check-in system allowing facility managers to:

1. Create a stay with minimal group leader info (name, surname, email, phone)
2. Generate a signed link for the guest to complete their data online
3. Track stay status through a workflow (pending → ready → in_progress → completed)
4. Trigger external reporting (Police, Ross1000) when stay is ready

User Story

As a facility manager
I want to create a stay with just basic contact info
So that the guest can complete check-in online before arrival

As a guest (group leader)
I want to receive a secure link
So that I can complete my data and add my travel companions online

As a facility manager
I want to see when check-in is complete
So that I can report guest data to authorities

Current Flow Issues

Currently:

• Manager must enter all guest data manually
• No way for guests to self-register
• No workflow tracking for stay completion

Proposed Solution

1. Stay Status Workflow

New Table: stay_status (lookup)

File: model/stay_status.py

class Table(object):
    """Stay Status lookup table"""
    
    def config_db(self, pkg):
        tbl = pkg.table('stay_status', pkey='id', 
                       name_long='Stay Status',
                       caption_field='description')
        
        self.sysFields(tbl)
        tbl.column('code', size=':15', unique=True)
        tbl.column('description', size=':50')
        tbl.column('sequence', dtype='I')  # Ordering

Status Codes:

Code          Description                Meaning
-----------   ------------------------   ----------------------------------
pending       Pending Check-in           Created, waiting for guest data
ready         Ready to Report            Guest completed online check-in
in_progress   Stay In Progress           Currently hosting guests
completed     Stay Completed             Check-out done
cancelled     Cancelled                  Booking cancelled

2. Update Stay Model

File: model/stay.py

Add fields:

# Status tracking
tbl.column('status_id', size='22', name_long='Status', validate_notnull=True,
          default='pending')\
   .relation('host.stay_status.id', mode='foreignkey',
            relation_name='stays', onDelete='raise')

# Online check-in link
tbl.column('checkin_token', size='64', name_long='Check-in Token',
          indexed=True)
tbl.column('checkin_token_expires', dtype='DH', name_long='Token Expires')

# Group leader minimal info (for initial creation)
tbl.column('leader_email', size=':100', name_long='Leader Email')
tbl.column('leader_phone', size=':30', name_long='Leader Phone')
tbl.column('leader_firstname', size=':50', name_long='Leader First Name')
tbl.column('leader_surname', size=':50', name_long='Leader Surname')

# Alias
tbl.aliasColumn('status_code', '@status_id.code')
tbl.aliasColumn('status_description', '@status_id.description')

Add methods:

def generate_checkin_link(self, record):
    """Generate signed check-in link"""
    import secrets
    from datetime import datetime, timedelta
    
    # Generate secure token
    token = secrets.token_urlsafe(32)
    
    # Set expiration (e.g., 7 days)
    expires = datetime.now() + timedelta(days=7)
    
    # Update record
    record['checkin_token'] = token
    record['checkin_token_expires'] = expires
    
    # Return signed URL
    return f"/host/onlinecheckin?pkey={record['id']}&token={token}&_signed=True"

def validate_checkin_token(self, stay_id, token):
    """Validate check-in token"""
    from datetime import datetime
    
    stay = self.db.table('host.stay').record(pkey=stay_id).output('dict')
    
    if not stay:
        return False, "Stay not found"
    
    if stay['checkin_token'] != token:
        return False, "Invalid token"
    
    if stay['checkin_token_expires'] < datetime.now():
        return False, "Token expired"
    
    if stay['status_code'] != 'pending':
        return False, "Check-in already completed"
    
    return True, "Token valid"

def complete_checkin(self, stay_id):
    """Mark check-in as completed → status = ready"""
    # Validate all required data present
    # Update status to 'ready'
    # Send notification to facility manager

3. Online Check-in Webpage

File: webpages/onlinecheckin.py

#!/usr/bin/env python
# encoding: utf-8

class GnrCustomWebPage(object):
    """Public webpage for guest online check-in"""
    
    py_requires = 'gnrcomponents/wizard:Wizard'
    
    def main(self, root, **kwargs):
        """Main check-in wizard"""
        pkey = self.page.path_info.get('pkey')
        token = self.page.path_info.get('token')
        
        # Validate token
        valid, message = self.db.table('host.stay').validate_checkin_token(pkey, token)
        
        if not valid:
            root.div(message, color='red', font_size='20px')
            return
        
        # Load stay info
        stay = self.db.table('host.stay').record(pkey=pkey).output('dict')
        
        # Multi-step wizard
        wizard = root.wizard(id='checkin_wizard', 
                            title='Online Check-in',
                            _class='checkin_wizard')
        
        # Step 1: Group Leader Data
        self.wizard_step_leader(wizard, stay)
        
        # Step 2: Additional Guests
        self.wizard_step_guests(wizard, stay)
        
        # Step 3: Review & Confirm
        self.wizard_step_confirm(wizard, stay)
    
    def wizard_step_leader(self, wizard, stay):
        """Step 1: Complete group leader information"""
        step = wizard.wizardStep(title='Your Information',
                                subtitle='Complete your personal data')
        
        fb = step.formbuilder(cols=2, border_spacing='4px',
                             datapath='.leader')
        
        # Pre-fill from stay
        fb.data('.leader.firstname', stay['leader_firstname'])
        fb.data('.leader.surname', stay['leader_surname'])
        fb.data('.leader.email', stay['leader_email'])
        fb.data('.leader.phone', stay['leader_phone'])
        
        # Personal data
        fb.textbox('^.firstname', lbl='First Name', validate_notnull=True)
        fb.textbox('^.surname', lbl='Surname', validate_notnull=True)
        fb.dateTextBox('^.birth_date', lbl='Birth Date', validate_notnull=True)
        fb.textbox('^.birth_place', lbl='Birth Place')
        fb.comboBox('^.gender', lbl='Gender', 
                   values='M:Male,F:Female', validate_notnull=True)
        fb.textbox('^.citizenship', lbl='Citizenship')
        
        # Document (required for group leader)
        fb.div('Document Information', colspan=2, font_weight='bold')
        fb.filteringSelect('^.document_type', lbl='Document Type',
                          dbtable='host.document_type',
                          validate_notnull=True)
        fb.textbox('^.document_number', lbl='Document Number',
                  validate_notnull=True)
        fb.textbox('^.document_issued_by', lbl='Issued By')
        fb.dateTextBox('^.document_issue_date', lbl='Issue Date')
        fb.dateTextBox('^.document_expiry_date', lbl='Expiry Date')
        
        # Contact
        fb.textbox('^.email', lbl='Email', validate_notnull=True)
        fb.textbox('^.phone', lbl='Phone')
    
    def wizard_step_guests(self, wizard, stay):
        """Step 2: Add additional guests"""
        step = wizard.wizardStep(title='Travel Companions',
                                subtitle='Add other guests traveling with you')
        
        # Editable grid for additional guests
        grid = step.includedView(struct='host.guest',
                                datapath='.guests',
                                grid_editable=True)
        
        grid.button('Add Guest', fire='.add_guest')
    
    def wizard_step_confirm(self, wizard, stay):
        """Step 3: Review and confirm"""
        step = wizard.wizardStep(title='Review',
                                subtitle='Please review your information')
        
        # Display summary
        step.div('^.leader.firstname', lbl='First Name')
        step.div('^.leader.surname', lbl='Surname')
        # ... show all data for review
        
        # Confirm button
        step.button('Complete Check-in',
                   fire='.submit_checkin',
                   action='this.publish("submit_checkin");')
    
    def rpc_submit_checkin(self, stay_id, leader_data, guests_data):
        """RPC: Save check-in data and update status"""
        
        # 1. Create/update leader anagrafica
        anagrafica_id = self._create_or_update_anagrafica(leader_data)
        
        # 2. Create leader guest record
        leader_guest_id = self._create_guest(anagrafica_id, leader_data)
        
        # 3. Create stay_guest for leader
        self._create_stay_guest(stay_id, leader_guest_id, 
                               guest_type='17',  # CAPO FAMIGLIA
                               is_leader=True)
        
        # 4. Create additional guests
        for guest_data in guests_data:
            guest_anagrafica_id = self._create_or_update_anagrafica(guest_data)
            guest_id = self._create_guest(guest_anagrafica_id, guest_data)
            self._create_stay_guest(stay_id, guest_id,
                                   guest_type='19')  # FAMILIARE
        
        # 5. Update stay status to 'ready'
        self.db.table('host.stay').update({
            'id': stay_id,
            'status_id': '@status_code=ready'
        })
        
        # 6. Send notification to facility manager
        self._send_manager_notification(stay_id)
        
        return {'success': True, 'message': 'Check-in completed!'}

4. Update Stay Table Handler

File: resources/tables/stay/th_stay.py

Add to form:

def th_form(self, form):
    # ... existing code ...
    
    # Quick creation mode
    quick_create = form.dialog(title='Quick Stay Creation',
                              fire='quick_create_stay')
    qc_fb = quick_create.formbuilder(cols=2)
    qc_fb.field('facility_id')
    qc_fb.field('check_in_date')
    qc_fb.field('check_out_date')
    qc_fb.field('leader_firstname', lbl='Guest First Name')
    qc_fb.field('leader_surname', lbl='Guest Surname')
    qc_fb.field('leader_email', lbl='Guest Email')
    qc_fb.field('leader_phone', lbl='Guest Phone')
    qc_fb.button('Create & Send Link', fire='.create_and_send')
    
    # Check-in link section (only if pending)
    checkin_section = top.div(visible='^.status_code=="pending"')
    checkin_section.div('Online Check-in Link:', font_weight='bold')
    checkin_section.div('^.checkin_link', 
                       color='blue',
                       cursor='pointer',
                       onclick='window.open(this.innerHTML);')
    checkin_section.button('Copy Link', 
                          fire='.copy_link',
                          icon='copy')
    checkin_section.button('Send Link via Email',
                          fire='.send_checkin_email',
                          icon='email')
    
    # Report buttons (only if ready)
    report_section = top.div(visible='^.status_code=="ready"',
                            margin_top='10px')
    report_section.button('Report to Police',
                         fire='.report_police',
                         icon='send',
                         color='green')
    report_section.button('Export to Ross1000',
                         fire='.export_ross1000',
                         icon='export')

def rpc_create_quick_stay(self, data):
    """RPC: Create stay with minimal info and generate link"""
    
    # Create stay record
    stay_id = self.db.table('host.stay').insert({
        'facility_id': data['facility_id'],
        'check_in_date': data['check_in_date'],
        'check_out_date': data['check_out_date'],
        'leader_firstname': data['leader_firstname'],
        'leader_surname': data['leader_surname'],
        'leader_email': data['leader_email'],
        'leader_phone': data['leader_phone'],
        'status_id': '@code=pending'
    })
    
    # Generate signed link
    link = self.db.table('host.stay').generate_checkin_link(stay_id)
    
    # Send email
    self._send_checkin_email(data['leader_email'], link)
    
    return {'stay_id': stay_id, 'link': link}

def rpc_report_to_police(self, stay_id):
    """RPC: Trigger police reporting"""
    from resources.services.guest_reporting_service import GuestReportingService
    
    service = GuestReportingService(self.db)
    result = service.report_to_police(stay_id)
    
    if result['success']:
        # Update status to in_progress
        self.db.table('host.stay').update({
            'id': stay_id,
            'status_id': '@code=in_progress'
        })
    
    return result

5. Email Templates

File: resources/email_templates/checkin_link.html

<html>
<body>
  <h2>Complete Your Check-in</h2>
  <p>Dear {{leader_firstname}} {{leader_surname}},</p>
  <p>Please complete your online check-in for your upcoming stay at {{facility_name}}.</p>
  <p><strong>Check-in:</strong> {{check_in_date}}<br>
     <strong>Check-out:</strong> {{check_out_date}}</p>
  <p><a href="{{checkin_link}}" style="background:#007bff;color:white;padding:10px 20px;text-decoration:none;border-radius:5px;">
    Complete Check-in
  </a></p>
  <p>This link will expire on {{token_expires}}.</p>
</body>
</html>

Data Flow

1. Manager creates stay with minimal info
   ├─ leader_firstname, leader_surname
   ├─ leader_email, leader_phone
   ├─ check_in_date, check_out_date
   └─ status = 'pending'
   ↓
2. System generates signed link
   ├─ Creates secure token
   ├─ Sets expiration (7 days)
   └─ Returns: /host/onlinecheckin?pkey=XXX&token=YYY&_signed=True
   ↓
3. Email sent to guest with link
   ↓
4. Guest clicks link → Online check-in wizard
   ├─ Step 1: Complete personal data + document
   ├─ Step 2: Add travel companions
   └─ Step 3: Review and confirm
   ↓
5. Guest submits → System processes
   ├─ Creates/updates anagrafica records
   ├─ Creates guest records
   ├─ Creates stay_guest records
   ├─ Updates stay.status = 'ready'
   └─ Notifies manager
   ↓
6. Manager sees "Ready to Report"
   ├─ Clicks "Report to Police"
   ├─ System calls GuestReportingService
   └─ Updates status = 'in_progress'
   ↓
7. After check-out
   └─ Manager updates status = 'completed'

UI/UX Mockup

Manager View (Stay Form)

┌─────────────────────────────────────────────┐
│ Stay #12345                                 │
│ Status: [Pending Check-in ●]                │
├─────────────────────────────────────────────┤
│ Facility: Hotel Bella Vista                 │
│ Check-in: 2026-02-15                        │
│ Check-out: 2026-02-20                       │
│ Nights: 5                                   │
├─────────────────────────────────────────────┤
│ Group Leader Contact:                       │
│ Name: Mario Rossi                           │
│ Email: mario.rossi@example.com              │
│ Phone: +39 333 1234567                      │
├─────────────────────────────────────────────┤
│ ⚠️ Waiting for guest to complete check-in   │
│                                             │
│ Check-in Link:                              │
│ https://myhotel.com/host/onlinecheckin?...  │
│ [📋 Copy Link]  [📧 Send Email]             │
└─────────────────────────────────────────────┘

Manager View (After Guest Completes)

┌─────────────────────────────────────────────┐
│ Stay #12345                                 │
│ Status: [Ready to Report ✓]                 │
├─────────────────────────────────────────────┤
│ ✅ Guest completed online check-in          │
│                                             │
│ Group Leader: Mario Rossi                   │
│ Additional Guests: 3                        │
│ - Laura Rossi (spouse)                      │
│ - Luca Rossi (son, 10 years)                │
│ - Sofia Rossi (daughter, 8 years)           │
├─────────────────────────────────────────────┤
│ [📤 Report to Police]  [📤 Export Ross1000] │
└─────────────────────────────────────────────┘

Guest View (Online Check-in Wizard)

┌─────────────────────────────────────────────┐
│          Online Check-in Wizard             │
│  [1. Your Info] → [2. Companions] → [3. Review]  │
├─────────────────────────────────────────────┤
│ Step 1: Your Information                    │
│                                             │
│ First Name: [Mario           ]              │
│ Surname:    [Rossi           ]              │
│ Birth Date: [15/03/1980      ]              │
│ Gender:     [Male ▼          ]              │
│                                             │
│ Document Information                        │
│ Type:       [Passport ▼      ]              │
│ Number:     [AA1234567       ]              │
│ Issued By:  [Rome            ]              │
│                                             │
│              [Next Step →]                   │
└─────────────────────────────────────────────┘

Security Considerations

1. Signed URLs: Use secure tokens (32+ chars)
2. Expiration: Tokens expire after 7 days
3. One-time Use: Token invalidated after use
4. Status Check: Only 'pending' stays accept check-in
5. HTTPS Only: Enforce SSL for check-in pages
6. Rate Limiting: Prevent brute-force token guessing

Database Schema Changes

New Table: stay_status

CREATE TABLE host.stay_status (
  id UUID PRIMARY KEY,
  code VARCHAR(15) UNIQUE NOT NULL,
  description VARCHAR(50) NOT NULL,
  sequence INTEGER
  -- + sys_record fields
);

Update Table: stay

ALTER TABLE host.stay ADD COLUMN status_id UUID REFERENCES host.stay_status(id);
ALTER TABLE host.stay ADD COLUMN checkin_token VARCHAR(64);
ALTER TABLE host.stay ADD COLUMN checkin_token_expires TIMESTAMP;
ALTER TABLE host.stay ADD COLUMN leader_email VARCHAR(100);
ALTER TABLE host.stay ADD COLUMN leader_phone VARCHAR(30);
ALTER TABLE host.stay ADD COLUMN leader_firstname VARCHAR(50);
ALTER TABLE host.stay ADD COLUMN leader_surname VARCHAR(50);

Acceptance Criteria

• stay_status lookup table created with 5 statuses
• stay model updated with status and minimal leader fields
• Quick stay creation form implemented
• Signed check-in link generation working
• Online check-in wizard (3 steps) implemented
• Guest can complete their data and add companions
• System creates anagrafica and guest records
• Status transitions: pending → ready → in_progress → completed
• "Report to Police" button visible only when status = 'ready'
• Email template for check-in link created
• Token validation and expiration working
• Security measures implemented

Testing Checklist

• Create stay with minimal info
• Generate check-in link
• Guest completes check-in (all steps)
• Status updates to 'ready'
• Manager sees report buttons
• Token expiration works
• Invalid tokens rejected
• Email sending works

Dependencies

• Existing: stay, stay_guest, guest, anagrafica tables
• New: stay_status lookup table
• Email sending service (Genropy built-in)

Estimated Effort

• stay_status model: 0.5 day
• Stay model updates: 1 day
• Quick creation UI: 1 day
• Online check-in wizard: 3 days
• Data processing (RPC methods): 2 days
• Email templates: 0.5 day
• Testing: 2 days

Total: ~10 days

Future Enhancements

• SMS notifications (in addition to email)
• Multi-language support for check-in wizard
• QR code generation for check-in link
• Mobile-responsive design
• Document upload (scan/photo)
• Integration with payment systems