Ejecuta este SQL en SQL Editor → New Query → Run:
-- Workspaces (negocios)
CREATE TABLE workspaces (
id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
nombre text NOT NULL,
owner_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
invite_code text UNIQUE DEFAULT upper(substr(replace(gen_random_uuid()::text,'-',''),1,8)),
empresa text DEFAULT 'MC Conecta Living',
direccion text,
clabe text,
cuenta_scotiabank text,
tarjeta_oxxo text,
dias_tolerancia integer DEFAULT 3,
nombre_cobrador text,
created_at timestamptz DEFAULT now()
);
CREATE TABLE memberships (
id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
workspace_id uuid REFERENCES workspaces(id) ON DELETE CASCADE,
user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
role text DEFAULT 'member',
email text,
UNIQUE(workspace_id, user_id)
);
CREATE TABLE inmuebles (
id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
workspace_id uuid REFERENCES workspaces(id) ON DELETE CASCADE,
num text NOT NULL,
piso text,
inquilino text NOT NULL,
tel text,
email text,
renta numeric DEFAULT 0,
dia integer DEFAULT 1,
personas integer DEFAULT 1,
notas text,
fecha_inicio_contrato date,
fecha_termino_contrato date,
fecha_salida date,
created_at timestamptz DEFAULT now()
);
-- Contratos: cada inquilino de un espacio (historial). El inmueble espeja al vigente.
CREATE TABLE contratos (
id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
workspace_id uuid REFERENCES workspaces(id) ON DELETE CASCADE,
inmueble_id uuid REFERENCES inmuebles(id) ON DELETE CASCADE,
inquilino text NOT NULL,
tel text,
email text,
renta numeric DEFAULT 0,
dia integer DEFAULT 1,
personas integer DEFAULT 1,
fecha_inicio_contrato date,
fecha_termino_contrato date,
fecha_salida date,
notas text,
created_at timestamptz DEFAULT now()
);
CREATE TABLE pagos (
id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
workspace_id uuid REFERENCES workspaces(id) ON DELETE CASCADE,
inmueble_id uuid REFERENCES inmuebles(id) ON DELETE CASCADE,
contrato_id uuid REFERENCES contratos(id) ON DELETE SET NULL,
concepto text DEFAULT 'renta',
monto numeric DEFAULT 0,
pagado numeric DEFAULT 0,
vencimiento date NOT NULL,
fecha_pago date,
notas text,
origen text DEFAULT 'manual',
servicio_id uuid,
omitido boolean DEFAULT false,
created_at timestamptz DEFAULT now()
);
CREATE TABLE servicios (
id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
workspace_id uuid REFERENCES workspaces(id) ON DELETE CASCADE,
tipo text NOT NULL,
total numeric NOT NULL,
periodo_ini date NOT NULL,
periodo_fin date NOT NULL,
dias_servicio integer,
mes integer,
anio integer,
subsidio_modo text DEFAULT 'adeudo',
notas text,
created_at timestamptz DEFAULT now()
);
CREATE TABLE servicio_detalles (
id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
servicio_id uuid REFERENCES servicios(id) ON DELETE CASCADE,
inmueble_id uuid REFERENCES inmuebles(id) ON DELETE CASCADE,
workspace_id uuid REFERENCES workspaces(id) ON DELETE CASCADE,
dias_uso integer DEFAULT 0,
personas integer DEFAULT 1,
ponderacion numeric DEFAULT 0,
porcentaje numeric DEFAULT 0,
monto numeric DEFAULT 0,
es_administracion boolean DEFAULT false
);
ALTER TABLE workspaces ENABLE ROW LEVEL SECURITY;
ALTER TABLE memberships ENABLE ROW LEVEL SECURITY;
ALTER TABLE inmuebles ENABLE ROW LEVEL SECURITY;
ALTER TABLE contratos ENABLE ROW LEVEL SECURITY;
ALTER TABLE pagos ENABLE ROW LEVEL SECURITY;
ALTER TABLE servicios ENABLE ROW LEVEL SECURITY;
ALTER TABLE servicio_detalles ENABLE ROW LEVEL SECURITY;
CREATE POLICY "ws" ON workspaces FOR ALL USING (id IN (SELECT workspace_id FROM memberships WHERE user_id=auth.uid()));
CREATE POLICY "mem" ON memberships FOR ALL USING (workspace_id IN (SELECT workspace_id FROM memberships WHERE user_id=auth.uid()));
CREATE POLICY "inm" ON inmuebles FOR ALL USING (workspace_id IN (SELECT workspace_id FROM memberships WHERE user_id=auth.uid()));
CREATE POLICY "con" ON contratos FOR ALL USING (workspace_id IN (SELECT workspace_id FROM memberships WHERE user_id=auth.uid()));
CREATE POLICY "pag" ON pagos FOR ALL USING (workspace_id IN (SELECT workspace_id FROM memberships WHERE user_id=auth.uid()));
CREATE POLICY "svc" ON servicios FOR ALL USING (workspace_id IN (SELECT workspace_id FROM memberships WHERE user_id=auth.uid()));
CREATE POLICY "svcd" ON servicio_detalles FOR ALL USING (workspace_id IN (SELECT workspace_id FROM memberships WHERE user_id=auth.uid()));
-- NOTA: este script es el de instalación desde cero y NO refleja el esquema
-- actual en producción (faltan clientes, contratos, gastos, y las políticas
-- reales usan es_miembro_del_workspace). Para la colaboración por código, corre
-- además migracion_colaboradores.sql.
