// ==============================================================================
// NIGERIA LISTING: DATABASE SCHEMA TYPES
// ==============================================================================

export type ListingType = 'FOR_SALE' | 'FOR_RENT' | 'SHORTLET' | 'JOINT_VENTURE';

export type PropertyType =
  | 'APARTMENT_FLAT'
  | 'DETACHED_DUPLEX'
  | 'SEMI_DETACHED_DUPLEX'
  | 'TERRACED_DUPLEX'
  | 'MANSION'
  | 'BUNGALOW'
  | 'COMMERCIAL_OFFICE'
  | 'RETAIL_SHOP'
  | 'WAREHOUSE'
  | 'LAND_RESIDENTIAL'
  | 'LAND_COMMERCIAL'
  | 'MIXED_USE';

export type PropertyStatus =
  | 'DRAFT'
  | 'PENDING_REVIEW'
  | 'APPROVED'
  | 'PUBLISHED'
  | 'UNDER_OFFER'
  | 'SOLD'
  | 'RENTED'
  | 'REJECTED'
  | 'SUSPENDED'
  | 'ARCHIVED';

export type NigerianTitleType =
  | 'CERTIFICATE_OF_OCCUPANCY'
  | 'GOVERNORS_CONSENT'
  | 'DEED_OF_ASSIGNMENT'
  | 'GAZETTE'
  | 'EXCISION'
  | 'REGISTERED_SURVEY_PLAN'
  | 'COURT_JUDGEMENT'
  | 'RIGHT_OF_OCCUPANCY'
  | 'OTHER'
  | 'NONE';

export type LeadStatus =
  | 'NEW'
  | 'CONTACTED'
  | 'VIEWING_SCHEDULED'
  | 'VIEWING_COMPLETED'
  | 'NEGOTIATING'
  | 'WON'
  | 'LOST'
  | 'SPAM';

export type AgencyMemberRole = 'ADMIN' | 'AGENT' | 'COORDINATOR';
export type BillingInterval = 'MONTHLY' | 'QUARTERLY' | 'BI_ANNUAL' | 'ANNUAL';
export type SubscriptionStatus = 'TRIALING' | 'ACTIVE' | 'PAST_DUE' | 'CANCELED' | 'EXPIRED';
export type PaymentStatus = 'INITIALIZED' | 'PENDING' | 'SUCCESSFUL' | 'FAILED' | 'REFUNDED';
export type PaymentPurpose = 'SUBSCRIPTION' | 'FEATURED_LISTING' | 'VERIFICATION_FEE' | 'OTHER';

export interface UserRow {
  id: string;
  clerk_id: string;
  email: string;
  first_name: string | null;
  last_name: string | null;
  phone_number: string | null;
  whatsapp_number: string | null;
  avatar_url: string | null;
  is_active: boolean;
  is_suspended: boolean;
  suspended_reason: string | null;
  suspended_at: string | null;
  metadata: Record<string, unknown>;
  created_at: string;
  updated_at: string;
}

export interface RoleRow {
  id: string;
  name: string;
  description: string | null;
  is_system: boolean;
  created_at: string;
}

export interface PermissionRow {
  id: string;
  module: string;
  description: string;
}

export interface AgencyRow {
  id: string;
  name: string;
  slug: string;
  owner_id: string;
  cac_rc_number: string | null;
  tax_id_number: string | null;
  logo_url: string | null;
  cover_image_url: string | null;
  description: string | null;
  email: string | null;
  phone: string | null;
  whatsapp_number: string | null;
  office_address: string | null;
  state_id: number | null;
  lga_id: number | null;
  is_verified: boolean;
  verified_at: string | null;
  verified_by_user_id: string | null;
  is_active: boolean;
  created_at: string;
  updated_at: string;
}

export interface StateRow {
  id: number;
  name: string;
  code: string;
  slug: string;
}

export interface LgaRow {
  id: number;
  state_id: number;
  name: string;
  slug: string;
}

export interface DistrictRow {
  id: number;
  lga_id: number;
  name: string;
  slug: string;
  latitude: number | null;
  longitude: number | null;
}

export interface AmenityRow {
  id: string;
  name: string;
  category: 'SECURITY' | 'UTILITIES' | 'LEISURE' | 'COMFORT';
  icon_name: string | null;
}

export interface PropertyRow {
  id: string;
  reference_code: string;
  slug: string;
  title: string;
  description: string;
  listing_type: ListingType;
  property_type: PropertyType;
  price: number;
  currency: string;
  price_prefix: string | null;
  rental_frequency: string | null;
  service_charge: number;
  caution_fee: number;
  legal_fee_percentage: number;
  agency_fee_percentage: number;
  is_negotiable: boolean;
  bedrooms: number;
  bathrooms: number;
  toilets: number;
  parking_spaces: number;
  total_area_sqm: number | null;
  plot_size_plots: number | null;
  title_type: NigerianTitleType;
  is_off_plan: boolean;
  year_built: number | null;
  state_id: number;
  lga_id: number;
  district_id: number | null;
  street_address: string | null;
  landmark: string | null;
  latitude: number | null;
  longitude: number | null;
  hide_exact_address: boolean;
  created_by_user_id: string;
  agency_id: string | null;
  assigned_agent_id: string | null;
  status: PropertyStatus;
  is_featured: boolean;
  featured_until: string | null;
  is_verified: boolean;
  rejection_reason: string | null;
  moderated_by_user_id: string | null;
  moderated_at: string | null;
  published_at: string | null;
  view_count: number;
  inquiry_count: number;
  created_at: string;
  updated_at: string;
  deleted_at: string | null;
}

export interface PropertyImageRow {
  id: string;
  property_id: string;
  storage_path: string;
  url: string;
  thumbnail_url: string | null;
  caption: string | null;
  display_order: number;
  is_primary: boolean;
  created_at: string;
}

export interface LeadRow {
  id: string;
  property_id: string;
  agency_id: string | null;
  assigned_to_user_id: string | null;
  user_id: string | null;
  full_name: string;
  email: string;
  phone_number: string;
  whatsapp_number: string | null;
  message: string;
  status: LeadStatus;
  source: string;
  follow_up_date: string | null;
  lost_reason: string | null;
  created_at: string;
  updated_at: string;
}

export interface SubscriptionPlanRow {
  id: string;
  name: string;
  description: string | null;
  price_ngn: number;
  billing_interval: BillingInterval;
  entitlements: Record<string, unknown>;
  is_active: boolean;
  created_at: string;
}

export interface SubscriptionRow {
  id: string;
  user_id: string | null;
  agency_id: string | null;
  plan_id: string;
  status: SubscriptionStatus;
  current_period_start: string;
  current_period_end: string;
  cancel_at_period_end: boolean;
  canceled_at: string | null;
  created_at: string;
  updated_at: string;
}

export interface EntitlementOverrideRow {
  id: string;
  target_user_id: string | null;
  target_agency_id: string | null;
  entitlement_key: string;
  override_value: unknown;
  reason: string;
  expires_at: string | null;
  granted_by_user_id: string;
  is_active: boolean;
  revoked_at: string | null;
  revoked_by_user_id: string | null;
  created_at: string;
}

export interface PaymentRow {
  id: string;
  reference: string;
  user_id: string;
  agency_id: string | null;
  amount_kobo: number;
  currency: string;
  status: PaymentStatus;
  purpose: PaymentPurpose;
  subscription_id: string | null;
  metadata: Record<string, unknown>;
  paystack_channel: string | null;
  paystack_gateway_response: string | null;
  paid_at: string | null;
  created_at: string;
  updated_at: string;
}

export interface AuditLogRow {
  id: string;
  actor_user_id: string | null;
  actor_role: string | null;
  action: string;
  entity_type: string;
  entity_id: string;
  old_state: Record<string, unknown> | null;
  new_state: Record<string, unknown> | null;
  ip_address: string | null;
  user_agent: string | null;
  created_at: string;
}

export type ViewingStatus = 'SCHEDULED' | 'CONFIRMED' | 'COMPLETED' | 'CANCELLED' | 'NO_SHOW';
export type ViewingMeetingType = 'PHYSICAL' | 'VIRTUAL_VIDEO';

export interface ViewingAppointmentRow {
  id: string;
  lead_id: string;
  property_id: string;
  scheduled_by_user_id: string;
  scheduled_at: string;
  duration_minutes: number;
  meeting_type: ViewingMeetingType;
  meeting_location: string | null;
  notes: string | null;
  status: ViewingStatus;
  cancellation_reason: string | null;
  created_at: string;
  updated_at: string;
}

export type AgentKycStatus = 'PENDING' | 'APPROVED' | 'REJECTED' | 'NEEDS_ACTION';
export type AgentIdType = 'NIN_SLIP' | 'DRIVERS_LICENSE' | 'INTL_PASSPORT' | 'VOTERS_CARD';
export type AgentProfessionalBody = 'LASRERA' | 'ERCAAN' | 'NIESV' | 'REDAN' | 'CAC_BN' | 'OTHER';

export interface AgentVerificationRow {
  id: string;
  user_id: string;
  full_name: string;
  phone_number: string;
  nin_number: string;
  id_type: AgentIdType;
  id_document_url: string;
  professional_body: AgentProfessionalBody;
  registration_number: string;
  membership_document_url: string | null;
  proof_of_address_url: string | null;
  passport_photo_url: string | null;
  office_address: string | null;
  state_id: number | null;
  city: string | null;
  years_of_experience: number;
  specialization: string | null;
  status: AgentKycStatus;
  rejection_reason: string | null;
  action_required_reason?: string | null;
  action_required_field?: string | null;
  intended_plan_id?: string | null;
  intended_billing_interval?: BillingInterval | null;
  reviewed_by_user_id: string | null;
  reviewed_at: string | null;
  created_at: string;
  updated_at: string;
}

export interface SavedPropertyRow {
  id: string;
  user_id: string;
  property_id: string;
  created_at: string;
}

// ------------------------------------------------------------------------------
// RATINGS, REVIEWS & DEAL VERIFICATION TYPES
// ------------------------------------------------------------------------------

export type DealVerificationStatus =
  | 'UNVERIFIED_SELF_REPORTED'
  | 'PENDING_BUYER_CONFIRMATION'
  | 'PENDING_STAFF_AUDIT'
  | 'VERIFIED_PLATFORM_DEAL'
  | 'VERIFIED_OFFLINE_DEAL'
  | 'DISPUTED_OR_FLAGGED';

export interface PropertyTransactionRow {
  id: string;
  property_id: string;
  agent_id: string;
  agency_id: string | null;
  buyer_user_id: string | null;
  lead_id: string | null;
  viewing_id: string | null;
  transaction_type: 'SALE' | 'RENTAL' | 'SHORTLET';
  final_price: number | null;
  currency: string;
  verification_status: DealVerificationStatus;
  buyer_confirmation_token: string | null;
  buyer_confirmed_at: string | null;
  proof_document_url: string | null;
  proof_document_type: string | null;
  audited_by_user_id: string | null;
  audited_at: string | null;
  audit_notes: string | null;
  is_anomaly_flagged: boolean;
  anomaly_reason: string | null;
  closed_at: string;
  created_at: string;
  updated_at: string;
}

export type ReviewVerificationTier =
  | 'VERIFIED_BUYER_DEAL'
  | 'VERIFIED_INSPECTION'
  | 'VERIFIED_INQUIRY'
  | 'UNVERIFIED';

export type ReviewStatus = 'PUBLISHED' | 'PENDING_MODERATION' | 'FLAGGED' | 'REJECTED';

export interface ReviewCategoryRatings {
  responsiveness?: number;
  transparency?: number;
  punctuality?: number;
  knowledge?: number;
}

export interface ReviewRow {
  id: string;
  reviewer_id: string;
  target_type: 'AGENT' | 'AGENCY' | 'PROPERTY';
  target_id: string;
  property_id: string | null;
  transaction_id: string | null;
  viewing_id: string | null;
  rating: number; // 1 - 5
  category_ratings: ReviewCategoryRatings;
  title: string | null;
  comment: string | null;
  verification_tier: ReviewVerificationTier;
  status: ReviewStatus;
  flagged_reason: string | null;
  agent_response: string | null;
  agent_responded_at: string | null;
  helpful_count: number;
  ip_address: string | null;
  user_agent: string | null;
  created_at: string;
  updated_at: string;
  reviewer?: {
    id: string;
    first_name: string | null;
    last_name: string | null;
    avatar_url: string | null;
    email?: string;
  };
  property?: {
    id: string;
    title: string;
    slug: string;
    reference_code: string;
  };
}

export interface ReviewHelpfulVoteRow {
  id: string;
  review_id: string;
  user_id: string;
  created_at: string;
}

export interface RatingSummary {
  averageRating: number;
  totalReviews: number;
  verifiedBuyerReviews: number;
  verifiedInspectionReviews: number;
  starCounts: {
    5: number;
    4: number;
    3: number;
    2: number;
    1: number;
  };
  categoryAverages: {
    responsiveness: number;
    transparency: number;
    punctuality: number;
    knowledge: number;
  };
}

export interface AgentTrackRecordSummary {
  totalClosedDeals: number;
  verifiedPlatformDeals: number;
  verifiedOfflineDeals: number;
  selfReportedDeals: number;
  verifiedInspectionCount: number;
}