73 lines
2.5 KiB
SQL
73 lines
2.5 KiB
SQL
-- 1. Usuarios y Perfiles
|
|
CREATE TABLE users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nombre VARCHAR(100) NOT NULL,
|
|
email VARCHAR(150) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role ENUM('propietario', 'inquilino', 'profesional') NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE tenant_profiles (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
ingresos_mensuales DECIMAL(10,2) NOT NULL,
|
|
tipo_contrato ENUM('indefinido', 'temporal', 'autonomo') NOT NULL,
|
|
tiene_mascotas BOOLEAN DEFAULT FALSE,
|
|
num_convivientes INT DEFAULT 1,
|
|
score_calculado INT DEFAULT 0,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- 2. Inmuebles y Contratos
|
|
CREATE TABLE properties (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
owner_id INT NOT NULL,
|
|
direccion VARCHAR(255) NOT NULL,
|
|
ref_catastral VARCHAR(20) UNIQUE,
|
|
cups_luz VARCHAR(22),
|
|
cups_gas VARCHAR(22),
|
|
ibi_anual DECIMAL(10,2) DEFAULT 0.00,
|
|
comunidad_mes DECIMAL(10,2) DEFAULT 0.00,
|
|
FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE leases (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
property_id INT NOT NULL,
|
|
tenant_id INT NOT NULL,
|
|
precio_alquiler DECIMAL(10,2) NOT NULL,
|
|
dia_cobro TINYINT DEFAULT 1,
|
|
fecha_inicio DATE NOT NULL,
|
|
fecha_fin DATE NOT NULL,
|
|
estado ENUM('borrador', 'firmado', 'activo', 'finalizado') DEFAULT 'borrador',
|
|
FOREIGN KEY (property_id) REFERENCES properties(id),
|
|
FOREIGN KEY (tenant_id) REFERENCES users(id)
|
|
);
|
|
|
|
-- 3. Transacciones y Finanzas
|
|
CREATE TABLE transactions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
lease_id INT,
|
|
property_id INT NOT NULL,
|
|
tipo ENUM('alquiler', 'servicio_reparacion', 'gasto_deducible') NOT NULL,
|
|
concepto VARCHAR(255) NOT NULL,
|
|
monto_total DECIMAL(10,2) NOT NULL,
|
|
comision_plataforma DECIMAL(10,2) DEFAULT 0.00,
|
|
stripe_payment_id VARCHAR(100),
|
|
estado_pago ENUM('pendiente', 'completado', 'fallido') DEFAULT 'pendiente',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (property_id) REFERENCES properties(id)
|
|
);
|
|
|
|
-- 4. Marketplace de Servicios
|
|
CREATE TABLE service_requests (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
property_id INT NOT NULL,
|
|
professional_id INT,
|
|
descripcion TEXT NOT NULL,
|
|
monto_presupuesto DECIMAL(10,2),
|
|
estado ENUM('solicitado', 'aceptado', 'pagado', 'completado') DEFAULT 'solicitado',
|
|
FOREIGN KEY (property_id) REFERENCES properties(id),
|
|
FOREIGN KEY (professional_id) REFERENCES users(id)
|
|
); |