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

export type EntryStatus = 'draft' | 'posted' | 'cancelled';

export interface JournalEntryLine {
  id?: string;
  account_id: string;
  account_name?: string;
  account_code?: string;
  partner_id?: string;
  partner_name?: string;
  debit: number;
  credit: number;
  description?: string;
  ref?: string;
}

export interface JournalEntry {
  id: string;
  tenant_id: string;
  company_id: string;
  company_name?: string;
  journal_id: string;
  journal_name?: string;
  name: string;
  ref?: string;
  date: Date;
  status: EntryStatus;
  notes?: string;
  lines?: JournalEntryLine[];
  total_debit?: number;
  total_credit?: number;
  created_at: Date;
  posted_at?: Date;
}

export interface CreateJournalEntryDto {
  company_id: string;
  journal_id: string;
  name: string;
  ref?: string;
  date: string;
  notes?: string;
  lines: Omit<JournalEntryLine, 'id' | 'account_name' | 'account_code' | 'partner_name'>[];
}

export interface UpdateJournalEntryDto {
  ref?: string | null;
  date?: string;
  notes?: string | null;
  lines?: Omit<JournalEntryLine, 'id' | 'account_name' | 'account_code' | 'partner_name'>[];
}

export interface JournalEntryFilters {
  company_id?: string;
  journal_id?: string;
  status?: EntryStatus;
  date_from?: string;
  date_to?: string;
  search?: string;
  page?: number;
  limit?: number;
}

class JournalEntriesService {
  async findAll(tenantId: string, filters: JournalEntryFilters = {}): Promise<{ data: JournalEntry[]; total: number }> {
    const { company_id, journal_id, status, date_from, date_to, search, page = 1, limit = 20 } = filters;
    const offset = (page - 1) * limit;

    let whereClause = 'WHERE je.tenant_id = $1';
    const params: any[] = [tenantId];
    let paramIndex = 2;

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

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

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

    if (date_from) {
      whereClause += ` AND je.date >= $${paramIndex++}`;
      params.push(date_from);
    }

    if (date_to) {
      whereClause += ` AND je.date <= $${paramIndex++}`;
      params.push(date_to);
    }

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

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

    params.push(limit, offset);
    const data = await query<JournalEntry>(
      `SELECT je.*,
              c.name as company_name,
              j.name as journal_name,
              (SELECT COALESCE(SUM(debit), 0) FROM financial.journal_entry_lines WHERE entry_id = je.id) as total_debit,
              (SELECT COALESCE(SUM(credit), 0) FROM financial.journal_entry_lines WHERE entry_id = je.id) as total_credit
       FROM financial.journal_entries je
       LEFT JOIN auth.companies c ON je.company_id = c.id
       LEFT JOIN financial.journals j ON je.journal_id = j.id
       ${whereClause}
       ORDER BY je.date DESC, je.name DESC
       LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`,
      params
    );

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

  async findById(id: string, tenantId: string): Promise<JournalEntry> {
    const entry = await queryOne<JournalEntry>(
      `SELECT je.*,
              c.name as company_name,
              j.name as journal_name
       FROM financial.journal_entries je
       LEFT JOIN auth.companies c ON je.company_id = c.id
       LEFT JOIN financial.journals j ON je.journal_id = j.id
       WHERE je.id = $1 AND je.tenant_id = $2`,
      [id, tenantId]
    );

    if (!entry) {
      throw new NotFoundError('Póliza no encontrada');
    }

    // Get lines
    const lines = await query<JournalEntryLine>(
      `SELECT jel.*,
              a.name as account_name,
              a.code as account_code,
              p.name as partner_name
       FROM financial.journal_entry_lines jel
       LEFT JOIN financial.accounts a ON jel.account_id = a.id
       LEFT JOIN core.partners p ON jel.partner_id = p.id
       WHERE jel.entry_id = $1
       ORDER BY jel.created_at`,
      [id]
    );

    entry.lines = lines;
    entry.total_debit = lines.reduce((sum, l) => sum + Number(l.debit), 0);
    entry.total_credit = lines.reduce((sum, l) => sum + Number(l.credit), 0);

    return entry;
  }

  async create(dto: CreateJournalEntryDto, tenantId: string, userId: string): Promise<JournalEntry> {
    // Validate lines balance
    const totalDebit = dto.lines.reduce((sum, l) => sum + l.debit, 0);
    const totalCredit = dto.lines.reduce((sum, l) => sum + l.credit, 0);

    if (Math.abs(totalDebit - totalCredit) > 0.01) {
      throw new ValidationError('La póliza no está balanceada. Débitos y créditos deben ser iguales.');
    }

    if (dto.lines.length < 2) {
      throw new ValidationError('La póliza debe tener al menos 2 líneas.');
    }

    const client = await getClient();

    try {
      await client.query('BEGIN');

      // Create entry
      const entryResult = await client.query(
        `INSERT INTO financial.journal_entries (tenant_id, company_id, journal_id, name, ref, date, notes, created_by)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
         RETURNING *`,
        [tenantId, dto.company_id, dto.journal_id, dto.name, dto.ref, dto.date, dto.notes, userId]
      );
      const entry = entryResult.rows[0] as JournalEntry;

      // Create lines (include tenant_id for multi-tenant security)
      for (const line of dto.lines) {
        await client.query(
          `INSERT INTO financial.journal_entry_lines (entry_id, tenant_id, account_id, partner_id, debit, credit, description, ref)
           VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`,
          [entry.id, tenantId, line.account_id, line.partner_id, line.debit, line.credit, line.description, line.ref]
        );
      }

      await client.query('COMMIT');

      return this.findById(entry.id, tenantId);
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }

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

    if (existing.status !== 'draft') {
      throw new ConflictError('Solo se pueden modificar pólizas en estado borrador');
    }

    const client = await getClient();

    try {
      await client.query('BEGIN');

      // Update entry header
      const updateFields: string[] = [];
      const values: any[] = [];
      let paramIndex = 1;

      if (dto.ref !== undefined) {
        updateFields.push(`ref = $${paramIndex++}`);
        values.push(dto.ref);
      }
      if (dto.date !== undefined) {
        updateFields.push(`date = $${paramIndex++}`);
        values.push(dto.date);
      }
      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);

      if (updateFields.length > 2) {
        await client.query(
          `UPDATE financial.journal_entries SET ${updateFields.join(', ')} WHERE id = $${paramIndex}`,
          values
        );
      }

      // Update lines if provided
      if (dto.lines) {
        const totalDebit = dto.lines.reduce((sum, l) => sum + l.debit, 0);
        const totalCredit = dto.lines.reduce((sum, l) => sum + l.credit, 0);

        if (Math.abs(totalDebit - totalCredit) > 0.01) {
          throw new ValidationError('La póliza no está balanceada');
        }

        // Delete existing lines
        await client.query(`DELETE FROM financial.journal_entry_lines WHERE entry_id = $1`, [id]);

        // Insert new lines (include tenant_id for multi-tenant security)
        for (const line of dto.lines) {
          await client.query(
            `INSERT INTO financial.journal_entry_lines (entry_id, tenant_id, account_id, partner_id, debit, credit, description, ref)
             VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`,
            [id, tenantId, line.account_id, line.partner_id, line.debit, line.credit, line.description, line.ref]
          );
        }
      }

      await client.query('COMMIT');

      return this.findById(id, tenantId);
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }

  async post(id: string, tenantId: string, userId: string): Promise<JournalEntry> {
    const entry = await this.findById(id, tenantId);

    if (entry.status !== 'draft') {
      throw new ConflictError('Solo se pueden publicar pólizas en estado borrador');
    }

    // Validate balance
    if (Math.abs((entry.total_debit || 0) - (entry.total_credit || 0)) > 0.01) {
      throw new ValidationError('La póliza no está balanceada');
    }

    await query(
      `UPDATE financial.journal_entries
       SET status = 'posted', posted_at = CURRENT_TIMESTAMP, posted_by = $1, updated_at = CURRENT_TIMESTAMP, updated_by = $1
       WHERE id = $2 AND tenant_id = $3`,
      [userId, id, tenantId]
    );

    return this.findById(id, tenantId);
  }

  async cancel(id: string, tenantId: string, userId: string): Promise<JournalEntry> {
    const entry = await this.findById(id, tenantId);

    if (entry.status === 'cancelled') {
      throw new ConflictError('La póliza ya está cancelada');
    }

    await query(
      `UPDATE financial.journal_entries
       SET status = 'cancelled', cancelled_at = CURRENT_TIMESTAMP, cancelled_by = $1, updated_at = CURRENT_TIMESTAMP, updated_by = $1
       WHERE id = $2 AND tenant_id = $3`,
      [userId, id, tenantId]
    );

    return this.findById(id, tenantId);
  }

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

    if (entry.status !== 'draft') {
      throw new ConflictError('Solo se pueden eliminar pólizas en estado borrador');
    }

    await query(`DELETE FROM financial.journal_entries WHERE id = $1 AND tenant_id = $2`, [id, tenantId]);
  }
}

export const journalEntriesService = new JournalEntriesService();
