Untitled

can you convert this into into a notebook format so i can deploy this as a bundle so add in # command and magic where its needed

expecting 2 differenft files sports model and races model




# Define table name and create the Sports table
table_name = "slvr_crtd_nrt_back.tab_info_service_sports"  # Replace 'your_database' with your actual database name

spark.sql(f"""
CREATE TABLE IF NOT EXISTS {table_name} (
    tab_info_service_sports_pk STRING,  -- New Primary Key Column
    id STRING NOT NULL,
    name STRING,
    displayName STRING,
    spectrumId STRING,
    sameGame BOOLEAN,
    self_link STRING,
    selfTemplate_link STRING,
    competitions_link STRING,
    footytab_link STRING,
    tournaments_link STRING,
    matches_link STRING,
    markets_link STRING,
    markets_all_channels_link STRING,
    created_datetime TIMESTAMP,  -- Metadata Column
    updated_datetime TIMESTAMP,  -- Metadata Column
    deleted_datetime TIMESTAMP,  -- Metadata Column
    change_status STRING         -- Metadata Column
) USING DELTA
CLUSTER BY (id);
""")

# Add comments to each column in the Sports table
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tab_info_service_sports_pk COMMENT 'Primary key for the Sports table';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN id COMMENT 'Unique identifier for the sport';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN name COMMENT 'Name of the sport';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN displayName COMMENT 'Display name of the sport';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN spectrumId COMMENT 'Spectrum identifier';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN sameGame COMMENT 'Indicator if same game is available';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN self_link COMMENT 'Self link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN selfTemplate_link COMMENT 'Self template link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN competitions_link COMMENT 'Competitions link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN footytab_link COMMENT 'Footytab link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tournaments_link COMMENT 'Tournaments link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN matches_link COMMENT 'Matches link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN markets_link COMMENT 'Markets link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN markets_all_channels_link COMMENT 'Markets all channels link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN created_datetime COMMENT 'Timestamp when the record was created';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN updated_datetime COMMENT 'Timestamp when the record was last updated';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN deleted_datetime COMMENT 'Timestamp when the record was deleted';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN change_status COMMENT 'Status of the change';""")

# List of all column names for the Sports table including new metadata columns
columns = [
    "tab_info_service_sports_pk", "id", "name", "displayName", "spectrumId", "sameGame",
    "self_link", "selfTemplate_link", "competitions_link", "footytab_link",
    "tournaments_link", "matches_link", "markets_link", "markets_all_channels_link",
    "created_datetime", "updated_datetime", "deleted_datetime", "change_status"
]

# Set 'Internal' classification for all columns in the Sports table
for col_name in columns:
    spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN {col_name} SET TAGS ('Classification' = 'Internal');""")

# Update table properties for Delta Lake features in the Sports table
spark.sql(f"""ALTER TABLE {table_name} SET TBLPROPERTIES (
    delta.enableChangeDataFeed = true,
    delta.logRetentionDuration = 'interval 90 days',
    delta.deletedFileRetentionDuration = 'interval 90 days'
);""") 

# Define table name and create the Competitions table
table_name = "slvr_crtd_nrt_back.tab_info_service_competitions"

spark.sql(f"""
CREATE TABLE IF NOT EXISTS {table_name} (
    tab_info_service_sports_pk STRING,  -- New Primary Key Column
    sport_id STRING NOT NULL,
    id STRING NOT NULL,
    name STRING,
    spectrumId STRING,
    hasMarkets BOOLEAN,
    sameGame BOOLEAN,
    self_link STRING,
    selfTemplate_link STRING,
    competitions_link STRING,
    footytab_link STRING,
    tournaments_link STRING,
    matches_link STRING,
    markets_link STRING,
    markets_all_channels_link STRING,
    created_datetime TIMESTAMP,  -- Metadata Column
    updated_datetime TIMESTAMP,  -- Metadata Column
    deleted_datetime TIMESTAMP,  -- Metadata Column
    change_status STRING         -- Metadata Column
) USING DELTA
CLUSTER BY (sport_id, id);
""")

# Add comments to each column in the Competitions table
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tab_info_service_sports_pk COMMENT 'Primary key for the Competitions table';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN sport_id COMMENT 'Foreign key referencing sports.id';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN id COMMENT 'Unique identifier for the competition';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN name COMMENT 'Name of the competition';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN spectrumId COMMENT 'Spectrum identifier';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN hasMarkets COMMENT 'Indicator if markets are available';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN sameGame COMMENT 'Indicator if same game is available';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN self_link COMMENT 'Self link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN selfTemplate_link COMMENT 'Self template link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN competitions_link COMMENT 'Competitions link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN footytab_link COMMENT 'Footytab link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tournaments_link COMMENT 'Tournaments link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN matches_link COMMENT 'Matches link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN markets_link COMMENT 'Markets link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN markets_all_channels_link COMMENT 'Markets all channels link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN created_datetime COMMENT 'Timestamp when the record was created';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN updated_datetime COMMENT 'Timestamp when the record was last updated';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN deleted_datetime COMMENT 'Timestamp when the record was deleted';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN change_status COMMENT 'Status of the change';""")

# List of all column names for the Competitions table including new metadata columns
columns = [
    "tab_info_service_sports_pk", "sport_id", "id", "name", "spectrumId", "hasMarkets", "sameGame",
    "self_link", "selfTemplate_link", "competitions_link", "footytab_link",
    "tournaments_link", "matches_link", "markets_link", "markets_all_channels_link",
    "created_datetime", "updated_datetime", "deleted_datetime", "change_status"
]

# Set 'Internal' classification for all columns in the Competitions table
for col_name in columns:
    spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN {col_name} SET TAGS ('Classification' = 'Internal');""")

# Update table properties for Delta Lake features in the Competitions table
spark.sql(f"""ALTER TABLE {table_name} SET TBLPROPERTIES (
    delta.enableChangeDataFeed = true,
    delta.logRetentionDuration = 'interval 90 days',
    delta.deletedFileRetentionDuration = 'interval 90 days'
);""")  

# Define table name and create the Tournaments table
table_name = "slvr_crtd_nrt_back.tab_info_service_tournaments"

spark.sql(f"""
CREATE TABLE IF NOT EXISTS {table_name} (
    tab_info_service_sports_pk STRING,  -- New Primary Key Column
    sport_id STRING NOT NULL,
    competition_id STRING NOT NULL,
    id STRING NOT NULL,
    name STRING,
    spectrumId STRING,
    sameGame BOOLEAN,
    self_link STRING,
    selfTemplate_link STRING,
    competitions_link STRING,
    footytab_link STRING,
    tournaments_link STRING,
    matches_link STRING,
    markets_link STRING,
    markets_all_channels_link STRING,
    created_datetime TIMESTAMP,  -- Metadata Column
    updated_datetime TIMESTAMP,  -- Metadata Column
    deleted_datetime TIMESTAMP,  -- Metadata Column
    change_status STRING         -- Metadata Column
) USING DELTA
CLUSTER BY (sport_id, competition_id, id);
""")

# Add comments to each column in the Tournaments table
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tab_info_service_sports_pk COMMENT 'Primary key for the Tournaments table';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN sport_id COMMENT 'Foreign key referencing sports.id';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN competition_id COMMENT 'Foreign key referencing competitions.id';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN id COMMENT 'Unique identifier for the tournament';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN name COMMENT 'Name of the tournament';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN spectrumId COMMENT 'Spectrum identifier';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN sameGame COMMENT 'Indicator if same game is available';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN self_link COMMENT 'Self link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN selfTemplate_link COMMENT 'Self template link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN competitions_link COMMENT 'Competitions link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN footytab_link COMMENT 'Footytab link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tournaments_link COMMENT 'Tournaments link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN matches_link COMMENT 'Matches link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN markets_link COMMENT 'Markets link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN markets_all_channels_link COMMENT 'Markets all channels link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN created_datetime COMMENT 'Timestamp when the record was created';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN updated_datetime COMMENT 'Timestamp when the record was last updated';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN deleted_datetime COMMENT 'Timestamp when the record was deleted';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN change_status COMMENT 'Status of the change';""")

# List of all column names for the Tournaments table including new metadata columns
columns = [
    "tab_info_service_sports_pk", "sport_id", "competition_id", "id", "name", "spectrumId", "sameGame",
    "self_link", "selfTemplate_link", "competitions_link", "footytab_link",
    "tournaments_link", "matches_link", "markets_link", "markets_all_channels_link",
    "created_datetime", "updated_datetime", "deleted_datetime", "change_status"
]

# Set 'Internal' classification for all columns in the Tournaments table
for col_name in columns:
    spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN {col_name} SET TAGS ('Classification' = 'Internal');""")

# Update table properties for Delta Lake features in the Tournaments table
spark.sql(f"""ALTER TABLE {table_name} SET TBLPROPERTIES (
    delta.enableChangeDataFeed = true,
    delta.logRetentionDuration = 'interval 90 days',
    delta.deletedFileRetentionDuration = 'interval 90 days'
);""")
  

# Define table name and create the Meeting table
table_name = "slvr_crtd_nrt_back.tab_info_service_meeting"

spark.sql(f"""
CREATE TABLE IF NOT EXISTS {table_name} (
    tab_info_service_racing_pk STRING,  -- New Primary Key Column
    meetingName STRING NOT NULL,
    meetingDate TIMESTAMP NOT NULL,
    location STRING,
    raceType STRING,
    displayMeetingName STRING,
    prizeMoney STRING,
    weatherCondition STRING,
    trackCondition STRING,
    railPosition STRING,
    venueMnemonic STRING,
    fixedOddsOnly BOOLEAN,
    sellCode_meetingCode STRING,
    sellCode_scheduledType STRING,
    _links_races STRING,
    created_datetime TIMESTAMP,  -- Metadata Column
    updated_datetime TIMESTAMP,  -- Metadata Column
    deleted_datetime TIMESTAMP,  -- Metadata Column
    change_status STRING         -- Metadata Column
) USING DELTA
CLUSTER BY (meetingName, meetingDate);
""")

# Add comments to each column in the Meeting table
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tab_info_service_racing_pk COMMENT 'Primary key for the Meeting table';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN meetingName COMMENT 'Unique name of the meeting';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN meetingDate COMMENT 'Date and time of the meeting';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN location COMMENT 'Location of the meeting';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN raceType COMMENT 'Type of the race';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN displayMeetingName COMMENT 'Display name of the meeting';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN prizeMoney COMMENT 'Prize money for the meeting';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN weatherCondition COMMENT 'Weather condition during the meeting';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN trackCondition COMMENT 'Condition of the track';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN railPosition COMMENT 'Rail position for the races';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN venueMnemonic COMMENT 'Venue mnemonic code';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN fixedOddsOnly COMMENT 'Indicator if only fixed odds are available';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN sellCode_meetingCode COMMENT 'Sell code meeting code';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN sellCode_scheduledType COMMENT 'Sell code scheduled type';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN _links_races COMMENT 'Link to races information';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN created_datetime COMMENT 'Timestamp when the record was created';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN updated_datetime COMMENT 'Timestamp when the record was last updated';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN deleted_datetime COMMENT 'Timestamp when the record was deleted';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN change_status COMMENT 'Status of the change';""")

# List of all column names for the Meeting table including new metadata columns
columns = [
    "tab_info_service_racing_pk", "meetingName", "meetingDate", "location", "raceType",
    "displayMeetingName", "prizeMoney", "weatherCondition", "trackCondition",
    "railPosition", "venueMnemonic", "fixedOddsOnly", "sellCode_meetingCode",
    "sellCode_scheduledType", "_links_races",
    "created_datetime", "updated_datetime", "deleted_datetime", "change_status"
]

# Set 'Internal' classification for all columns in the Meeting table
for col_name in columns:
    spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN {col_name} SET TAGS ('Classification' = 'Internal');""")

# Update table properties for Delta Lake features in the Meeting table
spark.sql(f"""ALTER TABLE {table_name} SET TBLPROPERTIES (
    delta.enableChangeDataFeed = true,
    delta.logRetentionDuration = 'interval 90 days',
    delta.deletedFileRetentionDuration = 'interval 90 days'
);""")  


# Define table name and create the ExoticPools table
table_name = "slvr_crtd_nrt_back.tab_info_service_exotic_pools"

spark.sql(f"""
CREATE TABLE IF NOT EXISTS {table_name} (
    tab_info_service_racing_pk STRING,  -- New Primary Key Column
    meetingName STRING NOT NULL,
    meetingDate TIMESTAMP NOT NULL,
    wageringProduct STRING NOT NULL,
    poolStatusCode STRING,
    cashOutEligibility STRING,
    startTime TIMESTAMP,
    multiLegApproximatesAvailable BOOLEAN,
    sameGame BOOLEAN,
    nextRaceToJump_legNumber INT,
    nextRaceToJump_raceNumber INT,
    nextRaceToJump_venueMnemonic STRING,
    nextRaceToJump_raceType STRING,
    nextRaceToJump_startTime TIMESTAMP,
    created_datetime TIMESTAMP,  -- Metadata Column
    updated_datetime TIMESTAMP,  -- Metadata Column
    deleted_datetime TIMESTAMP,  -- Metadata Column
    change_status STRING         -- Metadata Column
) USING DELTA
CLUSTER BY (meetingName, meetingDate, wageringProduct);
""") 

# Add comments to each column in the ExoticPools table
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tab_info_service_racing_pk COMMENT 'Primary key for the ExoticPools table';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN meetingName COMMENT 'Unique name of the meeting';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN meetingDate COMMENT 'Date and time of the meeting';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN wageringProduct COMMENT 'Wagering product name';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN poolStatusCode COMMENT 'Status code of the pool';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN cashOutEligibility COMMENT 'Cash out eligibility status';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN startTime COMMENT 'Start time of the pool';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN multiLegApproximatesAvailable COMMENT 'Indicator if multi-leg approximates are available';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN sameGame COMMENT 'Indicator if same game';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN nextRaceToJump_legNumber COMMENT 'Leg number of the next race to jump';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN nextRaceToJump_raceNumber COMMENT 'Race number of the next race to jump';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN nextRaceToJump_venueMnemonic COMMENT 'Venue mnemonic of the next race to jump';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN nextRaceToJump_raceType COMMENT 'Race type of the next race to jump';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN nextRaceToJump_startTime COMMENT 'Start time of the next race to jump';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN created_datetime COMMENT 'Timestamp when the record was created';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN updated_datetime COMMENT 'Timestamp when the record was last updated';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN deleted_datetime COMMENT 'Timestamp when the record was deleted';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN change_status COMMENT 'Status of the change';""")

# List of all column names for the ExoticPools table including new metadata columns
columns = [
    "tab_info_service_racing_pk", "meetingName", "meetingDate", "wageringProduct",
    "poolStatusCode", "cashOutEligibility", "startTime", "multiLegApproximatesAvailable",
    "sameGame", "nextRaceToJump_legNumber", "nextRaceToJump_raceNumber",
    "nextRaceToJump_venueMnemonic", "nextRaceToJump_raceType", "nextRaceToJump_startTime",
    "created_datetime", "updated_datetime", "deleted_datetime", "change_status"
]

# Set 'Internal' classification for all columns in the ExoticPools table
for col_name in columns:
    spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN {col_name} SET TAGS ('Classification' = 'Internal');""") 

# Update table properties for Delta Lake features in the ExoticPools table
spark.sql(f"""ALTER TABLE {table_name} SET TBLPROPERTIES (
    delta.enableChangeDataFeed = true,
    delta.logRetentionDuration = 'interval 90 days',
    delta.deletedFileRetentionDuration = 'interval 90 days'
);""") 


# Define table name and create the Races table
table_name = "slvr_crtd_nrt_back.tab_info_service_races"

spark.sql(f"""
CREATE TABLE IF NOT EXISTS {table_name} (
    tab_info_service_racing_pk STRING,  -- New Primary Key Column
    meetingName STRING NOT NULL,
    meetingDate TIMESTAMP NOT NULL,
    raceNumber INT NOT NULL,
    raceClassConditions STRING,
    raceStartTime TIMESTAMP,
    raceName STRING,
    raceStatus STRING,
    raceDistance INT,
    hasParimutuel BOOLEAN,
    hasFixedOdds BOOLEAN,
    broadcastChannel STRING,
    broadcastChannels_Channel1 STRING,
    skyRacing_audio STRING,
    skyRacing_video STRING,
    willHaveFixedOdds BOOLEAN,
    allIn BOOLEAN,
    cashOutEligibility STRING,
    allowBundle BOOLEAN,
    matchName STRING,
    fixedOddsOnlineBetting BOOLEAN,
    multiLegApproximates_wageringProduct STRING,
    _links_self STRING,
    _links_selfTemplate STRING,
    _links_self_all_channels STRING,
    results_WinningPosition1 INT,
    results_WinningPosition2 INT,
    results_WinningPosition3 INT,
    results_WinningPosition4 INT,
    created_datetime TIMESTAMP,  -- Metadata Column
    updated_datetime TIMESTAMP,  -- Metadata Column
    deleted_datetime TIMESTAMP,  -- Metadata Column
    change_status STRING         -- Metadata Column
) USING DELTA
CLUSTER BY (meetingName, meetingDate, raceNumber);
""")  

# Add comments to each column in the Races table
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tab_info_service_racing_pk COMMENT 'Primary key for the Races table';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN meetingName COMMENT 'Unique name of the meeting';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN meetingDate COMMENT 'Date and time of the meeting';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN raceNumber COMMENT 'Race number';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN raceClassConditions COMMENT 'Class conditions of the race';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN raceStartTime COMMENT 'Start time of the race';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN raceName COMMENT 'Name of the race';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN raceStatus COMMENT 'Status of the race';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN raceDistance COMMENT 'Distance of the race';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN hasParimutuel COMMENT 'Indicator if parimutuel betting is available';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN hasFixedOdds COMMENT 'Indicator if fixed odds betting is available';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN broadcastChannel COMMENT 'Broadcast channel information';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN broadcastChannels_Channel1 COMMENT 'Broadcast channel 1 information';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN skyRacing_audio COMMENT 'Sky Racing audio link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN skyRacing_video COMMENT 'Sky Racing video link';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN willHaveFixedOdds COMMENT 'Indicator if fixed odds will be available';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN allIn COMMENT 'Indicator for all-in betting';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN cashOutEligibility COMMENT 'Cash out eligibility status';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN allowBundle COMMENT 'Indicator if bundling is allowed';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN matchName COMMENT 'Name of the match';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN fixedOddsOnlineBetting COMMENT 'Indicator if fixed odds online betting is available';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN multiLegApproximates_wageringProduct COMMENT 'Multi-leg approximates wagering product';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN _links_self COMMENT 'Link to self information';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN _links_selfTemplate COMMENT 'Link to self template';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN _links_self_all_channels COMMENT 'Link to all channels';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN results_WinningPosition1 COMMENT 'Winning position 1';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN results_WinningPosition2 COMMENT 'Winning position 2';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN results_WinningPosition3 COMMENT 'Winning position 3';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN results_WinningPosition4 COMMENT 'Winning position 4';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN created_datetime COMMENT 'Timestamp when the record was created';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN updated_datetime COMMENT 'Timestamp when the record was last updated';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN deleted_datetime COMMENT 'Timestamp when the record was deleted';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN change_status COMMENT 'Status of the change';""")

# List of all column names for the Races table including new metadata columns
columns = [
    "tab_info_service_racing_pk", "meetingName", "meetingDate", "raceNumber",
    "raceClassConditions", "raceStartTime", "raceName", "raceStatus", "raceDistance",
    "hasParimutuel", "hasFixedOdds", "broadcastChannel", "broadcastChannels_Channel1",
    "skyRacing_audio", "skyRacing_video", "willHaveFixedOdds", "allIn",
    "cashOutEligibility", "allowBundle", "matchName", "fixedOddsOnlineBetting",
    "multiLegApproximates_wageringProduct", "_links_self", "_links_selfTemplate",
    "_links_self_all_channels", "results_WinningPosition1", "results_WinningPosition2",
    "results_WinningPosition3", "results_WinningPosition4",
    "created_datetime", "updated_datetime", "deleted_datetime", "change_status"
]

# Set 'Internal' classification for all columns in the Races table
for col_name in columns:
    spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN {col_name} SET TAGS ('Classification' = 'Internal');""")  

# Update table properties for Delta Lake features in the Races table
spark.sql(f"""ALTER TABLE {table_name} SET TBLPROPERTIES (
    delta.enableChangeDataFeed = true,
    delta.logRetentionDuration = 'interval 90 days',
    delta.deletedFileRetentionDuration = 'interval 90 days'
);""")
 
 
# Define table name and create the Legs table
table_name = "slvr_crtd_nrt_back.tab_info_service_legs"

spark.sql(f"""
CREATE TABLE IF NOT EXISTS {table_name} (
    tab_info_service_racing_pk STRING,  -- New Primary Key Column
    meetingName STRING NOT NULL,
    meetingDate TIMESTAMP NOT NULL,
    wageringProduct STRING NOT NULL,
    legNumber INT NOT NULL,
    raceNumber INT,
    venueMnemonic STRING,
    raceType STRING,
    startTime TIMESTAMP,
    created_datetime TIMESTAMP,  -- Metadata Column
    updated_datetime TIMESTAMP,  -- Metadata Column
    deleted_datetime TIMESTAMP,  -- Metadata Column
    change_status STRING         -- Metadata Column
) USING DELTA
CLUSTER BY (meetingName, meetingDate, wageringProduct, legNumber);
""") 

# Add comments to each column in the Legs table
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tab_info_service_racing_pk COMMENT 'Primary key for the Legs table';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN meetingName COMMENT 'Unique name of the meeting';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN meetingDate COMMENT 'Date and time of the meeting';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN wageringProduct COMMENT 'Wagering product name';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN legNumber COMMENT 'Leg number';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN raceNumber COMMENT 'Race number';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN venueMnemonic COMMENT 'Venue mnemonic code';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN raceType COMMENT 'Type of the race';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN startTime COMMENT 'Start time of the leg';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN created_datetime COMMENT 'Timestamp when the record was created';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN updated_datetime COMMENT 'Timestamp when the record was last updated';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN deleted_datetime COMMENT 'Timestamp when the record was deleted';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN change_status COMMENT 'Status of the change';""")

# List of all column names for the Legs table including new metadata columns
columns = [
    "tab_info_service_racing_pk", "meetingName", "meetingDate", "wageringProduct",
    "legNumber", "raceNumber", "venueMnemonic", "raceType", "startTime",
    "created_datetime", "updated_datetime", "deleted_datetime", "change_status"
]

# Set 'Internal' classification for all columns in the Legs table
for col_name in columns:
    spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN {col_name} SET TAGS ('Classification' = 'Internal');""")
    
# Update table properties for Delta Lake features in the Legs table
spark.sql(f"""ALTER TABLE {table_name} SET TBLPROPERTIES (
    delta.enableChangeDataFeed = true,
    delta.logRetentionDuration = 'interval 90 days',
    delta.deletedFileRetentionDuration = 'interval 90 days'
);""")   

# Define table name and create the Scratchings table
table_name = "slvr_crtd_nrt_back.tab_info_service_scratchings"

spark.sql(f"""
CREATE TABLE IF NOT EXISTS {table_name} (
    tab_info_service_racing_pk STRING,  -- New Primary Key Column
    meetingName STRING NOT NULL,
    meetingDate TIMESTAMP NOT NULL,
    raceNumber INT NOT NULL,
    runnerNumber INT NOT NULL,
    runnerName STRING,
    bettingStatus STRING,
    created_datetime TIMESTAMP,  -- Metadata Column
    updated_datetime TIMESTAMP,  -- Metadata Column
    deleted_datetime TIMESTAMP,  -- Metadata Column
    change_status STRING         -- Metadata Column
) USING DELTA
CLUSTER BY (meetingName, meetingDate, raceNumber, runnerNumber);
""") 

# Add comments to each column in the Scratchings table
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tab_info_service_racing_pk COMMENT 'Primary key for the Scratchings table';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN meetingName COMMENT 'Unique name of the meeting';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN meetingDate COMMENT 'Date and time of the meeting';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN raceNumber COMMENT 'Race number';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN runnerNumber COMMENT 'Runner number';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN runnerName COMMENT 'Name of the runner';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN bettingStatus COMMENT 'Betting status of the runner';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN created_datetime COMMENT 'Timestamp when the record was created';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN updated_datetime COMMENT 'Timestamp when the record was last updated';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN deleted_datetime COMMENT 'Timestamp when the record was deleted';""")
spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN change_status COMMENT 'Status of the change';""")

# List of all column names for the Scratchings table including new metadata columns
columns = [
    "tab_info_service_racing_pk", "meetingName", "meetingDate", "raceNumber",
    "runnerNumber", "runnerName", "bettingStatus",
    "created_datetime", "updated_datetime", "deleted_datetime", "change_status"
]

# Set 'Internal' classification for all columns in the Scratchings table
for col_name in columns:
    spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN {col_name} SET TAGS ('Classification' = 'Internal');""")  
    
# Update table properties for Delta Lake features in the Scratchings table
spark.sql(f"""ALTER TABLE {table_name} SET TBLPROPERTIES (
    delta.enableChangeDataFeed = true,
    delta.logRetentionDuration = 'interval 90 days',
    delta.deletedFileRetentionDuration = 'interval 90 days'
);""")