# IWS5.Database - FEMA
The IWS5.Database project is an [SQL Database project](https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/sql-database-projects?view=sql-server-ver17) containing all of the database tables (in [T-SQL](https://learn.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-ver17)), pre-deploy scripts, and post-deploy scripts used to populate and initialize the Process Director database.

Although the contents of the IWS5.Database project are essentially the same between FEMA and LPNU branches, the database schemas and post-deploy scripts are not identical. The below documentation is for the FEMA branch only. Please read the appropriate documentation for the branch you wish to use.

## Contents
1. [dbo](dbo/) - Contains database table definitions and stored procedures. When the database project is published, these `.sql` files are added to the final publish script to create/update tables.
2. [Pre-Deploy](Pre-Deploy/) - Contains migration scripts, currently exclusively for the transition to a numeric primary key for the `ATQueue` table. These scripts are designed to be executed only when a Process Director database instance has not already been migrated from the previous string primary key schema, and they are *not* included in the final publish script and thus must be executed manually. These scripts must be run in [SQLCMD mode](https://learn.microsoft.com/en-us/ssms/scripting/sqlcmd-scripts-query-editor) in SQL Studio Management Studio or using the [sqlcmd command-line utility](https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility?view=sql-server-ver17&tabs=go%2Cwindows-support&pivots=cs1-powershell).
3. [Post-Deploy](Post-Deploy/) - Contains a data insertion script that populates a clean instance of the Process Director database with the base information required for the system to run without errors. This data includes a default report, some system parameters, default users/groups, and etc. This script is included in the final publish script, but is designed to only make changes/additions whenever they are not already present in the database.
4. [Security](Security/) - Contains basic scripts for creating the default PDAdmin SQL user and assigning its role memberships. These scripts are *not* included in the final publish script and must be run manually, unless the [database setup package](https://github.com/Greenfield-Investments/setup-packages/tree/main/Database) is used in which case they are included and executed automatically when installation is complete.

## Publishing & Deployments
To publish the IWS5.Database project and create a database publishing script, follow these steps:

1. In Visual Studio 2022 or later (for compatibility purposes), right-click the IWS5.Database project in the Solution Explorer and click "Publish".
2. Select the target database connection by clicking the "Edit..." button and either selecting a recent connection or entering the details for a new one.
3. Enter the database name. If you are deploying a fresh copy of the database, use `ProcessDirector`; if you are deploying to an existing database copy (say, to update schemas), use the exact name of that database.
4. Optionally, change the publish script name. This only matters if you intend to copy the publish script from its output folder and transfer it to another server. When in doubt, use either `ProcessDirector.sql` or the default value of `IWS5.Database.sql`.
5. **Important:** if you are deploying to an existing database copy to update schemas, click "Advanced..." and make sure "Block incremental deployment if data loss might occur" is *unchecked*.

There are a few special considerations for publishing the database project:

1. You *must* select a target server for publish script generation, regardless of whether you intend to publish the database to that server.
2. The chosen target server for script generation determines which version of SQL Server the publish script will be compatible with. For example: if the target server is running SQL Server 2022 (v16), the script will only be compatible with other versions of SQL Server v16 (such as SQL Express). The only way to generate a publish script compatible with other versions (say, SQL Server 2025 (v17)) is to select a target server running the required version.

The generated publish script can be executed manually using SQLCMD mode on the target server, but it's much easier to use the [database setup package](https://github.com/Greenfield-Investments/setup-packages/tree/main/Database). Follow the instructions in the setup package's README to include the correct scripts and compile the package for installation on target servers.

## Schema Drift
The notable differences between the FEMA and LPNU branches of IWS5.Database are:

1. The `ATQueue` table in the FEMA branch uses the `Id` column (`int`) as the primary key. This means that queue names are only unique to a process, not across the entire system.
2. The FEMA branch does includes the "Option" folder field type, and thus has the `ATFieldInstanceOption` table.

## Additional Notes
There are a few tables which require some additional explanation:

1. [ATD_CFTemplate](dbo/Tables/ATD_CFTemplate.sql), [ATD_EMARK_DOC_TEMPLATE](dbo/Tables/ATD_EMARK_DOC_TEMPLATE.sql), [ATD_Test_Dates](dbo/Tables/ATD_Test_Dates.sql), [ATD_Test_Integer](dbo/Tables/ATD_Test_Integer.sql), and [ATD_Test_Mixed](dbo/Tables/ATD_Test_Mixed.sql) - These are leftover document template tables that never should have been committed to the repository. It should be safe to remove them from the project, and they should not be dropped by a schema update publish script if used on an existing production database, but it would be wise to exercise caution with these tables.
2. [ATD_DefaultTemplate](dbo/Tables/ATD_DefaultTemplate.sql) - This is the default document template table that should *never* be removed. The post-deploy script creates the default document template, but does not itself create this table.
3. [ATDocumenctReviewStatus](dbo/Tables/ATDocumenctReviewStatus.sql) - This table is spelled incorrectly and should instead be `ATDocumentReviewStatus`. Updating the spelling is possible and relatively trivial for new deployments, however doing so has the potential to break existing production database copies when a schema update publish script is used.
4. [ATDocumentCabinetFiledType](dbo/Tables/ATDocumentCabinetFiledType.sql) - This table is spelled incorrectly and should instead be `ATDocumentCabinetFieldType`. Updating the spelling is possible and relatively trivial for new deployments, however doing so has the potential to break existing production database copies when a schema update publish script is used.
5. [ATScheduledItem-old](dbo/Tables/ATScheduledItem-old.sql) and [ATScheduledItem](dbo/Tables/ATScheduledItem.sql) - These tables are related to the work-in-progress transition to a new scheduling schema for the IWS5.AdminApi project. ATScheduledItem-old has the old schema, and the [classic iAdmin application](https://github.com/Greenfield-Investments/iAdmin) was updated to use it instead of the new ATScheduledItem. The old table should not be removed until the new schema is finalized, at which time the scheduling functionality should be removed from the classic iAdmin entirely in favor of implementation in the new [React iAdmin (react-iAdmin) application](https://github.com/Greenfield-Investments/react-iAdmin).
6. [ATWorkItemArc](dbo/Tables/ATWorkItemArc.sql), [ATWorkItemReferenceArc](dbo/Tables/ATWorkItemReferenceArc.sql), [ATDocumentArc](dbo/Tables/ATDocumentArc.sql), and [ATTrackingRecordArc](dbo/Tables/ATTrackingRecordArc.sql) -  These tables were related to timer processing in the previous version of the Process Director suite and are severely out of date. They can most likely be removed, but have been left in the database project to ensure compatibility.