-- =============================================
-- Migration: Invoice/Billing Module (Factura Electrónica)
-- Date: 2026-02-28
-- Description: Complete invoicing system with VeriFactu, FACe, and recurring invoices
-- =============================================

-- =============================================
-- Invoice Series Configuration
-- =============================================
CREATE TABLE IF NOT EXISTS invoice_series (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  empresa_id UUID NOT NULL REFERENCES empresas(id),
  codigo TEXT NOT NULL, -- e.g. "F", "R", "P"
  nombre TEXT NOT NULL, -- e.g. "Facturas Ordinarias"
  tipo TEXT NOT NULL CHECK (tipo IN ('ordinaria', 'simplificada', 'rectificativa', 'proforma', 'presupuesto', 'albaran', 'recurrente')),
  prefijo TEXT, -- e.g. "2026-"
  siguiente_numero INTEGER DEFAULT 1,
  formato_numero TEXT DEFAULT '{prefijo}{numero:05d}', -- pattern
  activa BOOLEAN DEFAULT true,
  verifactu_habilitado BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(empresa_id, codigo)
);

-- =============================================
-- Invoices (Emitted)
-- =============================================
CREATE TABLE IF NOT EXISTS invoices (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  empresa_id UUID NOT NULL REFERENCES empresas(id),
  serie_id UUID NOT NULL REFERENCES invoice_series(id),
  numero TEXT NOT NULL, -- generated from serie
  tipo TEXT NOT NULL CHECK (tipo IN ('ordinaria', 'simplificada', 'rectificativa', 'proforma', 'presupuesto', 'albaran')),
  estado TEXT DEFAULT 'borrador' CHECK (estado IN ('borrador', 'emitida', 'enviada', 'pagada', 'anulada', 'rectificada')),
  
  -- Client data
  cliente_nombre TEXT NOT NULL,
  cliente_nif TEXT NOT NULL,
  cliente_direccion TEXT,
  cliente_email TEXT,
  cliente_telefono TEXT,
  
  -- Dates
  fecha_emision DATE NOT NULL DEFAULT CURRENT_DATE,
  fecha_vencimiento DATE,
  fecha_operacion DATE,
  
  -- Financial
  moneda TEXT DEFAULT 'EUR',
  tipo_cambio DECIMAL(10,6) DEFAULT 1,
  base_imponible DECIMAL(15,2) NOT NULL DEFAULT 0,
  total_impuestos DECIMAL(15,2) DEFAULT 0,
  total_retenciones DECIMAL(15,2) DEFAULT 0,
  total_suplidos DECIMAL(15,2) DEFAULT 0,
  total_factura DECIMAL(15,2) NOT NULL DEFAULT 0,
  
  -- Tax operation type
  tipo_operacion TEXT DEFAULT 'nacional' CHECK (tipo_operacion IN ('nacional', 'intracomunitaria', 'exportacion', 'inversion_sujeto_pasivo')),
  clave_regimen TEXT DEFAULT 'general' CHECK (clave_regimen IN ('general', 'recargo_equivalencia', 'simplificado', 'criterio_caja')),
  
  -- Rectificativa
  factura_rectificada_id UUID REFERENCES invoices(id),
  motivo_rectificacion TEXT,
  
  -- Recurring
  es_recurrente BOOLEAN DEFAULT false,
  recurrente_config JSONB, -- { intervalo: 'mensual'|'trimestral'|'anual', proximo: date, fin?: date }
  generada_desde UUID REFERENCES invoices(id), -- parent recurrente
  
  -- VeriFactu
  verifactu_enviada BOOLEAN DEFAULT false,
  verifactu_id TEXT,
  verifactu_qr TEXT,
  verifactu_fecha_envio TIMESTAMPTZ,
  verifactu_estado TEXT CHECK (verifactu_estado IN ('pendiente', 'enviada', 'aceptada', 'rechazada')),
  
  -- FACe (public sector)
  face_enviada BOOLEAN DEFAULT false,
  face_numero_registro TEXT,
  face_estado TEXT,
  
  -- Email
  email_enviado BOOLEAN DEFAULT false,
  email_fecha_envio TIMESTAMPTZ,
  
  -- PDF
  pdf_url TEXT,
  pdf_generado BOOLEAN DEFAULT false,
  
  -- Metadata
  notas TEXT,
  notas_internas TEXT,
  created_by UUID,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- =============================================
-- Invoice Lines
-- =============================================
CREATE TABLE IF NOT EXISTS invoice_lines (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  invoice_id UUID NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
  orden INTEGER NOT NULL DEFAULT 0,
  concepto TEXT NOT NULL,
  descripcion TEXT,
  cantidad DECIMAL(15,4) NOT NULL DEFAULT 1,
  precio_unitario DECIMAL(15,4) NOT NULL,
  descuento_porcentaje DECIMAL(5,2) DEFAULT 0,
  base_linea DECIMAL(15,2) NOT NULL, -- cantidad * precio * (1-descuento)
  
  -- Tax per line
  tipo_iva TEXT DEFAULT 'IVA_GENERAL',
  porcentaje_iva DECIMAL(5,2) DEFAULT 21,
  cuota_iva DECIMAL(15,2) DEFAULT 0,
  
  -- Recargo equivalencia per line
  recargo_porcentaje DECIMAL(5,2),
  recargo_cuota DECIMAL(15,2),
  
  -- Retention per line
  retencion_porcentaje DECIMAL(5,2),
  retencion_cuota DECIMAL(15,2),
  
  -- Suplido
  es_suplido BOOLEAN DEFAULT false,
  suplido_nif_tercero TEXT,
  
  created_at TIMESTAMPTZ DEFAULT now()
);

-- =============================================
-- Invoice Templates (Visual Customization)
-- =============================================
CREATE TABLE IF NOT EXISTS invoice_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  empresa_id UUID NOT NULL REFERENCES empresas(id),
  nombre TEXT NOT NULL DEFAULT 'Default',
  logo_url TEXT,
  color_primario TEXT DEFAULT '#1a56db',
  color_secundario TEXT DEFAULT '#374151',
  pie_pagina TEXT,
  datos_bancarios TEXT,
  condiciones_pago TEXT,
  es_default BOOLEAN DEFAULT false,
  html_template TEXT, -- custom HTML template
  created_at TIMESTAMPTZ DEFAULT now()
);

-- =============================================
-- Email Templates for Invoices
-- =============================================
CREATE TABLE IF NOT EXISTS invoice_email_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  empresa_id UUID NOT NULL REFERENCES empresas(id),
  nombre TEXT NOT NULL,
  asunto TEXT NOT NULL,
  cuerpo_html TEXT NOT NULL,
  es_default BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- =============================================
-- Indexes
-- =============================================
CREATE INDEX IF NOT EXISTS idx_invoice_series_empresa ON invoice_series(empresa_id);
CREATE INDEX IF NOT EXISTS idx_invoices_empresa ON invoices(empresa_id);
CREATE INDEX IF NOT EXISTS idx_invoices_serie ON invoices(serie_id);
CREATE INDEX IF NOT EXISTS idx_invoices_estado ON invoices(estado);
CREATE INDEX IF NOT EXISTS idx_invoices_fecha ON invoices(fecha_emision);
CREATE INDEX IF NOT EXISTS idx_invoices_cliente_nif ON invoices(cliente_nif);
CREATE INDEX IF NOT EXISTS idx_invoices_recurrente ON invoices(es_recurrente);
CREATE INDEX IF NOT EXISTS idx_invoice_lines_invoice ON invoice_lines(invoice_id);
CREATE INDEX IF NOT EXISTS idx_invoice_templates_empresa ON invoice_templates(empresa_id);
CREATE INDEX IF NOT EXISTS idx_invoice_email_templates_empresa ON invoice_email_templates(empresa_id);

-- =============================================
-- Row Level Security (RLS)
-- =============================================
ALTER TABLE invoice_series ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoice_lines ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoice_templates ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoice_email_templates ENABLE ROW LEVEL SECURITY;
