-- Provisional a 04-03
aforament INTEGER NOT NULL

-- Definitiu
aforament SMALLINT NOT NULL   -- + CHECK (aforament > 0 AND aforament <= 2000)

SELECT 3.10::double precision
     + 2.20::double precision
     + 4.30::double precision  AS total_flotant,
       3.10::numeric
     + 2.20::numeric
     + 4.30::numeric           AS total_numeric;

    total_flotant    | total_numeric
---------------------+---------------
   9.600000000000001 |          9.60
(1 row)

SELECT (3.10::double precision + 2.20::double precision + 4.30::double precision) = 9.60
           AS coincideix_flotant,
       (3.10::numeric + 2.20::numeric + 4.30::numeric) = 9.60
           AS coincideix_numeric;

 coincideix_flotant | coincideix_numeric
--------------------+--------------------
 f                  | t
(1 row)

SELECT SUM(0.20::real)          AS total_real,
       SUM(0.20::numeric(6,2))  AS total_numeric
  FROM generate_series(1, 1000);

 total_real | total_numeric
------------+---------------
  200.00003 |        200.00
(1 row)

INSERT INTO multes (soci_id, motiu, import) VALUES (15, 'retard', 12.348);
SELECT import FROM multes WHERE soci_id = 15;

 import
--------
  12.35

INSERT INTO multes (soci_id, motiu, import) VALUES (15, 'perdua', 25000.00);

ERROR:  numeric field overflow
DETAIL:  A field with precision 6, scale 2 must round to an absolute value less than 10^4.

SELECT 'ca'::char(5) = 'ca'::text        AS iguals,
       length('ca'::char(5))             AS longitud,
       '[' || 'ca'::char(5) || ']'       AS visualitzacio;

 iguals | longitud | visualitzacio
--------+----------+---------------
 t      |        2 | [ca   ]
(1 row)

INSERT INTO materials (tipus_material, titol, idioma)
VALUES ('llibre', 'Història general i natural de les Índies, illes i terra ferma del mar oceà, amb les anotacions i notes de l''il·lustríssim senyor cronista de la Corona de Castella, edició commemorativa del cinquè centenari', 'ca');

ERROR:  value too long for type character varying(200)

titol TEXT NOT NULL,
CONSTRAINT chk_materials_titol_longitud CHECK (char_length(titol) BETWEEN 1 AND 300)

CREATE TABLE prova_hores (
    amb_tz    TIMESTAMPTZ,
    sense_tz  TIMESTAMP
);

SET TIME ZONE 'Europe/Madrid';
INSERT INTO prova_hores VALUES ('2026-05-14 18:00:00', '2026-05-14 18:00:00');

SELECT * FROM prova_hores;

         amb_tz         |      sense_tz
------------------------+---------------------
 2026-05-14 18:00:00+02 | 2026-05-14 18:00:00

SET TIME ZONE 'America/Bogota';
SELECT * FROM prova_hores;

         amb_tz         |      sense_tz
------------------------+---------------------
 2026-05-14 11:00:00-05 | 2026-05-14 18:00:00

SELECT p.prestec_id,
       p.data_prestec,
       p.data_prestec + INTERVAL '21 days' AS venciment_calculat,
       p.data_devolucio_prevista,
       CURRENT_DATE - p.data_devolucio_prevista AS dies_retard
  FROM prestecs p
 WHERE p.data_devolucio IS NULL
   AND p.data_devolucio_prevista < CURRENT_DATE
 ORDER BY dies_retard DESC;

 prestec_id | data_prestec | venciment_calculat  | data_devolucio_prevista | dies_retard
------------+--------------+---------------------+-------------------------+-------------
       4188 | 2026-06-05   | 2026-06-26 00:00:00 | 2026-06-26              |          37
       4201 | 2026-06-14   | 2026-07-05 00:00:00 | 2026-07-05              |          28
(2 rows)

SELECT nom, cognoms FROM socis WHERE actiu;         -- sense '= TRUE'
SELECT nom, cognoms FROM socis WHERE NOT actiu;

ALTER TABLE esdeveniments ADD COLUMN slug_public UUID NOT NULL DEFAULT gen_random_uuid();
ALTER TABLE esdeveniments ADD CONSTRAINT uq_esdeveniments_slug UNIQUE (slug_public);

CREATE TYPE estat_multa AS ENUM ('pendent', 'pagada', 'condonada', 'anullada');
ALTER TABLE multes ALTER COLUMN estat TYPE estat_multa USING estat::estat_multa;

INSERT INTO multes (soci_id, motiu, import, estat)
VALUES (15, 'retard', 4.00, 'pagadaa');

ERROR:  invalid input value for enum estat_multa: "pagadaa"
LINE 2: VALUES (15, 'retard', 4.00, 'pagadaa');

CREATE TABLE estats_multa (
    codi     VARCHAR(15) PRIMARY KEY,
    nom      TEXT NOT NULL,
    es_final BOOLEAN NOT NULL DEFAULT FALSE,
    ordre    SMALLINT NOT NULL
);
ALTER TABLE multes
    ADD CONSTRAINT fk_multes_estat FOREIGN KEY (estat) REFERENCES estats_multa (codi);

ALTER TABLE multes ADD CONSTRAINT chk_multes_estat
    CHECK (estat IN ('pendent','pagada','condonada','anullada'));

INSERT INTO multes (soci_id, motiu, import, estat)
VALUES (15, 'retard', 4.00, 'pagadaa');

ERROR:  new row for relation "multes" violates check constraint "chk_multes_estat"
DETAIL:  Failing row contains (312, 15, null, retard, 4.00, 2026-08-02, pagadaa).

-- Alternativa a la taula subtitols_dvd
ALTER TABLE materials_dvd ADD COLUMN subtitols TEXT[];
UPDATE materials_dvd SET subtitols = ARRAY['es','ca','en'] WHERE material_id = 1204;

SELECT material_id FROM materials_dvd WHERE subtitols @> ARRAY['ca'];

 material_id
-------------
        1204
(1 row)

ALTER TABLE informes_esdeveniment ADD COLUMN respostes_enquesta JSONB;

UPDATE informes_esdeveniment
   SET respostes_enquesta = '{
        "versio_questionari": 3,
        "num_respostes": 18,
        "preguntes": [
          {"id": "p1", "text": "Valoració general",      "mitjana": 4.3},
          {"id": "p2", "text": "Adequació de la sala",    "mitjana": 3.8},
          {"id": "p5", "text": "Repetiries?",             "si": 16, "no": 2}
        ]}'::jsonb
 WHERE esdeveniment_id = 47;

SELECT esdeveniment_id, respostes_enquesta -> 'num_respostes' AS n
  FROM informes_esdeveniment
 WHERE respostes_enquesta @> '{"versio_questionari": 3}';

 esdeveniment_id | n
-----------------+----
              47 | 18
(1 row)

ALTER TABLE materials ADD COLUMN portada_url  TEXT;
ALTER TABLE materials ADD COLUMN portada_hash CHAR(64);   -- SHA-256, per detectar duplicats

CREATE DATABASE biblioredb ENCODING 'UTF8' LC_COLLATE 'es_ES.UTF-8' LC_CTYPE 'es_ES.UTF-8';

SELECT titol FROM (VALUES ('Ángeles'),('Antología'),('Àngels'),('anatomía'),('Zoo'))
    AS t(titol)
 ORDER BY titol COLLATE "C";

   titol
------------
 Antología
 Zoo
 anatomía
 Ángeles
 Àngels
(5 rows)

SELECT titol FROM (VALUES ('Ángeles'),('Antología'),('Àngels'),('anatomía'),('Zoo'))
    AS t(titol)
 ORDER BY titol COLLATE "es-ES-x-icu";

   titol
------------
 anatomía
 Ángeles
 Àngels
 Antología
 Zoo
(5 rows)

-- Insensible a majúscules: ILIKE
SELECT titol FROM materials WHERE titol ILIKE '%mapa del temps%';

       titol
--------------------
 El mapa del temps
(1 row)

-- Insensible a accents: extensió unaccent
CREATE EXTENSION IF NOT EXISTS unaccent;
SELECT titol FROM materials
 WHERE unaccent(titol) ILIKE unaccent('%angels%');

      titol
-------------------
 Àngels de paper
(1 row)

CREATE COLLATION cerca_es (
    provider = icu,
    locale = 'es-ES-u-ks-level1',   -- nivell 1: ignora accents i majúscules
    deterministic = false
);

-- A SQLite
CREATE TABLE prova (id INTEGER, import NUMERIC, data DATE);
INSERT INTO prova VALUES ('hola', 'molts diners', 'el dijous');
SELECT * FROM prova;

id    import        data
----  ------------  ----------
hola  molts diners  el dijous

ERROR:  invalid input syntax for type integer: "hola"
LINE 1: INSERT INTO prova VALUES ('hola', 'molts diners', 'el dijous');

CREATE TABLE prova (id INTEGER, import REAL, data TEXT) STRICT;
INSERT INTO prova VALUES ('hola', 1.0, '2026-05-14');

Runtime error: cannot store TEXT value in INTEGER column prova.id

data_alta    DATE        NOT NULL DEFAULT CURRENT_DATE,
data_pagament TIMESTAMPTZ NOT NULL DEFAULT now(),
estat        TEXT        NOT NULL DEFAULT 'programat',
publicat     BOOLEAN     NOT NULL DEFAULT FALSE,
acompanyants SMALLINT    NOT NULL DEFAULT 0,
honoraris    NUMERIC(8,2) NOT NULL DEFAULT 0

INSERT INTO esdeveniments (titol, tipus_esdeveniment_id, inici, fi, places_ofertes)
VALUES ('Contacontes de tardor', 4, '2026-10-03 17:30+02', '2026-10-03 18:30+02', 30);

SELECT titol, estat, publicat FROM esdeveniments WHERE titol = 'Contacontes de tardor';

         titol         |   estat    | publicat
-----------------------+------------+----------
 Contacontes de tardor | programat  | f

INSERT INTO esdeveniments (titol, tipus_esdeveniment_id, inici, fi, places_ofertes, estat)
VALUES ('Taller de haiku', 2, '2026-10-10 18:00+02', '2026-10-10 20:00+02', 15, NULL);

ERROR:  null value in column "estat" of relation "esdeveniments" violates not-null constraint

CONSTRAINT uq_materials_llibre_isbn      UNIQUE (isbn),
CONSTRAINT uq_exemplars_codi             UNIQUE (codi),
CONSTRAINT uq_ponents_email              UNIQUE (email),
CONSTRAINT uq_sales_sucursal_nom         UNIQUE (sucursal_id, nom),
CONSTRAINT uq_materials_revista_issn_num UNIQUE (issn, numero)

INSERT INTO sales (sucursal_id, nom, aforament) VALUES (1, 'Sala Polivalent', 60);
INSERT INTO sales (sucursal_id, nom, aforament) VALUES (2, 'Sala Polivalent', 45);

INSERT 0 1
INSERT 0 1

INSERT INTO sales (sucursal_id, nom, aforament) VALUES (1, 'Sala Polivalent', 60);

ERROR:  duplicate key value violates unique constraint "uq_sales_sucursal_nom"
DETAIL:  Key (sucursal_id, nom)=(1, Sala Polivalent) already exists.

INSERT INTO ponents (nom, cognoms, email) VALUES ('Elena', 'Roig', NULL);
INSERT INTO ponents (nom, cognoms, email) VALUES ('Marc',  'Duran', NULL);
INSERT INTO ponents (nom, cognoms, email) VALUES ('Aina',  'Ferrer', NULL);
SELECT count(*) FROM ponents WHERE email IS NULL;

 count
-------
     3

INSERT INTO multes (soci_id, prestec_id, motiu, import) VALUES (16, NULL, 'perdua', 18.00);
INSERT INTO multes (soci_id, prestec_id, motiu, import) VALUES (16, NULL, 'perdua', 18.00);
INSERT INTO multes (soci_id, prestec_id, motiu, import) VALUES (16, NULL, 'perdua', 18.00);

INSERT 0 1
INSERT 0 1
INSERT 0 1

ALTER TABLE multes DROP CONSTRAINT uq_multes_prestec_motiu;
ALTER TABLE multes ADD CONSTRAINT uq_multes_prestec_motiu
    UNIQUE NULLS NOT DISTINCT (prestec_id, motiu);

INSERT INTO multes (soci_id, prestec_id, motiu, import) VALUES (16, NULL, 'perdua', 18.00);

ERROR:  duplicate key value violates unique constraint "uq_multes_prestec_motiu"
DETAIL:  Key (prestec_id, motiu)=(null, perdua) already exists.

-- Aquestes dues declaracions són gairebé equivalents
CONSTRAINT pk_sales PRIMARY KEY (sala_id)

sala_id INTEGER NOT NULL,
CONSTRAINT uq_sales_id UNIQUE (sala_id)

CONSTRAINT chk_multes_import_no_negatiu CHECK (import >= 0),          -- RN5
CONSTRAINT chk_sales_aforament_positiu  CHECK (aforament > 0),
CONSTRAINT chk_inscripcions_acompanyants CHECK (acompanyants BETWEEN 0 AND 3),  -- R6
CONSTRAINT chk_multes_motiu   CHECK (motiu IN ('retard','deteriorament','perdua')),
CONSTRAINT chk_multes_estat   CHECK (estat IN ('pendent','pagada','condonada','anullada'))

INSERT INTO multes (soci_id, motiu, import) VALUES (14, 'retard', -40.00);

ERROR:  new row for relation "multes" violates check constraint "chk_multes_import_no_negatiu"
DETAIL:  Failing row contains (318, 14, null, retard, -40.00, 2026-08-02, pendent).

INSERT INTO inscripcions (esdeveniment_id, soci_id, acompanyants) VALUES (47, 16, 7);

ERROR:  new row for relation "inscripcions" violates check constraint "chk_inscripcions_acompanyants"
DETAIL:  Failing row contains (47, 16, 2026-08-02 12:14:03.221+02, confirmada, 7).

-- RN3: l'esdeveniment no pot acabar abans de començar
CONSTRAINT chk_esdeveniments_fi_posterior CHECK (fi > inici),

-- El préstec no es retorna abans de prestar-se
CONSTRAINT chk_prestecs_devolucio CHECK (data_devolucio IS NULL
                                      OR data_devolucio >= data_prestec),

-- RN8: un esdeveniment publicat necessita sala i places
CONSTRAINT chk_esdeveniments_publicat CHECK (NOT publicat
                                     OR (sala_id IS NOT NULL AND places_ofertes > 0))

INSERT INTO esdeveniments (titol, tipus_esdeveniment_id, inici, fi, places_ofertes)
VALUES ('Xerrada mal programada', 5, '2026-09-10 19:00+02', '2026-09-10 18:00+02', 40);

ERROR:  new row for relation "esdeveniments" violates check constraint "chk_esdeveniments_fi_posterior"
DETAIL:  Failing row contains (52, Xerrada mal programada, null, 5, null, 2026-09-10 19:00:00+02, 2026-09-10 18:00:00+02, 40, programat, f).

-- CHECK (data_devolucio >= data_prestec), sense contemplar NULL
INSERT INTO prestecs (soci_id, exemplar_id, data_prestec, data_devolucio_prevista)
VALUES (14, 3081, '2026-07-01', '2026-07-22');

INSERT 0 1

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE esdeveniments ADD CONSTRAINT excl_esdeveniments_encavalcament_sala
    EXCLUDE USING gist (
        sala_id WITH =,
        tstzrange(inici, fi) WITH &&
    ) WHERE (estat <> 'cancellat' AND sala_id IS NOT NULL);

INSERT INTO esdeveniments (titol, tipus_esdeveniment_id, sala_id, inici, fi, places_ofertes)
VALUES ('Club de lectura', 1, 3, '2026-09-17 18:00+02', '2026-09-17 20:00+02', 20);

INSERT INTO esdeveniments (titol, tipus_esdeveniment_id, sala_id, inici, fi, places_ofertes)
VALUES ('Presentació', 3, 3, '2026-09-17 19:00+02', '2026-09-17 21:00+02', 50);

INSERT 0 1
ERROR:  conflicting key value violates exclusion constraint "excl_esdeveniments_encavalcament_sala"
DETAIL:  Key (sala_id, tstzrange(inici, fi))=(3, ["2026-09-17 19:00:00+02","2026-09-17 21:00:00+02")) conflicts with existing key (sala_id, tstzrange(inici, fi))=(3, ["2026-09-17 18:00:00+02","2026-09-17 20:00:00+02")).

ALTER TABLE inscripcions ADD COLUMN places_ocupades SMALLINT
    GENERATED ALWAYS AS (1 + acompanyants) STORED;

ALTER TABLE esdeveniments ADD COLUMN durada_min INTEGER
    GENERATED ALWAYS AS (EXTRACT(EPOCH FROM (fi - inici)) / 60) STORED;

SELECT esdeveniment_id, inici, fi, durada_min FROM esdeveniments WHERE esdeveniment_id = 47;

 esdeveniment_id |         inici          |           fi           | durada_min
-----------------+------------------------+------------------------+------------
              47 | 2026-09-17 18:00:00+02 | 2026-09-17 20:00:00+02 |        120

UPDATE esdeveniments SET durada_min = 999 WHERE esdeveniment_id = 47;

ERROR:  column "durada_min" can only be updated to DEFAULT
DETAIL:  Column "durada_min" is a generated column.

CREATE DOMAIN dom_email AS TEXT
    CHECK (VALUE ~ '^[^@[:space:]]+@[^@[:space:]]+\.[A-Za-z]{2,}$');

CREATE DOMAIN dom_import_eur AS NUMERIC(8,2)
    CHECK (VALUE >= 0);

CREATE DOMAIN dom_idioma AS VARCHAR(5)
    CHECK (VALUE ~ '^[a-z]{2}(-[A-Z]{2})?$');

CREATE DOMAIN dom_codi_postal AS CHAR(5)
    CHECK (VALUE ~ '^[0-9]{5}$');

CREATE TABLE ponents (
    ponent_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    email     dom_email,
    ...
);

INSERT INTO ponents (nom, cognoms, email)
VALUES ('Elena', 'Roig', 'elena.roig-arrova-example.org');

ERROR:  value for domain dom_email violates check constraint "dom_email_check"

CREATE TABLE sales_sense_noms (
    sala_id     INTEGER PRIMARY KEY,
    sucursal_id INTEGER NOT NULL REFERENCES sucursals(sucursal_id),
    nom         TEXT NOT NULL,
    aforament   SMALLINT NOT NULL CHECK (aforament > 0),
    UNIQUE (sucursal_id, nom)
);

INSERT INTO sales_sense_noms VALUES (1, 1, 'Sala Polivalent', 0);

ERROR:  new row for relation "sales_sense_noms" violates check constraint "sales_sense_noms_aforament_check"

INSERT INTO sales VALUES (DEFAULT, 1, 'Sala Polivalent', 0);

ERROR:  new row for relation "sales" violates check constraint "chk_sales_aforament_positiu"

ALTER TABLE prestecs ADD CONSTRAINT chk_prestecs_recarrec CHECK (recarrec >= 0);

ERROR:  check constraint "chk_prestecs_recarrec" of relation "prestecs" is violated by some row

SELECT count(*) FROM prestecs WHERE recarrec < 0;

 count
-------
     3

UPDATE prestecs SET recarrec = 0 WHERE recarrec < 0;

UPDATE 3

ALTER TABLE prestecs
    ADD CONSTRAINT chk_prestecs_recarrec CHECK (recarrec >= 0) NOT VALID;

ALTER TABLE

ALTER TABLE prestecs VALIDATE CONSTRAINT chk_prestecs_recarrec;

ALTER TABLE

SELECT conname, convalidated FROM pg_constraint
 WHERE conrelid = 'prestecs'::regclass AND contype = 'c';

        conname         | convalidated
------------------------+--------------
 chk_prestecs_recarrec  | t

-- =====================================================================
-- BiblioRed · Migració V005: tipus definitius i restriccions
-- Tanca el mòdul 4. Referències R1..R14 i RN1..RN10 del document v1.0
-- =====================================================================

-- ---------------------------------------------------------------------
-- 0 · Dominis reutilitzables
-- ---------------------------------------------------------------------
CREATE DOMAIN dom_email AS TEXT
    CHECK (VALUE ~ '^[^@[:space:]]+@[^@[:space:]]+\.[A-Za-z]{2,}$');

CREATE DOMAIN dom_import_eur AS NUMERIC(8,2)
    CHECK (VALUE >= 0);                                          -- RN5

CREATE DOMAIN dom_idioma AS VARCHAR(5)
    CHECK (VALUE ~ '^[a-z]{2}(-[A-Z]{2})?$');

CREATE DOMAIN dom_codi_postal AS CHAR(5)
    CHECK (VALUE ~ '^[0-9]{5}$');

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE EXTENSION IF NOT EXISTS unaccent;

-- ---------------------------------------------------------------------
-- 1 · Catàleg de materials
-- ---------------------------------------------------------------------
CREATE TABLE materials (
    material_id    INTEGER  GENERATED BY DEFAULT AS IDENTITY,
    tipus_material VARCHAR(15) NOT NULL,
    titol          TEXT        NOT NULL,
    autor_id       INTEGER,
    editorial      TEXT,
    any_publicacio SMALLINT,
    idioma         dom_idioma  NOT NULL,
    data_alta      DATE        NOT NULL DEFAULT CURRENT_DATE,
    portada_url    TEXT,
    CONSTRAINT pk_materials            PRIMARY KEY (material_id),
    CONSTRAINT uq_materials_id_tipus   UNIQUE (material_id, tipus_material),
    CONSTRAINT chk_materials_tipus
        CHECK (tipus_material IN ('llibre','dvd','revista','audiollibre')),
    CONSTRAINT chk_materials_titol      CHECK (char_length(titol) BETWEEN 1 AND 300),
    CONSTRAINT chk_materials_any        CHECK (any_publicacio BETWEEN 1450 AND 2100),
    CONSTRAINT fk_materials_autor       FOREIGN KEY (autor_id)
        REFERENCES autors (autor_id) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE materials_llibre (
    material_id    INTEGER     NOT NULL,
    tipus_material VARCHAR(15) NOT NULL DEFAULT 'llibre',
    isbn           VARCHAR(13) COLLATE "C" NOT NULL,
    num_pagines    SMALLINT,
    enquadernacio  VARCHAR(20),
    CONSTRAINT pk_materials_llibre       PRIMARY KEY (material_id),
    CONSTRAINT uq_materials_llibre_isbn  UNIQUE (isbn),                   -- RN9
    CONSTRAINT chk_materials_llibre_tipus CHECK (tipus_material = 'llibre'),
    CONSTRAINT chk_materials_llibre_isbn CHECK (isbn ~ '^[0-9]{13}$'),
    CONSTRAINT chk_materials_llibre_pags CHECK (num_pagines IS NULL OR num_pagines > 0),
    CONSTRAINT chk_materials_llibre_enq
        CHECK (enquadernacio IS NULL
               OR enquadernacio IN ('tapa_dura','tapa_tova','espiral','butxaca')),
    CONSTRAINT fk_materials_llibre_material FOREIGN KEY (material_id, tipus_material)
        REFERENCES materials (material_id, tipus_material)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE materials_dvd (
    material_id    INTEGER     NOT NULL,
    tipus_material VARCHAR(15) NOT NULL DEFAULT 'dvd',
    durada_min     SMALLINT    NOT NULL,
    format_video   VARCHAR(15),
    codi_regio     SMALLINT,
    CONSTRAINT pk_materials_dvd        PRIMARY KEY (material_id),
    CONSTRAINT chk_materials_dvd_tipus CHECK (tipus_material = 'dvd'),
    CONSTRAINT chk_materials_dvd_dur   CHECK (durada_min > 0 AND durada_min < 1000),
    CONSTRAINT chk_materials_dvd_reg   CHECK (codi_regio IS NULL
                                              OR codi_regio BETWEEN 0 AND 8),
    CONSTRAINT fk_materials_dvd_material FOREIGN KEY (material_id, tipus_material)
        REFERENCES materials (material_id, tipus_material)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE materials_revista (
    material_id    INTEGER     NOT NULL,
    tipus_material VARCHAR(15) NOT NULL DEFAULT 'revista',
    issn           VARCHAR(9)  COLLATE "C" NOT NULL,
    numero         VARCHAR(20) NOT NULL,
    periodicitat   VARCHAR(20),
    CONSTRAINT pk_materials_revista        PRIMARY KEY (material_id),
    CONSTRAINT uq_materials_revista_issn   UNIQUE (issn, numero),        -- RN9
    CONSTRAINT chk_materials_revista_tipus CHECK (tipus_material = 'revista'),
    CONSTRAINT chk_materials_revista_issn  CHECK (issn ~ '^[0-9]{4}-[0-9]{3}[0-9X]$'),
    CONSTRAINT chk_materials_revista_per
        CHECK (periodicitat IS NULL
               OR periodicitat IN ('diaria','setmanal','quinzenal','mensual',
                                   'bimestral','trimestral','semestral','anual')),
    CONSTRAINT fk_materials_revista_material FOREIGN KEY (material_id, tipus_material)
        REFERENCES materials (material_id, tipus_material)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE materials_audiollibre (
    material_id    INTEGER     NOT NULL,
    tipus_material VARCHAR(15) NOT NULL DEFAULT 'audiollibre',
    durada_min     SMALLINT    NOT NULL,
    narrador       TEXT,
    format_audio   VARCHAR(15),
    CONSTRAINT pk_materials_audiollibre       PRIMARY KEY (material_id),
    CONSTRAINT chk_materials_audio_tipus      CHECK (tipus_material = 'audiollibre'),
    CONSTRAINT chk_materials_audio_dur        CHECK (durada_min > 0),
    CONSTRAINT chk_materials_audio_format
        CHECK (format_audio IS NULL OR format_audio IN ('mp3','m4b','flac','ogg')),
    CONSTRAINT fk_materials_audio_material FOREIGN KEY (material_id, tipus_material)
        REFERENCES materials (material_id, tipus_material)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE subtitols_dvd (
    material_id INTEGER    NOT NULL,
    idioma      dom_idioma NOT NULL,
    CONSTRAINT pk_subtitols_dvd PRIMARY KEY (material_id, idioma),
    CONSTRAINT fk_subtitols_dvd_material FOREIGN KEY (material_id)
        REFERENCES materials_dvd (material_id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- ---------------------------------------------------------------------
-- 2 · Socis: telèfons (R12) i adreces de sucursal (R13)
-- ---------------------------------------------------------------------
CREATE TABLE telefons_soci (
    soci_id INTEGER     NOT NULL,
    numero  VARCHAR(20) NOT NULL,
    tipus   VARCHAR(10) NOT NULL DEFAULT 'mobil',
    CONSTRAINT pk_telefons_soci  PRIMARY KEY (soci_id, numero),
    CONSTRAINT chk_telefons_tipus CHECK (tipus IN ('mobil','fix','feina')),
    CONSTRAINT chk_telefons_num   CHECK (numero ~ '^\+?[0-9 ]{6,20}$'),
    CONSTRAINT fk_telefons_soci  FOREIGN KEY (soci_id)
        REFERENCES socis (soci_id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- R12 (màxim 3 per soci): no expressable en CHECK. Disparador o aplicació.

ALTER TABLE sucursals
    ALTER COLUMN adr_codi_postal TYPE dom_codi_postal,
    ALTER COLUMN adr_carrer SET NOT NULL,
    ALTER COLUMN adr_ciutat SET NOT NULL;

ALTER TABLE socis ALTER COLUMN email TYPE dom_email;

-- ---------------------------------------------------------------------
-- 3 · Sales i esdeveniments (R3, R4, R5)
-- ---------------------------------------------------------------------
CREATE TABLE sales (
    sala_id     INTEGER  GENERATED BY DEFAULT AS IDENTITY,
    sucursal_id INTEGER  NOT NULL,
    nom         TEXT     NOT NULL,
    aforament   SMALLINT NOT NULL,
    planta      SMALLINT NOT NULL DEFAULT 0,
    accessible  BOOLEAN  NOT NULL DEFAULT TRUE,
    CONSTRAINT pk_sales                  PRIMARY KEY (sala_id),
    CONSTRAINT uq_sales_sucursal_nom     UNIQUE (sucursal_id, nom),        -- R3
    CONSTRAINT chk_sales_aforament_positiu CHECK (aforament > 0 AND aforament <= 2000),
    CONSTRAINT chk_sales_planta          CHECK (planta BETWEEN -3 AND 20),
    CONSTRAINT chk_sales_nom             CHECK (char_length(nom) BETWEEN 1 AND 80),
    CONSTRAINT fk_sales_sucursal         FOREIGN KEY (sucursal_id)
        REFERENCES sucursals (sucursal_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE tipus_esdeveniment (
    tipus_esdeveniment_id INTEGER     GENERATED BY DEFAULT AS IDENTITY,
    codi                  VARCHAR(30) COLLATE "C" NOT NULL,
    nom                   TEXT        NOT NULL,
    descripcio            TEXT,
    durada_estandard_min  SMALLINT,
    actiu                 BOOLEAN     NOT NULL DEFAULT TRUE,
    CONSTRAINT pk_tipus_esdeveniment      PRIMARY KEY (tipus_esdeveniment_id),
    CONSTRAINT uq_tipus_esdeveniment_codi UNIQUE (codi),
    CONSTRAINT chk_tipus_esdeveniment_codi CHECK (codi ~ '^[a-z][a-z0-9_]{2,29}$'),
    CONSTRAINT chk_tipus_esdeveniment_dur
        CHECK (durada_estandard_min IS NULL OR durada_estandard_min > 0)
);

CREATE TABLE esdeveniments (
    esdeveniment_id       INTEGER     GENERATED BY DEFAULT AS IDENTITY,
    titol                 TEXT        NOT NULL,
    descripcio            TEXT,
    tipus_esdeveniment_id INTEGER     NOT NULL,
    sala_id               INTEGER,                              -- D4: opcional
    inici                 TIMESTAMPTZ NOT NULL,
    fi                    TIMESTAMPTZ NOT NULL,
    places_ofertes        SMALLINT    NOT NULL,
    estat                 VARCHAR(15) NOT NULL DEFAULT 'programat',
    publicat              BOOLEAN     NOT NULL DEFAULT FALSE,
    durada_min            INTEGER     GENERATED ALWAYS AS
                              (EXTRACT(EPOCH FROM (fi - inici)) / 60) STORED,
    CONSTRAINT pk_esdeveniments       PRIMARY KEY (esdeveniment_id),
    CONSTRAINT chk_esdeveniments_titol CHECK (char_length(titol) BETWEEN 3 AND 200),
    CONSTRAINT chk_esdeveniments_fi_posterior CHECK (fi > inici),           -- RN3
    CONSTRAINT chk_esdeveniments_places CHECK (places_ofertes >= 0 AND places_ofertes <= 2000),
    CONSTRAINT chk_esdeveniments_estat
        CHECK (estat IN ('programat','obert','complet','celebrat','cancellat')),
    CONSTRAINT chk_esdeveniments_publicat                                   -- RN8
        CHECK (NOT publicat OR (sala_id IS NOT NULL AND places_ofertes > 0)),
    CONSTRAINT fk_esdeveniments_tipus FOREIGN KEY (tipus_esdeveniment_id)
        REFERENCES tipus_esdeveniment (tipus_esdeveniment_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_esdeveniments_sala FOREIGN KEY (sala_id)
        REFERENCES sales (sala_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

ALTER TABLE esdeveniments ADD CONSTRAINT excl_esdeveniments_encavalcament_sala   -- RN4
    EXCLUDE USING gist (sala_id WITH =, tstzrange(inici, fi) WITH &&)
    WHERE (estat <> 'cancellat' AND sala_id IS NOT NULL);

CREATE TABLE ponents (
    ponent_id INTEGER   GENERATED BY DEFAULT AS IDENTITY,
    nom       TEXT      NOT NULL,
    cognoms   TEXT      NOT NULL,
    email     dom_email,
    biografia TEXT,
    extern    BOOLEAN   NOT NULL DEFAULT TRUE,
    CONSTRAINT pk_ponents        PRIMARY KEY (ponent_id),
    CONSTRAINT uq_ponents_email  UNIQUE (email),      -- diversos NULL permesos
    CONSTRAINT chk_ponents_nom   CHECK (char_length(nom) > 0
                                    AND char_length(cognoms) > 0)
);

-- ---------------------------------------------------------------------
-- 4 · Inscripcions, informes, participacions i materials de l'esdeveniment
-- ---------------------------------------------------------------------
CREATE TABLE inscripcions (
    esdeveniment_id INTEGER     NOT NULL,
    soci_id         INTEGER     NOT NULL,
    data_inscripcio TIMESTAMPTZ NOT NULL DEFAULT now(),
    estat           VARCHAR(15) NOT NULL DEFAULT 'confirmada',
    acompanyants    SMALLINT    NOT NULL DEFAULT 0,
    places_ocupades SMALLINT    GENERATED ALWAYS AS (1 + acompanyants) STORED,
    CONSTRAINT pk_inscripcions      PRIMARY KEY (esdeveniment_id, soci_id),  -- R6
    CONSTRAINT chk_inscripcions_acompanyants CHECK (acompanyants BETWEEN 0 AND 3),
    CONSTRAINT chk_inscripcions_estat
        CHECK (estat IN ('confirmada','llista_espera','cancellada','assistida')),
    CONSTRAINT fk_inscripcions_esdeveniment FOREIGN KEY (esdeveniment_id)
        REFERENCES esdeveniments (esdeveniment_id) ON DELETE CASCADE  ON UPDATE CASCADE,
    CONSTRAINT fk_inscripcions_soci  FOREIGN KEY (soci_id)
        REFERENCES socis (soci_id)   ON DELETE RESTRICT ON UPDATE CASCADE
);
-- RN2 (aforament), RN6 (soci actiu) i RN7 (data < inici): disparador o aplicació.

CREATE TABLE informes_esdeveniment (
    esdeveniment_id     INTEGER      NOT NULL,
    assistents_reals    SMALLINT     NOT NULL,
    valoracio_mitjana   NUMERIC(3,2),
    observacions        TEXT,
    respostes_enquesta  JSONB,
    data_redaccio       DATE         NOT NULL DEFAULT CURRENT_DATE,
    CONSTRAINT pk_informes_esdeveniment PRIMARY KEY (esdeveniment_id),      -- R9, 1:1
    CONSTRAINT chk_informes_assistents  CHECK (assistents_reals >= 0),
    CONSTRAINT chk_informes_valoracio
        CHECK (valoracio_mitjana IS NULL OR valoracio_mitjana BETWEEN 0 AND 5),
    CONSTRAINT fk_informes_esdeveniment FOREIGN KEY (esdeveniment_id)
        REFERENCES esdeveniments (esdeveniment_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE participacions (
    esdeveniment_id INTEGER        NOT NULL,
    ponent_id       INTEGER        NOT NULL,
    rol             VARCHAR(25)    NOT NULL,
    honoraris       dom_import_eur NOT NULL DEFAULT 0,
    CONSTRAINT pk_participacions PRIMARY KEY (esdeveniment_id, ponent_id, rol),  -- R7
    CONSTRAINT chk_participacions_rol
        CHECK (rol IN ('moderador','tallerista','autor_convidat','presentador')),
    CONSTRAINT fk_participacions_esdeveniment FOREIGN KEY (esdeveniment_id)
        REFERENCES esdeveniments (esdeveniment_id) ON DELETE CASCADE  ON UPDATE CASCADE,
    CONSTRAINT fk_participacions_ponent FOREIGN KEY (ponent_id)
        REFERENCES ponents (ponent_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE esdeveniments_materials (
    esdeveniment_id INTEGER     NOT NULL,
    material_id     INTEGER     NOT NULL,
    paper           VARCHAR(15) NOT NULL DEFAULT 'recomanat',
    CONSTRAINT pk_esdeveniments_materials PRIMARY KEY (esdeveniment_id, material_id),  -- R8
    CONSTRAINT chk_esdeveniments_materials_paper
        CHECK (paper IN ('principal','recomanat')),
    CONSTRAINT fk_esdeveniments_materials_esdeveniment FOREIGN KEY (esdeveniment_id)
        REFERENCES esdeveniments (esdeveniment_id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_esdeveniments_materials_material FOREIGN KEY (material_id)
        REFERENCES materials (material_id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- ---------------------------------------------------------------------
-- 5 · Multes i pagaments (R10, R11)
-- ---------------------------------------------------------------------
CREATE TABLE multes (
    multa_id      INTEGER        GENERATED BY DEFAULT AS IDENTITY,
    soci_id       INTEGER        NOT NULL,
    prestec_id    INTEGER,                                    -- D6: opcional
    motiu         VARCHAR(15)    NOT NULL,
    import        dom_import_eur NOT NULL,                    -- RN5
    data_emissio  DATE           NOT NULL DEFAULT CURRENT_DATE,
    estat         VARCHAR(15)    NOT NULL DEFAULT 'pendent',
    CONSTRAINT pk_multes PRIMARY KEY (multa_id),
    CONSTRAINT uq_multes_prestec_motiu                                -- R10
        UNIQUE NULLS NOT DISTINCT (prestec_id, motiu),
    CONSTRAINT chk_multes_motiu CHECK (motiu IN ('retard','deteriorament','perdua')),
    CONSTRAINT chk_multes_estat
        CHECK (estat IN ('pendent','pagada','condonada','anullada')),
    CONSTRAINT chk_multes_import_max CHECK (import <= 500),
    CONSTRAINT fk_multes_soci    FOREIGN KEY (soci_id)
        REFERENCES socis (soci_id)     ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_multes_prestec FOREIGN KEY (prestec_id)
        REFERENCES prestecs (prestec_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE pagaments (
    pagament_id   INTEGER        GENERATED BY DEFAULT AS IDENTITY,
    multa_id      INTEGER        NOT NULL,
    data_pagament TIMESTAMPTZ    NOT NULL DEFAULT now(),
    import        dom_import_eur NOT NULL,
    metode        VARCHAR(15)    NOT NULL,
    referencia    VARCHAR(50),
    CONSTRAINT pk_pagaments          PRIMARY KEY (pagament_id),
    CONSTRAINT chk_pagaments_import  CHECK (import > 0),
    CONSTRAINT chk_pagaments_metode  CHECK (metode IN ('efectiu','targeta','passarella')),
    CONSTRAINT chk_pagaments_referencia
        CHECK (metode = 'efectiu' OR referencia IS NOT NULL),
    CONSTRAINT fk_pagaments_multa    FOREIGN KEY (multa_id)
        REFERENCES multes (multa_id) ON DELETE RESTRICT ON UPDATE CASCADE
);
-- RN5 (suma de pagaments <= import de la multa): disparador o aplicació.

-- ---------------------------------------------------------------------
-- 6 · Blindatge de les taules preexistents
-- ---------------------------------------------------------------------
ALTER TABLE prestecs
    ALTER COLUMN recarrec TYPE NUMERIC(6,2),
    ALTER COLUMN recarrec SET DEFAULT 0,
    ADD CONSTRAINT chk_prestecs_recarrec CHECK (recarrec >= 0) NOT VALID,
    ADD CONSTRAINT chk_prestecs_devolucio
        CHECK (data_devolucio IS NULL OR data_devolucio >= data_prestec) NOT VALID,
    ADD CONSTRAINT chk_prestecs_prevista
        CHECK (data_devolucio_prevista > data_prestec) NOT VALID;

ALTER TABLE prestecs VALIDATE CONSTRAINT chk_prestecs_recarrec;
ALTER TABLE prestecs VALIDATE CONSTRAINT chk_prestecs_devolucio;
ALTER TABLE prestecs VALIDATE CONSTRAINT chk_prestecs_prevista;

ALTER TABLE exemplars
    ALTER COLUMN num_exemplar TYPE SMALLINT,
    ALTER COLUMN codi TYPE VARCHAR(15) COLLATE "C",
    ADD CONSTRAINT chk_exemplars_estat
        CHECK (estat IN ('disponible','prestat','reservat','reparacio','baixa')),
    ADD CONSTRAINT chk_exemplars_num CHECK (num_exemplar > 0),
    ADD CONSTRAINT chk_exemplars_codi CHECK (codi ~ '^EJ-[0-9]{4,6}$');

ALTER TABLE reserves
    ADD CONSTRAINT chk_reserves_estat
        CHECK (estat IN ('activa','disponible','recollida','expirada','cancellada')),
    ADD CONSTRAINT chk_reserves_expiracio CHECK (data_expiracio > data_reserva);

-- ---------------------------------------------------------------------
-- 7 · Documentació al catàleg
-- ---------------------------------------------------------------------
COMMENT ON TABLE  materials IS
    'Superclasse de la jerarquia de catàleg (R1). Estratègia: taula per subclasse.';
COMMENT ON COLUMN multes.import IS
    'Import en euros congelat en el moment de l''emissió (R10). No recalcular.';
COMMENT ON COLUMN prestecs.recarrec IS
    'OBSOLETA. Històric anterior a l''ampliació; la veritat viu a multes.import.';
COMMENT ON CONSTRAINT excl_esdeveniments_encavalcament_sala ON esdeveniments IS
    'RN4: dos esdeveniments no cancel·lats no es poden encavalcar a la mateixa sala.';

CREATE TABLE quotes (
    id            VARCHAR(50),
    soci          INTEGER,
    exercici      VARCHAR(4),
    import        FLOAT,
    pagada        CHAR(1) DEFAULT 'N',
    data_pagament VARCHAR(20),
    metode        VARCHAR(50),
    descompte_pct FLOAT,
    observacions  CHAR(500)
);

-- 2
ALTER TABLE esdeveniments ADD CONSTRAINT chk_esdeveniments_cancellat_no_publicat
    CHECK (estat <> 'cancellat' OR NOT publicat);

UPDATE esdeveniments SET estat = 'cancellat' WHERE esdeveniment_id = 47 AND publicat;

ERROR:  new row for relation "esdeveniments" violates check constraint "chk_esdeveniments_cancellat_no_publicat"

CREATE TABLE quotes (
    quota_id      INTEGER        GENERATED BY DEFAULT AS IDENTITY,
    soci_id       INTEGER        NOT NULL,
    exercici      SMALLINT       NOT NULL,
    import        dom_import_eur NOT NULL,
    descompte_pct SMALLINT       NOT NULL DEFAULT 0,
    pagada        BOOLEAN        NOT NULL DEFAULT FALSE,
    data_pagament DATE,
    metode        VARCHAR(15),
    observacions  TEXT,
    import_final  NUMERIC(8,2)   GENERATED ALWAYS AS
                      (ROUND(import * (100 - descompte_pct) / 100.0, 2)) STORED,
    CONSTRAINT pk_quotes             PRIMARY KEY (quota_id),
    CONSTRAINT uq_quotes_soci_exercici UNIQUE (soci_id, exercici),
    CONSTRAINT chk_quotes_exercici   CHECK (exercici BETWEEN 2000 AND 2100),
    CONSTRAINT chk_quotes_descompte  CHECK (descompte_pct BETWEEN 0 AND 100),
    CONSTRAINT chk_quotes_metode
        CHECK (metode IS NULL OR metode IN ('efectiu','targeta','passarella','domiciliacio')),
    CONSTRAINT chk_quotes_coherencia_pagament
        CHECK ((pagada     AND data_pagament IS NOT NULL AND metode IS NOT NULL)
            OR (NOT pagada AND data_pagament IS NULL     AND metode IS NULL)),
    CONSTRAINT fk_quotes_soci FOREIGN KEY (soci_id)
        REFERENCES socis (soci_id) ON DELETE RESTRICT ON UPDATE CASCADE
);