-- Pipeline monitoring tables for CleanKitchens admin dashboard
-- Run this to set up monitoring infrastructure

USE cleankitchens;

-- 1. Pipeline runs tracking
CREATE TABLE IF NOT EXISTS pipeline_runs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    run_id VARCHAR(50) UNIQUE NOT NULL,
    jurisdiction VARCHAR(50) NOT NULL,
    run_type ENUM('api_import', 'csv_import', 'processing', 'full_pipeline') NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME DEFAULT NULL,
    status ENUM('running', 'completed', 'failed', 'partial') DEFAULT 'running',
    
    -- Import metrics
    records_checked INT DEFAULT 0,
    records_added INT DEFAULT 0,
    records_updated INT DEFAULT 0,
    records_skipped INT DEFAULT 0,
    
    -- Processing metrics
    records_processed INT DEFAULT 0,
    metadata_extracted INT DEFAULT 0,
    articles_generated INT DEFAULT 0,
    articles_published INT DEFAULT 0,
    
    -- Error tracking
    records_failed INT DEFAULT 0,
    error_count INT DEFAULT 0,
    last_error TEXT,
    
    -- Additional info
    source VARCHAR(100),
    script_name VARCHAR(255),
    notes TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_jurisdiction (jurisdiction),
    INDEX idx_run_type (run_type),
    INDEX idx_start_time (start_time),
    INDEX idx_status (status)
);

-- 2. Detailed processing logs
CREATE TABLE IF NOT EXISTS processing_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    run_id VARCHAR(50),
    inspection_id VARCHAR(50),
    jurisdiction VARCHAR(50),
    
    action ENUM('import', 'metadata_extract', 'article_generate', 'publish', 'skip', 'error') NOT NULL,
    status ENUM('success', 'skipped', 'failed', 'in_progress') NOT NULL,
    reason TEXT,
    details JSON,
    
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    processing_time_ms INT DEFAULT NULL,
    
    INDEX idx_run_id (run_id),
    INDEX idx_inspection_id (inspection_id),
    INDEX idx_timestamp (timestamp),
    INDEX idx_action_status (action, status),
    FOREIGN KEY (run_id) REFERENCES pipeline_runs(run_id) ON DELETE CASCADE
);

-- 3. Article edits and retractions
CREATE TABLE IF NOT EXISTS article_edits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    article_slug VARCHAR(500) NOT NULL,
    inspection_id VARCHAR(50),
    jurisdiction VARCHAR(50),
    
    edit_type ENUM('correction', 'retraction', 'update', 'unpublish') NOT NULL,
    original_content LONGTEXT,
    new_content LONGTEXT,
    changes_summary TEXT,
    reason TEXT NOT NULL,
    
    edited_by VARCHAR(100) NOT NULL,
    edited_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    approved_by VARCHAR(100),
    approved_at DATETIME,
    
    INDEX idx_article_slug (article_slug(255)),
    INDEX idx_inspection_id (inspection_id),
    INDEX idx_edit_type (edit_type),
    INDEX idx_edited_at (edited_at)
);

-- 4. Daily statistics summary
CREATE TABLE IF NOT EXISTS daily_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stat_date DATE NOT NULL,
    jurisdiction VARCHAR(50) NOT NULL,
    
    -- Import stats
    imports_attempted INT DEFAULT 0,
    imports_successful INT DEFAULT 0,
    imports_failed INT DEFAULT 0,
    
    -- Processing stats
    records_processed INT DEFAULT 0,
    articles_generated INT DEFAULT 0,
    articles_published INT DEFAULT 0,
    articles_skipped INT DEFAULT 0,
    
    -- Queue stats
    queue_depth INT DEFAULT 0,
    oldest_unprocessed_date DATE,
    
    -- Performance stats
    avg_processing_time_ms INT,
    max_processing_time_ms INT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_date_jurisdiction (stat_date, jurisdiction),
    INDEX idx_stat_date (stat_date)
);

-- 5. System alerts
CREATE TABLE IF NOT EXISTS system_alerts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    alert_type ENUM('error', 'warning', 'info') NOT NULL,
    severity ENUM('low', 'medium', 'high', 'critical') NOT NULL,
    jurisdiction VARCHAR(50),
    
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    details JSON,
    
    acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_by VARCHAR(100),
    acknowledged_at DATETIME,
    resolved BOOLEAN DEFAULT FALSE,
    resolved_at DATETIME,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_severity (severity),
    INDEX idx_acknowledged (acknowledged),
    INDEX idx_created_at (created_at)
);

-- 6. Jurisdiction status
CREATE TABLE IF NOT EXISTS jurisdiction_status (
    jurisdiction VARCHAR(50) PRIMARY KEY,
    
    -- Current status
    is_active BOOLEAN DEFAULT TRUE,
    last_import_run DATETIME,
    last_processing_run DATETIME,
    last_successful_run DATETIME,
    
    -- Counts
    total_records INT DEFAULT 0,
    processed_records INT DEFAULT 0,
    published_articles INT DEFAULT 0,
    pending_records INT DEFAULT 0,
    
    -- Configuration
    api_endpoint VARCHAR(500),
    update_frequency VARCHAR(50) DEFAULT 'daily',
    config JSON,
    
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_is_active (is_active)
);

-- Create initial jurisdiction entries
INSERT INTO jurisdiction_status (jurisdiction, is_active, api_endpoint) VALUES
('chicago', TRUE, 'https://data.cityofchicago.org/resource/4ijn-s7e5.json'),
('sf', FALSE, 'https://data.sfgov.org/resource/pyih-qa8i.json'),
('nyc', FALSE, NULL),
('miami', FALSE, NULL)
ON DUPLICATE KEY UPDATE updated_at = NOW();

-- Create view for current pipeline status
CREATE OR REPLACE VIEW current_pipeline_status AS
SELECT 
    j.jurisdiction,
    j.is_active,
    j.total_records,
    j.processed_records,
    j.pending_records,
    pr.run_id as latest_run_id,
    pr.run_type as latest_run_type,
    pr.start_time as latest_run_start,
    pr.status as latest_run_status,
    pr.records_added as latest_records_added,
    pr.articles_published as latest_articles_published
FROM jurisdiction_status j
LEFT JOIN (
    SELECT jurisdiction, 
           run_id, run_type, start_time, status, 
           records_added, articles_published,
           ROW_NUMBER() OVER (PARTITION BY jurisdiction ORDER BY start_time DESC) as rn
    FROM pipeline_runs
) pr ON j.jurisdiction = pr.jurisdiction AND pr.rn = 1;

-- Create stored procedure to update jurisdiction stats
DELIMITER $$
CREATE PROCEDURE update_jurisdiction_stats(IN p_jurisdiction VARCHAR(50))
BEGIN
    DECLARE v_total INT;
    DECLARE v_processed INT;
    DECLARE v_pending INT;
    DECLARE v_published INT;
    
    -- Get counts from staging table (table name based on jurisdiction)
    SET @sql = CONCAT('SELECT COUNT(*) INTO @v_total FROM ', p_jurisdiction, '_staging');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    SET @sql = CONCAT('SELECT COUNT(*) INTO @v_processed FROM ', p_jurisdiction, '_staging WHERE processing_status = "published"');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    SET @sql = CONCAT('SELECT COUNT(*) INTO @v_pending FROM ', p_jurisdiction, '_staging WHERE processing_status = "raw"');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- Update jurisdiction status
    UPDATE jurisdiction_status 
    SET total_records = @v_total,
        processed_records = @v_processed,
        pending_records = @v_pending,
        updated_at = NOW()
    WHERE jurisdiction = p_jurisdiction;
END$$
DELIMITER ;

-- Grant permissions if needed
-- GRANT ALL ON cleankitchens.* TO 'your_user'@'localhost';