-- +goose Up
-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE "countries" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"language" text DEFAULT 'multi',"slug" text,"name" text NOT NULL,"code" text NOT NULL,"phone_code" text,"currency" text,"continent" text);
CREATE TABLE "cities" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"language" text DEFAULT 'multi',"slug" text,"name" text NOT NULL,"country_id" UUID,CONSTRAINT "fk_countries_cities" FOREIGN KEY ("country_id") REFERENCES "countries"("id"));
CREATE TABLE "addresses" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"street" text,"street_number" text,"postal_code" text,"country_id" UUID,"city_id" UUID,"latitude" real,"longitude" real,CONSTRAINT "fk_cities_addresses" FOREIGN KEY ("city_id") REFERENCES "cities"("id"),CONSTRAINT "fk_countries_addresses" FOREIGN KEY ("country_id") REFERENCES "countries"("id"));
CREATE TABLE "users" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"username" text NOT NULL,"email" text NOT NULL,"password" text NOT NULL,"first_name" text,"last_name" text,"display_name" text,"bio" text,"avatar_url" text,"role" text DEFAULT 'reader',"last_login_at" timestamptz,"verified" boolean DEFAULT false,"active" boolean DEFAULT true,"country_id" UUID,"city_id" UUID,"address_id" UUID,CONSTRAINT "fk_users_city" FOREIGN KEY ("city_id") REFERENCES "cities"("id"),CONSTRAINT "fk_users_address" FOREIGN KEY ("address_id") REFERENCES "addresses"("id"),CONSTRAINT "fk_users_country" FOREIGN KEY ("country_id") REFERENCES "countries"("id"),CONSTRAINT "uni_users_username" UNIQUE ("username"),CONSTRAINT "uni_users_email" UNIQUE ("email"));
CREATE TABLE "user_sessions" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"user_id" UUID,"token" text NOT NULL,"ip" text,"user_agent" text,"expires_at" timestamptz NOT NULL,CONSTRAINT "fk_user_sessions_user" FOREIGN KEY ("user_id") REFERENCES "users"("id"));
CREATE TABLE "password_resets" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"user_id" UUID,"token" text NOT NULL,"expires_at" timestamptz NOT NULL,"used" boolean DEFAULT false,CONSTRAINT "fk_password_resets_user" FOREIGN KEY ("user_id") REFERENCES "users"("id"));
CREATE TABLE "email_verifications" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"user_id" UUID,"token" text NOT NULL,"expires_at" timestamptz NOT NULL,"used" boolean DEFAULT false,CONSTRAINT "fk_email_verifications_user" FOREIGN KEY ("user_id") REFERENCES "users"("id"));
CREATE TABLE "works" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"language" text DEFAULT 'multi',"slug" text,"title" text NOT NULL,"description" text,"type" text DEFAULT 'other',"status" text DEFAULT 'draft',"published_at" timestamptz);
CREATE TABLE "work_copyrights" ("work_id" UUID,"copyright_id" UUID,"created_at" timestamptz,PRIMARY KEY ("work_id","copyright_id"));
CREATE TABLE "categories" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"name" text NOT NULL,"description" text,"parent_id" UUID,"path" text,"slug" text,CONSTRAINT "fk_categories_children" FOREIGN KEY ("parent_id") REFERENCES "categories"("id"));
CREATE TABLE "work_categories" ("category_id" UUID,"work_id" UUID,PRIMARY KEY ("category_id","work_id"),CONSTRAINT "fk_work_categories_category" FOREIGN KEY ("category_id") REFERENCES "categories"("id"),CONSTRAINT "fk_work_categories_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"));
CREATE TABLE "tags" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"name" text NOT NULL,"description" text,"slug" text);
CREATE TABLE "work_tags" ("tag_id" UUID,"work_id" UUID,PRIMARY KEY ("tag_id","work_id"),CONSTRAINT "fk_work_tags_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"),CONSTRAINT "fk_work_tags_tag" FOREIGN KEY ("tag_id") REFERENCES "tags"("id"));
CREATE TABLE "places" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"language" text DEFAULT 'multi',"slug" text,"name" text NOT NULL,"description" text,"latitude" real,"longitude" real,"country_id" UUID,"city_id" UUID,CONSTRAINT "fk_cities_places" FOREIGN KEY ("city_id") REFERENCES "cities"("id"),CONSTRAINT "fk_countries_places" FOREIGN KEY ("country_id") REFERENCES "countries"("id"));
CREATE TABLE "authors" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"language" text DEFAULT 'multi',"slug" text,"name" text NOT NULL,"status" text DEFAULT 'active',"birth_date" timestamptz,"death_date" timestamptz,"country_id" UUID,"city_id" UUID,"place_id" UUID,"address_id" UUID,CONSTRAINT "fk_authors_country" FOREIGN KEY ("country_id") REFERENCES "countries"("id"),CONSTRAINT "fk_authors_city" FOREIGN KEY ("city_id") REFERENCES "cities"("id"),CONSTRAINT "fk_authors_place" FOREIGN KEY ("place_id") REFERENCES "places"("id"),CONSTRAINT "fk_authors_address" FOREIGN KEY ("address_id") REFERENCES "addresses"("id"));
CREATE TABLE "work_authors" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"work_id" UUID,"author_id" UUID,"role" text DEFAULT 'author',"ordinal" integer DEFAULT 0,CONSTRAINT "fk_work_authors_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"),CONSTRAINT "fk_work_authors_author" FOREIGN KEY ("author_id") REFERENCES "authors"("id"));
CREATE TABLE "work_monetizations" ("work_id" UUID,"monetization_id" UUID,"created_at" timestamptz,PRIMARY KEY ("work_id","monetization_id"));
CREATE TABLE "publishers" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"language" text DEFAULT 'multi',"slug" text,"name" text NOT NULL,"description" text,"status" text DEFAULT 'active',"country_id" UUID,CONSTRAINT "fk_publishers_country" FOREIGN KEY ("country_id") REFERENCES "countries"("id"));
CREATE TABLE "books" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"language" text DEFAULT 'multi',"slug" text,"title" text NOT NULL,"description" text,"isbn" text,"format" text DEFAULT 'paperback',"status" text DEFAULT 'draft',"published_at" timestamptz,"publisher_id" UUID,CONSTRAINT "fk_publishers_books" FOREIGN KEY ("publisher_id") REFERENCES "publishers"("id"));
CREATE TABLE "book_authors" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"book_id" UUID,"author_id" UUID,"role" text DEFAULT 'author',"ordinal" integer DEFAULT 0,CONSTRAINT "fk_book_authors_book" FOREIGN KEY ("book_id") REFERENCES "books"("id"),CONSTRAINT "fk_book_authors_author" FOREIGN KEY ("author_id") REFERENCES "authors"("id"));
CREATE TABLE "author_monetizations" ("author_id" UUID,"monetization_id" UUID,"created_at" timestamptz,PRIMARY KEY ("author_id","monetization_id"));
CREATE TABLE "author_copyrights" ("author_id" UUID,"copyright_id" UUID,"created_at" timestamptz,PRIMARY KEY ("author_id","copyright_id"));
CREATE TABLE "book_works" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"book_id" UUID,"work_id" UUID,"order" integer DEFAULT 0,CONSTRAINT "fk_book_works_book" FOREIGN KEY ("book_id") REFERENCES "books"("id"),CONSTRAINT "fk_book_works_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"));
CREATE TABLE "book_monetizations" ("book_id" UUID,"monetization_id" UUID,"created_at" timestamptz,PRIMARY KEY ("book_id","monetization_id"));
CREATE TABLE "book_copyrights" ("book_id" UUID,"copyright_id" UUID,"created_at" timestamptz,PRIMARY KEY ("book_id","copyright_id"));
CREATE TABLE "publisher_monetizations" ("publisher_id" UUID,"monetization_id" UUID,"created_at" timestamptz,PRIMARY KEY ("publisher_id","monetization_id"));
CREATE TABLE "publisher_copyrights" ("publisher_id" UUID,"copyright_id" UUID,"created_at" timestamptz,PRIMARY KEY ("publisher_id","copyright_id"));
CREATE TABLE "sources" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"language" text DEFAULT 'multi',"slug" text,"name" text NOT NULL,"description" text,"url" text,"status" text DEFAULT 'active');
CREATE TABLE "source_monetizations" ("source_id" UUID,"monetization_id" UUID,"created_at" timestamptz,PRIMARY KEY ("source_id","monetization_id"));
CREATE TABLE "source_copyrights" ("source_id" UUID,"copyright_id" UUID,"created_at" timestamptz,PRIMARY KEY ("source_id","copyright_id"));
CREATE TABLE "work_sources" ("source_id" UUID,"work_id" UUID,PRIMARY KEY ("source_id","work_id"),CONSTRAINT "fk_work_sources_source" FOREIGN KEY ("source_id") REFERENCES "sources"("id"),CONSTRAINT "fk_work_sources_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"));
CREATE TABLE "editions" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"title" text NOT NULL,"description" text,"isbn" text,"version" text,"format" text DEFAULT 'paperback',"status" text DEFAULT 'draft',"published_at" timestamptz,"book_id" UUID,CONSTRAINT "fk_editions_book" FOREIGN KEY ("book_id") REFERENCES "books"("id"));
CREATE TABLE "translations" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"title" text NOT NULL,"content" text,"description" text,"language" text NOT NULL,"status" text DEFAULT 'draft',"published_at" timestamptz,"translatable_id" UUID NOT NULL,"translatable_type" text NOT NULL,"translator_id" UUID,"is_original_language" boolean DEFAULT false,"audio_url" text,"date_translated" timestamptz,CONSTRAINT "fk_users_translations" FOREIGN KEY ("translator_id") REFERENCES "users"("id"));
CREATE TABLE "text_blocks" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"work_id" UUID,"translation_id" UUID,"index" bigint,"type" text,"start_offset" integer DEFAULT 0,"end_offset" integer DEFAULT 0,"text" text,CONSTRAINT "fk_text_blocks_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"),CONSTRAINT "fk_text_blocks_translation" FOREIGN KEY ("translation_id") REFERENCES "translations"("id"));
CREATE TABLE "comments" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"text" text NOT NULL,"user_id" UUID,"work_id" UUID,"translation_id" UUID,"line_number" bigint,"text_block_id" UUID,"parent_id" UUID,CONSTRAINT "fk_comments_translation" FOREIGN KEY ("translation_id") REFERENCES "translations"("id"),CONSTRAINT "fk_comments_text_block" FOREIGN KEY ("text_block_id") REFERENCES "text_blocks"("id"),CONSTRAINT "fk_comments_children" FOREIGN KEY ("parent_id") REFERENCES "comments"("id"),CONSTRAINT "fk_users_comments" FOREIGN KEY ("user_id") REFERENCES "users"("id"),CONSTRAINT "fk_comments_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"));
CREATE TABLE "likes" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"user_id" UUID,"work_id" UUID,"translation_id" UUID,"comment_id" UUID,CONSTRAINT "fk_users_likes" FOREIGN KEY ("user_id") REFERENCES "users"("id"),CONSTRAINT "fk_likes_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"),CONSTRAINT "fk_likes_translation" FOREIGN KEY ("translation_id") REFERENCES "translations"("id"),CONSTRAINT "fk_comments_likes" FOREIGN KEY ("comment_id") REFERENCES "comments"("id"));
CREATE TABLE "bookmarks" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"name" text,"user_id" UUID,"work_id" UUID,"notes" text,"last_read_at" timestamptz,"progress" integer DEFAULT 0,CONSTRAINT "fk_bookmarks_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"),CONSTRAINT "fk_users_bookmarks" FOREIGN KEY ("user_id") REFERENCES "users"("id"));
CREATE TABLE "collections" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"language" text DEFAULT 'multi',"slug" text,"name" text NOT NULL,"description" text,"user_id" UUID,"is_public" boolean DEFAULT true,"cover_image_url" text,CONSTRAINT "fk_users_collections" FOREIGN KEY ("user_id") REFERENCES "users"("id"));
CREATE TABLE "collection_works" ("collection_id" UUID,"work_id" UUID,PRIMARY KEY ("collection_id","work_id"),CONSTRAINT "fk_collection_works_collection" FOREIGN KEY ("collection_id") REFERENCES "collections"("id"),CONSTRAINT "fk_collection_works_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"));
CREATE TABLE "contributions" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"name" text NOT NULL,"status" text DEFAULT 'draft',"user_id" UUID,"work_id" UUID,"translation_id" UUID,"reviewer_id" UUID,"reviewed_at" timestamptz,"feedback" text,CONSTRAINT "fk_contributions_reviewer" FOREIGN KEY ("reviewer_id") REFERENCES "users"("id"),CONSTRAINT "fk_users_contributions" FOREIGN KEY ("user_id") REFERENCES "users"("id"),CONSTRAINT "fk_contributions_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"),CONSTRAINT "fk_contributions_translation" FOREIGN KEY ("translation_id") REFERENCES "translations"("id"));
CREATE TABLE "languages" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"code" text NOT NULL,"name" text NOT NULL,"script" text,"direction" text);
CREATE TABLE "series" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"name" text NOT NULL,"description" text);
CREATE TABLE "work_series" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"work_id" UUID,"series_id" UUID,"number_in_series" integer DEFAULT 0,CONSTRAINT "fk_work_series_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"),CONSTRAINT "fk_work_series_series" FOREIGN KEY ("series_id") REFERENCES "series"("id"));
CREATE TABLE "translation_fields" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"translation_id" UUID,"field_name" text NOT NULL,"field_value" text NOT NULL,"language" text NOT NULL,CONSTRAINT "fk_translation_fields_translation" FOREIGN KEY ("translation_id") REFERENCES "translations"("id"));
CREATE TABLE "copyrights" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"identificator" text NOT NULL,"name" text NOT NULL,"description" text,"license" text,"start_date" timestamptz,"end_date" timestamptz);
CREATE TABLE "copyright_translations" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"copyright_id" UUID,"language_code" text NOT NULL,"message" text NOT NULL,"description" text,CONSTRAINT "fk_copyrights_translations" FOREIGN KEY ("copyright_id") REFERENCES "copyrights"("id"));
CREATE TABLE "copyright_claims" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"details" text NOT NULL,"status" text DEFAULT 'pending',"claim_date" timestamptz NOT NULL,"resolution" text,"resolved_at" timestamptz,"user_id" UUID,CONSTRAINT "fk_copyright_claims_user" FOREIGN KEY ("user_id") REFERENCES "users"("id"));
CREATE TABLE "monetizations" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"amount" decimal(10,2) DEFAULT 0,"currency" text DEFAULT 'USD',"type" text,"status" text DEFAULT 'active',"start_date" timestamptz,"end_date" timestamptz,"language" text NOT NULL);
CREATE TABLE "licenses" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"spdx_identifier" text,"name" text NOT NULL,"url" text,"description" text);
CREATE TABLE "moderation_flags" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"target_type" text NOT NULL,"target_id" UUID NOT NULL,"reason" text,"status" text DEFAULT 'open',"reviewer_id" UUID,"notes" text,CONSTRAINT "fk_moderation_flags_reviewer" FOREIGN KEY ("reviewer_id") REFERENCES "users"("id"));
CREATE TABLE "audit_logs" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"actor_id" UUID,"action" text NOT NULL,"entity_type" text NOT NULL,"entity_id" UUID NOT NULL,"before" jsonb DEFAULT '{}',"after" jsonb DEFAULT '{}',"at" timestamptz,CONSTRAINT "fk_audit_logs_actor" FOREIGN KEY ("actor_id") REFERENCES "users"("id"));
CREATE TABLE "work_stats" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"views" bigint DEFAULT 0,"likes" bigint DEFAULT 0,"comments" bigint DEFAULT 0,"bookmarks" bigint DEFAULT 0,"shares" bigint DEFAULT 0,"translation_count" bigint DEFAULT 0,"reading_time" integer DEFAULT 0,"complexity" decimal(5,2) DEFAULT 0,"sentiment" decimal(5,2) DEFAULT 0,"work_id" UUID,CONSTRAINT "fk_work_stats_work" FOREIGN KEY ("work_id") REFERENCES "works"("id") ON DELETE CASCADE ON UPDATE CASCADE);
CREATE TABLE "translation_stats" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"views" bigint DEFAULT 0,"likes" bigint DEFAULT 0,"comments" bigint DEFAULT 0,"shares" bigint DEFAULT 0,"reading_time" integer DEFAULT 0,"sentiment" decimal(5,2) DEFAULT 0,"translation_id" UUID,CONSTRAINT "fk_translation_stats_translation" FOREIGN KEY ("translation_id") REFERENCES "translations"("id") ON DELETE CASCADE ON UPDATE CASCADE);
CREATE TABLE "user_engagements" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"user_id" UUID,"date" date,"works_read" integer DEFAULT 0,"comments_made" integer DEFAULT 0,"likes_given" integer DEFAULT 0,"bookmarks_made" integer DEFAULT 0,"translations_made" integer DEFAULT 0,CONSTRAINT "fk_user_engagements_user" FOREIGN KEY ("user_id") REFERENCES "users"("id"));
CREATE TABLE "trendings" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"entity_type" text NOT NULL,"entity_id" UUID NOT NULL,"rank" integer NOT NULL,"score" decimal(10,2) DEFAULT 0,"time_period" text NOT NULL,"date" date);
CREATE TABLE "book_stats" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"sales" bigint DEFAULT 0,"views" bigint DEFAULT 0,"likes" bigint DEFAULT 0,"book_id" UUID,CONSTRAINT "fk_book_stats_book" FOREIGN KEY ("book_id") REFERENCES "books"("id"));
CREATE TABLE "collection_stats" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"items" bigint DEFAULT 0,"views" bigint DEFAULT 0,"likes" bigint DEFAULT 0,"collection_id" UUID,CONSTRAINT "fk_collection_stats_collection" FOREIGN KEY ("collection_id") REFERENCES "collections"("id"));
CREATE TABLE "media_stats" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"views" bigint DEFAULT 0,"downloads" bigint DEFAULT 0,"shares" bigint DEFAULT 0,"media_id" UUID);
CREATE TABLE "author_countries" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"author_id" UUID,"country_id" UUID,CONSTRAINT "fk_author_countries_author" FOREIGN KEY ("author_id") REFERENCES "authors"("id"),CONSTRAINT "fk_author_countries_country" FOREIGN KEY ("country_id") REFERENCES "countries"("id"));
CREATE TABLE "readability_scores" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"score" decimal(5,2),"language" text NOT NULL,"method" text,"work_id" UUID,CONSTRAINT "fk_readability_scores_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"));
CREATE TABLE "writing_styles" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"name" text NOT NULL,"description" text,"language" text NOT NULL,"work_id" UUID,CONSTRAINT "fk_writing_styles_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"));
CREATE TABLE "linguistic_layers" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"name" text NOT NULL,"description" text,"language" text NOT NULL,"type" text,"work_id" UUID,"data" jsonb DEFAULT '{}',CONSTRAINT "fk_linguistic_layers_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"));
CREATE TABLE "text_metadata" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"analysis" text,"language" text NOT NULL,"word_count" integer DEFAULT 0,"sentence_count" integer DEFAULT 0,"paragraph_count" integer DEFAULT 0,"average_word_length" decimal(5,2),"average_sentence_length" decimal(5,2),"work_id" UUID,CONSTRAINT "fk_text_metadata_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"));
CREATE TABLE "poetic_analyses" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"structure" text,"language" text NOT NULL,"rhyme_scheme" text,"meter_type" text,"stanza_count" integer DEFAULT 0,"line_count" integer DEFAULT 0,"work_id" UUID,CONSTRAINT "fk_poetic_analyses_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"));
CREATE TABLE "concepts" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"name" text NOT NULL,"description" text);
CREATE TABLE "words" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"text" text NOT NULL,"language" text NOT NULL,"part_of_speech" text,"lemma" text,"concept_id" UUID,CONSTRAINT "fk_concepts_words" FOREIGN KEY ("concept_id") REFERENCES "concepts"("id"));
CREATE TABLE "work_words" ("word_id" UUID,"work_id" UUID,PRIMARY KEY ("word_id","work_id"),CONSTRAINT "fk_work_words_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"),CONSTRAINT "fk_work_words_word" FOREIGN KEY ("word_id") REFERENCES "words"("id"));
CREATE TABLE "word_occurrences" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"text_block_id" UUID,"word_id" UUID,"start_offset" integer DEFAULT 0,"end_offset" integer DEFAULT 0,"lemma" text,"part_of_speech" text,CONSTRAINT "fk_word_occurrences_text_block" FOREIGN KEY ("text_block_id") REFERENCES "text_blocks"("id"),CONSTRAINT "fk_word_occurrences_word" FOREIGN KEY ("word_id") REFERENCES "words"("id"));
CREATE TABLE "work_concepts" ("concept_id" UUID,"work_id" UUID,PRIMARY KEY ("concept_id","work_id"),CONSTRAINT "fk_work_concepts_concept" FOREIGN KEY ("concept_id") REFERENCES "concepts"("id"),CONSTRAINT "fk_work_concepts_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"));
CREATE TABLE "language_entities" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"name" text NOT NULL,"type" text,"language" text NOT NULL);
CREATE TABLE "work_language_entities" ("language_entity_id" UUID,"work_id" UUID,PRIMARY KEY ("language_entity_id","work_id"),CONSTRAINT "fk_work_language_entities_language_entity" FOREIGN KEY ("language_entity_id") REFERENCES "language_entities"("id"),CONSTRAINT "fk_work_language_entities_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"));
CREATE TABLE "entity_occurrences" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"text_block_id" UUID,"language_entity_id" UUID,"start_offset" integer DEFAULT 0,"end_offset" integer DEFAULT 0,CONSTRAINT "fk_entity_occurrences_text_block" FOREIGN KEY ("text_block_id") REFERENCES "text_blocks"("id"),CONSTRAINT "fk_entity_occurrences_language_entity" FOREIGN KEY ("language_entity_id") REFERENCES "language_entities"("id"));
CREATE TABLE "language_analyses" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"language" text NOT NULL,"analysis" jsonb DEFAULT '{}',"work_id" UUID,CONSTRAINT "fk_language_analyses_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"));
CREATE TABLE "gamifications" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"points" integer DEFAULT 0,"level" integer DEFAULT 1,"badges" jsonb DEFAULT '{}',"streaks" integer DEFAULT 0,"last_active" timestamptz,"user_id" UUID,CONSTRAINT "fk_gamifications_user" FOREIGN KEY ("user_id") REFERENCES "users"("id"));
CREATE TABLE "stats" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"data" jsonb DEFAULT '{}',"period" text,"start_date" timestamptz,"end_date" timestamptz,"user_id" UUID,"work_id" UUID,CONSTRAINT "fk_stats_user" FOREIGN KEY ("user_id") REFERENCES "users"("id"),CONSTRAINT "fk_stats_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"));
CREATE TABLE "search_documents" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"entity_type" text,"entity_id" UUID,"language_code" text,"title" text,"body" text,"keywords" text);
CREATE TABLE "emotions" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"name" text NOT NULL,"description" text,"language" text NOT NULL,"intensity" decimal(5,2) DEFAULT 0,"user_id" UUID,"work_id" UUID,"collection_id" UUID,CONSTRAINT "fk_emotions_user" FOREIGN KEY ("user_id") REFERENCES "users"("id"),CONSTRAINT "fk_emotions_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"),CONSTRAINT "fk_emotions_collection" FOREIGN KEY ("collection_id") REFERENCES "collections"("id"));
CREATE TABLE "moods" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"name" text NOT NULL,"description" text,"language" text NOT NULL);
CREATE TABLE "work_moods" ("mood_id" UUID,"work_id" UUID,PRIMARY KEY ("mood_id","work_id"),CONSTRAINT "fk_work_moods_mood" FOREIGN KEY ("mood_id") REFERENCES "moods"("id"),CONSTRAINT "fk_work_moods_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"));
CREATE TABLE "topic_clusters" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"name" text NOT NULL,"description" text,"keywords" text);
CREATE TABLE "work_topic_clusters" ("topic_cluster_id" UUID,"work_id" UUID,PRIMARY KEY ("topic_cluster_id","work_id"),CONSTRAINT "fk_work_topic_clusters_topic_cluster" FOREIGN KEY ("topic_cluster_id") REFERENCES "topic_clusters"("id"),CONSTRAINT "fk_work_topic_clusters_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"));
CREATE TABLE "edges" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"source_table" text NOT NULL,"source_id" UUID NOT NULL,"target_table" text NOT NULL,"target_id" UUID NOT NULL,"relation" text NOT NULL DEFAULT 'ASSOCIATED_WITH',"language" text DEFAULT 'en',"extra" jsonb DEFAULT '{}');
CREATE TABLE "embeddings" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"external_id" text,"entity_type" text NOT NULL,"entity_id" UUID NOT NULL,"model" text NOT NULL,"dim" integer DEFAULT 0,"work_id" UUID,"translation_id" UUID,CONSTRAINT "fk_embeddings_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"),CONSTRAINT "fk_embeddings_translation" FOREIGN KEY ("translation_id") REFERENCES "translations"("id"));
CREATE TABLE "localizations" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"key" text NOT NULL,"value" text NOT NULL,"language" text NOT NULL);
CREATE TABLE "media" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"url" text NOT NULL,"type" text NOT NULL,"mime_type" text,"size" bigint DEFAULT 0,"title" text,"description" text,"language" text NOT NULL,"author_id" UUID,"translation_id" UUID,"country_id" UUID,"city_id" UUID,CONSTRAINT "fk_media_city" FOREIGN KEY ("city_id") REFERENCES "cities"("id"),CONSTRAINT "fk_media_author" FOREIGN KEY ("author_id") REFERENCES "authors"("id"),CONSTRAINT "fk_media_translation" FOREIGN KEY ("translation_id") REFERENCES "translations"("id"),CONSTRAINT "fk_media_country" FOREIGN KEY ("country_id") REFERENCES "countries"("id"));
CREATE TABLE "notifications" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"message" text NOT NULL,"type" text,"read" boolean DEFAULT false,"language" text NOT NULL,"user_id" UUID,"related_id" UUID,"related_type" text,CONSTRAINT "fk_notifications_user" FOREIGN KEY ("user_id") REFERENCES "users"("id"));
CREATE TABLE "editorial_workflows" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"stage" text NOT NULL,"notes" text,"language" text NOT NULL,"work_id" UUID,"translation_id" UUID,"user_id" UUID,"assigned_to_id" UUID,"due_date" timestamptz,"completed_at" timestamptz,CONSTRAINT "fk_editorial_workflows_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"),CONSTRAINT "fk_editorial_workflows_translation" FOREIGN KEY ("translation_id") REFERENCES "translations"("id"),CONSTRAINT "fk_editorial_workflows_user" FOREIGN KEY ("user_id") REFERENCES "users"("id"),CONSTRAINT "fk_editorial_workflows_assigned_to" FOREIGN KEY ("assigned_to_id") REFERENCES "users"("id"));
CREATE TABLE "admins" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"user_id" UUID,"role" text NOT NULL,"permissions" jsonb DEFAULT '{}',CONSTRAINT "fk_admins_user" FOREIGN KEY ("user_id") REFERENCES "users"("id"));
CREATE TABLE "votes" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"value" integer DEFAULT 0,"user_id" UUID,"work_id" UUID,"translation_id" UUID,"comment_id" UUID,CONSTRAINT "fk_votes_user" FOREIGN KEY ("user_id") REFERENCES "users"("id"),CONSTRAINT "fk_votes_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"),CONSTRAINT "fk_votes_translation" FOREIGN KEY ("translation_id") REFERENCES "translations"("id"),CONSTRAINT "fk_votes_comment" FOREIGN KEY ("comment_id") REFERENCES "comments"("id"));
CREATE TABLE "contributors" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"name" text NOT NULL,"role" text,"user_id" UUID,"work_id" UUID,"translation_id" UUID,CONSTRAINT "fk_contributors_user" FOREIGN KEY ("user_id") REFERENCES "users"("id"),CONSTRAINT "fk_contributors_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"),CONSTRAINT "fk_contributors_translation" FOREIGN KEY ("translation_id") REFERENCES "translations"("id"));
CREATE TABLE "interaction_events" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"user_id" UUID,"target_type" text NOT NULL,"target_id" UUID NOT NULL,"kind" text NOT NULL,"occurred_at" timestamptz,CONSTRAINT "fk_interaction_events_user" FOREIGN KEY ("user_id") REFERENCES "users"("id"));
CREATE TABLE "hybrid_entity_works" ("id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),"created_at" timestamptz,"updated_at" timestamptz,"name" text NOT NULL,"type" text,"work_id" UUID,"translation_id" UUID,CONSTRAINT "fk_hybrid_entity_works_work" FOREIGN KEY ("work_id") REFERENCES "works"("id"),CONSTRAINT "fk_hybrid_entity_works_translation" FOREIGN KEY ("translation_id") REFERENCES "translations"("id"));

-- +goose Down
DROP TABLE IF EXISTS "hybrid_entity_works";
DROP TABLE IF EXISTS "interaction_events";
DROP TABLE IF EXISTS "contributors";
DROP TABLE IF EXISTS "votes";
DROP TABLE IF EXISTS "admins";
DROP TABLE IF EXISTS "editorial_workflows";
DROP TABLE IF EXISTS "notifications";
DROP TABLE IF EXISTS "media";
DROP TABLE IF EXISTS "localizations";
DROP TABLE IF EXISTS "embeddings";
DROP TABLE IF EXISTS "edges";
DROP TABLE IF EXISTS "work_topic_clusters";
DROP TABLE IF EXISTS "topic_clusters";
DROP TABLE IF EXISTS "work_moods";
DROP TABLE IF EXISTS "moods";
DROP TABLE IF EXISTS "emotions";
DROP TABLE IF EXISTS "search_documents";
DROP TABLE IF EXISTS "stats";
DROP TABLE IF EXISTS "gamifications";
DROP TABLE IF EXISTS "language_analyses";
DROP TABLE IF EXISTS "entity_occurrences";
DROP TABLE IF EXISTS "work_language_entities";
DROP TABLE IF EXISTS "language_entities";
DROP TABLE IF EXISTS "work_concepts";
DROP TABLE IF EXISTS "word_occurrences";
DROP TABLE IF EXISTS "work_words";
DROP TABLE IF EXISTS "words";
DROP TABLE IF EXISTS "concepts";
DROP TABLE IF EXISTS "poetic_analyses";
DROP TABLE IF EXISTS "text_metadata";
DROP TABLE IF EXISTS "linguistic_layers";
DROP TABLE IF EXISTS "writing_styles";
DROP TABLE IF EXISTS "readability_scores";
DROP TABLE IF EXISTS "author_countries";
DROP TABLE IF EXISTS "media_stats";
DROP TABLE IF EXISTS "collection_stats";
DROP TABLE IF EXISTS "book_stats";
DROP TABLE IF EXISTS "trendings";
DROP TABLE IF EXISTS "user_engagements";
DROP TABLE IF EXISTS "translation_stats";
DROP TABLE IF EXISTS "work_stats";
DROP TABLE IF EXISTS "audit_logs";
DROP TABLE IF EXISTS "moderation_flags";
DROP TABLE IF EXISTS "licenses";
DROP TABLE IF EXISTS "monetizations";
DROP TABLE IF EXISTS "copyright_claims";
DROP TABLE IF EXISTS "copyright_translations";
DROP TABLE IF EXISTS "copyrights";
DROP TABLE IF EXISTS "translation_fields";
DROP TABLE IF EXISTS "work_series";
DROP TABLE IF EXISTS "series";
DROP TABLE IF EXISTS "languages";
DROP TABLE IF EXISTS "contributions";
DROP TABLE IF EXISTS "collection_works";
DROP TABLE IF EXISTS "collections";
DROP TABLE IF EXISTS "bookmarks";
DROP TABLE IF EXISTS "likes";
DROP TABLE IF EXISTS "comments";
DROP TABLE IF EXISTS "text_blocks";
DROP TABLE IF EXISTS "translations";
DROP TABLE IF EXISTS "editions";
DROP TABLE IF EXISTS "work_sources";
DROP TABLE IF EXISTS "source_copyrights";
DROP TABLE IF EXISTS "source_monetizations";
DROP TABLE IF EXISTS "sources";
DROP TABLE IF EXISTS "publisher_copyrights";
DROP TABLE IF EXISTS "publisher_monetizations";
DROP TABLE IF EXISTS "book_copyrights";
DROP TABLE IF EXISTS "book_monetizations";
DROP TABLE IF EXISTS "book_works";
DROP TABLE IF EXISTS "author_copyrights";
DROP TABLE IF EXISTS "author_monetizations";
DROP TABLE IF EXISTS "book_authors";
DROP TABLE IF EXISTS "work_authors";
DROP TABLE IF EXISTS "authors";
DROP TABLE IF EXISTS "places";
DROP TABLE IF EXISTS "work_tags";
DROP TABLE IF EXISTS "tags";
DROP TABLE IF EXISTS "work_categories";
DROP TABLE IF EXISTS "categories";
DROP TABLE IF EXISTS "work_copyrights";
DROP TABLE IF EXISTS "works";
DROP TABLE IF EXISTS "email_verifications";
DROP TABLE IF EXISTS "password_resets";
DROP TABLE IF EXISTS "user_sessions";
DROP TABLE IF EXISTS "users";
DROP TABLE IF EXISTS "addresses";
DROP TABLE IF EXISTS "cities";
DROP TABLE IF EXISTS "countries";
DROP TABLE IF EXISTS "sqlite_sequence";