import logging
import uuid
from contextlib import contextmanager
from typing import Any, Dict, Generator, List, Optional, Tuple

try:
    import mysql.connector  # type: ignore
except Exception:  # pragma: no cover - only hit when mysql-connector-python isn't installed
    mysql = None  # type: ignore[assignment]

try:
    from mysql.connector import Error  # type: ignore
except Exception:  # pragma: no cover - only hit when mysql-connector-python isn't installed
    Error = Exception  # type: ignore[misc,assignment]

logger = logging.getLogger(__name__)
class SchemaManager:
    def __init__(self, db_repository):
        self.db = db_repository
    def init_schema(self):
        """Veritabanı şemasını oluşturur."""
        logger.info("Veritabanı şeması kontrol ediliyor...")
        queries = [
            """CREATE TABLE IF NOT EXISTS profiles (
                id CHAR(36) PRIMARY KEY,
                name VARCHAR(100) NOT NULL UNIQUE,
                is_active TINYINT(1) DEFAULT 1,
                is_deleted TINYINT(1) DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS app_settings (
                profile_id CHAR(36),
                setting_key VARCHAR(100),
                setting_value LONGTEXT,
                setting_type VARCHAR(20),
                PRIMARY KEY (profile_id, setting_key),
                CONSTRAINT fk_settings_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS document_counters (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36) NOT NULL,
                doc_type VARCHAR(20) NOT NULL,
                last_no INT NOT NULL DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY uniq_doc_counter (profile_id, doc_type),
                CONSTRAINT fk_doc_counter_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS users (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                username VARCHAR(50) NOT NULL,
                full_name VARCHAR(100),
                password_hash VARCHAR(255),
                role VARCHAR(20) DEFAULT 'employee',
                is_active TINYINT(1) DEFAULT 1,
                is_deleted TINYINT(1) DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                CONSTRAINT fk_users_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE RESTRICT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS user_permissions (
                user_id CHAR(36),
                permission_code VARCHAR(50),
                PRIMARY KEY (user_id, permission_code),
                CONSTRAINT fk_perm_users FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS user_access_scopes (
                user_id CHAR(36),
                scope_type VARCHAR(50),
                target_id VARCHAR(100),
                CONSTRAINT fk_scope_users FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS depots (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                name VARCHAR(100),
                is_deleted TINYINT(1) DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                CONSTRAINT fk_depots_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE RESTRICT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS categories (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                name VARCHAR(100),
                CONSTRAINT fk_cats_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS persons (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                full_name VARCHAR(150),
                phone_number VARCHAR(50),
                address TEXT,
                notes TEXT,
                is_deleted TINYINT(1) DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                CONSTRAINT fk_persons_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE RESTRICT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS product_definitions (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                stock_type VARCHAR(20),
                name VARCHAR(150),
                category_id CHAR(36),
                barcode_header VARCHAR(50),
                karat_purity VARCHAR(50),
                workmanship_fee DECIMAL(19, 4) DEFAULT 0,
                workmanship_currency VARCHAR(10) DEFAULT 'TRY',
                default_purchase_price DECIMAL(19, 4) DEFAULT 0,
                default_sale_price DECIMAL(19, 4) DEFAULT 0,
                purchase_currency VARCHAR(10) DEFAULT 'TRY',
                sale_currency VARCHAR(10) DEFAULT 'TRY',
                default_weight_gr DECIMAL(19, 4) DEFAULT 0,
                is_active TINYINT(1) DEFAULT 1,
                is_deleted TINYINT(1) DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                CONSTRAINT fk_products_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE RESTRICT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS stock_items (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                depot_id CHAR(36),
                stock_type VARCHAR(20) DEFAULT 'Product',
                item_type_name VARCHAR(150),
                quantity DECIMAL(19, 4) DEFAULT 0,
                quantity_gr DECIMAL(19, 4) DEFAULT 0,
                purchase_price_base DECIMAL(19, 4) DEFAULT 0,
                unique_barcode VARCHAR(100),
                is_deleted TINYINT(1) DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                CONSTRAINT fk_stock_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE RESTRICT,
                UNIQUE KEY idx_unique_stock (profile_id, depot_id, item_type_name, purchase_price_base, unique_barcode)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS barcode_history (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                barcode VARCHAR(100),
                product_type VARCHAR(100),
                gram DECIMAL(19, 4) DEFAULT 0,
                karat VARCHAR(50),
                buy_date DATETIME,
                buy_from VARCHAR(150),
                buy_price DECIMAL(19, 4) DEFAULT 0,
                buy_method VARCHAR(50),
                is_sold TINYINT(1) DEFAULT 0,
                sell_date DATETIME,
                sell_to VARCHAR(150),
                sell_price DECIMAL(19, 4) DEFAULT 0,
                sell_method VARCHAR(50),
                movements_json LONGTEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                CONSTRAINT fk_barcode_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE RESTRICT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS stock_movements (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                date DATETIME,
                item_type VARCHAR(50),
                product_name VARCHAR(150),
                operation_type VARCHAR(50),
                quantity DECIMAL(19, 4),
                source VARCHAR(100),
                destination VARCHAR(100),
                description TEXT,
                user VARCHAR(100),
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                CONSTRAINT fk_moves_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS financial_accounts (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                name VARCHAR(100),
                type VARCHAR(20),
                is_deleted TINYINT(1) DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                CONSTRAINT fk_accounts_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE RESTRICT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS financial_transactions (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                account_id CHAR(36),
                transaction_date DATE,
                transaction_type VARCHAR(20),
                amount DECIMAL(19, 4) DEFAULT 0,
                currency VARCHAR(10),
                base_amount DECIMAL(19, 4) DEFAULT 0,
                description TEXT,
                related_person_id CHAR(36),
                related_record_id CHAR(36),
                is_deleted TINYINT(1) DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                CONSTRAINT fk_trans_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE RESTRICT,
                CONSTRAINT fk_trans_accounts FOREIGN KEY (account_id) REFERENCES financial_accounts(id) ON DELETE RESTRICT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS sales (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                sale_date DATE,
                customer_full_name VARCHAR(150),
                total_amount DECIMAL(19, 4) DEFAULT 0,
                currency VARCHAR(10),
                payment_method_summary TEXT,
                products_json LONGTEXT,
                is_deleted TINYINT(1) DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                CONSTRAINT fk_sales_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE RESTRICT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS taxes (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36) NOT NULL,
                name VARCHAR(120) NOT NULL,
                rate DECIMAL(19, 4) NOT NULL DEFAULT 0,
                type VARCHAR(10) NOT NULL, -- 'percent' | 'fixed'
                country VARCHAR(2) DEFAULT NULL,
                active TINYINT(1) DEFAULT 1,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_taxes_profile (profile_id),
                CONSTRAINT fk_taxes_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS tax_profiles (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36) NOT NULL,
                name VARCHAR(120) NOT NULL,
                description TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_tax_profiles_profile (profile_id),
                CONSTRAINT fk_tax_profiles_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS tax_profile_items (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36) NOT NULL,
                tax_profile_id CHAR(36) NOT NULL,
                tax_id CHAR(36) NOT NULL,
                priority INT NOT NULL DEFAULT 0,
                enabled_by_default TINYINT(1) DEFAULT 1,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY uniq_profile_tax (tax_profile_id, tax_id),
                INDEX idx_tax_profile_items_profile (profile_id),
                CONSTRAINT fk_tax_profile_items_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE,
                CONSTRAINT fk_tax_profile_items_profile FOREIGN KEY (tax_profile_id) REFERENCES tax_profiles(id) ON DELETE CASCADE,
                CONSTRAINT fk_tax_profile_items_tax FOREIGN KEY (tax_id) REFERENCES taxes(id) ON DELETE RESTRICT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS tax_applications (
                id CHAR(36) PRIMARY KEY,
                source_table VARCHAR(60) NOT NULL,
                source_id CHAR(36) NOT NULL,
                profile_id CHAR(36) NOT NULL,
                subtotal DECIMAL(19, 4) NOT NULL DEFAULT 0,
                tax_total DECIMAL(19, 4) NOT NULL DEFAULT 0,
                grand_total DECIMAL(19, 4) NOT NULL DEFAULT 0,
                currency VARCHAR(10) NOT NULL,
                snapshot_json LONGTEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_tax_app_source (source_table, source_id),
                INDEX idx_tax_app_profile (profile_id),
                CONSTRAINT fk_tax_app_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS debt_records (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                person_id CHAR(36),
                person_full_name VARCHAR(150),
                record_type VARCHAR(20),
                transaction_date DATE,
                debt_category VARCHAR(20),
                original_amount DECIMAL(19, 4) DEFAULT 0,
                currency VARCHAR(10),
                total_amount_base DECIMAL(19, 4) DEFAULT 0,
                product_name VARCHAR(150),
                product_quantity DECIMAL(19, 4) DEFAULT 0,
                description TEXT,
                status VARCHAR(20) DEFAULT 'Active',
                due_date DATE,
                is_paid TINYINT(1) DEFAULT 0,
                paid_amount_base DECIMAL(19, 4) DEFAULT 0,
                paid_quantity DECIMAL(19, 4) DEFAULT 0,
                payment_date DATE,
                is_deleted TINYINT(1) DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                CONSTRAINT fk_debt_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE RESTRICT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS customer_history_events (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                person_id CHAR(36),
                person_full_name VARCHAR(150),
                event_date DATETIME,
                event_type VARCHAR(100),
                description TEXT,
                debt_original DECIMAL(19, 4) DEFAULT 0,
                credit_original DECIMAL(19, 4) DEFAULT 0,
                currency VARCHAR(20),
                transaction_amount DECIMAL(19, 4) DEFAULT 0,
                related_id CHAR(36),
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                CONSTRAINT fk_hist_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE RESTRICT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS repair_records (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                customer_full_name VARCHAR(150),
                product_description TEXT,
                complaint_request TEXT,
                receipt_date DATE,
                estimated_fee DECIMAL(19, 4) DEFAULT 0,
                fee_currency VARCHAR(10),
                status VARCHAR(50),
                delivery_date DATE,
                notes TEXT,
                is_deleted TINYINT(1) DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                CONSTRAINT fk_repair_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE RESTRICT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS scheduled_transactions (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                due_date DATE,
                transaction_type VARCHAR(20),
                amount DECIMAL(19, 4) DEFAULT 0,
                currency VARCHAR(10),
                description TEXT,
                target VARCHAR(100),
                person_full_name VARCHAR(150),
                status VARCHAR(20) DEFAULT 'Pending',
                is_deleted TINYINT(1) DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                CONSTRAINT fk_st_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE RESTRICT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS service_definitions (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                name VARCHAR(100),
                custom_columns_json LONGTEXT,
                CONSTRAINT fk_servdef_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE RESTRICT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS audit_logs (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                user_name VARCHAR(100),
                action VARCHAR(50),
                target_table VARCHAR(50),
                target_id VARCHAR(100),
                details TEXT,
                ip_address VARCHAR(50),
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                CONSTRAINT fk_audit_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS service_jobs (
                id CHAR(36) PRIMARY KEY,
                profile_id CHAR(36),
                service_def_id CHAR(36),
                customer_name VARCHAR(150),
                description TEXT,
                status VARCHAR(50),
                fee DECIMAL(19, 4) DEFAULT 0,
                currency VARCHAR(10),
                start_date DATE,
                delivery_date DATE,
                dynamic_data_json LONGTEXT,
                CONSTRAINT fk_servjob_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE RESTRICT,
                CONSTRAINT fk_servjob_def FOREIGN KEY (service_def_id) REFERENCES service_definitions(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS local_sync_queue (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                transaction_uuid CHAR(36),
                table_name VARCHAR(50),
                action_type VARCHAR(20),
                record_id CHAR(36),
                payload_json JSON,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                KEY idx_local_sync_queue_tx_uuid (transaction_uuid),
                KEY idx_local_sync_queue_created_at (created_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS deleted_profiles (
                profile_id CHAR(36) PRIMARY KEY,
                profile_name VARCHAR(100) NULL,
                transaction_uuid CHAR(36) NULL,
                device_id VARCHAR(50) NULL,
                deleted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                KEY idx_deleted_profiles_deleted_at (deleted_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS sync_state (
                last_pulled_event_id BIGINT DEFAULT 0,
                device_id VARCHAR(50)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS sync_profile_state (
                profile_id CHAR(36) PRIMARY KEY,
                last_pulled_event_id BIGINT NOT NULL DEFAULT 0,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                CONSTRAINT fk_sync_profile_state_profiles FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""",
            """CREATE TABLE IF NOT EXISTS conflict_logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                message TEXT,
                is_read TINYINT(1) DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"""
        ]
        with self.db.transaction_scope() as cursor:
            try:
                cursor.execute("SET FOREIGN_KEY_CHECKS=0")
            except Exception:
                pass
            for sql in queries:
                try:
                    cursor.execute(sql)
                except Exception as e:
                    logger.warning(f"Tablo oluşturma uyarısı: {e}")
            try:
                cursor.execute(
                    """
                    CREATE TABLE IF NOT EXISTS agenda_notes (
                        profile_id CHAR(36),
                        note_key VARCHAR(100),
                        note_value LONGTEXT,
                        PRIMARY KEY (profile_id, note_key),
                        INDEX idx_profile (profile_id)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
                    """
                )
            except Exception:
                pass
            try:
                cursor.execute("SET FOREIGN_KEY_CHECKS=1")
            except Exception:
                pass
        logger.info("Şema yapılandırması tamamlandı.")
        # Legacy VAT column is removed/ignored in the new tax engine.
        # Best-effort cleanup: do not fail schema init if the column doesn't exist.
        try:
            with self.db.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute("ALTER TABLE product_definitions DROP COLUMN vat_rate")
                conn.commit()
        except Exception:
            pass
        try:
            self.db.invalidate_table_columns_cache("product_definitions")
        except Exception:
            pass
        try:
            # Remove legacy VAT-related settings (no longer used).
            self.db.execute_non_query(
                "DELETE FROM app_settings WHERE setting_key IN ('default_vat_rate', 'vat_mode')"
            )
        except Exception:
            pass
        # Backfill columns for legacy databases created by older versions.
        self._ensure_column_exists(
            "profiles",
            "updated_at",
            "DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
        )
        self._ensure_column_exists("product_definitions", "workmanship_fee", "DECIMAL(19, 4) DEFAULT 0")
        self._ensure_column_exists("product_definitions", "workmanship_currency", "VARCHAR(10) DEFAULT 'TRY'")
        self._ensure_column_exists("product_definitions", "purchase_currency", "VARCHAR(10) DEFAULT 'TRY'")
        self._ensure_column_exists("product_definitions", "sale_currency", "VARCHAR(10) DEFAULT 'TRY'")
        self._ensure_column_exists("stock_items", "stock_type", "VARCHAR(20) DEFAULT 'Product'")
        try:
            self.db.invalidate_table_columns_cache("product_definitions")
        except Exception:
            pass
        try:
            self.db.invalidate_table_columns_cache("stock_items")
        except Exception:
            pass
        try:
            with self.db.transaction_scope() as cursor:
                cursor.execute(
                    """
                    UPDATE profiles
                    SET updated_at = COALESCE(updated_at, created_at, NOW())
                    WHERE updated_at IS NULL
                    """
                )
                cursor.execute(
                    """
                    UPDATE stock_items si
                    JOIN product_definitions pd
                      ON pd.profile_id = si.profile_id
                     AND pd.name = si.item_type_name
                     AND pd.is_deleted = 0
                    SET si.stock_type = pd.stock_type
                    WHERE si.profile_id IS NOT NULL
                      AND (si.stock_type IS NULL OR si.stock_type = '' OR si.stock_type <> pd.stock_type)
                    """
                )
        except Exception:
            pass
        # Initialize single sync_state row if missing.
        try:
            state = self.db.fetch_one("SELECT device_id FROM sync_state LIMIT 1")
            if not state:
                self.db.execute_non_query(
                    "INSERT INTO sync_state (last_pulled_event_id, device_id) VALUES (%s, %s)",
                    (0, str(uuid.uuid4())),
                )
        except Exception:
            pass
    def _ensure_column_exists(self, table_name, column_name, definition):
        try:
            with self.db.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN {column_name} {definition}")
                    conn.commit()
        except Exception:
            pass
class DatabaseRepository:
    def __init__(self, db_name: str = "Goldr"):
        self.db_config = {
            "host": "127.0.0.1",
            "user": "root",
            "password": "root",  # GÜNCEL: Embedded kurulumda belirlediğimiz şifre
            "database": db_name,
            "port": 3308,        # GÜNCEL: Embedded sunucu portu
            "autocommit": False,
            "charset": "utf8mb4",
            "pool_name": "golder_pool",
            "pool_size": 10,
            "pool_reset_session": True,
            "auth_plugin": "mysql_native_password", # GÜNCEL: Plugin hatasını çözer
            "allow_local_infile": True
        }
        self._pool = None
        self._table_columns_cache: Dict[str, set] = {}
        try:
            self._initialize_pool()
            SchemaManager(self).init_schema()
        except Exception as e:
            logger.warning(f"DB Repository init uyarısı (Normal olabilir, server henüz başlamamış olabilir): {e}")
    @contextmanager
    def transaction_scope(self):
        """Transactional işlemler için context manager."""
        conn = None
        try:
            conn = self._pool.get_connection()
            if not conn.is_connected():
                logger.warning("Transaction öncesi bağlantı kopuk, yeniden bağlanılıyor...")
                try:
                    conn.reconnect(attempts=3, delay=1)
                except Exception as rec_err:
                    logger.critical(f"Yeniden bağlanma başarısız: {rec_err}")
                    raise ConnectionError("Veritabanı bağlantısı koptu ve yeniden sağlanamadı.")
            conn.start_transaction()
            cur = conn.cursor(dictionary=True, buffered=True)
            try:
                yield cur
                conn.commit()
            except Exception as e:
                try:
                    conn.rollback()
                except Exception:
                    pass
                logger.error(f"Transaction Rollback (Reason: {e})")
                raise e
            finally:
                cur.close()
        except Exception as e:
            logger.critical(f"Kritik Veritabanı Hatası: {e}")
            raise e
        finally:
            if conn:
                try:
                    conn.close()
                except Exception:
                    pass
    def _initialize_pool(self):
        try:
            tmp_config = self.db_config.copy()
            for k in ['database', 'pool_name', 'pool_size', 'pool_reset_session']:
                tmp_config.pop(k, None)
            cnx = mysql.connector.connect(**tmp_config)
            cnx.cursor().execute(f"CREATE DATABASE IF NOT EXISTS {self.db_config['database']} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
            cnx.close()
            self._pool = mysql.connector.pooling.MySQLConnectionPool(**self.db_config)
            logger.info("Veritabanı bağlantı havuzu oluşturuldu.")
        except Error as e:
            logger.critical(f"DB Start Error: {e}")
            raise
    @contextmanager
    def get_connection(self):
        conn = self._pool.get_connection()
        try:
            yield conn
        finally:
            conn.close()
    @contextmanager
    def get_cursor(self, dictionary=True):
        with self.get_connection() as conn:
            cur = conn.cursor(dictionary=dictionary, buffered=True)
            try:
                yield cur
            finally:
                cur.close()
    def execute_non_query(self, query, params=None):
        with self.transaction_scope() as cur:
            cur.execute(query, params or ())
            return cur.rowcount
    def fetch_all(self, query, params=None):
        with self.get_cursor() as cur:
            cur.execute(query, params or ())
            return cur.fetchall()
    def fetch_one(self, query, params=None):
        with self.get_cursor() as cur:
            cur.execute(query, params or ())
            return cur.fetchone()
    def invalidate_table_columns_cache(self, table_name: Optional[str] = None) -> None:
        if table_name:
            self._table_columns_cache.pop(table_name, None)
        else:
            self._table_columns_cache.clear()
    def get_table_columns(self, table_name: str) -> set:
        if not table_name:
            return set()
        cached = self._table_columns_cache.get(table_name)
        if cached is not None:
            return cached
        try:
            rows = self.fetch_all(
                """
                SELECT COLUMN_NAME
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s
                """,
                (table_name,),
            )
            cols = {r.get("COLUMN_NAME") for r in (rows or []) if r and r.get("COLUMN_NAME")}
        except Exception as e:
            logger.warning(f"Kolon listesi alınamadı ({table_name}): {e}")
            cols = set()
        self._table_columns_cache[table_name] = cols
        return cols
    def has_column(self, table_name: str, column_name: str) -> bool:
        return column_name in self.get_table_columns(table_name)
    def delete_profile(self, profile_id: str) -> int:
        """
        Profili ve profile ait TÜM verileri kalıcı olarak siler.
        Not: Şemada birçok tablo `profiles` tablosuna `ON DELETE RESTRICT` ile bağlı olduğu için,
        önce ilgili tablolardaki kayıtları temizleyip en son `profiles` kaydını sileriz.
        Dönüş:
            Silinen profil adedi (0 veya 1)
        """
        if not profile_id:
            raise ValueError("profile_id gerekli")
        with self.transaction_scope() as cur:
            cur.execute(
                """
                SELECT DISTINCT c.TABLE_NAME
                FROM INFORMATION_SCHEMA.COLUMNS c
                JOIN INFORMATION_SCHEMA.TABLES t
                  ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
                WHERE c.TABLE_SCHEMA = DATABASE()
                  AND c.COLUMN_NAME = 'profile_id'
                  AND t.TABLE_TYPE = 'BASE TABLE'
                """
            )
            rows = cur.fetchall() or []
            tables = [
                r.get("TABLE_NAME")
                for r in rows
                if isinstance(r, dict) and isinstance(r.get("TABLE_NAME"), str)
            ]
            tables_set = {t for t in tables if t and t not in {"profiles", "deleted_profiles"}}
            priority = ["financial_transactions", "financial_accounts", "service_jobs", "service_definitions"]
            ordered: List[str] = []
            for t in priority:
                if t in tables_set:
                    ordered.append(t)
                    tables_set.remove(t)
            users_present = "users" in tables_set
            if users_present:
                tables_set.remove("users")
            ordered.extend(sorted(tables_set))
            if users_present:
                ordered.append("users")
            for table_name in ordered:
                if not table_name.replace("_", "").isalnum():
                    raise ValueError(f"Beklenmeyen tablo adı: {table_name}")
                cur.execute(f"DELETE FROM `{table_name}` WHERE profile_id = %s", (profile_id,))
            cur.execute("DELETE FROM `profiles` WHERE id = %s", (profile_id,))
            return cur.rowcount
if __name__ == "__main__":
    db = DatabaseRepository()




