markitect-main/markitect/finance/models.py

"""
Database models and schema for MarkiTect cost tracking system.

This module defines the complete database schema for financial tracking including:
- Cost categories and items (recurring/one-time)
- Calculation periods and status management
- Cost transactions and audit trail
- Issue cost allocations
- Issue activity tracking for cost allocation

The schema follows double-entry bookkeeping principles with comprehensive
audit trails for all financial operations.
"""

import sqlite3
import os
from datetime import datetime, date
from decimal import Decimal
from typing import Optional, Dict, Any, List
from pathlib import Path


class FinanceModels:
    """Database model manager for finance-related tables."""

    def __init__(self, db_path: str):
        """
        Initialize finance models manager.

        Args:
            db_path: Path to SQLite database file
        """
        self.db_path = db_path

    def initialize_finance_schema(self) -> None:
        """
        Initialize all finance-related database tables.

        Creates comprehensive schema for cost tracking including:
        - Cost categories and items
        - Calculation periods
        - Cost transactions (audit trail)
        - Issue cost allocations
        - Issue activity tracking
        """
        # Ensure directory exists
        db_dir = os.path.dirname(self.db_path)
        if db_dir and not os.path.exists(db_dir):
            os.makedirs(db_dir)

        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            # Enable foreign key constraints
            cursor.execute('PRAGMA foreign_keys = ON')

            # Create cost categories table
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS cost_categories (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL UNIQUE,
                    description TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')

            # Create cost items table
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS cost_items (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    category_id INTEGER REFERENCES cost_categories(id),
                    name TEXT NOT NULL,
                    description TEXT,
                    cost_type TEXT CHECK (cost_type IN ('monthly', 'one_time')) NOT NULL,
                    amount_eur DECIMAL(10,2) NOT NULL CHECK (amount_eur >= 0),
                    currency TEXT DEFAULT 'EUR',
                    starting_from_date DATE NOT NULL,
                    ending_date DATE,
                    is_active BOOLEAN DEFAULT TRUE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    CONSTRAINT valid_date_range CHECK (ending_date IS NULL OR ending_date >= starting_from_date),
                    CONSTRAINT active_ongoing CHECK (NOT (is_active = FALSE AND ending_date IS NULL))
                )
            ''')

            # Create calculation periods table
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS cost_periods (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    period_start DATE NOT NULL,
                    period_end DATE NOT NULL,
                    period_type TEXT DEFAULT 'monthly',
                    status TEXT CHECK (status IN ('open', 'calculating', 'closed')) DEFAULT 'open',
                    total_costs DECIMAL(10,2) DEFAULT 0 CHECK (total_costs >= 0),
                    active_issues_count INTEGER DEFAULT 0 CHECK (active_issues_count >= 0),
                    cost_per_issue DECIMAL(10,2) DEFAULT 0 CHECK (cost_per_issue >= 0),
                    loss_carried_forward DECIMAL(10,2) DEFAULT 0 CHECK (loss_carried_forward >= 0),
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    CONSTRAINT valid_period CHECK (period_end >= period_start),
                    CONSTRAINT unique_period UNIQUE (period_start, period_end)
                )
            ''')

            # Create cost transactions table (audit trail)
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS cost_transactions (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    period_id INTEGER REFERENCES cost_periods(id),
                    cost_item_id INTEGER REFERENCES cost_items(id),
                    transaction_type TEXT CHECK (transaction_type IN
                        ('cost_incurred', 'cost_allocated', 'loss_forward', 'adjustment')) NOT NULL,
                    amount_eur DECIMAL(10,2) NOT NULL,
                    issue_id INTEGER,
                    transaction_date DATE NOT NULL,
                    description TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    CONSTRAINT positive_allocated_amount CHECK (
                        transaction_type != 'cost_allocated' OR amount_eur > 0
                    )
                )
            ''')

            # Create issue cost allocations table
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS issue_cost_allocations (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    issue_id INTEGER NOT NULL,
                    period_id INTEGER REFERENCES cost_periods(id),
                    allocated_amount DECIMAL(10,2) NOT NULL CHECK (allocated_amount > 0),
                    allocation_date DATE NOT NULL,
                    transaction_id INTEGER REFERENCES cost_transactions(id),
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    CONSTRAINT unique_issue_period UNIQUE (issue_id, period_id)
                )
            ''')

            # Create issue activity log table
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS issue_activity_log (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    issue_id INTEGER NOT NULL,
                    activity_type TEXT CHECK (activity_type IN
                        ('created', 'modified', 'closed', 'reopened', 'commented', 'status_changed')) NOT NULL,
                    activity_date DATE NOT NULL,
                    period_id INTEGER REFERENCES cost_periods(id),
                    activity_details TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')

            # Create indexes for performance
            self._create_indexes(cursor)

            # Insert default cost categories
            self._insert_default_categories(cursor)

            conn.commit()
            # Success - schema initialized (silent for CLI compatibility)

        except sqlite3.Error as e:
            conn.rollback()
            # Re-raise without printing to avoid interfering with CLI output
            raise

        finally:
            conn.close()

    def _create_indexes(self, cursor: sqlite3.Cursor) -> None:
        """Create database indexes for performance optimization."""
        indexes = [
            # Cost items indexes
            'CREATE INDEX IF NOT EXISTS idx_cost_items_active ON cost_items(is_active)',
            'CREATE INDEX IF NOT EXISTS idx_cost_items_type ON cost_items(cost_type)',
            'CREATE INDEX IF NOT EXISTS idx_cost_items_dates ON cost_items(starting_from_date, ending_date)',
            'CREATE INDEX IF NOT EXISTS idx_cost_items_category ON cost_items(category_id)',

            # Cost periods indexes
            'CREATE INDEX IF NOT EXISTS idx_cost_periods_status ON cost_periods(status)',
            'CREATE INDEX IF NOT EXISTS idx_cost_periods_dates ON cost_periods(period_start, period_end)',

            # Cost transactions indexes
            'CREATE INDEX IF NOT EXISTS idx_cost_transactions_period ON cost_transactions(period_id)',
            'CREATE INDEX IF NOT EXISTS idx_cost_transactions_type ON cost_transactions(transaction_type)',
            'CREATE INDEX IF NOT EXISTS idx_cost_transactions_issue ON cost_transactions(issue_id)',
            'CREATE INDEX IF NOT EXISTS idx_cost_transactions_date ON cost_transactions(transaction_date)',

            # Issue cost allocations indexes
            'CREATE INDEX IF NOT EXISTS idx_issue_allocations_issue ON issue_cost_allocations(issue_id)',
            'CREATE INDEX IF NOT EXISTS idx_issue_allocations_period ON issue_cost_allocations(period_id)',

            # Issue activity log indexes
            'CREATE INDEX IF NOT EXISTS idx_issue_activity_issue ON issue_activity_log(issue_id)',
            'CREATE INDEX IF NOT EXISTS idx_issue_activity_date ON issue_activity_log(activity_date)',
            'CREATE INDEX IF NOT EXISTS idx_issue_activity_period ON issue_activity_log(period_id)',
            'CREATE INDEX IF NOT EXISTS idx_issue_activity_type ON issue_activity_log(activity_type)'
        ]

        for index_sql in indexes:
            cursor.execute(index_sql)

    def _insert_default_categories(self, cursor: sqlite3.Cursor) -> None:
        """Insert default cost categories."""
        default_categories = [
            ('Infrastructure', 'Server hosting, cloud services, and infrastructure costs'),
            ('Software', 'SaaS subscriptions, licenses, and software tools'),
            ('Domain & DNS', 'Domain registration, DNS services, SSL certificates'),
            ('Development Tools', 'IDEs, development platforms, and productivity tools'),
            ('AI & ML Services', 'LLM APIs, AI tools, and machine learning services'),
            ('Marketing', 'Marketing tools, analytics, and promotional services'),
            ('Support & Maintenance', 'Support contracts, maintenance fees, and updates'),
            ('One-time Expenses', 'Setup fees, equipment purchases, and project-specific costs')
        ]

        for name, description in default_categories:
            cursor.execute('''
                INSERT OR IGNORE INTO cost_categories (name, description)
                VALUES (?, ?)
            ''', (name, description))

    def get_connection(self) -> sqlite3.Connection:
        """
        Get a database connection with proper configuration.

        Returns:
            SQLite connection with foreign keys enabled
        """
        conn = sqlite3.connect(self.db_path)
        conn.execute('PRAGMA foreign_keys = ON')
        return conn

    def validate_schema(self) -> bool:
        """
        Validate that the finance schema is properly initialized.

        Returns:
            True if all required tables exist, False otherwise
        """
        required_tables = [
            'cost_categories',
            'cost_items',
            'cost_periods',
            'cost_transactions',
            'issue_cost_allocations',
            'issue_activity_log'
        ]

        conn = self.get_connection()
        cursor = conn.cursor()

        try:
            cursor.execute('''
                SELECT name FROM sqlite_master
                WHERE type='table' AND name IN ({})
            '''.format(','.join('?' * len(required_tables))), required_tables)

            existing_tables = {row[0] for row in cursor.fetchall()}
            missing_tables = set(required_tables) - existing_tables

            if missing_tables:
                return False

            return True

        except sqlite3.Error as e:
            return False

        finally:
            conn.close()

    def get_schema_info(self) -> Dict[str, Any]:
        """
        Get information about the finance database schema.

        Returns:
            Dictionary with schema information
        """
        conn = self.get_connection()
        cursor = conn.cursor()

        schema_info = {
            'tables': {},
            'indexes': [],
            'constraints': []
        }

        try:
            # Get table information
            cursor.execute('''
                SELECT name FROM sqlite_master
                WHERE type='table' AND name LIKE 'cost_%' OR name LIKE '%_activity_log'
                OR name LIKE 'issue_cost_%'
            ''')

            tables = [row[0] for row in cursor.fetchall()]

            for table in tables:
                cursor.execute(f'PRAGMA table_info({table})')
                columns = cursor.fetchall()
                schema_info['tables'][table] = {
                    'columns': [
                        {
                            'name': col[1],
                            'type': col[2],
                            'not_null': bool(col[3]),
                            'default': col[4],
                            'primary_key': bool(col[5])
                        }
                        for col in columns
                    ]
                }

            # Get indexes
            cursor.execute('''
                SELECT name, sql FROM sqlite_master
                WHERE type='index' AND name LIKE 'idx_%'
            ''')
            schema_info['indexes'] = [{'name': row[0], 'sql': row[1]} for row in cursor.fetchall()]

            return schema_info

        except sqlite3.Error as e:
            print(f"❌ Error getting schema info: {e}")
            return schema_info

        finally:
            conn.close()

    def drop_finance_schema(self) -> None:
        """
        Drop all finance-related tables (for testing/reset).

        WARNING: This will permanently delete all financial data!
        """
        conn = self.get_connection()
        cursor = conn.cursor()

        try:
            # Disable foreign key constraints for dropping
            cursor.execute('PRAGMA foreign_keys = OFF')

            # Drop tables in reverse dependency order
            tables_to_drop = [
                'issue_activity_log',
                'issue_cost_allocations',
                'cost_transactions',
                'cost_periods',
                'cost_items',
                'cost_categories'
            ]

            for table in tables_to_drop:
                cursor.execute(f'DROP TABLE IF EXISTS {table}')

            conn.commit()
            # Schema dropped successfully (silent for CLI compatibility)

        except sqlite3.Error as e:
            conn.rollback()
            # Re-raise without printing to avoid interfering with CLI output
            raise

        finally:
            conn.close()