db_manager.py

import sqlite3
import pandas as pd
from datetime import datetime
from contextlib import contextmanager

class DBManager:
    """Manages SQLite database for BTA Furnace monitoring system"""
    
    def __init__(self, db_path='bta_furnace.db'):
        self.db_path = db_path
        self.init_schema()
    
    @contextmanager
    def get_connection(self):
        """Context manager untuk database connection"""
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row  # Enable column access by name
        try:
            yield conn
        finally:
            conn.close()
    
    def init_schema(self):
        """Initialize database schema jika belum ada"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            
            # Tabel data measurement
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS data_measurement (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
                    cone_depan REAL,
                    bodi_tengah REAL,
                    cone_belakang REAL,
                    suhu_avg REAL,
                    ketebalan_actual REAL,
                    ketebalan_prediksi REAL,
                    status TEXT,
                    is_flagged INTEGER DEFAULT 0
                )
            ''')
            
            # Tabel maintenance log
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS maintenance_log (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    maintenance_date DATETIME DEFAULT CURRENT_TIMESTAMP,
                    previous_thickness REAL,
                    reset_to_thickness REAL,
                    notes TEXT
                )
            ''')
            
            # Tabel app config
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS app_config (
                    key TEXT PRIMARY KEY,
                    value TEXT
                )
            ''')
            
            # Insert default config jika belum ada
            cursor.execute("SELECT COUNT(*) FROM app_config")
            if cursor.fetchone()[0] == 0:
                defaults = [
                    ('min_safe_thickness', '115'),
                    ('threshold_temp', '400'),
                    ('slaging_interval', '300'),
                    ('reset_thickness', '230'),
                    ('laju_penipisan', '0.35')
                ]
                cursor.executemany("INSERT INTO app_config (key, value) VALUES (?, ?)", defaults)
            
            conn.commit()
    
    def insert_measurement(self, cone_depan, bodi_tengah, cone_belakang, ketebalan_actual, 
                          ketebalan_prediksi, status='AMAN', is_flagged=False):
        """Insert measurement data ke database"""
        suhu_avg = (cone_depan + bodi_tengah + cone_belakang) / 3
        
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''
                INSERT INTO data_measurement 
                (cone_depan, bodi_tengah, cone_belakang, suhu_avg, ketebalan_actual, 
                 ketebalan_prediksi, status, is_flagged)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ''', (cone_depan, bodi_tengah, cone_belakang, suhu_avg, ketebalan_actual, 
                  ketebalan_prediksi, status, int(is_flagged)))
            conn.commit()
            return cursor.lastrowid
    
    def get_latest_measurement(self):
        """Get latest measurement"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''
                SELECT * FROM data_measurement 
                ORDER BY timestamp DESC LIMIT 1
            ''')
            row = cursor.fetchone()
            if row:
                result = dict(row)
                # Ensure numeric columns are float
                for col in ['cone_depan', 'bodi_tengah', 'cone_belakang', 'suhu_avg', 'ketebalan_actual', 'ketebalan_prediksi']:
                    if col in result and result[col] is not None:
                        result[col] = float(result[col])
                return result
            return None
    
    def get_historis(self, limit=100, flagged_only=False):
        """Get historical measurements"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            
            if flagged_only:
                query = '''
                    SELECT * FROM data_measurement 
                    WHERE is_flagged = 1
                    ORDER BY timestamp DESC LIMIT ?
                '''
            else:
                query = '''
                    SELECT * FROM data_measurement 
                    ORDER BY timestamp DESC LIMIT ?
                '''
            
            cursor.execute(query, (limit,))
            rows = cursor.fetchall()
            results = []
            for row in rows:
                result = dict(row)
                # Ensure numeric columns are float
                for col in ['cone_depan', 'bodi_tengah', 'cone_belakang', 'suhu_avg', 'ketebalan_actual', 'ketebalan_prediksi']:
                    if col in result and result[col] is not None:
                        result[col] = float(result[col])
                results.append(result)
            return results
    
    def get_all_data_as_dataframe(self):
        """Get all measurement data sebagai Pandas DataFrame"""
        with self.get_connection() as conn:
            df = pd.read_sql_query(
                "SELECT * FROM data_measurement ORDER BY timestamp ASC",
                conn
            )
            return df
    
    def log_maintenance(self, previous_thickness, reset_to_thickness=230, notes=''):
        """Log maintenance event"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''
                INSERT INTO maintenance_log 
                (previous_thickness, reset_to_thickness, notes)
                VALUES (?, ?, ?)
            ''', (previous_thickness, reset_to_thickness, notes))
            conn.commit()
            return cursor.lastrowid
    
    def get_maintenance_history(self, limit=50):
        """Get maintenance history"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''
                SELECT * FROM maintenance_log 
                ORDER BY maintenance_date DESC LIMIT ?
            ''', (limit,))
            rows = cursor.fetchall()
            return [dict(row) for row in rows]
    
    def get_config(self, key):
        """Get config value"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT value FROM app_config WHERE key = ?", (key,))
            row = cursor.fetchone()
            value = row[0] if row else None
            # Ensure string type
            if value is not None and isinstance(value, bytes):
                value = value.decode('utf-8')
            return value
    
    def set_config(self, key, value):
        """Set config value"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(
                "INSERT OR REPLACE INTO app_config (key, value) VALUES (?, ?)",
                (key, str(value))
            )
            conn.commit()
    
    def get_stats(self):
        """Get statistics untuk dashboard summary"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            
            # Total measurements
            cursor.execute("SELECT COUNT(*) FROM data_measurement")
            total_measurements = cursor.fetchone()[0]
            
            # Total flagged (slaging)
            cursor.execute("SELECT COUNT(*) FROM data_measurement WHERE is_flagged = 1")
            total_flagged = cursor.fetchone()[0]
            
            # Latest measurement
            cursor.execute('''
                SELECT ketebalan_prediksi, timestamp FROM data_measurement 
                ORDER BY timestamp DESC LIMIT 1
            ''')
            latest = cursor.fetchone()
            current_thickness = float(latest[0]) if latest else 230.0
            
            # Total maintenance events
            cursor.execute("SELECT COUNT(*) FROM maintenance_log")
            total_maintenance = cursor.fetchone()[0]
            
            return {
                'total_measurements': total_measurements,
                'total_flagged': total_flagged,
                'current_thickness': current_thickness,
                'total_maintenance': total_maintenance
            }