-- =============================================
-- Migration: Security Hardening + GDPR + Cegid Integration + Reporting + Alerts
-- Date: 2026-02-26
-- Sprints: S1 (Security/GDPR), S2 (Cegid), S3 (Sync/Reporting), S7 (Alerts)
-- =============================================

-- =============================================
-- S1.1: Enable RLS on profiles
-- =============================================
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users can view own profile" ON profiles;
CREATE POLICY "Users can view own profile" ON profiles
  FOR SELECT USING (id = auth.uid());

DROP POLICY IF EXISTS "Admins full access profiles" ON profiles;
CREATE POLICY "Admins full access profiles" ON profiles
  FOR ALL USING (
    EXISTS (SELECT 1 FROM user_roles WHERE user_id = auth.uid() AND rol = 'admin')
  );

DROP POLICY IF EXISTS "Asesores view assigned client profiles" ON profiles;
CREATE POLICY "Asesores view assigned client profiles" ON profiles
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM asesor_cliente ac
      JOIN clientes c ON c.id = ac.cliente_id
      WHERE ac.asesor_id = auth.uid() AND c.user_id = profiles.id
    )
  );

DROP POLICY IF EXISTS "Asesores view asesor profiles" ON profiles;
CREATE POLICY "Asesores view asesor profiles" ON profiles
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM user_roles WHERE user_id = auth.uid() AND rol = 'asesor')
    AND EXISTS (SELECT 1 FROM user_roles WHERE user_id = profiles.id AND rol = 'asesor')
  );

DROP POLICY IF EXISTS "Users can update own profile" ON profiles;
CREATE POLICY "Users can update own profile" ON profiles
  FOR UPDATE USING (id = auth.uid())
  WITH CHECK (id = auth.uid());

-- =============================================
-- S1.6: GDPR Compliance Tables
-- =============================================
CREATE TABLE IF NOT EXISTS gdpr_consent_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  consent_type TEXT NOT NULL CHECK (consent_type IN (
    'terms_of_service', 'data_processing', 'ai_processing',
    'email_notifications', 'analytics', 'third_party_sharing'
  )),
  granted BOOLEAN NOT NULL,
  ip_address INET,
  user_agent TEXT,
  consent_text_version TEXT NOT NULL DEFAULT '1.0',
  granted_at TIMESTAMPTZ,
  revoked_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_gdpr_consent_user ON gdpr_consent_log(user_id);

CREATE TABLE IF NOT EXISTS gdpr_data_requests (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE SET NULL,
  request_type TEXT NOT NULL CHECK (request_type IN (
    'access', 'rectification', 'erasure', 'portability', 'restriction', 'objection'
  )),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN (
    'pending', 'processing', 'completed', 'denied', 'expired'
  )),
  requested_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deadline_at TIMESTAMPTZ NOT NULL DEFAULT (NOW() + INTERVAL '30 days'),
  completed_at TIMESTAMPTZ,
  response_notes TEXT,
  processed_by UUID REFERENCES profiles(id) ON DELETE SET NULL,
  export_url TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS data_retention_policies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  table_name TEXT NOT NULL,
  data_category TEXT NOT NULL CHECK (data_category IN (
    'personal', 'financial', 'fiscal', 'operational', 'audit', 'ai_processing'
  )),
  retention_period_days INTEGER NOT NULL,
  legal_basis TEXT NOT NULL CHECK (legal_basis IN (
    'consent', 'contract', 'legal_obligation', 'legitimate_interest', 'vital_interest', 'public_interest'
  )),
  action_after_expiry TEXT NOT NULL DEFAULT 'anonymize' CHECK (action_after_expiry IN (
    'anonymize', 'delete', 'archive'
  )),
  applicable_law TEXT,
  description TEXT,
  active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE gdpr_consent_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE gdpr_data_requests ENABLE ROW LEVEL SECURITY;
ALTER TABLE data_retention_policies ENABLE ROW LEVEL SECURITY;

-- =============================================
-- S2.1: Cegid Integration Tables
-- =============================================
CREATE TABLE IF NOT EXISTS cegid_company_mapping (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  cliente_id UUID REFERENCES clientes(id) ON DELETE CASCADE,
  cegid_company_code TEXT NOT NULL,
  cegid_company_name TEXT,
  api_type TEXT NOT NULL CHECK (api_type IN ('con', 'erp', 'fac', 'nom')),
  tipo_opcion_fiscal INTEGER DEFAULT 1,
  active BOOLEAN NOT NULL DEFAULT true,
  last_sync_at TIMESTAMPTZ,
  sync_enabled BOOLEAN NOT NULL DEFAULT true,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_cegid_mapping_unique ON cegid_company_mapping(cliente_id, cegid_company_code, api_type);

CREATE TABLE IF NOT EXISTS cegid_sync_runs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sync_type VARCHAR NOT NULL,
  status VARCHAR NOT NULL DEFAULT 'running' CHECK (status IN ('running', 'completed', 'failed', 'cancelled')),
  company_mapping_id UUID REFERENCES cegid_company_mapping(id) ON DELETE SET NULL,
  started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  duration_ms INTEGER,
  records_processed INTEGER DEFAULT 0,
  records_success INTEGER DEFAULT 0,
  records_failed INTEGER DEFAULT 0,
  error_message TEXT,
  metadata JSONB,
  created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS cegid_raw_records (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sync_run_id UUID REFERENCES cegid_sync_runs(id) ON DELETE CASCADE,
  record_type VARCHAR NOT NULL,
  external_id VARCHAR,
  raw_data JSONB NOT NULL,
  processed BOOLEAN DEFAULT false,
  processed_at TIMESTAMPTZ,
  error_message TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS cegid_posting_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  documento_id UUID REFERENCES documentos(id) ON DELETE SET NULL,
  sync_run_id UUID REFERENCES cegid_sync_runs(id) ON DELETE SET NULL,
  company_code VARCHAR NOT NULL,
  invoice_number VARCHAR,
  posting_date DATE,
  total_amount NUMERIC,
  status VARCHAR NOT NULL CHECK (status IN ('pending', 'posted', 'failed', 'retrying')),
  cegid_entry_id VARCHAR,
  request_payload JSONB,
  response_payload JSONB,
  error_message TEXT,
  retry_count INTEGER DEFAULT 0,
  last_retry_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =============================================
-- S3.2: Reporting Tables
-- =============================================
CREATE TABLE IF NOT EXISTS tax_summary (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  cliente_id UUID NOT NULL REFERENCES clientes(id) ON DELETE CASCADE,
  periodo TEXT NOT NULL,
  ejercicio_fiscal TEXT,
  iva_repercutido DECIMAL(15,2) DEFAULT 0,
  iva_soportado DECIMAL(15,2) DEFAULT 0,
  iva_resultado DECIMAL(15,2) DEFAULT 0,
  base_imponible_emitidas DECIMAL(15,2) DEFAULT 0,
  base_imponible_recibidas DECIMAL(15,2) DEFAULT 0,
  retenciones_practicadas DECIMAL(15,2) DEFAULT 0,
  retenciones_soportadas DECIMAL(15,2) DEFAULT 0,
  recargo_equivalencia DECIMAL(15,2) DEFAULT 0,
  total_suplidos DECIMAL(15,2) DEFAULT 0,
  num_facturas_emitidas INTEGER DEFAULT 0,
  num_facturas_recibidas INTEGER DEFAULT 0,
  source TEXT DEFAULT 'calculated',
  last_sync_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(cliente_id, periodo)
);

CREATE TABLE IF NOT EXISTS tax_iva_detail (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tax_summary_id UUID NOT NULL REFERENCES tax_summary(id) ON DELETE CASCADE,
  tipo_factura TEXT NOT NULL CHECK (tipo_factura IN ('emitida', 'recibida')),
  porcentaje_iva DECIMAL(5,2) NOT NULL,
  base_imponible DECIMAL(15,2) NOT NULL DEFAULT 0,
  cuota_iva DECIMAL(15,2) NOT NULL DEFAULT 0,
  num_facturas INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS suplidos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  documento_id UUID REFERENCES documentos(id) ON DELETE SET NULL,
  cliente_id UUID NOT NULL REFERENCES clientes(id) ON DELETE CASCADE,
  descripcion TEXT,
  importe DECIMAL(15,2) NOT NULL,
  fecha DATE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =============================================
-- S7: Alert Rules & History
-- =============================================
CREATE TABLE IF NOT EXISTS alert_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  description TEXT,
  trigger_type TEXT NOT NULL CHECK (trigger_type IN ('invoice_overdue', 'expense_deviation', 'sync_failure', 'duplicate_invoice')),
  threshold_value NUMERIC DEFAULT 20,
  threshold_unit TEXT DEFAULT 'percent',
  severity TEXT NOT NULL DEFAULT 'warning' CHECK (severity IN ('info', 'warning', 'danger', 'critical')),
  recipients TEXT[] NOT NULL DEFAULT ARRAY['administracion@externaliza2.com'],
  enabled BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS alert_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  alert_rule_id UUID REFERENCES alert_rules(id) ON DELETE SET NULL,
  trigger_data JSONB,
  severity TEXT NOT NULL DEFAULT 'warning',
  message TEXT NOT NULL,
  recipients TEXT[],
  delivery_status TEXT NOT NULL DEFAULT 'pending' CHECK (delivery_status IN ('pending', 'sent', 'failed')),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS automation_schedule (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task_name VARCHAR NOT NULL UNIQUE,
  description TEXT,
  schedule_cron VARCHAR NOT NULL,
  enabled BOOLEAN DEFAULT true,
  last_run_at TIMESTAMPTZ,
  next_run_at TIMESTAMPTZ,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =============================================
-- RLS for all new tables
-- =============================================
ALTER TABLE cegid_company_mapping ENABLE ROW LEVEL SECURITY;
ALTER TABLE cegid_sync_runs ENABLE ROW LEVEL SECURITY;
ALTER TABLE cegid_raw_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE cegid_posting_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE tax_summary ENABLE ROW LEVEL SECURITY;
ALTER TABLE tax_iva_detail ENABLE ROW LEVEL SECURITY;
ALTER TABLE suplidos ENABLE ROW LEVEL SECURITY;
ALTER TABLE alert_rules ENABLE ROW LEVEL SECURITY;
ALTER TABLE alert_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE automation_schedule ENABLE ROW LEVEL SECURITY;
