> Car Rental Fleet & Reservation Management System

query_live = """
    SELECT
        b.booking_id, b.booking_timestamp, b.pickup_timestamp, b.return_timestamp,
        i.model_name AS car_name, i.item_id AS car_id,
        l1.location_name AS pickup_location_real,
        c.first_name, c.last_name, c.phone, c.email, ...
    FROM wp_car_rental_bookings b
    LEFT JOIN wp_car_rental_booking_options o ON b.booking_id = o.booking_id
    LEFT JOIN wp_car_rental_items i ON o.item_sku = i.item_sku
    LEFT JOIN wp_car_rental_locations l1 ON b.pickup_location_code = l1.location_code
    LEFT JOIN wp_car_rental_customers c ON b.customer_id = c.customer_id
    WHERE b.is_cancelled = 0
      AND (b.return_timestamp >= %s OR b.booking_id IN ({ids}))
    GROUP BY b.booking_id
""".format(ids=placeholders_str)

warning_limit = today_date + timedelta(days=30)
...
e['alerts'] = []
if lookup_key in cars_lookup:
    car_data = cars_lookup[lookup_key]
    date_fields = ['technical_inspection_validity', 'insurance_validity', 'vignete_validity',
                   'tehnical_revision_validity', 'casco_insurance_validity']
    for field in date_fields:
        val = car_data.get(field)
        if val and val != "N/A":
            try:
                dt_obj = val if isinstance(val, date) else datetime.strptime(str(val), '%Y-%m-%d').date()
                if dt_obj <= warning_limit:
                    label = field.replace('_validity', '').replace('_', ' ').upper()
                    e['alerts'].append(f"{label}: {dt_obj.strftime('%d-%m')}")
            except: continue

# Override on top of the website data: only real values, never None
if int(bid) in overrides:
    ov = overrides[int(bid)]
    if ov.get('override_car_name'): event['car_name'] = ov['override_car_name']
    if ov.get('override_car_plate'):
        event['car_plate'] = ov['override_car_plate']
        event['registration_plate'] = ov['override_car_plate']
    if ov.get('override_pickup_date'): event['pickup_timestamp'] = ov['override_pickup_date']
    if ov.get('override_return_date'): event['return_timestamp'] = ov['override_return_date']
    if ov.get('override_total_price'): event['grand_total'] = ov['override_total_price']
    ...

if bid in contracted_ids:
    # a recent manual edit must not be overwritten by the contract's data
    if bid not in overrides:
        r_date = contracted_ids[bid]['return_timestamp']
        r_hour = contracted_ids[bid]['return_hour_timestamp']
        r_loc  = contracted_ids[bid]['return_location']

    # an extension has the highest priority over the contract
    if bid in latest_ext:
        r_date = latest_ext[bid]['new_return_date']
        r_hour = latest_ext[bid]['new_return_hour']
        r_loc  = latest_ext[bid]['new_return_location']
...

INSERT INTO booking_overrides
    (external_booked_id, override_car_name, override_car_plate,
     override_pickup_date, override_pickup_hour, ..., override_guarantee)
VALUES (...)
ON DUPLICATE KEY UPDATE
    override_pickup_date=VALUES(override_pickup_date),
    override_return_date=VALUES(override_return_date),
    override_total_price=VALUES(override_total_price),
    ...

def check_collision(current_event, selected_plate, all_reservations):
    try:
        cur_start = datetime.strptime(f"{current_event['pickup_timestamp']} {current_event.get('pickup_hour_timestamp', '00:00')}", "%d-%m-%Y %H:%M")
        cur_end = datetime.strptime(f"{current_event['return_timestamp']} {current_event.get('return_hour_timestamp', '00:00')}", "%d-%m-%Y %H:%M")
    except:
        return False, None, None

    for res in all_reservations:
        res_plate = res.get('car_number') or res.get('car_plate')

        if int(res['booked_id']) != int(current_event['booked_id']) and res_plate == selected_plate:
            try:
                other_start = datetime.strptime(...)
                other_end = datetime.strptime(...)

                if cur_start < other_end and cur_end > other_start:
                    interval_info = f"{other_start.strftime('%d.%m %H:%M')} - {other_end.strftime('%d.%m %H:%M')}"
                    return True, res['booked_id'], interval_info
            except:
                continue
    return False, None, None

SELECT c.booked_id,
       COALESCE(e.new_return_date, c.return_timestamp) AS final_return_date,
       COALESCE(e.new_return_hour, c.return_hour_timestamp) AS final_return_hour
FROM contracts_data c
LEFT JOIN (
    SELECT booked_id, new_return_date, new_return_hour
    FROM contract_extensions
    WHERE id IN (SELECT MAX(id) FROM contract_extensions GROUP BY booked_id)
) e ON c.booked_id = e.booked_id
WHERE c.is_completed IS NULL OR c.is_completed != 1
...

sql = '''
    INSERT INTO booking_overrides (external_booked_id, override_car_name, override_car_plate)
    VALUES (%s, %s, %s)
    ON DUPLICATE KEY UPDATE
        override_car_name = VALUES(override_car_name),
        override_car_plate = VALUES(override_car_plate)
'''
cursor.execute(sql, (booking_id, new_model, new_plate))

cursor.execute("""
    INSERT INTO events_details_added (booked_id, car_number)
    VALUES (%s, %s)
    ON DUPLICATE KEY UPDATE car_number = VALUES(car_number)
""", (booking_id, new_plate))

raw_active = list(data.pending_bookings.values()) + \
             list(data.current_bookings.values()) + \
             [b for b in data.contracted_bookings_data.values() if b.get('is_completed') != 1]
...

if b_start < end_search and b_end > start_search:          # overlaps the searched period

    # CASE A: the old booking ends on the first day of the search (car gets released)
    if b_end.date() == start_search.date():
        car_availability_data[plate] = {'type': 'start', 'time': r_hour}

    # CASE B: a new booking starts on the last day of the search (car is taken again)
    elif b_start.date() == end_search.date():
        car_availability_data[plate] = {'type': 'end', 'time': p_hour}

    # CASE C: total overlap inside the period -> not available
    else:
        busy_car_plates.add(plate)
...

for car in all_cars:
    clean_p = str(car['car_number']).strip().upper()

    # busy for the whole period
    if clean_p in {str(plate).strip().upper() for plate in busy_car_plates}:
        continue

    available_cars.append({'id': car['id'], 'model': car['car_model'], 'plate': car['car_number']})

# A. Add the ID to the block list (visual filtering)
cursor.execute("INSERT IGNORE INTO local_blocked_bookings (booked_id) VALUES (%s)", (bid,))

# B. RELEASE THE CAR (delete allocations from the swap/details tables)
cursor.execute("DELETE FROM events_details_added WHERE booked_id = %s", (bid,))
cursor.execute("DELETE FROM booking_overrides WHERE external_booked_id = %s", (bid,))

db_conn.commit()

valid_files = [f for f in files_to_process if f and str(f).strip().lower() not in ['none', '']]
if not valid_files:
    return extracted

with ThreadPoolExecutor(max_workers=len(valid_files)) as executor:
    ocr_results = list(executor.map(worker_ocr, valid_files))
...

is_uk_authority = any(k in u_text for k in ["DVLA", "SWANSEA", "GREAT BRITAIN", "S99 1BN"])
is_italian = any(k in u_text for k in ["ITALIANA", "PATENTE", "REPUBBLICA", "CARTA DI IDENTIT", "RESIDENZA", "CODICE FISCALE"])
is_spain = any(k in u_text for k in ["ESPANA", "CONDUCCION", "REINO DE ESPA", "DNI ", "DIRECCION GENERAL DE TRAFICO"])
...
res = {}
if is_uk_authority: res = extract_uk(text, u_text, u_no_spaces, lines)
elif is_italian: res = extract_italia(text, u_text, u_no_spaces, lines)
elif is_spain: res = extract_spain(text, u_text, u_no_spaces, lines)
elif is_ro_language or ("ROMANIA" in u_text and "DRIVING LICENCE" not in u_text): res = extract_romania(text, u_text, u_no_spaces, lines)
...

cnp_m = re.search(r'\b[1-9]\d{12}\b', text)
if cnp_m:
    extracted['cnp'] = cnp_m.group(0)
    c = extracted['cnp']
    pref = "19" if c[0] in ["1", "2"] else "20"
    extracted['birth_date'] = f"{c[5:7]}.{c[3:5]}.{pref}{c[1:3]}"

# ...and the licence number is located right after it, with OCR-error correction
pattern = extracted['cnp'] + r'[.\s5]*([I10B][0-9]{8}[A-Z0-9]{1,2})'
lic_m = re.search(pattern, u_no_spaces)
if lic_m:
    res = lic_m.group(1).upper()
    if len(res) > 10: res = res[:10]
    if res.startswith('1'): res = 'I' + res[1:]      # "1" read instead of "I"
    if res.endswith('5'):   res = res[:-1] + 'S'     # "5" read instead of "S"
    extracted['license_no'] = res
...

# merge across documents: keep the most complete licence number
if k == 'license_no':
    clean_val = re.sub(r'[^A-Z0-9]', '', val.upper())
    if len(clean_val) >= 7:
        if not extracted.get(key) or len(clean_val) >= len(str(extracted.get(key))):
            extracted[key] = clean_val

...
cursor.execute("SELECT rent_contract_number FROM contracts_data WHERE CAST(rent_contract_number AS UNSIGNED) >= 500 ORDER BY CAST(rent_contract_number AS UNSIGNED) DESC LIMIT 1")
    row = cursor.fetchone()
    if row and row.get('rent_contract_number') is not None:
        contract_num_str = str(int(row['rent_contract_number']) + 1)
    else:
        # empty database: take the number the agent typed in the form
        contract_num_str = str(rent_form.rent_contract_number.data)
...

total_seconds = int(return_ts - pickup_ts)
full_days, remainder_seconds = divmod(total_seconds, 86400)
rent_days = full_days + 1 if remainder_seconds >= 1800 else max(1, full_days)
...

buffer = BytesIO()
p = Canvas(buffer, pagesize=A4)
p.setFont("ArialUnicode", 8)
...
p.drawImage(header_img, 20, 770, 120, 60)
p.line(20, 770, 580, 770)

check_event = event_requested.copy()
check_event['return_timestamp'] = new_date_str
check_event['return_hour_timestamp'] = new_hour_str

collision, conflicting_id, conflict_period = check_collision(check_event, current_plate, all_reservations)
if collision:
    flash(f"⚠️ Car {current_plate} is busy with booking #{conflicting_id} ({conflict_period})!", "danger")
    return render_template('extension_form.html', form=form, event=event_requested, booked_id=booked_id)

sql = """
    INSERT INTO contract_extensions
    (booked_id, new_return_date, new_return_hour, new_return_location, extension_price)
    VALUES (%s, %s, %s, %s, %s)
"""

sql_check = "SELECT id FROM contract_extensions WHERE booked_id = %s AND extension_pdf IS NULL"
cursor.execute(sql_check, (booking_id,))
unsigned_extension = cursor.fetchone()
...
if unsigned_extension:
    flash("An additional act exists without a signed PDF. Sign it before closing the contract.")
    return redirect(url_for('booking_details', booking_id=booking_id))
...

sql = "UPDATE contracts_data SET completion_pv = %s, is_completed = %s WHERE booked_id = %s"
cursor.execute(sql, (binary_raport_to_insert, 1, booked_id))

# delete the override so the car is released in searches
cursor.execute("DELETE FROM booking_overrides WHERE external_booked_id = %s", (booked_id,))

db_conn.commit()

for plate, info in fleet_records.items():
    allocated_rev = info['allocated_revenue']
    rented_days = info['rented_days_total']

    rev_pav = allocated_rev / days_in_range                    # revenue per available vehicle-day
    occupancy_rate = (rented_days / days_in_range) * 100
    if occupancy_rate > 100: occupancy_rate = 100.0
    avg_real_price = (allocated_rev / rented_days) if rented_days > 0 else 0.0

    profitability_data.append({
        'plate': plate,
        'total_revenue': allocated_rev,
        'rev_pav': rev_pav,
        'occupancy_rate': occupancy_rate,
        'avg_real_price': avg_real_price,
        'ancillary_revenue': info['allocated_fees'],
        'risk_revenue': info['allocated_risk']
    })

profitability_data = sorted(profitability_data, key=lambda x: x['rev_pav'], reverse=True)
...

SELECT COUNT(*) FROM (
    SELECT first_name, last_name, phone, email
    FROM contracts_data
    GROUP BY first_name, last_name, phone, email
) AS unique_clients_subquery

total_seconds = int((return_datetime - pickup_datetime).total_seconds())
if total_seconds <= 0:
    total_seconds = 3600
days_render = int(math.ceil(total_seconds / 86400))
if days_render <= 0:
    days_render = 1

tax_factor = (1 + tax_percentage / 100) if show_with_tax == 1 else 1.0

# after-hours check for the pick-up location on that weekday
p_open_raw = loc_p.get(f'open_time_{pickup_day_short}')
p_close_raw = loc_p.get(f'close_time_{pickup_day_short}')
# MySQL TIME columns arrive as timedelta -> normalize to HH:MM:SS strings
p_open = str(p_open_raw).zfill(8) if isinstance(p_open_raw, timedelta) else str(p_open_raw or '08:00:00')
p_close = str(p_close_raw).zfill(8) if isinstance(p_close_raw, timedelta) else str(p_close_raw or '22:00:00')

if pickup_time_str < p_open or pickup_time_str > p_close:
    night_pickup_fee = float(loc_p['afterhours_pickup_fee'] or 0)
...

if is_guarantee_regime_final:
    message_lines.append("✓ *Standard car deposit* - the model's deposit is held at pick-up")
    message_lines.append("⚠️ *Contractual liability* - the customer is liable up to the deductible in case of damage")
else:
    message_lines.append("✓ *Full CASCO package included* - no stress over damage or scratches")
    message_lines.append("✓ *Zero deposit* - no deposit is held, no credit card required!")
...
generated_text_whatsapp = "\n".join(message_lines)

raw_phone = client_phone.strip().replace(" ", "").replace("+", "")
encoded_message = quote(generated_text_whatsapp)
whatsapp_direct_url = f"https://wa.me/{raw_phone}?text={encoded_message}"
return redirect(whatsapp_direct_url)
...
db_wp = pool_live.get_connection()
cursor_wp = db_wp.cursor(dictionary=True)

# disable autocommit: if a single table fails, everything is rolled back
db_wp.autocommit = False

cursor_wp.execute(insert_customer_sql, (first_name, last_name, phone, email, now_ts, now_ts))
generated_customer_id = cursor_wp.lastrowid
...
generated_booking_id = cursor_wp.lastrowid

# official code in the site's own format
official_code = generate_site_booking_code(generated_booking_id)
cursor_wp.execute(
    "UPDATE wp_car_rental_bookings SET booking_code = %s WHERE booking_id = %s",
    (official_code, generated_booking_id)
)
...
db_wp.commit()
...
except mysql.connector.Error as sql_error:
    if db_wp:
        db_wp.rollback()    # cancel everything if any rule fails
...
def generate_site_booking_code(booking_id):
    chars = string.ascii_uppercase + string.digits
    random_suffix = "".join(secrets.choice(chars) for _ in range(5))
    return f"R{booking_id}A{random_suffix}"