305 lines
9.0 KiB
Python
305 lines
9.0 KiB
Python
"""
|
|
Setup command for initializing the database.
|
|
"""
|
|
|
|
import logging
|
|
from typing import Optional
|
|
|
|
import typer
|
|
from rich.console import Console
|
|
from rich.progress import Progress, SpinnerColumn, TextColumn
|
|
|
|
from ..services.supabase import SupabaseService
|
|
from ..models.schemas import Region, Country, City, Tag, TagCategory
|
|
from ..config import DEFAULT_TAGS, GERMANY_CITIES
|
|
|
|
logger = logging.getLogger(__name__)
|
|
console = Console()
|
|
|
|
|
|
# SQL schema for creating tables
|
|
SCHEMA_SQL = """
|
|
-- Enable UUID extension
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- Regions table
|
|
CREATE TABLE IF NOT EXISTS regions (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Countries table
|
|
CREATE TABLE IF NOT EXISTS countries (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
region_id UUID REFERENCES regions(id),
|
|
name TEXT NOT NULL,
|
|
name_local TEXT,
|
|
code TEXT NOT NULL UNIQUE,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
language TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Cities table
|
|
CREATE TABLE IF NOT EXISTS cities (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
country_id UUID REFERENCES countries(id),
|
|
name TEXT NOT NULL,
|
|
name_local TEXT,
|
|
slug TEXT NOT NULL,
|
|
state TEXT,
|
|
population INTEGER,
|
|
latitude FLOAT,
|
|
longitude FLOAT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(country_id, slug)
|
|
);
|
|
|
|
-- Tags table
|
|
CREATE TABLE IF NOT EXISTS tags (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
category TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
description TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Companies table
|
|
CREATE TABLE IF NOT EXISTS companies (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
city_id UUID REFERENCES cities(id),
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL,
|
|
google_place_id TEXT UNIQUE,
|
|
address TEXT,
|
|
phone TEXT,
|
|
website TEXT,
|
|
email TEXT,
|
|
rating FLOAT,
|
|
review_count INTEGER,
|
|
latitude FLOAT,
|
|
longitude FLOAT,
|
|
website_markdown TEXT,
|
|
logo_url TEXT,
|
|
description TEXT,
|
|
content_html TEXT,
|
|
meta_title TEXT,
|
|
meta_description TEXT,
|
|
status TEXT DEFAULT 'scraped',
|
|
last_scraped_at TIMESTAMPTZ,
|
|
last_enriched_at TIMESTAMPTZ,
|
|
last_content_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Company tags junction table
|
|
CREATE TABLE IF NOT EXISTS company_tags (
|
|
company_id UUID REFERENCES companies(id) ON DELETE CASCADE,
|
|
tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
|
|
confidence FLOAT DEFAULT 1.0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
PRIMARY KEY (company_id, tag_id)
|
|
);
|
|
|
|
-- City tag aggregation
|
|
CREATE TABLE IF NOT EXISTS city_tags (
|
|
city_id UUID REFERENCES cities(id) ON DELETE CASCADE,
|
|
tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
|
|
company_count INTEGER DEFAULT 0,
|
|
PRIMARY KEY (city_id, tag_id)
|
|
);
|
|
|
|
-- Country tag aggregation
|
|
CREATE TABLE IF NOT EXISTS country_tags (
|
|
country_id UUID REFERENCES countries(id) ON DELETE CASCADE,
|
|
tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
|
|
company_count INTEGER DEFAULT 0,
|
|
city_count INTEGER DEFAULT 0,
|
|
PRIMARY KEY (country_id, tag_id)
|
|
);
|
|
|
|
-- Execution plans
|
|
CREATE TABLE IF NOT EXISTS execution_plans (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name TEXT NOT NULL,
|
|
country_code TEXT NOT NULL,
|
|
cities TEXT[] NOT NULL,
|
|
status TEXT DEFAULT 'pending',
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
started_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ
|
|
);
|
|
|
|
-- Plan steps
|
|
CREATE TABLE IF NOT EXISTS plan_steps (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
plan_id UUID REFERENCES execution_plans(id) ON DELETE CASCADE,
|
|
step_name TEXT NOT NULL,
|
|
city_id UUID REFERENCES cities(id),
|
|
status TEXT DEFAULT 'pending',
|
|
started_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ,
|
|
error_message TEXT,
|
|
items_processed INTEGER DEFAULT 0,
|
|
items_total INTEGER DEFAULT 0
|
|
);
|
|
|
|
-- Error logs
|
|
CREATE TABLE IF NOT EXISTS error_logs (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
entity_type TEXT NOT NULL,
|
|
entity_id UUID,
|
|
operation TEXT NOT NULL,
|
|
error_message TEXT NOT NULL,
|
|
details JSONB DEFAULT '{}',
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Indexes for performance
|
|
CREATE INDEX IF NOT EXISTS idx_companies_city ON companies(city_id);
|
|
CREATE INDEX IF NOT EXISTS idx_companies_status ON companies(status);
|
|
CREATE INDEX IF NOT EXISTS idx_company_tags_company ON company_tags(company_id);
|
|
CREATE INDEX IF NOT EXISTS idx_company_tags_tag ON company_tags(tag_id);
|
|
CREATE INDEX IF NOT EXISTS idx_city_tags_city ON city_tags(city_id);
|
|
CREATE INDEX IF NOT EXISTS idx_country_tags_country ON country_tags(country_id);
|
|
CREATE INDEX IF NOT EXISTS idx_plan_steps_plan ON plan_steps(plan_id);
|
|
"""
|
|
|
|
|
|
def setup_command(
|
|
seed_tags: bool = True,
|
|
seed_germany: bool = True,
|
|
) -> None:
|
|
"""
|
|
Initialize the database with schema and seed data.
|
|
|
|
This command should be run once when setting up a new installation.
|
|
It creates all required tables and optionally seeds reference data.
|
|
|
|
Args:
|
|
seed_tags: Whether to seed default tags
|
|
seed_germany: Whether to seed Germany cities
|
|
"""
|
|
console.print("\n[bold blue]Odoo Directory Setup[/bold blue]\n")
|
|
|
|
db = SupabaseService()
|
|
|
|
with Progress(
|
|
SpinnerColumn(),
|
|
TextColumn("[progress.description]{task.description}"),
|
|
console=console,
|
|
) as progress:
|
|
# Note: Schema should be created in Supabase SQL Editor
|
|
# The CLI just verifies connectivity and seeds data
|
|
|
|
task = progress.add_task("Verifying database connection...", total=None)
|
|
try:
|
|
# Test connection by listing regions
|
|
regions = db.list_regions()
|
|
progress.update(task, description="[green]Database connected")
|
|
except Exception as e:
|
|
console.print(f"\n[red]Database connection failed: {e}[/red]")
|
|
console.print(
|
|
"\n[yellow]Please create tables using the SQL schema in Supabase SQL Editor.[/yellow]"
|
|
)
|
|
console.print("\nSchema SQL can be found in directory/commands/setup.py")
|
|
raise typer.Exit(1)
|
|
|
|
# Seed tags
|
|
if seed_tags:
|
|
task = progress.add_task("Seeding tags...", total=None)
|
|
tag_count = _seed_tags(db)
|
|
progress.update(task, description=f"[green]Seeded {tag_count} tags")
|
|
|
|
# Seed Germany
|
|
if seed_germany:
|
|
task = progress.add_task("Seeding Germany data...", total=None)
|
|
city_count = _seed_germany(db)
|
|
progress.update(task, description=f"[green]Seeded {city_count} cities")
|
|
|
|
console.print("\n[bold green]Setup complete![/bold green]\n")
|
|
|
|
|
|
def _seed_tags(db: SupabaseService) -> int:
|
|
"""Seed default tags into database."""
|
|
count = 0
|
|
|
|
# Map category names to enum values
|
|
category_map = {
|
|
"services": TagCategory.SERVICE,
|
|
"industries": TagCategory.INDUSTRY,
|
|
"modules": TagCategory.MODULE,
|
|
"partner_levels": TagCategory.PARTNER_LEVEL,
|
|
}
|
|
|
|
for category_name, tag_names in DEFAULT_TAGS.items():
|
|
category = category_map[category_name]
|
|
|
|
for name in tag_names:
|
|
slug = name.lower().replace(" ", "-").replace("&", "and")
|
|
existing = db.get_tag_by_slug(slug)
|
|
|
|
if not existing:
|
|
db.create_tag(
|
|
Tag(
|
|
category=category,
|
|
name=name,
|
|
slug=slug,
|
|
)
|
|
)
|
|
count += 1
|
|
|
|
return count
|
|
|
|
|
|
def _seed_germany(db: SupabaseService) -> int:
|
|
"""Seed Germany region, country, and cities."""
|
|
# Create Europe region if not exists
|
|
region = db.get_region_by_slug("europe")
|
|
if not region:
|
|
region = db.create_region(Region(name="Europe", slug="europe"))
|
|
|
|
# Create Germany if not exists
|
|
country = db.get_country_by_code("DE")
|
|
if not country:
|
|
country = db.create_country(
|
|
Country(
|
|
region_id=region.id,
|
|
name="Germany",
|
|
name_local="Deutschland",
|
|
code="DE",
|
|
slug="deutschland",
|
|
language="de",
|
|
)
|
|
)
|
|
|
|
# Create cities
|
|
count = 0
|
|
for city_data in GERMANY_CITIES:
|
|
existing = db.get_city_by_slug(city_data["slug"], country.id)
|
|
if not existing:
|
|
db.create_city(
|
|
City(
|
|
country_id=country.id,
|
|
name=city_data["name"],
|
|
slug=city_data["slug"],
|
|
state=city_data.get("state"),
|
|
)
|
|
)
|
|
count += 1
|
|
|
|
return count
|
|
|
|
|
|
def print_schema() -> None:
|
|
"""Print the SQL schema for manual creation."""
|
|
console.print("\n[bold]Database Schema SQL:[/bold]\n")
|
|
console.print(SCHEMA_SQL)
|
|
console.print(
|
|
"\n[yellow]Copy this SQL and run it in your Supabase SQL Editor.[/yellow]"
|
|
)
|