Snowflake

CREATE WAREHOUSE bridged_wh
  WAREHOUSE_SIZE = 'SMALL'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'Warehouse for Bridged integration operations';

CREATE DATABASE bridged_integration
  COMMENT = 'Database for Bridged data sync operations';

CREATE SCHEMA bridged_integration.bridged_data
  COMMENT = 'Schema for Bridged customer and conversation data';

CREATE USER bridged_user
  PASSWORD = 'temporary_password'  -- Will use key pair authentication
  DEFAULT_ROLE = 'bridged_role'
  DEFAULT_WAREHOUSE = 'bridged_wh'
  DEFAULT_NAMESPACE = 'bridged_integration.bridged_data'
  MUST_CHANGE_PASSWORD = FALSE
  COMMENT = 'Service account for Bridged integration';

CREATE ROLE bridged_role;

GRANT USAGE ON WAREHOUSE bridged_wh TO ROLE bridged_role;
GRANT USAGE ON DATABASE bridged_integration TO ROLE bridged_role;
GRANT USAGE ON SCHEMA bridged_integration.bridged_data TO ROLE bridged_role;
GRANT CREATE TABLE ON SCHEMA bridged_integration.bridged_data TO ROLE bridged_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA bridged_integration.bridged_data TO ROLE bridged_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON FUTURE TABLES IN SCHEMA bridged_integration.bridged_data TO ROLE bridged_role;

GRANT ROLE bridged_role TO USER bridged_user;

# Generate a 2048-bit private key
openssl genrsa -out bridged_private_key.p8 2048

# Generate a corresponding public key
openssl rsa -in bridged_private_key.p8 -pubout -out bridged_public_key.pub

# Generate a private key in PKCS#8 format (required by Snowflake)
openssl pkcs8 -topk8 -inform PEM -in bridged_private_key.p8 -outform PEM -out bridged_private_key_pk8.p8 -nocrypt

ALTER USER bridged_user SET RSA_PUBLIC_KEY = '-----BEGIN PUBLIC KEY-----
<your_public_key_content>
-----END PUBLIC KEY-----';

-- Test key pair authentication (run from a machine with the private key)
-- This should connect successfully without a password

SELECT 
    customer_id,
    email,
    phone,
    first_name,
    last_name,
    last_interaction_date,
    lead_score
FROM bridged_integration.bridged_data.customer_profiles
WHERE opt_in_status = 'subscribed'
  AND last_interaction_date > DATEADD(day, -30, CURRENT_DATE())
LIMIT 50;

SELECT * FROM bridged_integration.bridged_data.customer_profiles
WHERE updated_at > :last_sync_timestamp
LIMIT 50;

-- Customer engagement events table
CREATE TABLE IF NOT EXISTS bridged_integration.bridged_data.customer_engagement (
    event_id VARCHAR(100) PRIMARY KEY,
    customer_id VARCHAR(100),
    customer_email VARCHAR(255),
    customer_phone VARCHAR(50),
    event_type VARCHAR(50),
    channel VARCHAR(50),
    conversation_id VARCHAR(100),
    message_content TEXT,
    engagement_timestamp TIMESTAMP_NTZ,
    metadata VARIANT,
    created_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

-- Lead scores table
CREATE TABLE IF NOT EXISTS bridged_integration.bridged_data.lead_scores (
    lead_id VARCHAR(100) PRIMARY KEY,
    score INTEGER,
    score_source VARCHAR(50),
    qualified BOOLEAN,
    qualification_date TIMESTAMP_NTZ,
    last_updated TIMESTAMP_NTZ
);

-- Upsert example (Bridged handles this automatically)
MERGE INTO bridged_integration.bridged_data.lead_scores AS target
USING (SELECT * FROM temp_staging) AS source
ON target.lead_id = source.lead_id
WHEN MATCHED THEN UPDATE SET
    target.score = source.score,
    target.qualified = source.qualified,
    target.last_updated = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT
    (lead_id, score, score_source, qualified, qualification_date, last_updated)
    VALUES (source.lead_id, source.score, source.score_source, source.qualified, source.qualification_date, CURRENT_TIMESTAMP());

-- Check that Bridged user has correct permissions
SHOW GRANTS TO USER bridged_user;

-- Test write access
INSERT INTO bridged_integration.bridged_data.lead_scores (lead_id, score, qualified)
VALUES ('test_lead_001', 85, FALSE);

-- Verify the insert
SELECT * FROM bridged_integration.bridged_data.lead_scores WHERE lead_id = 'test_lead_001';

ALTER USER bridged_user SET RSA_PUBLIC_KEY_2 = 'new_public_key';

ALTER USER bridged_user UNSET RSA_PUBLIC_KEY;