-- Dynamic Tables in Snowflake — 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 CURRENT_ROLE(), CURRENT_DATABASE(), CURRENT_SCHEMA(), CURRENT_WAREHOUSE();

-- PHASE 2
-- Select an approved practice database in the worksheet first.
USE WAREHOUSE BA_LAB_WH;
CREATE SCHEMA BA_DT_LAB;
USE SCHEMA BA_DT_LAB;

CREATE TABLE INVENTORY_INPUT (
  record_id NUMBER, product_id VARCHAR, location_id VARCHAR,
  received_units NUMBER, reserved_units NUMBER
);
INSERT INTO INVENTORY_INPUT VALUES
  (1,'P10','HYD',100,25),
  (2,'P20','HYD',40,5),
  (3,'P10','BLR',60,10),
  (4,'P30','HYD',-5,0),
  (5,'P40','HYD',20,NULL),
  (6,'P50','BLR',10,12);


-- PHASE 3
SELECT COUNT(*) AS source_rows FROM INVENTORY_INPUT;
SELECT record_id, COUNT(*) AS copies FROM INVENTORY_INPUT
GROUP BY record_id HAVING COUNT(*)<>1;

-- PHASE 4
CREATE VIEW INVENTORY_EXCEPTIONS AS
SELECT *,
  CASE
    WHEN product_id IS NULL OR location_id IS NULL THEN 'MISSING_KEY'
    WHEN received_units IS NULL OR reserved_units IS NULL THEN 'MISSING_QUANTITY'
    WHEN received_units < 0 OR reserved_units < 0 THEN 'NEGATIVE_QUANTITY'
    WHEN reserved_units > received_units THEN 'OVER_RESERVED'
  END AS exception_reason
FROM INVENTORY_INPUT
WHERE product_id IS NULL OR location_id IS NULL
   OR received_units IS NULL OR reserved_units IS NULL
   OR received_units < 0 OR reserved_units < 0
   OR reserved_units > received_units;

CREATE DYNAMIC TABLE VALID_STOCK
  TARGET_LAG = '5 minutes'
  WAREHOUSE = BA_LAB_WH
  REFRESH_MODE = INCREMENTAL
AS
SELECT record_id, product_id, location_id,
       received_units, reserved_units,
       received_units-reserved_units AS available_units
FROM INVENTORY_INPUT
WHERE product_id IS NOT NULL AND location_id IS NOT NULL
  AND received_units IS NOT NULL AND reserved_units IS NOT NULL
  AND received_units >= 0 AND reserved_units >= 0
  AND reserved_units <= received_units;


-- PHASE 5
CREATE DYNAMIC TABLE PRODUCT_AVAILABILITY
  TARGET_LAG = '10 minutes'
  WAREHOUSE = BA_LAB_WH
  REFRESH_MODE = INCREMENTAL
AS
SELECT product_id, location_id,
       SUM(available_units) AS available_units
FROM VALID_STOCK
GROUP BY product_id, location_id;

SELECT * FROM PRODUCT_AVAILABILITY
ORDER BY product_id, location_id;
SELECT record_id, exception_reason
FROM INVENTORY_EXCEPTIONS ORDER BY record_id;


-- PHASE 6
WITH expected(product_id,location_id,available_units) AS (
 SELECT * FROM VALUES ('P10','BLR',50),('P10','HYD',75),('P20','HYD',35)
)
SELECT 'MISSING_OR_WRONG' AS issue, * FROM (
 SELECT * FROM expected MINUS SELECT * FROM PRODUCT_AVAILABILITY
)
UNION ALL
SELECT 'UNEXPECTED_OR_WRONG' AS issue, * FROM (
 SELECT * FROM PRODUCT_AVAILABILITY MINUS SELECT * FROM expected
);
SELECT COUNT(*) AS report_rows, SUM(available_units) AS total_available
FROM PRODUCT_AVAILABILITY;
SELECT product_id,location_id,COUNT(*) AS copies
FROM PRODUCT_AVAILABILITY GROUP BY product_id,location_id HAVING COUNT(*)>1;

-- PHASE 7
SHOW DYNAMIC TABLES IN SCHEMA BA_DT_LAB;

SELECT name, state, state_message, refresh_action,
       data_timestamp, refresh_start_time, refresh_end_time
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY())
WHERE name IN ('VALID_STOCK','PRODUCT_AVAILABILITY')
ORDER BY refresh_start_time DESC;


-- PHASE 8
CREATE TEMP TABLE INVENTORY_DUPLICATE_DRILL AS SELECT * FROM INVENTORY_INPUT;
INSERT INTO INVENTORY_DUPLICATE_DRILL SELECT * FROM INVENTORY_INPUT WHERE record_id=1;
SELECT record_id,COUNT(*) AS copies FROM INVENTORY_DUPLICATE_DRILL
GROUP BY record_id HAVING COUNT(*)>1;
-- Expected: record_id 1, copies 2.
-- Reset the disposable copy exactly to the original seed.
TRUNCATE TABLE INVENTORY_DUPLICATE_DRILL;
INSERT INTO INVENTORY_DUPLICATE_DRILL SELECT * FROM INVENTORY_INPUT;
SELECT COUNT(*) AS reset_rows FROM INVENTORY_DUPLICATE_DRILL;
-- Expected: 6.
DROP TABLE INVENTORY_DUPLICATE_DRILL;

-- PHASE 9
UPDATE INVENTORY_INPUT
SET reserved_units=40 WHERE record_id=1;

ALTER DYNAMIC TABLE VALID_STOCK REFRESH;
ALTER DYNAMIC TABLE PRODUCT_AVAILABILITY REFRESH;

SELECT * FROM PRODUCT_AVAILABILITY
ORDER BY product_id, location_id;


-- PHASE 10
UPDATE INVENTORY_INPUT SET reserved_units=NULL WHERE record_id=2;
ALTER DYNAMIC TABLE VALID_STOCK REFRESH;
ALTER DYNAMIC TABLE PRODUCT_AVAILABILITY REFRESH;
-- Wait for successful completion, then inspect both paths.
SELECT * FROM PRODUCT_AVAILABILITY ORDER BY product_id,location_id;
SELECT record_id,exception_reason FROM INVENTORY_EXCEPTIONS ORDER BY record_id;
-- Restore exactly the value changed in this drill.
UPDATE INVENTORY_INPUT SET reserved_units=5 WHERE record_id=2;
ALTER DYNAMIC TABLE VALID_STOCK REFRESH;
ALTER DYNAMIC TABLE PRODUCT_AVAILABILITY REFRESH;

-- PHASE 11
ALTER DYNAMIC TABLE PRODUCT_AVAILABILITY SUSPEND;
ALTER DYNAMIC TABLE VALID_STOCK SUSPEND;
-- Resume upstream before downstream when continuing the exercise.
-- ALTER DYNAMIC TABLE VALID_STOCK RESUME;
-- ALTER DYNAMIC TABLE PRODUCT_AVAILABILITY RESUME;