-- Active: 1754444322322@@dev.io@3306@nucleo_base
#REMS - Real Estate Management System

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

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;



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;

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;

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
-- =============================================

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;

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)
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
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
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
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;

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
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
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
