# c77_mvc PostgreSQL Extension

[![PostgreSQL](https://img.shields.io/badge/PostgreSQL-11%2B-blue.svg)](https://www.postgresql.org/)

A PostgreSQL extension for materialized view management and table fitness analysis.

## Overview

c77_mvc (Materialized View and table fitness utilities) provides database administrators and developers with tools to:

1. **Create optimized materialized views** with synthetic keys and content hashing
2. **Manage materialized view health** with automatic staleness detection and refresh
3. **Analyze table fitness** for partitioning, indexing, and query optimization 
4. **Monitor data quality** with encoding issue detection and isolation

## Features

### Materialized View Management

- Create materialized views with synthetic keys and proper partitioning
- Track content hashes to detect data changes efficiently
- Isolate records with encoding issues into separate views
- Monitor materialized view health with configurable thresholds
- Automatically refresh views based on staleness metrics
- Estimate refresh times based on historical performance

### Table Fitness Analysis

- Analyze column characteristics for partitioning and indexing
- Identify optimal column combinations for keys and partitioning
- Evaluate data quality with comprehensive metrics
- Calculate overall Data Quality Index (DQI)
- Use statistical sampling for efficient analysis of large tables

## Requirements

- PostgreSQL 11 or later
- c77_dbh extension (dependency)

## Installation

### Quick Install

If you have both extensions available in your PostgreSQL extensions directory:

```sql
CREATE EXTENSION c77_dbh;  -- Install dependency first
CREATE EXTENSION c77_mvc;
```

### From Source

```bash
# Clone repository
git clone https://git.jctr3.com/trogers1884/c77_mvc.git
cd c77_mvc

# Copy files to PostgreSQL extension directory
export PGEXTDIR=$(pg_config --sharedir)/extension
sudo cp c77_mvc.control $PGEXTDIR/
sudo cp c77_mvc--1.0.sql $PGEXTDIR/

# Create extension in your database
psql -d your_database -c "CREATE EXTENSION c77_dbh;"
psql -d your_database -c "CREATE EXTENSION c77_mvc;"
```

For detailed installation instructions, see [INSTALLATION.md](INSTALLATION.md).

## Basic Usage

### Table Fitness Analysis

```sql
-- Analyze a table for fitness metrics
SELECT * FROM public.c77_mvc_analyze_table_fitness('schema_name', 'table_name');
```

### Creating Optimized Materialized Views

```sql
-- Create an optimized materialized view
SELECT * FROM public.c77_mvc_create_optimized_matv(
  'source_schema',           -- Source schema
  'source_table',            -- Source table
  'target_schema',           -- Target schema
  'matc_target_view_name',   -- Target materialized view name (must start with matc_)
  ARRAY['customer_id'],      -- Partition columns
  ARRAY['last_updated'],     -- Order-by columns
  ARRAY['notes'],            -- Columns to exclude from hash calculation (optional)
  false                      -- Filter for latest records only (optional)
);
```

### Managing Materialized View Health

```sql
-- Check materialized view health
SELECT * FROM public.c77_mvc_manage_matv_health(
  'schema_name',       -- Schema name
  'matc_view_name',    -- Materialized view name
  'quick',             -- Validation type: 'quick', 'daily', 'full'
  NULL                 -- Action: NULL, 'refresh', 'repair', 'reindex'
);

-- Check and refresh if needed
SELECT * FROM public.c77_mvc_manage_matv_health(
  'schema_name',
  'matc_view_name',
  'daily',
  'refresh'
);
```

For comprehensive usage examples, see [USAGE.md](USAGE.md).

## View Structure

When you create an optimized materialized view, the extension creates multiple objects:

| Object | Naming Pattern | Purpose |
|--------|----------------|---------|
| View | vtw_* | Source view with content hash, synthetic key, and encoding status |
| Materialized View | matc_* | Materialized copy of the vtw_ view |
| View | vm_* | Clean data view (excludes encoding issues) |
| View | vprob_* | Problematic data view (only encoding issues) |

## Documentation

- [Installation Guide](INSTALLATION.md)
- [Usage Guide](USAGE.md)
- [Technical Assessment](TECHNICAL.md)

## Contributing

1. Fork the repository
2. Create your feature branch (`git checkout -b feature/amazing-feature`)
3. Commit your changes (`git commit -m 'Add some amazing feature'`)
4. Push to the branch (`git push origin feature/amazing-feature`)
5. Open a Pull Request

## License

This project is licensed under the PostgreSQL License - see the LICENSE file for details.

## Acknowledgements

- Developed by [Your Name/Organization]
- Special thanks to contributors and testers