-- Communication Plugin Database Schema
-- This schema is isolated to the plugin_communication namespace

-- Create plugin schema
CREATE SCHEMA IF NOT EXISTS plugin_communication;

-- Messages table
CREATE TABLE IF NOT EXISTS plugin_communication.messages (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  site_id UUID REFERENCES sites(id) ON DELETE CASCADE,
  sender_id UUID REFERENCES users(id),
  recipient VARCHAR(255) NOT NULL,
  subject VARCHAR(255),
  content TEXT,
  type VARCHAR(20) DEFAULT 'email', -- email, sms, push
  status VARCHAR(20) DEFAULT 'pending', -- pending, sent, failed, scheduled
  scheduled_at TIMESTAMP WITH TIME ZONE,
  sent_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Notifications table
CREATE TABLE IF NOT EXISTS plugin_communication.notifications (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  site_id UUID REFERENCES sites(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id),
  title VARCHAR(255) NOT NULL,
  message TEXT,
  type VARCHAR(20) DEFAULT 'info', -- info, warning, error, success
  is_read BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Templates table
CREATE TABLE IF NOT EXISTS plugin_communication.templates (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  site_id UUID REFERENCES sites(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  subject VARCHAR(255),
  content TEXT,
  type VARCHAR(20) DEFAULT 'email', -- email, sms
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Scheduled messages table
CREATE TABLE IF NOT EXISTS plugin_communication.scheduled_messages (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  site_id UUID REFERENCES sites(id) ON DELETE CASCADE,
  message_id UUID REFERENCES plugin_communication.messages(id) ON DELETE CASCADE,
  scheduled_at TIMESTAMP WITH TIME ZONE NOT NULL,
  status VARCHAR(20) DEFAULT 'pending', -- pending, sent, cancelled
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_plugin_communication_messages_site_id 
ON plugin_communication.messages(site_id);

CREATE INDEX IF NOT EXISTS idx_plugin_communication_messages_status 
ON plugin_communication.messages(status);

CREATE INDEX IF NOT EXISTS idx_plugin_communication_messages_created_at 
ON plugin_communication.messages(created_at);

CREATE INDEX IF NOT EXISTS idx_plugin_communication_notifications_site_id 
ON plugin_communication.notifications(site_id);

CREATE INDEX IF NOT EXISTS idx_plugin_communication_notifications_user_id 
ON plugin_communication.notifications(user_id);

CREATE INDEX IF NOT EXISTS idx_plugin_communication_notifications_is_read 
ON plugin_communication.notifications(is_read);

CREATE INDEX IF NOT EXISTS idx_plugin_communication_templates_site_id 
ON plugin_communication.templates(site_id);

CREATE INDEX IF NOT EXISTS idx_plugin_communication_templates_type 
ON plugin_communication.templates(type);

CREATE INDEX IF NOT EXISTS idx_plugin_communication_scheduled_messages_scheduled_at 
ON plugin_communication.scheduled_messages(scheduled_at);

CREATE INDEX IF NOT EXISTS idx_plugin_communication_scheduled_messages_status 
ON plugin_communication.scheduled_messages(status);