# KhataLedger — a digital udhaar khata (OkCredit-style app)

A complete credit/ledger book web app built with **core PHP (no framework)** and **MySQL**,
styled with a small self-written CSS system (no framework, no CDN dependency), for shopkeepers to track credit given to customers ("udhaar")
and payments received — with **SMS + Email notifications**, **OTP-verified registration /
password reset**, and a **mobile-first, app-like interface**.

## Features

### Core ledger
- Dashboard: total amount to collect, today's net entries, customer list with balances
- Add / search customers (name, phone, email, address, notes) via an in-page modal
- Per-customer ledger: "You Gave" (credit) and "You Got" (payment) entries, running balance
- Delete individual entries
- Reports page with date-range filtering and totals
- CSRF protection on all forms, prepared statements everywhere (PDO)

### Frontend
- **No CSS/JS framework** — `assets/css/style.css` is a single, self-written stylesheet
  (layout grid, buttons, forms, cards, tables, modal, navbar — all plain CSS) and
  `assets/js/app.js` is plain vanilla JS for the mobile nav toggle, dismissible alerts, the
  "Add customer" modal, and OTP input boxes. Nothing is pulled from a CDN except optional
  Google Fonts, so the app's colors and layout can never silently fail to load.
  Ledger-book theme: deep green/gold palette, ruled-paper background, red/green "ink" for
  debit/credit.
- Fully responsive: a collapsible top navbar on desktop/tablet, and a **fixed bottom tab
  bar with a floating "+" button** on mobile — the app feels native on a phone
- OTP entry uses auto-advancing digit boxes; alerts auto-dismiss; "Add customer" opens in
  a modal instead of navigating away

### SMS + Email
- **SMS gateway**: integrates with a WhatsApp/SMS HTTP API in the `apiwa.adaptwind.in` style
  (`GET {url}?api_key=...&sender=...&number=...&message=...&footer=...`)
- **SMTP email**: a small dependency-free SMTP client (`includes/SmtpMailer.php`) — no
  Composer/PHPMailer needed. Supports STARTTLS (587) and SSL (465) with AUTH LOGIN.
- **Two levels of settings, deliberately separated so one merchant can never affect another:**
  - **Shared/company settings** (`settings` table) — the SMS API URL, footer text, default
    sender ID, and the entire SMTP configuration. These are **admin-only** now (see Admin
    Panel below) — a regular merchant can view but not edit them, specifically so one
    merchant editing a shared field can't break delivery for everyone else.
  - **Per-merchant settings** (on the `users` table) — each merchant has their **own**
    `sms_api_key` (their personal apiwa.adaptwind.in account key, entered on their own
    Settings page, with a link to register at apiwa.adaptwind.in if they don't have one
    yet) plus their own `whatsapp_sender` and business email. If a merchant hasn't set their
    own key, sending falls back to the shared company key so nothing breaks immediately.
- **OTP-based registration**: sign-up collects your details, then sends a 6-digit OTP by SMS
  (and email, if provided) — always via the shared/admin settings — before the account is created.
- **OTP-based forgot password**: enter your phone → OTP (shared settings) → verify → set a
  new password.
- **New customer alert**: adding a customer sends them a welcome SMS/email using the
  merchant's **own** SMS key + WhatsApp sender / email (with company SMTP as the technical
  relay for email — see note below).
- **Debit/credit alerts**: every credit ("you gave") or payment ("you got") entry sends the
  customer an SMS/email the same way, from the merchant's own identity.
- **Notification log**: every SMS/email attempt (sent or failed, with the gateway's raw
  response **and the exact SMS hit URL that was called**, api_key masked) is recorded in
  `notification_logs` and shown both per-customer and on Settings — useful for debugging
  exactly what was sent to the gateway without exposing the full key.

> **A note on "sending from the shop's own email":** raw SMTP can't truly spoof an arbitrary
> `From:` address without that domain's SPF/DKIM authorizing your server (most providers will
> silently drop or spam-flag it otherwise). So customer emails are relayed through the
> authenticated shared SMTP account, but the **display name is set to the shop's business
> name** and **Reply-To is set to the merchant's own email**, so replies land in the
> merchant's inbox. SMS has no such restriction — the gateway's `sender` and `api_key`
> parameters are the merchant's own, directly.

### Admin panel (new)
A completely separate panel and login (`admin_login.php`) for managing the whole
installation — not accessible from, or linked to, the merchant app:
- **First-time setup**: open `/admin_setup.php` once after a fresh install to create the
  first admin account (username + password, hashed with PHP's own `password_hash()`). It
  only works while the `admins` table is empty, then locks itself permanently — no default
  password ships with this project.
- **Dashboard** (`admin_dashboard.php`) — platform-wide totals: merchants, customers,
  transactions, total outstanding balance, and how many merchants are using their own SMS key.
- **Merchants** (`admin_users.php` → `admin_user_detail.php`) — search/browse every merchant,
  drill into one to see their notification identity, whether they've set their own SMS key,
  their credit/debit API token (with a **revoke** button), their whitelisted IPs (with
  **remove** buttons), and their full customer list with balances — which drills further into
  `admin_customer_detail.php` for a read-only view of that customer's transaction history.
- **Global Settings** (`admin_settings.php`) — the shared SMS gateway and SMTP configuration
  described above, plus the statement portal's app base URL. This is the *only* place these
  are editable.

### Customer statement portal (new)
Every SMS/email a customer receives (welcome message, credit/debit alerts) now includes a
personal statement link. It's a self-service, OTP-protected mini-site — no login needed:

1. Customer opens the link (`statement.php?c=<token>` — the token is a random 40-character
   ID unique to that customer, not their database ID, so it can't be guessed or enumerated).
2. They enter their mobile number. It must match the phone number the merchant has on file
   for them — this is checked before anything else is revealed.
3. On a match, a 6-digit OTP is sent to that number (and their email, if on file). They enter
   it on `statement_verify.php` to confirm.
4. Once verified (session valid for 30 minutes), `statement.php` shows the merchant's name,
   the customer's own details, and the full transaction history — credit ("You Gave") in red,
   payments ("You Got") in green, with a running balance column.
5. A **Download PDF** button (`statement_pdf.php`) generates a real `.pdf` file of the
   statement — built by `includes/SimplePdf.php`, a small hand-written PDF generator with no
   external library (TCPDF/mPDF/dompdf) required. PDF download re-checks the same
   OTP-verified session, so the file can't be pulled just by guessing the URL.

Where the link points is controlled by Settings → "Statement link URL" (`app_base_url`) —
leave it blank to auto-detect from the request, or set it explicitly for a fixed domain.

### Timezone
The whole app runs on **Asia/Kolkata (IST, +05:30)** — set in `config/database.php` for PHP
(`date_default_timezone_set`) and for the MySQL session (`SET time_zone = '+05:30'`) right
after connecting. Both clocks are pinned to the same offset so an OTP's expiry (computed in
PHP) is always compared against the same "now" as `created_at`/`NOW()` (computed by MySQL) —
they can no longer drift apart, which previously could make a freshly-created OTP look
already expired if the app server and DB server disagreed on the time zone. `verifyOtp()`
also compares expiry against a PHP-supplied timestamp rather than MySQL's `NOW()`, as a second
safeguard. If you need a different timezone, change both places in `config/database.php`.

### Credit / Debit API (new)
An HTTP endpoint (`api/api.php`) lets an external system — a POS, e-commerce store, or
accounting tool — record a credit or payment entry with one request:

```
GET /api/api.php?apikey=...&cusmob=9876543210&cusemail=demo@gmail.com&type=credit&amount=500&description=Groceries
```
(POST works identically, reading the same fields from the form body instead of the query string.)

Three checks run in order, all enforced server-side in `api/api.php`:
1. **`apikey`** must match the merchant's token, generated on Settings → "API access".
2. **Caller IP must be whitelisted.** A merchant adds an IP on Settings → "Whitelisted IPs" —
   adding one requires entering a 6-digit OTP sent to their own registered phone first, so an
   attacker with just a stolen API key still can't add their own IP without also controlling
   the merchant's phone. If a merchant hasn't whitelisted any IP yet, the API rejects every
   request outright.
3. **Customer match** — `cusmob` must match an existing customer's phone number under that
   merchant; if that customer also has an email on file, `cusemail` must match it too.

On success, the customer gets the exact same SMS/email alert (with their statement link) as a
manual entry made in the app — nothing extra to configure. Every call (accepted or rejected) is
logged to `api_request_logs` and shown on the Settings page for that merchant.

Full parameter reference, example requests/responses, and status codes are in the in-app
**API Documentation** page (`api_docs.php`, linked from Settings) — it also fills in your own
real API token in the example URL so you can copy-paste it directly.

### Payments & reminders (new)
- **Cashfree payment links** — from a customer's page, "Send payment link" creates a hosted
  Cashfree checkout page for a given amount and sends it by SMS + email. Cashfree credentials
  (App ID / secret key, test or live mode) are **per-merchant**, set on each merchant's own
  Settings page — not a shared admin setting. This matters because a Cashfree App ID/secret
  pair is tied to one specific bank settlement account: if it were shared, every merchant's
  customer payments would land in the same single account instead of that merchant's own.
  Every link generated is logged to `payment_links` (amount, URL, status), visible on the
  customer's page.
- **Send a reminder right now** — a one-click button that sends an SMS + email reminder of the
  customer's current outstanding balance (including their latest active payment link, if one
  exists, so they can pay immediately).
- **Scheduled, recurring reminders** — pick a start date on a customer's page and a reminder
  goes out that day, then automatically every day after via a small cron job
  (`cron/send_reminders.php`) — until the balance is settled (auto-detected and turned off) or
  the merchant cancels it. Set this up once after install:
  ```bash
  # real crontab (no secret needed) — e.g. once a day at 10am:
  0 10 * * * php /path/to/khatabook/cron/send_reminders.php
  ```
  or, on hosts that only offer a URL-based scheduler, set a secret on Admin → Global Settings →
  "Reminder cron secret" and call `https://yourapp.com/cron/send_reminders.php?key=YOUR_SECRET`
  instead. Without a secret set, the URL form is refused entirely (403) — only a real
  command-line run works, which is the safer default.
- **UPI payment details (new)** — on their own Settings page, a merchant can fill in any or all
  of: a UPI number (mobile-linked), a UPI ID/VPA, and an uploaded QR code image — each with its
  own "account holder / banking name" label. Whichever of these are filled in gets appended to
  **every** reminder (both "Send Reminder Now" and scheduled/recurring ones) automatically:
  the SMS lists the UPI number/ID as plain text, and the email additionally embeds the QR code
  image inline, so the customer can pay with any UPI app without needing a payment gateway at
  all. QR uploads are validated (PNG/JPG/WEBP, 2MB max, MIME-sniffed not just extension-checked)
  and stored under `/uploads/qr/`, which ships with an `.htaccess` disabling script execution
  in that folder as defense-in-depth.

## Requirements

- PHP 8.0+ with the `curl` extension enabled (falls back to `file_get_contents` if not available)
- MySQL 5.7+ / MariaDB
- A web server (Apache/Nginx) or PHP's built-in server
- An SMTP account (Gmail, Zoho, your host's mail server, etc.) if you want email alerts
- Your SMS/WhatsApp gateway's real `api_key` and `sender` value
- The `uploads/qr/` folder must be writable by the web server user (for merchants uploading a
  UPI QR code image) — e.g. `chmod 755 uploads/qr` on most shared hosting setups
- (Optional) internet access in the browser for the Google Fonts link — if unavailable, the
    app falls back to system fonts automatically, no layout breaks either way

## Setup

1. **Create the database**

   Fresh install:
   ```bash
   mysql -u root -p < sql/schema.sql
   ```

   Upgrading an existing install:
   ```bash
   mysql -u root -p < sql/migration_v2.sql   # adds SMS/email/OTP tables (skip if already applied)
   mysql -u root -p < sql/migration_v3.sql   # adds per-merchant whatsapp_sender column
   mysql -u root -p < sql/migration_v4.sql   # adds the customer statement portal (tokens, app_base_url)
   mysql -u root -p < sql/migration_v5.sql   # adds the credit/debit API (token, IP whitelist, logs)
   mysql -u root -p < sql/migration_v6.sql   # adds per-merchant SMS API key + admin panel table
   mysql -u root -p < sql/migration_v7.sql   # adds the SMS hit-URL column to notification_logs
   mysql -u root -p < sql/migration_v8.sql   # adds Cashfree payment links + scheduled reminders
   mysql -u root -p < sql/migration_v9.sql   # moves Cashfree credentials to per-merchant (fixes a shared-account bug in v8)
   mysql -u root -p < sql/migration_v10.sql  # adds per-merchant UPI payment details (number/ID/QR)
   ```

2. **Configure the DB connection** — edit `config/database.php`:
   ```php
   define('DB_HOST', 'localhost');
   define('DB_NAME', 'khatabook');
   define('DB_USER', 'root');
   define('DB_PASS', '');
   ```

3. **Run it**
   ```bash
   cd khatabook
   php -S localhost:8000
   ```
   Visit `http://localhost:8000`, or point Apache/Nginx at the `khatabook/` folder.

4. **Create the admin account** — open `/admin_setup.php` once (works only while no admin
   account exists yet) to create your own admin username/password. Log in at
   `/admin_login.php`, open **Global Settings**, and configure:
   - SMS: API URL (defaults to `https://apiwa.adaptwind.in/send-message`), a shared API key,
     default sender ID, optional footer / country code — used as the fallback for any
     merchant who hasn't set up their own key yet, and always used for OTPs.
   - SMTP: host, port, encryption (STARTTLS/SSL/none), username, password, from name/email.
   - Toggle "Enable SMS sending" / "Enable email sending" and use the **Send test** buttons.

5. **Register** a shop account at the normal merchant app (`/register.php`) — you'll be asked
   for a 6-digit OTP sent to your phone (and email, if given) before the account is created.

6. **Set your shop's own identity** — in the merchant Settings page, fill in "Your WhatsApp /
   SMS sender number" and "Your business email," and — importantly — **your own SMS API key**
   from your own apiwa.adaptwind.in account (there's a registration link right there). This
   keeps your message delivery independent from every other merchant's. If you want to accept
   online payments, add **your own Cashfree App ID and secret key** in the same Settings page
   (from your Cashfree merchant dashboard) — this is per-merchant too, for the same reason:
   payments need to land in your bank account, not anyone else's.

7. Start adding customers — each one gets a welcome SMS/email, and every ledger entry you
   record for them triggers an alert, all sent from your own identity.

## Project structure

```
khatabook/
├── config/
│   └── database.php                # DB connection (PDO), sets Asia/Kolkata timezone
├── includes/
│   ├── auth.php                      # merchant login guard — loads the full merchant record
│   ├── admin_auth.php                 # admin login guard (fully separate session/table)
│   ├── functions.php                  # helpers: money formatting, balances, CSRF, flash messages
│   ├── notify.php                     # settings storage, sendSms(), sendEmailMessage(), OTP, event notifications
│   ├── SmtpMailer.php                  # dependency-free raw-socket SMTP client
│   ├── SimplePdf.php                    # dependency-free hand-written PDF writer (statement PDFs)
│   ├── Cashfree.php                      # Cashfree Payment Links API client
│   ├── header.php / footer.php           # merchant navbar / mobile bottom tab bar (no framework)
│   └── admin_header.php / admin_footer.php  # separate, distinctly-branded admin navbar
├── assets/
│   ├── css/style.css                # the entire design system: grid, components, theme — one file
│   └── js/app.js                     # vanilla JS: nav toggle, alerts, modal, OTP boxes
├── sql/
│   ├── schema.sql                   # full schema (fresh installs)
│   └── migration_v2.sql .. v9.sql    # incremental ALTERs for upgrading an older install
├── index.php                          # redirects to login or dashboard
├── register.php                       # step 1: collect details (+ optional WhatsApp sender), send OTP
├── verify_otp.php                      # step 2: verify OTP (shared by register + forgot password)
├── forgot_password.php                  # request OTP for password reset
├── reset_password.php                    # set new password after OTP verification
├── login.php / logout.php
├── dashboard.php                          # summary + customer list
├── customers.php                          # search + add customers (modal, sends welcome SMS/email)
├── customer_detail.php                     # per-customer ledger, add/delete entries, notification history
├── reports.php                              # date-range reporting
├── settings.php                              # per-merchant SMS key, Cashfree, sender identity, API access, logs
├── statement.php                              # public customer statement portal (phone step + view)
├── statement_verify.php                        # public: OTP entry step for the statement portal
├── statement_pdf.php                            # public: generates the downloadable PDF statement
├── api/
│   └── api.php                                   # public: the credit/debit HTTP API endpoint
├── api_docs.php                                   # in-app API documentation (linked from Settings)
├── admin_setup.php                                 # one-time admin account creation
├── admin_login.php / admin_logout.php
├── admin_dashboard.php                              # platform-wide overview stats
├── admin_users.php                                   # search/browse all merchants
├── admin_user_detail.php                              # one merchant's profile, API/SMS status, customers
├── admin_customer_detail.php                           # read-only view of one customer's transactions
├── admin_settings.php                                   # the shared/global SMS + SMTP + app URL settings
├── cron/
│   └── send_reminders.php                                # daily job: sends due/recurring payment reminders
├── uploads/
│   └── qr/                                                 # merchant-uploaded UPI QR code images (+ .htaccess)
└── README.md
```

## Data model

- **users** — merchant/shop (business_name, owner_name, phone, email, `whatsapp_sender`,
  `sms_api_key` and `cashfree_*` — each merchant's own apiwa.adaptwind.in key and Cashfree
  credentials — plus `upi_number`/`upi_id`/`upi_qr_path` and their matching `*_bank_name`
  fields for UPI-based collection, `api_token`, password hash)
- **customers** — belong to a user; stores `email` and a random `public_token` (statement link)
- **transactions** — `type` is `credit` (you gave — they owe you) or `payment` (they paid you
  back). Balance = `SUM(credit) - SUM(payment)`.
- **settings** — key/value store for the shared/global SMS gateway + SMTP credentials
  (admin-only — see Admin Panel)
- **otps** — one-time codes for registration, password reset, statement verification, and IP
  whitelisting (10-minute expiry, single use)
- **notification_logs** — audit trail of every SMS/email attempt: channel, purpose, recipient,
  status, raw gateway/SMTP response, and `request_url` (the exact SMS gateway URL hit, with
  `api_key` masked)
- **api_whitelist_ips** — IP addresses a merchant has OTP-approved to call the API
- **api_request_logs** — audit trail of every call to `api/api.php` (accepted or rejected)
- **admins** — separate table for admin panel accounts (its own login, no default row shipped
  — created via `/admin_setup.php` on first run)
- **payment_links** — every Cashfree payment link generated for a customer (amount, hosted
  checkout URL, status)
- **payment_reminders** — one active recurring-reminder schedule per customer (`remind_date`,
  `is_active`, `last_sent_date`), consumed daily by `cron/send_reminders.php`

## Notes & possible extensions

- The SMTP client implements the common case (STARTTLS/SSL + AUTH LOGIN); providers requiring
  OAuth2 (some enterprise Gmail/Outlook setups) would need an access-token flow instead.
- `SimplePdf` only supports the standard Latin/WinAnsi character set (it uses PDF's built-in
  Helvetica font, so no font file to embed) — names/notes in non-Latin scripts (e.g. Devanagari)
  will be transliterated or dropped. Supporting those would need an embedded Unicode font.
- The API accepts both GET and POST for convenience (matching how it's commonly integrated),
  but since it changes data, POST is the better choice where your integration allows it — GET
  URLs (with the API key in them) tend to end up logged in web server access logs, proxies,
  and browser history along the way. There's currently no rate limiting on `api/api.php`
  beyond the IP whitelist; add one at the web server/reverse-proxy level if you expect
  high-volume or public-facing traffic.
- **Admin panel security**: `admin_setup.php` locks itself after the first admin account is
  created, but consider deleting the file (or restricting it at the web server level) once
  you're done, and put the whole admin panel behind an extra layer (HTTP Basic Auth, IP
  allowlist, or a VPN) if it's reachable from the public internet — it currently only relies
  on the admin username/password like any other login.
- Other ideas: CSV statement export, WhatsApp-native message templates, staff accounts per
  shop, photo attachments per entry.

## Troubleshooting: "SMS works but email doesn't"

If you hit exactly this symptom, it was very likely a real bug that's now fixed:
`includes/SmtpMailer.php` was opening its socket connection without an explicit transport
scheme for STARTTLS (port 587, the most common setup) — it called
`stream_socket_client("smtp.host.com:587", ...)` instead of
`stream_socket_client("tcp://smtp.host.com:587", ...)`. On several PHP/OS combinations, that
means the connection either fails outright or behaves unpredictably, while SMS delivery (a
completely separate HTTP/cURL call) is unaffected — which is exactly why one channel worked
and the other silently didn't. This is fixed in the current `SmtpMailer.php`.

If email still isn't going out after updating:
1. Check the **"Recent notification activity"** log (Settings, or a customer's page) — every
   attempt logs the real reason: SMTP disabled, no host configured, or the exact SMTP error
   returned by your provider (auth failure, wrong port, TLS handshake failure, etc).
2. Confirm on **Admin → Global Settings** that "Enable email sending" is checked, and that
   host/port/username/password/from-email are all filled in correctly.
3. Use the **"Send test email"** button (Settings or Admin → Global Settings) to get an
   immediate pass/fail without needing a real customer interaction.
4. Common provider gotchas: Gmail requires an **App Password** (not your normal password) once
   2FA is on; port 587 pairs with STARTTLS, port 465 pairs with SSL — mixing them up will fail
   the handshake.
5. If "From email" is left blank, `includes/notify.php` now falls back to the SMTP username
   automatically — many providers (Gmail, Outlook) otherwise reject or spam-flag mail whose
   envelope sender doesn't match the authenticated account.
