Drop support for Santiago de Compostela, add collection script

1 files changed, 71 insertions, 0 deletions

diff --git a/src/delay_collector/DB_SETUP.md b/src/delay_collector/DB_SETUP.md
new file mode 100644
index 0000000..fedfb93
--- /dev/null
+++ b/src/delay_collector/DB_SETUP.md
@@ -0,0 +1,71 @@
+# PostgreSQL Database Setup
+
+This document describes how to set up the PostgreSQL database for the delay collector.
+
+## Prerequisites
+
+- PostgreSQL 18
+- psql
+
+## Database Setup
+
+### 1. Create Database and User
+
+Connect to PostgreSQL as superuser:
+
+```bash
+sudo -u postgres psql
+```
+
+Create database and user:
+
+```sql
+-- Create database
+CREATE DATABASE busurbano;
+
+-- Create user
+CREATE USER busurbano_collector WITH PASSWORD 'your_secure_password';
+
+-- Grant privileges
+GRANT CONNECT ON DATABASE busurbano TO busurbano_collector;
+\c busurbano
+GRANT USAGE ON SCHEMA public TO busurbano_collector;
+GRANT INSERT, SELECT ON ALL TABLES IN SCHEMA public TO busurbano_collector;
+GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO busurbano_collector;
+ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, SELECT ON TABLES TO busurbano_collector;
+ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO busurbano_collector;
+```
+
+### 2. Create Schema
+
+Run the schema file:
+
+```bash
+psql -U postgres -d busurbano -f schema.sql
+```
+
+Or from within psql:
+
+```sql
+\c busurbano
+\i schema.sql
+```
+
+## Configuration
+
+Update your `.env` file:
+
+```bash
+cp .env.example .env
+nano .env
+```
+
+Set the following:
+
+```env
+DB_HOST=localhost
+DB_PORT=5432
+DB_NAME=busurbano
+DB_USER=busurbano_collector
+DB_PASSWORD=your_secure_password
+```