# TicketHub Central - Laravel Multi-Tenant Ticketing System

A central web application that lets staff log in once, see a list of 10 existing ticketing sites (each with its own MySQL database), select a site, and then view that site's tickets, comments, files, and related user information.

## Features

- **Single Sign-On**: Staff logs in with username (no email required)
- **Multi-Tenant Architecture**: Dynamic database connections to multiple MySQL databases
- **Ticket Management**: View tickets, comments, files, and user information
- **File Preview**: Thumbnails with click-to-open modal for images, PDFs, and videos
- **Staff Comments**: Add support responses to tickets
- **Filtering & Search**: Filter tickets by status, category, type, and search by ID/username
- **Pagination**: Efficient browsing of large ticket lists

## Requirements

- PHP 8.2+ (supports up to PHP 8.5)
- Composer 2.x
- Laravel 12.x
- MySQL 5.7+ or MariaDB 10.3+
- Node.js & NPM (optional, for asset compilation)

## Installation

### 1. Clone the repository

```bash
cd laravel-tickethub
```

### 2. Install PHP dependencies

```bash
composer install
```

### 3. Configure environment

```bash
cp .env.example .env
php artisan key:generate
```

### 4. Update `.env` with your database settings

```env
# Central Database (for auth and site configuration)
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tickethub_central
DB_USERNAME=root
DB_PASSWORD=your_password
```

### 5. Create the central database

```sql
CREATE DATABASE tickethub_central CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

### 6. Run migrations and seed data

```bash
php artisan migrate
php artisan db:seed
```

### 7. Start the development server

```bash
php artisan serve
```

Visit `http://localhost:8000` in your browser.

## Default Credentials

| Username | Password | Role |
|----------|----------|------|
| admin | admin123 | Administrator |
| support | support123 | Support Agent |

## Adding Tenant Sites

To add a new tenant site, insert a record into the `sites` table:

```sql
INSERT INTO sites (name, code, db_host, db_port, db_database, db_username, db_password, is_active, created_at, updated_at)
VALUES ('Site Name', 'site_code', 'db.host.com', 3306, 'database_name', 'db_user', 'db_password', 1, NOW(), NOW());
```

Or create a Site model in your code:

```php
use App\Models\Site;

Site::create([
    'name' => 'New Site',
    'code' => 'newsite',
    'db_host' => 'db.newsite.com',
    'db_port' => 3306,
    'db_database' => 'newsite_tickets',
    'db_username' => 'app_user',
    'db_password' => 'secure_password',
    'is_active' => true,
]);
```

## Tenant Database Schema

Each tenant database should have these tables (read-only from central app):

### tickets
```sql
CREATE TABLE tickets (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL,
    mac VARCHAR(20) DEFAULT NULL,
    category VARCHAR(100) DEFAULT NULL,
    type VARCHAR(100) DEFAULT NULL,
    box VARCHAR(50) DEFAULT NULL,
    box_ip VARCHAR(64) DEFAULT NULL,
    box_isp VARCHAR(200) DEFAULT NULL,
    vod_category VARCHAR(100) DEFAULT NULL,
    content TEXT,
    closed TINYINT(1) DEFAULT '0',
    support_closed TINYINT(1) NOT NULL DEFAULT '0',
    `read` TINYINT(1) NOT NULL DEFAULT '1',
    updated DATETIME NOT NULL,
    created DATETIME NOT NULL,
    KEY fk_username (username)
);
```

### ticket_comments
```sql
CREATE TABLE ticket_comments (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ticket_id BIGINT NOT NULL,
    content TEXT,
    support_reply TINYINT(1) DEFAULT '0',
    support_by VARCHAR(100) DEFAULT NULL,
    updated DATETIME NOT NULL,
    created DATETIME NOT NULL,
    KEY fk_ticket_id (ticket_id)
);
```

### ticket_files
```sql
CREATE TABLE ticket_files (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    comment_id INT DEFAULT NULL,
    file VARCHAR(255) DEFAULT NULL,
    created DATETIME NOT NULL,
    updated DATETIME NOT NULL
);
```

### users (central)
```sql
CREATE TABLE users (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    remember_token VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL
);
```

### users (tenant)
```sql
CREATE TABLE users (
    username VARCHAR(64) NOT NULL PRIMARY KEY,
    password VARCHAR(64) NOT NULL,
    name VARCHAR(128) NOT NULL,
    type ENUM('ROOT','SRSLR','RSLR','MNGR') NOT NULL DEFAULT 'RSLR',
    accounts_prefix VARCHAR(16) DEFAULT NULL,
    username_owner VARCHAR(64) DEFAULT NULL,
    status ENUM('A','S') NOT NULL DEFAULT 'A',
    comments VARCHAR(128) DEFAULT NULL,
    current_login_time DATETIME DEFAULT NULL,
    last_login_time DATETIME DEFAULT NULL,
    credit INT DEFAULT NULL
);
```

## Project Structure

```
laravel-tickethub/
├── app/
│   ├── Http/
│   │   ├── Controllers/
│   │   │   ├── Auth/
│   │   │   │   └── LoginController.php      # Authentication
│   │   │   ├── SiteController.php           # Sites listing
│   │   │   └── TicketController.php         # Ticket CRUD
│   │   └── Middleware/
│   │       └── SetTenantConnection.php      # Dynamic DB connection
│   ├── Models/
│   │   ├── Site.php                         # Central site config
│   │   ├── User.php                         # Central auth user
│   │   └── Tenant/
│   │       ├── TenantModel.php              # Base tenant model
│   │       ├── Ticket.php                   # Tenant tickets
│   │       ├── TicketComment.php            # Tenant comments
│   │       ├── TicketFile.php               # Tenant files
│   │       └── TenantUser.php               # Tenant users
│   ├── Providers/
│   │   └── AppServiceProvider.php
│   └── Services/
│       └── TenantConnectionService.php      # DB connection manager
├── config/
│   └── database.php                         # DB configuration
├── database/
│   ├── migrations/                          # Central DB migrations
│   └── seeders/
│       └── DatabaseSeeder.php               # Demo data
├── resources/
│   └── views/
│       ├── layouts/
│       │   └── app.blade.php                # Main layout
│       ├── auth/
│       │   └── login.blade.php              # Login page
│       ├── sites/
│       │   └── index.blade.php              # Sites dashboard
│       └── tickets/
│           ├── index.blade.php              # Ticket list
│           └── show.blade.php               # Ticket detail
└── routes/
    └── web.php                              # Route definitions
```

## Key Components

### TenantConnectionService

Manages dynamic database connections:

```php
use App\Services\TenantConnectionService;
use App\Models\Site;

$service = app(TenantConnectionService::class);
$site = Site::findByCode('north1');
$service->configure($site);

// Now all tenant models use this connection
$tickets = Ticket::all();
```

### SetTenantConnection Middleware

Automatically configures the tenant connection based on route parameter:

```php
Route::middleware(['auth', 'tenant'])->group(function () {
    Route::get('/sites/{site}/tickets', [TicketController::class, 'index']);
});
```

## Security Notes

- Store tenant database passwords encrypted in production
- Use environment variables for sensitive data
- The central app treats tenant databases as read-only for schema
- Only comments are writable by staff

## License

MIT License