-- Copyright 2026 Kingsrook, LLC. Licensed under the Apache License, Version 2.0.
-- https://www.apache.org/licenses/LICENSE-2.0
-- QBit CRM 0.2.0 P1 PostgreSQL reference schema (34 tables).
-- QQQ table names equal SQL names; Java field names map to snake_case columns.
-- Unique constraints match QQQ metadata. Relationship columns are indexed;
-- foreign-key constraints are deliberately not imposed (the QQQ/host layer owns
-- validation and deletion policy, including polymorphic and optional references).
-- Optional tenancy: separately include crm-tenancy.changelog.sql with context crm-tenancy.
-- With tenancy off, do not include it; this base schema has no tenant_key columns.

CREATE TABLE IF NOT EXISTS crm_activity (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   activity_type_id INTEGER NOT NULL,
   subject VARCHAR(500) NOT NULL,
   description TEXT,
   contact_id INTEGER,
   company_id INTEGER,
   deal_id INTEGER,
   owner_user_id TEXT NOT NULL,
   assigned_to_user_id TEXT,
   start_date TIMESTAMP WITHOUT TIME ZONE,
   end_date TIMESTAMP WITHOUT TIME ZONE,
   due_date TIMESTAMP WITHOUT TIME ZONE,
   completed_date TIMESTAMP WITHOUT TIME ZONE,
   is_completed BOOLEAN DEFAULT FALSE NOT NULL,
   priority INTEGER,
   duration_minutes INTEGER,
   outcome_id INTEGER,
   location VARCHAR(500),
   conference_link VARCHAR(1000),
   direction INTEGER,
   is_reminder_set BOOLEAN DEFAULT FALSE,
   reminder_date TIMESTAMP WITHOUT TIME ZONE,
   external_calendar_id VARCHAR(500),
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE
);

CREATE INDEX IF NOT EXISTS ix_crm_activity_activity_type_id ON crm_activity (activity_type_id);
CREATE INDEX IF NOT EXISTS ix_crm_activity_contact_id ON crm_activity (contact_id);
CREATE INDEX IF NOT EXISTS ix_crm_activity_company_id ON crm_activity (company_id);
CREATE INDEX IF NOT EXISTS ix_crm_activity_deal_id ON crm_activity (deal_id);
CREATE INDEX IF NOT EXISTS ix_crm_activity_owner_user_id ON crm_activity (owner_user_id);
CREATE INDEX IF NOT EXISTS ix_crm_activity_assigned_to_user_id ON crm_activity (assigned_to_user_id);
CREATE INDEX IF NOT EXISTS ix_crm_activity_outcome_id ON crm_activity (outcome_id);
CREATE INDEX IF NOT EXISTS ix_crm_activity_external_calendar_id ON crm_activity (external_calendar_id);

CREATE TABLE IF NOT EXISTS crm_activity_outcome (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   activity_type_id INTEGER NOT NULL,
   name VARCHAR(100) NOT NULL,
   sort_order INTEGER NOT NULL,
   is_active BOOLEAN DEFAULT TRUE NOT NULL,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_activity_outcome_1 UNIQUE (activity_type_id, name)
);

CREATE INDEX IF NOT EXISTS ix_crm_activity_outcome_activity_type_id ON crm_activity_outcome (activity_type_id);

CREATE TABLE IF NOT EXISTS crm_activity_participant (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   activity_id INTEGER NOT NULL,
   contact_id INTEGER,
   user_id TEXT,
   role INTEGER,
   create_date TIMESTAMP WITHOUT TIME ZONE
);

CREATE INDEX IF NOT EXISTS ix_crm_activity_participant_activity_id ON crm_activity_participant (activity_id);
CREATE INDEX IF NOT EXISTS ix_crm_activity_participant_contact_id ON crm_activity_participant (contact_id);
CREATE INDEX IF NOT EXISTS ix_crm_activity_participant_user_id ON crm_activity_participant (user_id);

CREATE TABLE IF NOT EXISTS crm_activity_type (
   code VARCHAR(50) UNIQUE,
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name VARCHAR(100) NOT NULL,
   icon_name VARCHAR(50),
   is_system BOOLEAN DEFAULT FALSE NOT NULL,
   sort_order INTEGER NOT NULL,
   is_active BOOLEAN DEFAULT TRUE NOT NULL,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_activity_type_1 UNIQUE (name)
);

CREATE TABLE IF NOT EXISTS crm_assignment_rule (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name VARCHAR(250) NOT NULL,
   description TEXT,
   entity_type INTEGER NOT NULL,
   criteria_json TEXT,
   assign_strategy INTEGER NOT NULL,
   assign_to_user_id TEXT,
   is_active BOOLEAN DEFAULT TRUE NOT NULL,
   sort_order INTEGER NOT NULL,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE
);

CREATE INDEX IF NOT EXISTS ix_crm_assignment_rule_assign_to_user_id ON crm_assignment_rule (assign_to_user_id);

CREATE TABLE IF NOT EXISTS crm_assignment_rule_member (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   assignment_rule_id INTEGER NOT NULL,
   user_id TEXT NOT NULL,
   sort_order INTEGER NOT NULL,
   is_active BOOLEAN DEFAULT TRUE NOT NULL,
   last_assigned_date TIMESTAMP WITHOUT TIME ZONE,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_assignment_rule_member_1 UNIQUE (assignment_rule_id, user_id)
);

CREATE INDEX IF NOT EXISTS ix_crm_assignment_rule_member_assignment_rule_id ON crm_assignment_rule_member (assignment_rule_id);
CREATE INDEX IF NOT EXISTS ix_crm_assignment_rule_member_user_id ON crm_assignment_rule_member (user_id);

CREATE TABLE IF NOT EXISTS crm_company (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name VARCHAR(250) NOT NULL,
   domain VARCHAR(250),
   company_type INTEGER,
   industry_id INTEGER,
   employee_count INTEGER,
   annual_revenue DECIMAL(20,6),
   annual_revenue_currency_code VARCHAR(3) DEFAULT 'USD',
   phone VARCHAR(50),
   website VARCHAR(500),
   address_line1 VARCHAR(250),
   address_line2 VARCHAR(250),
   city VARCHAR(100),
   state VARCHAR(100),
   postal_code VARCHAR(20),
   country VARCHAR(100),
   owner_user_id TEXT NOT NULL,
   parent_company_id INTEGER,
   description TEXT,
   last_activity_date TIMESTAMP WITHOUT TIME ZONE,
   last_contacted_date TIMESTAMP WITHOUT TIME ZONE,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE
);

CREATE INDEX IF NOT EXISTS ix_crm_company_industry_id ON crm_company (industry_id);
CREATE INDEX IF NOT EXISTS ix_crm_company_owner_user_id ON crm_company (owner_user_id);
CREATE INDEX IF NOT EXISTS ix_crm_company_parent_company_id ON crm_company (parent_company_id);

CREATE TABLE IF NOT EXISTS crm_consent_record (
   consent_text TEXT,
   evidence_form_submission_id INTEGER,
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   contact_id INTEGER NOT NULL,
   consent_type INTEGER NOT NULL,
   status INTEGER NOT NULL,
   legal_basis INTEGER,
   source VARCHAR(250),
   consent_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
   expiry_date TIMESTAMP WITHOUT TIME ZONE,
   create_date TIMESTAMP WITHOUT TIME ZONE
);

CREATE INDEX IF NOT EXISTS ix_crm_consent_record_contact_id ON crm_consent_record (contact_id);

CREATE TABLE IF NOT EXISTS crm_contact (
   first_touch_utm_source VARCHAR(500),
   first_touch_utm_medium VARCHAR(500),
   first_touch_utm_campaign VARCHAR(500),
   first_touch_utm_content VARCHAR(500),
   first_touch_utm_term VARCHAR(500),
   first_touch_landing_page VARCHAR(1000),
   first_touch_referrer VARCHAR(1000),
   first_touch_date TIMESTAMP WITHOUT TIME ZONE,
   last_touch_utm_source VARCHAR(500),
   last_touch_utm_medium VARCHAR(500),
   last_touch_utm_campaign VARCHAR(500),
   last_touch_utm_content VARCHAR(500),
   last_touch_utm_term VARCHAR(500),
   last_touch_landing_page VARCHAR(1000),
   last_touch_referrer VARCHAR(1000),
   last_touch_date TIMESTAMP WITHOUT TIME ZONE,
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   first_name VARCHAR(100),
   last_name VARCHAR(100),
   email VARCHAR(320),
   secondary_email VARCHAR(320),
   phone VARCHAR(50),
   mobile_phone VARCHAR(50),
   job_title VARCHAR(150),
   department VARCHAR(100),
   company_id INTEGER,
   lifecycle_stage_id INTEGER,
   lead_source_id INTEGER,
   owner_user_id TEXT,
   address_line1 VARCHAR(250),
   address_line2 VARCHAR(250),
   city VARCHAR(100),
   state VARCHAR(100),
   postal_code VARCHAR(20),
   country VARCHAR(100),
   timezone VARCHAR(50),
   preferred_language VARCHAR(10),
   website VARCHAR(500),
   linkedin_url VARCHAR(500),
   description TEXT,
   lead_score INTEGER DEFAULT 0,
   do_not_email BOOLEAN DEFAULT FALSE,
   do_not_call BOOLEAN DEFAULT FALSE,
   unsubscribe_date TIMESTAMP WITHOUT TIME ZONE,
   last_activity_date TIMESTAMP WITHOUT TIME ZONE,
   last_contacted_date TIMESTAMP WITHOUT TIME ZONE,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE
);

CREATE INDEX IF NOT EXISTS ix_crm_contact_company_id ON crm_contact (company_id);
CREATE INDEX IF NOT EXISTS ix_crm_contact_lifecycle_stage_id ON crm_contact (lifecycle_stage_id);
CREATE INDEX IF NOT EXISTS ix_crm_contact_lead_source_id ON crm_contact (lead_source_id);
CREATE INDEX IF NOT EXISTS ix_crm_contact_owner_user_id ON crm_contact (owner_user_id);

CREATE TABLE IF NOT EXISTS crm_contact_role (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name VARCHAR(100) NOT NULL,
   sort_order INTEGER NOT NULL,
   is_active BOOLEAN DEFAULT TRUE NOT NULL,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_contact_role_1 UNIQUE (name)
);

CREATE TABLE IF NOT EXISTS crm_currency (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   currency_code VARCHAR(3) NOT NULL,
   name VARCHAR(100) NOT NULL,
   symbol VARCHAR(5),
   exchange_rate_to_base DECIMAL(20,6) DEFAULT 1.0 NOT NULL,
   is_base_currency BOOLEAN DEFAULT FALSE NOT NULL,
   is_active BOOLEAN DEFAULT TRUE NOT NULL,
   rate_last_updated_date TIMESTAMP WITHOUT TIME ZONE,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_currency_1 UNIQUE (currency_code)
);

CREATE TABLE IF NOT EXISTS crm_deal (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name VARCHAR(250) NOT NULL,
   pipeline_id INTEGER NOT NULL,
   pipeline_stage_id INTEGER NOT NULL,
   amount DECIMAL(20,6),
   currency_code VARCHAR(3) DEFAULT 'USD',
   amount_in_base_currency DECIMAL(20,6),
   probability_override_pct INTEGER,
   weighted_amount DECIMAL(20,6),
   company_id INTEGER,
   primary_contact_id INTEGER,
   owner_user_id TEXT,
   lead_source_id INTEGER,
   expected_close_date DATE,
   actual_close_date DATE,
   win_loss_reason_id INTEGER,
   stage_entered_date TIMESTAMP WITHOUT TIME ZONE,
   description TEXT,
   priority INTEGER,
   last_activity_date TIMESTAMP WITHOUT TIME ZONE,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE
);

CREATE INDEX IF NOT EXISTS ix_crm_deal_pipeline_id ON crm_deal (pipeline_id);
CREATE INDEX IF NOT EXISTS ix_crm_deal_pipeline_stage_id ON crm_deal (pipeline_stage_id);
CREATE INDEX IF NOT EXISTS ix_crm_deal_primary_contact_id ON crm_deal (primary_contact_id);
CREATE INDEX IF NOT EXISTS ix_crm_deal_company_id ON crm_deal (company_id);
CREATE INDEX IF NOT EXISTS ix_crm_deal_owner_user_id ON crm_deal (owner_user_id);
CREATE INDEX IF NOT EXISTS ix_crm_deal_lead_source_id ON crm_deal (lead_source_id);
CREATE INDEX IF NOT EXISTS ix_crm_deal_win_loss_reason_id ON crm_deal (win_loss_reason_id);

CREATE TABLE IF NOT EXISTS crm_deal_contact (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   deal_id INTEGER NOT NULL,
   contact_id INTEGER NOT NULL,
   contact_role_id INTEGER,
   is_primary BOOLEAN DEFAULT FALSE NOT NULL,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_deal_contact_1 UNIQUE (deal_id, contact_id)
);

CREATE INDEX IF NOT EXISTS ix_crm_deal_contact_deal_id ON crm_deal_contact (deal_id);
CREATE INDEX IF NOT EXISTS ix_crm_deal_contact_contact_id ON crm_deal_contact (contact_id);
CREATE INDEX IF NOT EXISTS ix_crm_deal_contact_contact_role_id ON crm_deal_contact (contact_role_id);

CREATE TABLE IF NOT EXISTS crm_deal_product (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   deal_id INTEGER NOT NULL,
   product_id INTEGER NOT NULL,
   quantity DECIMAL(20,6) DEFAULT 1 NOT NULL,
   unit_price DECIMAL(20,6) NOT NULL,
   discount_pct DECIMAL(20,6) DEFAULT 0,
   total_amount DECIMAL(20,6),
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE
);

CREATE INDEX IF NOT EXISTS ix_crm_deal_product_deal_id ON crm_deal_product (deal_id);
CREATE INDEX IF NOT EXISTS ix_crm_deal_product_product_id ON crm_deal_product (product_id);

CREATE TABLE IF NOT EXISTS crm_deal_stage_history (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   deal_id INTEGER NOT NULL,
   from_stage_id INTEGER,
   to_stage_id INTEGER NOT NULL,
   user_id TEXT NOT NULL,
   transition_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
   duration_in_from_stage_days INTEGER,
   create_date TIMESTAMP WITHOUT TIME ZONE
);

CREATE INDEX IF NOT EXISTS ix_crm_deal_stage_history_deal_id ON crm_deal_stage_history (deal_id);
CREATE INDEX IF NOT EXISTS ix_crm_deal_stage_history_from_stage_id ON crm_deal_stage_history (from_stage_id);
CREATE INDEX IF NOT EXISTS ix_crm_deal_stage_history_to_stage_id ON crm_deal_stage_history (to_stage_id);
CREATE INDEX IF NOT EXISTS ix_crm_deal_stage_history_user_id ON crm_deal_stage_history (user_id);

CREATE TABLE IF NOT EXISTS crm_email_list (
   list_key VARCHAR(250) NOT NULL UNIQUE,
   require_double_opt_in BOOLEAN DEFAULT TRUE NOT NULL,
   consent_text TEXT,
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name VARCHAR(250) NOT NULL,
   slug VARCHAR(100) NOT NULL,
   description TEXT,
   is_active BOOLEAN DEFAULT TRUE NOT NULL,
   is_public BOOLEAN DEFAULT TRUE NOT NULL,
   sort_order INTEGER NOT NULL,
   member_count INTEGER DEFAULT 0,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_email_list_1 UNIQUE (name),
   CONSTRAINT uq_crm_email_list_2 UNIQUE (slug)
);

CREATE TABLE IF NOT EXISTS crm_email_list_member (
   confirmation_token_hash VARCHAR(64),
   confirmation_sent_date TIMESTAMP WITHOUT TIME ZONE,
   confirmed_date TIMESTAMP WITHOUT TIME ZONE,
   source VARCHAR(250),
   consent_text TEXT,
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   email_list_id INTEGER NOT NULL,
   contact_id INTEGER,
   email VARCHAR(320) NOT NULL,
   first_name VARCHAR(100),
   status INTEGER DEFAULT 1 NOT NULL,
   subscribed_date TIMESTAMP WITHOUT TIME ZONE,
   unsubscribed_date TIMESTAMP WITHOUT TIME ZONE,
   subscription_source VARCHAR(250),
   form_submission_id INTEGER,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_email_list_member_1 UNIQUE (email_list_id, email)
);

CREATE INDEX IF NOT EXISTS ix_crm_email_list_member_email_list_id ON crm_email_list_member (email_list_id);
CREATE INDEX IF NOT EXISTS ix_crm_email_list_member_contact_id ON crm_email_list_member (contact_id);
CREATE INDEX IF NOT EXISTS ix_crm_email_list_member_form_submission_id ON crm_email_list_member (form_submission_id);

CREATE TABLE IF NOT EXISTS crm_email_message (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   idempotency_key VARCHAR(250) UNIQUE,
   email_message_id VARCHAR(100),
   delivered_date TIMESTAMP WITHOUT TIME ZONE,
   bounced_date TIMESTAMP WITHOUT TIME ZONE,
   activity_id INTEGER,
   contact_id INTEGER,
   from_address VARCHAR(250),
   from_name VARCHAR(100),
   to_addresses TEXT NOT NULL,
   cc_addresses TEXT,
   bcc_addresses TEXT,
   subject VARCHAR(500) NOT NULL,
   body_html TEXT,
   body_text TEXT,
   sent_date TIMESTAMP WITHOUT TIME ZONE,
   thread_id VARCHAR(250),
   email_template_id INTEGER,
   sequence_enrollment_id INTEGER,
   direction INTEGER NOT NULL,
   status INTEGER,
   is_tracked BOOLEAN DEFAULT TRUE,
   open_count INTEGER DEFAULT 0,
   click_count INTEGER DEFAULT 0,
   first_opened_date TIMESTAMP WITHOUT TIME ZONE,
   first_clicked_date TIMESTAMP WITHOUT TIME ZONE,
   bounce_type INTEGER,
   is_spam_complaint BOOLEAN DEFAULT FALSE,
   reply_to_email_id INTEGER,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE
);

CREATE INDEX IF NOT EXISTS ix_crm_email_message_email_message_id ON crm_email_message (email_message_id);
CREATE INDEX IF NOT EXISTS ix_crm_email_message_contact_id ON crm_email_message (contact_id);
CREATE INDEX IF NOT EXISTS ix_crm_email_message_activity_id ON crm_email_message (activity_id);
CREATE INDEX IF NOT EXISTS ix_crm_email_message_thread_id ON crm_email_message (thread_id);
CREATE INDEX IF NOT EXISTS ix_crm_email_message_email_template_id ON crm_email_message (email_template_id);
CREATE INDEX IF NOT EXISTS ix_crm_email_message_sequence_enrollment_id ON crm_email_message (sequence_enrollment_id);
CREATE INDEX IF NOT EXISTS ix_crm_email_message_reply_to_email_id ON crm_email_message (reply_to_email_id);

CREATE TABLE IF NOT EXISTS crm_email_sequence (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name VARCHAR(250) NOT NULL,
   description TEXT,
   owner_user_id TEXT NOT NULL,
   is_active BOOLEAN DEFAULT TRUE NOT NULL,
   total_steps INTEGER,
   business_days_only BOOLEAN DEFAULT TRUE NOT NULL,
   send_window_start_hour INTEGER,
   send_window_end_hour INTEGER,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE
);

CREATE INDEX IF NOT EXISTS ix_crm_email_sequence_owner_user_id ON crm_email_sequence (owner_user_id);

CREATE TABLE IF NOT EXISTS crm_email_template (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name VARCHAR(250) NOT NULL,
   subject VARCHAR(500) NOT NULL,
   body_html TEXT NOT NULL,
   body_text TEXT,
   category VARCHAR(100),
   owner_user_id TEXT NOT NULL,
   is_shared BOOLEAN DEFAULT FALSE NOT NULL,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE
);

CREATE INDEX IF NOT EXISTS ix_crm_email_template_owner_user_id ON crm_email_template (owner_user_id);

CREATE TABLE IF NOT EXISTS crm_external_mapping (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   entity_type INTEGER NOT NULL,
   entity_id INTEGER NOT NULL,
   external_system VARCHAR(100) NOT NULL,
   external_id VARCHAR(500) NOT NULL,
   last_sync_date TIMESTAMP WITHOUT TIME ZONE,
   sync_status INTEGER,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_external_mapping_1 UNIQUE (entity_type, entity_id, external_system)
);

CREATE INDEX IF NOT EXISTS ix_crm_external_mapping_entity_id ON crm_external_mapping (entity_id);
CREATE INDEX IF NOT EXISTS ix_crm_external_mapping_external_id ON crm_external_mapping (external_id);

CREATE TABLE IF NOT EXISTS crm_form_submission (
   form_key VARCHAR(250) NOT NULL,
   marketing_consent BOOLEAN DEFAULT FALSE,
   consent_text TEXT,
   landing_page VARCHAR(1000),
   visitor_key VARCHAR(64),
   client_hash VARCHAR(64),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   form_type INTEGER NOT NULL,
   email VARCHAR(320) NOT NULL,
   first_name VARCHAR(100),
   last_name VARCHAR(100),
   company VARCHAR(250),
   phone VARCHAR(50),
   message TEXT,
   source_url VARCHAR(1000),
   utm_source VARCHAR(500),
   utm_medium VARCHAR(500),
   utm_campaign VARCHAR(500),
   utm_term VARCHAR(500),
   utm_content VARCHAR(500),
   referrer VARCHAR(1000),
   contact_id INTEGER,
   conversion_status INTEGER DEFAULT 1 NOT NULL,
   converted_date TIMESTAMP WITHOUT TIME ZONE,
   converted_by_user_id TEXT,
   metadata TEXT,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE
);

CREATE INDEX IF NOT EXISTS ix_crm_form_submission_contact_id ON crm_form_submission (contact_id);
CREATE INDEX IF NOT EXISTS ix_crm_form_submission_converted_by_user_id ON crm_form_submission (converted_by_user_id);

CREATE TABLE IF NOT EXISTS crm_industry (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name VARCHAR(100) NOT NULL,
   sort_order INTEGER NOT NULL,
   is_active BOOLEAN DEFAULT TRUE NOT NULL,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_industry_1 UNIQUE (name)
);

CREATE TABLE IF NOT EXISTS crm_lead_score_rule (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name VARCHAR(250) NOT NULL,
   description TEXT,
   entity_type INTEGER NOT NULL,
   field_path VARCHAR(250) NOT NULL,
   operator INTEGER NOT NULL,
   field_value VARCHAR(500),
   score_adjustment INTEGER NOT NULL,
   is_active BOOLEAN DEFAULT TRUE NOT NULL,
   sort_order INTEGER NOT NULL,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE
);

CREATE TABLE IF NOT EXISTS crm_lead_source (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name VARCHAR(100) NOT NULL,
   sort_order INTEGER NOT NULL,
   is_active BOOLEAN DEFAULT TRUE NOT NULL,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_lead_source_1 UNIQUE (name)
);

CREATE TABLE IF NOT EXISTS crm_lifecycle_stage (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name VARCHAR(100) NOT NULL,
   sort_order INTEGER NOT NULL,
   is_active BOOLEAN DEFAULT TRUE NOT NULL,
   description VARCHAR(500),
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_lifecycle_stage_1 UNIQUE (name)
);

CREATE TABLE IF NOT EXISTS crm_pipeline (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name VARCHAR(100) NOT NULL,
   description VARCHAR(500),
   is_default BOOLEAN DEFAULT FALSE NOT NULL,
   is_active BOOLEAN DEFAULT TRUE NOT NULL,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_pipeline_1 UNIQUE (name)
);

CREATE TABLE IF NOT EXISTS crm_pipeline_stage (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   pipeline_id INTEGER NOT NULL,
   name VARCHAR(100) NOT NULL,
   sort_order INTEGER NOT NULL,
   probability_pct INTEGER DEFAULT 0 NOT NULL,
   rot_days INTEGER,
   required_fields_json TEXT,
   is_closed_won BOOLEAN DEFAULT FALSE NOT NULL,
   is_closed_lost BOOLEAN DEFAULT FALSE NOT NULL,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_pipeline_stage_1 UNIQUE (pipeline_id, name),
   CONSTRAINT uq_crm_pipeline_stage_2 UNIQUE (pipeline_id, sort_order)
);

CREATE INDEX IF NOT EXISTS ix_crm_pipeline_stage_pipeline_id ON crm_pipeline_stage (pipeline_id);

CREATE TABLE IF NOT EXISTS crm_product (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name VARCHAR(250) NOT NULL,
   sku VARCHAR(100),
   description TEXT,
   unit_price DECIMAL(20,6) NOT NULL,
   currency_code VARCHAR(3) DEFAULT 'USD',
   is_active BOOLEAN DEFAULT TRUE NOT NULL,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_product_1 UNIQUE (sku)
);

CREATE TABLE IF NOT EXISTS crm_sales_goal (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   user_id TEXT NOT NULL,
   pipeline_id INTEGER,
   period_type INTEGER NOT NULL,
   period_start DATE NOT NULL,
   period_end DATE NOT NULL,
   target_amount DECIMAL(20,6) NOT NULL,
   currency_code VARCHAR(3) DEFAULT 'USD',
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_sales_goal_1 UNIQUE (user_id, pipeline_id, period_type, period_start)
);

CREATE INDEX IF NOT EXISTS ix_crm_sales_goal_user_id ON crm_sales_goal (user_id);
CREATE INDEX IF NOT EXISTS ix_crm_sales_goal_pipeline_id ON crm_sales_goal (pipeline_id);

CREATE TABLE IF NOT EXISTS crm_sequence_enrollment (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   sequence_id INTEGER NOT NULL,
   contact_id INTEGER NOT NULL,
   deal_id INTEGER,
   current_step_number INTEGER DEFAULT 0 NOT NULL,
   status INTEGER NOT NULL,
   enrolled_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
   enrolled_by_user_id TEXT NOT NULL,
   completed_date TIMESTAMP WITHOUT TIME ZONE,
   unenrolled_date TIMESTAMP WITHOUT TIME ZONE,
   unenroll_reason VARCHAR(250),
   next_step_date TIMESTAMP WITHOUT TIME ZONE,
   failure_count INTEGER DEFAULT 0,
   last_failure_message VARCHAR(1000),
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_sequence_enrollment_1 UNIQUE (sequence_id, contact_id)
);

CREATE INDEX IF NOT EXISTS ix_crm_sequence_enrollment_sequence_id ON crm_sequence_enrollment (sequence_id);
CREATE INDEX IF NOT EXISTS ix_crm_sequence_enrollment_contact_id ON crm_sequence_enrollment (contact_id);
CREATE INDEX IF NOT EXISTS ix_crm_sequence_enrollment_deal_id ON crm_sequence_enrollment (deal_id);
CREATE INDEX IF NOT EXISTS ix_crm_sequence_enrollment_enrolled_by_user_id ON crm_sequence_enrollment (enrolled_by_user_id);

CREATE TABLE IF NOT EXISTS crm_sequence_step (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   sequence_id INTEGER NOT NULL,
   step_number INTEGER NOT NULL,
   step_type INTEGER NOT NULL,
   delay_days INTEGER DEFAULT 0 NOT NULL,
   delay_hours INTEGER DEFAULT 0,
   email_template_id INTEGER,
   task_subject VARCHAR(500),
   task_description TEXT,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_sequence_step_1 UNIQUE (sequence_id, step_number)
);

CREATE INDEX IF NOT EXISTS ix_crm_sequence_step_sequence_id ON crm_sequence_step (sequence_id);
CREATE INDEX IF NOT EXISTS ix_crm_sequence_step_email_template_id ON crm_sequence_step (email_template_id);

CREATE TABLE IF NOT EXISTS crm_win_loss_reason (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name VARCHAR(100) NOT NULL,
   type INTEGER NOT NULL,
   sort_order INTEGER NOT NULL,
   is_active BOOLEAN DEFAULT TRUE NOT NULL,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT uq_crm_win_loss_reason_1 UNIQUE (name)
);

CREATE INDEX IF NOT EXISTS ix_crm_consent_record_evidence_form_submission_id ON crm_consent_record (evidence_form_submission_id);

CREATE TABLE IF NOT EXISTS crm_job_state (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name VARCHAR(250) NOT NULL UNIQUE,
   last_success TIMESTAMP WITHOUT TIME ZONE,
   last_full_pass TIMESTAMP WITHOUT TIME ZONE
);