/*
  # Initial Database Setup

  1. Tables
    - users
      - id (SERIAL PRIMARY KEY)
      - phone_number (TEXT UNIQUE)
      - username (TEXT)
      - email (TEXT)
      - address (TEXT)
      - profile_picture (TEXT)
      - created_at (TIMESTAMP)
    
    - posts
      - id (SERIAL PRIMARY KEY)
      - user_id (INTEGER FK)
      - content (TEXT)
      - image_url (TEXT)
      - created_at (TIMESTAMP)
      - updated_at (TIMESTAMP)

  2. Indexes
    - users_phone_number_idx
    - users_email_idx
    - posts_user_id_idx
    - posts_created_at_idx

  3. Security
    - Row Level Security enabled
    - Policies for authenticated users
*/

-- Create users table
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  phone_number TEXT UNIQUE NOT NULL,
  username TEXT,
  email TEXT,
  address TEXT,
  profile_picture TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for users
CREATE INDEX IF NOT EXISTS users_phone_number_idx ON users(phone_number);
CREATE INDEX IF NOT EXISTS users_email_idx ON users(email);

-- Enable RLS on users
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Create policies for users
CREATE POLICY "Users can read all users" ON users
  FOR SELECT TO authenticated USING (true);

CREATE POLICY "Users can update their own data" ON users
  FOR UPDATE TO authenticated USING (id = auth.uid()::integer);

CREATE POLICY "Users can insert their own data" ON users
  FOR INSERT TO authenticated WITH CHECK (id = auth.uid()::integer);

-- Create posts table
CREATE TABLE IF NOT EXISTS posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  content TEXT,
  image_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for posts
CREATE INDEX IF NOT EXISTS posts_user_id_idx ON posts(user_id);
CREATE INDEX IF NOT EXISTS posts_created_at_idx ON posts(created_at);

-- Enable RLS on posts
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Create policies for posts
CREATE POLICY "Users can read all posts" ON posts
  FOR SELECT TO authenticated USING (true);

CREATE POLICY "Users can insert their own posts" ON posts
  FOR INSERT TO authenticated WITH CHECK (user_id = auth.uid()::integer);

CREATE POLICY "Users can update their own posts" ON posts
  FOR UPDATE TO authenticated USING (user_id = auth.uid()::integer);

CREATE POLICY "Users can delete their own posts" ON posts
  FOR DELETE TO authenticated USING (user_id = auth.uid()::integer);

-- Insert sample users
INSERT INTO users (phone_number, username, email, address, profile_picture)
VALUES 
  ('+1234567890', 'John Doe', 'john@example.com', '123 Main St, City, Country', 'uploads/default.jpg'),
  ('+1987654321', 'Jane Smith', 'jane@example.com', '456 Oak Ave, Town, Country', 'uploads/default.jpg'),
  ('+1122334455', 'Mike Johnson', 'mike@example.com', '789 Pine Rd, Village, Country', 'uploads/default.jpg')
ON CONFLICT (phone_number) DO NOTHING;