# PostgreSQL Integration Tests

This directory contains Docker-based integration tests for the PostgreSQL reader.

## Quick Start

Run integration tests with Docker:

```bash
# From project root
make docker-test
```

Or use the script directly:

```bash
./tests/postgres/run_tests.sh
```

## Manual Testing

Start the PostgreSQL test database:

```bash
make docker-up
```

Run tests with the running database:

```bash
export RELSPEC_TEST_PG_CONN="postgres://relspec:relspec_test_password@localhost:5433/relspec_test"
go test -v ./pkg/readers/pgsql/
```

Stop the database:

```bash
make docker-down
```

## Test Database Contents

The test database (`relspec_test`) contains:

### Schemas (3)
- **public**: Main schema with user and blog data
- **analytics**: Event tracking and analytics data
- **inventory**: Product inventory management

### Tables (14 total)
- **public.users**: User accounts with various data types
- **public.posts**: Blog posts with foreign keys
- **public.comments**: Nested comments (self-referencing FK)
- **public.categories**: Post categories
- **public.post_categories**: Many-to-many junction table
- **analytics.events**: Event tracking
- **analytics.page_views**: Page view analytics
- **analytics.conversions**: Conversion tracking
- **inventory.products**: Product catalog
- **inventory.warehouses**: Warehouse locations
- **inventory.stock**: Product stock by warehouse

### Views (8 total)
- **public.active_users**: Active users only
- **public.published_posts**: Published posts with author info
- **public.post_stats**: Post statistics
- **analytics.daily_stats**: Daily aggregated statistics
- **analytics.top_pages**: Most viewed pages
- **inventory.available_products**: Available products with stock
- **inventory.low_stock_products**: Products with low inventory

### Sequences (5 total)
- **public.users_id_seq**: Users ID sequence
- **public.posts_id_seq**: Posts ID sequence (with cycle)
- **analytics.events_id_seq**: Events ID sequence
- Plus auto-generated sequences for serial columns

### Constraint Types
- **Primary Keys**: All tables have PKs
- **Foreign Keys**: Including cross-schema references
- **Unique Constraints**: Username, email, SKU, etc.
- **Check Constraints**: Age limits, status validation, balance checks

### Index Types
- **btree**: Standard B-tree indexes
- **gin**: GIN indexes for JSONB and array columns
- **hash**: Hash indexes for session lookups
- **Unique**: Unique indexes
- **Partial**: Conditional indexes (WHERE clauses)
- **Composite**: Multi-column indexes

## Connection Details

- **Host**: localhost
- **Port**: 5433 (to avoid conflicts with local PostgreSQL)
- **Database**: relspec_test
- **User**: relspec
- **Password**: relspec_test_password

Full connection string:
```
postgres://relspec:relspec_test_password@localhost:5433/relspec_test
```

## Requirements

- Docker or Docker Compose
- Go 1.25+

## Makefile Targets

- `make docker-up` - Start PostgreSQL container
- `make docker-down` - Stop PostgreSQL container
- `make docker-test` - Run full test suite with automatic cleanup
- `make docker-test-integration` - Run integration tests only

## Test Coverage

The tests verify:
- Schema introspection (excluding system schemas)
- Table metadata extraction
- View definitions and columns
- Sequence properties and ownership
- Column data types and constraints
- Primary key detection
- Foreign key relationships with referential actions
- Unique and check constraints
- Index types and definitions
- Cross-schema relationships
- Data type mapping
- Error handling