init_database.sql

-- Database Initialization Script
-- This script initializes the Ghana Commodity Pricing Engine database with sample data

-- =====================================================
-- POPULATE DATE DIMENSION
-- =====================================================

-- Populate date dimension for 2024-2025
DECLARE @start_date DATE = '2024-01-01';
DECLARE @end_date DATE = '2025-12-31';
DECLARE @current_date DATE = @start_date;

WHILE @current_date <= @end_date
BEGIN
    DECLARE @date_key INT = CAST(FORMAT(@current_date, 'yyyyMMdd') AS INT);
    DECLARE @year INT = YEAR(@current_date);
    DECLARE @month INT = MONTH(@current_date);
    DECLARE @day INT = DAY(@current_date);
    DECLARE @quarter INT = DATEPART(QUARTER, @current_date);
    DECLARE @month_name VARCHAR(20) = DATENAME(MONTH, @current_date);
    DECLARE @day_name VARCHAR(20) = DATENAME(WEEKDAY, @current_date);
    DECLARE @is_weekend BIT = CASE WHEN DATEPART(WEEKDAY, @current_date) IN (1, 7) THEN 1 ELSE 0 END;
    DECLARE @fiscal_year INT = CASE WHEN @month >= 7 THEN @year + 1 ELSE @year END;
    DECLARE @fiscal_quarter INT = CASE 
        WHEN @month IN (7, 8, 9) THEN 1
        WHEN @month IN (10, 11, 12) THEN 2
        WHEN @month IN (1, 2, 3) THEN 3
        ELSE 4
    END;
    
    INSERT INTO dim_date (
        date_key, full_date, year, month, day, quarter, 
        month_name, day_name, is_weekend, fiscal_year, fiscal_quarter
    )
    VALUES (
        @date_key, @current_date, @year, @month, @day, @quarter,
        @month_name, @day_name, @is_weekend, @fiscal_year, @fiscal_quarter
    );
    
    SET @current_date = DATEADD(DAY, 1, @current_date);
END;

-- =====================================================
-- POPULATE SAMPLE MARKETS
-- =====================================================

INSERT INTO dim_markets (market_id, market_name, location, region, latitude, longitude, is_active, created_date) VALUES
(1, 'Makola Market', 'Accra', 'Greater Accra', 5.5500, -0.2167, 1, '2024-01-01'),
(2, 'Kejetia Market', 'Kumasi', 'Ashanti', 6.6885, -1.6244, 1, '2024-01-01'),
(3, 'Tamale Central Market', 'Tamale', 'Northern', 9.4034, -0.8424, 1, '2024-01-01'),
(4, 'Takoradi Market Circle', 'Takoradi', 'Western', 4.8845, -1.7554, 1, '2024-01-01'),
(5, 'Cape Coast Central Market', 'Cape Coast', 'Central', 5.1037, -1.2448, 1, '2024-01-01'),
(6, 'Bolgatanga Market', 'Bolgatanga', 'Upper East', 10.7856, -0.8506, 1, '2024-01-01'),
(7, 'Wa Market', 'Wa', 'Upper West', 10.0601, -2.5057, 1, '2024-01-01'),
(8, 'Ho Central Market', 'Ho', 'Volta', 6.6108, 0.4708, 1, '2024-01-01'),
(9, 'Koforidua New Juaben Market', 'Koforidua', 'Eastern', 6.0898, -0.2599, 1, '2024-01-01'),
(10, 'Sunyani Central Market', 'Sunyani', 'Brong Ahafo', 7.3386, -2.3265, 1, '2024-01-01'),
(11, 'Techiman Market', 'Techiman', 'Brong Ahafo', 7.5931, -1.9303, 1, '2024-01-01'),
(12, 'Aflao Market', 'Aflao', 'Volta', 6.1167, 1.1833, 1, '2024-01-01'),
(13, 'Tema Community 1 Market', 'Tema', 'Greater Accra', 5.6698, -0.0166, 1, '2024-01-01'),
(14, 'Obuasi Market', 'Obuasi', 'Ashanti', 6.2027, -1.6640, 1, '2024-01-01'),
(15, 'Tarkwa Market', 'Tarkwa', 'Western', 5.3006, -1.9959, 1, '2024-01-01');

-- =====================================================
-- POPULATE SAMPLE COMMODITIES
-- =====================================================

INSERT INTO dim_commodities (commodity_id, commodity_name, category, unit_of_measure, is_active, created_date) VALUES
(1, 'Tomatoes', 'Vegetables', 'kg', 1, '2024-01-01'),
(2, 'Maize', 'Grains', 'kg', 1, '2024-01-01'),
(3, 'Rice', 'Grains', 'kg', 1, '2024-01-01'),
(4, 'Yam', 'Tubers', 'kg', 1, '2024-01-01'),
(5, 'Cassava', 'Tubers', 'kg', 1, '2024-01-01'),
(6, 'Plantain', 'Fruits', 'bunch', 1, '2024-01-01'),
(7, 'Onions', 'Vegetables', 'kg', 1, '2024-01-01'),
(8, 'Pepper', 'Vegetables', 'kg', 1, '2024-01-01'),
(9, 'Cocoa', 'Cash Crops', 'kg', 1, '2024-01-01'),
(10, 'Palm Oil', 'Oils', 'litre', 1, '2024-01-01'),
(11, 'Groundnuts', 'Legumes', 'kg', 1, '2024-01-01'),
(12, 'Cowpea', 'Legumes', 'kg', 1, '2024-01-01'),
(13, 'Sorghum', 'Grains', 'kg', 1, '2024-01-01'),
(14, 'Millet', 'Grains', 'kg', 1, '2024-01-01'),
(15, 'Sweet Potato', 'Tubers', 'kg', 1, '2024-01-01');

-- =====================================================
-- POPULATE SAMPLE PRICE DATA
-- =====================================================

-- Insert sample price data for October 2024
INSERT INTO fact_prices (date_key, market_id, commodity_id, raw_price, normalized_price, data_source, quality_score, is_anomaly, created_date) VALUES
(20241001, 1, 1, 8.50, 8.50, 'field_survey', 0.95, 0, '2024-10-01'),
(20241001, 1, 2, 4.20, 4.20, 'field_survey', 0.98, 0, '2024-10-01'),
(20241001, 1, 3, 6.80, 6.80, 'field_survey', 0.92, 0, '2024-10-01'),
(20241001, 2, 1, 7.90, 7.90, 'field_survey', 0.96, 0, '2024-10-01'),
(20241001, 2, 2, 4.10, 4.10, 'field_survey', 0.94, 0, '2024-10-01'),
(20241001, 3, 1, 9.20, 9.20, 'field_survey', 0.89, 0, '2024-10-01'),
(20241001, 3, 2, 4.50, 4.50, 'field_survey', 0.91, 0, '2024-10-01'),
(20241002, 1, 1, 8.75, 8.75, 'field_survey', 0.97, 0, '2024-10-02'),
(20241002, 1, 2, 4.25, 4.25, 'field_survey', 0.96, 0, '2024-10-02'),
(20241002, 2, 1, 8.10, 8.10, 'field_survey', 0.93, 0, '2024-10-02'),
(20241002, 2, 2, 4.15, 4.15, 'field_survey', 0.95, 0, '2024-10-02'),
(20241002, 3, 1, 9.50, 9.50, 'field_survey', 0.88, 0, '2024-10-02'),
(20241002, 3, 2, 4.60, 4.60, 'field_survey', 0.90, 0, '2024-10-02'),
(20241003, 1, 1, 15.20, 15.20, 'field_survey', 0.85, 1, '2024-10-03'),
(20241003, 1, 2, 4.30, 4.30, 'field_survey', 0.97, 0, '2024-10-03'),
(20241003, 2, 1, 8.20, 8.20, 'field_survey', 0.94, 0, '2024-10-03'),
(20241003, 2, 2, 4.20, 4.20, 'field_survey', 0.96, 0, '2024-10-03'),
(20241003, 3, 1, 9.80, 9.80, 'field_survey', 0.87, 0, '2024-10-03'),
(20241003, 3, 2, 4.70, 4.70, 'field_survey', 0.89, 0, '2024-10-03'),
(20241004, 1, 1, 8.60, 8.60, 'field_survey', 0.96, 0, '2024-10-04'),
(20241004, 1, 2, 4.35, 4.35, 'field_survey', 0.95, 0, '2024-10-04'),
(20241004, 2, 1, 8.00, 8.00, 'field_survey', 0.97, 0, '2024-10-04'),
(20241004, 2, 2, 4.25, 4.25, 'field_survey', 0.94, 0, '2024-10-04'),
(20241004, 3, 1, 9.60, 9.60, 'field_survey', 0.90, 0, '2024-10-04'),
(20241004, 3, 2, 4.65, 4.65, 'field_survey', 0.88, 0, '2024-10-04'),
(20241005, 1, 1, 8.80, 8.80, 'field_survey', 0.93, 0, '2024-10-05'),
(20241005, 1, 2, 4.40, 4.40, 'field_survey', 0.96, 0, '2024-10-05'),
(20241005, 2, 1, 8.30, 8.30, 'field_survey', 0.95, 0, '2024-10-05'),
(20241005, 2, 2, 4.30, 4.30, 'field_survey', 0.97, 0, '2024-10-05'),
(20241005, 3, 1, 9.40, 9.40, 'field_survey', 0.91, 0, '2024-10-05');

-- =====================================================
-- POPULATE SAMPLE PRICE RECOMMENDATIONS
-- =====================================================

INSERT INTO fact_price_recommendations (date_key, market_id, commodity_id, recommended_price, confidence_score, explanation, algorithm_version, price_range_min, price_range_max, created_date) VALUES
(20241006, 1, 1, 8.65, 0.92, 'Price recommendation based on 7-day rolling median with seasonal adjustment for October harvest period', '1.0.0', 8.20, 9.10, '2024-10-06'),
(20241006, 1, 2, 4.28, 0.95, 'Stable maize prices with minimal volatility observed across regional markets', '1.0.0', 4.10, 4.45, '2024-10-06'),
(20241006, 2, 1, 8.15, 0.89, 'Kumasi market showing consistent pricing patterns with slight upward trend', '1.0.0', 7.80, 8.50, '2024-10-06'),
(20241006, 2, 2, 4.22, 0.93, 'Maize prices remain steady with good supply from northern regions', '1.0.0', 4.05, 4.40, '2024-10-06'),
(20241006, 3, 1, 9.55, 0.87, 'Northern market premium due to transportation costs and local demand', '1.0.0', 9.20, 9.90, '2024-10-06'),
(20241006, 3, 2, 4.62, 0.90, 'Local maize production supporting stable pricing in northern markets', '1.0.0', 4.45, 4.80, '2024-10-06');

-- =====================================================
-- POPULATE SAMPLE DATA QUALITY METRICS
-- =====================================================

INSERT INTO data_quality_metrics (date_key, source_name, total_records, valid_records, completeness_score, accuracy_score, timeliness_score, consistency_score, created_date) VALUES
(20241001, 'field_survey', 7, 7, 1.00, 0.94, 1.00, 0.98, '2024-10-01'),
(20241002, 'field_survey', 6, 6, 1.00, 0.93, 1.00, 0.97, '2024-10-02'),
(20241003, 'field_survey', 6, 5, 1.00, 0.91, 1.00, 0.85, '2024-10-03'),
(20241004, 'field_survey', 6, 6, 1.00, 0.94, 1.00, 0.96, '2024-10-04'),
(20241005, 'field_survey', 5, 5, 1.00, 0.95, 1.00, 0.98, '2024-10-05');

-- =====================================================
-- POPULATE SAMPLE SYSTEM ALERTS
-- =====================================================

INSERT INTO system_alerts (alert_type, severity, message, component, market_id, commodity_id, alert_date, is_resolved, created_date) VALUES
('price_anomaly', 'high', 'Tomato prices in Makola Market exceeded normal range by 75% - possible supply disruption', 'pricing_engine', 1, 1, '2024-10-03 08:30:00', 0, '2024-10-03'),
('data_quality', 'medium', 'Quality score below threshold for field survey data on 2024-10-03', 'data_pipeline', NULL, NULL, '2024-10-03 09:15:00', 1, '2024-10-03'),
('system_error', 'low', 'API response time exceeded 1 second for price recommendation endpoint', 'api_service', NULL, NULL, '2024-10-04 14:22:00', 1, '2024-10-04');

PRINT 'Database initialization completed successfully!';
PRINT 'Sample data loaded for:';
PRINT '- Date dimension: 2024-2025';
PRINT '- Markets: 15 major Ghana markets';
PRINT '- Commodities: 15 common agricultural products';
PRINT '- Price data: 5 days of sample prices';
PRINT '- Recommendations: 6 sample price recommendations';
PRINT '- Quality metrics: 5 days of data quality tracking';
PRINT '- System alerts: 3 sample alerts';