BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "phase1_listings_backup_20260516235105" (
	"id"	INTEGER,
	"slug"	TEXT NOT NULL UNIQUE,
	"title"	TEXT NOT NULL,
	"category"	TEXT NOT NULL,
	"manufacturer"	TEXT NOT NULL,
	"model"	TEXT NOT NULL,
	"year"	INTEGER,
	"hours"	INTEGER,
	"price"	TEXT NOT NULL,
	"location"	TEXT NOT NULL,
	"hero_image"	TEXT,
	"short_description"	TEXT NOT NULL,
	"description"	TEXT NOT NULL,
	"specs"	TEXT NOT NULL,
	"featured"	INTEGER NOT NULL DEFAULT 0,
	"status"	TEXT NOT NULL DEFAULT 'Available',
	"created_at"	TEXT NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "site_settings" (
	"id"	INTEGER,
	"company_name"	TEXT,
	"logo_path"	TEXT,
	"favicon_path"	TEXT,
	"phone"	TEXT,
	"whatsapp"	TEXT,
	"email"	TEXT,
	"address"	TEXT,
	"footer_text"	TEXT,
	"facebook_url"	TEXT,
	"instagram_url"	TEXT,
	"youtube_url"	TEXT,
	"default_language"	TEXT,
	"homepage_hero_title"	TEXT,
	"homepage_hero_subtitle"	TEXT,
	"created_at"	TEXT,
	"updated_at"	TEXT,
	"enable_call_button"	INTEGER DEFAULT 1,
	"enable_whatsapp_button"	INTEGER DEFAULT 1,
	"enable_email_button"	INTEGER DEFAULT 1,
	"enable_sticky_contact_form"	INTEGER DEFAULT 1,
	"base_currency"	TEXT DEFAULT 'EUR',
	"enable_currency_conversion"	INTEGER DEFAULT 0,
	"openai_api_key_encrypted"	TEXT,
	"openai_logo_file_id"	TEXT,
	"openai_image_model"	TEXT DEFAULT 'gpt-5',
	"openai_image_edit_prompt"	TEXT,
	"fireworks_api_key_encrypted"	TEXT,
	"fireworks_model"	TEXT DEFAULT 'accounts/fireworks/models/kimi-k2p5',
	"fireworks_custom_model"	TEXT,
	"openai_model"	TEXT DEFAULT 'gpt-5.2',
	"ai_provider"	TEXT DEFAULT 'fireworks',
	"ai_generation_enabled"	INTEGER DEFAULT 0,
	"public_stock_prefix"	TEXT DEFAULT 'CAS',
	"show_internal_stock_publicly"	INTEGER DEFAULT 0,
	"fireworks_image_review_model"	TEXT DEFAULT 'accounts/fireworks/models/kimi-k2p5',
	"openai_angle_generation_prompt"	TEXT,
	"chatbot_enabled"	INTEGER DEFAULT 0,
	"chatbot_provider"	TEXT DEFAULT 'fireworks',
	"chatbot_model"	TEXT DEFAULT 'accounts/fireworks/models/kimi-k2p5',
	"chatbot_custom_model"	TEXT,
	"chatbot_greeting"	TEXT,
	"chatbot_fallback_message"	TEXT,
	"chatbot_lead_capture_enabled"	INTEGER DEFAULT 1,
	"chatbot_show_desktop"	INTEGER DEFAULT 1,
	"chatbot_show_mobile"	INTEGER DEFAULT 1,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "languages" (
	"id"	INTEGER,
	"code"	TEXT UNIQUE,
	"name"	TEXT,
	"is_active"	INTEGER,
	"is_default"	INTEGER,
	"sort_order"	INTEGER,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "menu_items" (
	"id"	INTEGER,
	"label_en"	TEXT,
	"label_it"	TEXT,
	"label_de"	TEXT,
	"label_nl"	TEXT,
	"label_no"	TEXT,
	"label_da"	TEXT,
	"label_sv"	TEXT,
	"label_pl"	TEXT,
	"url"	TEXT,
	"location"	TEXT,
	"sort_order"	INTEGER,
	"is_active"	INTEGER,
	"created_at"	TEXT,
	"updated_at"	TEXT,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "listings" (
	"id"	INTEGER,
	"stock_number"	TEXT UNIQUE,
	"slug"	TEXT UNIQUE,
	"title_en"	TEXT,
	"title_it"	TEXT,
	"title_de"	TEXT,
	"title_nl"	TEXT,
	"title_no"	TEXT,
	"title_da"	TEXT,
	"title_sv"	TEXT,
	"title_pl"	TEXT,
	"short_description_en"	TEXT,
	"short_description_it"	TEXT,
	"short_description_de"	TEXT,
	"short_description_nl"	TEXT,
	"short_description_no"	TEXT,
	"short_description_da"	TEXT,
	"short_description_sv"	TEXT,
	"short_description_pl"	TEXT,
	"description_html_en"	TEXT,
	"description_html_it"	TEXT,
	"description_html_de"	TEXT,
	"description_html_nl"	TEXT,
	"description_html_no"	TEXT,
	"description_html_da"	TEXT,
	"description_html_sv"	TEXT,
	"description_html_pl"	TEXT,
	"seo_title_en"	TEXT,
	"seo_title_it"	TEXT,
	"seo_title_de"	TEXT,
	"seo_title_nl"	TEXT,
	"seo_title_no"	TEXT,
	"seo_title_da"	TEXT,
	"seo_title_sv"	TEXT,
	"seo_title_pl"	TEXT,
	"meta_description_en"	TEXT,
	"meta_description_it"	TEXT,
	"meta_description_de"	TEXT,
	"meta_description_nl"	TEXT,
	"meta_description_no"	TEXT,
	"meta_description_da"	TEXT,
	"meta_description_sv"	TEXT,
	"meta_description_pl"	TEXT,
	"year"	TEXT,
	"make"	TEXT,
	"model"	TEXT,
	"category"	TEXT,
	"price"	TEXT,
	"price_label"	TEXT,
	"status"	TEXT,
	"source_url"	TEXT,
	"folder_path"	TEXT,
	"featured_image"	TEXT,
	"is_featured"	INTEGER DEFAULT 0,
	"is_published"	INTEGER DEFAULT 1,
	"created_at"	TEXT,
	"updated_at"	TEXT,
	"hours"	TEXT,
	"source_metadata_json"	TEXT,
	"public_stock_number"	TEXT,
	"is_deleted"	INTEGER DEFAULT 0,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "listing_images" (
	"id"	INTEGER,
	"listing_id"	INTEGER,
	"image_url"	TEXT,
	"local_path"	TEXT,
	"alt_text"	TEXT,
	"sort_order"	INTEGER,
	"is_primary"	INTEGER DEFAULT 0,
	"created_at"	TEXT,
	"file_size"	INTEGER,
	"checksum_sha256"	TEXT,
	"download_status"	TEXT,
	"openai_file_id"	TEXT,
	"generated_local_path"	TEXT,
	"use_generated_image"	INTEGER DEFAULT 0,
	"is_ai_generated"	INTEGER DEFAULT 0,
	"is_public"	INTEGER DEFAULT 1,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "leads" (
	"id"	INTEGER,
	"listing_id"	INTEGER,
	"customer_name"	TEXT,
	"customer_email"	TEXT,
	"customer_phone"	TEXT,
	"customer_whatsapp"	TEXT,
	"message"	TEXT,
	"source_page"	TEXT,
	"language_code"	TEXT,
	"created_at"	TEXT,
	"is_read"	INTEGER DEFAULT 0,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "site_pages" (
	"id"	INTEGER,
	"slug"	TEXT UNIQUE,
	"title_en"	TEXT,
	"title_it"	TEXT,
	"title_de"	TEXT,
	"title_nl"	TEXT,
	"title_no"	TEXT,
	"title_da"	TEXT,
	"title_sv"	TEXT,
	"title_pl"	TEXT,
	"content_html_en"	TEXT,
	"content_html_it"	TEXT,
	"content_html_de"	TEXT,
	"content_html_nl"	TEXT,
	"content_html_no"	TEXT,
	"content_html_da"	TEXT,
	"content_html_sv"	TEXT,
	"content_html_pl"	TEXT,
	"is_published"	INTEGER DEFAULT 1,
	"created_at"	TEXT,
	"updated_at"	TEXT,
	"page_type"	TEXT DEFAULT 'standard',
	"status"	TEXT DEFAULT 'published',
	"menu_visibility"	INTEGER DEFAULT 0,
	"menu_order"	INTEGER DEFAULT 100,
	"hero_image_path"	TEXT,
	"cta_title_en"	TEXT,
	"cta_text_en"	TEXT,
	"visual_prompt_en"	TEXT,
	"english_updated_at"	TEXT,
	"excerpt_en"	TEXT,
	"seo_title_en"	TEXT,
	"meta_description_en"	TEXT,
	"translation_status_it"	TEXT DEFAULT 'missing',
	"translated_at_it"	TEXT,
	"translation_published_it"	INTEGER DEFAULT 0,
	"excerpt_it"	TEXT,
	"seo_title_it"	TEXT,
	"meta_description_it"	TEXT,
	"translation_status_de"	TEXT DEFAULT 'missing',
	"translated_at_de"	TEXT,
	"translation_published_de"	INTEGER DEFAULT 0,
	"excerpt_de"	TEXT,
	"seo_title_de"	TEXT,
	"meta_description_de"	TEXT,
	"translation_status_nl"	TEXT DEFAULT 'missing',
	"translated_at_nl"	TEXT,
	"translation_published_nl"	INTEGER DEFAULT 0,
	"excerpt_nl"	TEXT,
	"seo_title_nl"	TEXT,
	"meta_description_nl"	TEXT,
	"translation_status_no"	TEXT DEFAULT 'missing',
	"translated_at_no"	TEXT,
	"translation_published_no"	INTEGER DEFAULT 0,
	"excerpt_no"	TEXT,
	"seo_title_no"	TEXT,
	"meta_description_no"	TEXT,
	"translation_status_da"	TEXT DEFAULT 'missing',
	"translated_at_da"	TEXT,
	"translation_published_da"	INTEGER DEFAULT 0,
	"excerpt_da"	TEXT,
	"seo_title_da"	TEXT,
	"meta_description_da"	TEXT,
	"translation_status_sv"	TEXT DEFAULT 'missing',
	"translated_at_sv"	TEXT,
	"translation_published_sv"	INTEGER DEFAULT 0,
	"excerpt_sv"	TEXT,
	"seo_title_sv"	TEXT,
	"meta_description_sv"	TEXT,
	"translation_status_pl"	TEXT DEFAULT 'missing',
	"translated_at_pl"	TEXT,
	"translation_published_pl"	INTEGER DEFAULT 0,
	"excerpt_pl"	TEXT,
	"seo_title_pl"	TEXT,
	"meta_description_pl"	TEXT,
	"show_in_header"	INTEGER DEFAULT 0,
	"show_in_footer"	INTEGER DEFAULT 0,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "faqs" (
	"id"	INTEGER,
	"question_en"	TEXT,
	"question_it"	TEXT,
	"question_de"	TEXT,
	"question_nl"	TEXT,
	"question_no"	TEXT,
	"question_da"	TEXT,
	"question_sv"	TEXT,
	"question_pl"	TEXT,
	"answer_en"	TEXT,
	"answer_it"	TEXT,
	"answer_de"	TEXT,
	"answer_nl"	TEXT,
	"answer_no"	TEXT,
	"answer_da"	TEXT,
	"answer_sv"	TEXT,
	"answer_pl"	TEXT,
	"sort_order"	INTEGER,
	"is_active"	INTEGER DEFAULT 1,
	"created_at"	TEXT,
	"updated_at"	TEXT,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "ai_generations" (
	"id"	INTEGER,
	"listing_id"	INTEGER,
	"provider"	TEXT,
	"model"	TEXT,
	"prompt"	TEXT,
	"response"	TEXT,
	"created_at"	TEXT,
	"status"	TEXT,
	"error"	TEXT,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "chat_sessions" (
	"id"	INTEGER,
	"session_token"	TEXT,
	"language_code"	TEXT,
	"source_page"	TEXT,
	"listing_id"	INTEGER,
	"created_at"	TEXT,
	"updated_at"	TEXT,
	"visitor_name"	TEXT,
	"visitor_email"	TEXT,
	"visitor_phone"	TEXT,
	"visitor_whatsapp"	TEXT,
	"ip_hash"	TEXT,
	"user_agent"	TEXT,
	"lead_created"	INTEGER DEFAULT 0,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "chat_messages" (
	"id"	INTEGER,
	"session_id"	INTEGER,
	"role"	TEXT,
	"message"	TEXT,
	"created_at"	TEXT,
	"model"	TEXT,
	"provider"	TEXT,
	"tokens_input"	INTEGER,
	"tokens_output"	INTEGER,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "users" (
	"id"	INTEGER,
	"username"	TEXT UNIQUE,
	"password_hash"	TEXT,
	"email"	TEXT,
	"role"	TEXT,
	"is_active"	INTEGER DEFAULT 1,
	"created_at"	TEXT,
	"updated_at"	TEXT,
	"must_change_password"	INTEGER DEFAULT 0,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "related_listings" (
	"id"	INTEGER,
	"listing_id"	INTEGER,
	"related_listing_id"	INTEGER,
	"sort_order"	INTEGER,
	"created_at"	TEXT,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "currency_rates" (
	"id"	INTEGER,
	"currency_code"	TEXT UNIQUE,
	"rate_from_eur"	REAL,
	"symbol"	TEXT,
	"is_active"	INTEGER,
	"updated_at"	TEXT,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "ai_image_edits" (
	"id"	INTEGER,
	"listing_id"	INTEGER,
	"source_image_id"	INTEGER,
	"source_edit_id"	INTEGER,
	"source_local_path"	TEXT,
	"result_path"	TEXT,
	"prompt"	TEXT,
	"response_id"	TEXT,
	"status"	TEXT DEFAULT 'pending',
	"error_message"	TEXT,
	"created_at"	TEXT,
	"updated_at"	TEXT,
	"edit_kind"	TEXT DEFAULT 'replacement',
	"review_status"	TEXT,
	"review_notes"	TEXT,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "price_history" (
	"id"	INTEGER,
	"listing_id"	INTEGER,
	"old_price"	TEXT,
	"new_price"	TEXT,
	"change_type"	TEXT,
	"percentage"	REAL,
	"changed_by"	TEXT,
	"created_at"	TEXT,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "page_views" (
	"id"	INTEGER,
	"path"	TEXT,
	"full_url"	TEXT,
	"referrer"	TEXT,
	"user_agent"	TEXT,
	"ip_hash"	TEXT,
	"country"	TEXT,
	"device_type"	TEXT,
	"is_bot"	INTEGER,
	"bot_name"	TEXT,
	"listing_id"	INTEGER,
	"language_code"	TEXT,
	"created_at"	TEXT,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "generated_visuals" (
	"id"	INTEGER,
	"page_id"	INTEGER,
	"listing_id"	INTEGER,
	"placement_key"	TEXT,
	"prompt"	TEXT,
	"provider"	TEXT,
	"model"	TEXT,
	"local_path"	TEXT,
	"width"	INTEGER,
	"height"	INTEGER,
	"is_active"	INTEGER DEFAULT 1,
	"created_at"	TEXT,
	"updated_at"	TEXT,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "redirects" (
	"id"	INTEGER,
	"old_path"	TEXT UNIQUE,
	"new_path"	TEXT,
	"status_code"	INTEGER DEFAULT 301,
	"created_at"	TEXT,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "chatbot_knowledge" (
	"id"	INTEGER,
	"title"	TEXT,
	"category"	TEXT,
	"content"	TEXT,
	"language_code"	TEXT DEFAULT 'en',
	"is_active"	INTEGER DEFAULT 1,
	"sort_order"	INTEGER DEFAULT 0,
	"created_at"	TEXT,
	"updated_at"	TEXT,
	PRIMARY KEY("id")
);
COMMIT;