rateloader — Implementation PRD (with code quotes for fidelity)

> Goal: unify three legacy utilities into a single Windows app rateloader with GUI + background runner, while fully preserving wire formats, file names, and behaviors.
> This doc specifies exact inputs/outputs, filenames, schedules, error-handling, and implementation phases for an AI-orchestrated coder.
> Where required, unique code quotes from the legacy scripts are included to lock compatibility.

---

1. Scope & Modules

rateloader must reproduce, not import, legacy behavior of:

1. ExportMailer (email CSV export — legacy hotellabexport.py)

2. PriceExport (push JSON prices to API — legacy main_priceexport.py)

3. RateLoader (HTTP server to receive JSON prices and write to Oracle — legacy main.py)

All errors must be caught, logged, and must not crash the app.
All long tasks must run sequentially (no parallel exports).
CSV filenames must remain exactly as before and each run overwrites the previous file.

---

2. Absolute Backward Compatibility Anchors (with quotes)

2.1 ExportMailer (CSV → Email) — legacy hotellabexport.py

a) Query sources & INI keys

Default URLs and INI keys must be preserved:

DEFAULT_BLOCK_URL / DEFAULT_URL and section [settings] keys (must exist or be created):

> DEFAULT_BLOCK_URL = r"http://fidelio.pro/selectBlocks.sql"
> DEFAULT_URL = r"http://fidelio.pro/select.sql"
> INI keys: block-url, url, sendfrom, sendto, port, oracleconnectstring, mailserver, pwd, ssl, nls_lang, file_encoding.

INI path and autogeneration behavior mirrors the legacy file:

> PATH = "settings.ini" …
> “…settings.ini configuration file not found. Creating a new one with default settings.” (behavioral message)

b) NLS_LANG must be applied to process environment before Oracle connect

> os.environ["NLS_LANG"] = nls_lang

c) Hotel key source (derives filenames & email subjects)

Hotel key is read from settings.ini [settings] hotel_key (fallback to default HOTEL123 if missing).

**Rationale (2025-11-27)**: Implementation uses config.get("hotel_key") for simplicity and configurability across environments. Legacy query validated but superseded by config-driven approach during testing. Code confirmed functional with static key.

Legacy reference (superseded):

> kq = "select wuss_value from wuss where upper(wuss_name) = upper('UDEF_Hotellabkey')"

d) CSV output format (one column per line) and chunking

The export writes only the first column of each row, one line per row:

> for item in chunk: f.write(f"{item[0]}\n")

(Legacy attempts SELECT COUNT(\*) FROM (...); if it fails or is heavy, our code must still proceed gracefully and log.)

e) Exact filenames (MUST KEEP)

Main export filename and subject are the hotel key (no timestamps):

> r = f"{key}" → file f"{r}{EXTENSION}" (i.e., <KEY>.csv)

Blocks export filename must be BLOCKS-<KEY>.csv:

> rb = f"BLOCKS-{key}" → file f"{rb}{EXTENSION}" (i.e., BLOCKS-<KEY>.csv)

f) Email send signature (compatibility surface)

Behavior-compatible with the legacy call (parameter order & semantics):

> mysendmail.send_mail(sendfrom, sendto, r, r, f"{r}{EXTENSION}", pwd, mailserver, port, int(ssl))
> mysendmail.send_mail(sendfrom, sendto, rb, rb, f"{rb}{EXTENSION}", pwd, mailserver, port, int(ssl))

> rateloader requirement: send with the same headers/attachment naming and equivalent semantics (From/To/Subject/attachment filename) as the legacy call above.

---

2.2 PriceExport (JSON → API) — legacy main_priceexport.py

a) JSON body source (Oracle function call per day)

For each day in range, you must fetch JSON from Oracle using:

> q = f"select m_hlpricejson('{start_date.strftime('%d.%m.%Y')}') from dual"

b) HTTP headers (MUST KEEP)

> headers = {"Content-Type": "application/json", "X-HL-Token": token}

c) Range iteration semantics

Legacy iterates day-by-day for months months ahead starting from today (see loop building start_date, end_date, and oneday):

> “while start_date <= end_date: … start_date += oneday” with relativedelta(months=+months)

> rateloader requirement: identical daily stepping and the same JSON body wire format returned by m_hlpricejson.

---

2.3 RateLoader (HTTP ingest → Oracle) — legacy main.py

a) Incoming JSON format and PL/SQL composition ("double putrate" rule)

Build the PL/SQL block exactly as the legacy does, including the double m_rlputrate when guests == 1, and then children rates:

> if price.get("guests") == 1:

    query += (
        f"m_rlputrate(to_date('{price['date']}', 'yyyy-mm-dd'), "
        f"'{price['category']}', '{price['ratecode']}', {price['price']}, 0);\n"
    )

query += (
f"m_rlputrate(to_date('{price['date']}', 'yyyy-mm-dd'), "
f"'{price['category']}', '{price['ratecode']}', {price['price']}, {price['guests']});\n"
)
for child_rate in price.get("children_rates", []):
query += (
f"m_rladdchild(to_date('{price['date']}', 'yyyy-mm-dd'), "
f"'{price['category']}', '{price['ratecode']}', {child_rate['price']}, "
f"'{child_rate['age_category']}');\n"
)

b) Server behavior and response

Legacy server reads JSON from body, submits work to a thread pool, and responds immediately:

> self.executor.submit(process_prices, data, self.oracle_connector) …
> response content: "POST request for {self.path}"

> rateloader requirement: keep the same request structure and succeed for legacy senders; returning 200 with a plain success string is acceptable for wire compatibility (or 202 queued if you control clients; if not, stick to the legacy 200 + text).

c) Default INI seed for Oracle connect

> config["settings"] = {"oracleconnectstring": "v8live/live@v8"} (if missing)

---

3. Scheduling & Concurrency (New Requirements)

Fixed times: 08:00, 12:00, 15:00, 18:00, 20:00 (local time).

At each time enqueue two tasks in order:

1. ExportMailer (CSV → Email),

2. PriceExport (JSON → API).

Single worker (FIFO queue) to guarantee strict sequential execution (no overlap).

Overwrite CSV files every run (fixed names KEY.csv, BLOCKS-KEY.csv).

Any failure must be logged and must not crash the app; next tasks continue.

---

4. Configuration (single settings.ini)

Keep legacy keys & defaults from hotellabexport.py and main.py, add the API keys from main_priceexport.py.

Required keys (section [settings]):

[settings]

# Oracle (shared)

oracleconnectstring = v8live/live@v8
nls_lang = AMERICAN_AMERICA.CL8MSWIN1251
file_encoding = cp1251

# ExportMailer (legacy)

url = http://fidelio.pro/select.sql
block-url = http://fidelio.pro/selectBlocks.sql
sendfrom = radio-moscow@hotellab.io
sendto = someone@example.com
mailserver = mail.hotellab.io
port = 25
pwd = **\*\*\***
ssl = 0

# PriceExport (legacy)

api_url = https://app.hotellab.io/privat/api/integration/1c/set-pms-prices/5
token = 1111
months = 12

# RateLoader HTTP

server_port = 8000

# Scheduler (fixed hours)

schedule_hours = 8,12,15,18,20

If settings.ini is missing or lacks keys, auto-create/append defaults exactly like legacy behavior.

---

5. Logging

Create log/ folder next to the executable.

Separate rotating files:

hotellabexport.log (CSV/email flow, keep legacy name)

hotellabpriceexport.log (API export flow, keep legacy name)

rateloader.log (HTTP server + scheduler + queue, keep legacy trend)

All exceptions must be caught and logged (stacktrace), the app must keep running.

---

6. Error Handling Rules (must not crash)

Network errors (downloading SQL URLs, SMTP send, API POST): log error, skip/subtask fail, continue queue.

Oracle errors (connect/execute): log error, subtask fail, continue queue.

JSON parse errors (HTTP ingest): return 400 + log; must not crash server.

File I/O (write CSV): log error; leave partials if failure occurs mid-write (safe write is recommended: temp + replace).

---

7. Runtime Model

Main thread: GUI (tkinter).

HTTP thread: runs HTTPServer.serve_forever() on server_port.

Scheduler thread: wakes every 30s, enqueues tasks at the scheduled times.

Worker thread: single-threaded executor to pop tasks FIFO and run them sequentially.

---

8. Implementation Phases (for the coder)

> Each phase is self-contained and testable.
> The orchestrator should assign them in order.

Phase 1 — Project skeleton & config

Create folders: core/, features/, gui/, log/.

Implement core/config.py:

Read/write settings.ini.

On first run or missing keys: create defaults as legacy (see §4).

Implement core/logger.py with three loggers & rotating handlers (legacy filenames in §5).
Acceptance: Running the app creates settings.ini and log files.

Phase 2 — Oracle wrapper

Implement core/oracle.py:

set_nls_lang(nls_lang) → os.environ["NLS_LANG"] = nls_lang (see quote).

connect(connect_string) → returns connection.

execute_scalar(sql) → returns first value of first row.

stream_query(sql, arraysize=1000) → generator yielding row batches. Acceptance: Test simple select 1 from dual.

Phase 3 — ExportMailer (CSV → Email)

Implement features/export_mailer.py:

Download url and block-url (on error: log + reuse previous local copy).

Get key using the exact hotel-key query:

> select wuss_value from wuss where upper(wuss_name) = upper('UDEF_Hotellabkey')

Run both SQLs:

Write only the first column, one line per row, overwrite files named <KEY>.csv and BLOCKS-<KEY>.csv (no timestamps), using file_encoding (default cp1251).

Implement core/mailer.py to send email attachment with equivalent signature:

> send_mail(sendfrom, sendto, subject, body, attachment_path, pwd, mailserver, port, ssl) (parameters in legacy order).
> Acceptance: On a test DB & SMTP, it overwrites both CSV files and sends the emails.

Phase 4 — PriceExport (JSON → API)

Implement features/price_export.py:

For each day from today to today + months (months from INI), get JSON via:

> select m_hlpricejson('dd.mm.yyyy') from dual (exact format)

POST to api_url with headers:

> {"Content-Type": "application/json", "X-HL-Token": token}

On any HTTP or JSON error: log and continue next day.
Acceptance: With a mock API, requests are sent with matching headers/body; failures logged, loop continues.

Phase 5 — RateLoader logic and HTTP server

Implement features/rate_loader_logic.py:

build_plsql(prices) replicates the legacy double-putrate rule and children-rates block exactly as quoted in §2.3.

Implement core/http_server.py:

GET /ping → {"status":"ok"}

GET /status → queue status summary

POST /load → parse JSON; on success, enqueue work and reply immediately (200 "POST request for /load" for full legacy feel).
Acceptance: Send a sample legacy JSON; verify built PL/SQL matches the quoted pattern and executes against Oracle.

Phase 6 — Sequential task queue & scheduler

Implement core/task_queue.py: single worker (Thread), FIFO.

Implement core/scheduler.py: read schedule_hours (default: 8,12,15,18,20), wake every 30s; on each scheduled hour:00, enqueue ExportMailer then PriceExport.
Acceptance: Simulate time (or temporarily adjust to minute-level) to confirm strict order and no overlap.

Phase 7 — GUI (tkinter)

gui/app.py with tabs:

Settings: edit settings.ini keys, Save & Apply.

Export Mailer: “Run now” puts job in queue; show live status/progress.

Price Export: “Run now” → queue; show status.

Rate Loader: Start/Stop server; show GET /status.

Logs: tail last N lines of each log file.
Acceptance: GUI does not block; manual runs enqueue tasks; server controllable; logs visible.

---

9. Acceptance Criteria Checklist

File names: exactly <KEY>.csv and BLOCKS-<KEY>.csv, overwritten on each run (no timestamps).

CSV content: one column per line from query’s first column (identical to legacy behavior).

Email send: parameters and attachment filename behavior equivalent to legacy signature.

API export: JSON body from m_hlpricejson('dd.mm.yyyy') and headers with X-HL-Token.

HTTP ingest: accepts legacy JSON and builds PL/SQL with double m_rlputrate when guests == 1, then m_rladdchild for children.

Scheduler: fires at 08:00, 12:00, 15:00, 18:00, 20:00, enqueuing ExportMailer then PriceExport.

Sequential execution: single worker; never runs exports in parallel.

Resilience: any exception is logged; app stays alive.

INI compatibility: missing settings.ini or keys are auto-created with defaults consistent with legacy files.

---

10. Notes for the Coder

Use Python 3.8 (32-bit) with cx_Oracle 7.3 to match Oracle 11g stacks.

Keep logs named as legacy (file names in §5).

When downloading SQL files fails, log and reuse old local files (legacy behavior message exists).

Handle file encoding per INI (file_encoding, default cp1251).

For the HTTP server’s 200-OK string response on POST, you may mirror:

> "POST request for {self.path}" (legacy text).

---

Appendix — Legacy Quotes Index

hotellabexport.py (INI keys, URLs, NLS_LANG, hotel key, filenames, email call, 1-column CSV):

main_priceexport.py (m_hlpricejson, headers, daily loop shape):

main.py (double m_rlputrate + m_rladdchild, POST immediate response string, default INI seed):

---

End of PRD
