Property Booking

using System.Security.Claims; using Microsoft.AspNetCore.Authentication.Cookies; using Microsoft.AspNetCore.Builder; using Microsoft.Extensions.Configuration; using Microsoft.Extensions.DependencyInjection; using Microsoft.Extensions.Hosting; using PropertyBooking.Data; using PropertyBooking.Repositories; var builder = WebApplication.CreateBuilder(args); // Configuration builder.Configuration.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true); // Services builder.Services.AddControllersWithViews(); builder.Services.AddSingleton(); builder.Services.AddScoped(); builder.Services.AddScoped(); builder.Services.AddScoped(); builder.Services.AddLogging(); builder.Services.AddAuthentication(CookieAuthenticationDefaults.AuthenticationScheme) .AddCookie(options => { options.LoginPath = "/Account/Login"; options.LogoutPath = "/Account/Logout"; options.ExpireTimeSpan = TimeSpan.FromHours(8); }); var app = builder.Build(); // Global exception handling and static files if (!app.Environment.IsDevelopment()) { app.UseExceptionHandler("/Home/Error"); } app.UseStaticFiles(); app.UseRouting(); app.UseAuthentication(); app.UseAuthorization(); // Routes app.MapControllerRoute( name: "default", pattern: "{controller=Property}/{action=Index}/{id?}"); app.Run(); { "ConnectionStrings": { "DefaultConnection": "Server=localhost;Database=PropertyBookingDB;Trusted_Connection=True;TrustServerCertificate=True;" }, "Logging": { "LogLevel": { "Default": "Information" } } } using System.Data; using System.Data.SqlClient; using Microsoft.Extensions.Configuration; namespace PropertyBooking.Data { // Lightweight DbContext wrapper for ADO.NET connection management public class DbContext { private readonly IConfiguration _configuration; public string ConnectionString { get; } public DbContext(IConfiguration configuration) { _configuration = configuration; ConnectionString = _configuration.GetConnectionString("DefaultConnection"); } public IDbConnection CreateConnection() { var conn = new SqlConnection(ConnectionString); conn.Open(); return conn; } } } using PropertyBooking.Models; namespace PropertyBooking.Repositories { public interface IUserRepository { Task RegisterAsync(User user); Task GetByEmailAsync(string email); Task ValidateUserAsync(string email, string password); } } using System.Data; using Microsoft.Extensions.Logging; using PropertyBooking.Data; using PropertyBooking.Models; namespace PropertyBooking.Repositories { public class UserRepository : IUserRepository { private readonly DbContext _db; private readonly ILogger _logger; public UserRepository(DbContext db, ILogger logger) { _db = db; _logger = logger; } public async Task RegisterAsync(User user) { try { using var conn = _db.CreateConnection(); using var cmd = conn.CreateCommand(); cmd.CommandText = "sp_RegisterUser"; cmd.CommandType = CommandType.StoredProcedure; var pName = cmd.CreateParameter(); pName.ParameterName = "@Name"; pName.Value = user.Name; cmd.Parameters.Add(pName); var pEmail = cmd.CreateParameter(); pEmail.ParameterName = "@Email"; pEmail.Value = user.Email; cmd.Parameters.Add(pEmail); var pPassword = cmd.CreateParameter(); pPassword.ParameterName = "@PasswordHash"; pPassword.Value = user.PasswordHash; cmd.Parameters.Add(pPassword); var pRole = cmd.CreateParameter(); pRole.ParameterName = "@Role"; pRole.Value = user.Role; cmd.Parameters.Add(pRole); var affected = await ((SqlCommand)cmd).ExecuteNonQueryAsync(); return affected > 0; } catch (Exception ex) { _logger.LogError(ex, "RegisterAsync failed"); throw; } } public async Task GetByEmailAsync(string email) { try { using var conn = _db.CreateConnection(); using var cmd = conn.CreateCommand(); cmd.CommandText = "sp_GetUserByEmail"; cmd.CommandType = CommandType.StoredProcedure; var pEmail = cmd.CreateParameter(); pEmail.ParameterName = "@Email"; pEmail.Value = email; cmd.Parameters.Add(pEmail); using var reader = await ((SqlCommand)cmd).ExecuteReaderAsync(); if (await reader.ReadAsync()) { return new User { Id = reader.GetInt32(reader.GetOrdinal("Id")), Name = reader.GetString(reader.GetOrdinal("Name")), Email = reader.GetString(reader.GetOrdinal("Email")), PasswordHash = reader.GetString(reader.GetOrdinal("PasswordHash")), Role = reader.GetString(reader.GetOrdinal("Role")) }; } return null; } catch (Exception ex) { _logger.LogError(ex, "GetByEmailAsync failed"); throw; } } public async Task ValidateUserAsync(string email, string password) { // For simplicity use plain match of password hash string (in real app, use salted hashing) var u = await GetByEmailAsync(email); if (u == null) return null; if (u.PasswordHash == password) return u; return null; } } } using PropertyBooking.Models; namespace PropertyBooking.Repositories { public interface IPropertyRepository { Task> GetAllAsync(); Task GetByIdAsync(int id); Task CreatePropertyAsync(Property property); Task> GetPropertyMediaAsync(int propertyId); } } using System.Data; using Microsoft.Extensions.Logging; using PropertyBooking.Data; using PropertyBooking.Models; namespace PropertyBooking.Repositories { public class PropertyRepository : IPropertyRepository { private readonly DbContext _db; private readonly ILogger _logger; public PropertyRepository(DbContext db, ILogger logger) { _db = db; _logger = logger; } public async Task> GetAllAsync() { var list = new List(); try { using var conn = _db.CreateConnection(); using var cmd = conn.CreateCommand(); cmd.CommandText = "sp_GetProperties"; cmd.CommandType = CommandType.StoredProcedure; using var reader = await ((SqlCommand)cmd).ExecuteReaderAsync(); while (await reader.ReadAsync()) { list.Add(new Property { Id = reader.GetInt32(reader.GetOrdinal("Id")), Title = reader.GetString(reader.GetOrdinal("Title")), Description = reader.GetString(reader.GetOrdinal("Description")), Price = reader.GetDecimal(reader.GetOrdinal("Price")), Address = reader.GetString(reader.GetOrdinal("Address")), Latitude = reader.GetDouble(reader.GetOrdinal("Latitude")), Longitude = reader.GetDouble(reader.GetOrdinal("Longitude")) }); } return list; } catch (Exception ex) { _logger.LogError(ex, "GetAllAsync failed"); throw; } } public async Task GetByIdAsync(int id) { try { using var conn = _db.CreateConnection(); using var cmd = conn.CreateCommand(); cmd.CommandText = "sp_GetPropertyById"; cmd.CommandType = CommandType.StoredProcedure; var pId = cmd.CreateParameter(); pId.ParameterName = "@Id"; pId.Value = id; cmd.Parameters.Add(pId); using var reader = await ((SqlCommand)cmd).ExecuteReaderAsync(); if (await reader.ReadAsync()) { var prop = new Property { Id = reader.GetInt32(reader.GetOrdinal("Id")), Title = reader.GetString(reader.GetOrdinal("Title")), Description = reader.GetString(reader.GetOrdinal("Description")), Price = reader.GetDecimal(reader.GetOrdinal("Price")), Address = reader.GetString(reader.GetOrdinal("Address")), Latitude = reader.GetDouble(reader.GetOrdinal("Latitude")), Longitude = reader.GetDouble(reader.GetOrdinal("Longitude")) }; return prop; } return null; } catch (Exception ex) { _logger.LogError(ex, "GetByIdAsync failed"); throw; } } public async Task CreatePropertyAsync(Property property) { try { using var conn = _db.CreateConnection(); using var cmd = conn.CreateCommand(); cmd.CommandText = "sp_CreateProperty"; cmd.CommandType = CommandType.StoredProcedure; var pTitle = cmd.CreateParameter(); pTitle.ParameterName = "@Title"; pTitle.Value = property.Title; cmd.Parameters.Add(pTitle); var pDesc = cmd.CreateParameter(); pDesc.ParameterName = "@Description"; pDesc.Value = property.Description; cmd.Parameters.Add(pDesc); var pPrice = cmd.CreateParameter(); pPrice.ParameterName = "@Price"; pPrice.Value = property.Price; cmd.Parameters.Add(pPrice); var pAddr = cmd.CreateParameter(); pAddr.ParameterName = "@Address"; pAddr.Value = property.Address; cmd.Parameters.Add(pAddr); var pLat = cmd.CreateParameter(); pLat.ParameterName = "@Latitude"; pLat.Value = property.Latitude; cmd.Parameters.Add(pLat); var pLon = cmd.CreateParameter(); pLon.ParameterName = "@Longitude"; pLon.Value = property.Longitude; cmd.Parameters.Add(pLon); var outId = new SqlParameter("@NewId", System.Data.SqlDbType.Int) { Direction = ParameterDirection.Output }; ((SqlCommand)cmd).Parameters.Add(outId); await ((SqlCommand)cmd).ExecuteNonQueryAsync(); return (int)outId.Value; } catch (Exception ex) { _logger.LogError(ex, "CreatePropertyAsync failed"); throw; } } public async Task> GetPropertyMediaAsync(int propertyId) { var list = new List(); try { using var conn = _db.CreateConnection(); using var cmd = conn.CreateCommand(); cmd.CommandText = "sp_GetPropertyMedia"; cmd.CommandType = CommandType.StoredProcedure; var pId = cmd.CreateParameter(); pId.ParameterName = "@PropertyId"; pId.Value = propertyId; cmd.Parameters.Add(pId); using var reader = await ((SqlCommand)cmd).ExecuteReaderAsync(); while (await reader.ReadAsync()) { list.Add(new PropertyMedia { Id = reader.GetInt32(reader.GetOrdinal("Id")), PropertyId = reader.GetInt32(reader.GetOrdinal("PropertyId")), MediaType = reader.GetString(reader.GetOrdinal("MediaType")), Url = reader.GetString(reader.GetOrdinal("Url")) }); } return list; } catch (Exception ex) { _logger.LogError(ex, "GetPropertyMediaAsync failed"); throw; } } } } using PropertyBooking.Models; namespace PropertyBooking.Repositories { public interface IBookingRepository { Task CreateBookingAsync(Booking booking); Task> GetBookingsForSellerAsync(int sellerId); } } using System.Data; using Microsoft.Extensions.Logging; using PropertyBooking.Data; using PropertyBooking.Models; namespace PropertyBooking.Repositories { public class BookingRepository : IBookingRepository { private readonly DbContext _db; private readonly ILogger _logger; public BookingRepository(DbContext db, ILogger logger) { _db = db; _logger = logger; } public async Task CreateBookingAsync(Booking booking) { try { using var conn = _db.CreateConnection(); using var cmd = conn.CreateCommand(); cmd.CommandText = "sp_CreateBooking"; cmd.CommandType = CommandType.StoredProcedure; var pProp = cmd.CreateParameter(); pProp.ParameterName = "@PropertyId"; pProp.Value = booking.PropertyId; cmd.Parameters.Add(pProp); var pBuyer = cmd.CreateParameter(); pBuyer.ParameterName = "@BuyerId"; pBuyer.Value = booking.BuyerId; cmd.Parameters.Add(pBuyer); var pNotes = cmd.CreateParameter(); pNotes.ParameterName = "@Notes"; pNotes.Value = booking.Notes ?? string.Empty; cmd.Parameters.Add(pNotes); var outId = new SqlParameter("@NewId", System.Data.SqlDbType.Int) { Direction = ParameterDirection.Output }; ((SqlCommand)cmd).Parameters.Add(outId); await ((SqlCommand)cmd).ExecuteNonQueryAsync(); return (int)outId.Value; } catch (Exception ex) { _logger.LogError(ex, "CreateBookingAsync failed"); throw; } } public async Task> GetBookingsForSellerAsync(int sellerId) { var list = new List(); try { using var conn = _db.CreateConnection(); using var cmd = conn.CreateCommand(); cmd.CommandText = "sp_GetBookingsForSeller"; cmd.CommandType = CommandType.StoredProcedure; var pSeller = cmd.CreateParameter(); pSeller.ParameterName = "@SellerId"; pSeller.Value = sellerId; cmd.Parameters.Add(pSeller); using var reader = await ((SqlCommand)cmd).ExecuteReaderAsync(); while (await reader.ReadAsync()) { list.Add(new BookingInfo { BookingId = reader.GetInt32(reader.GetOrdinal("BookingId")), PropertyTitle = reader.GetString(reader.GetOrdinal("Title")), BuyerName = reader.GetString(reader.GetOrdinal("BuyerName")), BuyerEmail = reader.GetString(reader.GetOrdinal("BuyerEmail")), Notes = reader.GetString(reader.GetOrdinal("Notes")), CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt")) }); } return list; } catch (Exception ex) { _logger.LogError(ex, "GetBookingsForSellerAsync failed"); throw; } } } } namespace PropertyBooking.Models { public class User { public int Id { get; set; } public string Name { get; set; } = ""; public string Email { get; set; } = ""; public string PasswordHash { get; set; } = ""; public string Role { get; set; } = "Buyer"; // Buyer or Seller or Admin } } namespace PropertyBooking.Models { public class Property { public int Id { get; set; } public string Title { get; set; } = ""; public string Description { get; set; } = ""; public decimal Price { get; set; } public string Address { get; set; } = ""; public double Latitude { get; set; } public double Longitude { get; set; } } public class PropertyMedia { public int Id { get; set; } public int PropertyId { get; set; } public string MediaType { get; set; } = ""; // "image" or "video" public string Url { get; set; } = ""; } } namespace PropertyBooking.Models { public class Booking { public int Id { get; set; } public int PropertyId { get; set; } public int BuyerId { get; set; } public string? Notes { get; set; } } public class BookingInfo { public int BookingId { get; set; } public string PropertyTitle { get; set; } = ""; public string BuyerName { get; set; } = ""; public string BuyerEmail { get; set; } = ""; public string Notes { get; set; } = ""; public DateTime CreatedAt { get; set; } } } using System.Security.Claims; using Microsoft.AspNetCore.Authentication; using Microsoft.AspNetCore.Authentication.Cookies; using Microsoft.AspNetCore.Mvc; using PropertyBooking.Models; using PropertyBooking.Repositories; namespace PropertyBooking.Controllers { public class AccountController : Controller { private readonly IUserRepository _users; private readonly ILogger _logger; public AccountController(IUserRepository users, ILogger logger) { _users = users; _logger = logger; } [HttpGet] public IActionResult Login(string? returnUrl = null) { ViewData["ReturnUrl"] = returnUrl; return View(); } [HttpPost] public async Task Login(string email, string password, string? returnUrl = null) { try { var user = await _users.ValidateUserAsync(email, password); if (user == null) { ModelState.AddModelError("", "Invalid credentials"); return View(); } var claims = new List { new Claim(ClaimTypes.Name, user.Name), new Claim(ClaimTypes.Email, user.Email), new Claim(ClaimTypes.Role, user.Role), new Claim("UserId", user.Id.ToString()) }; var identity = new ClaimsIdentity(claims, CookieAuthenticationDefaults.AuthenticationScheme); var principal = new ClaimsPrincipal(identity); await HttpContext.SignInAsync(CookieAuthenticationDefaults.AuthenticationScheme, principal); if (!string.IsNullOrEmpty(returnUrl) && Url.IsLocalUrl(returnUrl)) return Redirect(returnUrl); return RedirectToAction("Index", "Property"); } catch (Exception ex) { _logger.LogError(ex, "Login failed"); return StatusCode(500); } } [HttpGet] public IActionResult Register() { return View(); } [HttpPost] public async Task Register(string name, string email, string password, string role = "Buyer") { try { var existing = await _users.GetByEmailAsync(email); if (existing != null) { ModelState.AddModelError("", "Email already registered"); return View(); } var user = new User { Name = name, Email = email, PasswordHash = password, Role = role }; await _users.RegisterAsync(user); return RedirectToAction(nameof(Login)); } catch (Exception ex) { _logger.LogError(ex, "Register failed"); return StatusCode(500); } } [HttpPost] public async Task Logout() { await HttpContext.SignOutAsync(CookieAuthenticationDefaults.AuthenticationScheme); return RedirectToAction(nameof(Login)); } } } using Microsoft.AspNetCore.Authorization; using Microsoft.AspNetCore.Mvc; using PropertyBooking.Models; using PropertyBooking.Repositories; namespace PropertyBooking.Controllers { public class PropertyController : Controller { private readonly IPropertyRepository _props; private readonly ILogger _logger; public PropertyController(IPropertyRepository props, ILogger logger) { _props = props; _logger = logger; } [HttpGet] public async Task Index() { try { var list = await _props.GetAllAsync(); return View(list); } catch (Exception ex) { _logger.LogError(ex, "Index failed"); return StatusCode(500); } } [HttpGet] public async Task Details(int id) { try { var prop = await _props.GetByIdAsync(id); if (prop == null) return NotFound(); var media = await _props.GetPropertyMediaAsync(id); ViewData["Media"] = media; return View(prop); } catch (Exception ex) { _logger.LogError(ex, "Details failed"); return StatusCode(500); } } [Authorize(Roles = "Seller,Admin")] [HttpGet] public IActionResult Create() { return View(); } [Authorize(Roles = "Seller,Admin")] [HttpPost] public async Task Create(Property model) { try { var newId = await _props.CreatePropertyAsync(model); return RedirectToAction("Details", new { id = newId }); } catch (Exception ex) { _logger.LogError(ex, "Create failed"); ModelState.AddModelError("", "Unable to create property"); return View(model); } } } } using Microsoft.AspNetCore.Authorization; using Microsoft.AspNetCore.Mvc; using PropertyBooking.Models; using PropertyBooking.Repositories; namespace PropertyBooking.Controllers { [Authorize] public class BookingController : Controller { private readonly IBookingRepository _bookings; private readonly ILogger _logger; public BookingController(IBookingRepository bookings, ILogger logger) { _bookings = bookings; _logger = logger; } [HttpGet] public IActionResult Confirm(int propertyId) { ViewData["PropertyId"] = propertyId; return View(); } [HttpPost] public async Task Confirm(int propertyId, string notes) { try { var userId = int.Parse(User.Claims.First(c => c.Type == "UserId").Value); var booking = new Booking { PropertyId = propertyId, BuyerId = userId, Notes = notes }; var id = await _bookings.CreateBookingAsync(booking); return RedirectToAction("Index", "Property"); } catch (Exception ex) { _logger.LogError(ex, "Confirm failed"); return StatusCode(500); } } [Authorize(Roles = "Seller,Admin")] [HttpGet] public async Task SellerBookings() { try { var sellerId = int.Parse(User.Claims.First(c => c.Type == "UserId").Value); var list = await _bookings.GetBookingsForSellerAsync(sellerId); return View(list); } catch (Exception ex) { _logger.LogError(ex, "SellerBookings failed"); return StatusCode(500); } } } } using Microsoft.AspNetCore.Authorization; using Microsoft.AspNetCore.Mvc; namespace PropertyBooking.Controllers { [Authorize(Roles = "Admin")] public class AdminController : Controller { public IActionResult Index() { return View(); } } } @using Microsoft.AspNetCore.Authentication @using Microsoft.AspNetCore.Authentication.Cookies Property Booking

PropertyBooking

Home @if (User.Identity?.IsAuthenticated ?? false) { My Bookings @if (User.IsInRole("Seller")) { Create Property } @if (User.IsInRole("Admin")) { Admin } } else { Login Register }

@RenderBody()

@{ Layout = "_Layout.cshtml"; }

Login

@{ Layout = "_Layout.cshtml"; }

Register

@model IEnumerable @{ Layout = "_Layout.cshtml"; }

Properties

@foreach (var p in Model) {

@p.Title

@p.Address

@p.Description?.Length > 150 ? p.Description.Substring(0,150) + "..." : p.Description

@p.Price.ToString("C")

View

}

@model PropertyBooking.Models.Property @{ Layout = "_Layout.cshtml"; var media = ViewData["Media"] as IEnumerable; }

@Model.Title

@Model.Address

@Model.Description

@Model.Price.ToString("C")

@if (media != null && media.Any()) {

@foreach (var m in media) { if (m.MediaType == "image") {

} else if (m.MediaType == "video") {

} }

}

@if (User.Identity?.IsAuthenticated ?? false) { Book to Buy } else { Login to Book } @model PropertyBooking.Models.Property @{ Layout = "_Layout.cshtml"; }

Create Property

@{ Layout = "_Layout.cshtml"; var propId = ViewData["PropertyId"]; }

Confirm Booking

@model IEnumerable @{ Layout = "_Layout.cshtml"; }

Bookings for Your Properties

@foreach (var b in Model) { }

Property	Buyer	Contact	Notes	When
@b.PropertyTitle	@b.BuyerName	@b.BuyerEmail	@b.Notes	@b.CreatedAt.ToString("g")

@{ Layout = "_Layout.cshtml"; }

Admin Panel

Admin-only controls go here.

/* Simple responsive and attractive UI */ body { font-family: Arial, Helvetica, sans-serif; margin:0; color:#333; background:#f7f7f9; } .container { max-width:1100px; margin:20px auto; padding:0 16px; } .topbar { background: #fff; border-bottom:1px solid #e4e4e4; } .topbar .container { display:flex; align-items:center; justify-content:space-between; padding:14px 16px; } .brand { font-weight:700; font-size:20px; color:#2c3e50; text-decoration:none; } nav a { margin-left:12px; color:#2c3e50; text-decoration:none; } .logout { display:inline; margin-left:12px; } .main { padding:20px 0; } .grid { display:grid; grid-template-columns:repeat(auto-fit,minmax(240px,1fr)); gap:16px; } .card { background:#fff; padding:16px; border-radius:8px; box-shadow:0 1px 6px rgba(0,0,0,0.04); } .price { font-weight:700; color:#27ae60; } .btn { display:inline-block; padding:8px 12px; background:#2c3e50; color:#fff; text-decoration:none; border-radius:6px; border:none; cursor:pointer; } .form-row { margin-bottom:12px; display:flex; flex-direction:column; } .form-row label { margin-bottom:6px; font-weight:600; } input, textarea, select { padding:8px; border-radius:6px; border:1px solid #ddd; } .table { width:100%; border-collapse:collapse; background:#fff; border-radius:6px; overflow:hidden; box-shadow:0 1px 6px rgba(0,0,0,0.04); } .table th, .table td { padding:10px 12px; border-bottom:1px solid #f1f1f1; text-align:left; } .media-gallery { display:flex; gap:8px; flex-wrap:wrap; margin:12px 0; } .thumb { max-width:260px; border-radius:6px; box-shadow:0 1px 4px rgba(0,0,0,0.06); } .muted { color:#7d8893; } .footer { padding:14px 0; text-align:center; color:#777; margin-top:28px; } @media (max-width:600px) { .topbar .container { flex-direction:column; align-items:flex-start; gap:8px; } nav a { margin-left:0; margin-right:8px; } } // Minimal client script document.addEventListener("DOMContentLoaded", function () { // placeholder for future interactivity }); -- SQL setup script for PropertyBookingDB -- Run this on your SQL Server instance. Adjust file paths/ownership as needed. IF DB_ID('PropertyBookingDB') IS NULL BEGIN CREATE DATABASE PropertyBookingDB; END GO USE PropertyBookingDB; GO -- Users table IF OBJECT_ID('dbo.Users') IS NULL BEGIN CREATE TABLE dbo.Users ( Id INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(200) NOT NULL, Email NVARCHAR(200) NOT NULL UNIQUE, PasswordHash NVARCHAR(500) NOT NULL, Role NVARCHAR(50) NOT NULL, CreatedAt DATETIME NOT NULL DEFAULT GETDATE() ); END GO -- Properties table IF OBJECT_ID('dbo.Properties') IS NULL BEGIN CREATE TABLE dbo.Properties ( Id INT IDENTITY(1,1) PRIMARY KEY, Title NVARCHAR(250) NOT NULL, Description NVARCHAR(MAX) NULL, Price DECIMAL(18,2) NOT NULL, Address NVARCHAR(500) NULL, Latitude FLOAT NULL, Longitude FLOAT NULL, CreatedAt DATETIME NOT NULL DEFAULT GETDATE() ); END GO -- PropertyMedia IF OBJECT_ID('dbo.PropertyMedia') IS NULL BEGIN CREATE TABLE dbo.PropertyMedia ( Id INT IDENTITY(1,1) PRIMARY KEY, PropertyId INT NOT NULL, MediaType NVARCHAR(50) NOT NULL, -- 'image' or 'video' Url NVARCHAR(1000) NOT NULL, CreatedAt DATETIME NOT NULL DEFAULT GETDATE(), CONSTRAINT FK_PropertyMedia_Property FOREIGN KEY (PropertyId) REFERENCES dbo.Properties(Id) ON DELETE CASCADE ); END GO -- Bookings IF OBJECT_ID('dbo.Bookings') IS NULL BEGIN CREATE TABLE dbo.Bookings ( Id INT IDENTITY(1,1) PRIMARY KEY, PropertyId INT NOT NULL, BuyerId INT NOT NULL, Notes NVARCHAR(MAX) NULL, CreatedAt DATETIME NOT NULL DEFAULT GETDATE(), CONSTRAINT FK_Booking_Property FOREIGN KEY (PropertyId) REFERENCES dbo.Properties(Id), CONSTRAINT FK_Booking_Buyer FOREIGN KEY (BuyerId) REFERENCES dbo.Users(Id) ); END GO -- Stored Procedures -- Register user IF OBJECT_ID('dbo.sp_RegisterUser') IS NULL BEGIN CREATE PROCEDURE dbo.sp_RegisterUser @Name NVARCHAR(200), @Email NVARCHAR(200), @PasswordHash NVARCHAR(500), @Role NVARCHAR(50) AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.Users (Name, Email, PasswordHash, Role) VALUES (@Name, @Email, @PasswordHash, @Role); END END GO -- Get user by email IF OBJECT_ID('dbo.sp_GetUserByEmail') IS NULL BEGIN CREATE PROCEDURE dbo.sp_GetUserByEmail @Email NVARCHAR(200) AS BEGIN SET NOCOUNT ON; SELECT Id, Name, Email, PasswordHash, Role FROM dbo.Users WHERE Email = @Email; END END GO -- Get properties IF OBJECT_ID('dbo.sp_GetProperties') IS NULL BEGIN CREATE PROCEDURE dbo.sp_GetProperties AS BEGIN SET NOCOUNT ON; SELECT Id, Title, Description, Price, Address, Latitude, Longitude FROM dbo.Properties ORDER BY CreatedAt DESC; END END GO -- Get property by id IF OBJECT_ID('dbo.sp_GetPropertyById') IS NULL BEGIN CREATE PROCEDURE dbo.sp_GetPropertyById @Id INT AS BEGIN SET NOCOUNT ON; SELECT Id, Title, Description, Price, Address, Latitude, Longitude FROM dbo.Properties WHERE Id = @Id; END END GO -- Create property IF OBJECT_ID('dbo.sp_CreateProperty') IS NULL BEGIN CREATE PROCEDURE dbo.sp_CreateProperty @Title NVARCHAR(250), @Description NVARCHAR(MAX), @Price DECIMAL(18,2), @Address NVARCHAR(500), @Latitude FLOAT, @Longitude FLOAT, @NewId INT OUTPUT AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.Properties (Title, Description, Price, Address, Latitude, Longitude) VALUES (@Title, @Description, @Price, @Address, @Latitude, @Longitude); SET @NewId = SCOPE_IDENTITY(); END END GO -- Get property media IF OBJECT_ID('dbo.sp_GetPropertyMedia') IS NULL BEGIN CREATE PROCEDURE dbo.sp_GetPropertyMedia @PropertyId INT AS BEGIN SET NOCOUNT ON; SELECT Id, PropertyId, MediaType, Url FROM dbo.PropertyMedia WHERE PropertyId = @PropertyId ORDER BY Id; END END GO -- Create booking IF OBJECT_ID('dbo.sp_CreateBooking') IS NULL BEGIN CREATE PROCEDURE dbo.sp_CreateBooking @PropertyId INT, @BuyerId INT, @Notes NVARCHAR(MAX), @NewId INT OUTPUT AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.Bookings (PropertyId, BuyerId, Notes) VALUES (@PropertyId, @BuyerId, @Notes); SET @NewId = SCOPE_IDENTITY(); END END GO -- Get bookings for seller (simple example: all bookings; in a real system you'd link properties to seller user) -- For demo this returns bookings joined with properties and buyer info IF OBJECT_ID('dbo.sp_GetBookingsForSeller') IS NULL BEGIN CREATE PROCEDURE dbo.sp_GetBookingsForSeller @SellerId INT AS BEGIN SET NOCOUNT ON; SELECT b.Id AS BookingId, p.Title, u.Name AS BuyerName, u.Email AS BuyerEmail, b.Notes, b.CreatedAt FROM dbo.Bookings b INNER JOIN dbo.Properties p ON p.Id = b.PropertyId INNER JOIN dbo.Users u ON u.Id = b.BuyerId ORDER BY b.CreatedAt DESC; END END GO -- Seed a default admin and seller for convenience (if not exists) IF NOT EXISTS (SELECT 1 FROM dbo.Users WHERE Email = 'admin@example.com') BEGIN EXEC dbo.sp_RegisterUser @Name='Admin', @Email='admin@example.com', @PasswordHash='admin123', @Role='Admin'; END IF NOT EXISTS (SELECT 1 FROM dbo.Users WHERE Email = 'seller@example.com') BEGIN EXEC dbo.sp_RegisterUser @Name='Seller', @Email='seller@example.com', @PasswordHash='seller123', @Role='Seller'; END GO