-- StaySpot Complete Database Schema
-- Version: 1.0.0
-- Created: 2024


-- Create properties table
CREATE TABLE properties (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  type ENUM('apartment', 'house', 'condo', 'townhouse', 'studio', 'villa') NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  bedrooms INT NOT NULL DEFAULT 0,
  bathrooms DECIMAL(3,1) NOT NULL DEFAULT 0,
  area DECIMAL(8,2) COMMENT 'Area in square feet',
  address VARCHAR(500) NOT NULL,
  city VARCHAR(100) NOT NULL,
  state VARCHAR(100) NOT NULL,
  zip_code VARCHAR(20) NOT NULL,
  country VARCHAR(100) NOT NULL DEFAULT 'USA',
  latitude DECIMAL(10,8),
  longitude DECIMAL(11,8),
  images JSON,
  amenities JSON,
  status ENUM('available', 'occupied', 'maintenance', 'unavailable') NOT NULL DEFAULT 'available',
  is_published BOOLEAN NOT NULL DEFAULT FALSE,
  landlord_id INT NOT NULL,
  features JSON,
  rules JSON,
  deposit_amount DECIMAL(10,2),
  available_from DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (landlord_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_landlord_id (landlord_id),
  INDEX idx_status (status),
  INDEX idx_type (type),
  INDEX idx_city (city),
  INDEX idx_price (price),
  INDEX idx_created_at (created_at),
  INDEX idx_location (city, state),
  FULLTEXT idx_search (title, description, address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create leases table
CREATE TABLE leases (
  id INT AUTO_INCREMENT PRIMARY KEY,
  start_date DATETIME NOT NULL,
  end_date DATETIME NOT NULL,
  monthly_rent DECIMAL(10,2) NOT NULL,
  security_deposit DECIMAL(10,2) NOT NULL,
  lease_document VARCHAR(500),
  status ENUM('active', 'expired', 'terminated', 'pending') NOT NULL DEFAULT 'pending',
  terms JSON,
  special_conditions TEXT,
  property_id INT NOT NULL,
  tenant_id INT NOT NULL,
  landlord_id INT NOT NULL,
  signed_at DATETIME,
  terminated_at DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
  FOREIGN KEY (tenant_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (landlord_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_property_id (property_id),
  INDEX idx_tenant_id (tenant_id),
  INDEX idx_landlord_id (landlord_id),
  INDEX idx_status (status),
  INDEX idx_dates (start_date, end_date),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create payments table
CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  amount DECIMAL(10,2) NOT NULL,
  due_date DATETIME NOT NULL,
  paid_date DATETIME,
  status ENUM('pending', 'paid', 'overdue', 'failed', 'refunded') NOT NULL DEFAULT 'pending',
  payment_method ENUM('credit_card', 'debit_card', 'bank_transfer', 'cash', 'check', 'online'),
  transaction_id VARCHAR(255),
  receipt_url VARCHAR(500),
  description TEXT,
  late_fee DECIMAL(10,2) DEFAULT 0,
  lease_id INT NOT NULL,
  tenant_id INT NOT NULL,
  landlord_id INT NOT NULL,
  property_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (lease_id) REFERENCES leases(id) ON DELETE CASCADE,
  FOREIGN KEY (tenant_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (landlord_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
  INDEX idx_lease_id (lease_id),
  INDEX idx_tenant_id (tenant_id),
  INDEX idx_landlord_id (landlord_id),
  INDEX idx_property_id (property_id),
  INDEX idx_status (status),
  INDEX idx_due_date (due_date),
  INDEX idx_paid_date (paid_date),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create maintenance_requests table
CREATE TABLE maintenance_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  priority ENUM('low', 'medium', 'high', 'emergency') NOT NULL DEFAULT 'medium',
  status ENUM('pending', 'in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'pending',
  category ENUM('plumbing', 'electrical', 'hvac', 'appliance', 'structural', 'cosmetic', 'pest_control', 'other') NOT NULL DEFAULT 'other',
  images JSON,
  scheduled_date DATETIME,
  completed_date DATETIME,
  cost DECIMAL(10,2),
  notes TEXT,
  tenant_id INT NOT NULL,
  property_id INT NOT NULL,
  assigned_to INT,
  landlord_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (tenant_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
  FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (landlord_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_tenant_id (tenant_id),
  INDEX idx_property_id (property_id),
  INDEX idx_assigned_to (assigned_to),
  INDEX idx_landlord_id (landlord_id),
  INDEX idx_status (status),
  INDEX idx_priority (priority),
  INDEX idx_category (category),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create notifications table
CREATE TABLE notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  message TEXT NOT NULL,
  type ENUM(
    'payment_reminder',
    'maintenance_update',
    'lease_expiry',
    'new_message',
    'system_alert',
    'booking_confirmation',
    'payment_received',
    'maintenance_scheduled'
  ) NOT NULL,
  is_read BOOLEAN NOT NULL DEFAULT FALSE,
  priority ENUM('low', 'medium', 'high') NOT NULL DEFAULT 'medium',
  metadata JSON,
  user_id INT NOT NULL,
  related_entity_type ENUM('property', 'lease', 'payment', 'maintenance', 'user'),
  related_entity_id INT,
  expires_at DATETIME,
  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,
  INDEX idx_user_id (user_id),
  INDEX idx_is_read (is_read),
  INDEX idx_type (type),
  INDEX idx_created_at (created_at),
  INDEX idx_user_read (user_id, is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create documents table
CREATE TABLE documents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  file_url VARCHAR(500) NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_size INT,
  mime_type VARCHAR(100),
  document_type ENUM(
    'lease_agreement',
    'id_proof',
    'income_proof',
    'property_document',
    'maintenance_report',
    'inspection_report',
    'insurance_document',
    'other'
  ) NOT NULL,
  category VARCHAR(100),
  tags JSON,
  is_public BOOLEAN NOT NULL DEFAULT FALSE,
  expires_at DATETIME,
  uploaded_by INT NOT NULL,
  property_id INT,
  lease_id INT,
  user_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
  FOREIGN KEY (lease_id) REFERENCES leases(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_document_type (document_type),
  INDEX idx_property_id (property_id),
  INDEX idx_lease_id (lease_id),
  INDEX idx_uploaded_by (uploaded_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create reviews table
CREATE TABLE reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
  title VARCHAR(255),
  comment TEXT,
  review_type ENUM('property', 'landlord', 'tenant') NOT NULL,
  response TEXT,
  responded_at DATETIME,
  is_published BOOLEAN NOT NULL DEFAULT TRUE,
  helpful_count INT NOT NULL DEFAULT 0,
  reviewer_id INT NOT NULL,
  reviewee_id INT,
  property_id INT,
  lease_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (reviewee_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
  FOREIGN KEY (lease_id) REFERENCES leases(id) ON DELETE CASCADE,
  INDEX idx_review_type (review_type, property_id),
  INDEX idx_reviewer_id (reviewer_id),
  INDEX idx_rating (rating)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create messages table
CREATE TABLE messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  content TEXT NOT NULL,
  message_type ENUM('text', 'image', 'file', 'system') NOT NULL DEFAULT 'text',
  attachments JSON,
  is_read BOOLEAN NOT NULL DEFAULT FALSE,
  read_at DATETIME,
  sender_id INT NOT NULL,
  receiver_id INT NOT NULL,
  property_id INT,
  lease_id INT,
  parent_message_id INT,
  conversation_id VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
  FOREIGN KEY (lease_id) REFERENCES leases(id) ON DELETE CASCADE,
  FOREIGN KEY (parent_message_id) REFERENCES messages(id) ON DELETE CASCADE,
  INDEX idx_conversation_id (conversation_id),
  INDEX idx_sender_receiver (sender_id, receiver_id),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create audit_logs table
CREATE TABLE audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  action VARCHAR(100) NOT NULL,
  entity_type ENUM('user', 'property', 'lease', 'payment', 'maintenance', 'document', 'review') NOT NULL,
  entity_id INT,
  old_values JSON,
  new_values JSON,
  description TEXT,
  ip_address VARCHAR(45),
  user_agent TEXT,
  user_id INT NOT NULL,
  affected_user_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (affected_user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_entity_type (entity_type, entity_id),
  INDEX idx_user_id (user_id),
  INDEX idx_created_at (created_at),
  INDEX idx_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample properties
INSERT INTO properties (title, description, type, price, bedrooms, bathrooms, area, address, city, state, zip_code, amenities, features, landlord_id, is_published, status, created_at) VALUES
('Luxury Downtown Apartment', 'Beautiful luxury apartment in the heart of downtown with amazing city views and modern amenities. Features hardwood floors, stainless steel appliances, and a private balcony overlooking the city.', 'apartment', 2500.00, 2, 2, 1200, '123 Main Street', 'New York', 'NY', '10001', '["wifi", "parking", "gym", "pool", "air_conditioning", "laundry", "dishwasher"]', '["hardwood_floor", "central_air", "balcony", "window_coverings"]', 3, TRUE, 'available', NOW()),

('Cozy Suburban House', 'Charming 3-bedroom house in quiet suburban neighborhood with garden and garage. Perfect for families with plenty of outdoor space and recent renovations throughout.', 'house', 1800.00, 3, 2, 1800, '456 Oak Avenue', 'Chicago', 'IL', '60007', '["parking", "garden", "pet_friendly", "laundry"]', '["fireplace", "patio", "garage", "basement"]', 3, TRUE, 'available', NOW()),

('Modern City Condo', 'Sleek modern condo with high-end finishes and convenient downtown location. Walking distance to restaurants, shopping, and public transportation.', 'condo', 3200.00, 1, 1, 900, '789 Downtown Blvd', 'San Francisco', 'CA', '94102', '["wifi", "gym", "security", "elevator", "air_conditioning"]', '["hardwood_floor", "window_coverings", "storage", "walk_in_closet"]', 3, TRUE, 'available', NOW()),

('Spacious Townhouse', 'Large townhouse perfect for families, featuring multiple levels and private outdoor space. Recently updated with modern kitchen and bathrooms.', 'townhouse', 2200.00, 4, 3, 2200, '321 Elm Street', 'Austin', 'TX', '73301', '["parking", "pet_friendly", "laundry", "air_conditioning"]', '["patio", "basement", "walk_in_closet", "storage"]', 3, TRUE, 'available', NOW()),

('Charming Studio Apartment', 'Compact and efficient studio apartment in vibrant neighborhood, perfect for singles or students. Recently renovated with new appliances and flooring.', 'studio', 1500.00, 1, 1, 600, '654 Pine Road', 'Portland', 'OR', '97205', '["wifi", "air_conditioning", "laundry"]', '["hardwood_floor", "window_coverings", "storage"]', 3, TRUE, 'available', NOW()),

('Luxury Villa', 'Exclusive luxury villa with premium amenities and private outdoor entertainment area. Features gourmet kitchen, home theater, and resort-style pool.', 'villa', 5000.00, 5, 4, 3500, '987 Hillside Drive', 'Los Angeles', 'CA', '90210', '["wifi", "parking", "pool", "gym", "security", "air_conditioning"]', '["fireplace", "patio", "garage", "balcony", "hardwood_floor"]', 3, TRUE, 'available', NOW()),

('Urban Loft Style', 'Industrial-chic loft with exposed brick and high ceilings in trendy neighborhood. Open concept living with modern fixtures throughout.', 'apartment', 2800.00, 1, 1, 1100, '555 Industrial Ave', 'Brooklyn', 'NY', '11201', '["wifi", "parking", "air_conditioning", "pet_friendly"]', '["hardwood_floor", "high_ceiling", "exposed_brick"]', 3, TRUE, 'available', NOW()),

('Family Friendly Home', 'Perfect family home in excellent school district with large backyard and play area. Updated kitchen and bathrooms with plenty of storage space.', 'house', 2400.00, 4, 2.5, 2100, '777 Maple Lane', 'Seattle', 'WA', '98101', '["parking", "garden", "pet_friendly", "laundry"]', '["fireplace", "patio", "garage", "basement", "garden"]', 3, TRUE, 'available', NOW());

-- Insert sample leases
INSERT INTO leases (start_date, end_date, monthly_rent, security_deposit, status, property_id, tenant_id, landlord_id, signed_at, created_at) VALUES
('2024-01-01', '2024-12-31', 2500.00, 2500.00, 'active', 1, 22, 3, '2023-12-15', NOW()),
('2024-02-01', '2025-01-31', 1800.00, 1800.00, 'active', 2, 23, 3, '2024-01-20', NOW()),
('2024-03-01', '2025-02-28', 3200.00, 3200.00, 'pending', 3, 22, 3, NULL, NOW()),
('2023-11-01', '2024-10-31', 2200.00, 2200.00, 'active', 4, 23, 3, '2023-10-15', NOW()),
('2024-01-15', '2024-12-14', 1500.00, 1500.00, 'active', 5, 22, 3, '2024-01-10', NOW());

-- Insert sample payments for the active leases
INSERT INTO payments (amount, due_date, paid_date, status, payment_method, description, lease_id, tenant_id, landlord_id, property_id, created_at) VALUES
-- Lease 1 payments
(2500.00, '2024-01-01', '2024-01-01', 'paid', 'bank_transfer', 'January 2024 Rent', 1, 22, 3, 1, NOW()),
(2500.00, '2024-02-01', '2024-02-01', 'paid', 'bank_transfer', 'February 2024 Rent', 1, 22, 3, 1, NOW()),
(2500.00, '2024-03-01', '2024-03-01', 'paid', 'credit_card', 'March 2024 Rent', 1, 22, 3, 1, NOW()),
(2500.00, '2024-04-01', NULL, 'pending', NULL, 'April 2024 Rent', 1, 22, 3, 1, NOW()),

-- Lease 2 payments
(1800.00, '2024-02-01', '2024-02-01', 'paid', 'bank_transfer', 'February 2024 Rent', 2, 23, 3, 2, NOW()),
(1800.00, '2024-03-01', '2024-03-01', 'paid', 'bank_transfer', 'March 2024 Rent', 2, 23, 3, 2, NOW()),
(1800.00, '2024-04-01', NULL, 'pending', NULL, 'April 2024 Rent', 2, 23, 3, 2, NOW()),

-- Lease 4 payments
(2200.00, '2023-11-01', '2023-11-01', 'paid', 'bank_transfer', 'November 2023 Rent', 4, 23, 3, 4, NOW()),
(2200.00, '2023-12-01', '2023-12-01', 'paid', 'bank_transfer', 'December 2023 Rent', 4, 23, 3, 4, NOW()),
(2200.00, '2024-01-01', '2024-01-01', 'paid', 'credit_card', 'January 2024 Rent', 4, 23, 3, 4, NOW()),
(2200.00, '2024-02-01', '2024-02-01', 'paid', 'credit_card', 'February 2024 Rent', 4, 23, 3, 4, NOW()),
(2200.00, '2024-03-01', '2024-03-01', 'paid', 'bank_transfer', 'March 2024 Rent', 4, 23, 3, 4, NOW()),
(2200.00, '2024-04-01', NULL, 'pending', NULL, 'April 2024 Rent', 4, 23, 3, 4, NOW()),

-- Lease 5 payments
(1500.00, '2024-01-15', '2024-01-15', 'paid', 'bank_transfer', 'January 2024 Rent', 5, 22, 3, 5, NOW()),
(1500.00, '2024-02-15', '2024-02-15', 'paid', 'bank_transfer', 'February 2024 Rent', 5, 22, 3, 5, NOW()),
(1500.00, '2024-03-15', '2024-03-15', 'paid', 'credit_card', 'March 2024 Rent', 5, 22, 3, 5, NOW()),
(1500.00, '2024-04-15', NULL, 'pending', NULL, 'April 2024 Rent', 5, 22, 3, 5, NOW());

-- Insert sample maintenance requests
INSERT INTO maintenance_requests (title, description, priority, status, category, tenant_id, property_id, landlord_id, created_at) VALUES
('Leaking Kitchen Faucet', 'The kitchen faucet has been leaking constantly for the past 2 days. It drips about once per second and is wasting water.', 'medium', 'pending', 'plumbing', 22, 1, 3, NOW()),
('AC Not Cooling', 'The air conditioning unit is running but not cooling the apartment. The temperature stays around 78°F even when set to 72°F.', 'high', 'in_progress', 'hvac', 23, 2, 3, NOW()),
('Broken Window Blinds', 'The living room window blinds are broken and won''t open or close properly. The mechanism seems to be stuck.', 'low', 'completed', 'cosmetic', 22, 1, 3, NOW()),
('Electrical Outlet Not Working', 'The electrical outlet in the bedroom stopped working yesterday. Tried resetting the breaker but it still doesn''t work.', 'high', 'pending', 'electrical', 23, 2, 3, NOW());

-- Insert sample notifications
INSERT INTO notifications (title, message, type, user_id, created_at) VALUES
('Welcome to StaySpot!', 'Thank you for joining StaySpot. Your account has been successfully created.', 'system_alert', 22, NOW()),
('Payment Received', 'Your rent payment of $2,500.00 for March 2024 has been received.', 'payment_received', 3, NOW()),
('Maintenance Request Submitted', 'Your maintenance request "Leaking Kitchen Faucet" has been received and will be reviewed shortly.', 'maintenance_update', 22, NOW()),
('Lease Renewal Reminder', 'Your lease for Luxury Downtown Apartment will expire in 60 days. Contact your landlord to discuss renewal options.', 'lease_expiry', 22, NOW());

-- Insert sample documents
INSERT INTO documents (title, file_url, file_name, document_type, uploaded_by, property_id, lease_id, created_at) VALUES
('Lease Agreement', 'https://example.com/documents/lease_agreement_1.pdf', 'lease_agreement_1.pdf', 'lease_agreement', 3, 1, 1, NOW()),
('Property Insurance', 'https://example.com/documents/insurance_property_1.pdf', 'insurance_property_1.pdf', 'insurance_document', 3, 1, NULL, NOW()),
('Tenant ID Verification', 'https://example.com/documents/id_tenant_22.pdf', 'id_tenant_22.pdf', 'id_proof', 22, NULL, 1, NOW());

-- Insert sample reviews
INSERT INTO reviews (rating, title, comment, review_type, reviewer_id, property_id, created_at) VALUES
(5, 'Excellent Downtown Apartment', 'Beautiful apartment with amazing views. The landlord is very responsive and the location is perfect for downtown living.', 'property', 22, 1, NOW()),
(4, 'Great Family Home', 'Spacious house in a quiet neighborhood. The backyard is perfect for kids and pets. Minor maintenance issues were handled promptly.', 'property', 23, 2, NOW()),
(5, 'Responsive and Professional', 'John is an excellent landlord who always addresses concerns quickly. Very professional and easy to work with.', 'landlord', 22, NULL, NOW());

-- Insert sample audit logs
INSERT INTO audit_logs (action, entity_type, entity_id, user_id, description, created_at) VALUES
('user_login', 'user', 22, 22, 'User tenant.jane@example.com logged in', NOW()),
('property_viewed', 'property', 1, 22, 'User viewed property Luxury Downtown Apartment', NOW()),
('payment_processed', 'payment', 1, 22, 'Rent payment processed for January 2024', NOW()),
('maintenance_created', 'maintenance', 1, 22, 'Maintenance request created: Leaking Kitchen Faucet', NOW());

-- Show properties by status
SELECT status, COUNT(*) as count 
FROM properties 
GROUP BY status 
ORDER BY count DESC;

-- Show leases by status
SELECT status, COUNT(*) as count 
FROM leases 
GROUP BY status 
ORDER BY count DESC;