# Database Setup Guide

## Prerequisites
- PostgreSQL installed and running
- Node.js installed

## Step 1: Create PostgreSQL Database

Open PostgreSQL command line and run:

```sql
-- Create database
CREATE DATABASE chamcong_db;

-- Connect to the database
\c chamcong_db

-- The tables will be created automatically when you start the server!
```

## Step 2: Configure Environment Variables

1. Copy `.env.example` to `.env`
```bash
cp .env.example .env
```

2. Edit `.env` and update the database credentials:
```
DB_USER=postgres
DB_HOST=localhost
DB_NAME=chamcong_db
DB_PASSWORD=your_postgres_password
DB_PORT=5432
```

## Step 3: Install Dependencies

```bash
npm install
```

## Step 4: Start the Server

```bash
node server.js
```

You should see:
```
✅ Connected to PostgreSQL database
✅ Database tables initialized successfully
✅ Server running at: http://localhost:3000
📊 Health check: http://localhost:3000/api/health
```

## Step 5: Test the Connection

Visit: http://localhost:3000/api/health

Should return:
```json
{
  "success": true,
  "message": "Server is running",
  "database": "Connected",
  "timestamp": "2026-06-07T10:30:45.123Z"
}
```

## Common Issues

### ❌ "Error: connect ECONNREFUSED"
- PostgreSQL is not running
- Wrong host/port in `.env`
- Fix: Start PostgreSQL service

### ❌ "Error: role 'postgres' does not exist"
- Wrong DB_USER in `.env`
- Fix: Check your PostgreSQL username

### ❌ "Database chamcong_db does not exist"
- Database was not created
- Fix: Run the SQL command from Step 1

### ❌ "password authentication failed"
- Wrong DB_PASSWORD in `.env`
- Fix: Update password in `.env` file

## Database Schema

The `attendance_logs` table will be created automatically with:
- `id`: Primary key
- `user_id`: Employee ID
- `log_type`: 'IN' or 'OUT'
- `latitude`: GPS latitude
- `longitude`: GPS longitude
- `timestamp`: Check-in/out time
- `created_at`: Record creation time

Indexes are automatically created on `user_id` and `timestamp` for better performance.