-- Americas Care Database Schema
CREATE DATABASE IF NOT EXISTS americas_care
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
USE americas_care;

-- User Profiles (addresses, bio)
CREATE TABLE IF NOT EXISTS user_profiles (
  user_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  address_line1 VARCHAR(191) NULL,
  address_line2 VARCHAR(191) NULL,
  city VARCHAR(100) NULL,
  state VARCHAR(100) NULL,
  postal_code VARCHAR(20) NULL,
  country VARCHAR(2) NULL,
  bio TEXT NULL,
  date_of_birth DATE NULL,
  gender ENUM('male','female','other','prefer_not_to_say') NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_user_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Caregiver specific profile fields
CREATE TABLE IF NOT EXISTS caregiver_profiles (
  user_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  experience_years TINYINT UNSIGNED NOT NULL DEFAULT 0,
  hourly_rate_cents INT UNSIGNED NULL,
  availability VARCHAR(255) NULL, -- e.g. "Full-time, Weekends"
  certifications TEXT NULL,       -- CSV or text; can be normalized later
  verified_background_check TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_caregiver_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Categories and Services
CREATE TABLE IF NOT EXISTS categories (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(100) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS services (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  category_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  description VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_services_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
  INDEX idx_services_category (category_id)
) ENGINE=InnoDB;

-- Caregiver services (many-to-many)
CREATE TABLE IF NOT EXISTS caregiver_services (
  user_id BIGINT UNSIGNED NOT NULL,
  service_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, service_id),
  CONSTRAINT fk_caregiver_services_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_caregiver_services_service FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Jobs posted by seekers
CREATE TABLE IF NOT EXISTS jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  seeker_user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(191) NOT NULL,
  description TEXT NOT NULL,
  category_id INT UNSIGNED NULL,
  service_id INT UNSIGNED NULL,
  city VARCHAR(100) NULL,
  state VARCHAR(100) NULL,
  postal_code VARCHAR(20) NULL,
  schedule_type ENUM('fulltime','parttime','nights','weekends','flexible') NULL,
  hourly_rate_cents INT UNSIGNED NULL,
  image_url VARCHAR(255) NULL,
  status ENUM('open','paused','closed') NOT NULL DEFAULT 'open',
  posted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_jobs_seeker FOREIGN KEY (seeker_user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_jobs_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  CONSTRAINT fk_jobs_service FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE SET NULL,
  INDEX idx_jobs_status (status),
  INDEX idx_jobs_location (state, city, postal_code)
) ENGINE=InnoDB;

-- Job applications
CREATE TABLE IF NOT EXISTS job_applications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  job_id BIGINT UNSIGNED NOT NULL,
  caregiver_user_id BIGINT UNSIGNED NOT NULL,
  cover_letter TEXT NULL,
  status ENUM('applied','shortlisted','rejected','hired','withdrawn') NOT NULL DEFAULT 'applied',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_job_applications_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
  CONSTRAINT fk_job_applications_caregiver FOREIGN KEY (caregiver_user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_job_applications (job_id, caregiver_user_id),
  INDEX idx_job_applications_status (status)
) ENGINE=InnoDB;

-- Conversations and messages
CREATE TABLE IF NOT EXISTS conversations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  subject VARCHAR(191) NULL,
  created_by_user_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_conversations_creator FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS conversation_participants (
  conversation_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  last_read_at DATETIME NULL,
  PRIMARY KEY (conversation_id, user_id),
  CONSTRAINT fk_conv_participants_conversation FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
  CONSTRAINT fk_conv_participants_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  conversation_id BIGINT UNSIGNED NOT NULL,
  sender_user_id BIGINT UNSIGNED NOT NULL,
  body TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  read_at DATETIME NULL,
  CONSTRAINT fk_messages_conversation FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
  CONSTRAINT fk_messages_sender FOREIGN KEY (sender_user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_messages_conversation (conversation_id),
  INDEX idx_messages_sender (sender_user_id)
) ENGINE=InnoDB;

-- Background checks
CREATE TABLE IF NOT EXISTS background_checks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  requester_user_id BIGINT UNSIGNED NOT NULL, -- the seeker requesting
  caregiver_user_id BIGINT UNSIGNED NOT NULL,
  type ENUM('basic','standard','comprehensive') NOT NULL DEFAULT 'basic',
  vendor VARCHAR(100) NULL,       -- e.g. 'Checkr', 'Certn'
  price_cents INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('initiated','paid','processing','completed','failed','canceled') NOT NULL DEFAULT 'initiated',
  report_url VARCHAR(255) NULL,
  initiated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  payment_id BIGINT UNSIGNED NULL, -- link to payments
  CONSTRAINT fk_bg_checks_requester FOREIGN KEY (requester_user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_bg_checks_caregiver FOREIGN KEY (caregiver_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Plans (e.g., HomePay)
CREATE TABLE IF NOT EXISTS plans (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE,
  amount_cents INT UNSIGNED NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  `interval` ENUM('day','week','month','year') NOT NULL DEFAULT 'month',
  description VARCHAR(255) NULL,
  features JSON NULL,
  stripe_price_id VARCHAR(191) NULL, -- link to Stripe Price if configured
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Subscriptions
CREATE TABLE IF NOT EXISTS subscriptions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  plan_id BIGINT UNSIGNED NOT NULL,
  stripe_customer_id VARCHAR(191) NULL,
  stripe_subscription_id VARCHAR(191) NULL,
  status ENUM('active','past_due','canceled','incomplete','incomplete_expired','trialing','unpaid') NOT NULL DEFAULT 'active',
  current_period_start DATETIME NULL,
  current_period_end DATETIME NULL,
  canceled_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_subscriptions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_subscriptions_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE RESTRICT,
  INDEX idx_subscriptions_status (status)
) ENGINE=InnoDB;

-- Payments (one-off and invoice payments)
CREATE TABLE IF NOT EXISTS payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL, -- who paid
  provider ENUM('stripe') NOT NULL DEFAULT 'stripe',
  provider_payment_id VARCHAR(191) NOT NULL, -- PaymentIntent ID or Charge/Invoice ID
  amount_cents INT UNSIGNED NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  purpose ENUM('background_check','job_posting','subscription','other') NOT NULL,
  status ENUM('requires_payment_method','requires_confirmation','processing','succeeded','requires_action','canceled','failed','refunded') NOT NULL,
  related_id BIGINT UNSIGNED NULL, -- link to background_checks.id or subscriptions.id, depending on purpose
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_payments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_payments_provider_id (provider_payment_id),
  INDEX idx_payments_purpose (purpose),
  INDEX idx_payments_status (status)
) ENGINE=InnoDB;

-- Payroll entries (basic)
CREATE TABLE IF NOT EXISTS payroll_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  caregiver_user_id BIGINT UNSIGNED NOT NULL,
  seeker_user_id BIGINT UNSIGNED NOT NULL,
  start_time DATETIME NOT NULL,
  end_time DATETIME NOT NULL,
  hours_worked DECIMAL(5,2) NOT NULL,
  hourly_rate_cents INT UNSIGNED NOT NULL,
  gross_pay_cents INT UNSIGNED NOT NULL,
  taxes_cents INT UNSIGNED NOT NULL DEFAULT 0,
  net_pay_cents INT UNSIGNED NOT NULL,
  status ENUM('draft','approved','paid','void') NOT NULL DEFAULT 'draft',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_payroll_caregiver FOREIGN KEY (caregiver_user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_payroll_seeker FOREIGN KEY (seeker_user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_payroll_status (status)
) ENGINE=InnoDB;

-- Notifications
CREATE TABLE IF NOT EXISTS notifications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  type VARCHAR(100) NOT NULL,
  data JSON NULL,
  read_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_notifications_unread (user_id, read_at)
) ENGINE=InnoDB;

-- Reviews
CREATE TABLE IF NOT EXISTS reviews (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  reviewer_user_id BIGINT UNSIGNED NOT NULL,
  reviewee_user_id BIGINT UNSIGNED NOT NULL,
  rating TINYINT UNSIGNED NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_reviews_reviewer FOREIGN KEY (reviewer_user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_reviews_reviewee FOREIGN KEY (reviewee_user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_reviews_reviewee (reviewee_user_id)
) ENGINE=InnoDB;

-- Saved searches / groups (for cs-search-groups)
CREATE TABLE IF NOT EXISTS search_groups (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  filters JSON NULL, -- e.g. {"care_type":"senior","postal_code":"32135","distance":10}
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_search_groups_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Optional: stripe event logs (for debugging)
CREATE TABLE IF NOT EXISTS stripe_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  event_id VARCHAR(191) NOT NULL UNIQUE,
  type VARCHAR(191) NOT NULL,
  payload JSON NOT NULL,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Seed data: categories
INSERT INTO categories (slug, name, description) VALUES
  ('childcare', 'Childcare', 'Babysitters, nannies, newborn care'),
  ('special-needs', 'Special needs', 'Special needs care and assistance'),
  ('tutoring', 'Extra lesson', 'Tutoring and after-school help'),
  ('elderly-care', 'Elderly care', 'Senior/companion care'),
  ('pet-care', 'Pet care', 'Dog walking, pet sitting, grooming'),
  ('housekeeping', 'Housekeeping', 'Cleaning, organizing, maintenance')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Seed data: services (sample)
INSERT INTO services (category_id, name) VALUES
  ((SELECT id FROM categories WHERE slug='childcare'), 'Babysitter'),
  ((SELECT id FROM categories WHERE slug='childcare'), 'Nanny'),
  ((SELECT id FROM categories WHERE slug='childcare'), 'After-school care'),
  ((SELECT id FROM categories WHERE slug='tutoring'), 'Tutor'),
  ((SELECT id FROM categories WHERE slug='special-needs'), 'Special needs care'),
  ((SELECT id FROM categories WHERE slug='elderly-care'), 'Companion care'),
  ((SELECT id FROM categories WHERE slug='elderly-care'), 'Personal care assistant'),
  ((SELECT id FROM categories WHERE slug='elderly-care'), 'Meal preparation'),
  ((SELECT id FROM categories WHERE slug='pet-care'), 'Dog walking'),
  ((SELECT id FROM categories WHERE slug='pet-care'), 'Pet sitting'),
  ((SELECT id FROM categories WHERE slug='housekeeping'), 'House cleaning'),
  ((SELECT id FROM categories WHERE slug='housekeeping'), 'Laundry services');

-- Seed data: plan (HomePay @ $59/mo)
INSERT INTO plans (name, slug, amount_cents, currency, `interval`, description, features)
VALUES ('HomePay', 'homepay', 5900, 'USD', 'month','Bundle and save with HomePay monthly plan', JSON_ARRAY('Easy payroll processing','Automatic tax filing','Access to experts'))
ON DUPLICATE KEY UPDATE amount_cents = VALUES(amount_cents);

ALTER TABLE jobs ADD COLUMN image_url VARCHAR(255) NULL AFTER hourly_rate_cents;