-- =============================================
-- Migration: Bank Reconciliation Module
-- Date: 2026-02-28
-- Purpose: Complete bank reconciliation system with accounts, movements, rules, and sessions
-- =============================================

-- =============================================
-- Bank Accounts Table
-- =============================================
CREATE TABLE IF NOT EXISTS bank_accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  empresa_id UUID NOT NULL REFERENCES empresas(id) ON DELETE CASCADE,
  nombre TEXT NOT NULL,
  iban TEXT,
  bic_swift TEXT,
  entidad_bancaria TEXT,
  numero_cuenta TEXT,
  subcuenta_contable TEXT,
  saldo_inicial DECIMAL(15,2) DEFAULT 0,
  saldo_actual DECIMAL(15,2) DEFAULT 0,
  moneda TEXT DEFAULT 'EUR',
  activa BOOLEAN DEFAULT true,
  fecha_ultimo_sync TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_bank_accounts_empresa ON bank_accounts(empresa_id);
CREATE INDEX IF NOT EXISTS idx_bank_accounts_activa ON bank_accounts(activa);

-- =============================================
-- Bank Movements Table
-- =============================================
CREATE TABLE IF NOT EXISTS bank_movements (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  bank_account_id UUID NOT NULL REFERENCES bank_accounts(id) ON DELETE CASCADE,
  empresa_id UUID NOT NULL REFERENCES empresas(id) ON DELETE CASCADE,
  fecha_operacion DATE NOT NULL,
  fecha_valor DATE,
  concepto TEXT,
  concepto_complementario TEXT,
  importe DECIMAL(15,2) NOT NULL,
  saldo_posterior DECIMAL(15,2),
  referencia TEXT,
  tipo_movimiento TEXT CHECK (tipo_movimiento IN ('ingreso', 'pago', 'transferencia', 'comision', 'impuesto', 'otro')),
  estado_conciliacion TEXT DEFAULT 'pendiente' CHECK (estado_conciliacion IN ('pendiente', 'conciliado', 'parcial', 'descartado')),
  documento_asociado_id UUID REFERENCES documentos_financieros(id) ON DELETE SET NULL,
  asiento_id TEXT,
  regla_aplicada_id UUID,
  origen_importacion TEXT CHECK (origen_importacion IN ('norma43', 'csv', 'excel', 'api_bancaria', 'manual')),
  datos_raw JSONB,
  duplicado_de UUID REFERENCES bank_movements(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_bank_movements_account ON bank_movements(bank_account_id);
CREATE INDEX IF NOT EXISTS idx_bank_movements_fecha ON bank_movements(fecha_operacion);
CREATE INDEX IF NOT EXISTS idx_bank_movements_estado ON bank_movements(estado_conciliacion);
CREATE INDEX IF NOT EXISTS idx_bank_movements_empresa ON bank_movements(empresa_id);
CREATE INDEX IF NOT EXISTS idx_bank_movements_documento ON bank_movements(documento_asociado_id);

-- =============================================
-- Bank Rules Table
-- =============================================
CREATE TABLE IF NOT EXISTS bank_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  empresa_id UUID NOT NULL REFERENCES empresas(id) ON DELETE CASCADE,
  nombre TEXT NOT NULL,
  activa BOOLEAN DEFAULT true,
  prioridad INTEGER DEFAULT 0,
  condiciones JSONB NOT NULL,
  contrapartida TEXT NOT NULL,
  descripcion_asiento TEXT,
  tipo_movimiento TEXT,
  iva_aplicable DECIMAL(5,2),
  origen TEXT DEFAULT 'manual' CHECK (origen IN ('manual', 'ia', 'historico')),
  veces_aplicada INTEGER DEFAULT 0,
  ultima_aplicacion TIMESTAMPTZ,
  created_by UUID REFERENCES profiles(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_bank_rules_empresa ON bank_rules(empresa_id);
CREATE INDEX IF NOT EXISTS idx_bank_rules_activa ON bank_rules(activa);
CREATE INDEX IF NOT EXISTS idx_bank_rules_prioridad ON bank_rules(prioridad DESC);

-- =============================================
-- Bank Reconciliation Sessions Table
-- =============================================
CREATE TABLE IF NOT EXISTS bank_reconciliation_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  bank_account_id UUID NOT NULL REFERENCES bank_accounts(id) ON DELETE CASCADE,
  empresa_id UUID NOT NULL REFERENCES empresas(id) ON DELETE CASCADE,
  fecha_inicio DATE NOT NULL,
  fecha_fin DATE NOT NULL,
  saldo_extracto DECIMAL(15,2),
  saldo_contable DECIMAL(15,2),
  descuadre DECIMAL(15,2),
  movimientos_total INTEGER DEFAULT 0,
  movimientos_conciliados INTEGER DEFAULT 0,
  estado TEXT DEFAULT 'en_curso' CHECK (estado IN ('en_curso', 'completada', 'con_descuadre')),
  created_by UUID REFERENCES profiles(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  completed_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_bank_sessions_account ON bank_reconciliation_sessions(bank_account_id);
CREATE INDEX IF NOT EXISTS idx_bank_sessions_empresa ON bank_reconciliation_sessions(empresa_id);
CREATE INDEX IF NOT EXISTS idx_bank_sessions_estado ON bank_reconciliation_sessions(estado);
CREATE INDEX IF NOT EXISTS idx_bank_sessions_fecha ON bank_reconciliation_sessions(fecha_inicio, fecha_fin);

-- =============================================
-- Bank Import Logs Table
-- =============================================
CREATE TABLE IF NOT EXISTS bank_import_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  bank_account_id UUID NOT NULL REFERENCES bank_accounts(id) ON DELETE CASCADE,
  tipo_importacion TEXT NOT NULL,
  archivo_nombre TEXT,
  movimientos_importados INTEGER DEFAULT 0,
  movimientos_duplicados INTEGER DEFAULT 0,
  errores JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_bank_import_logs_account ON bank_import_logs(bank_account_id);
CREATE INDEX IF NOT EXISTS idx_bank_import_logs_fecha ON bank_import_logs(created_at);

-- =============================================
-- RLS Policies
-- =============================================
ALTER TABLE bank_accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE bank_movements ENABLE ROW LEVEL SECURITY;
ALTER TABLE bank_rules ENABLE ROW LEVEL SECURITY;
ALTER TABLE bank_reconciliation_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE bank_import_logs ENABLE ROW LEVEL SECURITY;

-- RLS Policy: Users can view bank accounts for their empresa
DROP POLICY IF EXISTS "Users view bank accounts for their empresa" ON bank_accounts;
CREATE POLICY "Users view bank accounts for their empresa" ON bank_accounts
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM clientes c
      WHERE c.empresa_id = bank_accounts.empresa_id
      AND c.user_id = auth.uid()
    )
    OR EXISTS (
      SELECT 1 FROM user_roles
      WHERE user_id = auth.uid() AND rol = 'admin'
    )
  );

-- RLS Policy: Users can view bank movements for their empresa
DROP POLICY IF EXISTS "Users view bank movements for their empresa" ON bank_movements;
CREATE POLICY "Users view bank movements for their empresa" ON bank_movements
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM clientes c
      WHERE c.empresa_id = bank_movements.empresa_id
      AND c.user_id = auth.uid()
    )
    OR EXISTS (
      SELECT 1 FROM user_roles
      WHERE user_id = auth.uid() AND rol = 'admin'
    )
  );

-- RLS Policy: Users can view bank rules for their empresa
DROP POLICY IF EXISTS "Users view bank rules for their empresa" ON bank_rules;
CREATE POLICY "Users view bank rules for their empresa" ON bank_rules
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM clientes c
      WHERE c.empresa_id = bank_rules.empresa_id
      AND c.user_id = auth.uid()
    )
    OR EXISTS (
      SELECT 1 FROM user_roles
      WHERE user_id = auth.uid() AND rol = 'admin'
    )
  );

-- RLS Policy: Users can view bank reconciliation sessions for their empresa
DROP POLICY IF EXISTS "Users view bank sessions for their empresa" ON bank_reconciliation_sessions;
CREATE POLICY "Users view bank sessions for their empresa" ON bank_reconciliation_sessions
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM clientes c
      WHERE c.empresa_id = bank_reconciliation_sessions.empresa_id
      AND c.user_id = auth.uid()
    )
    OR EXISTS (
      SELECT 1 FROM user_roles
      WHERE user_id = auth.uid() AND rol = 'admin'
    )
  );

-- RLS Policy: Users can view bank import logs for their empresa
DROP POLICY IF EXISTS "Users view bank import logs for their empresa" ON bank_import_logs;
CREATE POLICY "Users view bank import logs for their empresa" ON bank_import_logs
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM bank_accounts ba
      JOIN clientes c ON c.empresa_id = ba.empresa_id
      WHERE ba.id = bank_import_logs.bank_account_id
      AND c.user_id = auth.uid()
    )
    OR EXISTS (
      SELECT 1 FROM user_roles
      WHERE user_id = auth.uid() AND rol = 'admin'
    )
  );
