-- CREATING A CUSTOM ROLE HIERARCHY (best practice)
USE ROLE USERADMIN;

-- Create domain roles
CREATE ROLE bookings_read_role;       -- Can only read booking data
CREATE ROLE bookings_write_role;      -- Can read + write
CREATE ROLE analyst_role;             -- Cross-domain read access

-- Grant privileges to roles (done by SECURITYADMIN)
USE ROLE SECURITYADMIN;

GRANT USAGE ON DATABASE analytics_db TO ROLE bookings_read_role;
GRANT USAGE ON SCHEMA analytics_db.bookings TO ROLE bookings_read_role;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics_db.bookings TO ROLE bookings_read_role;
-- Future tables: automatically grant when new tables are created
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics_db.bookings TO ROLE bookings_read_role;

-- Hierarchy: write role inherits from read role
GRANT ROLE bookings_read_role TO ROLE bookings_write_role;
GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA analytics_db.bookings TO ROLE bookings_write_role;

-- Analyst role gets read across schemas
GRANT ROLE bookings_read_role TO ROLE analyst_role;

-- Best practice: assign custom roles UP to SYSADMIN (so SYSADMIN owns them)
GRANT ROLE bookings_read_role TO ROLE SYSADMIN;
GRANT ROLE bookings_write_role TO ROLE SYSADMIN;

-- Assign roles to users
GRANT ROLE analyst_role TO USER krishna_yadav;
GRANT ROLE bookings_write_role TO USER data_pipeline_sp;  -- Service Principal

-- User switches roles
USE ROLE analyst_role;
SELECT * FROM analytics_db.bookings.fact_flights;  -- Works!

-- STEP 1: Create a masking policy
CREATE MASKING POLICY email_mask AS (val STRING) RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() IN ('HR_ROLE', 'PRIVACY_OFFICER_ROLE')
            THEN val                        -- Show full email to privileged roles
        WHEN CURRENT_ROLE() IN ('ANALYST_ROLE')
            THEN REGEXP_REPLACE(val, '.+\@', '***@')  -- Show ***@domain.com
        ELSE
            '**REDACTED**'                 -- Everyone else: fully redacted
    END;
-- This policy is a SQL function — runs at query time for every row

-- STEP 2: Apply masking policy to a column
ALTER TABLE passengers
    MODIFY COLUMN email
    SET MASKING POLICY email_mask;
-- Now the email column is masked based on the querying role

-- Test: as ANALYST_ROLE
USE ROLE analyst_role;
SELECT email FROM passengers;
-- Returns: ***@gmail.com (masked)

-- Test: as HR_ROLE
USE ROLE hr_role;
SELECT email FROM passengers;
-- Returns: krishna@gmail.com (real value)

-- MULTIPLE PII COLUMNS:
ALTER TABLE passengers MODIFY COLUMN phone SET MASKING POLICY phone_mask;
ALTER TABLE passengers MODIFY COLUMN passport_no SET MASKING POLICY passport_mask;

-- VIEW: See all masking policies applied
SELECT * FROM INFORMATION_SCHEMA.POLICY_REFERENCES
WHERE POLICY_KIND = 'MASKING_POLICY';

-- STEP 1: Create a mapping table (user role → airline they can see)
CREATE TABLE airline_access_mapping (
    role_name     VARCHAR,
    airline_code  VARCHAR
);
INSERT INTO airline_access_mapping VALUES
    ('LUFTHANSA_ROLE', 'LH'),
    ('AIR_INDIA_ROLE', 'AI'),
    ('EMIRATES_ROLE',  'EK'),
    ('ADMIN_ROLE',     NULL);    -- NULL = see ALL airlines

-- STEP 2: Create Row Access Policy
CREATE ROW ACCESS POLICY airline_row_policy
    AS (airline_code VARCHAR) RETURNS BOOLEAN ->
    EXISTS (
        SELECT 1
        FROM airline_access_mapping m
        WHERE m.role_name = CURRENT_ROLE()
          AND (m.airline_code = airline_code   -- Match role to airline
               OR m.airline_code IS NULL)       -- Or admin (sees all)
    );

-- STEP 3: Apply to table
ALTER TABLE bookings
    ADD ROW ACCESS POLICY airline_row_policy ON (airline_code);

-- Test: Lufthansa user
USE ROLE LUFTHANSA_ROLE;
SELECT * FROM bookings;  -- Returns ONLY LH bookings, even though table has all airlines

-- Test: Admin
USE ROLE ADMIN_ROLE;
SELECT * FROM bookings;  -- Returns ALL bookings

-- PROVIDER SIDE (Amadeus shares booking summary with Lufthansa)

-- Step 1: Create a share object
CREATE SHARE lufthansa_share
    COMMENT = 'Lufthansa booking analytics share';

-- Step 2: Grant database and schema to share
GRANT USAGE ON DATABASE analytics_db TO SHARE lufthansa_share;
GRANT USAGE ON SCHEMA analytics_db.bookings TO SHARE lufthansa_share;

-- Step 3: Grant specific tables/views to share (NOT entire database)
GRANT SELECT ON VIEW analytics_db.bookings.lufthansa_bookings_vw TO SHARE lufthansa_share;
-- Share a VIEW (not the raw table) so you control exactly what they see
-- View can filter: WHERE airline_code = 'LH' — Lufthansa sees only their data

-- Step 4: Add recipient (Lufthansa's Snowflake account)
ALTER SHARE lufthansa_share ADD ACCOUNTS = 'lufthansa.eu-west.snowflakecomputing.com';

-- RECIPIENT SIDE (Lufthansa's Snowflake account)
-- Step 1: Create database from the share
CREATE DATABASE amadeus_share FROM SHARE amadeus.lufthansa_share;

-- Step 2: Query it like a normal database!
SELECT * FROM amadeus_share.bookings.lufthansa_bookings_vw;
-- Returns Amadeus's live data directly — no copy!
-- Lufthansa pays their own compute, Amadeus pays storage

-- W: WAREHOUSE OPTIMIZATION
ALTER WAREHOUSE etl_wh SET
    AUTO_SUSPEND = 120           -- Suspend after 2 minutes idle (default is 10 min)
    AUTO_RESUME = TRUE;          -- Auto-start on query (don't forget this!)

-- Create right-sized warehouses per workload
CREATE WAREHOUSE dev_wh   SIZE = 'XSMALL' AUTO_SUSPEND = 60;  -- Dev: tiny, fast suspend
CREATE WAREHOUSE bi_wh    SIZE = 'MEDIUM' AUTO_SUSPEND = 300;  -- BI: keep warm longer (cache!)
CREATE WAREHOUSE etl_wh   SIZE = 'LARGE'  AUTO_SUSPEND = 120;  -- ETL: large but fast suspend

-- S: STORAGE OPTIMIZATION
-- Transient tables: NO fail-safe (7-day window removed), cheaper storage
-- Use for: staging tables, temp tables, data you can reload
CREATE TRANSIENT TABLE staging_bookings (...);    -- No fail-safe period
CREATE TRANSIENT SCHEMA staging;                  -- All tables in schema are transient

-- Reduce time travel for non-critical tables (reduce storage cost)
ALTER TABLE staging_bookings SET DATA_RETENTION_TIME_IN_DAYS = 0;
ALTER TABLE audit_log SET DATA_RETENTION_TIME_IN_DAYS = 7;    -- Only 7 days
ALTER TABLE fact_bookings SET DATA_RETENTION_TIME_IN_DAYS = 30; -- 30 days for prod

-- C & R: RESOURCE MONITORS (spend limits with alerts)
CREATE RESOURCE MONITOR monthly_cap
    WITH CREDIT_QUOTA = 1000               -- 1000 credits per month max
    FREQUENCY = MONTHLY
    START_TIMESTAMP = IMMEDIATELY
    TRIGGERS
        ON 75 PERCENT DO NOTIFY            -- Email alert at 75% usage
        ON 90 PERCENT DO NOTIFY            -- Email alert at 90%
        ON 100 PERCENT DO SUSPEND;         -- Auto-suspend all warehouses at 100%!

ALTER WAREHOUSE bi_wh SET RESOURCE_MONITOR = monthly_cap;
-- Now BI warehouse auto-suspends if team exceeds 1000 credits/month

-- Use Cortex AI in a SQL query
SELECT
    booking_id,
    customer_feedback,
    SNOWFLAKE.CORTEX.SENTIMENT(customer_feedback)          AS sentiment_score,
    -- Returns -1 (negative) to +1 (positive)
    SNOWFLAKE.CORTEX.SUMMARIZE(customer_feedback)          AS feedback_summary,
    SNOWFLAKE.CORTEX.CLASSIFY_TEXT(customer_feedback,
        ['complaint', 'compliment', 'inquiry'])             AS feedback_category
FROM customer_feedback_table
WHERE booking_date > '2026-01-01';
-- ALL processing happens inside Snowflake — no API calls, no data leaving

-- Create an Iceberg table (data in YOUR ADLS, Snowflake manages catalog)
CREATE ICEBERG TABLE iceberg_bookings (
    booking_id    VARCHAR,
    booking_date  DATE,
    airline_code  VARCHAR,
    amount        NUMBER(10,2)
)
    CATALOG = 'SNOWFLAKE'              -- Snowflake is the catalog
    EXTERNAL_VOLUME = 'my_adls_vol'   -- Where files stored in ADLS
    BASE_LOCATION = 'bookings/';      -- Path within volume
-- Now both Snowflake AND Databricks can read the same Parquet+Iceberg files!

-- Create a Hybrid Table (OLTP+OLAP)
CREATE HYBRID TABLE live_bookings (
    booking_id    VARCHAR PRIMARY KEY,    -- Primary key (enforced, indexed)
    passenger_id  VARCHAR,
    booking_date  DATE,
    amount        NUMBER(10,2),
    status        VARCHAR,
    INDEX idx_passenger (passenger_id)    -- Secondary index (fast lookup)
);

-- Fast single-row lookup (OLTP-style)
SELECT * FROM live_bookings WHERE booking_id = 'BK12345678';
-- Uses row store → milliseconds response

-- Analytics (OLAP-style)
SELECT DATE_TRUNC('month', booking_date), SUM(amount)
FROM live_bookings
GROUP BY 1;
-- Uses column store → fast scan