CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    phone_number TEXT NULL,
    email TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    address_line1 TEXT NULL,
    address_line2 TEXT NULL,
    city TEXT NULL,
    state TEXT NULL,
    postal_code TEXT NULL,
    country TEXT NULL,
    user_role TEXT DEFAULT 'CUSTOMER',
    is_verified BOOLEAN DEFAULT FALSE,
    verify_code TEXT NULL UNIQUE DEFAULT NULL,
    verify_code_expire TIMESTAMP NULL DEFAULT NULL,
    user_profile_pic TEXT DEFAULT NULL,
    user_status BOOLEAN DEFAULT TRUE,
    multifactor BOOLEAN DEFAULT FALSE,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS activity_logs (
    id SERIAL PRIMARY KEY,
    user_id TEXT,
    action TEXT NOT NULL, 
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS permission (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS role_permission (
    id SERIAL PRIMARY KEY,
    role_id INT REFERENCES roles(id) ON DELETE CASCADE,
    permission_id INT REFERENCES permission(id) ON DELETE CASCADE,
    UNIQUE(role_id, permission_id)
);


CREATE TABLE IF NOT EXISTS staff (
    id TEXT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password TEXT NOT NULL, -- Hashed password
    role_id INT REFERENCES roles(id) ON DELETE CASCADE,
    multifactor BOOLEAN DEFAULT FALSE,
    user_status BOOLEAN DEFAULT FALSE,
    last_login TIMESTAMP NULL,
    verify_code TEXT NULL UNIQUE DEFAULT NULL,
    verify_code_expire TIMESTAMP NULL DEFAULT NULL,
    user_profile_pic TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS site_settings (
    id TEXT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    meta_title VARCHAR(60) DEFAULT NULL,
    meta_description VARCHAR(155) DEFAULT NULL,
    logo TEXT DEFAULT NULL,
    favicon TEXT DEFAULT NULL,
    logo_alt TEXT DEFAULT NULL,
    logo_height TEXT DEFAULT NULL,
    logo_width TEXT DEFAULT NULL,
    home_schema TEXT DEFAULT NULL,
    footer_logo TEXT DEFAULT NULL,
    footer_logo_height TEXT DEFAULT NULL,
    footer_logo_width TEXT DEFAULT NULL,
    footer_logo_alt TEXT DEFAULT NULL,
    footer_text TEXT DEFAULT NULL,
    facebook_url TEXT DEFAULT NULL,
    twitter_url TEXT DEFAULT NULL,
    linkedin_url TEXT DEFAULT NULL,
    pinterest_url TEXT DEFAULT NULL,
    instagram_url TEXT DEFAULT NULL,
    copyright_text TEXT DEFAULT NULL,
    google_site_verification TEXT DEFAULT NULL,
    bing_site_verification TEXT DEFAULT NULL,
    baidu_site_verification TEXT DEFAULT NULL,
    yandex_site_verification TEXT DEFAULT NULL,
    pinterest_verification TEXT DEFAULT NULL,
    google_analytics_id TEXT DEFAULT NULL,
    robots TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS categories (
    id TEXT PRIMARY KEY,
    parent_id TEXT REFERENCES categories(id) ON DELETE SET NULL,
    is_indexable BOOLEAN DEFAULT TRUE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS categories_translations (
    id TEXT PRIMARY KEY,
    category_id TEXT REFERENCES categories(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL UNIQUE,
    slug VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    meta_title TEXT,
    meta_description TEXT,
    category_schema JSONB DEFAULT NULL,
    language VARCHAR(10) NOT NULL CHECK (language IN ('en', 'ar')),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS posts (
    id TEXT PRIMARY KEY,
    post_category TEXT REFERENCES categories(id) ON DELETE CASCADE,
    post_featured_image TEXT DEFAULT NULL,
    is_indexable BOOLEAN DEFAULT TRUE,
    is_active BOOLEAN DEFAULT TRUE,
    post_create_date TIMESTAMP DEFAULT NOW(),
    post_update_date TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS post_translations (
    id TEXT PRIMARY KEY,
    post_id TEXT REFERENCES posts(id) ON DELETE CASCADE,
    post_title TEXT NOT NULL UNIQUE,
    post_slug TEXT NOT NULL UNIQUE,
    post_content TEXT NOT NULL,
    post_featured_image_alt_text TEXT DEFAULT NULL,
    post_meta_title VARCHAR(255) NOT NULL,
    post_meta_description VARCHAR(255) NOT NULL,
    post_schemas JSONB DEFAULT NULL,
    language VARCHAR(10) NOT NULL CHECK (language IN ('en', 'ar'))
);

CREATE TABLE IF NOT EXISTS pages (
    id TEXT PRIMARY KEY,
    is_indexable BOOLEAN DEFAULT TRUE,
    is_active BOOLEAN DEFAULT TRUE,
    page_create_date TIMESTAMP DEFAULT NOW(),
    page_update_date TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS pages_translations (
    id TEXT PRIMARY KEY,
    page_id TEXT REFERENCES pages(id) ON DELETE CASCADE,
    page_title TEXT NOT NULL UNIQUE,
    page_slug TEXT NOT NULL UNIQUE,
    page_meta_title VARCHAR(255) NOT NULL,
    page_meta_description VARCHAR(255) NOT NULL,
    page_schemas JSONB DEFAULT NULL,
    builder_data JSONB DEFAULT NULL,
    language VARCHAR(10) NOT NULL CHECK (language IN ('en', 'ar'))
);

CREATE TABLE IF NOT EXISTS products (
    id TEXT PRIMARY KEY,
    category_id TEXT REFERENCES categories(id), 
    product_featured_image TEXT DEFAULT NULL,
    is_indexable BOOLEAN DEFAULT TRUE,
    is_active BOOLEAN DEFAULT TRUE,
    product_create_date TIMESTAMP DEFAULT NOW(),
    product_update_date TIMESTAMP DEFAULT NOW()
);


CREATE TABLE IF NOT EXISTS products_translations (
    id TEXT PRIMARY KEY,
    product_id TEXT REFERENCES products(id) ON DELETE CASCADE,
    product_title TEXT NOT NULL UNIQUE,
    product_slug TEXT NOT NULL UNIQUE,
    product_content TEXT NOT NULL,
    product_featured_image_alt_text TEXT DEFAULT NULL,
    product_meta_title VARCHAR(255) NOT NULL,
    product_meta_description VARCHAR(255) NOT NULL,
    builder_data JSONB DEFAULT NULL,
    benefits JSONB DEFAULT NULL,
    product_schemas JSONB DEFAULT NULL,
    language VARCHAR(10) NOT NULL CHECK (language IN ('en', 'ar'))
);

-- Component templates (for reusable components)
CREATE TABLE IF NOT EXISTS component_templates (
    id TEXT,
    template_name VARCHAR(255) NOT NULL UNIQUE,
    component_type VARCHAR(100) NOT NULL,
    component_props JSON NOT NULL,
    category VARCHAR(100) DEFAULT 'general',
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS services (
    id TEXT PRIMARY KEY,
    service_featured_image TEXT DEFAULT NULL,
    is_indexable BOOLEAN DEFAULT TRUE,
    is_active BOOLEAN DEFAULT TRUE,
    service_create_date TIMESTAMP DEFAULT NOW(),
    service_update_date TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS services_translations (
    id TEXT PRIMARY KEY,
    service_id TEXT REFERENCES services(id) ON DELETE CASCADE,
    service_title TEXT NOT NULL UNIQUE,
    service_slug TEXT NOT NULL UNIQUE,
    service_content TEXT NOT NULL,
    service_featured_image_alt_text TEXT DEFAULT NULL,
    service_meta_title VARCHAR(255) NOT NULL,
    service_meta_description VARCHAR(255) NOT NULL,
    services_schemas JSONB DEFAULT NULL,
    builder_data JSONB DEFAULT NULL,
    language VARCHAR(10) NOT NULL CHECK (language IN ('en', 'ar'))
);

CREATE TABLE IF NOT EXISTS projects (
    id TEXT PRIMARY KEY,
    category_id TEXT REFERENCES categories(id),
    project_featured_image TEXT DEFAULT NULL,
    is_indexable BOOLEAN DEFAULT TRUE,
    is_active BOOLEAN DEFAULT TRUE,
    project_date DATE NOT NULL,
    project_location TEXT NOT NULL,
    project_create_date TIMESTAMP DEFAULT NOW(),
    project_update_date TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS projects_translations (
    id TEXT PRIMARY KEY,
    project_id TEXT REFERENCES projects(id) ON DELETE CASCADE,
    project_title TEXT NOT NULL UNIQUE,
    project_slug TEXT DEFAULT NULL,
    project_image_alt_text TEXT DEFAULT NULL,
    project_content TEXT NOT NULL,
    benefits JSONB DEFAULT NULL,
    project_schemas JSONB DEFAULT NULL,
    project_meta_title VARCHAR(255) NOT NULL,
    project_meta_description VARCHAR(255) NOT NULL,
    builder_data JSONB DEFAULT NULL,
    language VARCHAR(10) NOT NULL CHECK (language IN ('en', 'ar'))
);

CREATE TABLE IF NOT EXISTS menu (
    id TEXT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    position VARCHAR(20) CHECK (position IN ('top_header','header', 'footer_menu_1', 'footer_menu_2')) NOT NULL,
    language VARCHAR(10) NOT NULL CHECK (language IN ('en', 'ar')),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS menu_items (
    id TEXT PRIMARY KEY,
    menu_id TEXT REFERENCES menu(id) ON DELETE CASCADE,
    reference_id TEXT, -- ID of the page/category/tag if applicable
    title VARCHAR(255), -- optional for custom_link
    type VARCHAR(50) CHECK (type IN ('page', 'category', 'tag', 'custom_link')),
    link VARCHAR(255), -- required only for custom_link
    parent_id TEXT REFERENCES menu_items(id) ON DELETE CASCADE,
    sort_order INTEGER DEFAULT 0 NOT NULL,
    language VARCHAR(10) NOT NULL CHECK (language IN ('en', 'ar'))
);


-- Create authors table
CREATE TABLE IF NOT EXISTS authors (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    profile_picture VARCHAR(500),
    social_links JSONB DEFAULT '{}',
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create index for better performance
CREATE INDEX IF NOT EXISTS idx_authors_is_active ON authors(is_active);
CREATE INDEX IF NOT EXISTS idx_authors_created_at ON authors(created_at DESC);

-- Create reviews table
CREATE TABLE IF NOT EXISTS reviews (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    review_content TEXT NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_reviews_rating ON reviews(rating);
CREATE INDEX IF NOT EXISTS idx_reviews_is_active ON reviews(is_active);
CREATE INDEX IF NOT EXISTS idx_reviews_created_at ON reviews(created_at DESC);

ALTER TABLE posts ADD COLUMN author_id UUID;