nettown_backend/migrations/migrations_v2_unipile.sql

ALTER TABLE integration
    ADD COLUMN IF NOT EXISTS user_id INT NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS operator_id INT NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS integration_metadata JSONB NOT NULL DEFAULT '{}'::jsonb;

ALTER TABLE conversation
    ALTER COLUMN conversation_closed_at SET DEFAULT 'infinity',
    ALTER COLUMN conversation_sla_deadline SET DEFAULT 'infinity',
    ALTER COLUMN conversation_last_message_at SET DEFAULT NOW(),
    ALTER COLUMN conversation_last_message_preview SET DEFAULT '',
    ALTER COLUMN conversation_last_message_from SET DEFAULT 'client',
    ALTER COLUMN conversation_impact_value SET DEFAULT 0,
    ALTER COLUMN conversation_ticket_value SET DEFAULT 0,
    ALTER COLUMN conversation_conversion_chance SET DEFAULT 0,
    ALTER COLUMN conversation_optimum_window SET DEFAULT '';

ALTER TABLE message
    DROP CONSTRAINT IF EXISTS fk_quoted_message;

ALTER TABLE message
    ALTER COLUMN quoted_message_id SET DEFAULT 0,
    ALTER COLUMN message_provider_id SET DEFAULT '',
    ALTER COLUMN message_sender_provider_id SET DEFAULT '',
    ALTER COLUMN message_content SET DEFAULT '',
    ALTER COLUMN message_seen SET DEFAULT FALSE,
    ALTER COLUMN message_delivered SET DEFAULT FALSE,
    ALTER COLUMN message_event_type SET DEFAULT 0;

ALTER TABLE webhook_event
    ALTER COLUMN webhook_event_processed_at SET DEFAULT NOW();

CREATE UNIQUE INDEX IF NOT EXISTS uq_integration_account_active
    ON integration (integration_account_id)
    WHERE integration_deleted_at = 'infinity';

CREATE UNIQUE INDEX IF NOT EXISTS uq_client_provider_company_active
    ON client (company_id, client_provider_id)
    WHERE client_deleted_at = 'infinity';

ALTER TABLE conversation
    ADD CONSTRAINT uq_conversation_integration_external_deleted
    UNIQUE (integration_id, conversation_external_id, conversation_deleted_at);

ALTER TABLE message
    ADD CONSTRAINT uq_message_conversation_external_deleted
    UNIQUE (conversation_id, message_external_id, message_deleted_at);

ALTER TABLE message_attachment
    ADD CONSTRAINT uq_attachment_message_external_deleted
    UNIQUE (message_id, attachment_external_id, attachment_deleted_at);

ALTER TABLE conversation_participant
    ADD CONSTRAINT uq_participant_conversation_provider_deleted
    UNIQUE (conversation_id, participant_provider_id, participant_deleted_at);

ALTER TABLE webhook_event
    ADD CONSTRAINT uq_webhook_integration_event_external_deleted
    UNIQUE (integration_id, webhook_event_type, webhook_event_external_id, webhook_event_deleted_at);