-- Add retries and priority columns to calculation_job (if not exists)
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'calculation_job') AND name = 'retries')
BEGIN
    ALTER TABLE calculation_job ADD retries INT NOT NULL DEFAULT 0;
END

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'calculation_job') AND name = 'priority')
BEGIN
    ALTER TABLE calculation_job
        ADD priority VARCHAR(10) NOT NULL DEFAULT 'MEDIUM'
        CHECK (priority IN ('LOW', 'MEDIUM', 'HIGH'));
END

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'calculation_job') AND name = 'idx_priority')
BEGIN
    CREATE INDEX idx_priority ON calculation_job(priority);
END

-- Add retries column to distance_matrix (if not exists)
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'distance_matrix') AND name = 'retries')
BEGIN
    ALTER TABLE distance_matrix ADD retries INT NOT NULL DEFAULT 0;
END

ALTER TABLE distance_matrix
    DROP CONSTRAINT chk_distance_matrix_state;

ALTER TABLE distance_matrix
    ADD CONSTRAINT chk_distance_matrix_state CHECK (state IN ('VALID', 'STALE', 'EXCEPTION'));


-- Check if distance_d2d column exists before adding (already exists in V1)
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'premise_destination') AND name = 'distance_d2d')
BEGIN
    ALTER TABLE premise_destination
        ADD distance_d2d DECIMAL(15, 2) DEFAULT NULL;

    EXEC sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'travel distance between the two nodes in meters',
        @level0type = N'SCHEMA', @level0name = 'dbo',
        @level1type = N'TABLE',  @level1name = 'premise_destination',
        @level2type = N'COLUMN', @level2name = 'distance_d2d';
END

-- Add distance column to premise_route_section (if not exists)
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'premise_route_section') AND name = 'distance')
BEGIN
    ALTER TABLE premise_route_section
        ADD distance DECIMAL(15, 2) DEFAULT NULL;

    EXEC sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'travel distance between the two nodes in meters',
        @level0type = N'SCHEMA', @level0name = 'dbo',
        @level1type = N'TABLE',  @level1name = 'premise_route_section',
        @level2type = N'COLUMN', @level2name = 'distance';
END
