Coverage for src / idx_api / routers / tours.py: 31%

1"""Tour request endpoints.""" 

2 

3from datetime import datetime, timezone 

4 

5from fastapi import APIRouter, Depends, HTTPException 

6from sqlalchemy import text 

7from sqlalchemy.orm import Session 

8 

9from idx_api.database import get_db 

10from idx_api.schemas.tour import TourRequestCreate, TourRequestResponse 

11 

12router = APIRouter() 

13 

14 

15def ensure_tour_requests_table(db: Session) -> None: 

16 """Create tour_requests table if it doesn't exist.""" 

17 # Check if table exists first 

18 result = db.execute( 

19 text("SELECT name FROM sqlite_master WHERE type='table' AND name='tour_requests'") 

20 ).first() 

21 

22 if result is None: 

23 # Table doesn't exist, create it using raw cursor without commit 

24 # DDL executes immediately in SQLite, no commit needed 

25 connection = db.connection().connection 

26 cursor = connection.cursor() 

27 cursor.execute(""" 

28 CREATE TABLE tour_requests ( 

29 id INTEGER PRIMARY KEY AUTOINCREMENT, 

30 property_id TEXT NOT NULL, 

31 property_address TEXT NOT NULL, 

32 name TEXT NOT NULL, 

33 email TEXT NOT NULL, 

34 phone TEXT NOT NULL, 

35 preferred_date TEXT, 

36 preferred_time TEXT, 

37 message TEXT, 

38 created_at TEXT NOT NULL, 

39 status TEXT NOT NULL DEFAULT 'new', 

40 FOREIGN KEY (property_id) REFERENCES properties(listing_id) 

41 ) 

42 """) 

43 cursor.close() 

44 # No commit - SQLite DDL is immediate, let session handle transaction 

45 

46 

47@router.post("", response_model=TourRequestResponse, status_code=201) 

48async def create_tour_request( 

49 request: TourRequestCreate, 

50 db: Session = Depends(get_db), 

51): 

52 """ 

53 Create a new tour request for a property. 

54 

55 The request will be stored in the database with status 'new' 

56 for follow-up by the brokerage. 

57 """ 

58 # Ensure table exists 

59 ensure_tour_requests_table(db) 

60 

61 # Verify property exists 

62 property_exists = db.execute( 

63 text("SELECT 1 FROM properties WHERE listing_id = :id AND mlg_can_view = 1"), 

64 {"id": request.property_id}, 

65 ).first() 

66 

67 if not property_exists: 

68 raise HTTPException(status_code=404, detail="Property not found") 

69 

70 # Insert tour request 

71 now = datetime.now(timezone.utc).isoformat() 

72 result = db.execute( 

73 text(""" 

74 INSERT INTO tour_requests 

75 (property_id, property_address, name, email, phone, 

76 preferred_date, preferred_time, message, created_at, status) 

77 VALUES 

78 (:property_id, :property_address, :name, :email, :phone, 

79 :preferred_date, :preferred_time, :message, :created_at, 'new') 

80 RETURNING * 

81 """), 

82 { 

83 "property_id": request.property_id, 

84 "property_address": request.property_address, 

85 "name": request.name, 

86 "email": request.email, 

87 "phone": request.phone, 

88 "preferred_date": request.preferred_date, 

89 "preferred_time": request.preferred_time, 

90 "message": request.message, 

91 "created_at": now, 

92 }, 

93 ) 

94 

95 # Fetch result BEFORE committing to avoid "SQL statements in progress" 

96 tour_request = result.mappings().first() 

97 if not tour_request: 

98 raise HTTPException(status_code=500, detail="Failed to create tour request") 

99 

100 # Now commit after consuming the result 

101 db.commit() 

102 

103 return TourRequestResponse.model_validate(dict(tour_request)) 

104 

105 

106@router.get("", response_model=list[TourRequestResponse]) 

107async def list_tour_requests( 

108 status: str | None = None, 

109 limit: int = 50, 

110 db: Session = Depends(get_db), 

111): 

112 """ 

113 List tour requests (admin endpoint). 

114 

115 Can filter by status: new, contacted, scheduled, completed, cancelled 

116 """ 

117 ensure_tour_requests_table(db) 

118 

119 conditions = [] 

120 params = {"limit": limit} 

121 

122 if status: 

123 conditions.append("status = :status") 

124 params["status"] = status 

125 

126 where_clause = f"WHERE {' AND '.join(conditions)}" if conditions else "" 

127 

128 results = db.execute( 

129 text(f""" 

130 SELECT * FROM tour_requests 

131 {where_clause} 

132 ORDER BY created_at DESC 

133 LIMIT :limit 

134 """), 

135 params, 

136 ).mappings().all() 

137 

138 return [TourRequestResponse.model_validate(dict(r)) for r in results] 

139 

140 

141@router.get("/{request_id}", response_model=TourRequestResponse) 

142async def get_tour_request( 

143 request_id: int, 

144 db: Session = Depends(get_db), 

145): 

146 """Get a specific tour request by ID.""" 

147 ensure_tour_requests_table(db) 

148 

149 result = db.execute( 

150 text("SELECT * FROM tour_requests WHERE id = :id"), 

151 {"id": request_id}, 

152 ).mappings().first() 

153 

154 if not result: 

155 raise HTTPException(status_code=404, detail="Tour request not found") 

156 

157 return TourRequestResponse.model_validate(dict(result))