ALTER TABLE fichiers_membres RENAME TO fichiers_membres_old;
ALTER TABLE fichiers_wiki_pages RENAME TO fichiers_wiki_pages_old;
ALTER TABLE fichiers_acc_transactions RENAME TO fichiers_acc_transactions_old;

.read 1.0.0_schema.sql

INSERT INTO fichiers_membres SELECT * FROM fichiers_membres_old;
INSERT INTO fichiers_wiki_pages SELECT * FROM fichiers_wiki_pages_old;
INSERT INTO fichiers_acc_transactions SELECT * FROM fichiers_acc_transactions_old;

DROP TABLE fichiers_membres_old;
DROP TABLE fichiers_wiki_pages_old;
DROP TABLE fichiers_acc_transactions_old;

UPDATE acc_accounts SET type = 11 WHERE code = '120';
UPDATE acc_accounts SET type = 12 WHERE code = '129';

UPDATE acc_accounts SET type = 8, position = 4 WHERE id_chart = (SELECT id FROM acc_charts WHERE code IS NOT NULL) AND (code LIKE '86_%');
UPDATE acc_accounts SET type = 8, position = 5 WHERE id_chart = (SELECT id FROM acc_charts WHERE code IS NOT NULL) AND (code LIKE '87_%');

UPDATE acc_accounts SET position = 3 WHERE code IN ('5112', '5115', '530') AND code IS NOT NULL;

-- Put 890, 891 accounts in balance sheet, though it's not really correct...
UPDATE acc_accounts SET position = 3 WHERE
	code IN (890, 891)
	AND id_chart IN (SELECT id FROM acc_charts WHERE code IS NOT NULL);

UPDATE acc_accounts SET position = 1 WHERE code = '486' AND id_chart IN (SELECT id FROM acc_charts WHERE code = 'PCA2018');
UPDATE acc_accounts SET position = 2 WHERE code = '487' AND id_chart IN (SELECT id FROM acc_charts WHERE code = 'PCA2018');

UPDATE acc_transactions SET type = 0 WHERE type = 6;

ALTER TABLE membres_operations RENAME TO membres_operations_old;
ALTER TABLE membres_categories RENAME TO membres_categories_old;

DROP TABLE fichiers_compta_journal; -- Inutilisé à ce jour

-- Fix: comptes de clôture et fermeture
UPDATE compta_comptes SET libelle = 'Bilan d''ouverture' WHERE id = '890' AND libelle = 'Bilan de clôture';
INSERT OR REPLACE INTO compta_comptes (id, parent, libelle, position) VALUES ('891', '89', 'Bilan de clôture', 0);

-- N'est pas utilisé
DELETE FROM config WHERE cle = 'categorie_dons' OR cle = 'categorie_cotisations';

.read 1.0.0_schema.sql

-------- MIGRATION COMPTA ---------
INSERT INTO acc_charts (id, country, code, label) VALUES (1, 'FR', 'PCGA1999', 'Plan comptable associatif 1999');

-- Migration comptes de code comme identifiant à ID unique
-- Inversement valeurs actif/passif et produit/charge
INSERT INTO acc_accounts (id, id_chart, code, label, position, user)
	SELECT NULL, 1, id, libelle,
	CASE
		WHEN position = 1 THEN 2
		WHEN position = 2 THEN 1
		WHEN position = 3 THEN 3
		WHEN position = 4 THEN 5
		WHEN position = 8 THEN 4
		-- Suppression de la position "charge ou produit" qui n'a aucun sens
		WHEN position = 12 AND id LIKE '6%' THEN 4
		WHEN position = 12 AND id LIKE '7%' THEN 5
		WHEN position = 12 THEN 0
		ELSE 0
	END,
	CASE WHEN plan_comptable = 1 THEN 0 ELSE 1 END
	FROM compta_comptes;

-- Migrations projets vers comptes analytiques
INSERT INTO acc_accounts (id_chart, code, label, position, user, type)
	VALUES (1, '99', 'Projets', 0, 1, 0);

INSERT INTO acc_accounts (id_chart, code, label, position, user, type)
	SELECT 1, '99' || substr('0000' || id, -4), libelle, 0, 1, 7 FROM compta_projets;

-- Mise à jour de la position pour les comptes de tiers qui peuvent varier actif ou passif
UPDATE acc_accounts SET position = 3 WHERE code IN (4010, 4110, 4210, 428, 438);

-- Mise à jour position comptes bancaires, qui peuvent être à découvert et donc changer de côté au bilan
UPDATE acc_accounts SET position = 3 WHERE code LIKE '512%';

-- Migration comptes bancaires
UPDATE acc_accounts SET type = 1 WHERE code IN (SELECT id FROM compta_comptes_bancaires);

-- Caisse
UPDATE acc_accounts SET type = 2 WHERE code = '530';

-- Chèques et carte à encaisser
UPDATE acc_accounts SET type = 3 WHERE code = '5112' OR code = '5113';

-- Comptes d'ouverture et de clôture
UPDATE acc_accounts SET type = 9, position = 0 WHERE code = '890';
UPDATE acc_accounts SET type = 10, position = 0 WHERE code = '891';

-- Comptes de tiers
UPDATE acc_accounts SET type = 4 WHERE code IN (SELECT id FROM compta_comptes WHERE id LIKE '4%' AND plan_comptable = 0 AND desactive = 0);

-- Recopie des mouvements
INSERT INTO acc_transactions (id, label, notes, reference, date, id_year, id_creator)
	SELECT id, libelle, remarques, numero_piece, date, id_exercice, id_auteur
	FROM compta_journal;

-- Recettes
UPDATE acc_transactions SET type = 1 WHERE id IN (SELECT id FROM compta_journal WHERE id_categorie IN (SELECT id FROM compta_categories WHERE type = 1));

-- Dépenses
UPDATE acc_transactions SET type = 2 WHERE id IN (SELECT id FROM compta_journal WHERE id_categorie IN (SELECT id FROM compta_categories WHERE type = -1));

-- Virements
UPDATE acc_transactions SET type = 3 WHERE id IN (SELECT id FROM compta_journal
	WHERE (compte_credit IN ('530', '5112', '5115') OR compte_credit LIKE '512%')
	AND (compte_debit IN ('530', '5112', '5115') OR compte_debit LIKE '512%'));

-- Dettes
UPDATE acc_transactions SET type = 4 WHERE id IN (SELECT id FROM compta_journal WHERE compte_debit LIKE '6%' AND compte_credit LIKE '4%');

-- Créances
UPDATE acc_transactions SET type = 5 WHERE id IN (SELECT id FROM compta_journal WHERE compte_credit LIKE '7%' AND compte_debit LIKE '4%');

-- Création des lignes associées aux mouvements
INSERT INTO acc_transactions_lines (id_transaction, id_account, debit, credit, reference, id_analytical)
	SELECT id, (SELECT id FROM acc_accounts WHERE code = compte_credit), 0, CAST(REPLACE(montant * 100, '.0', '') AS INT), numero_cheque,
	CASE WHEN id_projet IS NOT NULL THEN (SELECT id FROM acc_accounts WHERE code = '99' || substr('0000' || id_projet, -4)) ELSE NULL END
	FROM compta_journal;

INSERT INTO acc_transactions_lines (id_transaction, id_account, debit, credit, reference, id_analytical)
	SELECT id, (SELECT id FROM acc_accounts WHERE code = compte_debit), CAST(REPLACE(montant * 100, '.0', '') AS INT), 0, numero_cheque,
	CASE WHEN id_projet IS NOT NULL THEN (SELECT id FROM acc_accounts WHERE code = '99' || substr('0000' || id_projet, -4)) ELSE NULL END
	FROM compta_journal;

-- Recopie des descriptions de catégories dans la table des comptes, et mise des comptes en signets
-- +Fix éventuels types qui ne correspondent pas à leur type… (@Fred C.) (... a.position = X)
-- Revenus
UPDATE acc_accounts SET type = 6, description = (SELECT description FROM compta_categories WHERE compte = acc_accounts.code)
	WHERE id IN (SELECT a.id FROM acc_accounts a INNER JOIN compta_categories c ON c.compte = a.code AND c.type = 1 AND a.position = 5);

-- Dépenses
UPDATE acc_accounts SET type = 5, description = (SELECT description FROM compta_categories WHERE compte = acc_accounts.code)
	WHERE id IN (SELECT a.id FROM acc_accounts a INNER JOIN compta_categories c ON c.compte = a.code AND c.type = -1 AND c.compte NOT LIKE '4%' AND a.position = 4);

-- Tiers
UPDATE acc_accounts SET type = 4, description = (SELECT description FROM compta_categories WHERE compte = acc_accounts.code)
	WHERE id IN (SELECT a.id FROM acc_accounts a INNER JOIN compta_categories c ON c.compte = a.code AND c.type = -1 AND c.compte LIKE '4%');

-- Recopie des exercices, mais la date de fin ne peut être nulle
INSERT INTO acc_years (id, label, start_date, end_date, closed, id_chart)
	SELECT id, libelle, debut, CASE WHEN fin IS NULL THEN date(debut, '+1 year') ELSE fin END, cloture, 1 FROM compta_exercices;

-- Recopie des catégories, on supprime la colonne id_cotisation_obligatoire
INSERT INTO membres_categories
	SELECT id, nom, droit_wiki, droit_membres, droit_compta, droit_inscription, droit_connexion, droit_config, cacher FROM membres_categories_old;

DROP TABLE membres_categories_old;

-- Transfert des rapprochements
UPDATE acc_transactions_lines SET reconciled = 1 WHERE id_transaction IN (SELECT id_operation FROM compta_rapprochement);

--------- MIGRATION COTISATIONS ----------

-- A edge-case where the end date is after the start date, let's fix it…
UPDATE cotisations SET fin = debut WHERE fin < debut;
UPDATE cotisations SET duree = NULL WHERE duree = 0;

INSERT INTO services SELECT id, intitule, description, duree, debut, fin FROM cotisations;

INSERT INTO services_fees (id, label, amount, id_service, id_account, id_year)
	SELECT id, intitule, CASE WHEN montant IS NOT NULL THEN CAST(montant*100 AS integer) ELSE NULL END, id,
		(SELECT id FROM acc_accounts WHERE code = (SELECT compte FROM compta_categories WHERE id = id_categorie_compta)),
		(SELECT MAX(id) FROM acc_years WHERE closed = 0)
	FROM cotisations;

INSERT INTO services_users SELECT cm.id, cm.id_membre, cm.id_cotisation,
	cm.id_cotisation,
	1,
	NULL,
	cm.date,
	CASE
		WHEN c.duree IS NOT NULL THEN date(cm.date, '+'||c.duree||' days')
		WHEN c.fin IS NOT NULL THEN c.fin
		ELSE NULL
	END
	FROM cotisations_membres cm
	INNER JOIN cotisations c ON c.id = cm.id_cotisation;

INSERT INTO services_reminders SELECT * FROM rappels;
INSERT INTO services_reminders_sent SELECT id, id_membre, id_cotisation,
	CASE WHEN id_rappel IS NULL THEN (SELECT MAX(id) FROM rappels) ELSE id_rappel END, date
	FROM rappels_envoyes
	WHERE id_rappel IS NOT NULL
	GROUP BY id_membre, id_cotisation, id_rappel;

-- Recopie des opérations par membre, mais le nom a changé pour acc_transactions_users, et il faut valider l'existence du membre ET du service
INSERT INTO acc_transactions_users
	SELECT a.* FROM membres_operations_old a
	INNER JOIN membres b ON b.id = a.id_membre
	INNER JOIN services_users c ON c.id = a.id_cotisation;

DROP TABLE cotisations;
DROP TABLE cotisations_membres;
DROP TABLE rappels;
DROP TABLE rappels_envoyes;

-- Suppression inutilisées
DROP TABLE compta_rapprochement;
DROP TABLE compta_journal;
DROP TABLE compta_categories;
DROP TABLE compta_comptes;
DROP TABLE compta_exercices;
DROP TABLE membres_operations_old;

DROP TABLE compta_projets;
DROP TABLE compta_comptes_bancaires;
DROP TABLE compta_moyens_paiement;

INSERT INTO acc_charts (country, code, label) VALUES ('FR', 'PCA2018', 'Plan comptable associatif 2018');

CREATE TEMP TABLE tmp_accounts (code,label,description,position,type);

.import charts/fr_2018.csv tmp_accounts

INSERT INTO acc_accounts (id_chart, code, label, description, position, type) SELECT
	(SELECT id FROM acc_charts WHERE code = 'PCA2018'),
	code, label, description,
	CASE position
		WHEN 'Actif' THEN 1
		WHEN 'Passif' THEN 2
		WHEN 'Actif ou passif' THEN 3
		WHEN 'Charge' THEN 4
		WHEN 'Produit' THEN 5
		ELSE 0
	END,
	CASE type
		WHEN 'Banque' THEN 1
		WHEN 'Caisse' THEN 2
		WHEN 'Attente d''encaissement' THEN 3
		WHEN 'Tiers' THEN 4
		WHEN 'Dépenses' THEN 5
		WHEN 'Recettes' THEN 6
		WHEN 'Analytique' THEN 7
		WHEN 'Bénévolat' THEN 8
		WHEN 'Ouverture' THEN 9
		WHEN 'Clôture' THEN 10
		WHEN 'Résultat excédentaire' THEN 11
		WHEN 'Résultat déficitaire' THEN 12
		ELSE 0
	END
	FROM tmp_accounts;

DROP TABLE tmp_accounts;

CREATE TABLE IF NOT EXISTS config (
-- Configuration de Garradin
    cle TEXT PRIMARY KEY NOT NULL,
    valeur TEXT
);

CREATE TABLE IF NOT EXISTS membres_categories
-- Catégories de membres
(
    id INTEGER PRIMARY KEY NOT NULL,
    nom TEXT NOT NULL,

    droit_wiki INTEGER NOT NULL DEFAULT 1,
    droit_membres INTEGER NOT NULL DEFAULT 1,
    droit_compta INTEGER NOT NULL DEFAULT 1,
    droit_inscription INTEGER NOT NULL DEFAULT 0,
    droit_connexion INTEGER NOT NULL DEFAULT 1,
    droit_config INTEGER NOT NULL DEFAULT 0,
    cacher INTEGER NOT NULL DEFAULT 0
);

-- Membres de l'asso
-- Table dynamique générée par l'application
-- voir Garradin\Membres\Champs.php

CREATE TABLE IF NOT EXISTS membres_sessions
-- Sessions
(
    selecteur TEXT NOT NULL,
    hash TEXT NOT NULL,
    id_membre INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    expire INT NOT NULL,

    PRIMARY KEY (selecteur, id_membre)
);

CREATE TABLE IF NOT EXISTS services
-- Types de services (cotisations)
(
    id INTEGER PRIMARY KEY NOT NULL,

    label TEXT NOT NULL,
    description TEXT NULL,

    duration INTEGER NULL CHECK (duration IS NULL OR duration > 0), -- En jours
    start_date TEXT NULL CHECK (start_date IS NULL OR date(start_date) = start_date),
    end_date TEXT NULL CHECK (end_date IS NULL OR (date(end_date) = end_date AND date(end_date) >= date(start_date)))
);

CREATE TABLE IF NOT EXISTS services_fees
(
    id INTEGER PRIMARY KEY NOT NULL,

    label TEXT NOT NULL,
    description TEXT NULL,

    amount INTEGER NULL,
    formula TEXT NULL, -- Formule de calcul du montant de la cotisation, si cotisation dynamique (exemple : membres.revenu_imposable * 0.01)

    id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE,
    id_account INTEGER NULL REFERENCES acc_accounts (id) ON DELETE SET NULL CHECK (id_account IS NULL OR id_year IS NOT NULL), -- NULL si le type n'est pas associé automatiquement à la compta
    id_year INTEGER NULL REFERENCES acc_years (id) ON DELETE SET NULL -- NULL si le type n'est pas associé automatiquement à la compta
);

CREATE TABLE IF NOT EXISTS services_users
-- Enregistrement des cotisations et activités
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_user INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE,
    id_fee INTEGER NULL REFERENCES services_fees (id) ON DELETE CASCADE,

    paid INTEGER NOT NULL DEFAULT 0,
    expected_amount INTEGER NULL,

    date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date),
    expiry_date TEXT NULL CHECK (date(expiry_date) IS NULL OR date(expiry_date) = expiry_date)
);

CREATE UNIQUE INDEX IF NOT EXISTS su_unique ON services_users (id_user, id_service, date);

CREATE INDEX IF NOT EXISTS su_service ON services_users (id_service);
CREATE INDEX IF NOT EXISTS su_fee ON services_users (id_fee);
CREATE INDEX IF NOT EXISTS su_paid ON services_users (paid);
CREATE INDEX IF NOT EXISTS su_expiry ON services_users (expiry_date);

CREATE TABLE IF NOT EXISTS services_reminders
-- Rappels de devoir renouveller une cotisation
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE,

    delay INTEGER NOT NULL, -- Délai en jours pour envoyer le rappel

    subject TEXT NOT NULL,
    body TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS services_reminders_sent
-- Enregistrement des rappels envoyés à qui et quand
(
    id INTEGER NOT NULL PRIMARY KEY,

    id_user INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE,
    id_reminder INTEGER NOT NULL REFERENCES services_reminders (id) ON DELETE CASCADE,

    date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date)
);

CREATE UNIQUE INDEX IF NOT EXISTS srs_index ON services_reminders_sent (id_user, id_service, id_reminder, date);

CREATE INDEX IF NOT EXISTS srs_reminder ON services_reminders_sent (id_reminder);
CREATE INDEX IF NOT EXISTS srs_user ON services_reminders_sent (id_user);

--
-- WIKI
--

CREATE TABLE IF NOT EXISTS wiki_pages
-- Pages du wiki
(
    id INTEGER PRIMARY KEY NOT NULL,
    uri TEXT NOT NULL, -- URI unique (équivalent NomPageWiki)
    titre TEXT NOT NULL,
    date_creation TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(date_creation) IS NOT NULL AND datetime(date_creation) = date_creation),
    date_modification TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(date_modification) IS NOT NULL AND datetime(date_modification) = date_modification),
    parent INTEGER NOT NULL DEFAULT 0, -- ID de la page parent
    revision INTEGER NOT NULL DEFAULT 0, -- Numéro de révision (commence à 0 si pas de texte, +1 à chaque changement du texte)
    droit_lecture INTEGER NOT NULL DEFAULT 0, -- Accès en lecture (-1 = public [site web], 0 = tous ceux qui ont accès en lecture au wiki, 1+ = ID de groupe)
    droit_ecriture INTEGER NOT NULL DEFAULT 0 -- Accès en écriture (0 = tous ceux qui ont droit d'écriture sur le wiki, 1+ = ID de groupe)
);

CREATE UNIQUE INDEX IF NOT EXISTS wiki_uri ON wiki_pages (uri);

CREATE VIRTUAL TABLE IF NOT EXISTS wiki_recherche USING fts4
-- Table dupliquée pour chercher une page
(
    id INT PRIMARY KEY NOT NULL, -- Clé externe obligatoire
    titre TEXT NOT NULL,
    contenu TEXT NULL, -- Contenu de la dernière révision
    FOREIGN KEY (id) REFERENCES wiki_pages(id)
);

CREATE TABLE IF NOT EXISTS wiki_revisions
-- Révisions du contenu des pages
(
    id_page INTEGER NOT NULL REFERENCES wiki_pages (id) ON DELETE CASCADE,
    revision INTEGER NULL,

    id_auteur INTEGER NULL REFERENCES membres (id) ON DELETE SET NULL,

    contenu TEXT NOT NULL,
    modification TEXT NULL, -- Description des modifications effectuées
    chiffrement INTEGER NOT NULL DEFAULT 0, -- 1 si le contenu est chiffré, 0 sinon
    date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(date) IS NOT NULL AND datetime(date) = date),

    PRIMARY KEY(id_page, revision)
);

CREATE INDEX IF NOT EXISTS wiki_revisions_id_page ON wiki_revisions (id_page);
CREATE INDEX IF NOT EXISTS wiki_revisions_id_auteur ON wiki_revisions (id_auteur);

-- Triggers pour synchro avec table wiki_pages
CREATE TRIGGER IF NOT EXISTS wiki_recherche_delete AFTER DELETE ON wiki_pages
    BEGIN
        DELETE FROM wiki_recherche WHERE id = old.id;
    END;

CREATE TRIGGER IF NOT EXISTS wiki_recherche_update AFTER UPDATE OF id, titre ON wiki_pages
    BEGIN
        UPDATE wiki_recherche SET id = new.id, titre = new.titre WHERE id = old.id;
    END;

-- Trigger pour mettre à jour le contenu de la table de recherche lors d'une nouvelle révision
CREATE TRIGGER IF NOT EXISTS wiki_recherche_contenu_insert AFTER INSERT ON wiki_revisions WHEN new.chiffrement != 1
    BEGIN
        UPDATE wiki_recherche SET contenu = new.contenu WHERE id = new.id_page;
    END;

-- Si le contenu est chiffré, la recherche n'affiche pas de contenu
CREATE TRIGGER IF NOT EXISTS wiki_recherche_contenu_chiffre AFTER INSERT ON wiki_revisions WHEN new.chiffrement = 1
    BEGIN
        UPDATE wiki_recherche SET contenu = '' WHERE id = new.id_page;
    END;

--
-- COMPTA
--

CREATE TABLE IF NOT EXISTS acc_charts
-- Plans comptables : il peut y en avoir plusieurs
(
    id INTEGER NOT NULL PRIMARY KEY,
    country TEXT NOT NULL,
    code TEXT NULL, -- NULL = plan comptable créé par l'utilisateur
    label TEXT NOT NULL,
    archived INTEGER NOT NULL DEFAULT 0 -- 1 = archivé, non-modifiable
);

CREATE TABLE IF NOT EXISTS acc_accounts
-- Comptes des plans comptables
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_chart INTEGER NOT NULL REFERENCES acc_charts ON DELETE CASCADE,

    code TEXT NOT NULL, -- peut contenir des lettres, eg. 53A, 53B, etc.

    label TEXT NOT NULL,
    description TEXT NULL,

    position INTEGER NOT NULL, -- position actif/passif/charge/produit
    type INTEGER NOT NULL DEFAULT 0, -- Type de compte spécial : banque, caisse, en attente d'encaissement, etc.
    user INTEGER NOT NULL DEFAULT 1 -- 1 = fait partie du plan comptable original, 0 = a été ajouté par l'utilisateur
);

CREATE UNIQUE INDEX IF NOT EXISTS acc_accounts_codes ON acc_accounts (code, id_chart);
CREATE INDEX IF NOT EXISTS acc_accounts_type ON acc_accounts (type);
CREATE INDEX IF NOT EXISTS acc_accounts_position ON acc_accounts (position);

CREATE TABLE IF NOT EXISTS acc_years
-- Exercices
(
    id INTEGER NOT NULL PRIMARY KEY,

    label TEXT NOT NULL,

    start_date TEXT NOT NULL CHECK (date(start_date) IS NOT NULL AND date(start_date) = start_date),
    end_date TEXT NOT NULL CHECK (date(end_date) IS NOT NULL AND date(end_date) = end_date),

    closed INTEGER NOT NULL DEFAULT 0,

    id_chart INTEGER NOT NULL REFERENCES acc_charts (id)
);

CREATE TRIGGER IF NOT EXISTS acc_years_delete BEFORE DELETE ON acc_years BEGIN
    UPDATE services_fees SET id_account = NULL, id_year = NULL WHERE id_year = OLD.id;
END;

CREATE INDEX IF NOT EXISTS acc_years_closed ON acc_years (closed);

CREATE TABLE IF NOT EXISTS acc_transactions
-- Opérations comptables
(
    id INTEGER PRIMARY KEY NOT NULL,

    type INTEGER NOT NULL DEFAULT 0, -- Type d'écriture, 0 = avancée (normale)
    status INTEGER NOT NULL DEFAULT 0, -- Statut (bitmask)

    label TEXT NOT NULL,
    notes TEXT NULL,
    reference TEXT NULL, -- N° de pièce comptable

    date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date),

    validated INTEGER NOT NULL DEFAULT 0, -- 1 = écriture validée, non modifiable

    hash TEXT NULL,
    prev_hash TEXT NULL,

    id_year INTEGER NOT NULL REFERENCES acc_years(id),
    id_creator INTEGER NULL REFERENCES membres(id) ON DELETE SET NULL,
    id_related INTEGER NULL REFERENCES acc_transactions(id) ON DELETE SET NULL -- écriture liée (par ex. remboursement d'une dette)
);

CREATE INDEX IF NOT EXISTS acc_transactions_year ON acc_transactions (id_year);
CREATE INDEX IF NOT EXISTS acc_transactions_date ON acc_transactions (date);
CREATE INDEX IF NOT EXISTS acc_transactions_related ON acc_transactions (id_related);
CREATE INDEX IF NOT EXISTS acc_transactions_type ON acc_transactions (type, id_year);
CREATE INDEX IF NOT EXISTS acc_transactions_status ON acc_transactions (status);

CREATE TABLE IF NOT EXISTS acc_transactions_lines
-- Lignes d'écritures d'une opération
(
    id INTEGER PRIMARY KEY NOT NULL,

    id_transaction INTEGER NOT NULL REFERENCES acc_transactions (id) ON DELETE CASCADE,
    id_account INTEGER NOT NULL REFERENCES acc_accounts (id), -- N° du compte dans le plan comptable

    credit INTEGER NOT NULL,
    debit INTEGER NOT NULL,

    reference TEXT NULL, -- Référence de paiement, eg. numéro de chèque
    label TEXT NULL,

    reconciled INTEGER NOT NULL DEFAULT 0,

    id_analytical INTEGER NULL REFERENCES acc_accounts(id) ON DELETE SET NULL,

    CONSTRAINT line_check1 CHECK ((credit * debit) = 0),
    CONSTRAINT line_check2 CHECK ((credit + debit) > 0)
);

CREATE INDEX IF NOT EXISTS acc_transactions_lines_transaction ON acc_transactions_lines (id_transaction);
CREATE INDEX IF NOT EXISTS acc_transactions_lines_account ON acc_transactions_lines (id_account);
CREATE INDEX IF NOT EXISTS acc_transactions_lines_analytical ON acc_transactions_lines (id_analytical);
CREATE INDEX IF NOT EXISTS acc_transactions_lines_reconciled ON acc_transactions_lines (reconciled);

CREATE TABLE IF NOT EXISTS acc_transactions_users
-- Liaison des écritures et des membres
(
    id_user INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    id_transaction INTEGER NOT NULL REFERENCES acc_transactions (id) ON DELETE CASCADE,
    id_service_user INTEGER NULL REFERENCES services_users (id) ON DELETE SET NULL,

    PRIMARY KEY (id_user, id_transaction)
);

CREATE INDEX IF NOT EXISTS acc_transactions_users_service ON acc_transactions_users (id_service_user);

CREATE TABLE IF NOT EXISTS plugins
(
    id TEXT NOT NULL PRIMARY KEY,
    officiel INTEGER NOT NULL DEFAULT 0,
    nom TEXT NOT NULL,
    description TEXT NULL,
    auteur TEXT NULL,
    url TEXT NULL,
    version TEXT NOT NULL,
    menu INTEGER NOT NULL DEFAULT 0,
    menu_condition TEXT NULL,
    config TEXT NULL
);

CREATE TABLE IF NOT EXISTS plugins_signaux
-- Association entre plugins et signaux (hooks)
(
    signal TEXT NOT NULL,
    plugin TEXT NOT NULL REFERENCES plugins (id),
    callback TEXT NOT NULL,
    PRIMARY KEY (signal, plugin)
);

CREATE TABLE IF NOT EXISTS fichiers
-- Données sur les fichiers
(
    id INTEGER NOT NULL PRIMARY KEY,
    nom TEXT NOT NULL, -- nom de fichier (par exemple image1234.jpeg)
    type TEXT NULL, -- Type MIME
    image INTEGER NOT NULL DEFAULT 0, -- 1 = image reconnue
    datetime TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(datetime) IS NOT NULL AND datetime(datetime) = datetime), -- Date d'ajout ou mise à jour du fichier
    id_contenu INTEGER NOT NULL REFERENCES fichiers_contenu (id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS fichiers_date ON fichiers (datetime);

CREATE TABLE IF NOT EXISTS fichiers_contenu
-- Contenu des fichiers
(
    id INTEGER NOT NULL PRIMARY KEY,
    hash TEXT NOT NULL, -- Hash SHA1 du contenu du fichier
    taille INTEGER NOT NULL, -- Taille en octets
    contenu BLOB NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS fichiers_hash ON fichiers_contenu (hash);

CREATE TABLE IF NOT EXISTS fichiers_membres
-- Associations entre fichiers et membres (photo de profil par exemple)
(
    fichier INTEGER NOT NULL REFERENCES fichiers (id) ON DELETE CASCADE,
    id INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    PRIMARY KEY(fichier, id)
);

CREATE TABLE IF NOT EXISTS fichiers_wiki_pages
-- Associations entre fichiers et pages du wiki
(
    fichier INTEGER NOT NULL REFERENCES fichiers (id) ON DELETE CASCADE,
    id INTEGER NOT NULL REFERENCES wiki_pages (id) ON DELETE CASCADE,
    PRIMARY KEY(fichier, id)
);

CREATE TABLE IF NOT EXISTS fichiers_acc_transactions
-- Associations entre fichiers et journal de compta (pièce comptable par exemple)
(
    fichier INTEGER NOT NULL REFERENCES fichiers (id) ON DELETE CASCADE,
    id INTEGER NOT NULL REFERENCES acc_transactions (id) ON DELETE CASCADE,
    PRIMARY KEY(fichier, id)
);

CREATE TABLE IF NOT EXISTS recherches
-- Recherches enregistrées
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_membre INTEGER NULL REFERENCES membres (id) ON DELETE CASCADE, -- Si non NULL, alors la recherche ne sera visible que par le membre associé
    intitule TEXT NOT NULL,
    creation TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(creation) IS NOT NULL AND datetime(creation) = creation),
    cible TEXT NOT NULL, -- "membres" ou "compta"
    type TEXT NOT NULL, -- "json" ou "sql"
    contenu TEXT NOT NULL
);


CREATE TABLE IF NOT EXISTS compromised_passwords_cache
-- Cache des hash de mots de passe compromis
(
    hash TEXT NOT NULL PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS compromised_passwords_cache_ranges
-- Cache des préfixes de mots de passe compromis
(
    prefix TEXT NOT NULL PRIMARY KEY,
    date INTEGER NOT NULL
);

UPDATE acc_accounts SET position = 3 WHERE code = '445' OR code = '444' AND id_chart IN (SELECT id FROM acc_charts WHERE code = 'PCGA1999');

UPDATE acc_transactions SET label = '[ERREUR ! À corriger !] ' || label, status = 8 WHERE id IN (
	SELECT DISTINCT t.id
		FROM acc_transactions t
		INNER JOIN acc_transactions_lines l ON l.id_transaction = t.id
		INNER JOIN acc_accounts a ON l.id_account = a.id
		INNER JOIN acc_years y ON y.id = t.id_year AND y.closed = 0 AND y.id_chart != a.id_chart
);

UPDATE services_fees SET id_account = NULL, id_year = NULL WHERE id_account IN (
	SELECT sf.id_account FROM services_fees sf
		INNER JOIN acc_accounts a ON a.id = sf.id_account
		INNER JOIN acc_years y ON y.id = sf.id_year
	WHERE a.id_chart != y.id_chart);

-- Fix services connected to old closed year
UPDATE services_fees SET id_year = NULL, id_account = NULL WHERE id_year IN (SELECT id FROM acc_years WHERE closed = 1);

-- Fix credit/debt payment types
UPDATE acc_transactions SET type = 0 WHERE id_related IS NOT NULL AND type IN (4,5);

-- Add indexes
DROP INDEX IF EXISTS acc_transactions_type;
CREATE INDEX IF NOT EXISTS acc_transactions_type ON acc_transactions (type, id_year);

CREATE INDEX IF NOT EXISTS acc_transactions_lines_transaction ON acc_transactions_lines (id_transaction);

ALTER TABLE membres RENAME TO users;
ALTER TABLE plugins RENAME TO plugins_old;

-- References old membres table
ALTER TABLE services_users RENAME TO services_users_old;
ALTER TABLE services_reminders_sent RENAME TO services_reminders_sent_old;
ALTER TABLE acc_transactions RENAME TO acc_transactions_old;
ALTER TABLE acc_transactions_users RENAME TO acc_transactions_users_old;

.read 1.2.0_schema.sql

INSERT INTO services_users SELECT * FROM services_users_old;
INSERT INTO services_reminders_sent SELECT * FROM services_reminders_sent_old;
INSERT INTO acc_transactions SELECT * FROM acc_transactions_old;
INSERT INTO acc_transactions_users SELECT * FROM acc_transactions_users_old;

DROP TABLE services_reminders_sent_old;
DROP TABLE acc_transactions_users_old;
DROP TABLE acc_transactions_old;
DROP TABLE services_users_old;

INSERT INTO plugins_signals SELECT * FROM plugins_signaux;
INSERT INTO plugins SELECT * FROM plugins_old;

DROP TABLE plugins_signaux;
DROP TABLE plugins_old;

INSERT INTO searches SELECT * FROM recherches;
UPDATE searches SET target = 'accounting' WHERE target = 'compta';
UPDATE searches SET target = 'users' WHERE target = 'membres';

DROP TABLE recherches;

INSERT INTO config VALUES ('log_retention', 720);
INSERT INTO config VALUES ('log_anonymize', 365);

---
--- Main stuff
---

CREATE TABLE IF NOT EXISTS config (
-- Configuration, key/value store
    key TEXT PRIMARY KEY NOT NULL,
    value TEXT NULL
);

CREATE TABLE IF NOT EXISTS config_users_fields (
    name TEXT PRIMARY KEY NOT NULL,
    order INTEGER NOT NULL,
    type TEXT NOT NULL,
    label TEXT NOT NULL,
    help TEXT NULL
    mandatory INTEGER NOT NULL DEFAULT 0,
    private INTEGER NOT NULL DEFAULT 0,
    user_editable INTEGER NOT NULL DEFAULT 1,
    list_row INTEGER NULL,
    options TEXT,
    system TEXT NULL
);

CREATE TABLE IF NOT EXISTS plugins
(
    id TEXT NOT NULL PRIMARY KEY,
    official INTEGER NOT NULL DEFAULT 0, -- 1 if plugin is official
    name TEXT NOT NULL,
    description TEXT NULL,
    author TEXT NULL,
    url TEXT NULL,
    version TEXT NOT NULL,
    menu INTEGER NOT NULL DEFAULT 0, -- 1 if plugin should be shown in sidebar menu
    menu_condition TEXT NULL, -- Brindille condition to know if item should be shown in menu
    config TEXT NULL
);

CREATE TABLE IF NOT EXISTS plugins_signals
-- Link between plugins and signals
(
    signal TEXT NOT NULL,
    plugin TEXT NOT NULL REFERENCES plugins (id),
    callback TEXT NOT NULL,
    PRIMARY KEY (signal, plugin)
);

CREATE TABLE IF NOT EXISTS searches
-- Saved searches
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_user INTEGER NULL REFERENCES users (id) ON DELETE CASCADE, -- If not NULL, then search will only be visible by this user
    label TEXT NOT NULL,
    created TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(created) IS NOT NULL AND datetime(created) = created),
    target TEXT NOT NULL, -- "users" ou "accounting"
    type TEXT NOT NULL, -- "json" ou "sql"
    content TEXT NOT NULL
);


CREATE TABLE IF NOT EXISTS compromised_passwords_cache
-- Cache des hash de mots de passe compromis
(
    hash TEXT NOT NULL PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS compromised_passwords_cache_ranges
-- Cache des préfixes de mots de passe compromis
(
    prefix TEXT NOT NULL PRIMARY KEY,
    date INTEGER NOT NULL
);

---
--- Users
---

-- CREATE TABLE users (...);
-- Organization users table, dynamically created, see config_users_fields table

CREATE TABLE IF NOT EXISTS users_categories
-- Users categories, mainly used to manage rights
(
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,

    -- Permissions, 0 = no access, 1 = read-only, 2 = read-write, 9 = admin
    perm_web INTEGER NOT NULL DEFAULT 1,
    perm_documents INTEGER NOT NULL DEFAULT 1,
    perm_users INTEGER NOT NULL DEFAULT 1,
    perm_accounting INTEGER NOT NULL DEFAULT 1,

    perm_subscribe INTEGER NOT NULL DEFAULT 0,
    perm_connect INTEGER NOT NULL DEFAULT 1,
    perm_config INTEGER NOT NULL DEFAULT 0,

    hidden INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS users_categories_hidden ON users_categories (hidden);

CREATE TABLE IF NOT EXISTS users_sessions
-- Permanent sessions for logged-in users
(
    selector TEXT NOT NULL,
    hash TEXT NOT NULL,
    id_user INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    expire INT NOT NULL,

    PRIMARY KEY (selector, id_user)
);

CREATE TABLE IF NOT EXISTS logs
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_user INTEGER NULL REFERENCES users (id),
    type INTEGER NOT NULL,
    details TEXT NULL,
    created TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(created) IS NOT NULL AND datetime(created) = created),
    ip_address TEXT NULL
);

CREATE INDEX logs_ip ON logs (ip_address, created);
CREATE INDEX logs_user ON logs (id_user, created);
CREATE INDEX logs_created ON logs (created);

---
--- Services
---

CREATE TABLE IF NOT EXISTS services
-- Services types (French: cotisations)
(
    id INTEGER PRIMARY KEY NOT NULL,

    label TEXT NOT NULL,
    description TEXT NULL,

    duration INTEGER NULL CHECK (duration IS NULL OR duration > 0), -- En jours
    start_date TEXT NULL CHECK (start_date IS NULL OR date(start_date) = start_date),
    end_date TEXT NULL CHECK (end_date IS NULL OR (date(end_date) = end_date AND date(end_date) >= date(start_date)))
);

CREATE TABLE IF NOT EXISTS services_fees
-- Services fees
(
    id INTEGER PRIMARY KEY NOT NULL,

    label TEXT NOT NULL,
    description TEXT NULL,

    amount INTEGER NULL,
    formula TEXT NULL, -- Formula to calculate fee amount dynamically (this contains a SQL statement)

    id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE,
    id_account INTEGER NULL REFERENCES acc_accounts (id) ON DELETE SET NULL CHECK (id_account IS NULL OR id_year IS NOT NULL), -- NULL if fee is not linked to accounting, this is reset using a trigger if the year is deleted
    id_year INTEGER NULL REFERENCES acc_years (id) ON DELETE SET NULL -- NULL if fee is not linked to accounting
);

CREATE TABLE IF NOT EXISTS services_users
-- Records of services and fees linked to users
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_user INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE,
    id_fee INTEGER NULL REFERENCES services_fees (id) ON DELETE CASCADE,

    paid INTEGER NOT NULL DEFAULT 0,
    expected_amount INTEGER NULL,

    date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date),
    expiry_date TEXT NULL CHECK (date(expiry_date) IS NULL OR date(expiry_date) = expiry_date)
);

CREATE UNIQUE INDEX IF NOT EXISTS su_unique ON services_users (id_user, id_service, date);

CREATE INDEX IF NOT EXISTS su_service ON services_users (id_service);
CREATE INDEX IF NOT EXISTS su_fee ON services_users (id_fee);
CREATE INDEX IF NOT EXISTS su_paid ON services_users (paid);
CREATE INDEX IF NOT EXISTS su_expiry ON services_users (expiry_date);

CREATE TABLE IF NOT EXISTS services_reminders
-- Reminders for service expiry
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE,

    delay INTEGER NOT NULL, -- Delay in days before or after expiry date

    subject TEXT NOT NULL,
    body TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS services_reminders_sent
-- Records of sent reminders, to keep track
(
    id INTEGER NOT NULL PRIMARY KEY,

    id_user INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE,
    id_reminder INTEGER NOT NULL REFERENCES services_reminders (id) ON DELETE CASCADE,

    sent_date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(sent_date) IS NOT NULL AND date(sent_date) = sent_date),
    due_date TEXT NOT NULL CHECK (date(due_date) IS NOT NULL AND date(due_date) = due_date)
);

CREATE UNIQUE INDEX IF NOT EXISTS srs_index ON services_reminders_sent (id_user, id_service, id_reminder, due_date);

CREATE INDEX IF NOT EXISTS srs_reminder ON services_reminders_sent (id_reminder);
CREATE INDEX IF NOT EXISTS srs_user ON services_reminders_sent (id_user);

--
-- Accounting
--

CREATE TABLE IF NOT EXISTS acc_charts
-- Accounting charts (plans comptables)
(
    id INTEGER NOT NULL PRIMARY KEY,
    country TEXT NOT NULL,
    code TEXT NULL, -- the code is NULL if the chart is user-created or imported
    label TEXT NOT NULL,
    archived INTEGER NOT NULL DEFAULT 0 -- 1 = archived, cannot be changed
);

CREATE TABLE IF NOT EXISTS acc_accounts
-- Accounts of the charts (comptes)
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_chart INTEGER NOT NULL REFERENCES acc_charts ON DELETE CASCADE,

    code TEXT NOT NULL, -- can contain numbers and letters, eg. 53A, 53B...

    label TEXT NOT NULL,
    description TEXT NULL,

    position INTEGER NOT NULL, -- position in the balance sheet (position actif/passif/charge/produit)
    type INTEGER NOT NULL DEFAULT 0, -- type (category) of favourite account: bank, cash, third party, etc.
    user INTEGER NOT NULL DEFAULT 1 -- 0 = is part of the original chart, 0 = has been added by the user
);

CREATE UNIQUE INDEX IF NOT EXISTS acc_accounts_codes ON acc_accounts (code, id_chart);
CREATE INDEX IF NOT EXISTS acc_accounts_type ON acc_accounts (type);
CREATE INDEX IF NOT EXISTS acc_accounts_position ON acc_accounts (position);

CREATE TABLE IF NOT EXISTS acc_years
-- Years (exercices)
(
    id INTEGER NOT NULL PRIMARY KEY,

    label TEXT NOT NULL,

    start_date TEXT NOT NULL CHECK (date(start_date) IS NOT NULL AND date(start_date) = start_date),
    end_date TEXT NOT NULL CHECK (date(end_date) IS NOT NULL AND date(end_date) = end_date),

    closed INTEGER NOT NULL DEFAULT 0, -- 0 = open, 1 = closed

    id_chart INTEGER NOT NULL REFERENCES acc_charts (id)
);

CREATE INDEX IF NOT EXISTS acc_years_closed ON acc_years (closed);

-- Make sure id_account is reset when a year is deleted
CREATE TRIGGER IF NOT EXISTS acc_years_delete BEFORE DELETE ON acc_years BEGIN
    UPDATE services_fees SET id_account = NULL, id_year = NULL WHERE id_year = OLD.id;
END;

CREATE TABLE IF NOT EXISTS acc_transactions
-- Transactions (écritures comptables)
(
    id INTEGER PRIMARY KEY NOT NULL,

    type INTEGER NOT NULL DEFAULT 0, -- Transaction type, zero is advanced
    status INTEGER NOT NULL DEFAULT 0, -- Status (bitmask)

    label TEXT NOT NULL,
    notes TEXT NULL,
    reference TEXT NULL, -- N° de pièce comptable

    date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date),

    validated INTEGER NOT NULL DEFAULT 0, -- 1 means transaction is locked

    hash TEXT NULL,
    prev_hash TEXT NULL,

    id_year INTEGER NOT NULL REFERENCES acc_years(id),
    id_creator INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
    id_related INTEGER NULL REFERENCES acc_transactions(id) ON DELETE SET NULL -- linked transaction (eg. payment of a debt)
);

CREATE INDEX IF NOT EXISTS acc_transactions_year ON acc_transactions (id_year);
CREATE INDEX IF NOT EXISTS acc_transactions_date ON acc_transactions (date);
CREATE INDEX IF NOT EXISTS acc_transactions_related ON acc_transactions (id_related);
CREATE INDEX IF NOT EXISTS acc_transactions_type ON acc_transactions (type, id_year);
CREATE INDEX IF NOT EXISTS acc_transactions_status ON acc_transactions (status);

CREATE TABLE IF NOT EXISTS acc_transactions_lines
-- Transactions lines (lignes des écritures)
(
    id INTEGER PRIMARY KEY NOT NULL,

    id_transaction INTEGER NOT NULL REFERENCES acc_transactions (id) ON DELETE CASCADE,
    id_account INTEGER NOT NULL REFERENCES acc_accounts (id),

    credit INTEGER NOT NULL,
    debit INTEGER NOT NULL,

    reference TEXT NULL, -- Usually a payment reference (par exemple numéro de chèque)
    label TEXT NULL,

    reconciled INTEGER NOT NULL DEFAULT 0,

    id_analytical INTEGER NULL REFERENCES acc_accounts(id) ON DELETE SET NULL,

    CONSTRAINT line_check1 CHECK ((credit * debit) = 0),
    CONSTRAINT line_check2 CHECK ((credit + debit) > 0)
);

CREATE INDEX IF NOT EXISTS acc_transactions_lines_transaction ON acc_transactions_lines (id_transaction);
CREATE INDEX IF NOT EXISTS acc_transactions_lines_account ON acc_transactions_lines (id_account);
CREATE INDEX IF NOT EXISTS acc_transactions_lines_analytical ON acc_transactions_lines (id_analytical);
CREATE INDEX IF NOT EXISTS acc_transactions_lines_reconciled ON acc_transactions_lines (reconciled);

CREATE TABLE IF NOT EXISTS acc_transactions_users
-- Linking transactions and users
(
    id_user INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    id_transaction INTEGER NOT NULL REFERENCES acc_transactions (id) ON DELETE CASCADE,
    id_service_user INTEGER NULL REFERENCES services_users (id) ON DELETE SET NULL,

    PRIMARY KEY (id_user, id_transaction)
);

CREATE INDEX IF NOT EXISTS acc_transactions_users_service ON acc_transactions_users (id_service_user);

---------- FILES ----------------

CREATE TABLE IF NOT EXISTS files
-- Files metadata
(
    id INTEGER NOT NULL PRIMARY KEY,
    path TEXT NOT NULL,
    parent TEXT NOT NULL,
    name TEXT NOT NULL, -- File name
    type INTEGER NOT NULL, -- File type, 1 = file, 2 = directory
    mime TEXT NULL,
    size INT NULL,
    modified TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (ddatetime(modified) IS NOT NULL AND atetime(modified) = modified),
    image INT NOT NULL DEFAULT 0,

    CHECK (type = 2 OR (mime IS NOT NULL AND size IS NOT NULL))
);

-- Unique index as this is used to make up a file path
CREATE UNIQUE INDEX IF NOT EXISTS files_unique ON files (path);
CREATE INDEX IF NOT EXISTS files_parent ON files (parent);
CREATE INDEX IF NOT EXISTS files_name ON files (name);
CREATE INDEX IF NOT EXISTS files_modified ON files (modified);

CREATE TABLE IF NOT EXISTS files_contents
-- Files contents (empty if using another storage backend)
(
    id INTEGER NOT NULL PRIMARY KEY REFERENCES files(id) ON DELETE CASCADE,
    compressed INT NOT NULL DEFAULT 0,
    content BLOB NOT NULL
);

CREATE VIRTUAL TABLE IF NOT EXISTS files_search USING fts4
-- Search inside files content
(
    tokenize=unicode61, -- Available from SQLITE 3.7.13 (2012)
    path TEXT NOT NULL,
    title TEXT NULL,
    content TEXT NOT NULL, -- Text content
    notindexed=path
);

CREATE TABLE IF NOT EXISTS web_pages
(
    id INTEGER NOT NULL PRIMARY KEY,
    parent TEXT NOT NULL, -- Parent path, empty = web root
    path TEXT NOT NULL, -- Full page directory name
    uri TEXT NOT NULL, -- Page identifier
    file_path TEXT NOT NULL, -- Full file path for contents
    type INTEGER NOT NULL, -- 1 = Category, 2 = Page
    status TEXT NOT NULL,
    format TEXT NOT NULL,
    published TEXT NOT NULL CHECK (datetime(published) IS NOT NULL AND datetime(published) = published),
    modified TEXT NOT NULL CHECK (datetime(modified) IS NOT NULL AND datetime(modified) = modified),
    title TEXT NOT NULL,
    content TEXT NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS web_pages_path ON web_pages (path);
CREATE UNIQUE INDEX IF NOT EXISTS web_pages_uri ON web_pages (uri);
CREATE UNIQUE INDEX IF NOT EXISTS web_pages_file_path ON web_pages (file_path);
CREATE INDEX IF NOT EXISTS web_pages_parent ON web_pages (parent);
CREATE INDEX IF NOT EXISTS web_pages_published ON web_pages (published);
CREATE INDEX IF NOT EXISTS web_pages_title ON web_pages (title);

;	Ce fichier contient la configuration par défaut des champs des fiches membres.
;	La configuration est ensuite enregistrée au format INI dans la table 
;	config de la base de données.
;
;	Syntaxe :
;
;	[nom_du_champ] ; Nom unique du champ, ne peut contenir que des lettres et des tirets bas
;	type = text
;	title = "Super champ trop cool"
;	mandatory = true
;	editable = false
;
;	Description des options possibles pour chaque champ :
;
;	type: (défaut: text) OBLIGATOIRE
;		certains types gérés par <input type> de HTML5 :
;		text, number, date, datetime, url, email, checkbox, file, password, tel
;		champs spécifiques :
;		- country = sélecteur de pays
;		- textarea = texte multi lignes
;		- multiple = multiples cases à cocher (jusqu'à 32, binaire)
;		- select = un choix parmis plusieurs
;	title: OBLIGATOIRE
;		Titre du champ
;	help:
;		Texte d'aide sur les fiches membres
;	options[]:
;		pour définir les options d'un champ de type select ou multiple
;	editable:
;		true = modifiable par le membre
;		false = modifiable uniquement par un admin (défaut)
;	mandatory:
;		true = obligatoire, la fiche membre ne pourra être enregistrée si ce champ est vide
;		false = facultatif (défaut)
;	private:
;		true = non visible par le membre lui-même
;		false = visible par le membre (défaut)
;	list_row:
;		Si absent ou zéro ('0') ou false, ce champ n'apparaîtra pas dans la liste des membres
;		Si présent et un chiffre supérieur à 0, alors le champ apparaîtra dans la liste des membres
;		dans l'ordre défini par le chiffre (si nom est à 2 et email à 1, alors email sera
;		la première colonne et nom la seconde)
;	install:
;		true = sera ajouté aux fiches membres à l'installation
;		false = sera seulement présent dans les champs supplémentaires possibles (défaut)

[numero]
type = number
title = "Numéro de membre"
help = "Doit être unique, laisser vide pour que le numéro soit attribué automatiquement"
mandatory = false
install = true
editable = false
list_row = 1

[nom]
type = text
title = "Nom & prénom"
mandatory = true
install = true
editable = true
list_row = 2

[email]
; ce champ est facultatif et de type 'email'
type = email
title = "Adresse E-Mail"
mandatory = false
install = true
editable = true

[passe]
; ce champ est obligatoirement présent et de type 'password'
; le titre ne peut être modifié
type = password
mandatory = false
install = true
editable = true

[adresse]
type = textarea
title = "Adresse postale"
help = "Indiquer ici le numéro, le type de voie, etc."
install = true
editable = true

[code_postal]
type = text
title = "Code postal"
install = true
editable = true
list_row = 3

[ville]
type = text
title = "Ville"
install = true
editable = true
list_row = 4

[pays]
type = country
title = "Pays"
install = true
editable = true

[telephone]
type = tel
title = "Numéro de téléphone"
install = true
editable = true

[lettre_infos]
type = checkbox
title = "Inscription à la lettre d'information"
install = true
editable = true

[groupe_travail]
type = multiple
title = "Groupes de travail"
editable = false
options[] = "Télécoms"
options[] = "Trésorerie"
options[] = "Relations publiques"
options[] = "Communication presse"
options[] = "Organisation d'événements"

[date_naissance]
type = date
title = "Date de naissance"
editable = true

[notes]
type = textarea
title = "Notes"
editable = false
private = true

[photo]
type = file
title = "Photo"
editable = false
private = false

;	Ce fichier contient la configuration par défaut des champs des fiches membres.
;	La configuration est ensuite enregistrée au format INI dans la table 
;	config de la base de données.
;
;	Syntaxe :
;
;	[nom_du_champ] ; Nom unique du champ, ne peut contenir que des lettres et des tirets bas
;	type = text
;	label = "Super champ trop cool"
;	mandatory = true
;	user_editable = false
;
;	Description des options possibles pour chaque champ :
;
;	type: (défaut: text) OBLIGATOIRE
;		certains types gérés par <input type> de HTML5 :
;		text, number, date, datetime, url, email, checkbox, file, password, tel
;		champs spécifiques :
;		- country = sélecteur de pays
;		- textarea = texte multi lignes
;		- multiple = multiples cases à cocher (jusqu'à 32, binaire)
;		- select = un choix parmis plusieurs
;	label: OBLIGATOIRE
;		Titre du champ
;	help:
;		Texte d'aide sur les fiches membres
;	options[]:
;		pour définir les options d'un champ de type select ou multiple
;	user_editable:
;		true = modifiable par le membre
;		false = modifiable uniquement par un admin (défaut)
;	mandatory:
;		true = obligatoire, la fiche membre ne pourra être enregistrée si ce champ est vide
;		false = facultatif (défaut)
;	private:
;		true = non visible par le membre lui-même
;		false = visible par le membre (défaut)
;	list_row:
;		Si absent ou zéro ('0') ou false, ce champ n'apparaîtra pas dans la liste des membres
;		Si présent et un chiffre supérieur à 0, alors le champ apparaîtra dans la liste des membres
;		dans l'ordre défini par le chiffre (si nom est à 2 et email à 1, alors email sera
;		la première colonne et nom la seconde)
;	install:
;		true = sera ajouté aux fiches membres à l'installation
;		false = sera seulement présent dans les champs supplémentaires possibles (défaut)

[numero]
type = number
label = "Numéro de membre"
help = "Doit être unique, laisser vide pour que le numéro soit attribué automatiquement"
mandatory = true
install = true
user_editable = false
list_row = 1

[nom]
type = text
label = "Nom & prénom"
mandatory = true
install = true
user_editable = true
list_row = 2

[email]
; ce champ est facultatif et de type 'email'
type = email
label = "Adresse E-Mail"
mandatory = false
install = true
user_editable = true

[passe]
; ce champ est obligatoirement présent et de type 'password'
; le titre ne peut être modifié
type = password
mandatory = false
install = true
user_editable = true

[adresse]
type = textarea
label = "Adresse postale"
help = "Indiquer ici le numéro, le type de voie, etc."
install = true
user_editable = true

[code_postal]
type = text
label = "Code postal"
install = true
user_editable = true
list_row = 3

[ville]
type = text
label = "Ville"
install = true
user_editable = true
list_row = 4

[pays]
type = country
label = "Pays"
install = true
user_editable = true

[telephone]
type = tel
label = "Numéro de téléphone"
install = true
user_editable = true

[lettre_infos]
type = checkbox
label = "Inscription à la lettre d'information"
install = true
user_editable = true

[groupe_travail]
type = multiple
label = "Groupes de travail"
user_editable = false
options[] = "Télécoms"
options[] = "Trésorerie"
options[] = "Relations publiques"
options[] = "Communication presse"
options[] = "Organisation d'événements"

[date_naissance]
type = date
label = "Date de naissance"
user_editable = true

[notes]
type = textarea
label = "Notes"
user_editable = false
private = true

[photo]
type = file
label = "Photo"
user_editable = false
private = false

<?php

namespace Garradin;

use Garradin\Files\Files;
use Garradin\Entities\Files\File;
use Garradin\Membres\Champs;

use KD2\SMTP;

class Config extends Entity
{
	const DEFAULT_FILES = [
		'admin_background' => File::CONTEXT_CONFIG . '/admin_bg.png',
................................................................................
	protected $email_asso;
	protected $telephone_asso;
	protected $site_asso;

	protected $monnaie;
	protected $pays;

	protected $champs_membres;
	protected $categorie_membres;

	protected $frequence_sauvegardes;
	protected $nombre_sauvegardes;

	protected $champ_identifiant;
	protected $champ_identite;
................................................................................

	protected $admin_background;
	protected $admin_homepage = 'config/admin_homepage.skriv';
	protected $admin_css = 'config/admin.css';

	protected $site_disabled;






	protected $_types = [
		'nom_asso'              => 'string',
		'adresse_asso'          => '?string',
		'email_asso'            => 'string',
		'telephone_asso'        => '?string',
		'site_asso'             => '?string',

		'monnaie'               => 'string',
		'pays'                  => 'string',

		'champs_membres'        => Champs::class,

		'categorie_membres'     => 'int',

		'frequence_sauvegardes' => '?int',
		'nombre_sauvegardes'    => '?int',

		'champ_identifiant'     => 'string',
		'champ_identite'        => 'string',
................................................................................
		'couleur1'              => '?string',
		'couleur2'              => '?string',
		'admin_background'      => '?string',
		'admin_homepage'        => '?string',
		'admin_css'             => '?string',

		'site_disabled'         => 'bool',



	];

	static protected $_instance = null;

	static public function getInstance()
	{
		return self::$_instance ?: self::$_instance = new self;
................................................................................
		if (empty($config)) {
			return;
		}

		$default = array_fill_keys(array_keys($this->_types), null);
		$config = array_merge($default, $config);

		$config['champs_membres'] = new Champs($config['champs_membres']);

		foreach ($this->_types as $key => $type) {
			$value = $config[$key];

			if ($type[0] == '?' && $value === null) {
				continue;
			}
		}

		$this->load($config);

		$this->champs_membres = new Membres\Champs((string)$this->champs_membres);
	}

	public function save(): bool
	{
		if (!count($this->_modified)) {
			return true;
		}
................................................................................
		$values = [];
		$db = DB::getInstance();

		foreach ($this->_modified as $key => $modified) {
			$value = $this->$key;
			$type = ltrim($this->_types[$key], '?');

			if ($type == Champs::class) {
				$value = $value->toString();
			}
			elseif (is_object($value)) {
				throw new \UnexpectedValueException('Unexpected object as value: ' . get_class($value));
			}

			$values[$key] = $value;
		}

		unset($value, $key, $modified);
................................................................................

		foreach ($values as $key => $value)
		{
			$db->preparedQuery('INSERT OR REPLACE INTO config (key, value) VALUES (?, ?);', $key, $value);
		}

		if (!empty($values['champ_identifiant'])) {
			// Regenerate login index
			$db->exec('DROP INDEX IF EXISTS users_id_field;');
			$config = Config::getInstance();
			$champs = $config->get('champs_membres');
			$champs->createIndexes();
		}

		$db->commit();

		$this->_modified = [];

		return true;
................................................................................
		switch ($this->_types[$key]) {
			case 'int':
				return (int) $value;
			case 'bool':
				return (bool) $value;
			case 'string':
				return (string) $value;
			case Champs::class:
				if (!is_object($value) || !($value instanceof $this->_types[$key])) {
					throw new \InvalidArgumentException(sprintf('"%s" is not of type "%s"', $key, $this->_types[$key]));
				}
				return $value;
			default:
				throw new \InvalidArgumentException(sprintf('"%s" has unknown type "%s"', $key, $this->_types[$key]));
		}
	}

	public function selfCheck(): void
	{
		$this->assert(trim($this->nom_asso) != '', 'Le nom de l\'association ne peut rester vide.');
		$this->assert(trim($this->monnaie) != '', 'La monnaie ne peut rester vide.');
		$this->assert(trim($this->pays) != '' && Utils::getCountryName($this->pays), 'Le pays ne peut rester vide.');
		$this->assert(null === $this->site_asso || filter_var($this->site_asso, FILTER_VALIDATE_URL), 'L\'adresse URL du site web est invalide.');
		$this->assert(trim($this->email_asso) != '' && SMTP::checkEmailIsValid($this->email_asso, false), 'L\'adresse e-mail de l\'association est  invalide.');
		$this->assert($this->champs_membres instanceof Champs, 'Objet champs membres invalide');

		// Files can only have one value: their name
		$this->assert($this->admin_background === null || $this->admin_background === self::DEFAULT_FILES['admin_background']);
		$this->assert($this->admin_homepage === null || $this->admin_homepage === self::DEFAULT_FILES['admin_homepage']);
		$this->assert($this->admin_css === null || $this->admin_css === self::DEFAULT_FILES['admin_css']);

		$champs = $this->champs_membres;

<?php

namespace Garradin;

use Garradin\Files\Files;
use Garradin\Entities\Files\File;


use KD2\SMTP;

class Config extends Entity
{
	const DEFAULT_FILES = [
		'admin_background' => File::CONTEXT_CONFIG . '/admin_bg.png',
................................................................................
	protected $email_asso;
	protected $telephone_asso;
	protected $site_asso;

	protected $monnaie;
	protected $pays;


	protected $categorie_membres;

	protected $frequence_sauvegardes;
	protected $nombre_sauvegardes;

	protected $champ_identifiant;
	protected $champ_identite;
................................................................................

	protected $admin_background;
	protected $admin_homepage = 'config/admin_homepage.skriv';
	protected $admin_css = 'config/admin.css';

	protected $site_disabled;

	protected $log_retention;
	protected $log_anonymize;

	protected $field_user_number;

	protected $_types = [
		'nom_asso'              => 'string',
		'adresse_asso'          => '?string',
		'email_asso'            => 'string',
		'telephone_asso'        => '?string',
		'site_asso'             => '?string',

		'monnaie'               => 'string',
		'pays'                  => 'string',



		'categorie_membres'     => 'int',

		'frequence_sauvegardes' => '?int',
		'nombre_sauvegardes'    => '?int',

		'champ_identifiant'     => 'string',
		'champ_identite'        => 'string',
................................................................................
		'couleur1'              => '?string',
		'couleur2'              => '?string',
		'admin_background'      => '?string',
		'admin_homepage'        => '?string',
		'admin_css'             => '?string',

		'site_disabled'         => 'bool',

		'log_retention'         => 'int',
		'log_anonymize'         => 'int',
	];

	static protected $_instance = null;

	static public function getInstance()
	{
		return self::$_instance ?: self::$_instance = new self;
................................................................................
		if (empty($config)) {
			return;
		}

		$default = array_fill_keys(array_keys($this->_types), null);
		$config = array_merge($default, $config);



		foreach ($this->_types as $key => $type) {
			$value = $config[$key];

			if ($type[0] == '?' && $value === null) {
				continue;
			}
		}

		$this->load($config);


	}

	public function save(): bool
	{
		if (!count($this->_modified)) {
			return true;
		}
................................................................................
		$values = [];
		$db = DB::getInstance();

		foreach ($this->_modified as $key => $modified) {
			$value = $this->$key;
			$type = ltrim($this->_types[$key], '?');




			if (is_object($value)) {
				throw new \UnexpectedValueException('Unexpected object as value: ' . get_class($value));
			}

			$values[$key] = $value;
		}

		unset($value, $key, $modified);
................................................................................

		foreach ($values as $key => $value)
		{
			$db->preparedQuery('INSERT OR REPLACE INTO config (key, value) VALUES (?, ?);', $key, $value);
		}

		if (!empty($values['champ_identifiant'])) {
			DynamicFields::changeLoginField($values['champ_identifiant']);




		}

		$db->commit();

		$this->_modified = [];

		return true;
................................................................................
		switch ($this->_types[$key]) {
			case 'int':
				return (int) $value;
			case 'bool':
				return (bool) $value;
			case 'string':
				return (string) $value;





			default:
				throw new \InvalidArgumentException(sprintf('"%s" has unknown type "%s"', $key, $this->_types[$key]));
		}
	}

	public function selfCheck(): void
	{
		$this->assert(trim($this->nom_asso) != '', 'Le nom de l\'association ne peut rester vide.');
		$this->assert(trim($this->monnaie) != '', 'La monnaie ne peut rester vide.');
		$this->assert(trim($this->pays) != '' && Utils::getCountryName($this->pays), 'Le pays ne peut rester vide.');
		$this->assert(null === $this->site_asso || filter_var($this->site_asso, FILTER_VALIDATE_URL), 'L\'adresse URL du site web est invalide.');
		$this->assert(trim($this->email_asso) != '' && SMTP::checkEmailIsValid($this->email_asso, false), 'L\'adresse e-mail de l\'association est  invalide.');


		// Files can only have one value: their name
		$this->assert($this->admin_background === null || $this->admin_background === self::DEFAULT_FILES['admin_background']);
		$this->assert($this->admin_homepage === null || $this->admin_homepage === self::DEFAULT_FILES['admin_homepage']);
		$this->assert($this->admin_css === null || $this->admin_css === self::DEFAULT_FILES['admin_css']);

		$champs = $this->champs_membres;

		foreach ($lines as $line) {
			$total += $line->credit;
			$total -= $line->debit;
		}

		$this->assert(0 === $total, sprintf('Écriture non équilibrée : déséquilibre (%s) entre débits et crédits', Utils::money_format($total)));

		$this->assert($db->test('acc_years', 'id = ?', $this->id_year), 'L\'exercice sélectionné n\'existe pas');
		$this->assert($this->id_creator === null || $db->test('membres', 'id = ?', $this->id_creator), 'Le compte membre créateur de l\'écriture n\'existe pas');
	}

	public function importFromDepositForm(?array $source = null): void
	{
		if (null === $source) {
			$source = $_POST;
		}

		foreach ($lines as $line) {
			$total += $line->credit;
			$total -= $line->debit;
		}

		$this->assert(0 === $total, sprintf('Écriture non équilibrée : déséquilibre (%s) entre débits et crédits', Utils::money_format($total)));



	}

	public function importFromDepositForm(?array $source = null): void
	{
		if (null === $source) {
			$source = $_POST;
		}

<?php

namespace Garradin\Users;

use Garradin\Config;
use Garradin\DB;
use Garradin\Entity;
use Garradin\Utils;
use Garradin\Users\DynamicFields;

class DynamicField extends Entity
{
	const TABLE = 'config_users_fields';

	protected $name;
	protected $order;
	protected $type;
	protected $label;
	protected $help;
	protected $mandatory;
	protected $private;
	protected $user_editable;
	protected $list_row;
	protected $options;
	protected $system;

	protected $_types = [
		'name'          => 'string',
		'order'         => 'int',
		'type'          => 'string',
		'label'         => 'string',
		'help'          => '?string',
		'mandatory'     => 'bool',
		'private'       => 'bool',
		'user_editable' => 'bool',
		'list_row'      => '?int',
		'options'       => '?string',
		'system'        => '?string',
	];


	const TYPES = [
		'email'		=>	'Adresse E-Mail',
		'url'		=>	'Adresse URL',
		'checkbox'	=>	'Case à cocher',
		'date'		=>	'Date',
		'datetime'	=>	'Date et heure',
		'file'      =>  'Fichier',
		'password'  =>  'Mot de passe',
		'number'	=>	'Numéro',
		'tel'		=>	'Numéro de téléphone',
		'select'	=>	'Sélecteur à choix unique',
		'multiple'  =>  'Sélecteur à choix multiple',
		'country'	=>	'Sélecteur de pays',
		'text'		=>	'Texte',
		'textarea'	=>	'Texte multi-lignes',
	];

	const PHP_TYPES = [
		'email'    => '?string',
		'url'      => '?string',
		'checkbox' => 'int',
		'date'     => '?date',
		'datetime' => '?datetime',
		'file'     => '?string',
		'password' => '?string',
		'number'   => '?string',
		'tel'      => '?string',
		'select'   => '?string',
		'multiple' => 'int',
		'country'  => '?string',
		'text'     => '?string',
		'textarea' => '?string',
	];

	const SQL_TYPES = [
		'email'    => 'TEXT',
		'url'      => 'TEXT',
		'checkbox' => 'INTEGER NOT NULL DEFAULT 0',
		'date'     => 'TEXT',
		'datetime' => 'TEXT',
		'file'     => 'TEXT',
		'password' => 'TEXT',
		'number'   => 'TEXT',
		'tel'      => 'TEXT',
		'select'   => 'TEXT',
		'multiple' => 'INTEGER NOT NULL DEFAULT 0',
		'country'  => 'TEXT',
		'text'     => 'TEXT',
		'textarea' => 'TEXT',
	];

	const SQL_CONSTRAINTS = [
		'checkbox' => '%1s = 1 OR %1s = 0',
		'date'     => '%1s IS NULL OR (date(%1$s) IS NOT NULL AND date(%1s) = %1$s)',
		'datetime' => '%1s IS NULL OR (date(%1$s) IS NOT NULL AND date(%1s) = %1$s)',
	];

	const SYSTEM_FIELDS = [
		'id'           => 'int',
		'id_category'  => 'int',
		'pgp_key'      => '?string',
		'otp_secret'   => '?string',
		'date_login'   => '?DateTime',
		'date_created' => '?DateTime',
	];

	public function delete(): bool
	{
		if ($this->system) {
			throw new ValidationException('Ce champ est utilisé en interne, il n\'est pas possible de le supprimer');
		}

		parent::delete();
	}

	public function selfCheck(): void
	{
		$this->name = strtolower($this->name);

		$this->assert(!array_key_exists($this->name, self::SYSTEM_FIELDS), 'Ce nom de champ est déjà utilisé par un champ système, merci d\'en choisir un autre.');
		$this->assert(preg_match('!^[a-z][a-z0-9]*(_[a-z0-9]+)*$!', $this->name), 'Le nom du champ est invalide : ne sont acceptés que les lettres minuscules et les chiffres (éventuellement séparés par un underscore).');

		$this->assert(trim($this->label) != '', 'Le libellé est obligatoire.');
		$this->assert($this->type && array_key_exists($this->type, self::TYPES), 'Type de champ invalide.');
		$this->assert($this->system != 'password' || $this->type == 'password', 'Le champ mot de passe ne peut être d\'un type différent de mot de passe.');

		$this->assert(!($this->type == 'multiple' || $this->type == 'select') || !empty($this->options), 'Le champ nécessite de comporter au moins une option possible.');

		$db = DB::getInstance();

		$this->asserts($this->exists() || $this->system == 'preset' || !array_key_exists($this->name, $this->getPresets()), 'Ce nom de champ est déjà utilisé par un champ pré-défini.');
		$this->asserts(!$this->exists() && !$db->test(self::TABLE, 'name = ?', $this->name), 'Ce nom de champ est déjà utilisé par un autre champ.');
		$this->asserts($this->exists() && !$db->test(self::TABLE, 'name = ? AND id != ?', $this->name, $this->id()), 'Ce nom de champ est déjà utilisé par un autre champ.');
	}
}

<?php

namespace Garradin\Entities\Users;

use KD2\DB\EntityManager;

use Garradin\Entity;
use Garradin\DB;
use Garradin\Config;
use Garradin\Utils;
use Garradin\UserException;
use Garradin\ValidationException;

use Garradin\Users\DynamicFields;

use KD2\SMTP;

class User extends Entity
{
	const TABLE = 'users';

	protected function __construct()
	{
		$fields = DynamicFields::all();

		foreach ($fields as $key => $config) {
			$this->$key = null;
			$this->_types[$key] = $config->type;
		}

		parent::__construct();
	}

	public function selfCheck(): void
	{
		$df = DynamicFields::getInstance();

		// Check email addresses
		foreach (DynamicFields::getEmailFields() as $field) {
			$this->assert($this->$field === null || SMTP::checkEmailIsValid($this->$field, false), 'Cette adresse email n\'est pas valide.');
		}

		// check user number
		$field = DynamicFields::getNumberField();
		$this->assert($this->$field !== null && ctype_alnum($this->$field), 'Numéro de membre invalide : ne peut contenir que des chiffres et des lettres.');

		$db = DB::getInstance();
		$this->assert(!$this->exists() && !$db->test(self::TABLE, sprintf('%s = ?', $db->quoteIdentifier($field)), $this->$field), 'Ce numéro de membre est déjà utilisé par un autre membre.');
		$this->assert($this->exists() && !$db->test(self::TABLE, sprintf('%s = ? AND id != ?', $db->quoteIdentifier($field)), $this->$field, $this->id()), 'Ce numéro de membre est déjà utilisé par un autre membre.');

		$field = DynamicFields::getLoginField();
		if ($this->$field !== null) {
			$this->assert(!$this->exists() && !$db->test(self::TABLE, sprintf('%s = ? COLLATE NOCASE ', $db->quoteIdentifier($field)), $this->$field), sprintf('Le champ %s utilisé comme identifiant est déjà utilisé par un autre membre. Il doit être unique pour chaque membre.', $field_name));
			$this->assert($this->exists() && !$db->test(self::TABLE, sprintf('%s = ? COLLATE NOCASE AND id != ?', $db->quoteIdentifier($field)), $this->$field, $this->id()), sprintf('Le champ %s utilisé comme identifiant est déjà utilisé par un autre membre. Il doit être unique pour chaque membre.', $field_name));
		}
	}

	public function delete(): bool
	{
		$session = Session::getInstance();

		if ($session->isLogged()) {
			$user = $session->getUser();

			if ($user->id == $this->id) {
				throw new UserException('Il n\'est pas possible de supprimer son propre compte.');
			}
		}

		return parent::delete();
	}
}

	const FORMAT_ENCRYPTED = 'skriv/encrypted';
	const FORMAT_MARKDOWN = 'markdown';

	const FORMATS_LIST = [
		self::FORMAT_SKRIV => 'SkrivML',
		self::FORMAT_MARKDOWN => 'MarkDown',
		self::FORMAT_ENCRYPTED => 'Chiffré',

	];

	const STATUS_ONLINE = 'online';
	const STATUS_DRAFT = 'draft';

	const STATUS_LIST = [
		self::STATUS_ONLINE => 'En ligne',

	const FORMAT_ENCRYPTED = 'skriv/encrypted';
	const FORMAT_MARKDOWN = 'markdown';

	const FORMATS_LIST = [
		self::FORMAT_SKRIV => 'SkrivML',
		self::FORMAT_MARKDOWN => 'MarkDown',
		self::FORMAT_ENCRYPTED => 'Chiffré',
		self::FORMAT_MARKDOWN => 'Markdown',
	];

	const STATUS_ONLINE = 'online';
	const STATUS_DRAFT = 'draft';

	const STATUS_LIST = [
		self::STATUS_ONLINE => 'En ligne',

		$columns = self::LIST_COLUMNS;

		$tables = sprintf('%s f
			INNER JOIN acc_transactions t ON t.id = f.name
			INNER JOIN acc_years y ON t.id_year = y.id', Files::getVirtualTableName());

		$sum = 0;

		// Only fetch directories with an ID as the name
		$conditions = sprintf('f.parent = \'%s\' AND f.type = %d AND printf("%%d", f.name) = name', File::CONTEXT_TRANSACTION, File::TYPE_DIRECTORY);

		$list = new DynamicList($columns, $tables, $conditions);
		$list->orderBy('year', true);
		$list->setCount('COUNT(DISTINCT t.id)');
		$list->setModifier(function (&$row) {
			$row->date = \DateTime::createFromFormat('!Y-m-d', $row->date);
		});

		return $list;
	}
}

		$columns = self::LIST_COLUMNS;

		$tables = sprintf('%s f
			INNER JOIN acc_transactions t ON t.id = f.name
			INNER JOIN acc_years y ON t.id_year = y.id', Files::getVirtualTableName());



		// Only fetch directories with an ID as the name
		$conditions = sprintf('f.parent = \'%s\' AND f.type = %d AND printf("%%d", f.name) = name', File::CONTEXT_TRANSACTION, File::TYPE_DIRECTORY);

		$list = new DynamicList($columns, $tables, $conditions);
		$list->orderBy('year', true);
		$list->setCount('COUNT(DISTINCT t.id)');
		$list->setModifier(function (&$row) {
			$row->date = \DateTime::createFromFormat('!Y-m-d', $row->date);
		});

		return $list;
	}
}

<?php

namespace Garradin\Files;

use Garradin\Entities\Files\File;

use Garradin\DynamicList;
use Garradin\Config;

class Users
{
	const LIST_COLUMNS = [
		'number' => [
................................................................................

		$list = new DynamicList($columns, $tables, $conditions);
		$list->orderBy('number', false);
		$list->setCount('COUNT(DISTINCT m.id)');

		return $list;
	}
}

<?php

namespace Garradin\Files;

use Garradin\Entities\Files\File;
use Garradin\Entities\Users\User;
use Garradin\DynamicList;
use Garradin\Config;

class Users
{
	const LIST_COLUMNS = [
		'number' => [
................................................................................

		$list = new DynamicList($columns, $tables, $conditions);
		$list->orderBy('number', false);
		$list->setCount('COUNT(DISTINCT m.id)');

		return $list;
	}

	static public function deleteMultiple(array $ids): void
	{
		$session = Session::getInstance();

		if ($session->isLogged()) {
			$user = $session->getUser();

			foreach ($ids as $id) {
				if ($user->id == $id) {
					throw new UserException('Il n\'est pas possible de supprimer son propre compte.');
				}
			}
		}

		foreach ($ids as &$id)
		{
			$id = (int) $id;
			Files::delete(File::CONTEXT_USER . '/' . $id);
		}

		$db = DB::getInstance();

		// Suppression du membre
		$db->delete(User::TABLE, $db->where('id', $membres));
	}

    static public function changeCategory(int $category_id, array $ids)
    {
		$session = Session::getInstance();
		$user_id = null;

		if ($session->isLogged()) {
			$user_id = $session->getUser()->id;
		}

        foreach ($ids as &$id) {
            $id = (int) $id;

            // Don't allow current user ID to change his/her category
            // as that means he/she could be logged out
            if ($id == $user_id) {
            	$id = null;
            }
        }

        unset($id);

        // Remove logged-in user ID
        $ids = array_filter($ids);

        $db = DB::getInstance();
        return $db->update(User::TABLE,
            ['id_category' => $category_id],
            $db->where('id', $ids)
        );
    }
}

		// c'est dans Config::set que sont vérifiées les données utilisateur (renvoie UserException)
		$config = Config::getInstance();
		$config->set('nom_asso', $name);
		$config->set('email_asso', $user_email);
		$config->set('monnaie', '€');
		$config->set('pays', 'FR');
		$config->set('site_disabled', true);



		$champs = Membres\Champs::importInstall();
		$champs->create(); // Pas de copie car pas de table membres existante
		$config->set('champs_membres', $champs);

		$config->set('champ_identifiant', 'email');
		$config->set('champ_identite', 'nom');

		// c'est dans Config::set que sont vérifiées les données utilisateur (renvoie UserException)
		$config = Config::getInstance();
		$config->set('nom_asso', $name);
		$config->set('email_asso', $user_email);
		$config->set('monnaie', '€');
		$config->set('pays', 'FR');
		$config->set('site_disabled', true);
		$config->set('log_retention', 720);
		$config->set('log_anonymize', 365);

		$champs = Membres\Champs::importInstall();
		$champs->create(); // Pas de copie car pas de table membres existante
		$config->set('champs_membres', $champs);

		$config->set('champ_identifiant', 'email');
		$config->set('champ_identite', 'nom');

<?php

namespace Garradin;

use Garradin\Config;
use Garradin\DB;

class Log
{
	/**
	 * How many seconds in the past should we look for failed attempts?
	 * @type int
	 */
	const LOCKOUT_DELAY = 5*60;

	/**
	 * Number of maximum login attempts per minute
	 * 1 = max. 1 attempt per minute
	 * 1.2 = max 6 attempts in 5 minutes
	 * @type float
	 */
	const LOCKOUT_RATE = 1.2;

	const LOGIN_FAIL = 1;
	const LOGIN_SUCCESS = 2;
	const LOGIN_REMIND = 3;
	const LOGIN_CHANGE = 4;

	const DELETE = 10;
	const CREATE = 11;
	const EDIT = 12;

	const ACTIONS = [
		self::LOGIN_FAIL => 'Connexion refusée',
		self::LOGIN_SUCCESS => 'Connexion réussie',
		self::LOGIN_REMIND => 'Rappel de mot de passe',
		self::LOGIN_CHANGE => 'Modification de mot de passe',
		self::DELETE => 'Suppression',
		self::CREATE => 'Création',
		self::EDIT => 'Modification',
	];

	static public function add(int $type, ?string $details = null): void
	{
		if ($type != LOGIN_FAIL) {
			$keep = Config::getInstance()->log_retention;

			// Don't log anything
			if ($keep == 0) {
				return;
			}
		}

		$ip = Utils::getIP();
		$session = Session::getInstance();
		$id_user = $session->isLogged() ? $session->getUser()->id : null;

		DB::getInstance()->insert('log', [
			'id_user'    => $user_id,
			'type'       => $type,
			'details'    => $details,
			'ip_address' => $ip,
		]);
	}

	static public function clean(): void
	{
		$config = Config::getInstance();
		$db = DB::getInstance();

		$days_delete = $config->log_retention;
		$days_anonymous = $config->log_anonymize;

		// Anonymize old logs according to configuration
		$db->exec(sprintf('UPDATE logs SET ip_address = NULL, id_user = NULL
			WHERE type != %d AND type != %d AND created < datetime(\'now\', \'-%d days\');',
			self::LOGIN_FAIL, self::LOGIN_REMIND, $days_anonymous));

		// Delete old logs according to configuration
		$db->exec(sprintf('DELETE FROM logs
			WHERE type != %d AND type != %d AND created < datetime(\'now\', \'-%d days\');',
			self::LOGIN_FAIL, self::LOGIN_REMIND, $days_delete));

		// Delete failed login attempts and reminders after 30 days
		$db->exec(sprintf('DELETE FROM logs WHERE type = %d OR type = %d AND created < datetime(\'now\', \'-%d days\');',
			self::LOGIN_FAIL, self::LOGIN_REMIND, 30));
	}

	/**
	 * Returns TRUE if the current IP address has done too many failed login attempts
	 * @return boolean
	 */
	static public function isLocked(): bool
	{
		$ip = Utils::getIP();

		// is IP locked out?
		$sql = sprintf('SELECT COUNT(*) FROM logs WHERE type = ? AND ip_address = ? AND created > datetime(\'now\', \'-%d seconds\');', self::LOCKOUT_DELAY);
		$count = $db->firstColumn($sql, self::LOGIN_FAIL, $ip);

		if (($count / self::LOCKOUT_DELAY) > self::LOCKOUT_RATE) {
			return true;
		}

		return false;
	}

	static public function list(): DynamicList
	{
		$config = Config::getInstance();

		$columns = [
			'id_user' => [
			],
			'identity' => [
				'label' => 'Membre',
				'select' => 'users.' . $config->champ_identite,
			],
			'created' => [
				'label' => 'Date'
			],
			'type' => [
				'label' => 'Action',
			],
			'ip_address' => [
				'label' => 'Adresse IP',
			],
			'details' => [
				'label' => 'Détails',
			],
		];

		$tables = 'logs LEFT JOIN users ON users.id = logs.id_user';

		$list = new DynamicList($columns, $tables);
		$list->orderBy('created', true);
		$list->setCount('COUNT(logs.id)');
		$list->setModifier(function (&$row) {
			$row->created = \DateTime::createFromFormat('!Y-m-d H:i:s', $row->created);
		});

		return $list;
	}
}

        $config = Config::getInstance();

        unset($data['id']);

        $this->_checkFields($data, $check_editable, false);
        $champ_id = $config->get('champ_identifiant');

        if (!empty($data[$champ_id])
            && $db->firstColumn('SELECT 1 FROM membres WHERE '.$champ_id.' = ? COLLATE NOCASE AND id != ? LIMIT 1;', $data[$champ_id], (int)$id))
        {
            throw new UserException('La valeur du champ '.$champ_id.' est déjà utilisée par un autre membre, or ce champ doit être unique à chaque membre.');
        }

        if (isset($data['numero']))
        {
            if (!preg_match('/^\d+$/', $data['numero']))
            {
                throw new UserException('Le numéro de membre ne doit contenir que des chiffres.');
            }
            elseif ($data['numero'] == 0)
            {
                throw new UserException('Le numéro de membre ne peut être vide.');
            }

            if ($db->test('membres', 'numero = ? AND id != ?', (int)$data['numero'], $id))
            {
                throw new UserException('Ce numéro est déjà attribué à un autre membre.');
            }
        }

        if (isset($data['delete_password'])) {
            $data['passe'] = null;
            unset($data['delete_password']);
        }
        elseif (!empty($data['passe']) && trim($data['passe']))
        {
            Session::checkPasswordValidity($data['passe']);
................................................................................
        return $db->first('SELECT *,
            '.$config->get('champ_identite').' AS identite,
            strftime(\'%s\', date_inscription) AS date_inscription,
            strftime(\'%s\', date_connexion) AS date_connexion
            FROM membres WHERE id = ? LIMIT 1;', (int)$id);
    }

    public function delete($ids)
    {
        if (!is_array($ids))
        {
            $ids = [(int)$ids];
        }

        $session = Session::getInstance();

        if ($session->isLogged())
        {
            $user = $session->getUser();

            foreach ($ids as $id)
            {
                if ($user->id == $id)
                {
                    throw new UserException('Il n\'est pas possible de supprimer son propre compte.');
                }
            }
        }

        return $this->_deleteMembres($ids);
    }

    public function getNom($id)
    {
        $db = DB::getInstance();
        $config = Config::getInstance();

        return $db->firstColumn('SELECT '.$config->get('champ_identite').' FROM membres WHERE id = ? LIMIT 1;', (int)$id);
    }
................................................................................
    }

    public function countAllButHidden()
    {
        $db = DB::getInstance();
        return $db->firstColumn('SELECT COUNT(*) FROM membres WHERE id_category NOT IN (SELECT id FROM users_categories WHERE hidden = 1);');
    }

    public function getAttachementsDirectory(int $id)
    {
        return File::CONTEXT_USER . '/' . $id;
    }

    static public function changeCategorie($id_cat, $membres)
    {
        foreach ($membres as &$id)
        {
            $id = (int) $id;
        }

        $db = DB::getInstance();
        return $db->update('membres',
            ['id_category' => (int)$id_cat],
            sprintf('id IN (%s)', implode(',', $membres))
        );
    }

    protected function _deleteMembres(array $membres)
    {
        foreach ($membres as &$id)
        {
            $id = (int) $id;

            Files::delete($this->getAttachementsDirectory($id));
        }

        Plugin::fireSignal('membre.suppression', $membres);

        $db = DB::getInstance();

        // Suppression du membre
        return $db->delete('membres', $db->where('id', $membres));
    }
}

        $config = Config::getInstance();

        unset($data['id']);

        $this->_checkFields($data, $check_editable, false);
        $champ_id = $config->get('champ_identifiant');
























        if (isset($data['delete_password'])) {
            $data['passe'] = null;
            unset($data['delete_password']);
        }
        elseif (!empty($data['passe']) && trim($data['passe']))
        {
            Session::checkPasswordValidity($data['passe']);
................................................................................
        return $db->first('SELECT *,
            '.$config->get('champ_identite').' AS identite,
            strftime(\'%s\', date_inscription) AS date_inscription,
            strftime(\'%s\', date_connexion) AS date_connexion
            FROM membres WHERE id = ? LIMIT 1;', (int)$id);
    }


























    public function getNom($id)
    {
        $db = DB::getInstance();
        $config = Config::getInstance();

        return $db->firstColumn('SELECT '.$config->get('champ_identite').' FROM membres WHERE id = ? LIMIT 1;', (int)$id);
    }
................................................................................
    }

    public function countAllButHidden()
    {
        $db = DB::getInstance();
        return $db->firstColumn('SELECT COUNT(*) FROM membres WHERE id_category NOT IN (SELECT id FROM users_categories WHERE hidden = 1);');
    }
}

<?php

namespace Garradin\Membres;

use Garradin\Config;
use Garradin\DB;
use Garradin\Utils;
use Garradin\UserException;

class Champs
{
    const TABLE = 'membres';

	protected $champs = null;

    protected $system_fields = [
        'date_connexion',
        'date_inscription',
        'clef_pgp',
        'secret_otp',
        'id',
        'id_category',
    ];

	protected $types = [
		'email'		=>	'Adresse E-Mail',
		'url'		=>	'Adresse URL',
		'checkbox'	=>	'Case à cocher',
		'date'		=>	'Date',
		'datetime'	=>	'Date et heure',
        'file'      =>  'Fichier',
        'password'  =>  'Mot de passe',
		'number'	=>	'Numéro',
		'tel'		=>	'Numéro de téléphone',
		'select'	=>	'Sélecteur à choix unique',
        'multiple'  =>  'Sélecteur à choix multiple',
		'country'	=>	'Sélecteur de pays',
		'text'		=>	'Texte',
		'textarea'	=>	'Texte multi-lignes',
	];

    protected $text_types = [
        'email',
        'text',
        'select',
        'textarea',
        'url',
        'password',
        'country'
    ];

    protected $config_fields = [
        'type',
        'title',
        'help',
        'editable',
        'list_row',
        'mandatory',
        'private',
        'options'
    ];

    static protected $presets = null;

	public function __toString()
	{
		return Utils::write_ini_string($this->champs);
	}

    public function toString()
    {
        return Utils::write_ini_string($this->champs);
    }

	static public function importInstall()
	{
		$champs = parse_ini_file(\Garradin\ROOT . '/include/data/champs_membres.ini', true);
        $champs = array_filter($champs, function ($row) { return !empty($row['install']); });
        return new \Garradin\Membres\Champs($champs, true);
	}

    static public function importPresets()
    {
        if (is_null(self::$presets))
        {
            self::$presets = parse_ini_file(\Garradin\ROOT . '/include/data/champs_membres.ini', true);
        }

        return self::$presets;
    }

    static public function listUnusedPresets(Champs $champs)
    {
        return array_diff_key(self::importPresets(), (array) $champs->getAll());
    }

	public function __construct($champs, $initial_setup = false)
	{
		if ($champs instanceOf Champs)
		{
			$this->champs = $champs->getAll();
		}
        elseif (is_array($champs))
        {
            $this->setAll($champs, $initial_setup);
        }
		else
		{
			$champs = parse_ini_string((string)$champs, true);

            foreach ($champs as $key=>&$config)
            {
                $config = (object) $config;
                $this->_checkField($key, $config);
            }

            $this->champs = (object) $champs;
		}
	}

	public function getTypes()
	{
		return $this->types;
	}

	public function get($champ, $key = null)
	{
        if (!property_exists($this->champs, $champ))
            return null;

        if ($key !== null)
        {
            if (property_exists($this->champs->$champ, $key))
                return $this->champs->$champ->$key;
            else
                return null;
        }

		return $this->champs->$champ;
	}

    public function isText($champ)
    {
        if (!property_exists($this->champs, $champ))
            return null;

        if (in_array($this->champs->$champ->type, $this->text_types))
            return true;
        else
            return false;
    }

    public function getKeys($all = false)
    {
        $keys = [];

        foreach ($this->champs as $key => $config)
        {
            if (!$all && $key == 'passe')
            {
                continue;
            }

            $keys[] = $key;
        }

        return $keys;
    }

	public function getAll()
	{
		return $this->champs;
	}

    public function getList()
    {
        $champs = clone $this->champs;
        unset($champs->passe);

        return $champs;
    }

    public function listAssocNames()
    {
        $out = [];

        foreach ($this->champs as $key => $config) {
            if ($key == 'passe') {
                continue;
            }

            $out[$key] = $config->title;
        }

        return $out;
    }

    public function getMultiples()
    {
        $out = [];

        foreach ($this->champs as $id => $champ) {
            if ($champ->type == 'multiple') {
                $out[$id] = $champ;
            }
        }

        return $out;
    }

    public function getListedFields()
    {
        $champs = (array) $this->champs;

        $champs = array_filter($champs, function ($a) {
            return empty($a->list_row) ? false : true;
        });

        uasort($champs, function ($a, $b) {
            if ($a->list_row == $b->list_row)
                return 0;

            return ($a->list_row > $b->list_row) ? 1 : -1;
        });

        return (object) $champs;
    }

    public function getFirstListed()
    {
        foreach ($this->champs as $key=>$config)
        {
            if (empty($config->list_row))
            {
                continue;
            }

            return $key;
        }
    }

    /**
     * Vérifie la cohérence et la présence des bons éléments pour un champ
     * @param  string $name     Nom du champ
     * @param  array $config    Configuration du champ
     * @return boolean true
     */
    protected function _checkField($name, \stdClass &$config)
    {
        if (!preg_match('!^\w+(_\w+)*$!', $name))
        {
            throw new UserException('Le nom du champ est invalide.');
        }

        foreach ($config as $key=>&$value)
        {
            // Champ install non pris en compte
            if ($key == 'install')
            {
                unset($config->$key);
                continue;
            }

            if (!in_array($key, $this->config_fields))
            {
                throw new \BadMethodCallException('Champ '.$key.' non valide.');
            }

            if ($key == 'editable' || $key == 'private' || $key == 'mandatory')
            {
                $value = (bool) (int) $value;
            }
            elseif ($key == 'list_row')
            {
                $value = (int) $value;
            }
            elseif ($key == 'help' || $key == 'title')
            {
                $value = trim((string) $value);
            }
            elseif ($key == 'options')
            {
                $value = (array) $value;

                foreach ($value as $option_key=>$option_value)
                {
                    if (trim($option_value) == '')
                    {
                        unset($value[$option_key]);
                    }
                }
            }
        }

        if (empty($config->title) && $name != 'passe')
        {
            throw new UserException('Champ "'.$name.'" : Le titre est obligatoire.');
        }

        if (empty($config->type) || !array_key_exists($config->type, $this->types))
        {
            throw new UserException('Champ "'.$name.'" : Le type est vide ou non valide.');
        }

        if ($name == 'email' && $config->type != 'email')
        {
            throw new UserException('Le champ email ne peut être d\'un type différent de email.');
        }

        if ($name == 'passe' && $config->type != 'password')
        {
            throw new UserException('Le champ mot de passe ne peut être d\'un type différent de mot de passe.');
        }

        if (($config->type == 'multiple' || $config->type == 'select') && empty($config->options))
        {
            throw new UserException('Le champ "'.$name.'" nécessite de comporter au moins une option possible.');
        }

        if (!property_exists($config, 'editable'))
        {
            $config->editable = false;
        }

        if (!property_exists($config, 'mandatory'))
        {
            $config->mandatory = false;
        }

        if (!property_exists($config, 'private'))
        {
            $config->private = false;
        }

        return true;
    }

    /**
     * Ajouter un nouveau champ
     * @param string $name Nom du champ
     * @param array $config Configuration du champ
     * @return boolean true
     */
    public function add($name, $config)
    {
        if (!preg_match('!^[a-z]!', $name))
        {
            throw new UserException('Le nom du champ est invalide : le premier caractère doit être une lettre.');
        }
        
        if (!preg_match('!^[a-z][a-z0-9]*(_[a-z0-9]+)*$!', $name))
        {
            throw new UserException('Le nom du champ est invalide : ne sont acceptés que les lettres minuscules et les chiffres (éventuellement séparés par un underscore).');
        }

        $config = (object) $config;
        
        $this->_checkField($name, $config);

        $this->champs->$name = $config;

        return true;
    }

    /**
     * Modifie un champ particulier
     * @param string $champ Nom du champ
     * @param string $key   Nom de la clé à modifier
     * @param mixed  $value Valeur à affecter
     * @return boolean true
     */
	public function set($champ, $key, $value)
	{
        if (!isset($this->champs->$champ))
        {
            throw new \LogicException('Champ "'.$champ.'" inconnu.');
        }

        // Vérification
        $config = clone $this->champs->$champ;
        $config->$key = $value;
        $this->_checkField($champ, $config);

		$this->champs->$champ = $config;
		return true;
	}

    public function checkCustomFieldName($name)
    {
        if (in_array($name, $this->system_fields))
        {
            throw new UserException('Ce nom unique de champ existe déjà dans les champs systèmes utilisés par Garradin.');
        }

        $presets = self::importPresets();

        if (array_key_exists($name, $presets))
        {
            throw new UserException('Le champ personnalisé ne peut avoir le même nom qu\'un champ pré-défini.');
        }

        if (isset($this->champs->$name))
        {
            throw new UserException('Ce nom est déjà utilisé par un autre champ.');
        }
    }

    /**
     * Modifie les champs en interne en vérifiant que tout va bien
     * @param array $champs Liste des champs
     * @return boolean true
     */
    public function setAll($champs, $initial_setup = false)
    {
        $presets = self::importPresets();
        $champs = (object) $champs;

        if (!isset($champs->passe))
        {
            $champs->passe = (object) ['type' => 'password'];
        }

        $config = null;

        foreach ($champs as $key=>&$config)
        {
            if (in_array($key, $this->system_fields))
            {
                throw new UserException('Ce nom unique de champ existe déjà dans les champs systèmes utilisés par Garradin.');
            }

            if (is_array($config))
            {
                $config = (object) $config;
            }

            if (isset($presets[$key]))
            {
                $config->type = $presets[$key]['type'];
            }

            $this->_checkField($key, $config);
        }

        unset($config);

        if ($initial_setup)
        {
            $this->champs = $champs;
            return true;
        }

        if (!property_exists($champs, 'email'))
        {
            throw new UserException('Le champ E-Mail ne peut être supprimé des fiches membres.');
        }

        if (!property_exists($champs, 'passe'))
        {
            throw new UserException('Le champ Mot de passe ne peut être supprimé des fiches membres.');
        }

        if (!property_exists($champs, 'numero'))
        {
            throw new UserException('Le champ numéro de membre ne peut être supprimé des fiches membres.');
        }

        $config = Config::getInstance();

        $identite = $config->get('champ_identite');

        if ($identite != 'id' && !property_exists($champs, $identite))
        {
            throw new UserException('Le champ '.$config->get('champ_identite')
                .' est défini comme identité des membres et ne peut donc être supprimé des fiches membres.');
        }

        $identifiant = $config->get('champ_identifiant');

        if ($identifiant != 'id' && !property_exists($champs, $identifiant))
        {
            throw new UserException('Le champ '.$config->get('champ_identifiant')
                .' est défini comme identifiant à la connexion et ne peut donc être supprimé des fiches membres.');
        }

        $this->champs = $champs;

        return true;
    }

    public function getSQLSchema(string $table_name = self::TABLE): string
    {
        $config = Config::getInstance();
        $db = DB::getInstance();

        // Champs à créer
        $create = [
            'id INTEGER PRIMARY KEY, -- Numéro attribué automatiquement',
            'id_category INTEGER NOT NULL REFERENCES users_categories(id),',
            'date_connexion TEXT NULL CHECK (date_connexion IS NULL OR datetime(date_connexion) = date_connexion), -- Date de dernière connexion',
            'date_inscription TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date_inscription) IS NOT NULL AND date(date_inscription) = date_inscription), -- Date d\'inscription',
            'secret_otp TEXT NULL, -- Code secret pour TOTP',
            'clef_pgp TEXT NULL, -- Clé publique PGP'
        ];

        end($this->champs);
        $last_one = key($this->champs);

        foreach ($this->champs as $key=>$cfg)
        {
            if ($cfg->type == 'number' || $cfg->type == 'multiple' || $cfg->type == 'checkbox')
                $type = 'INTEGER';
            elseif ($cfg->type == 'file')
                $type = 'BLOB';
            else
                $type = 'TEXT COLLATE NOCASE';

            $line = sprintf('%s %s', $db->quoteIdentifier($key), $type);

            if ($last_one != $key) {
                $line .= ',';
            }

            if (!empty($cfg->title))
            {
                $line .= ' -- ' . str_replace(["\n", "\r"], '', $cfg->title);
            }

            $create[] = $line;
        }

        $sql = sprintf("CREATE TABLE %s\n(\n\t%s\n);", $table_name, implode("\n\t", $create));
        return $sql;
    }

    public function getCopyFields(): array
    {
        $config = Config::getInstance();

        // Champs à recopier
        $copy = [
            'id'               => 'id',
            'id_category'      => 'id_category',
            'date_connexion'   => 'date_connexion',
            'date_inscription' => 'date_inscription',
            'secret_otp'       => 'secret_otp',
            'clef_pgp'         => 'clef_pgp',
        ];

        $anciens_champs = $config->get('champs_membres');
        $anciens_champs = is_null($anciens_champs) ? $this->champs : $anciens_champs->getAll();

        foreach ($this->champs as $key=>$cfg)
        {
            if (property_exists($anciens_champs, $key)) {
                $copy[$key] = $key;
            }
        }

        return $copy;
    }

    public function getSQLCopy(string $old_table_name, string $new_table_name = self::TABLE, array $fields = null): string
    {
        if (null === $fields) {
            $fields = $this->getCopyFields();
        }

        $db = DB::getInstance();

        return sprintf('INSERT INTO %s (%s) SELECT %s FROM %s;',
            $new_table_name,
            implode(', ', array_map([$db, 'quoteIdentifier'], $fields)),
            implode(', ', array_map([$db, 'quoteIdentifier'], array_keys($fields))),
            $old_table_name
        );
    }

    public function copy(string $old_table_name, string $new_table_name = self::TABLE, array $fields = null): void
    {
        DB::getInstance()->exec($this->getSQLCopy($old_table_name, $new_table_name, $fields));
    }

    public function create(string $table_name = self::TABLE)
    {
        $db = DB::getInstance();
        $db->begin();
        $this->createTable($table_name);
        $this->createIndexes($table_name);
        $db->commit();
    }

    public function createTable(string $table_name = self::TABLE): void
    {
        DB::getInstance()->exec($this->getSQLSchema($table_name));
    }

    public function createIndexes(string $table_name = self::TABLE): void
    {
        $db = DB::getInstance();
        $config = Config::getInstance();

        if ($id_field = $config->get('champ_identifiant')) {
            // Mettre les champs identifiant vides à NULL pour pouvoir créer un index unique
            $db->exec(sprintf('UPDATE %s SET %s = NULL WHERE %2$s = \'\';',
                $table_name, $id_field));

            $collation = '';

            if ($this->isText($id_field)) {
                $collation = ' COLLATE NOCASE';
            }

            // Création de l'index unique
            $db->exec(sprintf('CREATE UNIQUE INDEX IF NOT EXISTS users_id_field ON %s (%s%s);', $table_name, $id_field, $collation));
        }

        $db->exec(sprintf('CREATE UNIQUE INDEX IF NOT EXISTS user_number ON %s (numero);', $table_name));
        $db->exec(sprintf('CREATE INDEX IF NOT EXISTS users_category ON %s (id_category);', $table_name));

        // Create index on listed columns
        // FIXME: these indexes are currently unused by SQLite in the default user list
        // when there is more than one non-hidden category, as this makes SQLite merge multiple results
        // and so the index is not useful in that case sadly.
        // EXPLAIN QUERY PLAN SELECT * FROM membres WHERE "id_category" IN (3) ORDER BY "nom" ASC LIMIT 0,100;
        // --> SEARCH TABLE membres USING INDEX users_list_nom (id_category=?)
        // EXPLAIN QUERY PLAN SELECT * FROM membres WHERE "id_category" IN (3, 7) ORDER BY "nom" ASC LIMIT 0,100;
        // --> SEARCH TABLE membres USING INDEX user_category (id_category=?)
        // USE TEMP B-TREE FOR ORDER BY
        $listed_fields = array_keys((array) $this->getListedFields());
        foreach ($listed_fields as $field) {
            if ($field === $config->get('champ_identifiant')) {
                // Il y a déjà un index
                continue;
            }

            $collation = '';

            if ($this->isText($field)) {
                $collation = ' COLLATE NOCASE';
            }

            $db->exec(sprintf('CREATE INDEX IF NOT EXISTS users_list_%s ON %s (id_category, %1$s%s);', $field, $table_name, $collation));
        }
    }

    /**
     * Enregistre les changements de champs en base de données
     * @return boolean true
     */
    public function save()
    {
        $db = DB::getInstance();
        $config = Config::getInstance();

    	$db->exec('PRAGMA foreign_keys = OFF;');

        $db->begin();
        $this->createTable(self::TABLE . '_tmp');
        $this->copy(self::TABLE, self::TABLE . '_tmp');
        $db->exec(sprintf('DROP TABLE IF EXISTS %s;', self::TABLE));
    	$db->exec(sprintf('ALTER TABLE %s_tmp RENAME TO %1$s;', self::TABLE));

        $this->createIndexes(self::TABLE);

    	$db->commit();
    	$db->exec('PRAGMA foreign_keys = ON;');

    	$config->set('champs_membres', $this);
    	$config->save();

    	return true;
    }
}

	}

	protected function getUserForLogin($login)
	{
		$champ_id = Config::getInstance()->get('champ_identifiant');

		// Ne renvoie un membre que si celui-ci a le droit de se connecter
		$query = 'SELECT m.id, m.%1$s AS login, m.passe AS password, m.secret_otp AS otp_secret
			FROM membres AS m
			INNER JOIN users_categories AS c ON c.id = m.id_category
			WHERE m.%1$s = ? COLLATE NOCASE AND c.perm_connect >= %2$d
			LIMIT 1;';

		$query = sprintf($query, $champ_id, self::ACCESS_READ);

		return $this->db->first($query, $login);

	}

	protected function getUserForLogin($login)
	{
		$champ_id = Config::getInstance()->get('champ_identifiant');

		// Ne renvoie un membre que si celui-ci a le droit de se connecter
		$query = 'SELECT u.id, m.%1$s AS login, m.password, m.secret_otp AS otp_secret
			FROM users AS u
			INNER JOIN users_categories AS c ON c.id = m.id_category
			WHERE m.%1$s = ? COLLATE NOCASE AND c.perm_connect >= %2$d
			LIMIT 1;';

		$query = sprintf($query, $champ_id, self::ACCESS_READ);

		return $this->db->first($query, $login);

{
	const PLUGIN_ID_SYNTAX = '[a-z]+(?:_[a-z]+)*';

	protected $id = null;
	protected $plugin = null;
	protected $config_changed = false;







	protected $mimes = [
		'css' => 'text/css',
		'gif' => 'image/gif',
		'htm' => 'text/html',
		'html' => 'text/html',
		'ico' => 'image/x-ico',
		'jpe' => 'image/jpeg',
................................................................................
		'js' => 'application/x-javascript',
		'pdf' => 'application/pdf',
		'png' => 'image/png',
		'swf' => 'application/shockwave-flash',
		'xml' => 'text/xml',
		'svg' => 'image/svg+xml',
	];





	static public function getPath($id, $fail_with_exception = true)
	{
		if (file_exists(PLUGINS_ROOT . '/' . $id . '.tar.gz'))
		{
			return 'phar://' . PLUGINS_ROOT . '/' . $id . '.tar.gz';
		}
................................................................................

		if (!$this->plugin)
		{
			throw new UserException(sprintf('Le plugin "%s" n\'existe pas ou n\'est pas installé correctement.', $id));
		}

		$this->plugin->config = json_decode($this->plugin->config);
		
		if (!is_object($this->plugin->config))
		{
			$this->plugin->config = new \stdClass;
		}

		// Juste pour vérifier que le fichier source du plugin existe bien
		self::getPath($id);
................................................................................
		if (file_exists($this->path() . '/uninstall.php'))
		{
			$plugin = $this;
			include $this->path() . '/uninstall.php';
		}

		$db = DB::getInstance();
		$db->delete('plugins_signaux', 'plugin = ?', $this->id);
		return $db->delete('plugins', 'id = ?', $this->id);
	}

	/**
	 * Renvoie TRUE si le plugin a besoin d'être mis à jour
	 * (si la version notée dans la DB est différente de la version notée dans garradin_plugin.ini)
	 * @return boolean TRUE si le plugin doit être mis à jour, FALSE sinon
................................................................................
			$plugin = $this;
			include $this->path() . '/upgrade.php';
		}

		$infos = (object) parse_ini_file($this->path() . '/garradin_plugin.ini', false);

		$data = [
			'nom'		=>	$infos->nom,
			'description'=>	$infos->description,
			'auteur'	=>	$infos->auteur,
			'url'		=>	$infos->url,
			'version'	=>	$infos->version,
			'menu'		=>	(int)(bool)$infos->menu,
		];

		if ($infos->menu && !empty($infos->menu_condition))
		{
................................................................................
		}

		$db = DB::getInstance();

		// Signaux exclusifs, qui ne peuvent être attribués qu'à un seul plugin
		if (strpos($signal, 'boucle.') === 0)
		{
			$registered = $db->firstColumn('SELECT plugin FROM plugins_signaux WHERE signal = ? AND plugin != ?;', $signal, $this->id);

			if ($registered)
			{
				throw new \LogicException('Le signal ' . $signal . ' est exclusif et déjà associé au plugin "'.$registered.'"');
			}
		}

		$callable_name = str_replace('Garradin\\Plugin\\', '', $callable_name);

		$st = $db->prepare('INSERT OR REPLACE INTO plugins_signaux VALUES (:signal, :plugin, :callback);');
		$st->bindValue(':signal', $signal);
		$st->bindValue(':plugin', $this->id);
		$st->bindValue(':callback', $callable_name);
		return $st->execute();
	}

	/**
	 * Liste des plugins installés (en DB)
	 * @return array Liste des plugins triés par nom
	 */
	static public function listInstalled()
	{
		$db = DB::getInstance();
		$plugins = $db->getGrouped('SELECT id, * FROM plugins ORDER BY nom;');
		$system = explode(',', PLUGINS_SYSTEM);

		foreach ($plugins as &$row)
		{
			$row->system = in_array($row->id, $system);
			$row->disabled = !self::getPath($row->id, false);
		}
................................................................................
	 * @return array Tableau associatif id => nom (ou un tableau vide si aucun plugin ne doit être affiché)
	 */
	static public function listMenu(Session $session)
	{
		self::checkAndInstallSystemPlugins();

		$db = DB::getInstance();
		$list = $db->getGrouped('SELECT id, nom, menu_condition FROM plugins WHERE menu = 1 ORDER BY nom;');

		// FIXME deprecated
		$fix_legacy = [
			'{Membres::DROIT_AUCUN}' => '{ACCESS_NONE}',
			'{Membres::DROIT_ACCES}' => '{ACCESS_READ}',
			'{Membres::DROIT_ECRITURE}' => '{ACCESS_WRITE}',
			'{Membres::DROIT_ADMIN}' => '{ACCESS_ADMIN}',
................................................................................
				// Ne pas lister les plugins dont le code a disparu
				unset($list[$id]);
				continue;
			}

			if (!$row->menu_condition)
			{
				$row = $row->nom;
				continue;
			}

			$new_condition = strtr($row->menu_condition, $fix_legacy);

			// FIXME: legacy
			if ($new_condition != $row->menu_condition) {
................................................................................

			if (!$db->firstColumn($query))
			{
				unset($list[$id]);
				continue;
			}

			$row = $row->nom;
		}

		unset($row);

		return $list;
	}

................................................................................
		if (!file_exists($path . '/garradin_plugin.ini'))
		{
			throw new UserException(sprintf('Le plugin "%s" n\'est pas une extension Garradin : fichier garradin_plugin.ini manquant.', $id));
		}

		$infos = (object) parse_ini_file($path . '/garradin_plugin.ini', false);

		$required = ['nom', 'description', 'auteur', 'url', 'version', 'menu', 'config'];

		foreach ($required as $key)
		{
			if (!property_exists($infos, $key))
			{
				throw new \RuntimeException('Le fichier garradin_plugin.ini ne contient pas d\'entrée "'.$key.'".');
			}
................................................................................

			$config = json_encode($config);
		}

		$data = [
			'id' 		=> 	$id,
			'officiel' 	=> 	(int)(bool)$official,
			'nom'		=>	$infos->nom,
			'description'=>	$infos->description,
			'auteur'	=>	$infos->auteur,
			'url'		=>	$infos->url,
			'version'	=>	$infos->version,
			'menu'		=>	(int)(bool)$infos->menu,
			'config'	=>	$config,
		];

		if ($infos->menu && !empty($infos->menu_condition))
................................................................................
	 * @param  array  $params Paramètres du callback (array ou null)
	 * @return NULL 		  NULL si aucun plugin n'a été appelé,
	 * TRUE si un plugin a été appelé et a arrêté l'exécution,
	 * FALSE si des plugins ont été appelés mais aucun n'a stopé l'exécution
	 */
	static public function fireSignal($signal, $params = null, &$callback_return = null)
	{




		$list = DB::getInstance()->get('SELECT * FROM plugins_signaux WHERE signal = ?;', $signal);

		if (!count($list)) {
			return null;
		}

		if (null === $params) {
			$params = [];

{
	const PLUGIN_ID_SYNTAX = '[a-z]+(?:_[a-z]+)*';

	protected $id = null;
	protected $plugin = null;
	protected $config_changed = false;

	/**
	 * Set to false to disable signal firing
	 * @var boolean
	 */
	static protected $signals = true;

	protected $mimes = [
		'css' => 'text/css',
		'gif' => 'image/gif',
		'htm' => 'text/html',
		'html' => 'text/html',
		'ico' => 'image/x-ico',
		'jpe' => 'image/jpeg',
................................................................................
		'js' => 'application/x-javascript',
		'pdf' => 'application/pdf',
		'png' => 'image/png',
		'swf' => 'application/shockwave-flash',
		'xml' => 'text/xml',
		'svg' => 'image/svg+xml',
	];

	static public function toggleSignals(bool $enabled) {
		self::$signals = $enabled;
	}

	static public function getPath($id, $fail_with_exception = true)
	{
		if (file_exists(PLUGINS_ROOT . '/' . $id . '.tar.gz'))
		{
			return 'phar://' . PLUGINS_ROOT . '/' . $id . '.tar.gz';
		}
................................................................................

		if (!$this->plugin)
		{
			throw new UserException(sprintf('Le plugin "%s" n\'existe pas ou n\'est pas installé correctement.', $id));
		}

		$this->plugin->config = json_decode($this->plugin->config);

		if (!is_object($this->plugin->config))
		{
			$this->plugin->config = new \stdClass;
		}

		// Juste pour vérifier que le fichier source du plugin existe bien
		self::getPath($id);
................................................................................
		if (file_exists($this->path() . '/uninstall.php'))
		{
			$plugin = $this;
			include $this->path() . '/uninstall.php';
		}

		$db = DB::getInstance();
		$db->delete('plugins_signals', 'plugin = ?', $this->id);
		return $db->delete('plugins', 'id = ?', $this->id);
	}

	/**
	 * Renvoie TRUE si le plugin a besoin d'être mis à jour
	 * (si la version notée dans la DB est différente de la version notée dans garradin_plugin.ini)
	 * @return boolean TRUE si le plugin doit être mis à jour, FALSE sinon
................................................................................
			$plugin = $this;
			include $this->path() . '/upgrade.php';
		}

		$infos = (object) parse_ini_file($this->path() . '/garradin_plugin.ini', false);

		$data = [
			'name'		=>	$infos->name ?? $infos->nom,
			'description'=>	$infos->description,
			'author'	=>	$infos->author ?? $infos->auteur,
			'url'		=>	$infos->url,
			'version'	=>	$infos->version,
			'menu'		=>	(int)(bool)$infos->menu,
		];

		if ($infos->menu && !empty($infos->menu_condition))
		{
................................................................................
		}

		$db = DB::getInstance();

		// Signaux exclusifs, qui ne peuvent être attribués qu'à un seul plugin
		if (strpos($signal, 'boucle.') === 0)
		{
			$registered = $db->firstColumn('SELECT plugin FROM plugins_signals WHERE signal = ? AND plugin != ?;', $signal, $this->id);

			if ($registered)
			{
				throw new \LogicException('Le signal ' . $signal . ' est exclusif et déjà associé au plugin "'.$registered.'"');
			}
		}

		$callable_name = str_replace('Garradin\\Plugin\\', '', $callable_name);

		$st = $db->prepare('INSERT OR REPLACE INTO plugins_signals VALUES (:signal, :plugin, :callback);');
		$st->bindValue(':signal', $signal);
		$st->bindValue(':plugin', $this->id);
		$st->bindValue(':callback', $callable_name);
		return $st->execute();
	}

	/**
	 * Liste des plugins installés (en DB)
	 * @return array Liste des plugins triés par nom
	 */
	static public function listInstalled()
	{
		$db = DB::getInstance();
		$plugins = $db->getGrouped('SELECT id, * FROM plugins ORDER BY name;');
		$system = explode(',', PLUGINS_SYSTEM);

		foreach ($plugins as &$row)
		{
			$row->system = in_array($row->id, $system);
			$row->disabled = !self::getPath($row->id, false);
		}
................................................................................
	 * @return array Tableau associatif id => nom (ou un tableau vide si aucun plugin ne doit être affiché)
	 */
	static public function listMenu(Session $session)
	{
		self::checkAndInstallSystemPlugins();

		$db = DB::getInstance();
		$list = $db->getGrouped('SELECT id, name, menu_condition FROM plugins WHERE menu = 1 ORDER BY name;');

		// FIXME deprecated
		$fix_legacy = [
			'{Membres::DROIT_AUCUN}' => '{ACCESS_NONE}',
			'{Membres::DROIT_ACCES}' => '{ACCESS_READ}',
			'{Membres::DROIT_ECRITURE}' => '{ACCESS_WRITE}',
			'{Membres::DROIT_ADMIN}' => '{ACCESS_ADMIN}',
................................................................................
				// Ne pas lister les plugins dont le code a disparu
				unset($list[$id]);
				continue;
			}

			if (!$row->menu_condition)
			{
				$row = $row->name;
				continue;
			}

			$new_condition = strtr($row->menu_condition, $fix_legacy);

			// FIXME: legacy
			if ($new_condition != $row->menu_condition) {
................................................................................

			if (!$db->firstColumn($query))
			{
				unset($list[$id]);
				continue;
			}

			$row = $row->name;
		}

		unset($row);

		return $list;
	}

................................................................................
		if (!file_exists($path . '/garradin_plugin.ini'))
		{
			throw new UserException(sprintf('Le plugin "%s" n\'est pas une extension Garradin : fichier garradin_plugin.ini manquant.', $id));
		}

		$infos = (object) parse_ini_file($path . '/garradin_plugin.ini', false);

		$required = ['name', 'description', 'author', 'url', 'version', 'menu', 'config'];

		foreach ($required as $key)
		{
			if (!property_exists($infos, $key))
			{
				throw new \RuntimeException('Le fichier garradin_plugin.ini ne contient pas d\'entrée "'.$key.'".');
			}
................................................................................

			$config = json_encode($config);
		}

		$data = [
			'id' 		=> 	$id,
			'officiel' 	=> 	(int)(bool)$official,
			'name'		=>	$infos->name,
			'description'=>	$infos->description,
			'author'	=>	$infos->author,
			'url'		=>	$infos->url,
			'version'	=>	$infos->version,
			'menu'		=>	(int)(bool)$infos->menu,
			'config'	=>	$config,
		];

		if ($infos->menu && !empty($infos->menu_condition))
................................................................................
	 * @param  array  $params Paramètres du callback (array ou null)
	 * @return NULL 		  NULL si aucun plugin n'a été appelé,
	 * TRUE si un plugin a été appelé et a arrêté l'exécution,
	 * FALSE si des plugins ont été appelés mais aucun n'a stopé l'exécution
	 */
	static public function fireSignal($signal, $params = null, &$callback_return = null)
	{
		if (!self::$signals) {
			return null;
		}

		$list = DB::getInstance()->get('SELECT * FROM plugins_signals WHERE signal = ?;', $signal);

		if (!count($list)) {
			return null;
		}

		if (null === $params) {
			$params = [];

<?php

namespace Garradin;

use Garradin\Membres\Session;
use Garradin\Membres\Champs;

use Garradin\Files\Files;

class Upgrade
{
	const MIN_REQUIRED_VERSION = '0.9.8';

	static public function preCheck(): bool
	{
		$v = DB::getInstance()->version();

		if (version_compare($v, garradin_version(), '>='))
		{
................................................................................
		return true;
	}

	static public function upgrade()
	{
		$db = DB::getInstance();
		$v = $db->version();



		Static_Cache::store('upgrade', 'Mise à jour en cours.');

		// Créer une sauvegarde automatique
		$backup_name = (new Sauvegarde)->create(false, 'pre-upgrade-' . garradin_version());

		try {
			if (version_compare($v, '1.0.0-rc1', '<'))
			{
				$db->beginSchemaUpdate();
				$db->import(ROOT . '/include/data/1.0.0_migration.sql');
				$db->commitSchemaUpdate();
			}


			if (version_compare($v, '1.0.0-rc10', '<'))
			{
				$db->beginSchemaUpdate();
				$db->import(ROOT . '/include/data/1.0.0-rc10_migration.sql');
				$db->commitSchemaUpdate();
			}

			if (version_compare($v, '1.0.0-beta1', '>=') && version_compare($v, '1.0.0-rc11', '<'))
			{
				// Missing trigger
				$db->beginSchemaUpdate();
				$db->import(ROOT . '/include/data/1.0.0_schema.sql');
				$db->commitSchemaUpdate();
			}

			if (version_compare($v, '1.0.0-rc14', '<'))
			{
				// Missing trigger
				$db->beginSchemaUpdate();
				$db->import(ROOT . '/include/data/1.0.0-rc14_migration.sql');
				$db->commitSchemaUpdate();
			}

			if (version_compare($v, '1.0.0-rc16', '<'))
			{
				// Missing trigger
				$db->beginSchemaUpdate();
				$db->import(ROOT . '/include/data/1.0.0-rc16_migration.sql');
				$db->commitSchemaUpdate();
			}

			if (version_compare($v, '1.0.1', '<'))
			{
				// Missing trigger
				$db->begin();
				$db->import(ROOT . '/include/data/1.0.1_migration.sql');
				$db->commit();
			}

			if (version_compare($v, '1.0.3', '<'))
			{
				// Missing trigger
				$db->begin();
				$db->import(ROOT . '/include/data/1.0.3_migration.sql');
				$db->commit();
			}

			if (version_compare($v, '1.0.6', '<'))
			{
				// Missing trigger
				$db->begin();
				$db->import(ROOT . '/include/data/1.0.6_migration.sql');
				$db->commit();
			}

			if (version_compare($v, '1.0.7', '<'))
			{
				// Missing trigger
				$db->begin();
				$db->import(ROOT . '/include/data/1.0.7_migration.sql');
				$db->commit();
			}

			if (version_compare($v, '1.1.0-beta1', '<'))
			{
				// Missing trigger
				$db->beginSchemaUpdate();

				$attachments = $db->getAssoc('SELECT f.id, w.uri || \'/\' || f.id || \'_\' || f.nom FROM fichiers f
					INNER JOIN fichiers_wiki_pages fw ON fw.fichier = f.id
					INNER JOIN wiki_pages w ON w.id = fw.id;');

				// Update Skriv content for attachments
				foreach ($db->iterate('SELECT r.rowid, r.contenu, p.uri FROM wiki_revisions r INNER JOIN wiki_pages p ON p.revision = r.revision AND p.id = r.id_page;') as $r) {
					$uri = $r->uri;
					$content = preg_replace_callback('!<<(image|fichier)\s*\|\s*(\d+)\s*(?:\|\s*(gauche|droite|centre))?\s*(?:\|\s*(.+)\s*)?>>!', function ($match) use ($attachments, $uri) {
						if (isset($attachments[$match[2]])) {
							$name = $attachments[$match[2]];

							if (dirname($name) == $uri) {
								$name = basename($name);
							}
							else {
								$name = '../' . $name;
							}
						}
						else {
							$name = '_ERREUR_fichier_inconnu_' . $match[2];
						}

						if (isset($match[3])) {
							$align = '|' . ($match[3] == 'centre' ? 'center' : ($match[3] == 'gauche' ? 'left' : 'right'));
						}
						else {
							$align = '';
						}

						$caption = isset($match[4]) ? '|' . $match[4] : '';

						return sprintf('<<%s|%s%s%s>>', $match[1] == 'fichier' ? 'file' : 'image', $name, $align, $caption);
					}, $r->contenu);

					$content = preg_replace_callback('!(image|fichier)://(\d+)!', function ($match) use ($attachments) {
						$name = $attachments[$match[2]] ?? '_ERREUR_fichier_inconnu_' . $match[2];
						return sprintf('#file:[%s]', $name);
					}, $content);

					if ($content != $r->contenu) {
						$db->update('wiki_revisions', ['contenu' => $content], 'rowid = :id', ['id' => $r->rowid]);
					}
				}

				$champs = new Champs($db->firstColumn('SELECT valeur FROM config WHERE cle = \'champs_membres\';'));
				$db->import(ROOT . '/include/data/1.1.0_migration.sql');

				// Rename membres table
				$champs->createTable($champs::TABLE  .'_tmp');

				$fields = $champs->getCopyFields();
				unset($fields['id_category']);
				$fields['id_categorie'] = 'id_category';
				$champs->copy($champs::TABLE, $champs::TABLE . '_tmp', $fields);

				$db->exec(sprintf('DROP TABLE IF EXISTS %s;', $champs::TABLE));
				$db->exec(sprintf('ALTER TABLE %s_tmp RENAME TO %1$s;', $champs::TABLE));

				$champs->createIndexes($champs::TABLE);

				$db->commitSchemaUpdate();

				// Migrate to a different storage
				if (FILE_STORAGE_BACKEND != 'SQLite') {
					Files::migrateStorage('SQLite', FILE_STORAGE_BACKEND, null, FILE_STORAGE_CONFIG);
					Files::truncateStorage('SQLite', null);
				}

				$pages = $db->iterate('SELECT * FROM web_pages;');

				foreach ($pages as $data) {
					$page = new \Garradin\Entities\Web\Page;
					$page->exists(true);
					$page->load((array) $data);
					$page->syncSearch();
				}
			}

			if (version_compare($v, '1.1.1', '<')) {
				// Reset admin_background if the file does not exist
				$bg = $db->firstColumn('SELECT value FROM config WHERE key = \'admin_background\';');

				if ($bg) {
					$file = Files::get($bg);

................................................................................
				$db->begin();
				$db->import(ROOT . '/include/data/1.1.3_migration.sql');
				$db->commit();
			}

			if (version_compare($v, '1.1.4', '<')) {
				// Set config file names
				$config = Config::getInstance();

				$file = Files::get(Config::DEFAULT_FILES['admin_background']);
				$config->set('admin_background', $file ? Config::DEFAULT_FILES['admin_background'] : null);

				$file = Files::get(Config::DEFAULT_FILES['admin_homepage']);
				$config->set('admin_homepage', $file ? Config::DEFAULT_FILES['admin_homepage'] : null);

				$config->save();
			}

			if (version_compare($v, '1.1.7', '<')) {
				$db->begin();
				$db->import(ROOT . '/include/data/1.1.7_migration.sql');
				$db->commit();
			}
................................................................................

				// Add UNIQUE index
				$db->import(ROOT . '/include/data/1.1.8_migration.sql');

				$db->commit();
			}

			if (version_compare($v, '1.1.8', '==')) {
				// Force sync to add missing pages if you had the buggy 1.1.8 version
				\Garradin\Web\Web::sync(true);
			}

			if (version_compare($v, '1.1.10', '<')) {
				\Garradin\Web\Web::sync(true); // Force sync of web pages
				Files::syncVirtualTable('', true);

				$db->begin();
				$db->exec(sprintf('DELETE FROM files_search WHERE path NOT IN (SELECT path FROM %s);', Files::getVirtualTableName()));
				$db->commit();
			}









			// Vérification de la cohérence des clés étrangères
			$db->foreignKeyCheck();

			// Delete local cached files
			Utils::resetCache(USER_TEMPLATES_CACHE_ROOT);
			Utils::resetCache(STATIC_CACHE_ROOT);

<?php

namespace Garradin;

use Garradin\Membres\Session;


use Garradin\Files\Files;

class Upgrade
{
	const MIN_REQUIRED_VERSION = '1.1.0';

	static public function preCheck(): bool
	{
		$v = DB::getInstance()->version();

		if (version_compare($v, garradin_version(), '>='))
		{
................................................................................
		return true;
	}

	static public function upgrade()
	{
		$db = DB::getInstance();
		$v = $db->version();

		Plugin::toggleSignals(false);

		Static_Cache::store('upgrade', 'Mise à jour en cours.');

		// Créer une sauvegarde automatique
		$backup_name = (new Sauvegarde)->create(false, 'pre-upgrade-' . garradin_version());

		try {


























































































































































			if (version_compare($v, '1.1.1', '<')) {
				// Reset admin_background if the file does not exist
				$bg = $db->firstColumn('SELECT value FROM config WHERE key = \'admin_background\';');

				if ($bg) {
					$file = Files::get($bg);

................................................................................
				$db->begin();
				$db->import(ROOT . '/include/data/1.1.3_migration.sql');
				$db->commit();
			}

			if (version_compare($v, '1.1.4', '<')) {
				// Set config file names


				$file = Files::get(Config::DEFAULT_FILES['admin_background']);
				$db->update('config', ['value' => $file ? Config::DEFAULT_FILES['admin_background'] : null], 'key = \'admin_background\'');

				$file = Files::get(Config::DEFAULT_FILES['admin_homepage']);
				$db->update('config', ['value' => $file ? Config::DEFAULT_FILES['admin_homepage'] : null], 'key = \'admin_homepage\'');


			}

			if (version_compare($v, '1.1.7', '<')) {
				$db->begin();
				$db->import(ROOT . '/include/data/1.1.7_migration.sql');
				$db->commit();
			}
................................................................................

				// Add UNIQUE index
				$db->import(ROOT . '/include/data/1.1.8_migration.sql');

				$db->commit();
			}






			if (version_compare($v, '1.1.10', '<')) {
				\Garradin\Web\Web::sync(true); // Force sync of web pages
				Files::syncVirtualTable('', true);

				$db->begin();
				$db->exec(sprintf('DELETE FROM files_search WHERE path NOT IN (SELECT path FROM %s);', Files::getVirtualTableName()));
				$db->commit();
			}

			if (version_compare($v, '1.2.0', '<')) {
				$db->begin();
				$db->import(ROOT . '/include/data/1.2.0_migration.sql');
				$db->commit();

				$db->exec('DELETE FROM config WHERE key = \'champs_membres\';');
			}

			// Vérification de la cohérence des clés étrangères
			$db->foreignKeyCheck();

			// Delete local cached files
			Utils::resetCache(USER_TEMPLATES_CACHE_ROOT);
			Utils::resetCache(STATIC_CACHE_ROOT);

<?php

namespace Garradin\Users;

use Garradin\Config;
use Garradin\DB;
use Garradin\Utils;

use Garradin\Entities\Users\DynamicField;
use Garradin\Entities\Users\User;

class DynamicFields
{
	const PRESETS_FILE = ROOT . '/include/data/users_fields_presets.ini';

	const TABLE = DynamicFields::TABLE;

	protected $_fields;
	protected $_fields_by_type;
	protected $_fields_by_system_use;
	protected $_presets;

	static protected $_instance;

	static public function getInstance()
	{
		if (null === self::$_instance) {
			self::$_instance = new self;
		}

		return self::$_instance;
	}

	/**
	 * Returns the list of columns containing an email address (there might be more than one)
	 * @return array
	 */
	static public function getEmailFields(): array
	{
		return array_keys(self::getInstance()->fieldsByType('email'));
	}

	static public function getNumberField(): string
	{
		return key(self::getInstance()->fieldsBySystemUse('number'));
	}

	static public function getLoginField(): string
	{
		return key(self::getInstance()->fieldsBySystemUse('login'));
	}

	static public function getNameFields(): array
	{
		return array_keys(self::getInstance()->fieldsBySystemUse('name'));
	}

	static public function getNameFieldsSQL(): string
	{
		return implode(' || \' \' ', array_keys(self::getInstance()->fieldsBySystemUse('name')));
	}

	static public function getEntityProperties(): array
	{
		$fields = self::getEntityTypes();
		return DynamicField::SYSTEM_FIELDS + $fields;
	}

	static public function changeLoginField(string $new_field): void
	{
		$old_field = self::getLoginField();

		if ($old_field === $new_field) {
			return;
		}

		$db = DB::getInstance();

		$sql = sprintf('UPDATE %s SET system = NULL WHERE system = \'login\';
			UPDATE %1$s SET system = \'login\' WHERE key = %s;',
			self::TABLE,
			$new_field
		);

		$db->exec($sql);

		// Regenerate login index
		$db->exec('DROP INDEX IF EXISTS users_id_field;');
		$this->createIndexes();
	}

	protected function __construct()
	{
		$this->reload();
	}

	protected function reload()
	{
		$db = DB::getInstance();
		$this->_fields = $db->getGrouped('SELECT key, * FROM config_users_fields ORDER BY "order";');
		$this->reloadCache();
	}

	protected function reloadCache()
	{
		$this->_fields_by_type = [];
		$this->_fields_by_system_use = [];

		foreach ($this->_fields as $key => &$field) {
			if (!isset($this->_fields_by_type[$field->type])) {
				$this->_fields_by_type[$field->type] = [];
			}

			$this->_fields_by_type[$field->type][$key] =& $field;

			if (!$field->system) {
				continue;
			}

			if (!isset($this->_fields_by_system_use[$field->system])) {
				$this->_fields_by_system_use[$field->system] = [];
			}

			$this->_fields_by_system_use[$field->system][$key] =& $field;
		}
	}

	protected function fieldsByType(string $type): array
	{
		return $this->_fields_by_type[$type] ?? [];
	}

	protected function fieldsBySystemUse(string $use): array
	{
		return $this->_fields_by_system_use[$use] ?? [];
	}

	public function getEntityTypes(): array
	{
		$types = [];

		foreach ($this->_fields as $key => $field) {
			$types[$key] = $field->type;
		}

		return $types;
	}

	public function getPresets(): array
	{
		if (null === $this->_presets)
		{
			$this->_presets = parse_ini_file(self::PRESETS_FILE, true);

			foreach ($this->_presets as $preset) {
				$preset = (object) $preset;
			}

			unset($preset);
		}

		return $this->_presets;
	}

	public function listUnusedPresets(): array
	{
		return array_diff_key(self::getPresets(), (array) $this->_fields);
	}

	public function getInstallPresets()
	{
		return array_filter($this->getPresets(), function ($row) { return !$row->install; });
	}

	public function isText(string $field)
	{
		$type = $this->_fields[$field]->type;
		return self::TYPES[$type] == 'string';
	}

	public function getKeys()
	{
		return array_keys($this->_fields);
	}

	public function all()
	{
		return $this->_fields;
	}

	public function allExceptPassword()
	{
		return array_filter($this->_fields, function ($a) {
			return $a->system != 'password';
		});
	}

	public function listAssocNames()
	{
		$out = [];

		foreach ($this->_fields as $key => $field) {
			if ($field->system == 'password') {
				continue;
			}

			$out[$key] = $field->label;
		}

		return $out;
	}

	public function getMultiples()
	{
		return array_filter($this->_fields, function ($a) {
			return $a->type == 'multiple';
		});
	}

	public function getListedFields()
	{
		$champs = (array) $this->champs;

		$champs = array_filter($champs, function ($a) {
			return empty($a->list_row) ? false : true;
		});

		uasort($champs, function ($a, $b) {
			if ($a->list_row == $b->list_row)
				return 0;

			return ($a->list_row > $b->list_row) ? 1 : -1;
		});

		return (object) $champs;
	}

	public function getFirstListed()
	{
		foreach ($this->champs as $key=>$config)
		{
			if (empty($config->list_row))
			{
				continue;
			}

			return $key;
		}
	}



	public function getSQLSchema(string $table_name = User::TABLE): string
	{
		$db = DB::getInstance();

		// Champs à créer
		$create = [
			'id INTEGER PRIMARY KEY, -- Numéro attribué automatiquement',
			'id_category INTEGER NOT NULL REFERENCES users_categories(id),',
			'date_login TEXT NULL CHECK (date_login IS NULL OR datetime(date_login) = date_login), -- Date de dernière connexion',
			'date_created TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date_created) = date_created), -- Date d\'inscription',
			'otp_secret TEXT NULL, -- Code secret pour TOTP',
			'pgp_key TEXT NULL, -- Clé publique PGP'
		];

		end($this->_fields);
		$last_one = key($this->_fields);

		foreach ($this->_fields as $key=>$cfg)
		{
			$type = DynamicField::SQL_TYPES[$field->type];

			if ($type == 'TEXT') {
				$type = 'TEXT COLLATE NOCASE';
			}

			$line = sprintf('%s %s', $db->quoteIdentifier($key), $type);

			if ($last_one != $key) {
				$line .= ',';
			}

			if (!empty($cfg->title))
			{
				$line .= ' -- ' . str_replace(["\n", "\r"], '', $cfg->label);
			}

			$create[] = $line;
		}

		$sql = sprintf("CREATE TABLE %s\n(\n\t%s\n);", $table_name, implode("\n\t", $create));
		return $sql;
	}

	public function getCopyFields(): array
	{
		// Champs à recopier
		$copy = array_keys(DynamicField::SYSTEM_FIELDS);

		$db = DB::getInstance();
		// FIXME
		$anciens_champs = new Champs($db->firstColumn('SELECT value FROM config WHERE key = ?;', 'champs_membres'));
		$anciens_champs = is_null($anciens_champs) ? $this->champs : $anciens_champs->getAll();

		foreach ($this->champs as $key=>$cfg)
		{
			if (property_exists($anciens_champs, $key)) {
				$copy[$key] = $key;
			}
		}

		return $copy;
	}

	public function getSQLCopy(string $old_table_name, string $new_table_name = User::TABLE, array $fields = null): string
	{
		if (null === $fields) {
			$fields = $this->getCopyFields();
		}

		$db = DB::getInstance();

		return sprintf('INSERT INTO %s (%s) SELECT %s FROM %s;',
			$new_table_name,
			implode(', ', array_map([$db, 'quoteIdentifier'], $fields)),
			implode(', ', array_map([$db, 'quoteIdentifier'], array_keys($fields))),
			$old_table_name
		);
	}

	public function copy(string $old_table_name, string $new_table_name = User::TABLE, array $fields = null): void
	{
		DB::getInstance()->exec($this->getSQLCopy($old_table_name, $new_table_name, $fields));
	}

	public function create(string $table_name = User::TABLE)
	{
		$db = DB::getInstance();
		$db->begin();
		$this->createTable($table_name);
		$this->createIndexes($table_name);
		$db->commit();
	}

	public function createTable(string $table_name = User::TABLE): void
	{
		DB::getInstance()->exec($this->getSQLSchema($table_name));
	}

	public function createIndexes(string $table_name = User::TABLE): void
	{
		$db = DB::getInstance();
		$id_field = $db->firstColumn('SELECT value FROM config WHERE key = ?;', 'champ_identifiant');

		if ($id_field) {
			// Mettre les champs identifiant vides à NULL pour pouvoir créer un index unique
			$db->exec(sprintf('UPDATE %s SET %s = NULL WHERE %2$s = \'\';',
				$table_name, $id_field));

			$collation = '';

			if ($this->isText($id_field)) {
				$collation = ' COLLATE NOCASE';
			}

			// Création de l'index unique
			$db->exec(sprintf('CREATE UNIQUE INDEX IF NOT EXISTS users_id_field ON %s (%s%s);', $table_name, $id_field, $collation));
		}

		$db->exec(sprintf('CREATE UNIQUE INDEX IF NOT EXISTS user_number ON %s (numero);', $table_name));
		$db->exec(sprintf('CREATE INDEX IF NOT EXISTS users_category ON %s (id_category);', $table_name));

		// Create index on listed columns
		// FIXME: these indexes are currently unused by SQLite in the default user list
		// when there is more than one non-hidden category, as this makes SQLite merge multiple results
		// and so the index is not useful in that case sadly.
		// EXPLAIN QUERY PLAN SELECT * FROM membres WHERE "id_category" IN (3) ORDER BY "nom" ASC LIMIT 0,100;
		// --> SEARCH TABLE membres USING INDEX users_list_nom (id_category=?)
		// EXPLAIN QUERY PLAN SELECT * FROM membres WHERE "id_category" IN (3, 7) ORDER BY "nom" ASC LIMIT 0,100;
		// --> SEARCH TABLE membres USING INDEX user_category (id_category=?)
		// USE TEMP B-TREE FOR ORDER BY
		$listed_fields = array_keys((array) $this->getListedFields());
		foreach ($listed_fields as $field) {
			if ($field === $id_field) {
				// Il y a déjà un index
				continue;
			}

			$collation = '';

			if ($this->isText($field)) {
				$collation = ' COLLATE NOCASE';
			}

			$db->exec(sprintf('CREATE INDEX IF NOT EXISTS users_list_%s ON %s (id_category, %1$s%s);', $field, $table_name, $collation));
		}
	}

	/**
	 * Enregistre les changements de champs en base de données
	 * @return boolean true
	 */
	public function rebuildUsersTable()
	{
		$db = DB::getInstance();

		$db->exec('PRAGMA foreign_keys = OFF;');

		$db->begin();
		$this->createTable(User::TABLE . '_tmp');
		$this->copy(User::TABLE, User::TABLE . '_tmp');
		$db->exec(sprintf('DROP TABLE IF EXISTS %s;', User::TABLE));
		$db->exec(sprintf('ALTER TABLE %s_tmp RENAME TO %1$s;', User::TABLE));

		$this->createIndexes(User::TABLE);

		$db->commit();
		$db->exec('PRAGMA foreign_keys = ON;');

		return true;
	}

	public function preview(array $fields)
	{
	}

	public function save(array $fields)
	{
		foreach ($this->_fields_by_system_use as $key => $field) {
			if (!array_key_exists($key, $fields)) {
				throw new UserException(sprintf('Le champ "%s" ne peut être supprimé des fiches membres car il est utilisé dans la configuration.'));
			}
		}

		foreach ($fields as $field) {
			$field->save();
		}
	}
}

<?php

namespace Garradin\Web\Render;

use Garradin\Utils;
use Garradin\Entities\Files\File;


trait AttachmentAwareTrait
{
	protected $current_path;
	protected $context;
	protected $link_prefix;
	protected $link_suffix;

	protected function resolveAttachment(string $uri) {
		$prefix = $this->current_path;
		$pos = strpos($uri, '/');

		// "Image.jpg"
		if ($pos === false) {
			return WWW_URL . $prefix . '/' . $uri;
		}
		// "bla/Image.jpg" outside of web context
		elseif ($this->context !== File::CONTEXT_WEB && $pos !== 0) {
			return WWW_URL . $this->context . '/' . $uri;
		}
		// "bla/Image.jpg" in web context or absolute link, eg. "/transactions/2442/42.jpg"
		else {
			return WWW_URL . ltrim($uri, '/');
		}
	}

	protected function resolveLink(string $uri) {
		$first = substr($uri, 0, 1);
		if ($first == '/' || $first == '!') {
			return Utils::getLocalURL($uri);
		}

		if (strpos(Utils::basename($uri), '.') === false) {
			$uri .= $this->link_suffix;
		}

		return $this->link_prefix . $uri;
	}

	public function isRelativeTo(File $file) {
		$this->current_path = Utils::dirname($file->path);
		$this->context = strtok($this->current_path, '/');
		$this->link_suffix = '';

		if ($this->context === File::CONTEXT_WEB) {
			$this->link_prefix = WWW_URL . '/';
			$this->current_path = Utils::basename(Utils::dirname($file->path));
		}
		else {
			$this->link_prefix = $options['prefix'] ?? sprintf(ADMIN_URL . 'common/files/preview.php?p=%s/', $this->context);
			$this->link_suffix = '.skriv';
		}
	}
}

						{/if}
					</td>
				</tr>
				{/if}
			{/foreach}

			</tbody>

			{if $can_delete}
			<tfoot>
				<tr>
					<td class="check"><input type="checkbox" value="Tout cocher / décocher" id="f_all2" /><label for="f_all2"></label></td>
					<td class="actions" colspan="5">
						<em>Pour les fichiers sélectionnés&nbsp;:</em>
							<input type="hidden" name="parent" value="{$path}" />

						{/if}
					</td>
				</tr>
				{/if}
			{/foreach}

			</tbody>

			{if $can_delete}
			<tfoot>
				<tr>
					<td class="check"><input type="checkbox" value="Tout cocher / décocher" id="f_all2" /><label for="f_all2"></label></td>
					<td class="actions" colspan="5">
						<em>Pour les fichiers sélectionnés&nbsp;:</em>
							<input type="hidden" name="parent" value="{$path}" />

}

if (!$file->checkWriteAccess($session)) {
    throw new UserException('Vous n\'avez pas le droit de modifier ce fichier.');
}

$context = $file->context();





$csrf_key = 'file_rename_' . $file->pathHash();

$form->runIf('rename', function () use ($file) {
	$file->changeFileName(f('new_name'));
}, $csrf_key, '!');

$tpl->assign(compact('file', 'csrf_key'));

$tpl->display('common/files/rename.tpl');

}

if (!$file->checkWriteAccess($session)) {
    throw new UserException('Vous n\'avez pas le droit de modifier ce fichier.');
}

$context = $file->context();

if ($context == File::CONTEXT_CONFIG || $context == File::CONTEXT_WEB) {
	throw new UserException('Vous n\'avez pas le droit de renommer ce fichier.');
}

$csrf_key = 'file_rename_' . $file->pathHash();

$form->runIf('rename', function () use ($file) {
	$file->changeFileName(f('new_name'));
}, $csrf_key, '!');

$tpl->assign(compact('file', 'csrf_key'));

$tpl->display('common/files/rename.tpl');

(function () {

const TYPES = ['code', ];

window.WebEditor = class {
	constructor(textarea) {


		this.textarea = textarea;
		this.parse(textarea.value)
		this.editor = 



	}












	build() {

































































	}

	parse(text) {
		var blocks = text.split("\n\n----\n\n");
		this.blocks = [];

		for (var i = 0; i < blocks.length; i++) {
			let block = blocks[i];


			if ((i % 1) == 0) {


				this.blocks.push(this.parseMeta(block));
			}





			else {
				this.blocks[this.blocks.length - 1].content = block.trim("\r\n");










			}
		}
	}

	parseMeta(text) {

		var lines = text.split("\n");
		var meta = {};







		for (var i = 0; i < lines.length; i++) {
			var line = lines[i].split(':', 2);

			if (line.length != 2) {
				continue;
			}

			meta[line[0].trim().toLowerCase()] = line[1].trim();
		}

		return meta;
	}
}

})();

(function () {

const TYPES = ['code', ];

window.WebEditor = class {
	constructor() {
	}

	editor(textarea) {
		this.parse(textarea.value);

		this.textarea = textarea;
		this.form = textarea.form;
		textarea.style.display = 'none';

		this.form.addEventListener('submit', () => {
			let e = document.createElement('input');
			e.type = 'hidden';
			e.name = this.textarea.name;
			e.value = this.export();
			this.textarea.remove();
			form.appendChild(e);
		});

		this.textarea.parentNode.insertBefore(this.buildEditor(), this.textarea);
	}

	buildEditor() {
		var e = document.createElement('div');
		e.className = 'web-editor';

		e.appendChild(this.buildToolbar());

		var c = document.createElement('div');
		c.className = 'web-editor-content';

		for (var i = 0; i < this.blocks; i++) {
			c.appendChild(this.buildBlock(this.blocks[i]));
		}

		e.appendChild(c);

		return e;
	}

	buildToolbar() {
		// add column, add row, move up, move down, change type, delete + block buttons
	}

	buildBlock(block) {
		let o = document.createElement('div');

		switch (block.type) {
			case 'columns':
				o.className = 'web-columns';
				block.columns.forEach((b) => o.appendChild(this.buildBlock(b)));
				break;
			case 'column':
				o.className = 'web-column';
				block.blocks.forEach((b) => o.appendChild(this.buildBlock(b)));
				break;
			case 'heading':
				var i = document.createElement('input');
				i.value = block.content;
				o.appendChild(i);
				break;
			case 'markdown':
				o.appendChild(this.buildMarkdownEditor(block));
				break;
			case 'skriv':
				o.appendChild(this.buildSkrivEditor(block));
				break;
			case 'video':
				o.appendChild(this.buildVideoEditor(block));
				break;
			case 'image':
				o.appendChild(this.buildImageEditor(block));
				break;
			case 'gallery':
				o.appendChild(this.buildGalleryEditor(block));
				break;
			case 'code':
			case 'quote':
			default:
				var i = document.createElement('textarea');
				i.value = block.content;
				o.appendChild(i);
				break;
				//throw Error('Unknown block type');
		}
	}

	buildMarkdownEditor(block) {
	}

	parse(text) {
		var raw_blocks = text.split("\n\n----\n\n");
		this.blocks = [];

		for (var i = 0; i < raw_blocks.length; i++) {
			let block = this.parseBlock(raw_blocks[i]);
			let last = this.blocks.length ? this.blocks[this.blocks.length - 1] : null;


			if (block.type == 'columns') {
				block.columns = [];
				this.blocks.push(block);
			}
			else if (block.type == 'column') {
				if (last && last.type == 'columns') {
					block.blocks = [];
					last.columns.push(block);
				}
				else {

					console.log("Invalid column", block);
				}
			}
			else {
				if (last && last.columns.length) {
					last.columns[last.columns.length - 1].blocks = block;
				}
				else {
					console.log("Invalid block", block);
				}
			}
		}
	}

	parseBlock(text) {
		text = text.replace("\r", "");
		var parts = text.split("\n\n", 2);

		var headers = {};
		var content = parts.length == 2 ? parts[1] : '';
		var lines = parts[0].split("\n");

		// Un-escape line separators
		content = content.replace("\n\n\\----\n\n", "\n\n----\n\n");

		for (var i = 0; i < lines.length; i++) {
			var line = lines[i].split(':', 2);

			if (line.length != 2) {
				continue;
			}

			headers[line[0].trim().toLowerCase()] = line[1].trim();
		}

		return {'content': content, 'headers': headers};
	}
}

})();