database.py

import sqlite3
import os
import shutil
import logging
from datetime import datetime, date
from typing import Tuple, Optional, List, Dict

"""
database.py - Manages database operations for ShopEase, a grocery inventory management app.
Uses SQLite for storage with dynamic path handling for local and Hugging Face Spaces deployment.
Supports product names and units in English and Bengali (e.g., "Sugar" or "চিনি").
Includes indexing, pruning, and backup features for scalability and reliability.
"""

# Configure logging with file output for persistence
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('shopease.log'),  # Save logs to a file
        logging.StreamHandler()
    ]
)
logger = logging.getLogger(__name__)

# Determine database path dynamically
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DATA_DIR = os.path.join(BASE_DIR, 'data')
DB_PATH = os.path.join(DATA_DIR, 'inventory.db')

# Create data directory if it doesn’t exist (local use)
if not os.path.exists(DATA_DIR):
    os.makedirs(DATA_DIR)
    logger.info(f"Created data directory at {DATA_DIR}")

# Use Hugging Face Spaces path if available
if os.path.exists('/data'):
    DB_PATH = '/data/inventory.db'
    logger.info("Using Hugging Face Spaces database path: /data/inventory.db")
else:
    logger.info(f"Using local database path: {DB_PATH}")

# Log database file status
if os.path.exists(DB_PATH):
    logger.info(f"Database file exists at {DB_PATH}, size: {os.path.getsize(DB_PATH)} bytes, last modified: {datetime.fromtimestamp(os.path.getmtime(DB_PATH))}")
else:
    logger.warning(f"Database file does not exist at {DB_PATH}, will create new database")

# Connect to SQLite database with WAL mode for better concurrency
try:
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    conn.execute("PRAGMA journal_mode=WAL;")  # Enable Write-Ahead Logging
    c = conn.cursor()
    logger.info("Successfully connected to the database with WAL mode")
except sqlite3.Error as e:
    logger.error(f"Database connection failed: {e}")
    raise

# Create tables and indexes
try:
    c.execute('''CREATE TABLE IF NOT EXISTS products
                 (id INTEGER PRIMARY KEY AUTOINCREMENT, 
                  name TEXT NOT NULL, 
                  unit TEXT NOT NULL)''')
    c.execute('''CREATE TABLE IF NOT EXISTS transactions
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                  product_id INTEGER NOT NULL,
                  quantity REAL NOT NULL,
                  price REAL,
                  date TEXT NOT NULL,
                  FOREIGN KEY(product_id) REFERENCES products(id))''')
    c.execute('''CREATE TABLE IF NOT EXISTS daily_summaries
                 (date TEXT PRIMARY KEY,
                  cash_in REAL NOT NULL,
                  cash_out REAL NOT NULL,
                  purchase_costs REAL NOT NULL,
                  profit_loss REAL NOT NULL)''')
    c.execute("CREATE INDEX IF NOT EXISTS idx_transactions_product_date ON transactions(product_id, date)")
    c.execute("CREATE INDEX IF NOT EXISTS idx_daily_summaries_date ON daily_summaries(date)")
    conn.commit()
    logger.info("Tables and indexes created/verified successfully")
except sqlite3.Error as e:
    logger.error(f"Error creating tables or indexes: {e}")
    raise

# Database Functions

def add_product(name: str, unit: str) -> None:
    """Add a new product to the products table."""
    try:
        c.execute("INSERT INTO products (name, unit) VALUES (?, ?)", (name.strip(), unit.strip()))
        conn.commit()
        logger.info(f"Added product: {name} with unit {unit}")
    except sqlite3.Error as e:
        logger.error(f"Error adding product {name}: {e}")
        raise

def add_transaction(product_id: int, quantity: float, price: Optional[float] = None) -> None:
    """Add a transaction (purchase or sale) to the transactions table."""
    try:
        date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        c.execute("INSERT INTO transactions (product_id, quantity, price, date) VALUES (?, ?, ?, ?)",
                  (product_id, quantity, price, date))
        conn.commit()
        logger.info(f"Added transaction for product_id {product_id}: {quantity} at total cost {price} INR on {date}")
    except sqlite3.Error as e:
        logger.error(f"Error adding transaction for product_id {product_id}: {e}")
        raise

def get_current_quantity(product_id: int) -> float:
    """Calculate the current quantity of a product."""
    try:
        c.execute("SELECT SUM(quantity) FROM transactions WHERE product_id = ?", (product_id,))
        result = c.fetchone()[0]
        return result if result is not None else 0.0
    except sqlite3.Error as e:
        logger.error(f"Error getting current quantity for product_id {product_id}: {e}")
        raise

def get_last_price_before_date(product_id: int, target_date: Optional[str] = None) -> Optional[float]:
    """Get the last total purchase cost before a specific date."""
    try:
        if target_date is None:
            target_date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        c.execute("SELECT price FROM transactions WHERE product_id = ? AND quantity > 0 AND price IS NOT NULL AND date <= ? ORDER BY date DESC LIMIT 1",
                  (product_id, target_date))
        result = c.fetchone()
        return result[0] if result else None
    except sqlite3.Error as e:
        logger.error(f"Error getting last price for product_id {product_id} on {target_date}: {e}")
        raise

def get_last_purchase_date(product_id: int, target_date: Optional[str] = None) -> Optional[str]:
    """Get the date of the last purchase before a specific date."""
    try:
        if target_date is None:
            target_date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        c.execute("SELECT date FROM transactions WHERE product_id = ? AND quantity > 0 AND date <= ? ORDER BY date DESC LIMIT 1",
                  (product_id, target_date))
        result = c.fetchone()
        return result[0] if result else None
    except sqlite3.Error as e:
        logger.error(f"Error getting last purchase date for product_id {product_id} on {target_date}: {e}")
        raise

def get_purchase_history(product_id: int, start_date: Optional[date] = None, end_date: Optional[date] = None) -> List[Tuple[str, float, float]]:
    """Retrieve purchase history for a product within a date range."""
    try:
        query = "SELECT date, quantity, price FROM transactions WHERE product_id = ? AND quantity > 0"
        params = [product_id]
        if start_date:
            query += " AND date >= ?"
            params.append(start_date.strftime("%Y-%m-%d 00:00:00"))
        if end_date:
            query += " AND date <= ?"
            params.append(end_date.strftime("%Y-%m-%d 23:59:59"))
        query += " ORDER BY date ASC"
        c.execute(query, params)
        return c.fetchall()
    except sqlite3.Error as e:
        logger.error(f"Error retrieving purchase history for product_id {product_id}: {e}")
        raise

def get_all_products() -> List[Tuple[int, str, str]]:
    """Retrieve all products."""
    try:
        c.execute("SELECT id, name, unit FROM products")
        return c.fetchall()
    except sqlite3.Error as e:
        logger.error(f"Error retrieving all products: {e}")
        raise

def get_product_id(name: str) -> Optional[int]:
    """Get the ID of a product by its name."""
    try:
        c.execute("SELECT id FROM products WHERE name = ?", (name.strip(),))
        result = c.fetchone()
        return result[0] if result else None
    except sqlite3.Error as e:
        logger.error(f"Error getting ID for product {name}: {e}")
        raise

def get_transactions_by_date(selected_date: date) -> List[Tuple[int, str, float, float, str, str]]:
    """Retrieve all purchase transactions for a specific date."""
    try:
        start_date = selected_date.strftime("%Y-%m-%d 00:00:00")
        end_date = selected_date.strftime("%Y-%m-%d 23:59:59")
        c.execute("SELECT t.id, p.name, t.quantity, t.price, p.unit, t.date FROM transactions t JOIN products p ON t.product_id = p.id WHERE t.quantity > 0 AND t.date BETWEEN ? AND ?",
                  (start_date, end_date))
        return c.fetchall()
    except sqlite3.Error as e:
        logger.error(f"Error retrieving transactions for {selected_date}: {e}")
        raise

def delete_transaction(transaction_id: int) -> Tuple[bool, str]:
    """Delete a specific transaction and clean up products if no transactions remain."""
    try:
        c.execute("SELECT product_id, quantity FROM transactions WHERE id = ?", (transaction_id,))
        trans = c.fetchone()
        if trans:
            product_id, quantity = trans
            current_qty = get_current_quantity(product_id)
            if current_qty - quantity < 0:
                return False, "Cannot delete: would result in negative stock."
            c.execute("DELETE FROM transactions WHERE id = ?", (transaction_id,))
            conn.commit()
            logger.info(f"Deleted transaction {transaction_id} for product_id {product_id}")
            c.execute("SELECT COUNT(*) FROM transactions WHERE product_id = ?", (product_id,))
            if c.fetchone()[0] == 0:
                c.execute("DELETE FROM products WHERE id = ?", (product_id,))
                conn.commit()
                logger.info(f"Cleaned up product_id {product_id} with no remaining transactions")
            return True, "Transaction deleted successfully."
        return False, "Transaction not found."
    except sqlite3.Error as e:
        logger.error(f"Error deleting transaction {transaction_id}: {e}")
        raise

def get_daily_transactions(selected_date: date) -> List[Tuple[str, float, float, str]]:
    """Retrieve all transactions (purchases and sales) for a specific date."""
    try:
        start_date = selected_date.strftime("%Y-%m-%d 00:00:00")
        end_date = selected_date.strftime("%Y-%m-%d 23:59:59")
        c.execute("""
            SELECT p.name, t.quantity, t.price, 
                   CASE WHEN t.quantity > 0 THEN 'purchase' ELSE 'sale' END as type
            FROM transactions t 
            JOIN products p ON t.product_id = p.id 
            WHERE t.date BETWEEN ? AND ?
        """, (start_date, end_date))
        return c.fetchall()
    except sqlite3.Error as e:
        logger.error(f"Error retrieving daily transactions for {selected_date}: {e}")
        raise

def calculate_daily_earnings(selected_date: date) -> float:
    """Calculate daily earnings (sales revenue - purchase costs)."""
    try:
        transactions = get_daily_transactions(selected_date)
        purchase_cost = sum(price for _, quantity, price, trans_type in transactions if trans_type == 'purchase' and price is not None)
        sale_revenue = sum(price * abs(quantity) for _, quantity, price, trans_type in transactions if trans_type == 'sale' and price is not None)
        net_earnings = sale_revenue - purchase_cost
        logger.info(f"Calculated daily earnings for {selected_date}: Sales Revenue={sale_revenue}, Purchase Cost={purchase_cost}, Net={net_earnings}")
        return net_earnings
    except Exception as e:
        logger.error(f"Error calculating daily earnings for {selected_date}: {e}")
        raise

def estimate_daily_needs(selected_date: date) -> Dict[str, float]:
    """Estimate daily product needs based on sales and inventory."""
    try:
        transactions = get_daily_transactions(selected_date)
        needs = {}
        for product_name, quantity, _, trans_type in transactions:
            if trans_type == 'sale':
                needs[product_name] = needs.get(product_name, 0) + abs(quantity)
        for product_id, name, unit in get_all_products():
            current_qty = get_current_quantity(product_id)
            if name in needs and current_qty < needs[name]:
                needs[name] = max(0, needs[name] - current_qty)
            elif name not in needs and current_qty == 0:
                needs[name] = 0.0
        return needs
    except Exception as e:
        logger.error(f"Error estimating daily needs for {selected_date}: {e}")
        raise

def save_daily_summary(selected_date: date, cash_in: float, cash_out: float, purchase_costs: float, profit_loss: float) -> None:
    """Save or update a daily cash flow summary."""
    try:
        date_str = selected_date.isoformat()
        cash_in = float(cash_in) if cash_in is not None else 0.0
        cash_out = float(cash_out) if cash_out is not None else 0.0
        purchase_costs = float(purchase_costs) if purchase_costs is not None else 0.0
        profit_loss = float(profit_loss) if profit_loss is not None else 0.0

        c.execute("""
            INSERT OR REPLACE INTO daily_summaries (date, cash_in, cash_out, purchase_costs, profit_loss)
            VALUES (?, ?, ?, ?, ?)
        """, (date_str, cash_in, cash_out, purchase_costs, profit_loss))
        conn.commit()
        logger.info(f"Saved daily summary for {date_str}: Cash In={cash_in}, Cash Out={cash_out}, Purchase Costs={purchase_costs}, Profit/Loss={profit_loss}")
    except (sqlite3.Error, ValueError) as e:
        logger.error(f"Error saving daily summary for {selected_date}: {e}")
        raise

def get_daily_summary(selected_date: date) -> Optional[Tuple[float, float, float, float]]:
    """Retrieve the daily cash flow summary for a specific date."""
    try:
        date_str = selected_date.isoformat()
        logger.info(f"Querying daily summary for date: {date_str}")
        c.execute("SELECT cash_in, cash_out, purchase_costs, profit_loss FROM daily_summaries WHERE date = ?", (date_str,))
        result = c.fetchone()
        if result:
            logger.info(f"Found summary for {date_str}: {result}")
            return (float(result[0]), float(result[1]), float(result[2]), float(result[3]))
        logger.info(f"No summary found for {date_str}")
        return None
    except sqlite3.Error as e:
        logger.error(f"Error retrieving daily summary for {selected_date}: {e}")
        raise

def delete_daily_summary(selected_date: date) -> Tuple[bool, str]:
    """Delete a daily summary and associated purchase transactions with confirmation."""
    try:
        date_str = selected_date.isoformat()
        start_date = selected_date.strftime("%Y-%m-%d 00:00:00")
        end_date = selected_date.strftime("%Y-%m-%d 23:59:59")

        # Check if summary exists
        c.execute("SELECT date FROM daily_summaries WHERE date = ?", (date_str,))
        summary_exists = c.fetchone() is not None
        logger.info(f"Before deletion, summary exists for {date_str}: {summary_exists}")

        # Count transactions to delete
        c.execute("SELECT COUNT(*) FROM transactions WHERE date BETWEEN ? AND ? AND quantity > 0", (start_date, end_date))
        trans_count = c.fetchone()[0]
        logger.info(f"Found {trans_count} purchase transactions to delete for {date_str}")

        # Delete transactions
        c.execute("DELETE FROM transactions WHERE date BETWEEN ? AND ? AND quantity > 0", (start_date, end_date))
        trans_deleted = c.rowcount
        logger.info(f"Deleted {trans_deleted} transactions for {date_str}")

        # Delete summary
        c.execute("DELETE FROM daily_summaries WHERE date = ?", (date_str,))
        summary_deleted = c.rowcount
        logger.info(f"Deleted {summary_deleted} summary rows for {date_str}")

        conn.commit()

        # Verify deletion
        c.execute("SELECT date FROM daily_summaries WHERE date = ?", (date_str,))
        post_check = c.fetchone()
        if post_check is not None:
            logger.error(f"Deletion failed: Summary still exists for {date_str} after delete: {post_check}")
            raise sqlite3.Error(f"Failed to delete summary for {date_str}")

        if summary_exists or trans_deleted > 0:
            message = f"Daily summary and {trans_deleted} transaction(s) for {date_str} deleted successfully."
            logger.info(message)
            return True, message
        else:
            message = f"No daily summary or transactions found for {date_str}."
            logger.info(message)
            return False, message
    except sqlite3.Error as e:
        logger.error(f"Error deleting daily summary and transactions for {selected_date}: {e}")
        conn.rollback()
        raise

def get_summaries_by_range(start_date: date, end_date: date) -> List[Tuple[date, float, float, float, float]]:
    """Retrieve all daily summaries within a date range."""
    try:
        start_str = start_date.isoformat()
        end_str = end_date.isoformat()
        logger.info(f"Querying summaries for range {start_str} to {end_str}")
        c.execute("SELECT date, cash_in, cash_out, purchase_costs, profit_loss FROM daily_summaries WHERE date BETWEEN ? AND ? ORDER BY date",
                  (start_str, end_str))
        results = c.fetchall()
        summaries = [(datetime.strptime(r[0], "%Y-%m-%d").date(), float(r[1]), float(r[2]), float(r[3]), float(r[4])) for r in results]
        logger.info(f"Retrieved {len(summaries)} summaries for range {start_str} to {end_str}")
        return summaries
    except sqlite3.Error as e:
        logger.error(f"Error retrieving summaries for range {start_date} to {end_date}: {e}")
        raise

def prune_old_transactions(keep_years: int = 2) -> None:
    """Remove transactions older than a specified number of years with safeguards."""
    try:
        if keep_years < 1:
            logger.error("Pruning with keep_years < 1 is not allowed to prevent accidental data loss")
            raise ValueError("keep_years must be at least 1")
        cutoff_date = datetime.now().replace(year=datetime.now().year - keep_years).strftime("%Y-%m-%d %H:%M:%S")
        c.execute("SELECT COUNT(*) FROM transactions WHERE date < ?", (cutoff_date,))
        count = c.fetchone()[0]
        logger.info(f"Found {count} transactions to prune before {cutoff_date}")
        c.execute("DELETE FROM transactions WHERE date < ?", (cutoff_date,))
        deleted_count = c.rowcount
        conn.commit()
        logger.info(f"Pruned {deleted_count} transactions older than {keep_years} years (cutoff: {cutoff_date})")
    except (sqlite3.Error, ValueError) as e:
        logger.error(f"Error pruning old transactions: {e}")
        raise

def backup_database(backup_path: str = None) -> None:
    """Create a backup of the current database with timestamp."""
    try:
        if backup_path is None:
            timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
            backup_path = os.path.join(DATA_DIR, f"backup_inventory_{timestamp}.db")
        shutil.copy2(DB_PATH, backup_path)
        logger.info(f"Backed up database to {backup_path}")
    except OSError as e:
        logger.error(f"Error backing up database: {e}")
        raise

# Periodic backup on startup
try:
    backup_database()
except Exception as e:
    logger.error(f"Failed to create initial backup: {e}")

# Note: Connection is left open for Streamlit; close manually if needed in other contexts
# conn.close()