Supabase Database Integration

-- Table: users
CREATE TABLE public.users (
  id UUID NOT NULL PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  avatar_url TEXT NULL,
  billing_address JSONB NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  email TEXT NULL,
  first_name TEXT NULL,
  full_name TEXT NULL,
  last_name TEXT NULL,
  last_sign_in_at TIMESTAMPTZ NULL,
  payment_method JSONB NULL,
  provider TEXT NULL
);

COMMENT ON TABLE public.users IS 'Profile information for users.';
COMMENT ON COLUMN public.users.id IS 'References the internal Supabase auth user.';
-- RLS policies for users are typically managed on auth.users and user profile data can be restricted.
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own profile" ON public.users FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users can update their own profile" ON public.users FOR UPDATE USING (auth.uid() = id);

-- Function and Trigger to create a user profile entry on new auth.users sign up
CREATE FUNCTION public.handle_new_user() 
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.users (id, email, full_name, avatar_url, provider)
  VALUES (
    NEW.id,
    NEW.email,
    NEW.raw_user_meta_data->>'full_name',
    NEW.raw_user_meta_data->>'avatar_url',
    NEW.raw_user_meta_data->>'provider'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- Table: accounts
CREATE TABLE public.accounts (
  id UUID NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  username TEXT NOT NULL,
  name TEXT NULL,
  of_id TEXT NULL,
  status TEXT NOT NULL DEFAULT 'pending'::text,
  sess TEXT NULL,
  user_agent TEXT NULL,
  cookies JSONB NULL,
  local_storage JSONB NULL,
  web_cookies JSONB NULL,
  web_local_storage JSONB NULL,
  web_session_storage JSONB NULL,
  auth_data_valid BOOLEAN NULL,
  auth_validation_details JSONB NULL,
  bc_token_sha TEXT NULL,
  active_fans INTEGER NULL,
  expired_fans INTEGER NULL,
  proxy_id TEXT NULL,
  proxy_address TEXT NULL,
  proxy_port INTEGER NULL,
  proxy_username TEXT NULL,
  proxy_password TEXT NULL, -- Ensure this is stored securely if not using a vault
  proxy_country_code TEXT NULL,
  proxy_city_name TEXT NULL,
  proxy_mode TEXT NULL, -- e.g., 'auto', 'manual', 'none'
  proxy_is_active BOOLEAN NULL,
  proxy_region_preference TEXT NULL,
  proxy_assigned_at TIMESTAMPTZ NULL,
  sync_started BOOLEAN NOT NULL DEFAULT FALSE,
  last_login_attempt TIMESTAMPTZ NULL,
  last_login_status TEXT NULL,
  last_scraped_at TIMESTAMPTZ NULL,
  last_sync_at TIMESTAMPTZ NULL,
  last_validation_at TIMESTAMPTZ NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NULL
);

COMMENT ON TABLE public.accounts IS 'Stores OnlyFans creator accounts linked to users, including operational data like proxy and session info.';
COMMENT ON COLUMN public.accounts.of_id IS 'The OnlyFans specific user ID for the account.';
COMMENT ON COLUMN public.accounts.status IS 'Operational status of the account (e.g., active, error, pending_auth).';
COMMENT ON COLUMN public.accounts.sess IS 'Session token for OnlyFans.';
COMMENT ON COLUMN public.accounts.proxy_mode IS 'Proxy configuration mode: auto, manual, or none.';
COMMENT ON COLUMN public.accounts.proxy_password IS 'Proxy password - ensure encryption or secure vault storage.';

-- Row Level Security for accounts
ALTER TABLE public.accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can manage their own accounts" ON public.accounts
  FOR ALL USING (auth.uid() = user_id);

-- Index for faster lookups
CREATE INDEX idx_accounts_user_id ON public.accounts(user_id);
CREATE INDEX idx_accounts_of_id ON public.accounts(of_id);
CREATE INDEX idx_accounts_status ON public.accounts(status);

CREATE TABLE customers (
  id UUID REFERENCES users(id) PRIMARY KEY,
  stripe_customer_id TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL
);

-- Row Level Security for customers
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own customer data" ON customers
  FOR SELECT USING (auth.uid() = id);

CREATE TABLE fans (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  account_id UUID REFERENCES accounts(id) ON DELETE CASCADE NOT NULL,
  username TEXT NOT NULL,
  name TEXT,
  avatar_url TEXT,
  header_url TEXT,
  is_favorite BOOLEAN DEFAULT FALSE,
  last_active TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  UNIQUE(account_id, username)
);

-- Row Level Security for fans
ALTER TABLE fans ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view fans through accounts" ON fans
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM accounts 
      WHERE accounts.id = fans.account_id 
      AND accounts.user_id = auth.uid()
    )
  );

CREATE TABLE fans_service (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  account_id UUID REFERENCES accounts(id) ON DELETE CASCADE NOT NULL,
  fan_id UUID REFERENCES fans(id) ON DELETE CASCADE,
  fan_username TEXT NOT NULL,
  subscription_tier TEXT,
  subscription_price DECIMAL(10, 2),
  is_active BOOLEAN DEFAULT TRUE,
  status TEXT, 
  first_subscription_date TIMESTAMP WITH TIME ZONE,
  last_subscription_date TIMESTAMP WITH TIME ZONE,
  next_billing_date TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  UNIQUE(account_id, fan_id)
);

-- Row Level Security for fans_service
ALTER TABLE fans_service ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view fans_service through accounts" ON fans_service
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM accounts 
      WHERE accounts.id = fans_service.account_id 
      AND accounts.user_id = auth.uid()
    )
  );

CREATE TABLE fans_statistics_service (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  account_id UUID REFERENCES accounts(id) ON DELETE CASCADE NOT NULL,
  fan_id UUID REFERENCES fans(id) ON DELETE CASCADE,
  fan_username TEXT NOT NULL,
  current_spend DECIMAL(10, 2) DEFAULT 0,
  lifetime_spend DECIMAL(10, 2) DEFAULT 0,
  tips_amount DECIMAL(10, 2) DEFAULT 0,
  tips_count INTEGER DEFAULT 0,
  paid_posts_amount DECIMAL(10, 2) DEFAULT 0,
  paid_posts_count INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  UNIQUE(account_id, fan_id)
);

-- Row Level Security for fans_statistics_service
ALTER TABLE fans_statistics_service ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view fans_statistics_service through accounts" ON fans_statistics_service
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM accounts 
      WHERE accounts.id = fans_statistics_service.account_id 
      AND accounts.user_id = auth.uid()
    )
  );

CREATE TABLE fans_message_statistics_service (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  account_id UUID REFERENCES accounts(id) ON DELETE CASCADE NOT NULL,
  fan_id UUID REFERENCES fans(id) ON DELETE CASCADE,
  fan_username TEXT NOT NULL,
  messages_received INTEGER DEFAULT 0,
  messages_sent INTEGER DEFAULT 0,
  last_message_date TIMESTAMP WITH TIME ZONE,
  response_rate DECIMAL(5, 2) DEFAULT 0,
  average_response_time INTEGER, -- in minutes
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  UNIQUE(account_id, fan_id)
);

-- Row Level Security for fans_message_statistics_service
ALTER TABLE fans_message_statistics_service ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view fans_message_statistics_service through accounts" ON fans_message_statistics_service
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM accounts 
      WHERE accounts.id = fans_message_statistics_service.account_id 
      AND accounts.user_id = auth.uid()
    )
  );

CREATE TABLE products (
  id TEXT PRIMARY KEY,
  active BOOLEAN,
  name TEXT,
  description TEXT,
  image TEXT,
  metadata JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL
);

-- Allow public read access to products
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow public read access to products" ON products
  FOR SELECT USING (true);

CREATE TABLE prices (
  id TEXT PRIMARY KEY,
  product_id TEXT REFERENCES products(id),
  active BOOLEAN,
  description TEXT,
  unit_amount INTEGER,
  currency TEXT,
  type TEXT,
  interval TEXT,
  interval_count INTEGER,
  trial_period_days INTEGER,
  metadata JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL
);

-- Allow public read access to prices
ALTER TABLE prices ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow public read access to prices" ON prices
  FOR SELECT USING (true);

CREATE TABLE subscriptions (
  id TEXT PRIMARY KEY,
  user_id UUID REFERENCES users(id) NOT NULL,
  status TEXT,
  metadata JSONB,
  price_id TEXT REFERENCES prices(id),
  quantity INTEGER,
  cancel_at_period_end BOOLEAN,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  current_period_start TIMESTAMP WITH TIME ZONE,
  current_period_end TIMESTAMP WITH TIME ZONE,
  ended_at TIMESTAMP WITH TIME ZONE,
  cancel_at TIMESTAMP WITH TIME ZONE,
  canceled_at TIMESTAMP WITH TIME ZONE,
  trial_start TIMESTAMP WITH TIME ZONE,
  trial_end TIMESTAMP WITH TIME ZONE
);

-- Row Level Security for subscriptions
ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own subscriptions" ON subscriptions
  FOR SELECT USING (auth.uid() = user_id);

CREATE TABLE folders (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL
);

-- Row Level Security for folders
ALTER TABLE folders ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own folders" ON folders
  FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can create their own folders" ON folders
  FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own folders" ON folders
  FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own folders" ON folders
  FOR DELETE USING (auth.uid() = user_id);

CREATE TABLE scripts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  folder_id UUID REFERENCES folders(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  content TEXT NOT NULL,
  is_template BOOLEAN DEFAULT FALSE,
  variables JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL
);

-- Row Level Security for scripts
ALTER TABLE scripts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own scripts" ON scripts
  FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can create their own scripts" ON scripts
  FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own scripts" ON scripts
  FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own scripts" ON scripts
  FOR DELETE USING (auth.uid() = user_id);

CREATE TABLE tags (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  color TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  UNIQUE(user_id, name)
);

-- Row Level Security for tags
ALTER TABLE tags ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own tags" ON tags
  FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can create their own tags" ON tags
  FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own tags" ON tags
  FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own tags" ON tags
  FOR DELETE USING (auth.uid() = user_id);

CREATE TABLE emojis (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  symbol TEXT NOT NULL,
  category TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL
);

-- Row Level Security for emojis
ALTER TABLE emojis ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own emojis" ON emojis
  FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can create their own emojis" ON emojis
  FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own emojis" ON emojis
  FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own emojis" ON emojis
  FOR DELETE USING (auth.uid() = user_id);

CREATE TABLE email_tracking (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  email_id TEXT NOT NULL,
  campaign_id TEXT,
  sent_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  opened_at TIMESTAMP WITH TIME ZONE,
  clicked_at TIMESTAMP WITH TIME ZONE,
  click_count INTEGER DEFAULT 0,
  links_clicked JSONB,
  device_info JSONB,
  is_bounced BOOLEAN DEFAULT FALSE,
  bounce_reason TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Row Level Security for email_tracking
ALTER TABLE email_tracking ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own email tracking data" ON email_tracking
  FOR SELECT USING (auth.uid() = user_id);

import { createClient } from '@supabase/supabase-js';
import { Database } from './types_db';

// Create a single supabase client for interacting with your database
const supabase = createClient<Database>(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);

// Example query: get user accounts
async function getUserAccounts(userId: string) {
    const { data, error } = await supabase
      .from('accounts')
      .select('*')
      .eq('user_id', userId);
      
  if (error) {
    console.error('Error fetching accounts:', error);
    return null;
  }
  
  return data;
}

// Subscribe to changes in the accounts table for a specific user
const accountsSubscription = supabase
  .channel('table-db-changes')
  .on(
    'postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'accounts',
      filter: `user_id=eq.${user.id}`
    },
    (payload) => {
      console.log('Change received!', payload);
      // Update UI or state based on the change
      updateAccounts(payload.new);
    }
  )
  .subscribe();

// Don't forget to unsubscribe when component unmounts
useEffect(() => {
  return () => {
    supabase.removeChannel(accountsSubscription);
  };
}, []);