Build marts models

{{ config(materialized='table') }}

WITH races AS (
    SELECT * FROM {{ ref('stg_f1_races') }}
),

circuits AS (
    SELECT * FROM {{ ref('stg_f1_circuits') }}
),

results AS (
    SELECT * FROM {{ ref('stg_f1_results') }}
),

constructors AS (
    SELECT * FROM {{ ref('stg_f1_constructors') }}
),

driver_standings AS (
    SELECT * FROM {{ ref('stg_f1_driver_standings') }}
),

race_details AS (
    SELECT
        r.race_id, r.year, r.round, r.name AS race_name,
        c.circuit_name, c.location, c.country, r.date AS race_date
    FROM races r
    LEFT JOIN circuits c ON r.circuit_id = c.circuit_id
),

race_results AS (
    SELECT
        res.race_id, res.driver_id, res.constructor_id,
        con.constructor_name, con.nationality AS constructor_nationality,
        res.grid, res.position, res.points
    FROM results res
    LEFT JOIN constructors con ON res.constructor_id = con.constructor_id
)

SELECT
    rd.race_id, rd.year, rd.round, rd.race_name, rd.circuit_name, rd.country, rd.race_date,
    rr.constructor_id, rr.constructor_name, rr.constructor_nationality,
    rr.driver_id, rr.grid, rr.position, rr.points,
    ds.position AS driver_standing_position, ds.points AS driver_total_points
FROM race_details rd
JOIN race_results rr ON rd.race_id = rr.race_id
LEFT JOIN driver_standings ds ON rr.driver_id = ds.driver_id AND rd.race_id = ds.race_id
ORDER BY rd.year, rd.round, rr.position