-- Active: 1754444322322@@dev.io@3306@nucleo_base #REMS - Real Estate Management System DROP TABLE IF EXISTS mod_rems_lots; CREATE TABLE `mod_rems_lots` ( `mod_rems_lots_id` int NOT NULL AUTO_INCREMENT, `mod_rems_lots_block` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Ej: 'MZA-05' `mod_rems_lots_number` int NOT NULL, `mod_rems_lots_phase_id` int DEFAULT NULL, `mod_rems_lots_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Ej: 'Lote 1' `mod_rems_lots_area_m2` decimal(10,2) NOT NULL, `mod_rems_lots_available` int DEFAULT 1, -- 1 disponible, 2 reservado, 3 en negociacion, 4 promesa de compra, 5 vendido, 6 en construccion, 7 entregado, 8 no disponible, 9 rechazado caido, 10 disponible al contado, 11 especiales `mod_rems_lots_status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'disponible' COMMENT 'Estado comercial del lote: disponible, reservado, en_negociacion, promesa_compra, vendido, en_construccion, entregado, no_disponible, rechazado_caido, disponible_al_contado, especiales', `mod_rems_lots_project_id` int DEFAULT NULL, -- Proyecto al que pertenece `mod_rems_lots_coordinates` varchar(455) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Coordenadas poligonales `mod_rems_lots_details` varchar(455) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Detalles del lote `mod_rems_lots_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `mod_rems_lots_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `mod_rems_lots_state` int DEFAULT 1, -- 1: activo, 0: inactivo (borrado lógico) PRIMARY KEY (`mod_rems_lots_id`) USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; #crea un tabla para crear fases DROP TABLE IF EXISTS mod_rems_phases; CREATE TABLE `mod_rems_phases` ( `mod_rems_phases_id` int NOT NULL AUTO_INCREMENT, `mod_rems_phases_project_id` int NOT NULL, -- Proyecto al que pertenece la fase `mod_rems_phases_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Nombre de la fase. Ej: 'Fase 1', 'Etapa A' `mod_rems_phases_type` enum('subdivision','urbanization','construction','delivery') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Tipo de fase: loteo, urbanización, construcción, entrega `mod_rems_phases_class` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, -- Clase CSS para asignar color. Ej: 'phase-blue', 'phase-green' `mod_rems_phases_color` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, -- Color CSS como variable. Ej: '--color-blue', '#3498db' `mod_rems_phases_start_date` date DEFAULT NULL, -- Fecha de inicio de la fase `mod_rems_phases_end_date` date DEFAULT NULL, -- Fecha de finalización de la fase `mod_rems_phases_status` enum('not_started','in_progress','completed','delayed') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'not_started', -- Estado: no iniciada, en progreso, completada, retrasada `mod_rems_phases_budget` decimal(15,2) DEFAULT '0.00', -- Presupuesto asignado a la fase `mod_rems_phases_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Notas adicionales sobre la fase `mod_rems_phases_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `mod_rems_phases_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `mod_rems_phases_state` int DEFAULT '1', -- 1: activo, 0: inactivo (borrado lógico) PRIMARY KEY (`mod_rems_phases_id`) USING BTREE, KEY `idx_mod_rems_phases_project` (`mod_rems_phases_project_id`), KEY `idx_mod_rems_phases_dates` (`mod_rems_phases_start_date`,`mod_rems_phases_end_date`), FULLTEXT KEY `ft_mod_rems_phases_search` (`mod_rems_phases_name`,`mod_rems_phases_notes`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; DROP TABLE IF EXISTS mod_rems_projects; CREATE TABLE `mod_rems_projects` ( `mod_rems_projects_id` int NOT NULL AUTO_INCREMENT, -- Unique identifier for the project `mod_rems_projects_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Name of the project `mod_rems_projects_location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Location of the project `mod_rems_projects_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Detailed description `mod_rems_projects_start_date` date DEFAULT NULL, -- Project start date `mod_rems_projects_end_date` date DEFAULT NULL, -- Project end date `mod_rems_projects_status` enum('planning','in_progress','on_hold','completed','cancelled') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'planning', -- Current status `mod_rems_projects_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, -- Record creation timestamp `mod_rems_projects_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Last update timestamp `mod_rems_projects_state` int DEFAULT '1', -- 1: active, 0: inactive PRIMARY KEY (`mod_rems_projects_id`) USING BTREE, KEY `idx_mod_rems_projects_status` (`mod_rems_projects_status`), KEY `idx_mod_rems_projects_dates` (`mod_rems_projects_start_date`,`mod_rems_projects_end_date`), FULLTEXT KEY `ft_mod_rems_projects_search` (`mod_rems_projects_name`,`mod_rems_projects_location`,`mod_rems_projects_description`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; DROP TABLE IF EXISTS mod_rems_phases; CREATE TABLE `mod_rems_phases` ( `mod_rems_phases_id` int NOT NULL AUTO_INCREMENT, -- Unique identifier for the phase `mod_rems_phases_project_id` int NOT NULL, -- Reference to parent project `mod_rems_phases_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Name of the phase `mod_rems_phases_type` enum('loteo','urbanizacion','construccion','entrega') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Type of phase `mod_rems_phases_start_date` date DEFAULT NULL, -- Phase start date `mod_rems_phases_end_date` date DEFAULT NULL, -- Phase end date `mod_rems_phases_status` enum('not_started','in_progress','completed','delayed') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'not_started', -- Current status `mod_rems_phases_budget` decimal(15,2) DEFAULT '0.00', -- Budget allocated for the phase `mod_rems_phases_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Additional notes `mod_rems_phases_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, -- Record creation timestamp `mod_rems_phases_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Last update timestamp `mod_rems_phases_state` int DEFAULT '1', -- 1: active, 0: inactive PRIMARY KEY (`mod_rems_phases_id`) USING BTREE, KEY `idx_mod_rems_phases_project` (`mod_rems_phases_project_id`), KEY `idx_mod_rems_phases_dates` (`mod_rems_phases_start_date`,`mod_rems_phases_end_date`), FULLTEXT KEY `ft_mod_rems_phases_search` (`mod_rems_phases_name`,`mod_rems_phases_notes`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; DROP TABLE IF EXISTS mod_rems_contacts; CREATE TABLE `mod_rems_contacts` ( `mod_rems_contacts_id` int NOT NULL AUTO_INCREMENT, -- Unique identifier for the contact `mod_rems_contacts_first_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Contact's first name `mod_rems_contacts_last_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Contact's last name `mod_rems_contacts_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Email address `mod_rems_contacts_phone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Primary phone number `mod_rems_contacts_address` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Full address `mod_rems_contacts_type` enum('lead','client','contractor','other') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Type of contact `mod_rems_contacts_source` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Source of contact (how they were acquired) `mod_rems_contacts_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Additional notes `mod_rems_contacts_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, -- Record creation timestamp `mod_rems_contacts_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Last update timestamp `mod_rems_contacts_state` int DEFAULT '1', -- 1: active, 0: inactive PRIMARY KEY (`mod_rems_contacts_id`) USING BTREE, UNIQUE KEY `unique_mod_rems_contacts_email` (`mod_rems_contacts_email`), KEY `idx_mod_rems_contacts_type` (`mod_rems_contacts_type`), KEY `idx_mod_rems_contacts_name` (`mod_rems_contacts_last_name`,`mod_rems_contacts_first_name`), KEY `idx_mod_rems_contacts_phone` (`mod_rems_contacts_phone`), FULLTEXT KEY `ft_mod_rems_contacts_search` (`mod_rems_contacts_first_name`,`mod_rems_contacts_last_name`,`mod_rems_contacts_email`,`mod_rems_contacts_phone`,`mod_rems_contacts_address`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- ============================================= -- MARKETING MODULE -- ============================================= DROP TABLE IF EXISTS mod_rems_campaigns; CREATE TABLE `mod_rems_campaigns` ( `mod_rems_campaigns_id` int NOT NULL AUTO_INCREMENT, -- Unique identifier for the campaign `mod_rems_campaigns_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Name of the marketing campaign `mod_rems_campaigns_type` enum('online','offline','social_media','email','other') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Type of campaign `mod_rems_campaigns_start_date` date DEFAULT NULL, -- Campaign start date `mod_rems_campaigns_end_date` date DEFAULT NULL, -- Campaign end date `mod_rems_campaigns_budget` decimal(15,2) DEFAULT '0.00', -- Total budget allocated `mod_rems_campaigns_status` enum('draft','active','paused','completed','cancelled') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'draft', -- Current status `mod_rems_campaigns_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Campaign description `mod_rems_campaigns_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, -- Record creation timestamp `mod_rems_campaigns_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Last update timestamp `mod_rems_campaigns_state` int DEFAULT '1', -- 1: active, 0: inactive PRIMARY KEY (`mod_rems_campaigns_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; DROP TABLE IF EXISTS mod_rems_leads; CREATE TABLE `mod_rems_leads` ( `mod_rems_leads_id` int NOT NULL AUTO_INCREMENT, -- Unique identifier for the lead `mod_rems_leads_contact_id` int NOT NULL, -- Reference to contact `mod_rems_leads_campaign_id` int DEFAULT NULL, -- Reference to marketing campaign `mod_rems_leads_status` enum('new','contacted','qualified','disqualified','converted') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'new', -- Lead status `mod_rems_leads_source` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Source of the lead `mod_rems_leads_interest_level` enum('hot','warm','cold') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'warm', -- Interest level `mod_rems_leads_budget_min` decimal(15,2) DEFAULT NULL, -- Minimum budget `mod_rems_leads_budget_max` decimal(15,2) DEFAULT NULL, -- Maximum budget `mod_rems_leads_project_id` int DEFAULT NULL, -- Reference to preferred project `mod_rems_leads_unit_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Preferred unit type `mod_rems_leads_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Additional notes `mod_rems_leads_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, -- Record creation timestamp `mod_rems_leads_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Last update timestamp `mod_rems_leads_state` int DEFAULT '1', -- 1: active, 0: inactive PRIMARY KEY (`mod_rems_leads_id`) USING BTREE, KEY `idx_mod_rems_leads_contact` (`mod_rems_leads_contact_id`), KEY `idx_mod_rems_leads_campaign` (`mod_rems_leads_campaign_id`), KEY `idx_mod_rems_leads_project` (`mod_rems_leads_project_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- ============================================= -- SALES MODULE -- ============================================= -- Units (Extending the existing mod_rems_lots concept) DROP TABLE IF EXISTS mod_rems_units; CREATE TABLE `mod_rems_units` ( `mod_rems_units_id` int NOT NULL AUTO_INCREMENT, -- Unique identifier for the unit `mod_rems_units_project_id` int NOT NULL, -- Reference to parent project `mod_rems_units_phase_id` int DEFAULT NULL, -- Reference to project phase `mod_rems_units_type` enum('lot','apartment','house','commercial') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Type of unit `mod_rems_units_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Unit number/identifier `mod_rems_units_area_m2` decimal(10,2) NOT NULL, -- Area in square meters `mod_rems_units_price` decimal(15,2) NOT NULL, -- Price of the unit `mod_rems_units_status` enum('available','reserved','sold','not_for_sale') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'available', -- Current status `mod_rems_units_features` json DEFAULT NULL, -- JSON object containing unit features `mod_rems_units_coordinates` json DEFAULT NULL, -- Geographic coordinates `mod_rems_units_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, -- Record creation timestamp `mod_rems_units_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Last update timestamp `mod_rems_units_state` int DEFAULT '1', -- 1: active, 0: inactive PRIMARY KEY (`mod_rems_units_id`) USING BTREE, KEY `idx_mod_rems_units_project` (`mod_rems_units_project_id`), KEY `idx_mod_rems_units_phase` (`mod_rems_units_phase_id`), KEY `idx_mod_rems_units_status` (`mod_rems_units_status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- Reservations DROP TABLE IF EXISTS mod_rems_reservations; CREATE TABLE `mod_rems_reservations` ( `mod_rems_reservations_id` int NOT NULL AUTO_INCREMENT, -- Unique identifier for the reservation `mod_rems_reservations_unit_id` int NOT NULL, -- Reference to the reserved unit `mod_rems_reservations_contact_id` int NOT NULL, -- Reference to the contact making the reservation `mod_rems_reservations_date` date NOT NULL, -- Date when reservation was made `mod_rems_reservations_expiration_date` date NOT NULL, -- Date when reservation expires `mod_rems_reservations_amount` decimal(15,2) NOT NULL, -- Reservation amount `mod_rems_reservations_status` enum('active','expired','converted','cancelled') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'active', -- Current status `mod_rems_reservations_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Additional notes `mod_rems_reservations_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, -- Record creation timestamp `mod_rems_reservations_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Last update timestamp `mod_rems_reservations_state` int DEFAULT '1', -- 1: active, 0: inactive PRIMARY KEY (`mod_rems_reservations_id`) USING BTREE, KEY `idx_mod_rems_reservations_unit` (`mod_rems_reservations_unit_id`), KEY `idx_mod_rems_reservations_contact` (`mod_rems_reservations_contact_id`), KEY `idx_mod_rems_reservations_dates` (`mod_rems_reservations_date`,`mod_rems_reservations_expiration_date`), KEY `idx_mod_rems_reservations_status` (`mod_rems_reservations_status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- Sales DROP TABLE IF EXISTS mod_rems_sales; CREATE TABLE `mod_rems_sales` ( `mod_rems_sales_id` int NOT NULL AUTO_INCREMENT, -- Unique identifier for the sale `mod_rems_sales_unit_id` int NOT NULL, -- Reference to the sold unit `mod_rems_sales_contact_id` int NOT NULL, -- Reference to the buyer `mod_rems_sales_reservation_id` int DEFAULT NULL, -- Reference to the original reservation (if any) `mod_rems_sales_date` date NOT NULL, -- Date of sale `mod_rems_sales_price` decimal(15,2) NOT NULL, -- Final sale price `mod_rems_sales_status` enum('pending','completed','cancelled','refunded') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'pending', -- Sale status `mod_rems_sales_payment_plan` json DEFAULT NULL, -- JSON with payment plan details `mod_rems_sales_commission_rate` decimal(5,2) DEFAULT NULL, -- Commission rate for the agent `mod_rems_sales_commission_amount` decimal(15,2) DEFAULT NULL, -- Calculated commission amount `mod_rems_sales_agent_id` int DEFAULT NULL, -- Reference to the sales agent `mod_rems_sales_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Additional notes `mod_rems_sales_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, -- Record creation timestamp `mod_rems_sales_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Last update timestamp `mod_rems_sales_state` int DEFAULT '1', -- 1: active, 0: inactive PRIMARY KEY (`mod_rems_sales_id`) USING BTREE, KEY `idx_mod_rems_sales_unit` (`mod_rems_sales_unit_id`), KEY `idx_mod_rems_sales_contact` (`mod_rems_sales_contact_id`), KEY `idx_mod_rems_sales_reservation` (`mod_rems_sales_reservation_id`), KEY `idx_mod_rems_sales_date` (`mod_rems_sales_date`), KEY `idx_mod_rems_sales_status` (`mod_rems_sales_status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- ============================================= -- LEGAL MODULE -- ============================================= -- Legal Documents DROP TABLE IF EXISTS mod_rems_legal_documents; CREATE TABLE `mod_rems_legal_documents` ( `mod_rems_legal_documents_id` int NOT NULL AUTO_INCREMENT, -- Unique identifier for the document `mod_rems_legal_documents_type` enum('contract','addendum','disclosure','other') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Type of legal document `mod_rems_legal_documents_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Document title `mod_rems_legal_documents_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Document description `mod_rems_legal_documents_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Full document content `mod_rems_legal_documents_status` enum('draft','active','archived') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'draft', -- Document status `mod_rems_legal_documents_effective_date` date DEFAULT NULL, -- Date when document becomes effective `mod_rems_legal_documents_expiration_date` date DEFAULT NULL, -- Date when document expires `mod_rems_legal_documents_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, -- Record creation timestamp `mod_rems_legal_documents_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Last update timestamp `mod_rems_legal_documents_state` int DEFAULT '1', -- 1: active, 0: inactive PRIMARY KEY (`mod_rems_legal_documents_id`) USING BTREE, KEY `idx_mod_rems_legal_documents_type` (`mod_rems_legal_documents_type`), KEY `idx_mod_rems_legal_documents_dates` (`mod_rems_legal_documents_effective_date`,`mod_rems_legal_documents_expiration_date`), FULLTEXT KEY `ft_mod_rems_legal_documents_search` (`mod_rems_legal_documents_title`,`mod_rems_legal_documents_description`,`mod_rems_legal_documents_content`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; DROP TABLE IF EXISTS mod_rems_property_transfers; CREATE TABLE `mod_rems_property_transfers` ( `mod_rems_property_transfers_id` int NOT NULL AUTO_INCREMENT, -- Unique identifier for the property transfer `mod_rems_property_transfers_sale_id` int NOT NULL, -- Reference to the sale `mod_rems_property_transfers_date` date NOT NULL, -- Date of the transfer `mod_rems_property_transfers_status` enum('pending','in_progress','completed','cancelled') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'pending', -- Transfer status `mod_rems_property_transfers_notary_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, -- Name of the notary `mod_rems_property_transfers_notary_contact` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, -- Contact information of the notary `mod_rems_property_transfers_registration_number` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, -- Property registration number `mod_rems_property_transfers_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Additional notes `mod_rems_property_transfers_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, -- Record creation timestamp `mod_rems_property_transfers_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Last update timestamp `mod_rems_property_transfers_state` int DEFAULT '1', -- 1: active, 0: inactive PRIMARY KEY (`mod_rems_property_transfers_id`) USING BTREE, KEY `idx_mod_rems_property_transfers_sale` (`mod_rems_property_transfers_sale_id`), KEY `idx_mod_rems_property_transfers_date` (`mod_rems_property_transfers_date`), KEY `idx_mod_rems_property_transfers_status` (`mod_rems_property_transfers_status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- ============================================= -- POST-SALES MODULE -- ============================================= -- Incidents DROP TABLE IF EXISTS mod_rems_incidents; CREATE TABLE `mod_rems_incidents` ( `mod_rems_incidents_id` int NOT NULL AUTO_INCREMENT, -- Unique identifier for the incident `mod_rems_incidents_unit_id` int NOT NULL, -- Reference to the unit with the incident `mod_rems_incidents_contact_id` int NOT NULL, -- Reference to the contact reporting the incident `mod_rems_incidents_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Short title/description `mod_rems_incidents_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Detailed description `mod_rems_incidents_date` date NOT NULL, -- Date when incident occurred `mod_rems_incidents_severity` enum('low','medium','high','critical') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'medium', -- Severity level `mod_rems_incidents_status` enum('reported','in_progress','resolved','closed') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'reported', -- Current status `mod_rems_incidents_assigned_to` int DEFAULT NULL, -- Staff member assigned to resolve `mod_rems_incidents_resolution_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Notes about resolution `mod_rems_incidents_resolved_date` date DEFAULT NULL, -- Date when incident was resolved `mod_rems_incidents_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, -- Record creation timestamp `mod_rems_incidents_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Last update timestamp `mod_rems_incidents_state` int DEFAULT '1', -- 1: active, 0: inactive PRIMARY KEY (`mod_rems_incidents_id`) USING BTREE, KEY `idx_mod_rems_incidents_unit` (`mod_rems_incidents_unit_id`), KEY `idx_mod_rems_incidents_contact` (`mod_rems_incidents_contact_id`), KEY `idx_mod_rems_incidents_dates` (`mod_rems_incidents_date`,`mod_rems_incidents_resolved_date`), KEY `idx_mod_rems_incidents_status` (`mod_rems_incidents_status`), FULLTEXT KEY `ft_mod_rems_incidents_search` (`mod_rems_incidents_title`,`mod_rems_incidents_description`,`mod_rems_incidents_resolution_notes`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- ============================================= -- FINANCE MODULE -- ============================================= -- Payments DROP TABLE IF EXISTS mod_rems_payments; CREATE TABLE `mod_rems_payments` ( `mod_rems_payments_id` int NOT NULL AUTO_INCREMENT, -- Unique identifier for the payment `mod_rems_payments_sale_id` int DEFAULT NULL, -- Reference to the sale (if applicable) `mod_rems_payments_contact_id` int NOT NULL, -- Reference to the contact making the payment `mod_rems_payments_date` date NOT NULL, -- Date of the payment `mod_rems_payments_amount` decimal(15,2) NOT NULL, -- Payment amount `mod_rems_payments_method` enum('cash','bank_transfer','check','credit_card','debit_card','other') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, -- Payment method `mod_rems_payments_reference` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, -- Payment reference number `mod_rems_payments_status` enum('pending','completed','failed','refunded') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'pending', -- Payment status `mod_rems_payments_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, -- Additional notes `mod_rems_payments_due_date` date DEFAULT NULL, -- Due date for the payment `mod_rems_payments_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, -- Record creation timestamp `mod_rems_payments_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Last update timestamp `mod_rems_payments_state` int DEFAULT '1', -- 1: active, 0: inactive PRIMARY KEY (`mod_rems_payments_id`) USING BTREE, KEY `idx_mod_rems_payments_sale` (`mod_rems_payments_sale_id`), KEY `idx_mod_rems_payments_contact` (`mod_rems_payments_contact_id`), KEY `idx_mod_rems_payments_dates` (`mod_rems_payments_date`,`mod_rems_payments_due_date`), KEY `idx_mod_rems_payments_status` (`mod_rems_payments_status`), KEY `idx_mod_rems_payments_reference` (`mod_rems_payments_reference`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- ============================================= -- DOCUMENT MANAGEMENT -- ============================================= -- Document Versions CREATE TABLE IF NOT EXISTS `document_versions` ( `version_id` INT NOT NULL AUTO_INCREMENT, `document_id` INT, `version_number` INT NOT NULL, `file_path` VARCHAR(512) NOT NULL, `changes` TEXT, `uploaded_by` INT, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`version_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================= -- SYSTEM USERS (for internal use) -- ============================================= -- Módulos del sistema -- =================== -- 3900 - Dashboard Principal -- 3901 - Módulo de Proyectos -- 3902 - Módulo de Lotes -- 3903 - Módulo de Fases -- 3904 - Módulo de Contactos -- 3905 - Módulo de Documentos -- 3906 - Módulo de Campañas -- 3907 - Módulo de Leads -- 3908 - Módulo de Propiedades -- 3909 - Módulo de Unidades -- 3910 - Módulo de Reservas -- 3911 - Módulo de Ventas -- 3912 - Módulo de Transferencias de Propiedad -- 3913 - Módulo de Incidentes -- 3914 - Módulo de Pagos -- 3915 - Módulo de Reportes -- 3916 - Módulo de Configuración