# README — PostgreSQL Database Setup for **EOS**

> This README provides instructions for creating and managing the **EOS** PostgreSQL database schema. The schema includes multiple tables (e.g., `tickets_open_additional_info` and others). Each table DDL is stored in separate `.sql` files such as `test.txt`.

---

## Table of contents
1. Purpose  
2. Prerequisites  
3. Database & schema overview  
4. Files included  
5. Quick start — create EOS database & load schema  
6. Detailed schema notes (example: `tickets_open_additional_info`)  
7. Adding more tables  
8. Best practices (migrations, backups, security)  
9. Troubleshooting  
10. Contact / maintainers  

---

# 1. Purpose
The **EOS** database stores application data related to ticketing, feedback, and operational workflows. The repository contains SQL DDL files (one per table or per module). These define table structures, indexes, constraints, and comments.

---

# 2. Prerequisites
- PostgreSQL server (recommended: v12+).  
- `psql` client.  
- Privileges to create database, schema, and tables.  

---

# 3. Database & schema overview
- **Database name:** `EOS`  
- **Schema:** default is `public`, unless otherwise specified.  
- **Tables:** multiple (`tickets_open_additional_info`, `tickets_open`, …). Each is defined in `.sql` files.  

---

# 4. Files included
- `test.txt` — SQL DDL for `tickets_open_additional_info`.  
- Additional `.sql` files — each will hold the DDL for other tables in **EOS**.  

---

# 5. Quick start — create EOS database & load schema

**1) Create the EOS database**
```bash
createdb EOS

# or via psql
psql -U postgres -c "CREATE DATABASE EOS;"
```

**2) Create application user (optional)**
```bash
psql -U postgres -c "CREATE ROLE eos_user WITH LOGIN PASSWORD 'strong_password';"
psql -U postgres -c "GRANT CONNECT ON DATABASE EOS TO eos_user;"
```

**3) Run schema files**
```bash
# Run for a single table
psql -U postgres -d EOS -f /path/to/test.txt

# Run all tables (assuming all SQL files are in ./schema)
for f in ./schema/*.sql; do
  psql -U postgres -d EOS -f "$f"
done
```

---

# 6. Detailed schema notes (example: `tickets_open_additional_info`)
The `tickets_open_additional_info` table is defined in `test.txt`. Highlights:
- **Primary key:** `ticket_id`.  
- **Important columns:** state, city, vehicle_tagging, trip times, feedback fields.  
- **Indexes:** on `ticket_id`, `state_name`, `city_name`.  
- **Comments:** explain references (e.g., `ticket_id` → `tickets_open.ticket_id`).  

Similar notes should be documented for each table when new `.sql` files are added.  

---

# 7. Adding more tables
- Place new `.sql` files in the `schema/` directory (or alongside `test.txt`).  
- Each `.sql` should contain `CREATE TABLE` + indexes + constraints.  
- Example:
  - `tickets_open.sql`  
  - `tickets_closed.sql`  
  - `tickets_feedback.sql`  
  - etc.  

To apply all:
```bash
psql -U postgres -d EOS -f tickets_open.sql
psql -U postgres -d EOS -f tickets_closed.sql
psql -U postgres -d EOS -f tickets_feedback.sql
```

---

# 8. Best practices
- Keep schema files under **version control (Git)**.  
- Use migration tools (Flyway, Liquibase) if schema changes are frequent.  
- Define **foreign keys** between related tables (e.g., `tickets_open_additional_info.ticket_id → tickets_open.ticket_id`).  
- Remove redundant indexes (avoid duplicate PK + unique index).  
- Use **least-privilege users** for the application (`SELECT/INSERT/UPDATE` only).  

---

# 9. Troubleshooting
- **"relation already exists"** → drop the table or add `IF NOT EXISTS`.  
- **foreign key issues** → ensure referenced table exists before dependent table.  
- **index duplication warnings** → simplify schema.  

---

# 10. Contact / maintainers
- Maintainer: (team/person name, email)  
- Database: `EOS`