# Portfolio Website - MySQL Database Architecture

## Overview
This project uses MySQL database for all data storage. JSON files have been completely removed and replaced with database tables.

## Database Setup

### 1. Create Database
```bash
mysql -u root -p < database.sql
```

Or manually run the SQL commands in `database.sql` using MySQL Workbench or phpMyAdmin.

### 2. Configure Environment
Edit `.env` file with your database credentials:
```env
DB_HOST=localhost
DB_PORT=3306
DB_NAME=asoodatabase
DB_USER=root
DB_PASSWORD=your_password
```

## Database Structure

### Tables

1. **hero** - Homepage hero section data
2. **about** - About page information
3. **services** - Services offered
4. **portfolio** - Portfolio projects
5. **testimonials** - Client testimonials
6. **blog** - Blog posts
7. **contact** - Contact form submissions
8. **settings** - Application settings
9. **social_media** - Social media links
10. **file_attachments** - All file uploads (centralized)
11. **users** - Admin users
12. **categories** - Content categories
13. **newsletter_subscribers** - Newsletter subscriptions

### File Attachments Architecture

The `file_attachments` table stores all files for any page:
- `page_name`: Table/page name (e.g., 'hero', 'portfolio', 'blog')
- `record_id`: ID of the record in that table
- `file_path`: Physical file path
- `file_folder`: Folder where file is stored
- `is_primary`: Primary image flag

Files are physically stored in: `uploads/{page_name}/{record_id}/`

## API Endpoints

All APIs are located in `/api/` folder:

### Hero
- `GET /api/hero.php` - Get hero data
- `POST /api/hero.php` - Create hero
- `PUT /api/hero.php?id=1` - Update hero
- `DELETE /api/hero.php?id=1` - Delete hero

### About
- `GET /api/about.php` - Get about data
- `POST /api/about.php` - Create about
- `PUT /api/about.php?id=1` - Update about
- `DELETE /api/about.php?id=1` - Delete about

### Services
- `GET /api/services.php` - Get all services
- `GET /api/services.php?id=1` - Get single service
- `POST /api/services.php` - Create service
- `PUT /api/services.php?id=1` - Update service
- `DELETE /api/services.php?id=1` - Delete service

### Portfolio
- `GET /api/portfolio.php` - Get all portfolio items
- `GET /api/portfolio.php?id=1` - Get single item
- `GET /api/portfolio.php?featured=1` - Get featured items
- `GET /api/portfolio.php?category=web` - Get by category
- `GET /api/portfolio.php?categories=1` - Get all categories
- `POST /api/portfolio.php` - Create portfolio item
- `PUT /api/portfolio.php?id=1` - Update portfolio item
- `DELETE /api/portfolio.php?id=1` - Delete portfolio item

### Blog
- `GET /api/blog.php` - Get all published posts
- `GET /api/blog.php?id=1` - Get post by ID
- `GET /api/blog.php?slug=post-slug` - Get post by slug
- `GET /api/blog.php?category=tech` - Get posts by category
- `GET /api/blog.php?recent=1&limit=5` - Get recent posts
- `GET /api/blog.php?search=keyword` - Search posts
- `POST /api/blog.php` - Create post
- `PUT /api/blog.php?id=1` - Update post
- `DELETE /api/blog.php?id=1` - Delete post

### Contact
- `GET /api/contact.php` - Get all messages
- `GET /api/contact.php?unread=1` - Get unread messages
- `POST /api/contact.php` - Submit contact form
- `PUT /api/contact.php?id=1` - Mark as read/replied

### Testimonials
- `GET /api/testimonials.php` - Get all testimonials
- `POST /api/testimonials.php` - Create testimonial
- `PUT /api/testimonials.php?id=1` - Update testimonial
- `DELETE /api/testimonials.php?id=1` - Delete testimonial

### Settings
- `GET /api/settings.php` - Get all settings
- `GET /api/settings.php?key=site_name` - Get specific setting
- `POST /api/settings.php` - Create/update setting

### Social Media
- `GET /api/social-media.php` - Get all social media links
- `POST /api/social-media.php` - Create link
- `PUT /api/social-media.php?id=1` - Update link
- `DELETE /api/social-media.php?id=1` - Delete link

### Newsletter
- `GET /api/newsletter.php` - Get subscribers
- `POST /api/newsletter.php` - Subscribe
- `DELETE /api/newsletter.php?email=test@test.com` - Unsubscribe

### Categories
- `GET /api/categories.php` - Get all categories
- `GET /api/categories.php?parents=1` - Get parent categories
- `GET /api/categories.php?parent_id=1` - Get child categories
- `POST /api/categories.php` - Create category
- `PUT /api/categories.php?id=1` - Update category
- `DELETE /api/categories.php?id=1` - Delete category

### File Upload
- `POST /api/upload.php` - Upload file
  - Form data: `file`, `page_name`, `record_id`, `is_primary`, `display_order`
- `DELETE /api/upload.php?id=1` - Delete file

## Models

All models extend `BaseModel` class which provides basic CRUD operations:

- `HeroModel`
- `AboutModel`
- `ServicesModel`
- `PortfolioModel`
- `BlogModel`
- `ContactModel`
- `TestimonialsModel`
- `SettingsModel`
- `SocialMediaModel`
- `FileAttachmentModel`
- `UserModel`
- `CategoryModel`
- `NewsletterModel`

## Usage Example

### PHP
```php
require_once 'models/HeroModel.php';

$heroModel = new HeroModel();
$hero = $heroModel->getActiveHero();

echo $hero['title'];
```

### JavaScript (Fetch API)
```javascript
// Get hero data
fetch('/api/hero.php')
  .then(response => response.json())
  .then(data => {
    console.log(data.data);
  });

// Submit contact form
fetch('/api/contact.php', {
  method: 'POST',
  headers: {
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    name: 'John Doe',
    email: 'john@example.com',
    message: 'Hello!'
  })
})
.then(response => response.json())
.then(data => {
  console.log(data.message);
});

// Upload file
const formData = new FormData();
formData.append('file', fileInput.files[0]);
formData.append('page_name', 'portfolio');
formData.append('record_id', '1');
formData.append('is_primary', '1');

fetch('/api/upload.php', {
  method: 'POST',
  body: formData
})
.then(response => response.json())
.then(data => {
  console.log(data.file_path);
});
```

## File Upload Structure

```
uploads/
├── hero/
│   └── 1/
│       ├── file1.jpg
│       └── file2.jpg
├── portfolio/
│   ├── 1/
│   │   └── project1.jpg
│   └── 2/
│       └── project2.jpg
├── blog/
│   └── 1/
│       └── featured.jpg
└── testimonials/
    └── 1/
        └── avatar.jpg
```

## Security Notes

1. Change `SECRET_KEY` in `.env` for production
2. Update database password
3. Restrict file upload types
4. Implement authentication for admin APIs
5. Use prepared statements (already implemented in Database class)
6. Validate and sanitize all inputs

## Migration from JSON

All JSON dependencies have been removed. Data is now:
- Stored in MySQL tables
- Retrieved via Models
- Accessed through REST APIs
- Files stored physically with metadata in database
