# SkyBase — Complete Setup & Hosting Guide

## What You're Deploying

SkyBase is a PHP + MySQL web application with two modules:

- **Product Database** — searchable/filterable table of all raw materials
- **Regulatory Module** — halal certification tracking, expiry alerts, kit alternatives

Both modules run from a single database and a single codebase.

---

## File Structure (All 21 Files)

```
SkyBase/
├── index.php                  ← Product database (main page)
├── import.php                 ← CSV importer (admin only)
├── regulatory.php             ← Regulatory dashboard
├── login.php                  ← Login page
├── logout.php                 ← Logout handler
├── admin.php                  ← Admin panel (user management)
├── install.php                ← Run ONCE to seed users, then delete
├── SETUP.md                   ← This guide
├── config/
│   ├── database.php           ← ⚠ Edit this with your DB credentials
│   └── auth.php               ← Session & auth helpers
├── api/
│   ├── products.php           ← Products list + search API
│   ├── product.php            ← Single product detail + smart alternatives
│   ├── options.php            ← Filter options, stats, autocomplete
│   ├── regulatory.php         ← Regulatory dashboard data API
│   └── certifications.php     ← Cert CRUD + halal sync API
├── includes/
│   └── csv_parser.php         ← WooCommerce CSV parser
├── assets/
│   ├── css/
│   │   ├── style.css          ← Main design system
│   │   └── regulatory.css     ← Regulatory module styles
│   ├── images/
│   │   ├── srg-logo.svg       ← SVG fallback logo
│   │   └── srg-logo.png       ← ⚠ Place your SRG logo PNG here
│   └── js/
│       ├── app.js             ← Core frontend logic
│       └── regulatory.js      ← Regulatory drawer + dashboard logic
└── sql/
    ├── schema.sql             ← Run FIRST — core tables
    ├── regulatory_schema.sql  ← Run SECOND — regulatory tables
    └── auth_schema.sql        ← Run THIRD — users table
```

---

## Option A — Local XAMPP Setup

### Prerequisites

- XAMPP installed with Apache + MySQL services running
- PHP 7.4 or higher (PHP 8.x preferred)
- All SkyBase files placed in: `C:\xampp\htdocs\SkyBase\`

---

### Step 1 — Create the Database

1. Open your browser → go to **http://localhost/phpmyadmin**
2. In the left panel, click **New**
3. Database name: `skybase`
4. Collation: `utf8mb4_unicode_ci`
5. Click **Create**

---

### Step 2 — Run the Core Schema

1. Click the `skybase` database in the left panel
2. Click the **Import** tab at the top
3. Click **Choose File** → select `sql/schema.sql`
4. Leave all settings as default
5. Click **Go**

You should see: *"Import has been successfully finished."*
Tables created: `suppliers`, `products`

---

### Step 3 — Run the Regulatory Schema

1. Still in phpMyAdmin with `skybase` selected
2. Click the **Import** tab again
3. Click **Choose File** → select `sql/regulatory_schema.sql`
4. Click **Go**

You should see: *"Import has been successfully finished."*
Tables added: `certifications`, `product_alternatives`
Columns added to `products`: `halal_status`, `halal_body`, `halal_cert_no`, `halal_expiry`

> **MySQL 5.7 note:** If you see an error about `IF NOT EXISTS` on `ALTER TABLE`, open `regulatory_schema.sql` in a text editor and remove the `IF NOT EXISTS` from each `ADD COLUMN` line. Then re-run.

---

### Step 4 — Configure the Database Connection

Open `config/database.php` and set:

```php
define('DB_HOST', 'localhost');
define('DB_NAME', 'skybase');
define('DB_USER', 'root');   // XAMPP default
define('DB_PASS', '');       // XAMPP default is empty
```

Save the file.

---

### Step 5 — Run the Auth Schema

1. phpMyAdmin → `skybase` database → **Import**
2. Choose `sql/auth_schema.sql` → **Go**

---

### Step 6 — Seed Default Users

Visit: **http://localhost/SkyBase/install.php?key=skybase-install-2024**

This creates two accounts:

| Username | Password | Role  |
|----------|----------|-------|
| admin    | admin    | Admin |
| user     | user     | User  |

> **Delete `install.php` from your server after running it.** It contains a key that anyone with the URL can use.

---

### Step 7 — Place the SRG Logo

Copy your `srg-logo.png` file to:
`C:\xampp\htdocs\SkyBase\assets\images\srg-logo.png`

The SVG fallback (`srg-logo.svg`) is already in place and will be used automatically if the PNG is not found.

---

### Step 8 — Import Your Product CSV

1. Open: **http://localhost/SkyBase/login.php** → sign in as `admin`
2. Navigate to **Import CSV** in the sidebar (admin only)
3. Click **Choose File** → select your WooCommerce CSV export
4. Click **Start Import**
5. Wait for the progress bar to complete (8,000+ rows takes 30–90 seconds)

---

### Step 9 — Open SkyBase

Visit: **http://localhost/SkyBase/login.php**

- Sign in with `admin` / `admin` (or `user` / `user`)
- Product Database loads after login
- Admin panel: **http://localhost/SkyBase/admin.php** (admin only)
- Click **Regulatory** in the left sidebar to open the regulatory dashboard

---

## Option B — cPanel Shared Hosting Deployment

### Prerequisites

- cPanel hosting with PHP 7.4+ and MySQL 5.7+
- Access to phpMyAdmin via cPanel
- FTP client or cPanel File Manager

---

### Step 1 — Upload Files to the Server

**Option 1 — via cPanel File Manager:**
1. Zip your entire `SkyBase/` folder on your computer
2. Log in to cPanel → open **File Manager**
3. Navigate to `public_html/`
4. Click **Upload** → upload the zip file
5. Once uploaded, right-click it → **Extract**
6. This creates `public_html/SkyBase/`

**Option 2 — via FTP:**
1. Connect using FileZilla or any FTP client
2. Upload the entire `SkyBase/` folder to `public_html/`

Your app will be accessible at: `https://yourdomain.com/SkyBase/`

---

### Step 2 — Create the MySQL Database

1. Log in to cPanel → click **MySQL Databases**
2. Under "Create New Database" → type a name (e.g., `skybase`) → click **Create Database**
   - cPanel prepends your username: the full name will be `username_skybase`
3. Scroll down to "MySQL Users" → create a new user:
   - Username: `skydbuser` (becomes `username_skydbuser`)
   - Password: choose a strong password → **Create User**
4. Scroll to "Add User to Database" → select your new user + database → **Add**
5. On the privileges page → check **All Privileges** → **Make Changes**

Write down these three values — you need them in the next step:
- Database name: `username_skybase`
- Username: `username_skydbuser`
- Password: (what you just set)

---

### Step 3 — Edit the Database Config

On your local copy **before uploading**, or directly via File Manager on the server:

Open `config/database.php`:

```php
define('DB_HOST', 'localhost');           // almost always localhost on cPanel
define('DB_NAME', 'username_skybase');    // your full database name
define('DB_USER', 'username_skydbuser'); // your full database username
define('DB_PASS', 'your_strong_password');
```

Save the file. If you edited it locally, re-upload it to overwrite the server copy.

---

### Step 4 — Run the Core Schema via phpMyAdmin

1. In cPanel → click **phpMyAdmin**
2. In the left panel, click your database (`username_skybase`)
3. Click the **Import** tab
4. Click **Choose File** → select `sql/schema.sql` from your local computer
5. Click **Go**

Confirm success message. Tables `suppliers` and `products` now exist.

---

### Step 5 — Run the Regulatory Schema

1. Still in phpMyAdmin with your database selected
2. Click the **Import** tab
3. Click **Choose File** → select `sql/regulatory_schema.sql`
4. Click **Go**

Confirm success. Tables `certifications` and `product_alternatives` are now created.

---

### Step 5b — Run the Auth Schema

1. Import tab again → select `sql/auth_schema.sql` → **Go**

Table `users` is now created.

---

### Step 6 — Adjust PHP Settings (Required for CSV Import)

cPanel → scroll down to **Software** section → click **Select PHP Version** or **PHP Options**

Set these values:

| Setting               | Minimum Value | Recommended  |
|-----------------------|---------------|--------------|
| `upload_max_filesize` | 20M           | 64M          |
| `post_max_size`       | 20M           | 64M          |
| `max_execution_time`  | 300           | 600          |
| `memory_limit`        | 256M          | 512M         |

Click **Save** or **Apply**.

> If you do not see PHP Options in cPanel, create a `.htaccess` file in your `SkyBase/` folder with:
> ```
> php_value upload_max_filesize 64M
> php_value post_max_size 64M
> php_value max_execution_time 600
> php_value memory_limit 512M
> ```

---

### Step 7 — Seed Default Users

Visit: `https://yourdomain.com/SkyBase/install.php?key=skybase-install-2024`

| Username | Password | Role  | Access                          |
|----------|----------|-------|---------------------------------|
| admin    | admin    | Admin | All pages + admin panel + import|
| user     | user     | User  | Products + regulatory (read)    |

> **Delete `install.php` immediately after running.** In cPanel File Manager, right-click it → Delete.

---

### Step 7b — Place the SRG Logo

In cPanel File Manager, upload your `srg-logo.png` to:
`public_html/SkyBase/assets/images/srg-logo.png`

---

### Step 8 — Import Your Product CSV

1. Visit: `https://yourdomain.com/SkyBase/login.php` → sign in as `admin`
2. Click **Import CSV** in the sidebar
3. Choose your WooCommerce CSV → **Start Import**
4. Wait for completion — do not close the browser tab

---

### Step 9 — Verify the Application

1. Visit: `https://yourdomain.com/SkyBase/login.php` — login page should appear
2. Sign in as `admin` — product table should load
3. Try searching and filtering — results should appear instantly
4. Click any product row — the detail drawer should open
5. Visit admin panel: `https://yourdomain.com/SkyBase/admin.php`
6. Visit: `https://yourdomain.com/SkyBase/regulatory.php` — regulatory dashboard should load

---

### Step 9 — (Optional) Enable Nightly Cert Expiry Refresh

The regulatory module refreshes certificate expiry statuses on every page load automatically. If you want a MySQL scheduled event to run this nightly as a background job:

1. In phpMyAdmin → click your database → click the **SQL** tab
2. Run this to enable the event scheduler:
   ```sql
   SET GLOBAL event_scheduler = ON;
   ```
3. The `regulatory_schema.sql` already created a nightly event called `nightly_cert_refresh`. Verify it exists:
   ```sql
   SHOW EVENTS;
   ```

> **Note:** Many shared hosting providers disable `SET GLOBAL event_scheduler`. If the above fails, don't worry — the PHP fallback in the API handles expiry recalculation automatically on every request.

---

## CSV Column Mapping Reference

The CSV importer reads your WooCommerce export and maps columns automatically.

| WooCommerce CSV Column       | SkyBase Field         |
|------------------------------|-----------------------|
| ID                           | wc_id                 |
| SKU                          | sku                   |
| Name                         | name                  |
| Short description            | short_description     |
| Description                  | description           |
| Brands                       | supplier (normalized) |
| Tags                         | tags                  |
| Categories                   | categories            |
| Images                       | image_url             |
| Attribute: INCI Name         | inci_name             |
| Attribute: Function          | function_text         |
| Attribute: System Code       | system_code           |
| Attribute: Link              | product_link          |
| Attribute: Source            | source                |
| Attribute: Company           | company               |
| Attribute: CoO               | country_of_origin     |
| Attribute: CoP               | country_of_purchase   |
| Attribute: MOQ               | moq                   |
| Attribute: GTG               | gtg                   |
| Attribute: Remarks           | remarks               |

The importer reads all `Attribute N name` / `Attribute N value(s)` pairs (columns 1–11) and maps them by attribute name, so column order does not matter.

---

## Adding Certifications After Import

Certifications (Halal, GMP, ISO, etc.) are added manually per product:

1. Go to the product database → click any product row to open the drawer
2. Click the **Regulatory** tab inside the drawer
3. Click **+ Add Certification**
4. Fill in: Type, Certifying Body, Certificate Number, Issue Date, Expiry Date
5. Click **Save**

For Halal certifications, the system automatically syncs `halal_status`, `halal_cert_no`, and `halal_expiry` back to the product record for fast filtering.

---

## Adding Kit Alternatives

1. Open a product drawer → click the **Kit Alternatives** tab
2. Scroll past the Smart Suggestions section
3. Click **+ Add Manual Alternative**
4. Search for the alternative product by name
5. Set similarity percentage and reason
6. Click **Save**

Alternatives are bidirectional — adding A→B automatically makes B→A as well.

---

## Troubleshooting

**Blank white page after uploading:**
- Check `config/database.php` — incorrect credentials cause a silent failure
- Enable PHP error display temporarily: add `ini_set('display_errors', 1);` at the top of `index.php`
- Check cPanel → Logs → Error Logs for PHP fatal errors

**Import times out before finishing:**
- Increase `max_execution_time` to 600 in PHP settings (see Step 6)
- For very large CSVs, try splitting the file into two halves and importing sequentially — the importer uses `ON DUPLICATE KEY UPDATE` so re-importing is safe

**Import completes but no products appear:**
- In phpMyAdmin, run: `SELECT COUNT(*) FROM products;`
- If count is 0, check that the CSV uses WooCommerce's default column naming
- Verify the CSV is UTF-8 encoded (open in Notepad++ and check Encoding menu)

**Regulatory schema import fails with "duplicate column" error:**
- The `products` table already has the halal columns from a previous run
- Safe to ignore this error — the data tables exist correctly
- Alternatively: drop and recreate the database, then run both SQL files fresh

**"Table 'certifications' doesn't exist" error on regulatory page:**
- You skipped Step 3/Step 5 (regulatory schema)
- Run `sql/regulatory_schema.sql` in phpMyAdmin — it is safe to run on an existing database

**Search returns no results:**
- MySQL 5.6+ is required for FULLTEXT on InnoDB
- Run in phpMyAdmin: `REPAIR TABLE products;`
- Check that your MySQL user has `SELECT` privilege

**Regulatory dashboard shows no stats:**
- This is normal when no certifications have been added yet
- Import products first, then add certifications via product drawers

**Certificate expiry dates look wrong:**
- The server's timezone affects `CURDATE()` calculations
- In phpMyAdmin run: `SELECT NOW(), CURDATE();` to verify the server date/time
- Adjust dates when adding certifications if the server is in a different timezone

**Drawer does not open on regulatory.php:**
- Check browser console (F12) for JavaScript errors
- Ensure `assets/js/app.js` and `assets/js/regulatory.js` are both uploaded
- Verify file permissions — files should be 644, directories 755

---

## Permissions Reference (cPanel)

After uploading, set these permissions in File Manager (right-click → Change Permissions):

| Type       | Permission |
|------------|------------|
| PHP files  | 644        |
| CSS/JS     | 644        |
| SQL files  | 644        |
| Directories| 755        |

---

## Performance Notes

- FULLTEXT index covers: `name`, `inci_name`, `short_description`, `description`, `function_text`, `tags`, `remarks`
- Indexed columns for fast filtering: `supplier_id`, `source`, `country_of_origin`, `gtg`, `halal_status`
- Frontend uses 320ms debounced search and server-side pagination
- Smart alternatives use keyword extraction with 3-tier matching — no external API needed
- Tested with 8,700+ products — typical page load under 200ms on standard shared hosting

---

*SkyBase v1.0 — Built for Sky Resources Group*