CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  phone TEXT,
  password_hash TEXT NOT NULL,
  role TEXT NOT NULL CHECK(role IN ('admin','partner')),
  status TEXT NOT NULL DEFAULT 'active',
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS sessions (
  session_id TEXT PRIMARY KEY,
  user_id INTEGER NOT NULL,
  expires_at TEXT NOT NULL,
  created_at TEXT NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS partners (
  user_id INTEGER PRIMARY KEY,
  partner_type TEXT NOT NULL CHECK(partner_type IN ('agent','developer','provider','operator')),
  company_name TEXT NOT NULL,
  city TEXT NOT NULL,
  areas_json TEXT NOT NULL DEFAULT '[]',
  vertical_type TEXT NOT NULL DEFAULT 'property',
  service_categories_json TEXT NOT NULL DEFAULT '[]',
  coverage_postcodes_json TEXT NOT NULL DEFAULT '[]',
  supports_domestic INTEGER NOT NULL DEFAULT 1,
  supports_commercial INTEGER NOT NULL DEFAULT 0,
  same_day_available INTEGER NOT NULL DEFAULT 0,
  weekend_available INTEGER NOT NULL DEFAULT 0,
  preferred_lead_types_json TEXT NOT NULL DEFAULT '[]',
  vehicle_types TEXT,
  crew_size TEXT,
  lead_budget_min INTEGER NOT NULL DEFAULT 0,
  lead_budget_max INTEGER NOT NULL DEFAULT 0,
  insurance_details TEXT,
  license_details TEXT,
  address TEXT,
  tax_id TEXT,
  verified_status TEXT NOT NULL DEFAULT 'pending',
  created_by_admin_id INTEGER,
  created_at TEXT NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id),
  FOREIGN KEY(created_by_admin_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS packages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  monthly_credits INTEGER NOT NULL,
  price_monthly INTEGER NOT NULL,
  price_annual INTEGER NOT NULL,
  cities_allowed INTEGER NOT NULL DEFAULT 1,
  features_json TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS subscriptions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  partner_id INTEGER NOT NULL,
  package_id INTEGER NOT NULL,
  start_date TEXT NOT NULL,
  end_date TEXT NOT NULL,
  status TEXT NOT NULL CHECK(status IN ('active','paused','expired','cancelled')),
  credits_monthly INTEGER NOT NULL,
  credits_used INTEGER NOT NULL DEFAULT 0,
  credits_remaining INTEGER NOT NULL DEFAULT 0,
  finalized_amount INTEGER NOT NULL DEFAULT 0,
  assigned_by_admin_id INTEGER,
  approval_status TEXT NOT NULL DEFAULT 'approved',
  payment_proof_url TEXT,
  approved_by_admin_id INTEGER,
  approved_at TEXT,
  billing_cycle TEXT NOT NULL DEFAULT 'yearly',
  credits_reset_month TEXT,
  created_at TEXT NOT NULL,
  FOREIGN KEY(partner_id) REFERENCES users(id),
  FOREIGN KEY(package_id) REFERENCES packages(id),
  FOREIGN KEY(assigned_by_admin_id) REFERENCES users(id),
  FOREIGN KEY(approved_by_admin_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS invoices (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  partner_id INTEGER NOT NULL,
  invoice_number TEXT,
  client_id INTEGER,
  lead_assignment_id INTEGER,
  customer_name TEXT,
  customer_phone TEXT,
  customer_email TEXT,
  customer_address TEXT,
  amount INTEGER NOT NULL,
  period TEXT NOT NULL,
  status TEXT NOT NULL,
  issue_date TEXT,
  due_date TEXT,
  subtotal REAL NOT NULL DEFAULT 0,
  tax_amount REAL NOT NULL DEFAULT 0,
  total_amount REAL NOT NULL DEFAULT 0,
  amount_paid REAL NOT NULL DEFAULT 0,
  notes TEXT,
  share_token TEXT,
  pdf_url TEXT,
  created_at TEXT NOT NULL,
  FOREIGN KEY(partner_id) REFERENCES users(id),
  FOREIGN KEY(client_id) REFERENCES clients(id),
  FOREIGN KEY(lead_assignment_id) REFERENCES lead_assignments(id)
);

CREATE TABLE IF NOT EXISTS partner_invoice_settings (
  partner_id INTEGER PRIMARY KEY,
  company_name TEXT,
  contact_email TEXT,
  contact_phone TEXT,
  address TEXT,
  vat_number TEXT,
  logo_url TEXT,
  payment_terms TEXT,
  bank_details TEXT,
  footer_note TEXT,
  updated_at TEXT NOT NULL,
  FOREIGN KEY(partner_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS invoice_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  invoice_id INTEGER NOT NULL,
  service_name TEXT NOT NULL,
  quantity REAL NOT NULL DEFAULT 1,
  unit_price REAL NOT NULL DEFAULT 0,
  line_total REAL NOT NULL DEFAULT 0,
  notes TEXT,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  FOREIGN KEY(invoice_id) REFERENCES invoices(id)
);

CREATE TABLE IF NOT EXISTS leads (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  phone TEXT NOT NULL,
  email TEXT,
  city TEXT NOT NULL,
  area TEXT NOT NULL,
  budget_min INTEGER,
  budget_max INTEGER,
  property_type TEXT NOT NULL,
  purpose TEXT NOT NULL CHECK(purpose IN ('buy','rent')),
  timeframe TEXT,
  source TEXT,
  vertical_type TEXT NOT NULL DEFAULT 'property',
  lead_score INTEGER NOT NULL DEFAULT 0,
  service_category TEXT,
  lead_type TEXT NOT NULL DEFAULT 'platform' CHECK(lead_type IN ('platform','partner_ad')),
  lead_bucket TEXT NOT NULL DEFAULT 'system_pool' CHECK(lead_bucket IN ('system_pool','media_ads')),
  target_partner_id INTEGER,
  verified_by_admin INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL
  ,
  FOREIGN KEY(target_partner_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS lead_assignments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  lead_id INTEGER NOT NULL,
  partner_id INTEGER NOT NULL,
  assigned_at TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'assigned',
  viewed_at TEXT,
  contacted_at TEXT,
  FOREIGN KEY(lead_id) REFERENCES leads(id),
  FOREIGN KEY(partner_id) REFERENCES users(id),
  UNIQUE(lead_id, partner_id)
);

CREATE TABLE IF NOT EXISTS lead_status_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  assignment_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  notes TEXT,
  created_at TEXT NOT NULL,
  FOREIGN KEY(assignment_id) REFERENCES lead_assignments(id)
);

CREATE TABLE IF NOT EXISTS invalid_reports (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  assignment_id INTEGER NOT NULL,
  partner_id INTEGER NOT NULL,
  reason TEXT NOT NULL,
  details TEXT,
  status TEXT NOT NULL CHECK(status IN ('pending','approved','rejected')),
  admin_notes TEXT,
  created_at TEXT NOT NULL,
  FOREIGN KEY(assignment_id) REFERENCES lead_assignments(id),
  FOREIGN KEY(partner_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS credit_adjustments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  partner_id INTEGER NOT NULL,
  amount INTEGER NOT NULL,
  reason TEXT NOT NULL,
  created_at TEXT NOT NULL,
  FOREIGN KEY(partner_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS campaigns (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  partner_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  city TEXT NOT NULL,
  start_date TEXT,
  end_date TEXT,
  status TEXT NOT NULL,
  FOREIGN KEY(partner_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS campaign_stats_daily (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  campaign_id INTEGER NOT NULL,
  date TEXT NOT NULL,
  impressions INTEGER NOT NULL DEFAULT 0,
  clicks INTEGER NOT NULL DEFAULT 0,
  leads INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY(campaign_id) REFERENCES campaigns(id)
);

CREATE TABLE IF NOT EXISTS tickets (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  partner_id INTEGER NOT NULL,
  subject TEXT NOT NULL,
  priority TEXT NOT NULL,
  status TEXT NOT NULL,
  created_at TEXT NOT NULL,
  FOREIGN KEY(partner_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS ticket_messages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  ticket_id INTEGER NOT NULL,
  sender_role TEXT NOT NULL CHECK(sender_role IN ('admin','partner')),
  message TEXT NOT NULL,
  attachments TEXT,
  created_at TEXT NOT NULL,
  FOREIGN KEY(ticket_id) REFERENCES tickets(id)
);

CREATE TABLE IF NOT EXISTS notifications (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  partner_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  message TEXT NOT NULL,
  is_read INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  FOREIGN KEY(partner_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS admin_profiles (
  user_id INTEGER PRIMARY KEY,
  admin_role TEXT NOT NULL DEFAULT 'super_admin',
  manager_user_id INTEGER,
  twofa_secret TEXT,
  twofa_enabled INTEGER NOT NULL DEFAULT 0,
  twofa_grace_used INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id),
  FOREIGN KEY(manager_user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS password_reset_requests (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  email TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','completed','rejected')),
  admin_id INTEGER,
  admin_notes TEXT,
  created_at TEXT NOT NULL,
  resolved_at TEXT,
  FOREIGN KEY(user_id) REFERENCES users(id),
  FOREIGN KEY(admin_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS admin_permissions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  module TEXT NOT NULL,
  can_view INTEGER NOT NULL DEFAULT 1,
  can_add INTEGER NOT NULL DEFAULT 0,
  can_edit INTEGER NOT NULL DEFAULT 0,
  can_delete INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  UNIQUE(user_id, module),
  FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS morning_meetings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  partner_id INTEGER NOT NULL,
  meeting_date TEXT NOT NULL,
  agenda TEXT NOT NULL,
  tasks TEXT,
  status TEXT NOT NULL DEFAULT 'planned',
  created_at TEXT NOT NULL,
  FOREIGN KEY(partner_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS diary_entries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  partner_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  event_date TEXT NOT NULL,
  event_time TEXT,
  details TEXT,
  created_at TEXT NOT NULL,
  FOREIGN KEY(partner_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS data_bank_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  partner_id INTEGER NOT NULL,
  category TEXT NOT NULL,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  created_at TEXT NOT NULL,
  FOREIGN KEY(partner_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS clients (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  partner_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  phone TEXT NOT NULL,
  email TEXT,
  city TEXT,
  notes TEXT,
  created_at TEXT NOT NULL,
  FOREIGN KEY(partner_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS deals (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  partner_id INTEGER NOT NULL,
  client_id INTEGER,
  assignment_id INTEGER,
  title TEXT NOT NULL,
  value_amount INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'negotiation',
  notes TEXT,
  close_date TEXT,
  created_at TEXT NOT NULL,
  FOREIGN KEY(partner_id) REFERENCES users(id),
  FOREIGN KEY(client_id) REFERENCES clients(id),
  FOREIGN KEY(assignment_id) REFERENCES lead_assignments(id)
);

CREATE TABLE IF NOT EXISTS landing_settings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  key TEXT NOT NULL UNIQUE,
  value TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS partner_badges (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  partner_name TEXT NOT NULL,
  badge_label TEXT NOT NULL,
  city TEXT NOT NULL,
  image_url TEXT,
  sort_order INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS enquiries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  enquiry_type TEXT NOT NULL CHECK(enquiry_type IN ('partner_application','buyer_enquiry')),
  full_name TEXT NOT NULL,
  phone TEXT NOT NULL,
  email TEXT,
  city TEXT,
  area TEXT,
  property_type TEXT,
  budget_min INTEGER,
  budget_max INTEGER,
  message TEXT,
  status TEXT NOT NULL DEFAULT 'new' CHECK(status IN ('new','contacted','qualified','closed','rejected')),
  admin_notes TEXT,
  email_sent INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS moving_enquiries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  enquiry_id INTEGER,
  lead_id INTEGER,
  from_postcode TEXT NOT NULL,
  to_postcode TEXT NOT NULL,
  move_date TEXT,
  service_type TEXT NOT NULL,
  property_type TEXT,
  bedrooms TEXT,
  move_size TEXT,
  floor_number TEXT,
  lift_access INTEGER NOT NULL DEFAULT 0,
  packing_needed INTEGER NOT NULL DEFAULT 0,
  dismantling_needed INTEGER NOT NULL DEFAULT 0,
  storage_needed INTEGER NOT NULL DEFAULT 0,
  job_type TEXT,
  vehicle_size TEXT,
  loading_help_needed INTEGER NOT NULL DEFAULT 0,
  parking_notes TEXT,
  permit_required INTEGER NOT NULL DEFAULT 0,
  waste_type TEXT,
  access_notes TEXT,
  special_items TEXT,
  budget_range TEXT,
  contact_name TEXT NOT NULL,
  contact_phone TEXT NOT NULL,
  contact_email TEXT,
  created_at TEXT NOT NULL,
  FOREIGN KEY(enquiry_id) REFERENCES enquiries(id),
  FOREIGN KEY(lead_id) REFERENCES leads(id)
);

CREATE TABLE IF NOT EXISTS move_requirements (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  lead_id INTEGER NOT NULL UNIQUE,
  from_postcode TEXT NOT NULL,
  to_postcode TEXT NOT NULL,
  move_date TEXT,
  service_type TEXT NOT NULL,
  property_type TEXT,
  bedrooms TEXT,
  move_size TEXT,
  floor_number TEXT,
  lift_access INTEGER NOT NULL DEFAULT 0,
  packing_needed INTEGER NOT NULL DEFAULT 0,
  dismantling_needed INTEGER NOT NULL DEFAULT 0,
  storage_needed INTEGER NOT NULL DEFAULT 0,
  job_type TEXT,
  vehicle_size TEXT,
  loading_help_needed INTEGER NOT NULL DEFAULT 0,
  parking_notes TEXT,
  permit_required INTEGER NOT NULL DEFAULT 0,
  waste_type TEXT,
  access_notes TEXT,
  special_items TEXT,
  budget_min INTEGER NOT NULL DEFAULT 0,
  budget_max INTEGER NOT NULL DEFAULT 0,
  is_commercial INTEGER NOT NULL DEFAULT 0,
  same_day_requested INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  FOREIGN KEY(lead_id) REFERENCES leads(id)
);

CREATE TABLE IF NOT EXISTS partner_service_categories (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  partner_id INTEGER NOT NULL,
  service_category TEXT NOT NULL,
  created_at TEXT NOT NULL,
  FOREIGN KEY(partner_id) REFERENCES users(id),
  UNIQUE(partner_id, service_category)
);

CREATE TABLE IF NOT EXISTS moving_lead_matches (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  lead_id INTEGER NOT NULL,
  partner_id INTEGER NOT NULL,
  match_score INTEGER NOT NULL DEFAULT 0,
  reason_summary TEXT,
  status TEXT NOT NULL DEFAULT 'suggested',
  created_at TEXT NOT NULL,
  FOREIGN KEY(lead_id) REFERENCES leads(id),
  FOREIGN KEY(partner_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS service_areas (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  partner_id INTEGER NOT NULL,
  postcode_prefix TEXT NOT NULL,
  area_label TEXT,
  created_at TEXT NOT NULL,
  FOREIGN KEY(partner_id) REFERENCES users(id),
  UNIQUE(partner_id, postcode_prefix)
);

CREATE TABLE IF NOT EXISTS media_subscriptions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  partner_id INTEGER NOT NULL,
  package_name TEXT NOT NULL,
  start_date TEXT NOT NULL,
  end_date TEXT NOT NULL,
  status TEXT NOT NULL CHECK(status IN ('active','paused','expired','cancelled')),
  budget_total REAL NOT NULL DEFAULT 0,
  budget_used REAL NOT NULL DEFAULT 0,
  budget_remaining REAL NOT NULL DEFAULT 0,
  rate_per_1000 REAL NOT NULL DEFAULT 50,
  assigned_by_admin_id INTEGER,
  approval_status TEXT NOT NULL DEFAULT 'approved',
  payment_proof_path TEXT,
  approved_by_admin_id INTEGER,
  approved_at TEXT,
  created_at TEXT NOT NULL,
  FOREIGN KEY(partner_id) REFERENCES users(id),
  FOREIGN KEY(assigned_by_admin_id) REFERENCES users(id),
  FOREIGN KEY(approved_by_admin_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS admin_login_challenges (
  token TEXT PRIMARY KEY,
  user_id INTEGER NOT NULL,
  expires_at TEXT NOT NULL,
  created_at TEXT NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS admin_todos (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  task_text TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  created_at TEXT NOT NULL,
  completed_at TEXT,
  FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS media_performance_daily (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  partner_id INTEGER NOT NULL,
  media_subscription_id INTEGER NOT NULL,
  ad_date TEXT NOT NULL,
  platform TEXT NOT NULL,
  impressions INTEGER NOT NULL DEFAULT 0,
  clicks INTEGER NOT NULL DEFAULT 0,
  views INTEGER NOT NULL DEFAULT 0,
  leads INTEGER NOT NULL DEFAULT 0,
  charged_amount REAL NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  FOREIGN KEY(partner_id) REFERENCES users(id),
  FOREIGN KEY(media_subscription_id) REFERENCES media_subscriptions(id)
);