-- Example 1: Create a share
-- A share is a named object that contains pointers to database objects
-- The share itself does not store data; it references objects in the provider database
CREATE SHARE analytics_share
    COMMENT = 'Share for analytics data';

-- Example 2: Add objects to share
-- GRANT USAGE is required on DATABASE and SCHEMA before sharing tables/views
-- GRANT SELECT shares the actual table data with consumers
GRANT USAGE ON DATABASE analytics_prod TO SHARE analytics_share;
GRANT USAGE ON SCHEMA analytics_prod.shared_data TO SHARE analytics_share;
GRANT SELECT ON TABLE analytics_prod.shared_data.sales_data TO SHARE analytics_share;
GRANT SELECT ON TABLE analytics_prod.shared_data.customer_dim TO SHARE analytics_share;
-- You can also share views (useful for pre-aggregated or secured data)
GRANT SELECT ON VIEW analytics_prod.shared_data.sales_summary TO SHARE analytics_share;

-- Example 3: Add consumer accounts to share
-- Multiple accounts can be added in a single statement
-- Use ACCOUNT Locator or Organization Name + Account Name
ALTER SHARE analytics_share ADD ACCOUNTS = consumer_account_1, consumer_account_2;

-- Example 4: Remove a consumer from a share
ALTER SHARE analytics_share REMOVE ACCOUNTS = consumer_account_2;

-- Example 5: Modify share properties
ALTER SHARE analytics_share SET COMMENT = 'Updated share for Q4 analytics';

-- Example 6: View share details
DESC SHARE analytics_share;
SHOW SHARES LIKE 'analytics_%';

-- Example 7: Consumer creates database from share
-- The database name can differ from the share name
-- Consumer can only create ONE database per share
CREATE DATABASE analytics_share_db
    FROM SHARE provider_account.analytics_share;

-- Example 8: Query shared data
-- Shared data is read-only; no INSERT/UPDATE/DELETE allowed
-- Consumer can JOIN shared tables with local tables
SELECT 
    s.product,
    s.region,
    SUM(s.amount) as total_sales,
    c.customer_segment
FROM analytics_share_db.shared_data.sales_data s
JOIN analytics_share_db.shared_data.customer_dim c
ON s.customer_id = c.customer_id
GROUP BY 1, 2, 4;

-- Example 9: Create view on shared data (local enrichment)
-- Consumer can create views that combine shared and local data
CREATE VIEW local_analytics AS
SELECT 
    s.*,
    l.local_enrichment_column
FROM analytics_share_db.shared_data.sales_data s
LEFT JOIN local_enrichment_table l
ON s.id = l.id;

-- Example 10: Create a clone of shared data for modification
-- Consumer can clone shared tables if they need to modify data
CREATE TABLE sales_clone CLONE analytics_share_db.shared_data.sales_data;
-- Now consumer can modify sales_clone independently
UPDATE sales_clone SET amount = amount * 1.1 WHERE region = 'US';

-- Example 11: Monitor sharing usage (Provider perspective)
-- SHARING_HISTORY shows which consumers are querying shared data
SELECT 
    share_name,
    consumer_account,
    consumer_region,
    queries_executed,
    bytes_scanned,
    last_query_time
FROM TABLE(INFORMATION_SCHEMA.SHARING_HISTORY(
    SHARE_NAME => 'analytics_share',
    START_TIME => DATEADD(day, -30, CURRENT_TIMESTAMP())
))
ORDER BY last_query_time DESC;

-- Example 12: List all shares in your account
SHOW SHARES LIKE 'analytics_%';

-- List shares from specific provider
SHOW SHARES IN ACCOUNT provider_account;

-- Get detailed share information
DESC SHARE analytics_share;

-- Example 13: Monitor shared database usage
SELECT 
    share_name,
    consumer_account,
    database_name,
    schema_name,
    table_name,
    bytes_scanned,
    queries_executed
FROM TABLE(INFORMATION_SCHEMA.SHARING_HISTORY(
    START_TIME => DATEADD(day, -7, CURRENT_TIMESTAMP())
))
ORDER BY bytes_scanned DESC;

-- Example 14: Create a Marketplace listing
-- Providers publish data products for consumer discovery
CREATE LISTING weather_data_listing
    TITLE = 'Global Weather Forecasts'
    DESCRIPTION = 'Daily weather forecasts with 5-year history'
    COMMENT = 'Weather data for analytics'
    PROTOCOL = SNOWFLAKE          -- Protocol for data delivery
    IMPORT = TRUE                 -- Consumer can import data locally
    QUERY_ANYWARE = TRUE          -- Consumer can query without importing
    TERMS = 'Free for non-commercial use'
    CATEGORY = 'Weather'
    PROVIDER_NAMESPACE = 'my_org';

-- Example 15: Browse available Marketplace listings
-- Consumers search and discover data products
SHOW LISTINGS LIKE '%weather%';

-- Get listing details
DESC LISTING weather_data_listing;

-- Example 16: Get data from Marketplace
-- Consumer accepts terms and creates local database from listing
CREATE DATABASE weather_data
    FROM LISTING weather_data_listing;

-- Query imported Marketplace data
SELECT * FROM weather_data.forecasts 
WHERE city = 'New York' 
AND forecast_date = CURRENT_DATE();

-- Example 17: Create a private Data Exchange
-- Data Exchange enables controlled multi-party sharing
CREATE DATA EXCHANGE partner_exchange
    TYPE = PRIVATE                   -- PRIVATE, SECURE, or PUBLIC
    COMMENT = 'Private exchange for partner data collaboration';

-- Example 18: Add participants to exchange
-- PROVIDER participants share data; CONSUMER participants access it
ALTER DATA EXCHANGE partner_exchange ADD PARTICIPANT 
    PARTICIPANT_TYPE = PROVIDER
    ACCOUNT_NAME = partner_financial_account;

ALTER DATA EXCHANGE partner_exchange ADD PARTICIPANT 
    PARTICIPANT_TYPE = CONSUMER
    ACCOUNT_NAME = customer_bank_account;

-- Example 19: List exchange participants
SHOW DATA EXCHANGES LIKE 'partner_%';

-- Example 20: Provider publishes to exchange
-- After adding to exchange, provider creates a listing within the exchange
CREATE LISTING financial_data_listing
    TITLE = 'Real-time Market Data'
    DATA_EXCHANGE = partner_exchange
    PROTOCOL = SNOWFLAKE
    QUERY_ANYWARE = TRUE;

-- Example 21: Consumer accesses exchange listing
CREATE DATABASE market_data
    FROM LISTING financial_data_listing;

-- Example 22: Monitor exchange usage
SELECT 
    listing_name,
    consumer_account,
    queries_executed,
    bytes_scanned,
    last_access_time
FROM TABLE(INFORMATION_SCHEMA.SHARING_HISTORY(
    START_TIME => DATEADD(day, -30, CURRENT_TIMESTAMP())
))
WHERE share_name LIKE '%exchange%'
ORDER BY bytes_scanned DESC;

-- Example 23: Revoke share access
-- Provider can remove consumer access at any time
ALTER SHARE analytics_share REMOVE ACCOUNTS = old_consumer_account;

-- Example 24: Drop share (must remove all consumers and objects first)
-- Unshare all objects
REVOKE SELECT ON TABLE analytics_prod.shared_data.sales_data FROM SHARE analytics_share;
REVOKE USAGE ON SCHEMA analytics_prod.shared_data FROM SHARE analytics_share;
REVOKE USAGE ON DATABASE analytics_prod FROM SHARE analytics_share;
-- Remove all consumers
ALTER SHARE analytics_share REMOVE ACCOUNTS = consumer_account_1;
-- Drop the share
DROP SHARE analytics_share;