# Database Schema

**Last updated:** 2026-04-30
**Source:** Inferred from ORM models, controllers, install script, and raw query analysis.

---

## Connection

| Setting        | Value                  |
|----------------|------------------------|
| Driver         | `mysqli`               |
| Host           | `localhost`            |
| Database       | `host420804_db`        |
| User           | `host420804_db`        |
| Character set  | `utf8`                 |
| Table prefix   | _(none)_               |
| Persistent     | `FALSE`                |
| Config file    | `application/config/database.php` |

---

## Tables

### `user`

**Purpose:** Admin user accounts. Used for CMS login and authentication. Only admin-role users access the back-end panel.

**Model:** `application/models/user.php` → `User_Model extends ORM`
- `$table_name = 'user'`
- `$primary_val = 'username'` (allows lookup by username string in addition to numeric `id`)

**Columns:**

| Column          | Type           | Nullable | Notes                                                             |
|-----------------|----------------|----------|-------------------------------------------------------------------|
| `id`            | INT (PK, AI)   | NO       | Primary key, auto-increment (Kohana ORM default)                  |
| `role`          | VARCHAR        | NO       | Role name, e.g. `'sysadmin'`, `'admin'`. Stored as string.       |
| `username`      | VARCHAR(20)    | NO       | Unique login name. Validated: 3–20 chars, `[a-zA-Z0-9_.]`        |
| `email`         | VARCHAR(50)    | NO       | Unique email address. Validated: 5–50 chars.                      |
| `salt`          | VARCHAR        | NO       | MD5 random salt used when hashing the password.                   |
| `sha1_password` | VARCHAR(40)    | NO       | `SHA1(salt . plaintext_password)`                                 |
| `password_date` | DATETIME       | YES      | Timestamp of last password change. Format: `Y-m-d H:i:s`         |
| `is_active`     | TINYINT / BOOL | NO       | `1` = active account, `0` = disabled. Checked on login.           |
| `last_success`  | DATETIME       | YES      | Timestamp of last successful login. Updated on each login.        |
| `last_failed`   | DATETIME       | YES      | Timestamp of last failed login attempt. Updated on bad password.  |

**Relationships:** None declared in ORM. Standalone table.

**Key usages:**
- `application/controllers/admin/user.php` — login, logout, password change
- `application/controllers/install.php` — seed rows for `sysadmin` and `admin`
- `application/controllers/admin/force.php` — forced login (reads `username`, `last_success`, `last_failed`)

---

### `page`

**Purpose:** CMS content pages served on the public front-end. Each row is a named page (e.g. `o-firmie`, `kontakt`, `serwis`). Content is edited via the admin panel with a TinyMCE rich-text editor.

**Model:** `application/models/page.php` → `Page_Model extends ORM`
- `$table_names_plural = FALSE` → table name is `page` (not `pages`)
- Lookup by string `name` supported via `unique_key()`.

**Columns:**

| Column            | Type          | Nullable | Notes                                                                 |
|-------------------|---------------|----------|-----------------------------------------------------------------------|
| `id`              | INT (PK, AI)  | NO       | Primary key, auto-increment.                                          |
| `name`            | VARCHAR       | NO       | URL slug / unique identifier. e.g. `'o-firmie'`, `'kontakt'`. Unique.|
| `title`           | VARCHAR(95)   | YES      | HTML `<title>` tag value and displayed page title.                    |
| `header`          | VARCHAR(95)   | YES      | Heading shown inside the page `<h1>`. May differ from `title`.        |
| `content`         | MEDIUMTEXT    | YES      | Main HTML body. Edited via TinyMCE. Can be empty.                     |
| `meta_description`| TEXT          | YES      | `<meta name="description">` content.                                  |
| `meta_keywords`   | TEXT          | YES      | `<meta name="keywords">` content.                                     |
| `parent_id`       | INT (FK)      | YES      | Self-referential parent page ID. `NULL` = top-level page.             |

**Relationships:**
- Self-referential: `parent_id` references `page.id` (used by `categories` helper for hierarchical menu rendering).
- No ORM `$has_many`/`$belongs_to` declared — relationships are resolved manually in the `categories` helper (`application/helpers/categories.php`).

**Seed pages (from install script):**

| `name`                                        | `title`                                           |
|-----------------------------------------------|---------------------------------------------------|
| `o-firmie`                                    | O firmie                                          |
| `urzadzenia-biurowe-monochromatyczne`         | Urządzenia biurowe monochromatyczne               |
| `urzadzenia-biurowe-kolorowe`                 | Urządzenia biurowe kolorowe                       |
| `urzadzenia-uslugowe-monochromatyczne`        | Urządzenia usługowe monochromatyczne              |
| `urzadzenia-uslugowe-kolorowe`                | Urządzenia usługowe kolorowe                      |
| `urzadzenia-produkcyjne-monochromatyczne`     | Urządzenia produkcyjne monochromatyczne           |
| `urzadzenia-produkcyjne-kolorowe`             | Urządzenia produkcyjne kolorowe                   |
| `drukarki-monochromatyczne`                   | Drukarki monochromatyczne                         |
| `drukarki-kolorowe`                           | Drukarki kolorowe                                 |
| `powielacze-cyfrowe-riso-dlaczego-riso`       | Powielacze cyfrowe RISO - dlaczego RISO?          |
| `powielacze-cyfrowe-riso-urzadzenia`          | Powielacze cyfrowe RISO - urządzenia              |
| `plotery`                                     | Plotery                                           |
| `finansowanie`                                | Finansowanie                                      |
| `serwis`                                      | Serwis                                            |
| `uslugi`                                      | Usługi                                            |
| `kontakt`                                     | Kontakt                                           |
| `szybki-kontakt`                              | Szybki kontakt                                    |

**Key usages:**
- `application/controllers/admin/page.php` — edit page content (title, header, content, meta_*)
- `application/controllers/front/page.php` — public display; `show($name)` and `contact()` methods
- `application/helpers/categories.php` — renders hierarchical navigation menus using `id`, `name`, `title`, `parent_id`
- `application/views/front/page_show.php` — renders `header` and `content`
- `application/views/front/page_contact.php` — renders contact page `header` and `content`

---

### `gallery`

**Purpose:** Photo gallery albums. Each gallery has a name/slug and a collection of images. Sorted by creation date ascending.

**Model:** `application/models/gallery.php` → `Gallery_Model extends ORM`
- `$table_names_plural = FALSE` → table name is `gallery`
- `$has_many = array('gallery_images')` → links to `gallery_image` table via `gallery_id` FK
- `$sorting = array('created_at' => 'ASC')` → default sort order

**Columns:**

| Column       | Type          | Nullable | Notes                                                          |
|--------------|---------------|----------|----------------------------------------------------------------|
| `id`         | INT (PK, AI)  | NO       | Primary key, auto-increment.                                   |
| `name`       | VARCHAR       | NO       | URL slug / unique identifier. Used for lookup.                 |
| `title`      | VARCHAR       | YES      | Human-readable gallery name displayed in UI.                   |
| `created_at` | DATETIME      | YES      | Creation timestamp. Used for ASC sort order.                   |

> **Note:** Additional columns (e.g. `description`, `cover_image`) may exist but are not referenced in source code. The column set above is confirmed by code evidence.

**Relationships:**
- `has_many` → `gallery_image` via `gallery_image.gallery_id`

**Key usages:**
- `application/models/gallery.php` — ORM definition
- No admin controller found in the current codebase snapshot — gallery management may not yet be implemented or may exist outside the explored scope.

---

### `gallery_image`

**Purpose:** Individual images belonging to a gallery album. Sorted by `id` ascending (insertion order).

**Model:** `application/models/gallery_image.php` → `Gallery_Image_Model extends ORM`
- `$table_names_plural = FALSE` → table name is `gallery_image`
- `$belongs_to = array('gallery')` → foreign key `gallery_id` references `gallery.id`
- `$sorting = array('id' => 'ASC')`

**Columns:**

| Column       | Type          | Nullable | Notes                                                              |
|--------------|---------------|----------|--------------------------------------------------------------------|
| `id`         | INT (PK, AI)  | NO       | Primary key, auto-increment.                                       |
| `gallery_id` | INT (FK)      | NO       | Foreign key → `gallery.id`. Populated by Kohana ORM convention.   |
| `filename`   | VARCHAR       | YES      | Stored filename of the uploaded image (inferred from upload config).|
| `title`      | VARCHAR       | YES      | Optional image caption / alt text.                                 |

> **Note:** Column names for `gallery_image` (beyond `id` and `gallery_id`) are not directly referenced in any controller or view in the explored source. `filename` and `title` are inferred from typical Kohana gallery patterns and the upload config presence. Verify against live database.

**Relationships:**
- `belongs_to` → `gallery` via `gallery_id` → `gallery.id`

---

### `news`

**Purpose:** News/blog articles. Sorted by creation date descending (newest first). No admin controller was found in the explored source — may be partially implemented or planned.

**Model:** `application/models/news.php` → `News_Model extends ORM`
- `$table_names_plural = FALSE` → table name is `news`
- `$sorting = array('created_at' => 'DESC')`
- Unique key lookup by `name` slug supported.

**Columns:**

| Column            | Type          | Nullable | Notes                                                         |
|-------------------|---------------|----------|---------------------------------------------------------------|
| `id`              | INT (PK, AI)  | NO       | Primary key, auto-increment.                                  |
| `name`            | VARCHAR       | NO       | URL slug / unique identifier. Unique constraint expected.     |
| `title`           | VARCHAR       | YES      | Article headline.                                             |
| `content`         | MEDIUMTEXT    | YES      | Article body HTML.                                            |
| `created_at`      | DATETIME      | YES      | Publication/creation timestamp. Used for DESC sort.           |

> **Note:** Only `name` and `created_at` are confirmed by model code. `title` and `content` are inferred from structural similarity to the `page` table and common news patterns. Additional columns may exist. Verify against live database.

**Relationships:** None declared in ORM.

---

## ORM Conventions (Kohana 2.x)

These conventions apply to all tables above and govern how Kohana resolves columns automatically:

| Convention                  | Detail                                                                 |
|-----------------------------|------------------------------------------------------------------------|
| Primary key                 | `id` (INT, auto-increment) — default for all models                    |
| Timestamps                  | `created_at` and `updated_at` — auto-populated when present            |
| Foreign keys                | `{related_model}_id` — e.g. `gallery_id` in `gallery_image`           |
| `has_many` target table     | Uses the child model's `$table_name` and looks up `{parent}_id` FK    |
| `$table_names_plural = FALSE`| Overrides default pluralisation — table name matches model name exactly|

---

## Confidence Levels

| Table           | Confidence | Basis                                                    |
|-----------------|------------|----------------------------------------------------------|
| `user`          | HIGH       | All columns directly referenced in controllers/install   |
| `page`          | HIGH       | All columns directly referenced in controllers/views     |
| `gallery`       | MEDIUM     | `id`, `name`, `created_at` confirmed; other cols inferred|
| `gallery_image` | LOW-MEDIUM | Only `id` and `gallery_id` confirmed; rest inferred      |
| `news`          | LOW-MEDIUM | Only `id`, `name`, `created_at` confirmed; rest inferred |

> Columns marked as inferred should be verified against the live `host420804_db` MySQL database or a mysqldump before any schema migrations are written.