WiSearch/dbschema.sql

-- public.admin_users definition

-- Drop table

-- DROP TABLE public.admin_users;

CREATE TABLE public.admin_users (
	id serial4 NOT NULL,
	username text NOT NULL,
	password_hash text NOT NULL,
	"role" text DEFAULT 'user'::text NULL,
	CONSTRAINT admin_users_pkey PRIMARY KEY (id),
	CONSTRAINT admin_users_username_key UNIQUE (username)
);


-- public.blacklisted_domains definition

-- Drop table

-- DROP TABLE public.blacklisted_domains;

CREATE TABLE public.blacklisted_domains (
	id serial4 NOT NULL,
	"domain" text NULL,
	CONSTRAINT blacklisted_domains_domain_key UNIQUE (domain),
	CONSTRAINT blacklisted_domains_pkey PRIMARY KEY (id)
);


-- public.media_files definition

-- Drop table

-- DROP TABLE public.media_files;

CREATE TABLE public.media_files (
	id serial4 NOT NULL,
	source_url text NOT NULL,
	media_url text NOT NULL,
	media_type varchar(50) NULL,
	alt_text text NULL,
	discovered_at timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT media_files_media_url_key UNIQUE (media_url),
	CONSTRAINT media_files_pkey PRIMARY KEY (id)
);
CREATE INDEX idx_media_alt_trgm ON public.media_files USING gin (alt_text gin_trgm_ops);
CREATE INDEX idx_media_url_trgm ON public.media_files USING gin (source_url gin_trgm_ops);


-- public.submitted_urls definition

-- Drop table

-- DROP TABLE public.submitted_urls;

CREATE TABLE public.submitted_urls (
	id serial4 NOT NULL,
	url text NULL,
	submitted_at timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT submitted_urls_pkey PRIMARY KEY (id)
);


-- public.zones definition

-- Drop table

-- DROP TABLE public.zones;

CREATE TABLE public.zones (
	id serial4 NOT NULL,
	zone_name text NOT NULL,
	dns_server_ip text NULL,
	last_synced timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	is_active bool DEFAULT true NULL,
	CONSTRAINT zones_pkey PRIMARY KEY (id),
	CONSTRAINT zones_zone_name_key UNIQUE (zone_name)
);


-- public.activity_logs definition

-- Drop table

-- DROP TABLE public.activity_logs;

CREATE TABLE public.activity_logs (
	id serial4 NOT NULL,
	ip_address text NULL,
	user_id int4 NULL,
	search_query text NULL,
	"timestamp" timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT activity_logs_pkey PRIMARY KEY (id),
	CONSTRAINT activity_logs_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.admin_users(id) ON DELETE SET NULL
);


-- public.dns_records definition

-- Drop table

-- DROP TABLE public.dns_records;

CREATE TABLE public.dns_records (
	id serial4 NOT NULL,
	zone_id int4 NULL,
	hostname text NOT NULL,
	record_type text NULL,
	value text NULL,
	last_seen timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT dns_records_pkey PRIMARY KEY (id),
	CONSTRAINT dns_records_unique_node UNIQUE (zone_id, hostname, record_type, value),
	CONSTRAINT dns_records_zone_id_fkey FOREIGN KEY (zone_id) REFERENCES public.zones(id) ON DELETE CASCADE
);
CREATE INDEX idx_dns_hostname_trgm ON public.dns_records USING gin (hostname gin_trgm_ops);


-- public.sponsored_links definition

-- Drop table

-- DROP TABLE public.sponsored_links;

CREATE TABLE public.sponsored_links (
	id serial4 NOT NULL,
	title text NOT NULL,
	url text NOT NULL,
	description text NULL,
	keywords text NULL,
	is_active bool DEFAULT true NULL,
	user_id int4 NULL,
	CONSTRAINT sponsored_links_pkey PRIMARY KEY (id),
	CONSTRAINT sponsored_links_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.admin_users(id) ON DELETE CASCADE
);


-- public.web_health definition

-- Drop table

-- DROP TABLE public.web_health;

CREATE TABLE public.web_health (
	id serial4 NOT NULL,
	dns_record_id int4 NULL,
	url text NULL,
	status_code int4 NULL,
	page_title text NULL,
	meta_description text NULL,
	h1_content text NULL,
	server_header text NULL,
	content_length int4 NULL,
	response_time_ms float8 NULL,
	body_preview text NULL,
	is_up bool NULL,
	last_check timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	keywords text NULL,
	clicks int4 DEFAULT 0 NULL,
	created_at timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	last_crawled timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	category varchar(50) DEFAULT 'Άγνωστο'::character varying NULL,
	ai_summary text NULL,
	activity_status varchar(20) NULL,
	pagerank_score float8 DEFAULT 1.0 NULL,
	full_content text NULL,
	authority_weight float8 DEFAULT 1.0 NULL,
	CONSTRAINT web_health_pkey PRIMARY KEY (id),
	CONSTRAINT web_health_url_key UNIQUE (url),
	CONSTRAINT web_health_dns_record_id_fkey FOREIGN KEY (dns_record_id) REFERENCES public.dns_records(id) ON DELETE CASCADE
);
CREATE INDEX idx_full_content_fts ON public.web_health USING gin (to_tsvector('greek'::regconfig, full_content));
CREATE INDEX idx_wh_content_trgm ON public.web_health USING gin (full_content gin_trgm_ops);
CREATE INDEX idx_wh_summary_trgm ON public.web_health USING gin (ai_summary gin_trgm_ops);
CREATE INDEX idx_wh_title_trgm ON public.web_health USING gin (page_title gin_trgm_ops);


-- public.web_links definition

-- Drop table

-- DROP TABLE public.web_links;

CREATE TABLE public.web_links (
	id serial4 NOT NULL,
	dns_record_id int4 NULL,
	source_url text NOT NULL,
	target_url text NOT NULL,
	link_text text NULL,
	discovered_at timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT web_links_pkey PRIMARY KEY (id),
	CONSTRAINT web_links_unique UNIQUE (source_url, target_url),
	CONSTRAINT web_links_dns_record_id_fkey FOREIGN KEY (dns_record_id) REFERENCES public.dns_records(id) ON DELETE CASCADE
);
CREATE INDEX idx_target_url ON public.web_links USING btree (target_url);