USE [master]
GO
/****** Object:  Database [MedicalBilling_XXX]    Script Date: 8/11/2025 8:12:48 AM ******/
CREATE DATABASE [MedicalBilling_XXX]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'MedicalBilling_XXX', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLCBS\MSSQL\DATA\MedicalBilling_XXX.mdf' , SIZE = 73728KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'MedicalBilling_XXX_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLCBS\MSSQL\DATA\MedicalBilling_XXX_log.ldf' , SIZE = 73728KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
GO
ALTER DATABASE [MedicalBilling_XXX] SET COMPATIBILITY_LEVEL = 160
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MedicalBilling_XXX].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [MedicalBilling_XXX] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET ARITHABORT OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [MedicalBilling_XXX] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [MedicalBilling_XXX] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET  ENABLE_BROKER 
GO
ALTER DATABASE [MedicalBilling_XXX] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [MedicalBilling_XXX] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET RECOVERY FULL 
GO
ALTER DATABASE [MedicalBilling_XXX] SET  MULTI_USER 
GO
ALTER DATABASE [MedicalBilling_XXX] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [MedicalBilling_XXX] SET DB_CHAINING OFF 
GO
ALTER DATABASE [MedicalBilling_XXX] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [MedicalBilling_XXX] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [MedicalBilling_XXX] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [MedicalBilling_XXX] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO

-- CORRECTED SECTION --
-- 1. Switch to the database you want to work with.
USE [MedicalBilling_XXX]
GO

-- 2. Create the encryption key. 
--    Note: The certificate 'TDE_Cert_T92' must already exist in the master database.
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_T92;
GO

-- 3. Now, you can enable encryption on the database.
ALTER DATABASE [MedicalBilling_XXX] SET ENCRYPTION ON
GO
-- END CORRECTED SECTION --

ALTER DATABASE [MedicalBilling_XXX] SET QUERY_STORE = ON
GO
ALTER DATABASE [MedicalBilling_XXX] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 1000, QUERY_CAPTURE_MODE = AUTO, SIZE_BASED_CLEANUP_MODE = AUTO, MAX_PLANS_PER_QUERY = 200, WAIT_STATS_CAPTURE_MODE = ON)
GO
USE [MedicalBilling_XXX]
GO
ALTER ROLE [db_owner] ADD MEMBER [rbhamilton_sa]
GO
/****** Object:  Table [dbo].[AppointmentReminders]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AppointmentReminders](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[appointment_id] [int] NOT NULL,
	[group_number] [varchar](20) NOT NULL,
	[reminder_type] [varchar](20) NOT NULL,
	[phone_number_encrypted] [varbinary](max) NOT NULL,
	[patient_name_encrypted] [varbinary](max) NULL,
	[message_text_encrypted] [varbinary](max) NULL,
	[esendex_message_id] [varchar](100) NULL,
	[sent_at] [datetime] NULL,
	[delivery_status] [varchar](20) NULL,
	[patient_reply_encrypted] [varbinary](max) NULL,
	[reply_received_at] [datetime] NULL,
	[created_at] [datetime] NOT NULL,
	[matched_message_id] [varchar](100) NULL,
 CONSTRAINT [PK_AppointmentReminders] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Appointments]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Appointments](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[group_number] [varchar](20) NOT NULL,
	[provider_id] [int] NOT NULL,
	[patient_id] [int] NULL,
	[appointment_date] [date] NOT NULL,
	[start_time] [time](7) NOT NULL,
	[end_time] [time](7) NOT NULL,
	[status] [varchar](20) NOT NULL,
	[notes_encrypted] [varbinary](max) NULL,
	[created_at] [datetime] NOT NULL,
	[updated_at] [datetime] NULL,
	[created_by] [varchar](255) NOT NULL,
	[billing_header_id] [int] NULL,
 CONSTRAINT [PK_Appointments] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[BillingHeader]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BillingHeader](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[group_number] [varchar](20) NOT NULL,
	[user_id] [int] NOT NULL,
	[patient_id] [int] NOT NULL,
	[provider_id] [int] NOT NULL,
	[date_of_service] [date] NOT NULL,
	[refdocnumb] [char](4) NULL,
	[refdocname_encrypted] [varbinary](max) NULL,
	[ICD] [char](3) NOT NULL,
	[location] [char](1) NOT NULL,
	[created_at] [datetime] NULL,
	[comment_encrypted] [varbinary](max) NULL,
	[admission_date] [date] NULL,
	[last_date_of_service] [date] NULL,
	[number_of_visits] [int] NULL,
	[claim_type] [varchar](2) NULL,
	[hospitalization_id] [int] NULL,
	[claimno] [int] NULL,
	[diagnosis_description_encrypted] [varbinary](max) NULL,
 CONSTRAINT [PK_BillingHeader] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[BillingLines]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BillingLines](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[header_id] [int] NOT NULL,
	[sequence] [int] NOT NULL,
	[service_code] [varchar](10) NOT NULL,
	[fee_submitted] [decimal](10, 2) NOT NULL,
	[percentage] [int] NOT NULL,
	[units] [int] NOT NULL,
	[start_time] [time](7) NULL,
	[end_time] [time](7) NULL,
 CONSTRAINT [PK_BillingLines] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[BillingSubmissions]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BillingSubmissions](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[group_number] [varchar](20) NOT NULL,
	[file_name] [varchar](100) NOT NULL,
	[prepared_at] [datetime] NOT NULL,
	[starting_claim_number] [int] NOT NULL,
	[next_claim_number] [int] NOT NULL,
	[doctor_count] [int] NOT NULL,
	[total_50_lines] [int] NOT NULL,
	[total_amount_submitted] [decimal](10, 2) NOT NULL,
	[username] [varchar](255) NOT NULL,
	[submissionreport] [varchar](255) NOT NULL,
	[submissiondate] [datetime] NULL,
	[validationfile] [varchar](255) NULL,
	[status] [varchar](20) NULL,
 CONSTRAINT [PK_BillingSubmissions] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ChiefComplaints]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChiefComplaints](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[group_number] [varchar](20) NOT NULL,
	[complaint_text_encrypted] [varbinary](max) NOT NULL,
	[complaint_text_hash] [varchar](256) NOT NULL,
	[usage_count] [int] NULL,
	[created_at] [datetime] NULL,
	[last_used] [datetime] NULL,
 CONSTRAINT [PK_ChiefComplaints] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ClinicEmailSettings]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ClinicEmailSettings](
	[group_number] [varchar](20) NOT NULL,
	[reminder_24hr_subject] [varchar](200) NULL,
	[reminder_24hr_template] [text] NULL,
	[reminder_2hr_subject] [varchar](200) NULL,
	[reminder_2hr_template] [text] NULL,
	[from_name] [varchar](100) NULL,
	[reply_to_email] [varchar](255) NULL,
	[is_active] [bit] NOT NULL,
	[created_at] [datetime] NOT NULL,
	[updated_at] [datetime] NULL,
 CONSTRAINT [PK_ClinicEmailSettings] PRIMARY KEY CLUSTERED 
(
	[group_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Clinics]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Clinics](
	[clinic_number] [varchar](20) NOT NULL,
	[group_number] [varchar](20) NOT NULL,
	[name_encrypted] [varbinary](max) NULL,
	[name_hash] [varchar](256) NULL,
	[address_encrypted] [varbinary](max) NULL,
	[phone_encrypted] [varbinary](max) NULL,
	[fax_encrypted] [varbinary](max) NULL,
	[email_encrypted] [varbinary](max) NULL,
	[api_key_encrypted] [varbinary](max) NULL,
	[city_encrypted] [varbinary](max) NULL,
	[prov_encrypted] [varbinary](max) NULL,
	[postal_encrypted] [varbinary](max) NULL,
	[portal_environment] [varchar](20) NOT NULL,
 CONSTRAINT [PK_Clinics] PRIMARY KEY CLUSTERED 
(
	[clinic_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ClinicSMSSettings]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ClinicSMSSettings](
	[group_number] [varchar](20) NOT NULL,
	[esendex_license_key] [varchar](100) NOT NULL,
	[reminder_24hr_template] [varchar](500) NULL,
	[reminder_2hr_template] [varchar](500) NULL,
	[confirmation_template] [varchar](500) NULL,
	[from_name] [varchar](50) NULL,
	[is_active] [bit] NOT NULL,
	[created_at] [datetime] NOT NULL,
	[updated_at] [datetime] NULL,
 CONSTRAINT [PK_ClinicSMSSettings] PRIMARY KEY CLUSTERED 
(
	[group_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Diag]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Diag](
	[ICD] [varchar](10) NOT NULL,
	[Description] [varchar](max) NOT NULL,
	[ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Diag] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[EmailReminders]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmailReminders](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[appointment_id] [int] NOT NULL,
	[group_number] [varchar](20) NOT NULL,
	[reminder_type] [varchar](20) NOT NULL,
	[email_address_encrypted] [varbinary](max) NOT NULL,
	[patient_name_encrypted] [varbinary](max) NULL,
	[subject_text_encrypted] [varbinary](max) NULL,
	[message_html_encrypted] [varbinary](max) NULL,
	[resend_message_id] [varchar](100) NULL,
	[sent_at] [datetime] NULL,
	[delivery_status] [varchar](20) NULL,
	[created_at] [datetime] NOT NULL,
 CONSTRAINT [PK_EmailReminders] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ExplanationCodes]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExplanationCodes](
	[code] [varchar](10) NOT NULL,
	[description] [text] NOT NULL,
 CONSTRAINT [PK_ExplanationCodes] PRIMARY KEY CLUSTERED 
(
	[code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[fees]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[fees](
	[Service_Code] [varchar](4) NOT NULL,
	[Description] [nvarchar](300) NULL,
	[Fee_Determinant] [nvarchar](50) NULL,
	[Low_Fee] [nvarchar](50) NULL,
	[High_Fee] [nvarchar](50) NULL,
	[Add_on_Indicator] [nvarchar](50) NULL,
	[Anaesthesia_Ind] [nvarchar](50) NULL,
	[Referring_Practitioner_Required] [nvarchar](50) NULL,
	[Start_Time_Required] [nvarchar](50) NULL,
	[Stop_Time_Required] [nvarchar](50) NULL,
	[StartDate] [date] NULL,
	[EndDate] [date] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PasswordResets]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PasswordResets](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[user_id] [int] NOT NULL,
	[code] [varchar](10) NOT NULL,
	[expires_at] [datetime] NOT NULL,
	[used_at] [datetime] NULL,
 CONSTRAINT [PK_PasswordResets] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PatientLastNames]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PatientLastNames](
	[name] [varchar](100) NOT NULL,
	[usage_count] [int] NULL,
	[created_at] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PatientPortalActivityLog]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PatientPortalActivityLog](
	[log_id] [int] IDENTITY(1,1) NOT NULL,
	[portal_user_id] [int] NULL,
	[linked_patient_id] [int] NULL,
	[action] [varchar](100) NOT NULL,
	[details_encrypted] [varbinary](max) NULL,
	[ip_address] [varchar](45) NULL,
	[user_agent] [varchar](500) NULL,
	[timestamp] [datetime] NULL,
 CONSTRAINT [PK_PatientPortalActivityLog] PRIMARY KEY CLUSTERED 
(
	[log_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PatientPortalMessages]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PatientPortalMessages](
	[message_id] [int] IDENTITY(1,1) NOT NULL,
	[portal_user_id] [int] NULL,
	[linked_patient_id] [int] NULL,
	[group_number] [varchar](20) NOT NULL,
	[subject_encrypted] [varbinary](max) NULL,
	[message_encrypted] [varbinary](max) NOT NULL,
	[sender] [varchar](20) NOT NULL,
	[sender_name] [varchar](100) NULL,
	[is_read] [bit] NULL,
	[priority] [varchar](10) NULL,
	[created_at] [datetime] NULL,
	[read_at] [datetime] NULL,
	[reply_to] [int] NULL,
 CONSTRAINT [PK_PatientPortalMessages] PRIMARY KEY CLUSTERED 
(
	[message_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PatientPortalSessions]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PatientPortalSessions](
	[session_id] [varchar](255) NOT NULL,
	[portal_user_id] [int] NOT NULL,
	[created_at] [datetime] NULL,
	[last_activity] [datetime] NULL,
	[ip_address] [varchar](45) NULL,
	[user_agent] [varchar](500) NULL,
	[is_active] [bit] NULL,
 CONSTRAINT [PK_PatientPortalSessions] PRIMARY KEY CLUSTERED 
(
	[session_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PatientPortalUsers]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PatientPortalUsers](
	[portal_user_id] [int] IDENTITY(1,1) NOT NULL,
	[group_number] [varchar](20) NOT NULL,
	[email_encrypted] [varbinary](max) NULL,
	[email_hash] [varchar](256) NULL,
	[password_hash] [varchar](255) NOT NULL,
	[first_name_encrypted] [varbinary](max) NOT NULL,
	[last_name_encrypted] [varbinary](max) NOT NULL,
	[last_name_hash] [varchar](256) NULL,
	[dob_encrypted] [varbinary](max) NULL,
	[sex] [nchar](2) NULL,
	[home_phone_encrypted] [varbinary](max) NULL,
	[work_phone_encrypted] [varbinary](max) NULL,
	[cell_phone_encrypted] [varbinary](max) NULL,
	[cell_verified] [bit] NULL,
	[address_encrypted] [varbinary](max) NULL,
	[city_encrypted] [varbinary](max) NULL,
	[province_encrypted] [varbinary](max) NULL,
	[postal_code_encrypted] [varbinary](max) NULL,
	[phn_encrypted] [varbinary](max) NULL,
	[phn_hash] [varchar](256) NULL,
	[linked_patient_id] [int] NULL,
	[status] [varchar](20) NULL,
	[verification_code] [varchar](10) NULL,
	[verification_expires] [datetime] NULL,
	[phone_verification_code] [varchar](6) NULL,
	[phone_verification_expires] [datetime] NULL,
	[failed_login_attempts] [int] NULL,
	[locked_until] [datetime] NULL,
	[created_at] [datetime] NULL,
	[phn_entered_at] [datetime] NULL,
	[last_login] [datetime] NULL,
	[is_active] [bit] NULL,
	[email_verified] [bit] NOT NULL,
	[email_verification_token] [varchar](255) NULL,
	[email_verification_token_expires] [datetime] NULL,
 CONSTRAINT [PK_PatientPortalUsers] PRIMARY KEY CLUSTERED 
(
	[portal_user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[patients]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[patients](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[last_name_encrypted] [varbinary](max) NULL,
	[first_name_encrypted] [varbinary](max) NULL,
	[last_name_hash] [varchar](256) NULL,
	[phn_hash] [varchar](256) NULL,
	[sex] [nchar](1) NULL,
	[dob_encrypted] [varbinary](max) NULL,
	[phn_encrypted] [varbinary](max) NULL,
	[home_phone_encrypted] [varbinary](max) NULL,
	[work_phone_encrypted] [varbinary](max) NULL,
	[cell_phone_encrypted] [varbinary](max) NULL,
	[cell_verified] [bit] NULL,
	[email_encrypted] [varbinary](max) NULL,
	[email_verified] [bit] NULL,
	[address_encrypted] [varbinary](max) NULL,
	[city_encrypted] [varbinary](max) NULL,
	[province_encrypted] [varbinary](max) NULL,
	[postal_code_encrypted] [varbinary](max) NULL,
	[provider_id] [int] NULL,
	[post_operative_restriction_until] [date] NULL,
	[active] [bit] NOT NULL,
	[original_patient_id] [int] NULL,
	[REF_DOC] [nchar](4) NULL,
	[REFNAME] [nvarchar](50) NULL,
	[ICD_Code] [nchar](3) NULL,
	[DIAG] [nchar](80) NULL,
	[first_name_hash] [varchar](256) NULL,
 CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PatientVitals]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PatientVitals](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[patient_id] [int] NOT NULL,
	[provider_id] [int] NOT NULL,
	[soap_note_id] [int] NULL,
	[recorded_date] [datetime] NOT NULL,
	[recorded_by] [varchar](255) NOT NULL,
	[blood_pressure_systolic] [int] NULL,
	[blood_pressure_diastolic] [int] NULL,
	[heart_rate] [int] NULL,
	[temperature] [decimal](4, 1) NULL,
	[respiratory_rate] [int] NULL,
	[oxygen_saturation] [int] NULL,
	[weight] [decimal](6, 2) NULL,
	[height] [decimal](5, 2) NULL,
	[waist_measurement] [decimal](5, 2) NULL,
	[vision] [varchar](20) NULL,
	[alcohol_consumption] [varchar](100) NULL,
	[drug_use] [varchar](255) NULL,
	[misc_1_label] [varchar](50) NULL,
	[misc_1_value_encrypted] [varbinary](max) NULL,
	[misc_2_label] [varchar](50) NULL,
	[misc_2_value_encrypted] [varbinary](max) NULL,
	[misc_3_label] [varchar](50) NULL,
	[misc_3_value_encrypted] [varbinary](max) NULL,
	[misc_4_label] [varchar](50) NULL,
	[misc_4_value_encrypted] [varbinary](max) NULL,
	[misc_5_label] [varchar](50) NULL,
	[misc_5_value_encrypted] [varbinary](max) NULL,
	[misc_6_label] [varchar](50) NULL,
	[misc_6_value_encrypted] [varbinary](max) NULL,
	[misc_7_label] [varchar](50) NULL,
	[misc_7_value_encrypted] [varbinary](max) NULL,
	[misc_8_label] [varchar](50) NULL,
	[misc_8_value_encrypted] [varbinary](max) NULL,
	[misc_9_label] [varchar](50) NULL,
	[misc_9_value_encrypted] [varbinary](max) NULL,
	[misc_10_label] [varchar](50) NULL,
	[misc_10_value_encrypted] [varbinary](max) NULL,
	[notes_encrypted] [varbinary](max) NULL,
	[created_at] [datetime] NOT NULL,
	[updated_at] [datetime] NULL,
 CONSTRAINT [PK_PatientVitals] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Providers]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Providers](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[group_number] [varchar](20) NOT NULL,
	[Clinic] [nchar](10) NOT NULL,
	[ProviderName_encrypted] [varbinary](max) NOT NULL,
	[BillingNumber_encrypted] [varbinary](max) NOT NULL,
	[ProviderName_hash] [varchar](256) NULL,
	[BillingNumber_hash] [varchar](256) NOT NULL,
	[DefaultSex] [nchar](1) NULL,
	[DefaultICD] [nchar](3) NULL,
	[DefaultService] [nchar](4) NULL,
	[DefaultLocation] [nchar](1) NULL,
	[Specialist] [nchar](1) NOT NULL,
	[address_encrypted] [varbinary](max) NULL,
	[city_prov_encrypted] [varbinary](max) NULL,
	[postal_encrypted] [varbinary](max) NULL,
	[email_encrypted] [varbinary](max) NULL,
	[phone_encrypted] [varbinary](max) NULL,
	[fax_encrypted] [varbinary](max) NULL,
	[cell_encrypted] [varbinary](max) NULL,
	[Mode] [nchar](1) NULL,
	[CorpLetter] [nchar](1) NULL,
	[CorpName_encrypted] [varbinary](max) NULL,
	[min_notice_hours] [int] NULL,
 CONSTRAINT [PK_Providers] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[RefDoc]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RefDoc](
	[Doctor_Number] [nchar](4) NOT NULL,
	[Doctor_Name] [nvarchar](50) NOT NULL,
	[City] [nvarchar](50) NOT NULL,
	[Specialty] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_RefDoc] PRIMARY KEY CLUSTERED 
(
	[Doctor_Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[RemittanceFiles]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RemittanceFiles](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[group_number] [varchar](20) NOT NULL,
	[filename] [varchar](100) NOT NULL,
	[run_code] [varchar](10) NOT NULL,
	[file_created_date] [datetime] NOT NULL,
	[date_picked_up] [datetime] NULL,
	[is_reprint] [bit] NULL,
	[file_size] [int] NULL,
	[processed] [bit] NULL,
	[date_processed] [datetime] NULL,
	[report_filename] [varchar](255) NULL,
 CONSTRAINT [PK_RemittanceFiles] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ScheduleTemplates]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ScheduleTemplates](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[group_number] [varchar](20) NOT NULL,
	[provider_id] [int] NOT NULL,
	[day_of_week] [smallint] NOT NULL,
	[start_time] [time](7) NOT NULL,
	[end_time] [time](7) NOT NULL,
	[appointment_length] [int] NOT NULL,
	[is_default] [bit] NOT NULL,
	[created_at] [datetime] NOT NULL,
	[updated_at] [datetime] NULL,
	[status] [varchar](20) NULL,
	[comments] [varchar](255) NULL,
 CONSTRAINT [PK_ScheduleTemplates] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SMS_Replies]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SMS_Replies](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[phone_number_encrypted] [varbinary](max) NOT NULL,
	[message_encrypted] [varbinary](max) NOT NULL,
	[message_id] [varchar](100) NULL,
	[received_at] [datetime] NOT NULL,
	[processed] [bit] NULL,
	[processed_at] [datetime] NULL,
 CONSTRAINT [PK_SMS_Replies] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SOAPNotes]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SOAPNotes](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[group_number] [varchar](20) NOT NULL,
	[patient_id] [int] NOT NULL,
	[appointment_id] [int] NULL,
	[provider_id] [int] NOT NULL,
	[visit_date] [date] NOT NULL,
	[subjective_encrypted] [varbinary](max) NULL,
	[objective_encrypted] [varbinary](max) NULL,
	[assessment_encrypted] [varbinary](max) NULL,
	[plan_encrypted] [varbinary](max) NULL,
	[created_at] [datetime] NOT NULL,
	[updated_at] [datetime] NULL,
	[created_by] [varchar](255) NOT NULL,
	[is_finalized] [bit] NULL,
	[chief_complaint_encrypted] [varbinary](max) NULL,
	[note_type] [varchar](20) NOT NULL,
 CONSTRAINT [PK_SOAPNotes] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SubmittedClaims]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SubmittedClaims](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[group_number] [varchar](20) NOT NULL,
	[service_code] [varchar](10) NULL,
	[patient_id] [int] NULL,
	[ret_code] [varchar](10) NULL,
	[date] [datetime] NULL,
	[units] [int] NULL,
	[location] [char](1) NULL,
	[paid_amount] [decimal](10, 2) NULL,
	[billed_amount] [decimal](10, 2) NULL,
	[copay] [decimal](10, 2) NULL,
	[pst] [decimal](10, 2) NULL,
	[gst] [decimal](10, 2) NULL,
	[claim_total] [nchar](10) NULL,
	[date_prepared] [datetime] NULL,
	[run_code] [varchar](10) NULL,
	[provider] [int] NULL,
	[ex_code] [varchar](255) NULL,
	[agent] [varchar](255) NULL,
	[referring_doctor_number] [varchar](10) NULL,
	[claimno] [int] NULL,
	[sequence] [int] NULL,
	[status] [varchar](50) NULL,
	[icd] [varchar](10) NULL,
	[icd_desc] [varchar](255) NULL,
	[comment_encrypted] [varbinary](max) NULL,
	[cps_claim_number] [varchar](50) NULL,
	[ex_code_reviewed] [bit] NOT NULL,
	[ex_code_notes_encrypted] [varbinary](max) NULL,
 CONSTRAINT [PK_SubmittedClaims] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Users]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[group_number] [varchar](20) NOT NULL,
	[username_encrypted] [varbinary](max) NOT NULL,
	[username_hash] [varchar](256) NOT NULL,
	[password_hash] [varchar](255) NOT NULL,
	[cell_encrypted] [varbinary](max) NULL,
	[email_encrypted] [varbinary](max) NULL,
	[role] [varchar](20) NULL,
	[status] [varchar](20) NULL,
	[created_at] [datetime] NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[VitalsSettings]    Script Date: 8/11/2025 8:12:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[VitalsSettings](
	[group_number] [varchar](20) NOT NULL,
	[misc_1_label] [varchar](50) NULL,
	[misc_2_label] [varchar](50) NULL,
	[misc_3_label] [varchar](50) NULL,
	[misc_4_label] [varchar](50) NULL,
	[misc_5_label] [varchar](50) NULL,
	[misc_6_label] [varchar](50) NULL,
	[misc_7_label] [varchar](50) NULL,
	[misc_8_label] [varchar](50) NULL,
	[misc_9_label] [varchar](50) NULL,
	[misc_10_label] [varchar](50) NULL,
	[created_at] [datetime] NOT NULL,
	[updated_at] [datetime] NULL,
 CONSTRAINT [PK_VitalsSettings] PRIMARY KEY CLUSTERED 
(
	[group_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_PatientLastNames_name]    Script Date: 8/11/2025 8:12:49 AM ******/
CREATE NONCLUSTERED INDEX [IX_PatientLastNames_name] ON [dbo].[PatientLastNames]
(
	[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_PatientPortalUsers_email_hash]    Script Date: 8/11/2025 8:12:49 AM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_PatientPortalUsers_email_hash] ON [dbo].[PatientPortalUsers]
(
	[email_hash] ASC
)
WHERE ([email_hash] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_PatientPortalUsers_phn_hash]    Script Date: 8/11/2025 8:12:49 AM ******/
CREATE NONCLUSTERED INDEX [IX_PatientPortalUsers_phn_hash] ON [dbo].[PatientPortalUsers]
(
	[phn_hash] ASC
)
WHERE ([phn_hash] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_Patients_last_name_hash]    Script Date: 8/11/2025 8:12:49 AM ******/
CREATE NONCLUSTERED INDEX [IX_Patients_last_name_hash] ON [dbo].[patients]
(
	[last_name_hash] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_Patients_phn_hash]    Script Date: 8/11/2025 8:12:49 AM ******/
CREATE NONCLUSTERED INDEX [IX_Patients_phn_hash] ON [dbo].[patients]
(
	[phn_hash] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_Providers_BillingNumber_hash]    Script Date: 8/11/2025 8:12:49 AM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Providers_BillingNumber_hash] ON [dbo].[Providers]
(
	[BillingNumber_hash] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_Providers_ProviderName_hash]    Script Date: 8/11/2025 8:12:49 AM ******/
CREATE NONCLUSTERED INDEX [IX_Providers_ProviderName_hash] ON [dbo].[Providers]
(
	[ProviderName_hash] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_Users_username_hash]    Script Date: 8/11/2025 8:12:49 AM ******/
CREATE NONCLUSTERED INDEX [IX_Users_username_hash] ON [dbo].[Users]
(
	[username_hash] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AppointmentReminders] ADD  CONSTRAINT [DF_AppointmentReminders_delivery_status]  DEFAULT ('pending') FOR [delivery_status]
GO
ALTER TABLE [dbo].[AppointmentReminders] ADD  CONSTRAINT [DF_AppointmentReminders_created_at]  DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[Appointments] ADD  CONSTRAINT [DF_Appointments_status]  DEFAULT ('available') FOR [status]
GO
ALTER TABLE [dbo].[Appointments] ADD  CONSTRAINT [DF_Appointments_created_at]  DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[BillingHeader] ADD  CONSTRAINT [DF_BillingHeader_created_at]  DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[BillingSubmissions] ADD  CONSTRAINT [DF_BillingSubmissions_prepared_at]  DEFAULT (getdate()) FOR [prepared_at]
GO
ALTER TABLE [dbo].[ChiefComplaints] ADD  CONSTRAINT [DF_ChiefComplaints_usage_count]  DEFAULT ((1)) FOR [usage_count]
GO
ALTER TABLE [dbo].[ChiefComplaints] ADD  CONSTRAINT [DF_ChiefComplaints_created_at]  DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[ChiefComplaints] ADD  CONSTRAINT [DF_ChiefComplaints_last_used]  DEFAULT (getdate()) FOR [last_used]
GO
ALTER TABLE [dbo].[ClinicEmailSettings] ADD  CONSTRAINT [DF_ClinicEmailSettings_is_active]  DEFAULT ((1)) FOR [is_active]
GO
ALTER TABLE [dbo].[ClinicEmailSettings] ADD  CONSTRAINT [DF_ClinicEmailSettings_created_at]  DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[Clinics] ADD  CONSTRAINT [DF_Clinics_portal_environment]  DEFAULT ('production') FOR [portal_environment]
GO
ALTER TABLE [dbo].[ClinicSMSSettings] ADD  CONSTRAINT [DF_ClinicSMSSettings_is_active]  DEFAULT ((1)) FOR [is_active]
GO
ALTER TABLE [dbo].[ClinicSMSSettings] ADD  CONSTRAINT [DF_ClinicSMSSettings_created_at]  DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[EmailReminders] ADD  CONSTRAINT [DF_EmailReminders_delivery_status]  DEFAULT ('pending') FOR [delivery_status]
GO
ALTER TABLE [dbo].[EmailReminders] ADD  CONSTRAINT [DF_EmailReminders_created_at]  DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[PatientLastNames] ADD  DEFAULT ((1)) FOR [usage_count]
GO
ALTER TABLE [dbo].[PatientLastNames] ADD  DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[PatientPortalActivityLog] ADD  CONSTRAINT [DF_PatientPortalActivityLog_timestamp]  DEFAULT (getdate()) FOR [timestamp]
GO
ALTER TABLE [dbo].[PatientPortalMessages] ADD  CONSTRAINT [DF_PatientPortalMessages_is_read]  DEFAULT ((0)) FOR [is_read]
GO
ALTER TABLE [dbo].[PatientPortalMessages] ADD  CONSTRAINT [DF_PatientPortalMessages_priority]  DEFAULT ('normal') FOR [priority]
GO
ALTER TABLE [dbo].[PatientPortalMessages] ADD  CONSTRAINT [DF_PatientPortalMessages_created_at]  DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[PatientPortalSessions] ADD  CONSTRAINT [DF_PatientPortalSessions_created_at]  DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[PatientPortalSessions] ADD  CONSTRAINT [DF_PatientPortalSessions_last_activity]  DEFAULT (getdate()) FOR [last_activity]
GO
ALTER TABLE [dbo].[PatientPortalSessions] ADD  CONSTRAINT [DF_PatientPortalSessions_is_active]  DEFAULT ((1)) FOR [is_active]
GO
ALTER TABLE [dbo].[PatientPortalUsers] ADD  CONSTRAINT [DF_PatientPortalUsers_cell_verified]  DEFAULT ((0)) FOR [cell_verified]
GO
ALTER TABLE [dbo].[PatientPortalUsers] ADD  CONSTRAINT [DF_PatientPortalUsers_status]  DEFAULT ('provisional') FOR [status]
GO
ALTER TABLE [dbo].[PatientPortalUsers] ADD  CONSTRAINT [DF_PatientPortalUsers_failed_login_attempts]  DEFAULT ((0)) FOR [failed_login_attempts]
GO
ALTER TABLE [dbo].[PatientPortalUsers] ADD  CONSTRAINT [DF_PatientPortalUsers_created_at]  DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[PatientPortalUsers] ADD  CONSTRAINT [DF_PatientPortalUsers_is_active]  DEFAULT ((1)) FOR [is_active]
GO
ALTER TABLE [dbo].[PatientPortalUsers] ADD  CONSTRAINT [DF_PatientPortalUsers_email_verified]  DEFAULT ((0)) FOR [email_verified]
GO
ALTER TABLE [dbo].[patients] ADD  CONSTRAINT [DF_patients_active]  DEFAULT ((1)) FOR [active]
GO
ALTER TABLE [dbo].[PatientVitals] ADD  CONSTRAINT [DF_PatientVitals_recorded_date]  DEFAULT (getdate()) FOR [recorded_date]
GO
ALTER TABLE [dbo].[PatientVitals] ADD  CONSTRAINT [DF_PatientVitals_created_at]  DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[Providers] ADD  CONSTRAINT [DF_Providers_min_notice_hours]  DEFAULT ((0)) FOR [min_notice_hours]
GO
ALTER TABLE [dbo].[RemittanceFiles] ADD  CONSTRAINT [DF_RemittanceFiles_date_picked_up]  DEFAULT (getdate()) FOR [date_picked_up]
GO
ALTER TABLE [dbo].[RemittanceFiles] ADD  CONSTRAINT [DF_RemittanceFiles_is_reprint]  DEFAULT ((0)) FOR [is_reprint]
GO
ALTER TABLE [dbo].[RemittanceFiles] ADD  CONSTRAINT [DF_RemittanceFiles_processed]  DEFAULT ((0)) FOR [processed]
GO
ALTER TABLE [dbo].[ScheduleTemplates] ADD  CONSTRAINT [DF_ScheduleTemplates_is_default]  DEFAULT ((0)) FOR [is_default]
GO
ALTER TABLE [dbo].[ScheduleTemplates] ADD  CONSTRAINT [DF_ScheduleTemplates_created_at]  DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[ScheduleTemplates] ADD  CONSTRAINT [DF_ScheduleTemplates_Status]  DEFAULT ('available') FOR [status]
GO
ALTER TABLE [dbo].[SMS_Replies] ADD  CONSTRAINT [DF_SMS_Replies_processed]  DEFAULT ((0)) FOR [processed]
GO
ALTER TABLE [dbo].[SOAPNotes] ADD  CONSTRAINT [DF_SOAPNotes_created_at]  DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[SOAPNotes] ADD  CONSTRAINT [DF_SOAPNotes_is_finalized]  DEFAULT ((0)) FOR [is_finalized]
GO
ALTER TABLE [dbo].[SubmittedClaims] ADD  CONSTRAINT [DF_SubmittedClaims_ex_code_reviewed]  DEFAULT ((0)) FOR [ex_code_reviewed]
GO
ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_created_at]  DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[VitalsSettings] ADD  CONSTRAINT [DF_VitalsSettings_created_at]  DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[AppointmentReminders]  WITH CHECK ADD  CONSTRAINT [FK_AppointmentReminders_Appointments] FOREIGN KEY([appointment_id])
REFERENCES [dbo].[Appointments] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AppointmentReminders] CHECK CONSTRAINT [FK_AppointmentReminders_Appointments]
GO
ALTER TABLE [dbo].[Appointments]  WITH CHECK ADD  CONSTRAINT [FK_Appointments_Patients] FOREIGN KEY([patient_id])
REFERENCES [dbo].[patients] ([ID])
GO
ALTER TABLE [dbo].[Appointments] CHECK CONSTRAINT [FK_Appointments_Patients]
GO
ALTER TABLE [dbo].[Appointments]  WITH CHECK ADD  CONSTRAINT [FK_Appointments_Providers] FOREIGN KEY([provider_id])
REFERENCES [dbo].[Providers] ([ID])
GO
ALTER TABLE [dbo].[Appointments] CHECK CONSTRAINT [FK_Appointments_Providers]
GO
ALTER TABLE [dbo].[BillingHeader]  WITH CHECK ADD  CONSTRAINT [FK_BillingHeader_Patients] FOREIGN KEY([patient_id])
REFERENCES [dbo].[patients] ([ID])
GO
ALTER TABLE [dbo].[BillingHeader] CHECK CONSTRAINT [FK_BillingHeader_Patients]
GO
ALTER TABLE [dbo].[BillingHeader]  WITH CHECK ADD  CONSTRAINT [FK_BillingHeader_Providers] FOREIGN KEY([provider_id])
REFERENCES [dbo].[Providers] ([ID])
GO
ALTER TABLE [dbo].[BillingHeader] CHECK CONSTRAINT [FK_BillingHeader_Providers]
GO
ALTER TABLE [dbo].[BillingHeader]  WITH CHECK ADD   CONSTRAINT [FK_BillingHeader_Users] FOREIGN KEY([user_id])
REFERENCES [dbo].[Users] ([ID])
GO
ALTER TABLE [dbo].[BillingHeader] CHECK CONSTRAINT [FK_BillingHeader_Users]
GO
ALTER TABLE [dbo].[BillingLines]  WITH CHECK ADD  CONSTRAINT [FK_BillingLines_BillingHeader] FOREIGN KEY([header_id])
REFERENCES [dbo].[BillingHeader] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[BillingLines] CHECK CONSTRAINT [FK_BillingLines_BillingHeader]
GO
ALTER TABLE [dbo].[EmailReminders]  WITH CHECK ADD  CONSTRAINT [FK_EmailReminders_Appointments] FOREIGN KEY([appointment_id])
REFERENCES [dbo].[Appointments] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[EmailReminders] CHECK CONSTRAINT [FK_EmailReminders_Appointments]
GO
ALTER TABLE [dbo].[PasswordResets]  WITH CHECK ADD  CONSTRAINT [FK_PasswordResets_Users] FOREIGN KEY([user_id])
REFERENCES [dbo].[Users] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[PasswordResets] CHECK CONSTRAINT [FK_PasswordResets_Users]
GO
ALTER TABLE [dbo].[PatientPortalMessages]  WITH CHECK ADD  CONSTRAINT [FK_PatientPortalMessages_PatientPortalUsers] FOREIGN KEY([portal_user_id])
REFERENCES [dbo].[PatientPortalUsers] ([portal_user_id])
GO
ALTER TABLE [dbo].[PatientPortalMessages] CHECK CONSTRAINT [FK_PatientPortalMessages_PatientPortalUsers]
GO
ALTER TABLE [dbo].[PatientPortalMessages]  WITH CHECK ADD  CONSTRAINT [FK_PatientPortalMessages_Patients] FOREIGN KEY([linked_patient_id])
REFERENCES [dbo].[patients] ([ID])
GO
ALTER TABLE [dbo].[PatientPortalMessages] CHECK CONSTRAINT [FK_PatientPortalMessages_Patients]
GO
ALTER TABLE [dbo].[PatientPortalSessions]  WITH CHECK ADD  CONSTRAINT [FK_PatientPortalSessions_PatientPortalUsers] FOREIGN KEY([portal_user_id])
REFERENCES [dbo].[PatientPortalUsers] ([portal_user_id])
GO
ALTER TABLE [dbo].[PatientPortalSessions] CHECK CONSTRAINT [FK_PatientPortalSessions_PatientPortalUsers]
GO
ALTER TABLE [dbo].[PatientPortalUsers]  WITH CHECK ADD  CONSTRAINT [FK_PatientPortalUsers_Patients] FOREIGN KEY([linked_patient_id])
REFERENCES [dbo].[patients] ([ID])
GO
ALTER TABLE [dbo].[PatientPortalUsers] CHECK CONSTRAINT [FK_PatientPortalUsers_Patients]
GO
ALTER TABLE [dbo].[PatientVitals]  WITH CHECK ADD  CONSTRAINT [FK_PatientVitals_Patients] FOREIGN KEY([patient_id])
REFERENCES [dbo].[patients] ([ID])
GO
ALTER TABLE [dbo].[PatientVitals] CHECK CONSTRAINT [FK_PatientVitals_Patients]
GO
ALTER TABLE [dbo].[PatientVitals]  WITH CHECK ADD  CONSTRAINT [FK_PatientVitals_Providers] FOREIGN KEY([provider_id])
REFERENCES [dbo].[Providers] ([ID])
GO
ALTER TABLE [dbo].[PatientVitals] CHECK CONSTRAINT [FK_PatientVitals_Providers]
GO
ALTER TABLE [dbo].[PatientVitals]  WITH CHECK ADD  CONSTRAINT [FK_PatientVitals_SOAPNotes] FOREIGN KEY([soap_note_id])
REFERENCES [dbo].[SOAPNotes] ([ID])
GO
ALTER TABLE [dbo].[PatientVitals] CHECK CONSTRAINT [FK_PatientVitals_SOAPNotes]
GO
ALTER TABLE [dbo].[ScheduleTemplates]  WITH CHECK ADD  CONSTRAINT [FK_ScheduleTemplates_Providers] FOREIGN KEY([provider_id])
REFERENCES [dbo].[Providers] ([ID])
GO
ALTER TABLE [dbo].[ScheduleTemplates] CHECK CONSTRAINT [FK_ScheduleTemplates_Providers]
GO
ALTER TABLE [dbo].[SOAPNotes]  WITH CHECK ADD  CONSTRAINT [FK_SOAPNotes_Appointments] FOREIGN KEY([appointment_id])
REFERENCES [dbo].[Appointments] ([ID])
GO
ALTER TABLE [dbo].[SOAPNotes] CHECK CONSTRAINT [FK_SOAPNotes_Appointments]
GO
ALTER TABLE [dbo].[SOAPNotes]  WITH CHECK ADD  CONSTRAINT [FK_SOAPNotes_Patients] FOREIGN KEY([patient_id])
REFERENCES [dbo].[patients] ([ID])
GO
ALTER TABLE [dbo].[SOAPNotes] CHECK CONSTRAINT [FK_SOAPNotes_Patients]
GO
ALTER TABLE [dbo].[SOAPNotes]  WITH CHECK ADD  CONSTRAINT [FK_SOAPNotes_Providers] FOREIGN KEY([provider_id])
REFERENCES [dbo].[Providers] ([ID])
GO
ALTER TABLE [dbo].[SOAPNotes] CHECK CONSTRAINT [FK_SOAPNotes_Providers]
GO
ALTER TABLE [dbo].[BillingLines]  WITH CHECK ADD  CONSTRAINT [CK_BillingLines_percentage] CHECK  (([percentage]=(100) OR [percentage]=(75) OR [percentage]=(50)))
GO
ALTER TABLE [dbo].[BillingLines] CHECK CONSTRAINT [CK_BillingLines_percentage]
GO
USE [master]
GO
ALTER DATABASE [MedicalBilling_XXX] SET  READ_WRITE 
GO