-- ============================================
-- Premium User Features & Land Images
-- Run this SQL after existing tables are created
-- ============================================

-- 1. Add user_type column to users table
ALTER TABLE users ADD COLUMN user_type ENUM('basic', 'premium') DEFAULT 'basic' AFTER role;

-- 2. Add premium_amount column for custom payment amounts (NULL means use default)
ALTER TABLE users ADD COLUMN premium_amount DECIMAL(10,2) DEFAULT NULL AFTER user_type;

-- 3. Create land_images table
CREATE TABLE IF NOT EXISTS land_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    land_id INT NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    image_name VARCHAR(255),
    file_size INT,
    uploaded_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (land_id) REFERENCES lands(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_land_images (land_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Add premium_yearly_fee to payment_settings
INSERT INTO payment_settings (setting_key, setting_value, description)
VALUES ('premium_yearly_fee', '100000', 'Annual subscription fee for premium users (TZS)')
ON DUPLICATE KEY UPDATE setting_value = setting_value;

-- 5. Add max_images_per_land to payment_settings
INSERT INTO payment_settings (setting_key, setting_value, description)
VALUES ('max_images_per_land', '5', 'Maximum number of images per land for premium users')
ON DUPLICATE KEY UPDATE setting_value = setting_value;

-- 6. Update existing users to basic type
UPDATE users SET user_type = 'basic' WHERE user_type IS NULL;

-- Sample: Make a user premium with custom amount
-- UPDATE users SET user_type = 'premium', premium_amount = 150000 WHERE id = 1;
