# Installation Guide for c77_mvc PostgreSQL Extension

## Prerequisites

Before installing the c77_mvc extension, ensure you have:

1. PostgreSQL 11 or later installed
2. Administrative access to your PostgreSQL instance
3. The c77_dbh extension installed (required dependency)
4. Git (if installing from source repository)

## Standard Installation

### Option 1: Using PostgreSQL Extensions Directory

1. Copy the extension files to your PostgreSQL extensions directory:

```bash
# Get the extension directory location
export PGEXTDIR=$(pg_config --sharedir)/extension

# Copy files
sudo cp c77_mvc.control $PGEXTDIR/
sudo cp c77_mvc--1.0.sql $PGEXTDIR/
```

2. Connect to your PostgreSQL database and create the extension:

```sql
CREATE EXTENSION c77_dbh;  -- Install dependency first if not already installed
CREATE EXTENSION c77_mvc;
```

### Option 2: Installing from Git Repository

1. Clone the repository:

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

2. Copy files to your PostgreSQL extensions directory:

```bash
export PGEXTDIR=$(pg_config --sharedir)/extension
sudo cp c77_mvc.control $PGEXTDIR/
sudo cp c77_mvc--1.0.sql $PGEXTDIR/
```

3. Connect to your PostgreSQL database and create the extension:

```sql
CREATE EXTENSION c77_dbh;  -- Install dependency first if not already installed
CREATE EXTENSION c77_mvc;
```

## Manual Installation

If you prefer to install the extension manually or if you need to customize the installation process, follow these steps:

1. Ensure the c77_dbh extension is already installed:

```sql
SELECT * FROM pg_extension WHERE extname = 'c77_dbh';
```

If not installed, install it first:

```sql
CREATE EXTENSION c77_dbh;
```

2. Create the table and functions manually by executing the SQL commands:

```sql
-- Create the audit table
CREATE TABLE IF NOT EXISTS public.c77_mvc_table_fitness_audit (
  run_id BIGSERIAL,
  run_timestamp timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
  source_schema text COLLATE pg_catalog."default",
  source_table text COLLATE pg_catalog."default",
  analysis_result jsonb,
  notes text[] COLLATE pg_catalog."default",
  CONSTRAINT table_fitness_audit_pkey PRIMARY KEY (run_id)
) TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_table_fitness_audit_table
  ON public.c77_mvc_table_fitness_audit USING btree
  (source_schema COLLATE pg_catalog."default" ASC NULLS LAST, source_table COLLATE pg_catalog."default" ASC NULLS LAST)
  TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_table_fitness_audit_timestamp
  ON public.c77_mvc_table_fitness_audit USING btree
  (run_timestamp ASC NULLS LAST)
  TABLESPACE pg_default;

-- Now execute all the function creation SQL commands from c77_mvc--1.0.sql
-- (Copy and paste all CREATE OR REPLACE FUNCTION statements from the SQL file)
```

3. Verify the installation:

```sql
-- Check if the main table exists
SELECT * FROM pg_tables WHERE tablename = 'c77_mvc_table_fitness_audit';

-- Check if key functions exist
SELECT proname, pronamespace::regnamespace as schema 
FROM pg_proc 
WHERE proname LIKE 'c77_mvc%' 
ORDER BY proname;
```

## Troubleshooting

### Common Issues

1. **Dependency Error**: If you see an error about missing the c77_dbh extension, make sure it's installed properly:

```sql
CREATE EXTENSION c77_dbh;
```

2. **Permission Issues**: Ensure your PostgreSQL user has sufficient privileges:

```sql
-- For a specific user
GRANT ALL ON SCHEMA public TO your_user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO your_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO your_user;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO your_user;
```

3. **Schema Issues**: If you're installing to a non-public schema, adjust permissions accordingly:

```sql
-- Replace 'custom_schema' with your target schema
GRANT ALL ON SCHEMA custom_schema TO your_user;
GRANT ALL ON ALL TABLES IN SCHEMA custom_schema TO your_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA custom_schema TO your_user;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA custom_schema TO your_user;
```

### Checking for Successful Installation

To verify if the extension was installed correctly:

```sql
-- List installed extensions
SELECT * FROM pg_extension WHERE extname = 'c77_mvc';

-- Check if the main table exists
SELECT * FROM information_schema.tables WHERE table_name = 'c77_mvc_table_fitness_audit';

-- Test a simple function
SELECT public.c77_mvc_calculate_sample_size(1000000);
```

## Upgrading

To upgrade from a previous version of the extension:

```sql
ALTER EXTENSION c77_mvc UPDATE TO '1.0';
```

## Uninstallation

If you need to uninstall the extension:

```sql
DROP EXTENSION c77_mvc;
```

Note: This will not remove the tables and objects created by the extension. To completely remove all objects:

```sql
DROP EXTENSION c77_mvc CASCADE;
```

## Getting Help

For additional help or to report issues:

- Visit the repository at: https://git.jctr3.com/trogers1884/c77_mvc
- Contact the maintainer via issues on the repository