Snowflake pour les Debutants :
Par ou commencer ?

-- ═══ Creer un warehouse de travail ═══
CREATE WAREHOUSE IF NOT EXISTS DEV_WH
    WITH
    WAREHOUSE_SIZE = 'X-SMALL'          -- Le plus petit (1 credit/heure)
    AUTO_SUSPEND = 60                    -- Suspend apres 60 secondes d'inactivite
    AUTO_RESUME = TRUE                   -- Redemarre automatiquement
    INITIALLY_SUSPENDED = TRUE;         -- Ne demarre pas tout de suite

-- ═══ Creer la database ═══
CREATE DATABASE IF NOT EXISTS LEARNING_DB
    COMMENT = 'Database pour apprendre Snowflake';

-- ═══ Utiliser la database ═══
USE DATABASE LEARNING_DB;

-- ═══ Creer des schemas (couches de donnees) ═══
CREATE SCHEMA IF NOT EXISTS RAW
    COMMENT = 'Donnees brutes - zone de landing';

CREATE SCHEMA IF NOT EXISTS STAGING
    COMMENT = 'Donnees nettoyees et transformees';

CREATE SCHEMA IF NOT EXISTS ANALYTICS
    COMMENT = 'Donnees metier prets pour le reporting';

-- Utiliser le bon contexte
USE LEARNING_DB.RAW;
USE WAREHOUSE DEV_WH;

-- ═══ Table des clients ═══
CREATE TABLE IF NOT EXISTS customers (
    customer_id     INT           NOT NULL,
    first_name      VARCHAR(100)  NOT NULL,
    last_name       VARCHAR(100)  NOT NULL,
    email           VARCHAR(255),
    city            VARCHAR(100),
    country         VARCHAR(50)   DEFAULT 'France',
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP()
);

-- ═══ Table des commandes ═══
CREATE TABLE IF NOT EXISTS orders (
    order_id        INT           NOT NULL,
    customer_id     INT           NOT NULL,
    product_name    VARCHAR(200),
    quantity        INT,
    unit_price      DECIMAL(10,2),
    order_date      DATE,
    status          VARCHAR(20)   DEFAULT 'pending'
);

-- Verifier les tables creees
SHOW TABLES IN SCHEMA LEARNING_DB.RAW;

-- Creer un warehouse dedie a l'ETL
CREATE WAREHOUSE IF NOT EXISTS ETL_WH
    WITH
    WAREHOUSE_SIZE = 'MEDIUM'
    AUTO_SUSPEND = 120
    AUTO_RESUME = TRUE
    COMMENT = 'Warehouse dedie aux pipelines ETL';

-- Redimensionner a la volee (pas de downtime !)
ALTER WAREHOUSE ETL_WH SET WAREHOUSE_SIZE = 'LARGE';

-- Suspendre manuellement pour economiser
ALTER WAREHOUSE ETL_WH SUSPEND;

-- Voir tous vos warehouses
SHOW WAREHOUSES;

USE LEARNING_DB.RAW;

-- ═══ Creer un file format pour les CSV ═══
CREATE FILE FORMAT IF NOT EXISTS csv_format
    TYPE = 'CSV'
    FIELD_DELIMITER = ','
    SKIP_HEADER = 1
    NULL_IF = ('', 'NULL', 'null')
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    TRIM_SPACE = TRUE;

-- ═══ Creer un file format pour JSON ═══
CREATE FILE FORMAT IF NOT EXISTS json_format
    TYPE = 'JSON'
    STRIP_OUTER_ARRAY = TRUE;

-- ═══ Creer un internal stage ═══
CREATE STAGE IF NOT EXISTS raw_data_stage
    FILE_FORMAT = csv_format
    COMMENT = 'Stage pour les fichiers bruts CSV';

-- ═══ External stage (exemple avec AWS S3) ═══
-- CREATE STAGE IF NOT EXISTS s3_stage
--     URL = 's3://mon-bucket/data/'
--     CREDENTIALS = (AWS_KEY_ID='...' AWS_SECRET_KEY='...')
--     FILE_FORMAT = csv_format;

-- Lister les fichiers dans un stage
LIST @raw_data_stage;

-- ═══ Etape 1 : Uploader un fichier vers le stage ═══
-- Via Snowsight : cliquez sur le stage > Upload
-- Ou via SnowSQL CLI :
-- PUT file:///chemin/vers/customers.csv @raw_data_stage;

-- ═══ Etape 2 : Charger dans la table ═══
COPY INTO customers
    FROM @raw_data_stage/customers.csv
    FILE_FORMAT = csv_format
    ON_ERROR = 'CONTINUE'              -- Continue malgre les erreurs
    PURGE = TRUE;                        -- Supprime le fichier apres chargement

-- ═══ Verifier le chargement ═══
SELECT COUNT(*) AS total_rows FROM customers;

-- ═══ Voir l'historique des chargements ═══
SELECT *
FROM TABLE(INFORMATION_SCHEMA.LOAD_HISTORY(
    TABLE_NAME => 'CUSTOMERS'
))
ORDER BY LAST_LOAD_TIME DESC
LIMIT 5;

-- Table avec une colonne VARIANT pour le JSON
CREATE TABLE IF NOT EXISTS raw_events (
    event_data  VARIANT,
    loaded_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

-- Charger le JSON
COPY INTO raw_events (event_data)
    FROM @raw_data_stage/events.json
    FILE_FORMAT = json_format;

-- Interroger les donnees semi-structurees
SELECT
    event_data:user_id::INT AS user_id,
    event_data:event_type::STRING AS event_type,
    event_data:timestamp::TIMESTAMP AS event_time
FROM raw_events
LIMIT 10;

-- ═══ Inserer des donnees de test ═══
INSERT INTO customers (customer_id, first_name, last_name, email, city)
VALUES
    (1, 'Marie',  'Dupont',  'marie@email.com',   'Paris'),
    (2, 'Ousmane','Diallo',  'ousmane@email.com', 'Dakar'),
    (3, 'Sophie', 'Martin',  'sophie@email.com',  'Nantes'),
    (4, 'Amadou', 'Ba',      'amadou@email.com',  'Paris');

INSERT INTO orders (order_id, customer_id, product_name, quantity, unit_price, order_date, status)
VALUES
    (101, 1, 'Laptop Pro',    1, 1299.99, '2026-02-10', 'completed'),
    (102, 2, 'Ecran 27"',     2, 349.00,  '2026-02-15', 'completed'),
    (103, 1, 'Clavier meca',  1, 159.00,  '2026-02-20', 'pending'),
    (104, 3, 'Souris ergo',   1, 89.99,   '2026-02-22', 'completed'),
    (105, 4, 'Laptop Pro',    1, 1299.99, '2026-02-25', 'pending');

-- ═══ Jointure : commandes par client ═══
SELECT
    c.first_name || ' ' || c.last_name AS client,
    c.city,
    COUNT(o.order_id) AS nb_commandes,
    SUM(o.quantity * o.unit_price) AS chiffre_affaires
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.first_name, c.last_name, c.city
ORDER BY chiffre_affaires DESC;

-- ═══ Window function : classement par CA ═══
SELECT
    c.first_name || ' ' || c.last_name AS client,
    SUM(o.quantity * o.unit_price) AS ca,
    RANK() OVER (ORDER BY SUM(o.quantity * o.unit_price) DESC) AS rang
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.first_name, c.last_name;