Social Media Post Scheduler & Analytics

You are a senior full-stack engineer proficient in Next.js 16, Tailwind CSS 4, and Supabase. Help me build a complete Social Media Post Scheduler and Analytics Platform. The brand name is {brand_name}, serving the {industry} industry, targeting {platforms}, with a team of {team_size} people, and brand primary color {primary_color}.

## Project Overview

This is a social media management tool for Malaysian SMEs and marketing agencies, replacing Buffer and Hootsuite. The system must support bilingual content creation (Chinese/English) and consider Malaysian social media usage habits and local holiday content planning.

## Tech Stack

- **Framework**: Next.js 16 (App Router) + TypeScript (strict mode)
- **Styling**: Tailwind CSS 4 + shadcn/ui component library
- **Backend**: Supabase (PostgreSQL + Auth + Storage + Edge Functions)
- **Calendar**: date-fns with a calendar UI component (react-day-picker or @fullcalendar/react)
- **AI Integration**: OpenAI API (for caption generation)
- **Deployment**: Vercel

## Database Design (Supabase PostgreSQL)

Create the following table structures. All tables must have RLS policies enabled:

### profiles table
- id (uuid, references auth.users)
- full_name (text)
- avatar_url (text)
- role (enum: owner, manager, editor, viewer)
- team_id (uuid)
- created_at, updated_at (timestamptz)

### teams table
- id (uuid, primary key)
- name (text)
- brand_name (text, default {brand_name})
- industry (text, default {industry})
- primary_color (text, default {primary_color})
- plan (enum: free, pro, enterprise)
- created_at (timestamptz)

### social_accounts table
- id (uuid, primary key)
- team_id (uuid, FK to teams, must create index)
- platform (enum: facebook, instagram, linkedin, tiktok)
- account_name (text)
- account_id (text)
- access_token (text, encrypted storage)
- is_connected (boolean)
- created_at (timestamptz)

### posts table
- id (uuid, primary key)
- team_id (uuid, FK, create index)
- author_id (uuid, FK to profiles)
- title (text)
- caption_zh (text, Chinese caption)
- caption_en (text, English caption)
- media_urls (text[], media file paths array)
- platforms (text[], target platforms)
- status (enum: draft, pending_review, approved, scheduled, published, failed)
- scheduled_at (timestamptz)
- published_at (timestamptz)
- is_recurring (boolean, default false)
- recurrence_rule (jsonb, e.g. {frequency: weekly, interval: 1, end_date: ...})
- template_id (uuid, optional FK to templates)
- category_id (uuid, optional FK to content categories)
- hashtag_set_id (uuid, optional FK to hashtag sets)
- created_at, updated_at (timestamptz)
- Create GIN index for full-text search on caption_zh and caption_en

### post_analytics table
- id (uuid, primary key)
- post_id (uuid, FK to posts, create index)
- platform (enum: facebook, instagram, linkedin, tiktok)
- impressions (integer, default 0)
- reach (integer, default 0)
- likes (integer, default 0)
- comments (integer, default 0)
- shares (integer, default 0)
- saves (integer, default 0)
- clicks (integer, default 0)
- engagement_rate (numeric(5,2))
- recorded_at (timestamptz)

### content_templates table
- id (uuid, primary key)
- team_id (uuid, FK, create index)
- name (text)
- description (text)
- caption_template_zh (text, with variable placeholders)
- caption_template_en (text)
- platforms (text[])
- category_id (uuid)
- created_at (timestamptz)

### content_categories table
- id (uuid, primary key)
- team_id (uuid, FK, create index)
- name_zh (text)
- name_en (text)
- color (text, for calendar display)
- icon (text)
- sort_order (integer)

### hashtag_sets table
- id (uuid, primary key)
- team_id (uuid, FK, create index)
- name (text)
- hashtags (text[], hashtag array)
- platform (text, optional platform filter)
- usage_count (integer, default 0)
- created_at (timestamptz)

### media_library table
- id (uuid, primary key)
- team_id (uuid, FK, create index)
- file_name (text)
- file_path (text, Supabase Storage path)
- file_type (enum: image, video, gif)
- file_size (bigint, bytes)
- width (integer)
- height (integer)
- thumbnail_url (text)
- tags (text[])
- uploaded_by (uuid, FK to profiles)
- created_at (timestamptz)

### approval_logs table
- id (uuid, primary key)
- post_id (uuid, FK to posts, create index)
- action (enum: submitted, approved, rejected, revision_requested)
- actor_id (uuid, FK to profiles)
- comment (text)
- created_at (timestamptz)

### RLS Policy Requirements
- All queries must use (select auth.uid()) instead of auth.uid() directly (performance optimization)
- Team members can only access their own team data
- viewer role: SELECT only
- editor: INSERT and UPDATE posts (status limited to draft)
- manager: can approve posts (change status to approved)
- owner: full permissions

## Page Structure and Features

### 1. Layout (src/app/layout.tsx)
- Responsive sidebar navigation (expanded on desktop, drawer on mobile)
- Nav items: Dashboard, Content Calendar, Create Post, Media Library, Templates, Hashtag Manager, Analytics, Team Settings
- Top bar: Brand logo ({brand_name}), notification bell, user avatar dropdown
- Use brand primary color {primary_color} for nav active state and primary buttons
- Fonts: Inter + Noto Sans SC, supporting bilingual interface

### 2. Dashboard (src/app/dashboard/page.tsx)
- Server Component, fetch data in parallel (Promise.all)
- Stats cards: Posts published this month, total engagements, avg engagement rate, pending approvals
- Today scheduled posts list (timeline view)
- Upcoming Malaysian holidays/important dates reminders (Chinese New Year, Hari Raya Aidilfitri, Deepavali, Merdeka Day, Malaysia Day, etc.)
- Platform connection status overview
- Quick action buttons: Create new post, View calendar, Bulk schedule

### 3. Content Calendar (src/app/calendar/page.tsx)
- Client Component using calendar library
- Three view modes: month, week, day
- Posts displayed as colored blocks on calendar (colors match content categories)
- Drag-and-drop rescheduling (drag posts to different dates/times)
- Click empty time slot to quickly create a post
- Filters: by platform, status, category, team member
- Display Malaysian public holidays and key marketing dates
- Week view shows hourly time slots
- Day view shows detailed timeline with post previews

### 4. Post Composer (src/app/posts/create/page.tsx)
- Multi-step form or two-column layout (left editor, right preview)
- **Platform Selector**: Multi-select from {platforms}, each showing icon and character limits
- **Bilingual Caption Editor**:
  - Chinese caption (caption_zh) and English caption (caption_en) in dual tabs
  - Rich text editing (emoji insertion support)
  - Character count (different limits per platform)
  - Variable insertion (e.g. {{brand_name}}, {{product}})
- **AI Caption Assistant Button**:
  - Opens dialog on click
  - Input: keywords, tone (professional/casual/promotional), target platform
  - Calls /api/ai/generate-caption route
  - Returns bilingual caption suggestions (3 options)
  - Support refinement and regeneration
- **Media Upload Area**:
  - Drag-and-drop or click to select
  - Supports images (JPG, PNG, WebP) and video (MP4, max 100MB)
  - Upload to Supabase Storage media bucket
  - Upload progress bar
  - Uploaded media thumbnail grid, sortable
  - Select existing files from media library
- **Hashtag Management**:
  - Select saved hashtag sets
  - Manual hashtag input (# autocomplete)
  - Display hashtag count (Instagram limit: 30)
- **Schedule Settings**:
  - Date picker + time picker
  - Timezone display (Asia/Kuala_Lumpur, GMT+8)
  - Best posting time recommendations (based on historical data)
  - Recurring schedule options: daily/weekly/monthly repeat
- **Preview Panel**:
  - Simulate post appearance on each platform
  - Switch between platform previews (Facebook post style, Instagram grid, LinkedIn article style, TikTok video cover)
- **Action Buttons**: Save draft, Submit for review, Schedule directly (manager/owner only)

### 5. Bulk Scheduling (src/app/posts/bulk/page.tsx)
- CSV upload functionality (provide template download)
- CSV format: title, caption_zh, caption_en, platform, scheduled_datetime, category
- Table preview after upload, editable per row
- Bulk status modification
- One-click schedule all posts

### 6. Media Library (src/app/media/page.tsx)
- Grid/list view toggle
- File type filter (image/video/GIF)
- Tag search
- Drag-and-drop upload zone
- Image preview lightbox
- File info panel (dimensions, size, uploader, usage count)
- Supabase Storage integration with signed URLs

### 7. Template Manager (src/app/templates/page.tsx)
- Template card grid
- Filter by category and platform
- Create/edit template (same as post editor but saved as template)
- Quick create post from template
- Template usage statistics

### 8. Hashtag Manager (src/app/hashtags/page.tsx)
- Hashtag set list (card layout)
- Create set: name + hashtag list + applicable platform
- Popular hashtag recommendations (based on usage frequency)
- One-click copy hashtag set
- Hashtag usage statistics

### 9. Analytics Dashboard (src/app/analytics/page.tsx)
- **Time Range Selector**: Last 7/30/90 days or custom range
- **Overview Cards**: Total posts, total engagements, avg engagement rate, best performing post
- **Engagement Trend Chart**: Line chart showing likes/comments/shares over time (use Recharts or Chart.js)
- **Platform Comparison**: Bar chart comparing platform performance
- **Best Posting Time Heatmap**: Engagement rate by day-of-week and hour
- **Content Category Performance**: Average engagement rate ranking by category
- **Team Member Contributions**: Post count and engagement rate per member
- **Post Leaderboard**: Table listing Top 20 posts (sorted by engagement rate)
- Use simulated data generator producing realistic analytics (consider Malaysian timezone posting habits: 8-9 AM, 12-1 PM, 8-10 PM peaks)

### 10. Approval Workflow (integrated in post detail page)
- Post status flow: draft -> pending_review -> approved/rejected -> scheduled -> published
- Approval timeline (showing each status change, actor, notes)
- Approvers can add comments and revision suggestions
- Email/in-app notifications (notify manager when post submitted for review)
- Bulk approval feature

### 11. Team Settings (src/app/settings/page.tsx)
- Team info editing (brand name, industry, primary color)
- Member management (invite, role change, remove)
- Social platform connection management (simulated OAuth flow)
- Notification preferences
- Default publishing settings (default platforms, default category)

## API Routes

### /api/posts (Route Handler)
- GET: Fetch posts list (supports pagination, filtering, sorting)
- POST: Create new post
- PATCH: Update post
- DELETE: Delete post

### /api/posts/bulk (Route Handler)
- POST: Bulk create posts (CSV parsing)

### /api/posts/[id]/publish (Route Handler)
- POST: Simulate publishing to social platforms (returns simulated success/failure and mock post URLs)

### /api/ai/generate-caption (Route Handler)
- POST: Call OpenAI API to generate bilingual captions
- Request body: {keywords, tone, platform, language, industry}
- Response: {suggestions: [{caption_zh, caption_en, hashtags}]}
- System prompt must include Malaysian business context

### /api/media/upload (Route Handler)
- POST: Handle file upload to Supabase Storage
- Generate thumbnails
- Return file path and metadata

### /api/analytics/overview (Route Handler)
- GET: Return aggregated analytics data for specified time range

### /api/analytics/seed (Route Handler, dev only)
- POST: Generate simulated analytics data to populate post_analytics table

## Simulated Data and Publishing

Since we are not connecting to real social platform APIs, implement the following simulation logic:
- When publishing a post, delay 1-3 seconds to simulate API call, 95% chance of success
- On success, generate simulated post URL (e.g. https://facebook.com/posts/mock-12345)
- After publishing, auto-generate simulated analytics data (random but within realistic ranges)
- Analytics data incrementally increases hourly (simulating real growth curves)

## Malaysian Localization Requirements

- Interface supports Chinese/English toggle
- Date format: DD/MM/YYYY (Malaysian convention)
- Timezone fixed: Asia/Kuala_Lumpur (GMT+8)
- Currency: MYR (if any paid features)
- Built-in Malaysian holiday calendar:
  - Chinese New Year
  - Hari Raya Aidilfitri
  - Deepavali
  - Hari Merdeka (August 31)
  - Malaysia Day (September 16)
  - 11.11 and 12.12 Shopping Festivals
  - Mid-Year Sale
- Post templates include localized content examples (e.g. CNY promotions, Hari Raya greetings)
- AI caption generation must support Malaysian Chinese vocabulary (e.g. pasar instead of the mainland Chinese equivalent)

## UI/UX Requirements

- Responsive design: desktop-first (1440px), adapting to tablet and mobile
- Brand primary color {primary_color} for primary buttons, nav active state, key metrics
- Dark sidebar (#0F172A) + white content area
- Card components with subtle borders and shadows
- Loading states using Skeleton components
- Success/failure notifications using Toast
- Form validation with React Hook Form + Zod
- Animations with Framer Motion (page transitions, card entrances)

## Code Quality Requirements

- TypeScript strict mode, all type definitions in src/types/ directory
- Clear Server Component and Client Component boundaries (use client directive at file top)
- All Supabase queries using type-safe client (generated from database.types.ts)
- Complete error boundaries (error.tsx) and loading states (loading.tsx)
- Optimize all images with next/image
- Environment variables: NEXT_PUBLIC_SUPABASE_URL, NEXT_PUBLIC_SUPABASE_ANON_KEY, SUPABASE_SERVICE_ROLE_KEY, OPENAI_API_KEY

Please start by creating the complete database migration files (Supabase SQL), then implement frontend features page by page. Pause after each step and wait for my confirmation before continuing.