-- Use the prosale database
USE prosale;

-- Create FCM device tokens table
CREATE TABLE IF NOT EXISTS fcm_device_tokens (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  token VARCHAR(500) NOT NULL UNIQUE,
  platform ENUM('web', 'android', 'ios') DEFAULT 'web',
  is_active BOOLEAN DEFAULT TRUE,
  last_used TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user_id (user_id),
  INDEX idx_token (token),
  INDEX idx_created_at (created_at)
);

-- Create notifications table (if not exists)
CREATE TABLE IF NOT EXISTS notifications (
  id INT PRIMARY KEY AUTO_INCREMENT,
  type VARCHAR(50) DEFAULT 'fcm',
  user_id INT NOT NULL,
  lead_id INT,
  title VARCHAR(255) NOT NULL,
  body TEXT,
  status ENUM('sent', 'read', 'failed') DEFAULT 'sent',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user_id (user_id),
  INDEX idx_lead_id (lead_id),
  INDEX idx_created_at (created_at)
);

-- Create lead subscriptions table (if not exists)
CREATE TABLE IF NOT EXISTS lead_subscription (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  lead_id INT NOT NULL,
  subscribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_id (user_id),
  UNIQUE KEY unique_subscription (user_id, lead_id)
);

-- Verify tables were created
SELECT COUNT(*) as tables_created FROM information_schema.tables 
WHERE table_schema = 'prosale' AND table_name IN ('fcm_device_tokens', 'notifications', 'lead_subscription');
