import { query } from '../config/database';
import { hashPassword } from '../utils/password';

export const getDocumentsByLead = async (leadId: number) => {
    const result = await query(
        `SELECT d.*, u.name as uploaded_by_name, v.name as verified_by_name
     FROM documents d
     LEFT JOIN users u ON d.uploaded_by = u.id
     LEFT JOIN users v ON d.verified_by = v.id
     WHERE d.lead_id = $1 AND d.parent_document_id IS NULL
     ORDER BY d.uploaded_at DESC`,
        [leadId]
    );

    // Get versions for each document
    for (const doc of result.rows) {
        const versions = await query(
            `SELECT * FROM documents WHERE parent_document_id = $1 ORDER BY version DESC`,
            [doc.id]
        );
        doc.versions = versions.rows;
    }

    return result.rows;
};

export const uploadDocument = async (
    leadId: number,
    documentType: string,
    documentName: string,
    filePath: string,
    fileSize: number,
    mimeType: string,
    uploadedBy: number,
    password?: string
) => {
    let passwordHash = null;
    let passwordProtected = false;

    if (password) {
        passwordHash = await hashPassword(password);
        passwordProtected = true;
    }

    const result = await query(
        `INSERT INTO documents (
      lead_id, document_type, document_name, file_path, file_size, mime_type,
      uploaded_by, password_protected, password_hash, ocr_status
    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
    RETURNING *`,
        [
            leadId,
            documentType,
            documentName,
            filePath,
            fileSize,
            mimeType,
            uploadedBy,
            passwordProtected,
            passwordHash,
            'pending',
        ]
    );

    // Log activity
    await query(
        `INSERT INTO activities (lead_id, activity_type, title, description, performed_by)
     VALUES ($1, $2, $3, $4, $5)`,
        [leadId, 'DOCUMENT_UPLOADED', 'Document Uploaded', `${documentName} uploaded`, uploadedBy]
    );

    return result.rows[0];
};

export const createDocumentVersion = async (
    parentDocumentId: number,
    filePath: string,
    fileSize: number,
    uploadedBy: number
) => {
    // Get parent document
    const parent = await query('SELECT * FROM documents WHERE id = $1', [parentDocumentId]);
    if (parent.rows.length === 0) {
        throw new Error('Parent document not found');
    }

    const parentDoc = parent.rows[0];
    const newVersion = parentDoc.version + 1;

    const result = await query(
        `INSERT INTO documents (
      lead_id, document_type, document_name, file_path, file_size, mime_type,
      uploaded_by, parent_document_id, version, password_protected, password_hash
    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
    RETURNING *`,
        [
            parentDoc.lead_id,
            parentDoc.document_type,
            parentDoc.document_name,
            filePath,
            fileSize,
            parentDoc.mime_type,
            uploadedBy,
            parentDocumentId,
            newVersion,
            parentDoc.password_protected,
            parentDoc.password_hash,
        ]
    );

    return result.rows[0];
};

export const verifyDocument = async (
    documentId: number,
    isVerified: boolean,
    verifiedBy: number
) => {
    const result = await query(
        `UPDATE documents 
     SET is_verified = $1, verified_by = $2, verified_at = CURRENT_TIMESTAMP
     WHERE id = $3
     RETURNING *`,
        [isVerified, verifiedBy, documentId]
    );

    if (result.rows.length === 0) {
        throw new Error('Document not found');
    }

    return result.rows[0];
};

// OCR processing (stub - would integrate with actual OCR service)
export const processOCR = async (documentId: number) => {
    // Update status to processing
    await query(
        `UPDATE documents SET ocr_status = 'processing' WHERE id = $1`,
        [documentId]
    );

    // Simulate OCR processing
    // In production, this would call an OCR service like Tesseract, AWS Textract, Google Vision API
    const mockOcrText = 'Sample extracted text from document...';
    const mockConfidence = 95.5;

    const result = await query(
        `UPDATE documents 
     SET ocr_text = $1, ocr_confidence = $2, ocr_status = 'completed'
     WHERE id = $3
     RETURNING *`,
        [mockOcrText, mockConfidence, documentId]
    );

    return result.rows[0];
};

// AI Document Verification (stub)
export const aiVerifyDocument = async (documentId: number) => {
    // Get document
    const doc = await query('SELECT * FROM documents WHERE id = $1', [documentId]);
    if (doc.rows.length === 0) {
        throw new Error('Document not found');
    }

    // Simulate AI verification
    // In production, this would use ML models to verify document authenticity
    const mockVerificationStatus = 'verified';
    const mockNotes = 'Document appears authentic. All fields are clearly visible and match expected format.';

    const result = await query(
        `UPDATE documents 
     SET ai_verification_status = $1, ai_verification_notes = $2
     WHERE id = $3
     RETURNING *`,
        [mockVerificationStatus, mockNotes, documentId]
    );

    return result.rows[0];
};

export const deleteDocument = async (documentId: number) => {
    const result = await query('DELETE FROM documents WHERE id = $1 RETURNING *', [documentId]);

    if (result.rows.length === 0) {
        throw new Error('Document not found');
    }

    return { message: 'Document deleted successfully' };
};
