-- Snowflake Time Travel — guided SQL phases
-- Documentation reviewed; not executed in a connected Snowflake account.
-- Read each matching article section first. Optional drills have dependencies.
-- Never execute this entire file as one batch.

-- PHASE 1
-- Select an approved practice database first.
USE WAREHOUSE BA_LAB_WH;
CREATE SCHEMA BA_TT_LAB;
USE SCHEMA BA_TT_LAB;

CREATE TABLE PRODUCT_PRICES (
 product_id NUMBER, product_name VARCHAR, unit_price NUMBER(10,2)
) DATA_RETENTION_TIME_IN_DAYS=1;

INSERT INTO PRODUCT_PRICES VALUES
 (1,'Notebook',100.00),
 (2,'Backpack',500.00),
 (3,'Pen set',50.00);

SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS'
  IN TABLE PRODUCT_PRICES;
SELECT * FROM PRODUCT_PRICES ORDER BY product_id;


-- PHASE 2
SELECT CURRENT_DATABASE(),CURRENT_SCHEMA(),CURRENT_ROLE(),CURRENT_WAREHOUSE();
SELECT COUNT(*) AS baseline_rows,SUM(unit_price) AS baseline_total FROM PRODUCT_PRICES;
SELECT product_id,COUNT(*) AS copies FROM PRODUCT_PRICES
GROUP BY product_id HAVING COUNT(*)>1;
SHOW TABLES LIKE 'PRODUCT_PRICES' IN SCHEMA BA_TT_LAB;

-- PHASE 3
UPDATE PRODUCT_PRICES SET unit_price=unit_price*10;
SET bad_update_qid = LAST_QUERY_ID();

SELECT $bad_update_qid AS incident_query_id;
SELECT * FROM PRODUCT_PRICES ORDER BY product_id;

SELECT * FROM PRODUCT_PRICES
BEFORE(STATEMENT => $bad_update_qid)
ORDER BY product_id;


-- PHASE 4
CREATE TABLE PRICES_BEFORE_BAD_UPDATE AS
SELECT * FROM PRODUCT_PRICES
BEFORE(STATEMENT => $bad_update_qid);

SELECT COUNT(*) AS rows, SUM(unit_price) AS price_total
FROM PRICES_BEFORE_BAD_UPDATE;

SELECT product_id, COUNT(*) AS copies
FROM PRICES_BEFORE_BAD_UPDATE
GROUP BY product_id HAVING COUNT(*)>1;


-- PHASE 5
SELECT t.product_id,t.product_name,
 r.unit_price AS known_good_price,t.unit_price AS current_price,
 t.unit_price-r.unit_price AS price_difference
FROM PRODUCT_PRICES t
JOIN PRICES_BEFORE_BAD_UPDATE r USING(product_id)
WHERE t.unit_price IS DISTINCT FROM r.unit_price
ORDER BY t.product_id;

-- PHASE 6
UPDATE PRODUCT_PRICES
SET product_name='Travel backpack' WHERE product_id=2;

BEGIN;
UPDATE PRODUCT_PRICES t
SET unit_price=r.unit_price
FROM PRICES_BEFORE_BAD_UPDATE r
WHERE t.product_id=r.product_id;

SELECT * FROM PRODUCT_PRICES ORDER BY product_id;
SELECT COUNT(*) AS mismatched_prices
FROM PRODUCT_PRICES t
JOIN PRICES_BEFORE_BAD_UPDATE r USING(product_id)
WHERE t.unit_price <> r.unit_price;
-- STOP: keep this transaction open and inspect both result sets.


-- PHASE 7
COMMIT;

-- PHASE 8
ROLLBACK;

-- PHASE 9
SELECT COALESCE(t.product_id,r.product_id) AS product_id,
 CASE WHEN t.product_id IS NULL THEN 'MISSING_CURRENT'
      WHEN r.product_id IS NULL THEN 'NOT_IN_SNAPSHOT'
 END AS key_exception
FROM PRODUCT_PRICES t
FULL OUTER JOIN PRICES_BEFORE_BAD_UPDATE r USING(product_id)
WHERE t.product_id IS NULL OR r.product_id IS NULL;


-- PHASE 10
WITH expected(product_id,product_name,unit_price) AS (
 SELECT * FROM VALUES (1,'Notebook',100.00),
 (2,'Travel backpack',500.00),(3,'Pen set',50.00)
)
SELECT 'MISSING_OR_WRONG' AS issue,* FROM (
 SELECT * FROM expected MINUS SELECT * FROM PRODUCT_PRICES
)
UNION ALL
SELECT 'UNEXPECTED_OR_WRONG' AS issue,* FROM (
 SELECT * FROM PRODUCT_PRICES MINUS SELECT * FROM expected
);
SELECT COUNT(*) AS rows_after_repair,SUM(unit_price) AS total_after_repair
FROM PRODUCT_PRICES;
SELECT product_id,COUNT(*) AS copies FROM PRODUCT_PRICES
GROUP BY product_id HAVING COUNT(*)>1;

-- PHASE 11
CREATE TABLE PRICE_DELETE_DRILL DATA_RETENTION_TIME_IN_DAYS=1 AS SELECT * FROM PRODUCT_PRICES;
DELETE FROM PRICE_DELETE_DRILL WHERE product_id=3;
SET delete_qid=LAST_QUERY_ID();
SELECT * FROM PRICE_DELETE_DRILL BEFORE(STATEMENT => $delete_qid)
WHERE product_id=3;
BEGIN;
INSERT INTO PRICE_DELETE_DRILL(product_id,product_name,unit_price)
SELECT h.product_id,h.product_name,h.unit_price
FROM PRICE_DELETE_DRILL BEFORE(STATEMENT => $delete_qid) h
WHERE h.product_id=3
 AND NOT EXISTS(SELECT 1 FROM PRICE_DELETE_DRILL c WHERE c.product_id=h.product_id);
SELECT COUNT(*) AS restored_rows,SUM(unit_price) AS restored_total FROM PRICE_DELETE_DRILL;
-- STOP: expected three rows and 650.00. Inspect before choosing an action.

-- PHASE 12
COMMIT;

-- PHASE 13
ROLLBACK;

-- PHASE 14
CREATE TABLE DROP_RECOVERY_DEMO AS
SELECT * FROM PRODUCT_PRICES;
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS'
  IN TABLE DROP_RECOVERY_DEMO;
-- Confirm a positive effective retention setting before proceeding.
DROP TABLE DROP_RECOVERY_DEMO;
UNDROP TABLE DROP_RECOVERY_DEMO;

SELECT COUNT(*) AS rows, SUM(unit_price) AS price_total
FROM DROP_RECOVERY_DEMO;


-- PHASE 15
SELECT * FROM DROP_RECOVERY_DEMO MINUS SELECT * FROM PRODUCT_PRICES;
SELECT * FROM PRODUCT_PRICES MINUS SELECT * FROM DROP_RECOVERY_DEMO;
SELECT product_id,COUNT(*) AS copies FROM DROP_RECOVERY_DEMO
GROUP BY product_id HAVING COUNT(*)>1;