-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Enum types
CREATE TYPE operation_status AS ENUM (
  'consulta_recibida',
  'reserva_realizada',
  'documentacion_pendiente',
  'documentacion_completa',
  'contrato_en_confeccion',
  'enviado_propietario',
  'enviado_inquilino',
  'correcciones',
  'listo_para_firma',
  'firmado',
  'archivado'
);

CREATE TYPE guarantee_type AS ENUM (
  'propietaria',
  'seguro_caucion',
  'bancaria',
  'recibo_sueldo',
  'mixta',
  'otra'
);

CREATE TYPE adjustment_type AS ENUM (
  'IPC',
  'ICL',
  'casa_propia',
  'fijo',
  'otro'
);

CREATE TYPE adjustment_frequency AS ENUM (
  'mensual',
  'trimestral',
  'cuatrimestral',
  'semestral',
  'anual'
);

CREATE TYPE property_type AS ENUM (
  'departamento',
  'casa',
  'local',
  'oficina',
  'cochera',
  'galpon',
  'otro'
);

CREATE TYPE user_role AS ENUM ('admin', 'employee', 'readonly');

-- Profiles table (extends Supabase auth.users)
CREATE TABLE profiles (
  id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL,
  role user_role NOT NULL DEFAULT 'employee',
  avatar_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Operations table
CREATE TABLE operations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  -- Internal code
  internal_code TEXT UNIQUE NOT NULL,
  status operation_status NOT NULL DEFAULT 'consulta_recibida',
  
  -- Property
  property_address TEXT NOT NULL,
  property_locality TEXT,
  property_type property_type DEFAULT 'departamento',
  property_observations TEXT,
  
  -- Owner
  owner_name TEXT NOT NULL,
  owner_dni TEXT,
  owner_phone TEXT,
  owner_email TEXT,
  
  -- Tenant
  tenant_name TEXT NOT NULL,
  tenant_dni TEXT,
  tenant_phone TEXT,
  tenant_email TEXT,
  
  -- Guarantee
  guarantee_type guarantee_type,
  guarantee_observations TEXT,
  
  -- Contract data
  entry_date DATE,
  start_date DATE,
  end_date DATE,
  initial_value NUMERIC(12,2),
  adjustment_type adjustment_type,
  adjustment_frequency adjustment_frequency,
  
  -- Contract sending
  sent_to_owner BOOLEAN DEFAULT FALSE,
  sent_to_owner_date DATE,
  sent_to_owner_observations TEXT,
  sent_to_tenant BOOLEAN DEFAULT FALSE,
  sent_to_tenant_date DATE,
  sent_to_tenant_observations TEXT,
  
  -- Meta
  created_by UUID REFERENCES profiles(id),
  assigned_to UUID REFERENCES profiles(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Checklist items definitions
CREATE TABLE checklist_templates (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  category TEXT NOT NULL,
  label TEXT NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE
);

-- Per-operation checklist state
CREATE TABLE operation_checklist (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  operation_id UUID REFERENCES operations(id) ON DELETE CASCADE NOT NULL,
  template_id UUID REFERENCES checklist_templates(id) NOT NULL,
  is_completed BOOLEAN DEFAULT FALSE,
  completed_at TIMESTAMPTZ,
  completed_by UUID REFERENCES profiles(id),
  notes TEXT,
  UNIQUE(operation_id, template_id)
);

-- Documents
CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  operation_id UUID REFERENCES operations(id) ON DELETE CASCADE NOT NULL,
  checklist_item_id UUID REFERENCES operation_checklist(id),
  file_name TEXT NOT NULL,
  file_url TEXT NOT NULL,
  file_size INT,
  mime_type TEXT,
  uploaded_by UUID REFERENCES profiles(id),
  uploaded_at TIMESTAMPTZ DEFAULT NOW()
);

-- Activity log
CREATE TABLE activity_log (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  operation_id UUID REFERENCES operations(id) ON DELETE CASCADE NOT NULL,
  user_id UUID REFERENCES profiles(id),
  action TEXT NOT NULL,
  details JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Alerts
CREATE TABLE alerts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  operation_id UUID REFERENCES operations(id) ON DELETE CASCADE NOT NULL,
  type TEXT NOT NULL,
  message TEXT NOT NULL,
  severity TEXT NOT NULL DEFAULT 'warning',
  is_resolved BOOLEAN DEFAULT FALSE,
  resolved_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ========= SEED CHECKLIST TEMPLATES =========
INSERT INTO checklist_templates (category, label, sort_order) VALUES
-- Inquilino
('inquilino', 'DNI frente', 1),
('inquilino', 'DNI dorso', 2),
('inquilino', 'Constancia de CUIL', 3),
('inquilino', 'Recibo de sueldo', 4),
('inquilino', 'Certificación de ingresos', 5),
('inquilino', 'Referencias laborales', 6),
('inquilino', 'Referencias personales', 7),
('inquilino', 'Comprobante de domicilio', 8),

-- Garante
('garante', 'DNI frente', 1),
('garante', 'DNI dorso', 2),
('garante', 'Título de propiedad', 3),
('garante', 'Informe de dominio', 4),
('garante', 'Informe de inhibición', 5),
('garante', 'Recibo de sueldo', 6),
('garante', 'Garantía aprobada', 7),

-- Propietario
('propietario', 'DNI', 1),
('propietario', 'Escritura', 2),
('propietario', 'CBU', 3),
('propietario', 'Datos fiscales', 4),
('propietario', 'Contrato de administración', 5),

-- Comercial
('comercial', 'Reserva recibida', 1),
('comercial', 'Transferencia recibida', 2),
('comercial', 'Transferencia verificada', 3),
('comercial', 'Comisión abonada', 4),
('comercial', 'Gastos administrativos abonados', 5),
('comercial', 'Inventario realizado', 6),
('comercial', 'Estado de inmueble registrado', 7),

-- Contrato
('contrato', 'Datos cargados', 1),
('contrato', 'Contrato redactado', 2),
('contrato', 'Contrato revisado', 3),
('contrato', 'Contrato aprobado internamente', 4),
('contrato', 'Contrato enviado al propietario', 5),
('contrato', 'Correcciones realizadas (propietario)', 6),
('contrato', 'Aprobado por propietario', 7),
('contrato', 'Contrato enviado al inquilino', 8),
('contrato', 'Correcciones realizadas (inquilino)', 9),
('contrato', 'Aprobado por inquilino', 10),

-- Firma
('firma', 'Fecha asignada', 1),
('firma', 'Firma propietario', 2),
('firma', 'Firma inquilino', 3),
('firma', 'Firma garantes', 4),
('firma', 'Entrega de llaves', 5),
('firma', 'Contrato archivado', 6);

-- ========= RLS POLICIES =========
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE operations ENABLE ROW LEVEL SECURITY;
ALTER TABLE operation_checklist ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE activity_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE alerts ENABLE ROW LEVEL SECURITY;
ALTER TABLE checklist_templates ENABLE ROW LEVEL SECURITY;

-- Profiles: users can see all profiles
CREATE POLICY "profiles_select" ON profiles FOR SELECT USING (true);
CREATE POLICY "profiles_update_own" ON profiles FOR UPDATE USING (auth.uid() = id);

-- Operations: authenticated users can read; admin/employee can write
CREATE POLICY "operations_select" ON operations FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "operations_insert" ON operations FOR INSERT WITH CHECK (auth.role() = 'authenticated');
CREATE POLICY "operations_update" ON operations FOR UPDATE USING (auth.role() = 'authenticated');
CREATE POLICY "operations_delete" ON operations FOR DELETE USING (
  EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin')
);

-- Checklist
CREATE POLICY "checklist_select" ON operation_checklist FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "checklist_insert" ON operation_checklist FOR INSERT WITH CHECK (auth.role() = 'authenticated');
CREATE POLICY "checklist_update" ON operation_checklist FOR UPDATE USING (auth.role() = 'authenticated');

-- Documents
CREATE POLICY "docs_select" ON documents FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "docs_insert" ON documents FOR INSERT WITH CHECK (auth.role() = 'authenticated');
CREATE POLICY "docs_delete" ON documents FOR DELETE USING (auth.role() = 'authenticated');

-- Activity log
CREATE POLICY "activity_select" ON activity_log FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "activity_insert" ON activity_log FOR INSERT WITH CHECK (auth.role() = 'authenticated');

-- Alerts
CREATE POLICY "alerts_select" ON alerts FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "alerts_update" ON alerts FOR UPDATE USING (auth.role() = 'authenticated');

-- Checklist templates: read for all
CREATE POLICY "templates_select" ON checklist_templates FOR SELECT USING (true);

-- Functions
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO profiles (id, full_name, email, role)
  VALUES (NEW.id, COALESCE(NEW.raw_user_meta_data->>'full_name', NEW.email), NEW.email, 'employee');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION handle_new_user();

-- Auto-generate internal code
CREATE OR REPLACE FUNCTION generate_internal_code()
RETURNS TRIGGER AS $$
DECLARE
  year_part TEXT;
  seq_num INT;
BEGIN
  year_part := TO_CHAR(NOW(), 'YYYY');
  SELECT COUNT(*) + 1 INTO seq_num FROM operations WHERE internal_code LIKE year_part || '-%';
  NEW.internal_code := year_part || '-' || LPAD(seq_num::TEXT, 4, '0');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_internal_code
  BEFORE INSERT ON operations
  FOR EACH ROW
  WHEN (NEW.internal_code IS NULL OR NEW.internal_code = '')
  EXECUTE FUNCTION generate_internal_code();

-- Auto-create checklist items when operation is created
CREATE OR REPLACE FUNCTION create_operation_checklist()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO operation_checklist (operation_id, template_id)
  SELECT NEW.id, id FROM checklist_templates WHERE is_active = TRUE;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_operation_created
  AFTER INSERT ON operations
  FOR EACH ROW EXECUTE FUNCTION create_operation_checklist();

-- Updated_at trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER operations_updated_at BEFORE UPDATE ON operations
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Indexes
CREATE INDEX idx_operations_status ON operations(status);
CREATE INDEX idx_operations_created_at ON operations(created_at);
CREATE INDEX idx_checklist_operation ON operation_checklist(operation_id);
CREATE INDEX idx_docs_operation ON documents(operation_id);
CREATE INDEX idx_activity_operation ON activity_log(operation_id);