دليل الاستخدام الشامل

/*
                      # Create email queue system
                    
                      1. New Tables
                        - `email_queue`
                          - `id` (uuid, primary key) - unique identifier for each batch
                          - `job_id` (text, not null) - groups batches belonging to the same send operation
                          - `client_token` (text, not null) - identifies the client/browser session
                          - `recipients` (text[], not null) - email addresses for this batch
                          - `subject` (text, not null) - email subject line
                          - `body` (text) - plain text email body
                          - `html` (text) - HTML email body
                          - `send_type` (text, default 'bcc') - 'bcc' or 'to' sending mode
                          - `smtp_config` (jsonb, not null) - SMTP connection settings, cleared after processing
                          - `status` (text, default 'pending') - pending, processing, completed, failed, cancelled
                          - `error_message` (text) - error details if batch failed
                          - `batch_index` (int, default 0) - order of this batch within the job
                          - `total_batches` (int, default 1) - total number of batches in the job
                          - `created_at` (timestamptz) - when the batch was created
                          - `updated_at` (timestamptz) - last update time
                          - `processed_at` (timestamptz) - when processing completed
                    
                      2. Security
                        - Enable RLS on `email_queue` table
                        - No anon/authenticated policies: all access goes through Edge Functions using service_role_key
                        - SMTP credentials are stored temporarily and cleared after processing
                    
                      3. Indexes
                        - Index on (job_id, client_token) for fast job lookups
                        - Index on (status, created_at) for queue processing order
                    
                      4. Important Notes
                        - SMTP config is stored as JSONB and cleared (set to '{}') after each batch is processed
                        - The table is fully locked down via RLS with no policies, ensuring only service_role can access
                        - This protects sensitive SMTP credentials from being read via the anon key
                    */
                    
                    CREATE TABLE IF NOT EXISTS email_queue (
                      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
                      job_id text NOT NULL,
                      client_token text NOT NULL,
                      recipients text[] NOT NULL DEFAULT '{}',
                      subject text NOT NULL,
                      body text,
                      html text,
                      send_type text NOT NULL DEFAULT 'bcc',
                      smtp_config jsonb NOT NULL DEFAULT '{}',
                      status text NOT NULL DEFAULT 'pending',
                      error_message text,
                      batch_index int NOT NULL DEFAULT 0,
                      total_batches int NOT NULL DEFAULT 1,
                      created_at timestamptz DEFAULT now(),
                      updated_at timestamptz DEFAULT now(),
                      processed_at timestamptz
                    );
                    
                    ALTER TABLE email_queue ENABLE ROW LEVEL SECURITY;
                    
                    CREATE INDEX IF NOT EXISTS idx_email_queue_job_client
                      ON email_queue (job_id, client_token);
                    
                    CREATE INDEX IF NOT EXISTS idx_email_queue_status_created
                      ON email_queue (status, created_at);
                    
                      DO $$
                    BEGIN
                      IF NOT EXISTS (
                        SELECT 1 FROM information_schema.columns
                        WHERE table_name = 'email_queue' AND column_name = 'personalize'
                      ) THEN
                        ALTER TABLE email_queue ADD COLUMN personalize jsonb DEFAULT '{}';
                      END IF;
                    END $$;