import { query, queryOne } from '../../config/database.js';
import { NotFoundError, ConflictError } from '../../shared/errors/index.js';

export type AccountType = 'asset' | 'liability' | 'equity' | 'income' | 'expense';

export interface AccountTypeEntity {
  id: string;
  code: string;
  name: string;
  account_type: AccountType;
  description?: string;
}

export interface Account {
  id: string;
  tenant_id: string;
  company_id: string;
  code: string;
  name: string;
  account_type_id: string;
  account_type_name?: string;
  account_type_code?: string;
  parent_id?: string;
  parent_name?: string;
  currency_id?: string;
  currency_code?: string;
  is_reconcilable: boolean;
  is_deprecated: boolean;
  notes?: string;
  created_at: Date;
}

export interface CreateAccountDto {
  company_id: string;
  code: string;
  name: string;
  account_type_id: string;
  parent_id?: string;
  currency_id?: string;
  is_reconcilable?: boolean;
  notes?: string;
}

export interface UpdateAccountDto {
  name?: string;
  parent_id?: string | null;
  currency_id?: string | null;
  is_reconcilable?: boolean;
  is_deprecated?: boolean;
  notes?: string | null;
}

export interface AccountFilters {
  company_id?: string;
  account_type_id?: string;
  parent_id?: string;
  is_deprecated?: boolean;
  search?: string;
  page?: number;
  limit?: number;
}

class AccountsService {
  // Account Types (catalog)
  async findAllAccountTypes(): Promise<AccountTypeEntity[]> {
    return query<AccountTypeEntity>(
      `SELECT * FROM financial.account_types ORDER BY code`
    );
  }

  async findAccountTypeById(id: string): Promise<AccountTypeEntity> {
    const accountType = await queryOne<AccountTypeEntity>(
      `SELECT * FROM financial.account_types WHERE id = $1`,
      [id]
    );
    if (!accountType) {
      throw new NotFoundError('Tipo de cuenta no encontrado');
    }
    return accountType;
  }

  // Accounts
  async findAll(tenantId: string, filters: AccountFilters = {}): Promise<{ data: Account[]; total: number }> {
    const { company_id, account_type_id, parent_id, is_deprecated, search, page = 1, limit = 50 } = filters;
    const offset = (page - 1) * limit;

    let whereClause = 'WHERE a.tenant_id = $1 AND a.deleted_at IS NULL';
    const params: any[] = [tenantId];
    let paramIndex = 2;

    if (company_id) {
      whereClause += ` AND a.company_id = $${paramIndex++}`;
      params.push(company_id);
    }

    if (account_type_id) {
      whereClause += ` AND a.account_type_id = $${paramIndex++}`;
      params.push(account_type_id);
    }

    if (parent_id !== undefined) {
      if (parent_id === null || parent_id === 'null') {
        whereClause += ' AND a.parent_id IS NULL';
      } else {
        whereClause += ` AND a.parent_id = $${paramIndex++}`;
        params.push(parent_id);
      }
    }

    if (is_deprecated !== undefined) {
      whereClause += ` AND a.is_deprecated = $${paramIndex++}`;
      params.push(is_deprecated);
    }

    if (search) {
      whereClause += ` AND (a.code ILIKE $${paramIndex} OR a.name ILIKE $${paramIndex})`;
      params.push(`%${search}%`);
      paramIndex++;
    }

    const countResult = await queryOne<{ count: string }>(
      `SELECT COUNT(*) as count FROM financial.accounts a ${whereClause}`,
      params
    );

    params.push(limit, offset);
    const data = await query<Account>(
      `SELECT a.*,
              at.name as account_type_name,
              at.code as account_type_code,
              ap.name as parent_name,
              cur.code as currency_code
       FROM financial.accounts a
       LEFT JOIN financial.account_types at ON a.account_type_id = at.id
       LEFT JOIN financial.accounts ap ON a.parent_id = ap.id
       LEFT JOIN core.currencies cur ON a.currency_id = cur.id
       ${whereClause}
       ORDER BY a.code
       LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`,
      params
    );

    return {
      data,
      total: parseInt(countResult?.count || '0', 10),
    };
  }

  async findById(id: string, tenantId: string): Promise<Account> {
    const account = await queryOne<Account>(
      `SELECT a.*,
              at.name as account_type_name,
              at.code as account_type_code,
              ap.name as parent_name,
              cur.code as currency_code
       FROM financial.accounts a
       LEFT JOIN financial.account_types at ON a.account_type_id = at.id
       LEFT JOIN financial.accounts ap ON a.parent_id = ap.id
       LEFT JOIN core.currencies cur ON a.currency_id = cur.id
       WHERE a.id = $1 AND a.tenant_id = $2 AND a.deleted_at IS NULL`,
      [id, tenantId]
    );

    if (!account) {
      throw new NotFoundError('Cuenta no encontrada');
    }

    return account;
  }

  async create(dto: CreateAccountDto, tenantId: string, userId: string): Promise<Account> {
    // Validate unique code within company
    const existing = await queryOne<Account>(
      `SELECT id FROM financial.accounts WHERE company_id = $1 AND code = $2 AND deleted_at IS NULL`,
      [dto.company_id, dto.code]
    );
    if (existing) {
      throw new ConflictError(`Ya existe una cuenta con código ${dto.code}`);
    }

    // Validate account type exists
    await this.findAccountTypeById(dto.account_type_id);

    // Validate parent account if specified
    if (dto.parent_id) {
      const parent = await queryOne<Account>(
        `SELECT id FROM financial.accounts WHERE id = $1 AND company_id = $2 AND deleted_at IS NULL`,
        [dto.parent_id, dto.company_id]
      );
      if (!parent) {
        throw new NotFoundError('Cuenta padre no encontrada');
      }
    }

    const account = await queryOne<Account>(
      `INSERT INTO financial.accounts (tenant_id, company_id, code, name, account_type_id, parent_id, currency_id, is_reconcilable, notes, created_by)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
       RETURNING *`,
      [
        tenantId,
        dto.company_id,
        dto.code,
        dto.name,
        dto.account_type_id,
        dto.parent_id,
        dto.currency_id,
        dto.is_reconcilable || false,
        dto.notes,
        userId,
      ]
    );

    return account!;
  }

  async update(id: string, dto: UpdateAccountDto, tenantId: string, userId: string): Promise<Account> {
    const existing = await this.findById(id, tenantId);

    // Validate parent (prevent self-reference)
    if (dto.parent_id) {
      if (dto.parent_id === id) {
        throw new ConflictError('Una cuenta no puede ser su propia cuenta padre');
      }
      const parent = await queryOne<Account>(
        `SELECT id FROM financial.accounts WHERE id = $1 AND company_id = $2 AND deleted_at IS NULL`,
        [dto.parent_id, existing.company_id]
      );
      if (!parent) {
        throw new NotFoundError('Cuenta padre no encontrada');
      }
    }

    const updateFields: string[] = [];
    const values: any[] = [];
    let paramIndex = 1;

    if (dto.name !== undefined) {
      updateFields.push(`name = $${paramIndex++}`);
      values.push(dto.name);
    }
    if (dto.parent_id !== undefined) {
      updateFields.push(`parent_id = $${paramIndex++}`);
      values.push(dto.parent_id);
    }
    if (dto.currency_id !== undefined) {
      updateFields.push(`currency_id = $${paramIndex++}`);
      values.push(dto.currency_id);
    }
    if (dto.is_reconcilable !== undefined) {
      updateFields.push(`is_reconcilable = $${paramIndex++}`);
      values.push(dto.is_reconcilable);
    }
    if (dto.is_deprecated !== undefined) {
      updateFields.push(`is_deprecated = $${paramIndex++}`);
      values.push(dto.is_deprecated);
    }
    if (dto.notes !== undefined) {
      updateFields.push(`notes = $${paramIndex++}`);
      values.push(dto.notes);
    }

    updateFields.push(`updated_by = $${paramIndex++}`);
    values.push(userId);
    updateFields.push(`updated_at = CURRENT_TIMESTAMP`);

    values.push(id, tenantId);

    const account = await queryOne<Account>(
      `UPDATE financial.accounts
       SET ${updateFields.join(', ')}
       WHERE id = $${paramIndex++} AND tenant_id = $${paramIndex} AND deleted_at IS NULL
       RETURNING *`,
      values
    );

    return account!;
  }

  async delete(id: string, tenantId: string, userId: string): Promise<void> {
    await this.findById(id, tenantId);

    // Check if account has children
    const children = await queryOne<{ count: string }>(
      `SELECT COUNT(*) as count FROM financial.accounts WHERE parent_id = $1 AND deleted_at IS NULL`,
      [id]
    );
    if (parseInt(children?.count || '0', 10) > 0) {
      throw new ConflictError('No se puede eliminar una cuenta que tiene subcuentas');
    }

    // Check if account has journal entry lines
    const entries = await queryOne<{ count: string }>(
      `SELECT COUNT(*) as count FROM financial.journal_entry_lines WHERE account_id = $1`,
      [id]
    );
    if (parseInt(entries?.count || '0', 10) > 0) {
      throw new ConflictError('No se puede eliminar una cuenta que tiene movimientos contables');
    }

    // Soft delete
    await query(
      `UPDATE financial.accounts SET deleted_at = CURRENT_TIMESTAMP, deleted_by = $1 WHERE id = $2 AND tenant_id = $3`,
      [userId, id, tenantId]
    );
  }

  async getBalance(accountId: string, tenantId: string): Promise<{ debit: number; credit: number; balance: number }> {
    await this.findById(accountId, tenantId);

    const result = await queryOne<{ total_debit: string; total_credit: string }>(
      `SELECT COALESCE(SUM(jel.debit), 0) as total_debit,
              COALESCE(SUM(jel.credit), 0) as total_credit
       FROM financial.journal_entry_lines jel
       INNER JOIN financial.journal_entries je ON jel.entry_id = je.id
       WHERE jel.account_id = $1 AND je.status = 'posted'`,
      [accountId]
    );

    const debit = parseFloat(result?.total_debit || '0');
    const credit = parseFloat(result?.total_credit || '0');

    return {
      debit,
      credit,
      balance: debit - credit,
    };
  }
}

export const accountsService = new AccountsService();
