-- ============================================================================
-- Propiedad Table Enhancement - Add Unit-Specific Fields
-- ============================================================================
-- Purpose: Add fields to support individual apartment/unit tracking
-- Author: Claude Code (Thoth's Algorithm)
-- Date: 2025-01-02
--
-- This script:
-- 1. Creates backup table
-- 2. Adds unit identification fields (numero_unidad, piso, tipo_unidad)
-- 3. Adds unit characteristics (metros_cuadrados, recamaras, banos, etc.)
-- 4. Creates indexes for performance
--
-- Usage:
--   mysql -u root -p --socket=/lamp/mysql/mysql.sock quantix < 01_alter_propiedad_add_unit_fields.sql
-- ============================================================================

-- Step 1: Create backup table
CREATE TABLE IF NOT EXISTS propiedad_backup_20250102 AS SELECT * FROM propiedad;

-- Verify backup
SELECT CONCAT('✓ Backup created: ', COUNT(*), ' rows') as status FROM propiedad_backup_20250102;

-- Step 2: Add unit identification fields
ALTER TABLE propiedad
    ADD COLUMN numero_unidad VARCHAR(10) NULL
        COMMENT 'Unit number: 01, 02, 101, 201, A, B, etc.'
        AFTER nombre_propiedad,

    ADD COLUMN piso INT NULL
        COMMENT 'Floor number (extracted from unit number or manually set)'
        AFTER numero_unidad,

    ADD COLUMN tipo_unidad ENUM('Departamento','Casa','Estudio','Penthouse','Loft')
        NULL DEFAULT 'Departamento'
        COMMENT 'Type of unit'
        AFTER piso;

-- Step 3: Add unit characteristics
ALTER TABLE propiedad
    ADD COLUMN orientacion ENUM('Norte','Sur','Este','Oeste',
                                'Noreste','Noroeste','Sureste','Suroeste',
                                'Interior','Esquina') NULL
        COMMENT 'Unit orientation/facing direction'
        AFTER tipo_unidad,

    ADD COLUMN metros_cuadrados DECIMAL(8,2) NULL
        COMMENT 'Square meters (M²)'
        AFTER orientacion,

    ADD COLUMN recamaras TINYINT UNSIGNED NULL DEFAULT 0
        COMMENT 'Number of bedrooms'
        AFTER metros_cuadrados,

    ADD COLUMN banos DECIMAL(3,1) UNSIGNED NULL DEFAULT 0
        COMMENT 'Number of bathrooms (e.g., 2.5 = 2 full + 1 half)'
        AFTER recamaras,

    ADD COLUMN estacionamientos TINYINT UNSIGNED NULL DEFAULT 0
        COMMENT 'Number of parking spaces'
        AFTER banos,

    ADD COLUMN bodega BOOLEAN DEFAULT FALSE
        COMMENT 'Has storage unit (bodega)'
        AFTER estacionamientos,

    ADD COLUMN balcon BOOLEAN DEFAULT FALSE
        COMMENT 'Has balcony/terrace'
        AFTER bodega,

    ADD COLUMN amueblado ENUM('Si','No','Parcial') NULL
        COMMENT 'Furnished status'
        AFTER balcon;

-- Step 4: Create indexes for performance
ALTER TABLE propiedad
    ADD INDEX idx_numero_unidad (numero_unidad),
    ADD INDEX idx_piso (piso),
    ADD INDEX idx_tipo_unidad (tipo_unidad);

-- Step 5: Verify alterations
DESCRIBE propiedad;

SELECT '✅ ALTER TABLE complete - New fields added successfully' as status;
SELECT 'Ready for expansion script: 02_expand_propiedades_to_units.php' as next_step;
