CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(191) NOT NULL UNIQUE,
    phone VARCHAR(50) NOT NULL,
    address VARCHAR(255),
    image VARCHAR(255),
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    
    -- Roles: controls what user can do
    role ENUM('admin','staff','basic','gold','premium','techguy') DEFAULT 'basic',
    
    -- Subscription plan: ties into pricing plans
    subscription_plan ENUM('basic','gold','premium') DEFAULT 'basic',
    
    -- Wallet handling
    balance DECIMAL(15,2) DEFAULT 0.00,
    currency VARCHAR(10) DEFAULT 'NGN',
    
    -- User account status
    status ENUM('active','inactive','pending') DEFAULT 'active',
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    
    -- Password reset handling
    reset_token VARCHAR(191) DEFAULT NULL,
    reset_expires DATETIME DEFAULT NULL
);





-- PROPERTIES table
CREATE TABLE properties (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    type ENUM('sale','rent') NOT NULL,
    category ENUM('residential','commercial','land','other') NOT NULL,
    price DECIMAL(15,2),

    -- Rental-specific fields
    monthly_fee DECIMAL(15,2) DEFAULT NULL,
    yearly_fee DECIMAL(15,2) DEFAULT NULL,
    other_fees DECIMAL(15,2) DEFAULT NULL,
    agency_fee DECIMAL(15,2) DEFAULT NULL,
    total_package DECIMAL(15,2) DEFAULT NULL,

    location VARCHAR(255),
    status ENUM('available','sold','rented','pending') DEFAULT 'available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- PROPERTY IMAGES
CREATE TABLE property_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    property_id INT NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
);

-- PROPERTY VIDEOS
CREATE TABLE property_videos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    property_id INT NOT NULL,
    video_path VARCHAR(255) NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
);

CREATE TABLE adverts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    property_id INT NOT NULL,
    user_id INT NOT NULL,
    location ENUM('homepage','property_details') NOT NULL,
    start_date DATE,
    end_date DATE,
    status ENUM('active','expired') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE admin_properties (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Type of property: estate, rental, or sale
    property_type ENUM('estate', 'rental', 'sale') NOT NULL,
    
    -- Property description
    description TEXT NOT NULL,
    
    -- Main property image
    property_image VARCHAR(255) NOT NULL,
    
    -- Additional images (up to 5), stored as comma-separated paths
    images VARCHAR(1024) DEFAULT NULL,
    
    -- Optional video path
    video VARCHAR(255) DEFAULT NULL,
    
    -- Price
    price DECIMAL(15,2) NOT NULL,
    
    -- Property location
    location VARCHAR(255) NOT NULL,
    
    -- Timestamp
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);  -- 1. Add a nullable admin_id column
ALTER TABLE admin_properties
ADD COLUMN admin_id INT DEFAULT NULL;

-- 2. Add a foreign key constraint that sets admin_id to NULL if the admin is deleted
ALTER TABLE admin_properties
ADD CONSTRAINT fk_admin
FOREIGN KEY (admin_id) REFERENCES admins(id)
ON DELETE SET NULL;



CREATE TABLE admin_adverts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_property_id INT NOT NULL,
    location ENUM('homepage','property_details','dashboard') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status ENUM('active','expired') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_property_id) REFERENCES admin_properties(id) ON DELETE CASCADE
);


CREATE TABLE property_inspections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    property_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    inspection_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (property_id) REFERENCES admin_properties(id) ON DELETE CASCADE
);



-- PROFILE VISITS
CREATE TABLE profile_visits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visitor_id INT NOT NULL,
    visited_user_id INT NOT NULL,
    visited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (visitor_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (visited_user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- PROPERTY VISITS
CREATE TABLE property_visits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visitor_id INT NOT NULL,
    property_id INT NOT NULL,
    visited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (visitor_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
);
-- TRANSACTIONS (wallet activities)
CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('credit','debit') NOT NULL, -- money in or out
    amount DECIMAL(15,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'NGN',
    description VARCHAR(255),
    reference VARCHAR(100) UNIQUE, -- e.g. txn ID from payment gateway
    status ENUM('pending','completed','failed') DEFAULT 'completed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);