CREATE TABLE "medio" (
	"id" text PRIMARY KEY NOT NULL,
	"clase" text NOT NULL,
	"clave" text NOT NULL,
	"publico" boolean DEFAULT false NOT NULL,
	"mime" text NOT NULL,
	"bytes" integer NOT NULL,
	"duracion_s" integer,
	"ancho" integer,
	"alto" integer,
	"sha256" text NOT NULL,
	"alt" text,
	"creado_en" timestamp with time zone NOT NULL,
	CONSTRAINT "medio_clave_unique" UNIQUE("clave")
);
--> statement-breakpoint
CREATE TABLE "pais" (
	"codigo" char(2) PRIMARY KEY NOT NULL,
	"nombre" text NOT NULL,
	"gentilicio" text,
	"region" text
);
--> statement-breakpoint
CREATE TABLE "persona" (
	"id" text PRIMARY KEY NOT NULL,
	"slug" text NOT NULL,
	"nombre" text NOT NULL,
	"nombre_legal" text,
	"ipi" text,
	"nacimiento" integer,
	"defuncion" integer,
	"pais_codigo" char(2),
	"retrato_id" text,
	"semblanza" text,
	"estado" text DEFAULT 'borrador' NOT NULL,
	"creado_en" timestamp with time zone NOT NULL,
	"actualizado_en" timestamp with time zone,
	CONSTRAINT "persona_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "album" (
	"id" text PRIMARY KEY NOT NULL,
	"slug" text NOT NULL,
	"titulo" text NOT NULL,
	"estilo_id" text NOT NULL,
	"tipo" text DEFAULT 'album' NOT NULL,
	"fecha" text NOT NULL,
	"portada_id" text,
	"sello" text,
	"resumen" text DEFAULT '' NOT NULL,
	"destacado" boolean DEFAULT false NOT NULL,
	"numero_catalogo" text,
	"formatos" text[],
	"pais_edicion_codigo" char(2),
	"estado" text DEFAULT 'borrador' NOT NULL,
	"creado_en" timestamp with time zone NOT NULL,
	"actualizado_en" timestamp with time zone,
	CONSTRAINT "album_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "album_enlace" (
	"album_id" text NOT NULL,
	"plataforma" text NOT NULL,
	"url" text NOT NULL,
	CONSTRAINT "album_enlace_album_id_plataforma_pk" PRIMARY KEY("album_id","plataforma")
);
--> statement-breakpoint
CREATE TABLE "autoria" (
	"cancion_id" text NOT NULL,
	"persona_id" text NOT NULL,
	"rol" text NOT NULL,
	"porcentaje" numeric(5, 2) NOT NULL,
	CONSTRAINT "autoria_cancion_id_persona_id_rol_pk" PRIMARY KEY("cancion_id","persona_id","rol")
);
--> statement-breakpoint
CREATE TABLE "cancion" (
	"id" text PRIMARY KEY NOT NULL,
	"slug" text NOT NULL,
	"titulo" text NOT NULL,
	"version_de_id" text,
	"version_nombre" text,
	"version_orden" integer DEFAULT 0 NOT NULL,
	"estilo_id" text NOT NULL,
	"album_id" text,
	"numero" integer,
	"audio_id" text,
	"muestra_id" text,
	"duracion_s" integer,
	"ano" integer,
	"letra" text,
	"idioma" text DEFAULT 'es' NOT NULL,
	"nota" text,
	"inspiracion" text,
	"instrumental" boolean DEFAULT false NOT NULL,
	"destacada" boolean DEFAULT false NOT NULL,
	"portada_id" text,
	"ala_venta" boolean DEFAULT false NOT NULL,
	"estado" text DEFAULT 'borrador' NOT NULL,
	"creado_en" timestamp with time zone NOT NULL,
	"actualizado_en" timestamp with time zone,
	CONSTRAINT "cancion_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "cancion_enlace" (
	"cancion_id" text NOT NULL,
	"plataforma" text NOT NULL,
	"url" text NOT NULL,
	CONSTRAINT "cancion_enlace_cancion_id_plataforma_pk" PRIMARY KEY("cancion_id","plataforma")
);
--> statement-breakpoint
CREATE TABLE "cancion_etiqueta" (
	"cancion_id" text NOT NULL,
	"etiqueta_id" text NOT NULL,
	CONSTRAINT "cancion_etiqueta_cancion_id_etiqueta_id_pk" PRIMARY KEY("cancion_id","etiqueta_id")
);
--> statement-breakpoint
CREATE TABLE "cancion_persona" (
	"cancion_id" text NOT NULL,
	"persona_id" text NOT NULL,
	"rol" text NOT NULL,
	"orden" integer DEFAULT 0 NOT NULL,
	CONSTRAINT "cancion_persona_cancion_id_persona_id_rol_pk" PRIMARY KEY("cancion_id","persona_id","rol")
);
--> statement-breakpoint
CREATE TABLE "credito_album" (
	"id" text PRIMARY KEY NOT NULL,
	"album_id" text NOT NULL,
	"persona_id" text,
	"nombre" text,
	"rol" text NOT NULL,
	"orden" integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE "estilo" (
	"id" text PRIMARY KEY NOT NULL,
	"slug" text NOT NULL,
	"nombre" text NOT NULL,
	"resumen" text DEFAULT '' NOT NULL,
	"orden" integer DEFAULT 0 NOT NULL,
	"principal" boolean DEFAULT false NOT NULL,
	"imagen_id" text,
	"estado" text DEFAULT 'borrador' NOT NULL,
	"creado_en" timestamp with time zone NOT NULL,
	"actualizado_en" timestamp with time zone,
	CONSTRAINT "estilo_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "etiqueta" (
	"id" text PRIMARY KEY NOT NULL,
	"slug" text NOT NULL,
	"nombre" text NOT NULL,
	CONSTRAINT "etiqueta_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "registro_obra" (
	"cancion_id" text PRIMARY KEY NOT NULL,
	"iswc" text,
	"isrc" text,
	"entidad" text,
	"numero" text,
	"editorial" text
);
--> statement-breakpoint
CREATE TABLE "traduccion" (
	"cancion_id" text NOT NULL,
	"idioma" text NOT NULL,
	"texto" text NOT NULL,
	CONSTRAINT "traduccion_cancion_id_idioma_pk" PRIMARY KEY("cancion_id","idioma")
);
--> statement-breakpoint
CREATE TABLE "video" (
	"id" text PRIMARY KEY NOT NULL,
	"cancion_id" text NOT NULL,
	"youtube_id" text NOT NULL,
	"titulo" text NOT NULL,
	"orden" integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE "acepcion" (
	"id" text PRIMARY KEY NOT NULL,
	"termino_id" text NOT NULL,
	"numero" integer NOT NULL,
	"categoria" text NOT NULL,
	"definicion" text NOT NULL,
	"ampliacion" text,
	"ejemplo" text
);
--> statement-breakpoint
CREATE TABLE "animo" (
	"id" text PRIMARY KEY NOT NULL,
	"codigo" text NOT NULL,
	"nombre" text NOT NULL,
	"gems" text,
	"valencia" integer,
	"energia" integer,
	"descripcion" text,
	CONSTRAINT "animo_codigo_unique" UNIQUE("codigo")
);
--> statement-breakpoint
CREATE TABLE "cancion_genero" (
	"cancion_id" text NOT NULL,
	"genero_id" text NOT NULL,
	CONSTRAINT "cancion_genero_cancion_id_genero_id_pk" PRIMARY KEY("cancion_id","genero_id")
);
--> statement-breakpoint
CREATE TABLE "capitulo" (
	"id" text PRIMARY KEY NOT NULL,
	"slug" text NOT NULL,
	"titulo" text NOT NULL,
	"tipo" text NOT NULL,
	"genero_id" text,
	"orden" integer DEFAULT 0 NOT NULL,
	"resumen" text DEFAULT '' NOT NULL,
	"cuerpo" text DEFAULT '' NOT NULL,
	"estado" text DEFAULT 'borrador' NOT NULL,
	"creado_en" timestamp with time zone NOT NULL,
	"actualizado_en" timestamp with time zone,
	CONSTRAINT "capitulo_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "capitulo_acepcion" (
	"capitulo_id" text NOT NULL,
	"acepcion_id" text NOT NULL,
	"automatico" boolean DEFAULT true NOT NULL,
	"confirmado" boolean DEFAULT false NOT NULL,
	CONSTRAINT "capitulo_acepcion_capitulo_id_acepcion_id_pk" PRIMARY KEY("capitulo_id","acepcion_id")
);
--> statement-breakpoint
CREATE TABLE "compas" (
	"id" text PRIMARY KEY NOT NULL,
	"codigo" text NOT NULL,
	"nombre" text,
	"numerador" integer NOT NULL,
	"denominador" integer NOT NULL,
	"nota" text,
	CONSTRAINT "compas_codigo_unique" UNIQUE("codigo")
);
--> statement-breakpoint
CREATE TABLE "genero" (
	"id" text PRIMARY KEY NOT NULL,
	"slug" text NOT NULL,
	"nombre" text NOT NULL,
	"resumen" text DEFAULT '' NOT NULL,
	"origen_lugar" text,
	"origen_ano" integer,
	"tempo" text,
	"rima" text,
	"metrica_nota" text,
	"compas_nota" text,
	"rasgos" text[],
	"estado" text DEFAULT 'borrador' NOT NULL,
	"origen" text DEFAULT 'manual' NOT NULL,
	"modelo_origen" text,
	"importado_en" timestamp with time zone,
	"revisado_por" text,
	"revisado_en" timestamp with time zone,
	"creado_en" timestamp with time zone NOT NULL,
	"actualizado_en" timestamp with time zone,
	CONSTRAINT "genero_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "genero_alias" (
	"id" text PRIMARY KEY NOT NULL,
	"genero_id" text NOT NULL,
	"nombre" text NOT NULL,
	"pais_codigo" char(2),
	"nota" text
);
--> statement-breakpoint
CREATE TABLE "genero_compas" (
	"genero_id" text NOT NULL,
	"compas_id" text NOT NULL,
	"principal" boolean DEFAULT false NOT NULL,
	"nota" text,
	CONSTRAINT "genero_compas_genero_id_compas_id_pk" PRIMARY KEY("genero_id","compas_id")
);
--> statement-breakpoint
CREATE TABLE "genero_instrumento" (
	"genero_id" text NOT NULL,
	"instrumento_id" text NOT NULL,
	"importancia" text DEFAULT 'habitual' NOT NULL,
	"papel" text,
	"orden" integer DEFAULT 0 NOT NULL,
	CONSTRAINT "genero_instrumento_genero_id_instrumento_id_pk" PRIMARY KEY("genero_id","instrumento_id")
);
--> statement-breakpoint
CREATE TABLE "genero_metro" (
	"genero_id" text NOT NULL,
	"metro_id" text NOT NULL,
	"predominante" boolean DEFAULT false NOT NULL,
	CONSTRAINT "genero_metro_genero_id_metro_id_pk" PRIMARY KEY("genero_id","metro_id")
);
--> statement-breakpoint
CREATE TABLE "genero_origen" (
	"genero_id" text NOT NULL,
	"origen_id" text NOT NULL,
	"tipo" text NOT NULL,
	"principal" boolean DEFAULT false NOT NULL,
	"nota" text,
	"orden" integer DEFAULT 0 NOT NULL,
	CONSTRAINT "genero_origen_genero_id_origen_id_tipo_pk" PRIMARY KEY("genero_id","origen_id","tipo")
);
--> statement-breakpoint
CREATE TABLE "genero_pais" (
	"genero_id" text NOT NULL,
	"pais_codigo" char(2) NOT NULL,
	"relacion" text DEFAULT 'adoptado' NOT NULL,
	"nota" text,
	CONSTRAINT "genero_pais_genero_id_pais_codigo_pk" PRIMARY KEY("genero_id","pais_codigo")
);
--> statement-breakpoint
CREATE TABLE "genero_persona" (
	"genero_id" text NOT NULL,
	"persona_id" text NOT NULL,
	"papel" text NOT NULL,
	"nota" text,
	"orden" integer DEFAULT 0 NOT NULL,
	CONSTRAINT "genero_persona_genero_id_persona_id_papel_pk" PRIMARY KEY("genero_id","persona_id","papel")
);
--> statement-breakpoint
CREATE TABLE "genero_seccion" (
	"id" text PRIMARY KEY NOT NULL,
	"genero_id" text NOT NULL,
	"clave" text NOT NULL,
	"titulo" text NOT NULL,
	"cuerpo" text NOT NULL,
	"orden" integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE "genero_tema" (
	"genero_id" text NOT NULL,
	"etiqueta_id" text NOT NULL,
	"orden" integer DEFAULT 0 NOT NULL,
	CONSTRAINT "genero_tema_genero_id_etiqueta_id_pk" PRIMARY KEY("genero_id","etiqueta_id")
);
--> statement-breakpoint
CREATE TABLE "instrumento" (
	"id" text PRIMARY KEY NOT NULL,
	"slug" text NOT NULL,
	"nombre" text NOT NULL,
	"familia" text NOT NULL,
	"subfamilia" text,
	"pais_codigo" char(2),
	"descripcion" text,
	"afinacion" text,
	"imagen_id" text,
	"muestra_id" text,
	"estado" text DEFAULT 'borrador' NOT NULL,
	"origen" text DEFAULT 'manual' NOT NULL,
	"modelo_origen" text,
	"importado_en" timestamp with time zone,
	"revisado_por" text,
	"revisado_en" timestamp with time zone,
	CONSTRAINT "instrumento_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "metro" (
	"id" text PRIMARY KEY NOT NULL,
	"codigo" text NOT NULL,
	"nombre" text NOT NULL,
	"silabas" integer,
	"termino_id" text,
	"nota" text,
	CONSTRAINT "metro_codigo_unique" UNIQUE("codigo")
);
--> statement-breakpoint
CREATE TABLE "modelo_ia" (
	"id" text PRIMARY KEY NOT NULL,
	"codigo" text NOT NULL,
	"familia" text NOT NULL,
	"version" text NOT NULL,
	"nombre" text NOT NULL,
	"vigente" boolean DEFAULT true NOT NULL,
	"nota_sintaxis" text,
	"esquema" jsonb,
	CONSTRAINT "modelo_ia_codigo_unique" UNIQUE("codigo")
);
--> statement-breakpoint
CREATE TABLE "obra_referencia" (
	"id" text PRIMARY KEY NOT NULL,
	"slug" text NOT NULL,
	"titulo" text NOT NULL,
	"ano" integer,
	"genero_id" text,
	"nota" text,
	"enlace" text,
	"cita" text,
	"estado" text DEFAULT 'borrador' NOT NULL,
	"origen" text DEFAULT 'manual' NOT NULL,
	"modelo_origen" text,
	"importado_en" timestamp with time zone,
	"revisado_por" text,
	"revisado_en" timestamp with time zone,
	CONSTRAINT "obra_referencia_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "obra_referencia_persona" (
	"obra_id" text NOT NULL,
	"persona_id" text NOT NULL,
	"papel" text NOT NULL,
	"orden" integer DEFAULT 0 NOT NULL,
	CONSTRAINT "obra_referencia_persona_obra_id_persona_id_papel_pk" PRIMARY KEY("obra_id","persona_id","papel")
);
--> statement-breakpoint
CREATE TABLE "prompt" (
	"id" text PRIMARY KEY NOT NULL,
	"modelo_ia_id" text NOT NULL,
	"genero_id" text,
	"animo_id" text,
	"idioma" text DEFAULT 'en' NOT NULL,
	"contenido" jsonb NOT NULL,
	"notas" text,
	"valoracion" integer,
	"ejemplo_id" text,
	"preferido" boolean DEFAULT false NOT NULL,
	"visibilidad" text DEFAULT 'privado' NOT NULL,
	"estado" text DEFAULT 'borrador' NOT NULL,
	"creado_en" timestamp with time zone NOT NULL,
	"actualizado_en" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "termino" (
	"id" text PRIMARY KEY NOT NULL,
	"slug" text NOT NULL,
	"termino" text NOT NULL,
	CONSTRAINT "termino_slug_unique" UNIQUE("slug"),
	CONSTRAINT "termino_termino_unique" UNIQUE("termino")
);
--> statement-breakpoint
CREATE TABLE "termino_variante" (
	"termino_id" text NOT NULL,
	"forma" text NOT NULL,
	CONSTRAINT "termino_variante_termino_id_forma_pk" PRIMARY KEY("termino_id","forma")
);
--> statement-breakpoint
CREATE TABLE "documento_legal" (
	"id" text PRIMARY KEY NOT NULL,
	"slug" text NOT NULL,
	"titulo" text NOT NULL,
	"resumen" text DEFAULT '' NOT NULL,
	"orden" integer DEFAULT 0 NOT NULL,
	"version_vigente_id" text,
	CONSTRAINT "documento_legal_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "entrada" (
	"id" text PRIMARY KEY NOT NULL,
	"slug" text NOT NULL,
	"titulo" text NOT NULL,
	"fecha" text NOT NULL,
	"resumen" text DEFAULT '' NOT NULL,
	"cuerpo" text DEFAULT '' NOT NULL,
	"imagen_id" text,
	"estado" text DEFAULT 'borrador' NOT NULL,
	"creado_en" timestamp with time zone NOT NULL,
	"actualizado_en" timestamp with time zone,
	CONSTRAINT "entrada_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "entrada_cancion" (
	"entrada_id" text NOT NULL,
	"cancion_id" text NOT NULL,
	CONSTRAINT "entrada_cancion_entrada_id_cancion_id_pk" PRIMARY KEY("entrada_id","cancion_id")
);
--> statement-breakpoint
CREATE TABLE "entrada_etiqueta" (
	"entrada_id" text NOT NULL,
	"etiqueta_id" text NOT NULL,
	CONSTRAINT "entrada_etiqueta_entrada_id_etiqueta_id_pk" PRIMARY KEY("entrada_id","etiqueta_id")
);
--> statement-breakpoint
CREATE TABLE "pagina" (
	"id" text PRIMARY KEY NOT NULL,
	"slug" text NOT NULL,
	"titulo" text NOT NULL,
	"descripcion" text DEFAULT '' NOT NULL,
	"cuerpo" text DEFAULT '' NOT NULL,
	"estado" text DEFAULT 'borrador' NOT NULL,
	"creado_en" timestamp with time zone NOT NULL,
	"actualizado_en" timestamp with time zone,
	CONSTRAINT "pagina_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "redireccion" (
	"desde" text PRIMARY KEY NOT NULL,
	"hacia" text NOT NULL,
	"permanente" boolean DEFAULT true NOT NULL,
	"motivo" text,
	"creada_en" timestamp with time zone NOT NULL
);
--> statement-breakpoint
CREATE TABLE "version_legal" (
	"id" text PRIMARY KEY NOT NULL,
	"documento_id" text NOT NULL,
	"cuerpo" text NOT NULL,
	"vigente_desde" timestamp with time zone NOT NULL,
	"resumen_cambios" text
);
--> statement-breakpoint
CREATE TABLE "contador_factura" (
	"serie" text PRIMARY KEY NOT NULL,
	"ultimo_numero" integer DEFAULT 0 NOT NULL,
	"actualizado_en" timestamp with time zone NOT NULL
);
--> statement-breakpoint
CREATE TABLE "datos_facturacion" (
	"id" text PRIMARY KEY NOT NULL,
	"usuario_id" text NOT NULL,
	"tipo" text DEFAULT 'particular' NOT NULL,
	"nombre" text NOT NULL,
	"nif" text,
	"direccion" text,
	"cp" text,
	"ciudad" text,
	"provincia" text,
	"pais_codigo" char(2) NOT NULL,
	"predeterminada" boolean DEFAULT false NOT NULL,
	"creado_en" timestamp with time zone NOT NULL,
	"actualizado_en" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "descarga" (
	"id" text PRIMARY KEY NOT NULL,
	"usuario_id" text NOT NULL,
	"cancion_slug" text NOT NULL,
	"bajada_en" timestamp with time zone NOT NULL,
	"ip" text,
	"agente" text
);
--> statement-breakpoint
CREATE TABLE "factura" (
	"id" text PRIMARY KEY NOT NULL,
	"pedido_id" text NOT NULL,
	"serie" text NOT NULL,
	"numero" integer NOT NULL,
	"tipo" text DEFAULT 'normal' NOT NULL,
	"rectifica_a_id" text,
	"fecha" timestamp with time zone NOT NULL,
	"base_centimos" integer NOT NULL,
	"iva_tipo" integer NOT NULL,
	"iva_cuota_centimos" integer NOT NULL,
	"total_centimos" integer NOT NULL,
	"moneda" text DEFAULT 'eur' NOT NULL,
	"datos_congelados" jsonb NOT NULL,
	"pais_codigo" char(2) NOT NULL
);
--> statement-breakpoint
CREATE TABLE "reproduccion" (
	"id" text PRIMARY KEY NOT NULL,
	"cancion_slug" text NOT NULL,
	"usuario_id" text,
	"segundos" integer DEFAULT 0 NOT NULL,
	"dia" text NOT NULL,
	"creado_en" timestamp with time zone NOT NULL
);
--> statement-breakpoint
ALTER TABLE "persona" ADD CONSTRAINT "persona_pais_codigo_pais_codigo_fk" FOREIGN KEY ("pais_codigo") REFERENCES "public"."pais"("codigo") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "persona" ADD CONSTRAINT "persona_retrato_id_medio_id_fk" FOREIGN KEY ("retrato_id") REFERENCES "public"."medio"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "album" ADD CONSTRAINT "album_estilo_id_estilo_id_fk" FOREIGN KEY ("estilo_id") REFERENCES "public"."estilo"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "album" ADD CONSTRAINT "album_portada_id_medio_id_fk" FOREIGN KEY ("portada_id") REFERENCES "public"."medio"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "album" ADD CONSTRAINT "album_pais_edicion_codigo_pais_codigo_fk" FOREIGN KEY ("pais_edicion_codigo") REFERENCES "public"."pais"("codigo") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "album_enlace" ADD CONSTRAINT "album_enlace_album_id_album_id_fk" FOREIGN KEY ("album_id") REFERENCES "public"."album"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "autoria" ADD CONSTRAINT "autoria_cancion_id_cancion_id_fk" FOREIGN KEY ("cancion_id") REFERENCES "public"."cancion"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "autoria" ADD CONSTRAINT "autoria_persona_id_persona_id_fk" FOREIGN KEY ("persona_id") REFERENCES "public"."persona"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "cancion" ADD CONSTRAINT "cancion_estilo_id_estilo_id_fk" FOREIGN KEY ("estilo_id") REFERENCES "public"."estilo"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "cancion" ADD CONSTRAINT "cancion_album_id_album_id_fk" FOREIGN KEY ("album_id") REFERENCES "public"."album"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "cancion" ADD CONSTRAINT "cancion_audio_id_medio_id_fk" FOREIGN KEY ("audio_id") REFERENCES "public"."medio"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "cancion" ADD CONSTRAINT "cancion_muestra_id_medio_id_fk" FOREIGN KEY ("muestra_id") REFERENCES "public"."medio"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "cancion" ADD CONSTRAINT "cancion_portada_id_medio_id_fk" FOREIGN KEY ("portada_id") REFERENCES "public"."medio"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "cancion_enlace" ADD CONSTRAINT "cancion_enlace_cancion_id_cancion_id_fk" FOREIGN KEY ("cancion_id") REFERENCES "public"."cancion"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "cancion_etiqueta" ADD CONSTRAINT "cancion_etiqueta_cancion_id_cancion_id_fk" FOREIGN KEY ("cancion_id") REFERENCES "public"."cancion"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "cancion_etiqueta" ADD CONSTRAINT "cancion_etiqueta_etiqueta_id_etiqueta_id_fk" FOREIGN KEY ("etiqueta_id") REFERENCES "public"."etiqueta"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "cancion_persona" ADD CONSTRAINT "cancion_persona_cancion_id_cancion_id_fk" FOREIGN KEY ("cancion_id") REFERENCES "public"."cancion"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "cancion_persona" ADD CONSTRAINT "cancion_persona_persona_id_persona_id_fk" FOREIGN KEY ("persona_id") REFERENCES "public"."persona"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "credito_album" ADD CONSTRAINT "credito_album_album_id_album_id_fk" FOREIGN KEY ("album_id") REFERENCES "public"."album"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "credito_album" ADD CONSTRAINT "credito_album_persona_id_persona_id_fk" FOREIGN KEY ("persona_id") REFERENCES "public"."persona"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "estilo" ADD CONSTRAINT "estilo_imagen_id_medio_id_fk" FOREIGN KEY ("imagen_id") REFERENCES "public"."medio"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "registro_obra" ADD CONSTRAINT "registro_obra_cancion_id_cancion_id_fk" FOREIGN KEY ("cancion_id") REFERENCES "public"."cancion"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "traduccion" ADD CONSTRAINT "traduccion_cancion_id_cancion_id_fk" FOREIGN KEY ("cancion_id") REFERENCES "public"."cancion"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "video" ADD CONSTRAINT "video_cancion_id_cancion_id_fk" FOREIGN KEY ("cancion_id") REFERENCES "public"."cancion"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "acepcion" ADD CONSTRAINT "acepcion_termino_id_termino_id_fk" FOREIGN KEY ("termino_id") REFERENCES "public"."termino"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "cancion_genero" ADD CONSTRAINT "cancion_genero_cancion_id_cancion_id_fk" FOREIGN KEY ("cancion_id") REFERENCES "public"."cancion"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "cancion_genero" ADD CONSTRAINT "cancion_genero_genero_id_genero_id_fk" FOREIGN KEY ("genero_id") REFERENCES "public"."genero"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "capitulo" ADD CONSTRAINT "capitulo_genero_id_genero_id_fk" FOREIGN KEY ("genero_id") REFERENCES "public"."genero"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "capitulo_acepcion" ADD CONSTRAINT "capitulo_acepcion_capitulo_id_capitulo_id_fk" FOREIGN KEY ("capitulo_id") REFERENCES "public"."capitulo"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "capitulo_acepcion" ADD CONSTRAINT "capitulo_acepcion_acepcion_id_acepcion_id_fk" FOREIGN KEY ("acepcion_id") REFERENCES "public"."acepcion"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_alias" ADD CONSTRAINT "genero_alias_genero_id_genero_id_fk" FOREIGN KEY ("genero_id") REFERENCES "public"."genero"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_alias" ADD CONSTRAINT "genero_alias_pais_codigo_pais_codigo_fk" FOREIGN KEY ("pais_codigo") REFERENCES "public"."pais"("codigo") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_compas" ADD CONSTRAINT "genero_compas_genero_id_genero_id_fk" FOREIGN KEY ("genero_id") REFERENCES "public"."genero"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_compas" ADD CONSTRAINT "genero_compas_compas_id_compas_id_fk" FOREIGN KEY ("compas_id") REFERENCES "public"."compas"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_instrumento" ADD CONSTRAINT "genero_instrumento_genero_id_genero_id_fk" FOREIGN KEY ("genero_id") REFERENCES "public"."genero"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_instrumento" ADD CONSTRAINT "genero_instrumento_instrumento_id_instrumento_id_fk" FOREIGN KEY ("instrumento_id") REFERENCES "public"."instrumento"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_metro" ADD CONSTRAINT "genero_metro_genero_id_genero_id_fk" FOREIGN KEY ("genero_id") REFERENCES "public"."genero"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_metro" ADD CONSTRAINT "genero_metro_metro_id_metro_id_fk" FOREIGN KEY ("metro_id") REFERENCES "public"."metro"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_origen" ADD CONSTRAINT "genero_origen_genero_id_genero_id_fk" FOREIGN KEY ("genero_id") REFERENCES "public"."genero"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_origen" ADD CONSTRAINT "genero_origen_origen_id_genero_id_fk" FOREIGN KEY ("origen_id") REFERENCES "public"."genero"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_pais" ADD CONSTRAINT "genero_pais_genero_id_genero_id_fk" FOREIGN KEY ("genero_id") REFERENCES "public"."genero"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_pais" ADD CONSTRAINT "genero_pais_pais_codigo_pais_codigo_fk" FOREIGN KEY ("pais_codigo") REFERENCES "public"."pais"("codigo") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_persona" ADD CONSTRAINT "genero_persona_genero_id_genero_id_fk" FOREIGN KEY ("genero_id") REFERENCES "public"."genero"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_persona" ADD CONSTRAINT "genero_persona_persona_id_persona_id_fk" FOREIGN KEY ("persona_id") REFERENCES "public"."persona"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_seccion" ADD CONSTRAINT "genero_seccion_genero_id_genero_id_fk" FOREIGN KEY ("genero_id") REFERENCES "public"."genero"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_tema" ADD CONSTRAINT "genero_tema_genero_id_genero_id_fk" FOREIGN KEY ("genero_id") REFERENCES "public"."genero"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "genero_tema" ADD CONSTRAINT "genero_tema_etiqueta_id_etiqueta_id_fk" FOREIGN KEY ("etiqueta_id") REFERENCES "public"."etiqueta"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "instrumento" ADD CONSTRAINT "instrumento_pais_codigo_pais_codigo_fk" FOREIGN KEY ("pais_codigo") REFERENCES "public"."pais"("codigo") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "instrumento" ADD CONSTRAINT "instrumento_imagen_id_medio_id_fk" FOREIGN KEY ("imagen_id") REFERENCES "public"."medio"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "instrumento" ADD CONSTRAINT "instrumento_muestra_id_medio_id_fk" FOREIGN KEY ("muestra_id") REFERENCES "public"."medio"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "obra_referencia" ADD CONSTRAINT "obra_referencia_genero_id_genero_id_fk" FOREIGN KEY ("genero_id") REFERENCES "public"."genero"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "obra_referencia_persona" ADD CONSTRAINT "obra_referencia_persona_obra_id_obra_referencia_id_fk" FOREIGN KEY ("obra_id") REFERENCES "public"."obra_referencia"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "obra_referencia_persona" ADD CONSTRAINT "obra_referencia_persona_persona_id_persona_id_fk" FOREIGN KEY ("persona_id") REFERENCES "public"."persona"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "prompt" ADD CONSTRAINT "prompt_modelo_ia_id_modelo_ia_id_fk" FOREIGN KEY ("modelo_ia_id") REFERENCES "public"."modelo_ia"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "prompt" ADD CONSTRAINT "prompt_genero_id_genero_id_fk" FOREIGN KEY ("genero_id") REFERENCES "public"."genero"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "prompt" ADD CONSTRAINT "prompt_animo_id_animo_id_fk" FOREIGN KEY ("animo_id") REFERENCES "public"."animo"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "prompt" ADD CONSTRAINT "prompt_ejemplo_id_medio_id_fk" FOREIGN KEY ("ejemplo_id") REFERENCES "public"."medio"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "termino_variante" ADD CONSTRAINT "termino_variante_termino_id_termino_id_fk" FOREIGN KEY ("termino_id") REFERENCES "public"."termino"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "entrada" ADD CONSTRAINT "entrada_imagen_id_medio_id_fk" FOREIGN KEY ("imagen_id") REFERENCES "public"."medio"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "entrada_cancion" ADD CONSTRAINT "entrada_cancion_entrada_id_entrada_id_fk" FOREIGN KEY ("entrada_id") REFERENCES "public"."entrada"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "entrada_cancion" ADD CONSTRAINT "entrada_cancion_cancion_id_cancion_id_fk" FOREIGN KEY ("cancion_id") REFERENCES "public"."cancion"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "entrada_etiqueta" ADD CONSTRAINT "entrada_etiqueta_entrada_id_entrada_id_fk" FOREIGN KEY ("entrada_id") REFERENCES "public"."entrada"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "entrada_etiqueta" ADD CONSTRAINT "entrada_etiqueta_etiqueta_id_etiqueta_id_fk" FOREIGN KEY ("etiqueta_id") REFERENCES "public"."etiqueta"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "version_legal" ADD CONSTRAINT "version_legal_documento_id_documento_legal_id_fk" FOREIGN KEY ("documento_id") REFERENCES "public"."documento_legal"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "datos_facturacion" ADD CONSTRAINT "datos_facturacion_usuario_id_usuario_id_fk" FOREIGN KEY ("usuario_id") REFERENCES "public"."usuario"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "datos_facturacion" ADD CONSTRAINT "datos_facturacion_pais_codigo_pais_codigo_fk" FOREIGN KEY ("pais_codigo") REFERENCES "public"."pais"("codigo") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "descarga" ADD CONSTRAINT "descarga_usuario_id_usuario_id_fk" FOREIGN KEY ("usuario_id") REFERENCES "public"."usuario"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "factura" ADD CONSTRAINT "factura_pedido_id_pedido_id_fk" FOREIGN KEY ("pedido_id") REFERENCES "public"."pedido"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "factura" ADD CONSTRAINT "factura_pais_codigo_pais_codigo_fk" FOREIGN KEY ("pais_codigo") REFERENCES "public"."pais"("codigo") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "reproduccion" ADD CONSTRAINT "reproduccion_usuario_id_usuario_id_fk" FOREIGN KEY ("usuario_id") REFERENCES "public"."usuario"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "medio_clase" ON "medio" USING btree ("clase");--> statement-breakpoint
CREATE INDEX "medio_sha" ON "medio" USING btree ("sha256");--> statement-breakpoint
CREATE INDEX "persona_nombre" ON "persona" USING btree ("nombre");--> statement-breakpoint
CREATE INDEX "persona_pais" ON "persona" USING btree ("pais_codigo");--> statement-breakpoint
CREATE INDEX "album_estilo" ON "album" USING btree ("estilo_id");--> statement-breakpoint
CREATE INDEX "album_fecha" ON "album" USING btree ("fecha");--> statement-breakpoint
CREATE INDEX "cancion_estilo" ON "cancion" USING btree ("estilo_id");--> statement-breakpoint
CREATE INDEX "cancion_album" ON "cancion" USING btree ("album_id");--> statement-breakpoint
CREATE INDEX "cancion_version_de" ON "cancion" USING btree ("version_de_id");--> statement-breakpoint
CREATE INDEX "cancion_destacada" ON "cancion" USING btree ("destacada");--> statement-breakpoint
CREATE INDEX "credito_album_album" ON "credito_album" USING btree ("album_id");--> statement-breakpoint
CREATE UNIQUE INDEX "estilo_principal_unico" ON "estilo" USING btree ("principal") WHERE "estilo"."principal";--> statement-breakpoint
CREATE INDEX "estilo_orden" ON "estilo" USING btree ("orden");--> statement-breakpoint
CREATE INDEX "video_cancion" ON "video" USING btree ("cancion_id");--> statement-breakpoint
CREATE UNIQUE INDEX "acepcion_numero" ON "acepcion" USING btree ("termino_id","numero");--> statement-breakpoint
CREATE INDEX "capitulo_tipo" ON "capitulo" USING btree ("tipo","orden");--> statement-breakpoint
CREATE INDEX "genero_nombre" ON "genero" USING btree ("nombre");--> statement-breakpoint
CREATE INDEX "genero_origen_ano" ON "genero" USING btree ("origen_ano");--> statement-breakpoint
CREATE INDEX "genero_alias_nombre" ON "genero_alias" USING btree ("nombre");--> statement-breakpoint
CREATE INDEX "genero_origen_origen" ON "genero_origen" USING btree ("origen_id");--> statement-breakpoint
CREATE UNIQUE INDEX "genero_seccion_clave" ON "genero_seccion" USING btree ("genero_id","clave");--> statement-breakpoint
CREATE INDEX "instrumento_familia" ON "instrumento" USING btree ("familia");--> statement-breakpoint
CREATE INDEX "modelo_ia_familia" ON "modelo_ia" USING btree ("familia");--> statement-breakpoint
CREATE INDEX "obra_referencia_genero" ON "obra_referencia" USING btree ("genero_id");--> statement-breakpoint
CREATE INDEX "prompt_genero" ON "prompt" USING btree ("genero_id");--> statement-breakpoint
CREATE INDEX "prompt_modelo" ON "prompt" USING btree ("modelo_ia_id");--> statement-breakpoint
CREATE INDEX "prompt_animo" ON "prompt" USING btree ("animo_id");--> statement-breakpoint
CREATE INDEX "entrada_fecha" ON "entrada" USING btree ("fecha");--> statement-breakpoint
CREATE INDEX "redireccion_hacia" ON "redireccion" USING btree ("hacia");--> statement-breakpoint
CREATE INDEX "version_legal_documento" ON "version_legal" USING btree ("documento_id","vigente_desde");--> statement-breakpoint
CREATE INDEX "datos_facturacion_usuario" ON "datos_facturacion" USING btree ("usuario_id");--> statement-breakpoint
CREATE INDEX "descarga_usuario" ON "descarga" USING btree ("usuario_id");--> statement-breakpoint
CREATE INDEX "descarga_fecha" ON "descarga" USING btree ("bajada_en");--> statement-breakpoint
CREATE UNIQUE INDEX "factura_serie_numero" ON "factura" USING btree ("serie","numero");--> statement-breakpoint
CREATE INDEX "factura_pedido" ON "factura" USING btree ("pedido_id");--> statement-breakpoint
CREATE INDEX "factura_fecha" ON "factura" USING btree ("fecha");--> statement-breakpoint
CREATE INDEX "reproduccion_dia" ON "reproduccion" USING btree ("dia");--> statement-breakpoint
CREATE INDEX "reproduccion_cancion" ON "reproduccion" USING btree ("cancion_slug");--> statement-breakpoint
CREATE INDEX "reproduccion_usuario" ON "reproduccion" USING btree ("usuario_id");--> statement-breakpoint
-- ===========================================================================
-- Lo que Drizzle no sabe expresar, y que es justo lo que impide que el modelo
-- mienta. Añadido a mano; si algún día se regenera esta migración, esto se
-- vuelve a poner.
-- ===========================================================================

-- --- Las versiones de una canción -----------------------------------------
--
-- La clave foránea no se declara en el esquema porque Drizzle no deja
-- referenciar una tabla dentro de su propia definición.
ALTER TABLE "cancion" ADD CONSTRAINT "cancion_version_de_fk"
	FOREIGN KEY ("version_de_id") REFERENCES "cancion"("id") ON DELETE cascade;
--> statement-breakpoint

-- Una canción no es versión de sí misma.
ALTER TABLE "cancion" ADD CONSTRAINT "cancion_no_es_su_propia_version"
	CHECK ("version_de_id" IS NULL OR "version_de_id" <> "id");
--> statement-breakpoint

-- Y una versión no puede tener versiones: un solo nivel.
--
-- Se hace con una clave foránea compuesta contra una columna generada, no con
-- un disparador. `es_version` se calcula sola, la pareja (id, es_version) es
-- única, y la referencia exige que el padre tenga `es_version` a falso —lo que
-- garantiza la columna `padre_no_es_version`, fija en falso—. Con eso quedan
-- descartados a la vez el nivel tres y los ciclos, sin escribir procedimientos.
ALTER TABLE "cancion" ADD COLUMN "es_version" boolean
	GENERATED ALWAYS AS ("version_de_id" IS NOT NULL) STORED;
--> statement-breakpoint
ALTER TABLE "cancion" ADD COLUMN "padre_no_es_version" boolean
	GENERATED ALWAYS AS (CASE WHEN "version_de_id" IS NULL THEN NULL ELSE false END) STORED;
--> statement-breakpoint
ALTER TABLE "cancion" ADD CONSTRAINT "cancion_id_es_version_unica"
	UNIQUE ("id", "es_version");
--> statement-breakpoint
ALTER TABLE "cancion" ADD CONSTRAINT "cancion_version_de_principal"
	FOREIGN KEY ("version_de_id", "padre_no_es_version")
	REFERENCES "cancion"("id", "es_version");
--> statement-breakpoint

-- --- La vista de lo que se lista y se cuenta -------------------------------
--
-- Cuatro funciones devuelven listas de canciones y siete rutas las pintan, más
-- los recuentos por estilo y por etiqueta. Si a una se le olvida el filtro, el
-- catálogo dice veinticinco temas donde hay dieciocho y nadie lo nota. Lo
-- normal es consultar esto; pedir versiones es lo que hay que escribir a
-- propósito.
CREATE VIEW "cancion_principal" AS
	SELECT * FROM "cancion" WHERE "version_de_id" IS NULL;
--> statement-breakpoint

-- --- El reparto de autoría suma cien ---------------------------------------
--
-- Un reparto que no suma cien no es un dato incompleto: es dinero mal
-- repartido. Se comprueba al terminar la transacción y no en cada fila, porque
-- durante una inserción de tres autores los estados intermedios no suman.
CREATE FUNCTION "autoria_suma_cien"() RETURNS trigger AS $$
DECLARE
	total numeric;
	obra text;
BEGIN
	obra := COALESCE(NEW."cancion_id", OLD."cancion_id");
	SELECT COALESCE(SUM("porcentaje"), 0) INTO total
		FROM "autoria" WHERE "cancion_id" = obra;
	IF total <> 0 AND total <> 100 THEN
		RAISE EXCEPTION 'El reparto de autoría de % suma %, y tiene que sumar 100', obra, total;
	END IF;
	RETURN NULL;
END;
$$ LANGUAGE plpgsql;
--> statement-breakpoint
CREATE CONSTRAINT TRIGGER "autoria_suma_cien_trg"
	AFTER INSERT OR UPDATE OR DELETE ON "autoria"
	DEFERRABLE INITIALLY DEFERRED
	FOR EACH ROW EXECUTE FUNCTION "autoria_suma_cien"();
--> statement-breakpoint

-- --- Un solo estilo principal ----------------------------------------------
--
-- Lo genera Drizzle a partir del `uniqueIndex(...).where(...)` del esquema, con
-- su índice parcial: solo entran las filas marcadas, así que la unicidad se
-- aplica entre ellas y las demás no estorban. Antes era un acuerdo escrito en
-- un comentario. Aquí no hay nada que añadir.

-- --- Una arista principal por género ----------------------------------------
CREATE UNIQUE INDEX "genero_origen_principal_unico" ON "genero_origen" ("genero_id")
	WHERE "principal";
--> statement-breakpoint

-- Un género no deriva de sí mismo.
ALTER TABLE "genero_origen" ADD CONSTRAINT "genero_origen_no_reflexivo"
	CHECK ("genero_id" <> "origen_id");
--> statement-breakpoint

-- --- Sin ciclos en el linaje de géneros -------------------------------------
--
-- Aquí sí hace falta un disparador: un grafo admite que A derive de B y B de A,
-- y una consulta recursiva sobre eso no termina. Con un catálogo de cien
-- géneros, comprobarlo al insertar no cuesta nada.
CREATE FUNCTION "genero_sin_ciclos"() RETURNS trigger AS $$
BEGIN
	IF EXISTS (
		WITH RECURSIVE "sube"("id") AS (
			SELECT NEW."origen_id"
			UNION
			SELECT "o"."origen_id" FROM "genero_origen" "o"
				JOIN "sube" ON "o"."genero_id" = "sube"."id"
		)
		SELECT 1 FROM "sube" WHERE "id" = NEW."genero_id"
	) THEN
		RAISE EXCEPTION 'Ese origen crearía un ciclo en el linaje de géneros';
	END IF;
	RETURN NEW;
END;
$$ LANGUAGE plpgsql;
--> statement-breakpoint
CREATE TRIGGER "genero_sin_ciclos_trg"
	BEFORE INSERT OR UPDATE ON "genero_origen"
	FOR EACH ROW EXECUTE FUNCTION "genero_sin_ciclos"();
--> statement-breakpoint

-- --- La valoración va de uno a cinco ----------------------------------------
ALTER TABLE "valoracion" ADD CONSTRAINT "valoracion_de_uno_a_cinco"
	CHECK ("puntuacion" BETWEEN 1 AND 5);
--> statement-breakpoint

-- --- El documento legal apunta a su versión vigente --------------------------
ALTER TABLE "documento_legal" ADD CONSTRAINT "documento_legal_version_vigente_fk"
	FOREIGN KEY ("version_vigente_id") REFERENCES "version_legal"("id") ON DELETE set null;
--> statement-breakpoint

-- --- Una rectificativa apunta a la factura que rectifica ---------------------
ALTER TABLE "factura" ADD CONSTRAINT "factura_rectifica_fk"
	FOREIGN KEY ("rectifica_a_id") REFERENCES "factura"("id");
--> statement-breakpoint
ALTER TABLE "factura" ADD CONSTRAINT "factura_rectificativa_apunta"
	CHECK (("tipo" = 'rectificativa') = ("rectifica_a_id" IS NOT NULL));
--> statement-breakpoint

-- --- El metro enlaza con el glosario -----------------------------------------
ALTER TABLE "metro" ADD CONSTRAINT "metro_termino_fk"
	FOREIGN KEY ("termino_id") REFERENCES "termino"("id") ON DELETE set null;