1.1.26

 * la recopiant à la place du fichier association.sqlite
 *
 * Défaut : true
 */

//const ALLOW_MODIFIED_IMPORT = true;

/**
 * Doit-on suggérer à l'utilisateur d'utiliser la version chiffrée du site ?
 *
 * 1 ou true = affiche un message de suggestion sur l'écran de connexion invitant à utiliser le site chiffré
 * (conseillé si vous avez un certificat auto-signé ou peu connu type CACert)
 * 2 = rediriger automatiquement sur la version chiffrée pour l'administration (mais pas le site public)
 * 3 = rediriger automatiquement sur la version chiffrée pour administration et site public
 * false ou 0 = aucune version chiffrée disponible, donc ne rien proposer ni rediriger
 *
 * Défaut : false
 */

//const PREFER_HTTPS = false;

/**
 * Répertoire où se situe le code source de Garradin
 *
 * Défaut : répertoire racine de Garradin (__DIR__)
 */

//const ROOT = __DIR__;

 * PrinceXML, Chromium, wkhtmltopdf ou weasyprint. Si aucune solution n'est disponible,
 * une erreur sera levée.
 *
 * %1$s sera remplacé par le chemin du fichier HTML, et %2$s par le chemin du fichier PDF.
 *
 * Exemple : chromium --headless --print-to-pdf=%2$s %1$s
 *







 * Défaut : null
 */
//const PDF_COMMAND = 'wkhtmltopdf -q --print-media-type --enable-local-file-access %s %s';

/**
 * CALC_CONVERT_COMMAND
 * Outil de conversion de formats de tableur vers un format propriétaire

 * Il faut recopier cette clé dans le fichier config.local.php
 * dans la constante CONTRIBUTOR_LICENSE.
 *
 * Merci de ne pas essayer de contourner cette licence et de contribuer au
 * financement de notre travail :-)
 */
//const CONTRIBUTOR_LICENSE = 'XXXXX';

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 GROUP BY closed ORDER BY closed LIMIT 1)
	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_pca_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);

-- Remove triggers in case they interact with the migration
DROP TRIGGER IF EXISTS wiki_recherche_delete;
DROP TRIGGER IF EXISTS wiki_recherche_update;
DROP TRIGGER IF EXISTS wiki_recherche_contenu_insert;
DROP TRIGGER IF EXISTS wiki_recherche_contenu_chiffre;

-- Fix some rare edge cases where date_inscription is incorrect
UPDATE membres SET date_inscription = date() WHERE date(date_inscription) IS NULL;

-- Uh, force another login id if email is not correct
UPDATE config SET valeur = 'numero' WHERE cle = 'champ_identifiant' AND valeur = 'email'
	AND (SELECT COUNT(*) FROM membres WHERE email IS NOT NULL GROUP BY LOWER(email) HAVING COUNT(*) > 1 LIMIT 1);

-- Other weird things to fix before migration
UPDATE wiki_pages SET uri = 'page_' || id WHERE uri = '' OR uri IS NULL;
DELETE FROM config WHERE cle = 'connexion' OR cle = 'wiki';

ALTER TABLE membres_categories RENAME TO membres_categories_old;

INSERT OR IGNORE INTO config (cle, valeur) VALUES ('desactiver_site', '0');
ALTER TABLE config RENAME TO config_old;

.read 1.1.0_schema.sql

INSERT INTO config SELECT * FROM config_old;
DROP TABLE config_old;

-- This is not used anymore
DELETE FROM config WHERE key = 'version';
DELETE FROM config WHERE key = 'accueil_wiki';

-- New config key
INSERT INTO config (key, value) VALUES ('telephone_asso', NULL);

-- Create directories
INSERT INTO files (parent, name, path, type) VALUES ('', 'documents', 'documents', 2);
INSERT INTO files (parent, name, path, type) VALUES ('', 'config', 'config', 2);
INSERT INTO files (parent, name, path, type) VALUES ('', 'transaction', 'transaction', 2);
INSERT INTO files (parent, name, path, type) VALUES ('', 'skel', 'skel', 2);
INSERT INTO files (parent, name, path, type) VALUES ('', 'user', 'user', 2);
INSERT INTO files (parent, name, path, type) VALUES ('', 'web', 'web', 2);

-- Copy droit_wiki value to droit_web and droit_documents
INSERT INTO users_categories
	SELECT id, nom,
		droit_wiki, -- perm_web
		droit_wiki, -- perm_documents
		droit_membres,
		droit_compta,
		droit_inscription,
		droit_connexion,
		droit_config,
		cacher
	FROM membres_categories_old;

DROP TABLE membres_categories_old;

UPDATE recherches SET contenu = REPLACE(contenu, 'id_categorie', 'id_category') WHERE cible = 'membres' AND contenu LIKE '%id_categorie%';

CREATE TEMP TABLE files_transactions (old_id, old_transaction, old_name, new_path, new_id, same_name);

-- Adding an extra step as some file names can have the same name
INSERT INTO files_transactions
	SELECT f.id, t.id, f.nom, NULL, NULL, NULL
	FROM fichiers f
		INNER JOIN fichiers_acc_transactions t ON t.fichier = f.id;

UPDATE files_transactions SET same_name = old_id || '_'
	WHERE old_id IN (SELECT old_id FROM files_transactions GROUP BY old_transaction, old_name HAVING COUNT(*) > 1);

-- Make file name is unique!
UPDATE files_transactions SET new_path = 'transaction/' || old_transaction || '/' || COALESCE(old_id || '_', '') || old_name;

-- Copy existing files for transactions
INSERT INTO files (path, parent, name, type, mime, modified, size, image)
	SELECT
		ft.new_path,
		dirname(ft.new_path),
		basename(ft.new_path),
		1, f.type, f.datetime, c.taille, f.image
	FROM files_transactions ft
		INNER JOIN fichiers f ON f.id = ft.old_id
		INNER JOIN fichiers_contenu c ON c.id = f.id_contenu;

UPDATE files_transactions SET new_id = (SELECT id FROM files WHERE path = new_path);

INSERT INTO files_contents (id, compressed, content)
	SELECT ft.new_id, 0, c.contenu
	FROM fichiers f
		INNER JOIN files_transactions ft ON ft.old_id = f.id
		INNER JOIN fichiers_contenu c ON c.id = f.id_contenu;

-- Copy wiki pages content
CREATE TEMP TABLE wiki_as_files (old_id, new_id, path, content, title, uri,
	old_parent, new_parent, created, modified, author_id, encrypted, type, public);

INSERT INTO wiki_as_files
	SELECT
		id, NULL, '', CASE WHEN contenu IS NULL THEN '' ELSE contenu END, titre, uri,
		parent, parent, date_creation, date_modification, id_auteur, chiffrement,
		CASE WHEN (SELECT 1 FROM wiki_pages pp WHERE pp.parent = p.id LIMIT 1) THEN 1 ELSE 2 END, -- Type, 1 = category, 2 = page
		CASE WHEN droit_lecture = -1 THEN 1 ELSE 0 END -- public
	FROM wiki_pages p
	LEFT JOIN wiki_revisions r ON r.id_page = p.id AND r.revision = p.revision;

-- Build path
WITH RECURSIVE path(level, uri, parent, id) AS (
	SELECT 0, uri, old_parent, old_id
	FROM wiki_as_files
	UNION ALL
	SELECT path.level + 1,
	wiki_as_files.uri,
	wiki_as_files.old_parent,
	path.id
	FROM wiki_as_files
	JOIN path ON wiki_as_files.old_id = path.parent
	WHERE level <= 8 -- max level = 8 to avoid recursion
),
path_from_root AS (
	SELECT group_concat(uri, '/') AS path, id
	FROM (SELECT id, uri FROM path ORDER BY level DESC)
	GROUP BY id
)
UPDATE wiki_as_files SET path = (SELECT path FROM path_from_root WHERE id = wiki_as_files.old_id);

-- remove recursion
UPDATE wiki_as_files SET path = uri WHERE path IS NULL OR LENGTH(path) - LENGTH(REPLACE(path, '/', '')) >= 8;

-- Copy into files
INSERT INTO files (path, parent, name, type, mime, modified, size)
	SELECT
		'web/' || path || '/index.txt',
		'web/' || path,
		'index.txt',
		1,
		'text/plain',
		modified,
		0 -- size will be set after
	FROM wiki_as_files;

UPDATE wiki_as_files SET new_id = (SELECT id FROM files WHERE files.path = 'web/' || (CASE WHEN wiki_as_files.path IS NOT NULL THEN wiki_as_files.path || '/' ELSE '' END) || wiki_as_files.uri || '/index.txt');

-- x'0a' == \n
INSERT INTO files_contents (id, compressed, content)
	SELECT f.id, 0,
		'Title: ' || title || x'0a' || 'Published: ' || created || x'0a' || 'Status: '
		|| (CASE WHEN public THEN 'Online' ELSE 'Draft' END)
		|| x'0a' || 'Format: ' || (CASE WHEN encrypted THEN 'Skriv/Encrypted' ELSE 'Skriv' END)
		|| x'0a' || x'0a' || '----' || x'0a' || x'0a' || content
	FROM wiki_as_files waf
	INNER JOIN files f ON f.path = 'web/' || waf.path || '/index.txt';

-- Copy to search
INSERT INTO files_search (path, title, content)
	SELECT
		'web/' || path || '/index.txt',
		title,
		CASE WHEN encrypted THEN NULL ELSE content END
	FROM wiki_as_files WHERE encrypted = 0;

-- Copy to web_pages
INSERT INTO web_pages (id, parent, path, uri, file_path, type, status, title, published, modified, format, content)
	SELECT new_id,
	CASE WHEN dirname(path) = '.' THEN '' ELSE dirname(path) END,
	path,
	uri,
	'web/' || path || '/index.txt',
	type,
	CASE WHEN public THEN 'online' ELSE 'draft' END,
	title, created, modified,
	CASE WHEN encrypted THEN 'skriv/encrypted' ELSE 'skriv' END,
	content
	FROM wiki_as_files;

CREATE TEMP TABLE files_wiki (old_id, wiki_id, web_path, old_name, new_path, new_id);

-- Adding an extra step as some file names can have the same name
INSERT INTO files_wiki
	SELECT f.id, w.id, waf.path, f.nom, 'web/' || waf.path || '/' || f.id || '_' || f.nom, NULL
	FROM fichiers f
		INNER JOIN fichiers_wiki_pages w ON w.fichier = f.id
		INNER JOIN wiki_as_files waf ON w.id = waf.old_id;

-- Copy files linked to wiki pages
INSERT INTO files (path, parent, name, type, mime, modified, size, image)
	SELECT
		fw.new_path,
		dirname(fw.new_path),
		basename(fw.new_path),
		1,
		f.type,
		f.datetime,
		c.taille,
		f.image
	FROM fichiers f
		INNER JOIN fichiers_contenu c ON c.id = f.id_contenu
		INNER JOIN files_wiki fw ON fw.old_id = f.id;

UPDATE files_wiki SET new_id = (SELECT id FROM files WHERE path = new_path);

INSERT INTO files_contents (id, compressed, content)
	SELECT
		fw.new_id, 0, c.contenu
	FROM files_wiki fw
		INNER JOIN fichiers f ON f.id = fw.old_id
		INNER JOIN fichiers_contenu c ON c.id = f.id_contenu;

-- Create parent directories
INSERT INTO files (type, path, parent, name, modified)
	SELECT 2,
		'web/' || waf.path,
		dirname('web/' || waf.path),
		waf.uri,
		modified
	FROM wiki_as_files waf;

INSERT OR IGNORE INTO files (type, path, parent, name) SELECT 2, parent, dirname(parent), basename(parent) FROM files WHERE type = 2 AND dirname(parent) != '.' AND dirname(parent) != '' AND (SELECT 1 FROM files f2 WHERE f2.path = dirname(files.parent) LIMIT 1) IS NULL;
INSERT OR IGNORE INTO files (type, path, parent, name) SELECT 2, parent, dirname(parent), basename(parent) FROM files WHERE type = 2 AND dirname(parent) != '.' AND dirname(parent) != '' AND (SELECT 1 FROM files f2 WHERE f2.path = dirname(files.parent) LIMIT 1) IS NULL;
INSERT OR IGNORE INTO files (type, path, parent, name) SELECT 2, parent, dirname(parent), basename(parent) FROM files WHERE type = 2 AND dirname(parent) != '.' AND dirname(parent) != '' AND (SELECT 1 FROM files f2 WHERE f2.path = dirname(files.parent) LIMIT 1) IS NULL;
INSERT OR IGNORE INTO files (type, path, parent, name) SELECT 2, parent, dirname(parent), basename(parent) FROM files WHERE type = 2 AND dirname(parent) != '.' AND dirname(parent) != '' AND (SELECT 1 FROM files f2 WHERE f2.path = dirname(files.parent) LIMIT 1) IS NULL;
INSERT OR IGNORE INTO files (type, path, parent, name) SELECT 2, parent, dirname(parent), basename(parent) FROM files WHERE type = 2 AND dirname(parent) != '.' AND dirname(parent) != '' AND (SELECT 1 FROM files f2 WHERE f2.path = dirname(files.parent) LIMIT 1) IS NULL;
INSERT OR IGNORE INTO files (type, path, parent, name) SELECT 2, parent, dirname(parent), basename(parent) FROM files WHERE type = 2 AND dirname(parent) != '.' AND dirname(parent) != '' AND (SELECT 1 FROM files f2 WHERE f2.path = dirname(files.parent) LIMIT 1) IS NULL;
INSERT OR IGNORE INTO files (type, path, parent, name) SELECT 2, parent, dirname(parent), basename(parent) FROM files WHERE type = 2 AND dirname(parent) != '.' AND dirname(parent) != '' AND (SELECT 1 FROM files f2 WHERE f2.path = dirname(files.parent) LIMIT 1) IS NULL;
INSERT OR IGNORE INTO files (type, path, parent, name) SELECT 2, parent, dirname(parent), basename(parent) FROM files WHERE type = 2 AND dirname(parent) != '.' AND dirname(parent) != '' AND (SELECT 1 FROM files f2 WHERE f2.path = dirname(files.parent) LIMIT 1) IS NULL;
INSERT OR IGNORE INTO files (type, path, parent, name) SELECT 2, parent, dirname(parent), basename(parent) FROM files WHERE type = 2 AND dirname(parent) != '.' AND dirname(parent) != '' AND (SELECT 1 FROM files f2 WHERE f2.path = dirname(files.parent) LIMIT 1) IS NULL;
INSERT OR IGNORE INTO files (type, path, parent, name) SELECT 2, parent, dirname(parent), basename(parent) FROM files WHERE type = 2 AND dirname(parent) != '.' AND dirname(parent) != '' AND (SELECT 1 FROM files f2 WHERE f2.path = dirname(files.parent) LIMIT 1) IS NULL;

-- Copy existing config files
INSERT INTO files (path, parent, name, type, mime, modified, size, image)
	SELECT 'config/admin_bg.png', 'config', 'admin_bg.png', 1, type, datetime, c.taille, image
	FROM fichiers f
		INNER JOIN fichiers_contenu c ON c.id = f.id_contenu
	WHERE f.id = (SELECT c.value FROM config c WHERE key = 'image_fond') LIMIT 1;

INSERT INTO files_contents (id, compressed, content)
	SELECT f2.id, 0, c.contenu
	FROM files AS f2
		INNER JOIN fichiers f ON f2.path = 'config/admin_bg.png'
		INNER JOIN fichiers_contenu c ON c.id = f.id_contenu
		WHERE f.id = (SELECT c.value FROM config c WHERE key = 'image_fond') LIMIT 1;

-- Rename
UPDATE config SET key = 'admin_background', value = 'config/admin_bg.png' WHERE key = 'image_fond';

-- Copy connection page as a single file
INSERT INTO files (path, parent, name, type, mime, modified, size, image)
	SELECT 'config/admin_homepage.skriv', 'config', 'admin_homepage.skriv', 1, 'text/plain', datetime(), LENGTH(content), 0
	FROM wiki_as_files
	WHERE uri = (SELECT value FROM config WHERE key = 'accueil_connexion');

INSERT INTO files_contents (id, compressed, content)
	SELECT f.id, 0, waf.content
	FROM files f
		INNER JOIN wiki_as_files waf ON waf.uri = (SELECT value FROM config WHERE key = 'accueil_connexion')
	WHERE f.path = 'config/admin_homepage.skriv';

-- Rename
UPDATE config SET key = 'admin_homepage', value = 'config/admin_homepage.skriv' WHERE key = 'accueil_connexion';
UPDATE config SET key = 'site_disabled' WHERE key = 'desactiver_site';

-- Create transaction directories
INSERT INTO files (path, parent, name, type) SELECT 'transaction/' || id, 'transaction', id, 2 FROM fichiers_acc_transactions GROUP BY id;

-- Set file size
UPDATE files SET size = (SELECT LENGTH(content) FROM files_contents WHERE id = files.id) WHERE type = 1;

DELETE FROM plugins_signaux WHERE signal LIKE 'boucle.%';

DROP TABLE wiki_recherche;

DROP TABLE wiki_pages;
DROP TABLE wiki_revisions;

DROP TABLE fichiers_wiki_pages;
DROP TABLE fichiers_acc_transactions;
DROP TABLE fichiers_membres;

DROP TABLE fichiers;
DROP TABLE fichiers_contenu;

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

-- Cohérence avec plan 2018
UPDATE acc_charts SET code = 'PCA1999' WHERE code = 'PCGA1999';

UPDATE acc_accounts SET type = 13 WHERE type = 0 AND code = '1068';
UPDATE acc_accounts SET type = 14 WHERE type = 0 AND code = '110';
UPDATE acc_accounts SET type = 15 WHERE type = 0 AND code = '119';

-- 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;

ALTER TABLE services_reminders_sent RENAME TO srs_old;

-- Add new column in services_reminders_sent
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,

    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);

INSERT INTO services_reminders_sent SELECT id, id_user, id_service, id_reminder, date, date FROM srs_old;
DROP TABLE srs_old;

-- Missing acc_years_delete trigger, again, because of missing symlink in previous release
-- 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;

-- Remove any leftover duplicates
DELETE FROM web_pages WHERE id IN (SELECT id FROM web_pages GROUP BY uri HAVING COUNT(*) > 1);

-- Add unique index
CREATE UNIQUE INDEX IF NOT EXISTS web_pages_uri ON web_pages (uri);

;	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

CREATE TABLE IF NOT EXISTS config (

    key TEXT PRIMARY KEY NOT NULL,
    value TEXT NULL
);











































































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_config INTEGER NOT NULL DEFAULT 0,

    hidden INTEGER NOT NULL DEFAULT 0
);

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

-- 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 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
    id_analytical INTEGER NULL REFERENCES acc_accounts (id) ON DELETE SET NULL
);

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, -- This can be NULL if there is no fee for the service

    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,

    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);

--
-- 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 -- 0 = fait partie du plan comptable original, 1 = 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);

-- Balance des comptes par exercice

        FROM acc_accounts a
        INNER JOIN acc_transactions_lines l ON l.id_account = a.id
        INNER JOIN acc_transactions t ON t.id = l.id_transaction
        GROUP BY t.id_year, a.id
    );

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 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
-- 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)
);

---------- 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 (datetime(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);

    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) = published),
    modified TEXT NOT NULL CHECK (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);

-- FIXME: rename to english
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
);

CREATE TABLE IF NOT EXISTS emails (
-- List of emails addresses
-- We are not storing actual email addresses here for privacy reasons
-- So that we can keep the record (for opt-out reasons) even when the
-- email address has been removed from the users table
    id INTEGER NOT NULL PRIMARY KEY,
    hash TEXT NOT NULL,

static $default_config = [
	'CACHE_ROOT'            => DATA_ROOT . '/cache',
	'SHARED_CACHE_ROOT'     => DATA_ROOT . '/cache/shared',
	'DB_FILE'               => DATA_ROOT . '/association.sqlite',
	'DB_SCHEMA'             => ROOT . '/include/data/schema.sql',
	'PLUGINS_ROOT'          => DATA_ROOT . '/plugins',
	'PREFER_HTTPS'          => false,
	'ALLOW_MODIFIED_IMPORT' => true,
	'SHOW_ERRORS'           => true,
	'MAIL_ERRORS'           => false,
	'ERRORS_REPORT_URL'     => null,
	'ENABLE_TECH_DETAILS'   => true,
	'ENABLE_UPGRADES'       => true,
	'USE_CRON'              => false,

	'API_USER'              => null,
	'API_PASSWORD'          => null,
	'PDF_COMMAND'           => null,
	'CALC_CONVERT_COMMAND'  => null,
	'CONTRIBUTOR_LICENSE'   => null,
	'SQL_DEBUG'             => null,
	'SYSTEM_SIGNALS'        => [],

];

foreach ($default_config as $const => $value)
{
	$const = sprintf('Garradin\\%s', $const);

	if (!defined($const))

if (ERRORS_REPORT_URL)
{
	ErrorManager::setRemoteReporting(ERRORS_REPORT_URL, true);
}

ErrorManager::setProductionErrorTemplate(defined('Garradin\ERRORS_TEMPLATE') && ERRORS_TEMPLATE ? ERRORS_TEMPLATE : '<!DOCTYPE html><html><head><title>Erreur interne</title>
	<style type="text/css">
	body {font-family: sans-serif; }
	code, p, h1 { max-width: 400px; margin: 1em auto; display: block; }
	code { text-align: right; color: #666; }
	a { color: blue; }
	form { text-align: center; }
	</style></head><body><h1>Erreur interne</h1><p>Désolé mais le serveur a rencontré une erreur interne
	et ne peut répondre à votre requête. Merci de ré-essayer plus tard.</p>
	<p>Si vous suspectez un bug dans Garradin, vous pouvez suivre 
	<a href="https://fossil.kd2.org/garradin/wiki?name=Rapporter+un+bug&p">ces instructions</a>
	pour le rapporter.</p>
	<if(sent)><p>Un-e responsable a été notifié-e et cette erreur sera corrigée dès que possible.</p></if>
	<if(logged)><code>L\'erreur a été enregistrée dans les journaux système (error.log) sous la référence : <b>{$ref}</b></code></if>
	<p><a href="' . WWW_URL . '">&larr; Retour à la page d\'accueil</a></p>
	</body></html>');

ErrorManager::setHtmlHeader('<!DOCTYPE html><meta charset="utf-8" /><style type="text/css">
	body { font-family: sans-serif; } * { margin: 0; padding: 0; }
	u, code b, i, h3 { font-style: normal; font-weight: normal; text-decoration: none; }
	#icn { color: #fff; font-size: 2em; float: right; margin: 1em; padding: 1em; background: #900; border-radius: 50%; }
	section header { background: #fdd; padding: 1em; }
	section article { margin: 1em; }
	section article h3, section article h4 { font-size: 1em; font-family: mono; }
	code { border: 1px dotted #ccc; display: block; }
	code b { margin-right: 1em; color: #999; }

{
	if (PHP_SAPI == 'cli')
	{
		echo $e->getMessage();
	}
	else
	{

		$tpl = Template::getInstance();


		$tpl->assign('error', $e->getMessage());
		$tpl->assign('html_error', $e->getHTMLMessage());
		$tpl->assign('admin_url', ADMIN_URL);
		$tpl->display('error.tpl');
	}

	exit;
}

// Message d'erreur simple pour les erreurs de l'utilisateur
ErrorManager::setCustomExceptionHandler('\Garradin\UserException', '\Garradin\user_error');

<?php

namespace Garradin\Accounting;

use Garradin\Entities\Accounting\Account;
use Garradin\Entities\Accounting\Line;
use Garradin\Entities\Accounting\Transaction;
use Garradin\Entities\Accounting\Year;
use Garradin\Config;
use Garradin\CSV;
use Garradin\DB;
use Garradin\DynamicList;
use Garradin\Utils;
use Garradin\UserException;
use Garradin\ValidationException;
use KD2\DB\EntityManager;

	public function getClosingAccountId()
	{
		return DB::getInstance()->firstColumn('SELECT id FROM acc_accounts WHERE type = ? AND id_chart = ?;', Account::TYPE_CLOSING, $this->chart_id);
	}

	public function listUserAccounts(int $year_id): DynamicList
	{
		$id_field = Config::getInstance()->champ_identite;

		$columns = [
			'id' => [
				'select' => 'u.id',
			],
			'user_number' => [
				'select' => 'u.numero',
				'label' => 'N° membre',
			],
			'user_identity' => [
				'select' => 'u.' . $id_field,
				'label' => 'Membre',
			],
			'balance' => [
				'select' => 'SUM(l.debit - l.credit)',
				'label'  => 'Solde',
				//'order'  => 'balance != 0 %s, balance < 0 %1$s',
			],
			'status' => [
				'select' => null,
				'label' => 'Statut',
			],
		];

		$tables = 'acc_transactions_users tu
			INNER JOIN membres u ON u.id = tu.id_user
			INNER JOIN acc_transactions t ON tu.id_transaction = t.id
			INNER JOIN acc_transactions_lines l ON t.id = l.id_transaction
			INNER JOIN acc_accounts a ON a.id = l.id_account';

		$conditions = 'a.type = ' . Account::TYPE_THIRD_PARTY . ' AND t.id_year = ' . $year_id;

		$list = new DynamicList($columns, $tables, $conditions);

		$code = strtoupper(substr($chart_code, 3));

		if (DB::getInstance()->test(Chart::TABLE, 'country = ? AND code = ?', $country, $code)) {
			throw new \RuntimeException('Ce plan comptable est déjà installé');
		}

		$chart = new Chart;
        $chart->label = self::BUNDLED_CHARTS[$chart_code];
        $chart->country = $country;
        $chart->code = $code;
        $chart->save();
        $chart->accounts()->importCSV($file);
        return $chart;
	}

	static public function listInstallable(): array
	{
		$installed = DB::getInstance()->getAssoc('SELECT id, LOWER(country || \'_\' || code) FROM acc_charts;');
		$out = [];

		return $out;
	}

	static protected function getColors()
	{
		$config = Config::getInstance();
		$c1 = $config->get('couleur1') ?: ADMIN_COLOR1;
		$c2 = $config->get('couleur2') ?: ADMIN_COLOR2;
		list($h, $s, $v) = Utils::rgbToHsv($c1);
		list($h1, $s, $v) = Utils::rgbToHsv($c2);

		$colors = [];

		for ($i = 0; $i < 6; $i++) {
			if ($i % 2 == 0) {

		if (!array_key_exists($type, self::EXPORT_COLUMNS)) {
			throw new \InvalidArgumentException('Unknown type: ' . $type);
		}

		CSV::export(
			$format,
			sprintf('Export comptable %s - %s - %s', strtolower(self::EXPORT_NAMES[$type]), Config::getInstance()->get('nom_asso'), $year->label),
			self::iterateExport($year->id(), $type),
			array_values(self::EXPORT_COLUMNS[$type])
		);
	}

	static public function getExportExamples(Year $year)
	{

		}

		return $out;
	}

	static protected function iterateExport(int $year_id, string $type): \Generator
	{
		$id_field = Config::getInstance()->get('champ_identite');

		if (self::EXPORT_SIMPLE == $type) {
			$sql =  'SELECT t.id, t.type, t.status, t.label, t.date, t.notes, t.reference,
				l1.reference AS p_reference,
				a1.code AS debit_account,
				a2.code AS credit_account,
				l1.debit AS amount,
				a3.code AS analytical,
				GROUP_CONCAT(u.%s) AS linked_users
				FROM acc_transactions t
				INNER JOIN acc_transactions_lines l1 ON l1.id_transaction = t.id AND l1.debit != 0
				INNER JOIN acc_transactions_lines l2 ON l2.id_transaction = t.id AND l2.credit != 0
				INNER JOIN acc_accounts a1 ON a1.id = l1.id_account
				INNER JOIN acc_accounts a2 ON a2.id = l2.id_account
				LEFT JOIN acc_accounts a3 ON a3.id = l1.id_analytical
				LEFT JOIN acc_transactions_users tu ON tu.id_transaction = t.id
				LEFT JOIN membres u ON u.id = tu.id_user
				WHERE t.id_year = ?
					AND t.type != %d
				GROUP BY t.id
				ORDER BY t.date, t.id;';

			$sql = sprintf($sql, $id_field, Transaction::TYPE_ADVANCED);
		}
		else {
			$sql = 'SELECT t.id, t.type, t.status, t.label, t.date, t.notes, t.reference,
				l.id AS line_id, a.code AS account, l.debit AS debit, l.credit AS credit,
				l.reference AS line_reference, l.label AS line_label, l.reconciled,
				a2.code AS analytical,
				GROUP_CONCAT(u.%s) AS linked_users
				FROM acc_transactions t
				INNER JOIN acc_transactions_lines l ON l.id_transaction = t.id
				INNER JOIN acc_accounts a ON a.id = l.id_account
				LEFT JOIN acc_accounts a2 ON a2.id = l.id_analytical
				LEFT JOIN acc_transactions_users tu ON tu.id_transaction = t.id
				LEFT JOIN membres u ON u.id = tu.id_user
				WHERE t.id_year = ?
				GROUP BY t.id, l.id
				ORDER BY t.date, t.id, l.id;';

			$sql = sprintf($sql, $id_field);
		}

<?php

namespace Garradin;

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

use KD2\SMTP;
use KD2\Graphics\Image;

class Config extends Entity
{
	const FILES = [

		'admin_css'        => 'code',
		'admin_homepage'   => 'web',
		'logo'             => 'image',
		'icon'             => 'image',
		'favicon'          => 'image',
	];

	protected $nom_asso;
	protected $adresse_asso;
	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 $last_chart_change;
	protected $last_version_check;

	protected $couleur1;
	protected $couleur2;

	protected $files = [];

	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',

		'last_chart_change'     => '?int',
		'last_version_check'    => '?string',

		'couleur1'              => '?string',
		'couleur2'              => '?string',

		'files'                 => 'array',

		'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;
		}

		$this->selfCheck();

		$values = $this->modifiedProperties(true);

		$db = DB::getInstance();
		$db->begin();

		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();

		if (isset($values['couleur1']) || isset($values['couleur2'])) {
			// Reset graph cache
			Static_Cache::clean(0);
		}

		$this->_modified = [];

		return true;
	}

	public function delete(): bool
	{
		throw new \LogicException('Cannot delete config');
	}

	public function importForm($source = null): void
	{
		if (null === $source) {
			$source = $_POST;
		}

		// N'enregistrer les couleurs que si ce ne sont pas les couleurs par défaut
		if (isset($source['couleur1'], $source['couleur2'])
			&& ($source['couleur1'] == ADMIN_COLOR1 && $source['couleur2'] == ADMIN_COLOR2))
		{
			$source['couleur1'] = null;
			$source['couleur2'] = null;
		}

		parent::importForm($source);
	}

	protected function _filterType(string $key, $value)
	{
		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
		$this->assert(count($this->files) == count(self::FILES));

		foreach ($this->files as $key => $value) {
			$this->assert(array_key_exists($key, self::FILES));
			$this->assert(is_int($value) || is_null($value));
		}

		$champs = $this->champs_membres;

		$this->assert(!empty($champs->get($this->champ_identite)), sprintf('Le champ spécifié pour identité, "%s" n\'existe pas', $this->champ_identite));
		$this->assert(!empty($champs->get($this->champ_identifiant)), sprintf('Le champ spécifié pour identifiant, "%s" n\'existe pas', $this->champ_identifiant));

		$db = DB::getInstance();

		// Check that this field is actually unique
		if (isset($this->_modified['champ_identifiant'])) {
			$sql = sprintf('SELECT (COUNT(DISTINCT %s COLLATE U_NOCASE) = COUNT(*)) FROM membres WHERE %1$s IS NOT NULL AND %1$s != \'\';', $this->champ_identifiant);
			$is_unique = (bool) $db->firstColumn($sql);

			$this->assert($is_unique, sprintf('Le champ "%s" comporte des doublons et ne peut donc pas servir comme identifiant unique de connexion.', $this->champ_identifiant));
		}

		$this->assert($db->test('users_categories', 'id = ?', $this->categorie_membres), 'Catégorie de membres inconnue');
	}

	public function file(string $key): ?File
	{
		if (!isset(self::FILES[$key])) {
			throw new \InvalidArgumentException('Invalid file key: ' . $key);
		}

        self::registerCustomFunctions($this->db);
    }

    static public function registerCustomFunctions($db)
    {
        $db->createFunction('dirname', [Utils::class, 'dirname']);
        $db->createFunction('basename', [Utils::class, 'basename']);
        $db->createFunction('like', [self::class, 'unicodeLike']);

        $db->createFunction('email_hash', [Entities\Users\Email::class, 'getHash']);
        $db->createCollation('U_NOCASE', [Utils::class, 'unicodeCaseComparison']);
    }

    public function version(): ?string
    {
        if (-1 === $this->_version) {

			$columns[$alias] = $label_only ? $properties['label'] : $properties;
		}

		return $columns;
	}

	public function iterate(bool $include_hidden = true)

















	{
		$start = ($this->page - 1) * $this->per_page;
		$columns = [];

		foreach ($this->columns as $alias => $properties) {
			// Skip columns that require a certain order (eg. calculating a running sum)
			if (isset($properties['only_with_order']) && !($properties['only_with_order'] == $this->order && !$this->desc)) {

		$sql = sprintf('SELECT %s FROM %s WHERE %s %s ORDER BY %s',
			$columns, $this->tables, $this->conditions, $group, $order);

		if (null !== $this->per_page) {
			$sql .= sprintf(' LIMIT %d,%d', $start, $this->per_page);
		}

		foreach (DB::getInstance()->iterate($sql) as $row) {
			if ($this->modifier) {
				call_user_func_array($this->modifier, [&$row]);
			}

			foreach ($this->columns as $key => $config) {
				if (empty($config['label']) && !$include_hidden) {
					unset($row->$key);
				}
			}

			yield $row;
		}
	}

	public function loadFromQueryString()
	{









		if (!empty($_GET['export'])) {



			$this->export($this->title, $_GET['export']);
			exit;
		}

		if (!empty($_GET['o'])) {
			$this->orderBy($_GET['o'], !empty($_GET['d']));
		}

		if (!empty($_GET['p'])) {
			$this->page = (int)$_GET['p'];
		}
	}
}

			yield $row;
		}

		if (!$only_non_reconciled) {
			yield (object) ['sum' => $sum, 'reconciled_sum' => $reconciled_sum, 'date' => $end_date];
		}
	}




















































































































	public function getDepositJournal(int $year_id, array $checked = []): \Generator
	{
		$res = DB::getInstance()->iterate('SELECT l.debit, l.credit, t.id, t.date, t.reference, l.reference AS line_reference, t.label, l.label AS line_label, l.reconciled, l.id AS id_line, l.id_account
			FROM acc_transactions_lines l
			INNER JOIN acc_transactions t ON t.id = l.id_transaction
			WHERE t.id_year = ? AND l.id_account = ? AND l.credit = 0 AND NOT (t.status & ?)

<?php

namespace Garradin\Entities\Accounting;

use KD2\DB\EntityManager;

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

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

use Garradin\Accounting\Accounts;

		return [
			$type->accounts[0]->position == 'credit' ? $credit : $debit,
			$type->accounts[1]->position == 'credit' ? $credit : $debit,
		];
	}

	/**duplic
	 * Creates a new Transaction entity (not saved) from an existing one,
	 * trying to adapt to a different chart if possible
	 * @param  int    $id
	 * @param  Year   $year Target year
	 * @return Transaction
	 */
	public function duplicate(Year $year): Transaction

		}

		// Only set date if valid
		if ($this->date >= $year->start_date && $this->date <= $year->end_date) {
			$new->date = clone $this->date;
		}

		$new->status = 0;

		return $new;
	}

	public function payment_reference(): ?string
	{
		$line = current($this->getLines());

	public function selfCheck(): void
	{
		parent::selfCheck();
		$db = DB::getInstance();

		$this->assert(null !== $this->id_year, 'Aucun exercice spécifié.');
		$this->assert(array_key_exists($this->type, self::TYPES_NAMES), 'Type d\'écriture inconnu : ' . $this->type);
		$this->assert(null === $this->id_creator || $db->test('membres', 'id = ?', $this->id_creator), 'Le membre créateur de l\'écriture n\'existe pas ou plus');

		$is_in_year = $db->test(Year::TABLE, 'id = ? AND start_date <= ? AND end_date >= ?', $this->id_year, $this->date->format('Y-m-d'), $this->date->format('Y-m-d'));

		$this->assert($is_in_year, 'La date ne correspond pas à l\'exercice sélectionné : ' . $this->date->format('d/m/Y'));

		$total = 0;

			$accounts_ids = [$line->id_account];
			$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');

		$found_accounts = $db->getAssoc(sprintf('SELECT id, id FROM acc_accounts WHERE %s AND id_chart = (SELECT id_chart FROM acc_years WHERE id = %d);', $db->where('id', $accounts_ids), $this->id_year));

		$diff = array_diff($accounts_ids, $found_accounts);
		$this->assert(count($diff) == 0, sprintf('Certains comptes (%s) ne sont pas liés au bon plan comptable', implode(', ', $diff)));

		$this->assert(!$this->id_related || $db->test('acc_transactions', 'id = ?', $this->id_related), 'L\'écriture liée indiquée n\'existe pas');

		}

		if ($debit != $credit) {
			// Add final balance line
			$line = new Line;

			if ($debit > $credit) {
				$line->credit = $debit - $credit;
			}
			else {
				$line->debit = $credit - $debit;
			}

			$open_account = EntityManager::findOne(Account::class, 'SELECT * FROM @TABLE WHERE id_chart = ? AND type = ? LIMIT 1;', $year->id_chart, Account::TYPE_OPENING);

			if (!$open_account) {
				throw new ValidationException('Aucun compte favori de bilan d\'ouverture n\'existe dans le plan comptable');
			}

		$db->commit();
	}

	public function listLinkedUsers()
	{
		$db = EntityManager::getInstance(self::class)->DB();
		$identity_column = Config::getInstance()->get('champ_identite');
		$sql = sprintf('SELECT m.id, m.%s AS identity, l.id_service_user FROM membres m INNER JOIN acc_transactions_users l ON l.id_user = m.id WHERE l.id_transaction = ?;', $identity_column);
		return $db->get($sql, $this->id());
	}

	public function listLinkedUsersAssoc()
	{
		$db = EntityManager::getInstance(self::class)->DB();
		$identity_column = Config::getInstance()->get('champ_identite');
		$sql = sprintf('SELECT m.id, m.%s AS identity, l.id_service_user
			FROM membres m
			INNER JOIN acc_transactions_users l ON l.id_user = m.id
			WHERE l.id_transaction = ?;', $identity_column);
		return $db->getAssoc($sql, $this->id());
	}

	public function listRelatedTransactions()
	{
		return EntityManager::getInstance(self::class)->all('SELECT * FROM @TABLE WHERE id_related = ?;', $this->id);

<?php

namespace Garradin\Entities\Files;

use KD2\Graphics\Image;
use KD2\DB\EntityManager as EM;

use Garradin\DB;
use Garradin\Entity;
use Garradin\Plugin;
use Garradin\UserException;
use Garradin\ValidationException;
use Garradin\Membres\Session;
use Garradin\Static_Cache;
use Garradin\Utils;
use Garradin\Entities\Web\Page;
use Garradin\Web\Render\Render;

use Garradin\Files\Files;

<?php

namespace Garradin\Entities\Services;

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

use Garradin\Entities\Accounting\Account;
use Garradin\Entities\Accounting\Year;
use KD2\DB\EntityManager;
use KD2\DB\DB_Exception;

class Fee extends Entity
{

		}

		return null;
	}

	protected function getFormulaSQL()
	{
		return sprintf('SELECT %s FROM membres WHERE id = ?;', $this->formula);
	}

	protected function checkFormula(): ?string
	{
		try {
			$db = DB::getInstance();
			$sql = $this->getFormulaSQL();
			$db->protectSelect(['membres' => null], $sql);
			return null;
		}
		catch (DB_Exception $e) {
			return $e->getMessage();
		}
	}

	public function service()
	{
		return EntityManager::findOneById(Service::class, $this->id_service);
	}

	public function activeUsersList(): DynamicList
	{

		$identity = Config::getInstance()->get('champ_identite');
		$columns = [
			'id_user' => [
				'select' => 'su.id_user',
			],
			'identity' => [
				'label' => 'Membre',
				'select' => 'm.' . $identity,
			],
			'paid' => [
				'label' => 'Payé ?',
				'select' => 'su.paid',
			],
			'paid_amount' => [
				'label' => 'Montant payé',
				'select' => 'SUM(l.credit)',
			],
			'date' => [
				'label' => 'Date',
				'select' => 'su.date',
			],
		];

		$tables = 'services_users su
			INNER JOIN membres m ON m.id = su.id_user
			INNER JOIN services_fees sf ON sf.id = su.id_fee
			INNER JOIN (SELECT id, MAX(date) FROM services_users GROUP BY id_user, id_fee) AS su2 ON su2.id = su.id
			LEFT JOIN acc_transactions_users tu ON tu.id_service_user = su.id
			LEFT JOIN acc_transactions_lines l ON l.id_transaction = tu.id_transaction';
		$conditions = sprintf('su.id_fee = %d AND (su.expiry_date >= date() OR su.expiry_date IS NULL)
			AND m.id_category NOT IN (SELECT id FROM users_categories WHERE hidden = 1)', $this->id());

		$list = new DynamicList($columns, $tables, $conditions);
		$list->groupBy('su.id_user');
		$list->orderBy('date', true);
		$list->setCount('COUNT(DISTINCT su.id_user)');

		$list->setExportCallback(function (&$row) {
			$row->paid_amount = $row->paid_amount ? Utils::money_format($row->paid_amount, '.', '', false) : null;
		});

		return $list;
	}

	public function unpaidUsersList(): DynamicList
	{
		$list = $this->activeUsersList();
		$conditions = sprintf('su.id_fee = %d AND su.paid = 0 AND m.id_category NOT IN (SELECT id FROM users_categories WHERE hidden = 1)', $this->id());
		$list->setConditions($conditions);
		return $list;
	}

	public function expiredUsersList(): DynamicList
	{
		$list = $this->activeUsersList();
		$conditions = sprintf('su.id_fee = %d AND su.expiry_date < date() AND m.id_category NOT IN (SELECT id FROM users_categories WHERE hidden = 1)', $this->id());
		$list->setConditions($conditions);
		return $list;
	}
}

<?php

namespace Garradin\Entities\Users;

use Garradin\Membres\Session;
use Garradin\Config;
use Garradin\DB;
use Garradin\UserException;
use Garradin\Entity;

class Category extends Entity
{

	}

	public function delete(): bool
	{
		$db = DB::getInstance();
		$config = Config::getInstance();

		if ($this->id() == $config->get('categorie_membres')) {
			throw new UserException('Il est interdit de supprimer la catégorie définie par défaut dans la configuration.');
		}

		if ($db->test('membres', 'id_category = ?', $this->id())) {
			throw new UserException('La catégorie contient encore des membres, il n\'est pas possible de la supprimer.');
		}

		return parent::delete();
	}

	public function setAllPermissions(int $access): void

<?php

namespace Garradin\Files;

use Garradin\Static_Cache;
use Garradin\DB;
use Garradin\Utils;
use Garradin\UserException;
use Garradin\ValidationException;
use Garradin\Membres\Session;
use Garradin\Entities\Files\File;
use Garradin\Entities\Web\Page;

use KD2\DB\EntityManager as EM;
use KD2\ZipWriter;

use const Garradin\{FILE_STORAGE_BACKEND, FILE_STORAGE_QUOTA, FILE_STORAGE_CONFIG};

			WHERE files_search MATCH ? %s
			ORDER BY points DESC
			LIMIT 0,50;', $where);

		return DB::getInstance()->get($query, ...$params);
	}






	static public function list(string $parent = ''): array
	{
		if ($parent !== '') {
			File::validatePath($parent);
		}

		// Update this path
		return self::callStorage('list', $parent);
	}
















	static public function zip(string $parent, ?Session $session)
	{
		$file = Files::get($parent);

		if (!$file) {
			throw new UserException('Ce répertoire n\'existe pas.');
		}

		};

		$add_file($parent);

		$zip->close();
	}




	static public function listForContext(string $context, ?string $ref = null)
	{
		$path = $context;

		if ($ref) {
			$path .= '/' . $ref;
		}

		return self::list($path);
	}




	static public function delete(string $path): void
	{
		$file = self::get($path);

		if (!$file) {
			return;
		}

			}

			// Used to make sorting easier
			// directory_blabla
			// file_image.jpeg
			$files[$file->getType() . '_' .$file->getFilename()] = self::_SplToFile($file);
		}




















		return Utils::knatcasesort($files);
	}

	static public function listDirectoriesRecursively(string $path): array
	{
		$fullpath = self::_getRoot() . DIRECTORY_SEPARATOR . str_replace('/', DIRECTORY_SEPARATOR, $path);

	}

	static public function get(string $path): ?File
	{
		$sql = 'SELECT * FROM @TABLE WHERE path = ? LIMIT 1;';
		return EM::findOne(File::class, $sql, $path);
	}






	static public function list(string $path): array
	{
		return EM::getInstance(File::class)->all('SELECT * FROM @TABLE WHERE parent = ? ORDER BY type DESC, name COLLATE U_NOCASE ASC;', $path);
	}

	static public function listDirectoriesRecursively(string $path): array

		$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;
	}
}

<?php

namespace Garradin\Files;

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

class Users
{
	const LIST_COLUMNS = [
		'number' => [
			'select' => 'm.numero',
			'label' => 'Numéro',
		],
		'identity' => [
			'select' => '',
			'label' => '',
		],
		'path' => [
		],
		'id' => [
			'label' => null,
			'select' => 'm.id',
		],
	];

	static public function list()
	{
		Files::syncVirtualTable(File::CONTEXT_USER);

		$config = Config::getInstance();
		$name_field = $config->get('champ_identite');
		$champs = $config->get('champs_membres');

		$columns = self::LIST_COLUMNS;
		$columns['identity']['select'] = 'm.' . $name_field;
		$columns['identity']['label'] = $champs->get($name_field)->title;


		$tables = sprintf('%s f INNER JOIN membres m ON m.id = f.name', 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_USER, File::TYPE_DIRECTORY);

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

		return $list;
	}
}

		}
		catch (UserException $e) {
			$this->addError($e);
			return false;
		}
	}

	public function runIf($condition, callable $fn, ?string $csrf_key = null, ?string $redirect = null): ?bool
	{
		if (is_string($condition) && empty($_POST[$condition])) {
			return null;
		}
		elseif (is_bool($condition) && !$condition) {
			return null;
		}

		return $this->run($fn, $csrf_key, $redirect);
	}

	public function check($token_action = '', Array $rules = null)
	{
		if (!\KD2\Form::tokenCheck($token_action))
		{
			$this->errors[] = 'Une erreur est survenue, merci de bien vouloir renvoyer le formulaire.';

<?php

namespace Garradin;

use Garradin\Accounting\Charts;
use Garradin\Entities\Accounting\Account;
use Garradin\Entities\Accounting\Year;
use Garradin\Entities\Users\Category;
use Garradin\Entities\Files\File;
use Garradin\Membres\Session;

/**
 * Pour procéder à l'installation de l'instance Garradin
 * Utile pour automatiser l'installation sans passer par la page d'installation
 */
class Install
{
	/**
	 * Reset the database to empty and create a new user with the same password
	 */
	static public function reset(Session $session, string $password, array $options = [])
	{
		$config = (object) Config::getInstance()->asArray();
		$user = $session->getUser();

		if (!$session->checkPassword($password, $user->passe))
		{
			throw new UserException('Le mot de passe ne correspond pas.');
		}

		if (!trim($config->nom_asso)) {
			throw new UserException('Le nom de l\'association est vide, merci de le renseigner dans la configuration.');
		}

		if (!trim($user->identite)) {
			throw new UserException('L\'utilisateur connecté ne dispose pas de nom, merci de le renseigner.');
		}

		DB::getInstance()->close();
		DB::deleteInstance();

		file_put_contents(CACHE_ROOT . '/reset', json_encode([
			'password'     => $session::hashPassword($password),
			'name'         => $user->identite,
			'email'        => $user->email,
			'organization' => $config->nom_asso,
		]));

		rename(DB_FILE, sprintf(DATA_ROOT . '/association.%s.sqlite', date('Y-m-d-His-') . 'avant-remise-a-zero'));

		self::showProgressSpinner('!install.php', 'Remise à zéro en cours…');
		exit;
	}

			throw new \LogicException('Invalid reset data');
		}

		// We can't use the real password, as it might not be valid (too short or compromised)
		$ok = self::install($data->organization ?? 'Association', $data->name, $data->email, md5($data->password));

		// Restore password
		DB::getInstance()->preparedQuery('UPDATE membres SET passe = ? WHERE id = 1;', [$data->password]);

		if (defined('\Garradin\LOCAL_LOGIN') && \Garradin\LOCAL_LOGIN) {
			Session::getInstance()->refresh();
		}

		@unlink(CACHE_ROOT . '/reset');

		$db->commit();

		file_put_contents(SHARED_CACHE_ROOT . '/version', garradin_version());

		// Configuration de base
		// 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');



		// Create default category for common users
		$cat = new Category;
		$cat->setAllPermissions(Session::ACCESS_NONE);
		$cat->importForm([
			'name' => 'Membres actifs',
			'perm_connect' => Session::ACCESS_READ,
		]);
		$cat->save();

		$config->set('categorie_membres', $cat->id());

		// Create default category for ancient users
		$cat = new Category;
		$cat->importForm([
			'name' => 'Anciens membres',
			'hidden' => 1,
		]);

		}
		.spinner h2::after {
			display: block;
			content: " ";
			margin: 1rem auto;
			width: 50px;
			height: 50px;
			border: 5px solid #000;
			border-radius: 50%%;
			border-top-color: #999;
			animation: spin 1s ease-in-out infinite;
		}

		@keyframes spin { to { transform: rotate(360deg); } }
		</style>
		%s
		</head>
		<body>
		<div class="spinner">
			<h2>%s</h2>
		</div>', $next, htmlspecialchars($message));
	}

}

<?php

namespace Garradin;

use KD2\Security;
use KD2\SMTP;
use Garradin\Membres\Session;

use Garradin\Files\Files;
use Garradin\Entities\Files\File;
use Garradin\Entities\Users\Email;

use Garradin\Users\Emails;
use Garradin\UserTemplate\UserTemplate;

        $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 U_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));
    }
}

<?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'	=>	'Nombre',
		'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
    {
        $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'
        ];

        $last_one = array_key_last((array)$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 U_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(bool $same = false): array
    {
        // 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 = $same ? null : Config::getInstance()->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, string $id_field = null): void
    {
        $db = DB::getInstance();
        $id_field ??= Config::getInstance()->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 U_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 U_NOCASE';
            }

            $db->exec(sprintf('CREATE INDEX IF NOT EXISTS users_list_%s ON %s (id_category, %s%s);', $field, $table_name, $db->quoteIdentifier($field), $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;
    }
}

<?php

namespace Garradin\Membres;

use Garradin\Config;
use Garradin\DB;
use Garradin\Utils;
use Garradin\Membres;
use Garradin\UserException;
use Garradin\Plugin;
use Garradin\Users\Emails;

use const Garradin\SECRET_KEY;
use const Garradin\WWW_URL;
use const Garradin\ADMIN_URL;

use KD2\Security;
use KD2\Security_OTP;
use KD2\Graphics\QRCode;
use KD2\HTTP;

class Session extends \KD2\UserSession
{
	const SECTION_WEB = 'web';
	const SECTION_DOCUMENTS = 'documents';
	const SECTION_USERS = 'users';
	const SECTION_ACCOUNTING = 'accounting';
	const SECTION_CONNECT = 'connect';
	const SECTION_CONFIG = 'config';
	const SECTION_SUBSCRIBE = 'subscribe';

	const ACCESS_NONE = 0;
	const ACCESS_READ = 1;
	const ACCESS_WRITE = 2;
	const ACCESS_ADMIN = 9;

	// Personalisation de la config de UserSession
	protected $cookie_name = 'gdin';
	protected $remember_me_cookie_name = 'gdinp';
	protected $remember_me_expiry = '+3 months';

	const MINIMUM_PASSWORD_LENGTH = 8;

	static protected $_instance = null;

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

	public function __clone()
	{
		throw new \LogicException('Cannot clone');
	}

	public function __construct()
	{
		if (self::$_instance !== null) {
			throw new \LogicException('Wrong call, use getInstance');
		}

		$url = parse_url(ADMIN_URL);

		parent::__construct(DB::getInstance(), [
			'cookie_domain' => $url['host'],
			'cookie_path'   => preg_replace('!/admin/$!', '/', $url['path']),
			'cookie_secure' => (\Garradin\PREFER_HTTPS >= 2) ? true : false,
		]);
	}

	static public function checkPasswordValidity($password)
	{
		if (strlen($password) < self::MINIMUM_PASSWORD_LENGTH)
		{
			throw new UserException(sprintf('Le mot de passe doit faire au moins %d caractères.', self::MINIMUM_PASSWORD_LENGTH));
		}

		$session = self::getInstance();
		$session->http = new HTTP;

		if ($session->isPasswordCompromised($password)) {
			throw new UserException('Ce mot de passe figure dans une liste de mots de passe compromis, il ne peut donc être utilisé ici. Si vous l\'avez utilisé sur d\'autres sites il est recommandé de le changer sur ces autres sites également.');
		}
	}

	public function isPasswordCompromised($password)
	{
		// Vérifier s'il n'y a pas un plugin qui gère déjà cet aspect
		// notamment en installation mutualisée c'est plus efficace
		$return = ['is_compromised' => null];
		$called = Plugin::fireSignal('motdepasse.compromis', ['password' => $password], $return);

		if ($called !== null) {
			return $return['is_compromised'];
		}

		return parent::isPasswordCompromised($password);
	}

	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 U_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 getUserDataForSession($id)
	{
		// Mettre à jour la date de connexion
		$this->db->preparedQuery('UPDATE membres SET date_connexion = datetime() WHERE id = ?;', [$id]);
		$config = Config::getInstance();

		return $this->db->first('SELECT m.*, m.'.$config->get('champ_identite').' AS identite,
			c.perm_connect, c.perm_web, c.perm_users, c.perm_documents,
			c.perm_subscribe, c.perm_accounting, c.perm_config
			FROM membres AS m
			INNER JOIN users_categories AS c ON m.id_category = c.id
			WHERE m.id = ? LIMIT 1;', $id);
	}

	protected function storeRememberMeSelector($selector, $hash, $expiry, $user_id)
	{
		return $this->db->insert('membres_sessions', [
			'selecteur' => $selector,
			'hash'      => $hash,
			'expire'    => $expiry,
			'id_membre' => $user_id,
		]);
	}

	protected function expireRememberMeSelectors()
	{
		return $this->db->delete('membres_sessions', $this->db->where('expire', '<', time()));
	}

	protected function getRememberMeSelector($selector)
	{
		return $this->db->first('SELECT selecteur AS selector, hash,
			s.id_membre AS user_id, m.passe AS user_password, expire AS expiry
			FROM membres_sessions AS s
			INNER JOIN membres AS m ON m.id = s.id_membre
			WHERE s.selecteur = ? LIMIT 1;', $selector);
	}

	protected function deleteRememberMeSelector($selector)
	{
		return $this->db->delete('membres_sessions', $this->db->where('selecteur', $selector));
	}

	protected function deleteAllRememberMeSelectors($user_id)
	{
		return $this->db->delete('membres_sessions', $this->db->where('id_membre', $user_id));
	}

	public function isLogged(bool $disable_local_login = false)
	{
		$logged = parent::isLogged();

		// Ajout de la gestion de LOCAL_LOGIN
		if (!$disable_local_login && defined('\Garradin\LOCAL_LOGIN')) {
			$logged = $this->forceLogin(\Garradin\LOCAL_LOGIN);
		}

		return $logged;
	}

	public function forceLogin(int $id)
	{
		// On va chercher le premier membre avec le droit de gérer la config
		if (-1 === $id) {
			$id = $this->db->firstColumn('SELECT id FROM membres
				WHERE id_category IN (SELECT id FROM users_categories WHERE perm_config = ?)
				LIMIT 1', self::ACCESS_ADMIN);
		}

		$logged = parent::isLogged();

		// Only login if required
		if ($id > 0 && (!$logged || ($logged && $this->user->id != $id))) {
			return $this->create($id);
		}

		return $logged;
	}

	// Ici checkOTP utilise NTP en second recours
	public function checkOTP($secret, $code)
	{
		if (Security_OTP::TOTP($secret, $code))
		{
			return true;
		}

		// Vérifier encore, mais avec le temps NTP
		// au cas où l'horloge du serveur n'est pas à l'heure
		if (\Garradin\NTP_SERVER
			&& ($time = Security_OTP::getTimeFromNTP(\Garradin\NTP_SERVER))
			&& Security_OTP::TOTP($secret, $code, $time))
		{
			return true;
		}

		return false;
	}

	public function getOTPSecret($secret = null)
	{
		if (!$secret)
		{
			$secret = Security_OTP::getRandomSecret();
		}

		$out = [];
		$out['secret'] = $secret;
		$out['secret_display'] = implode(' ', str_split($secret, 4));
		$out['url'] = Security_OTP::getOTPAuthURL(Config::getInstance()->get('nom_asso'), $secret);

		$qrcode = new QRCode($out['url']);
		$out['qrcode'] = 'data:image/svg+xml;base64,' . base64_encode($qrcode->toSVG());

		return $out;
	}

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

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

		$membre = $db->first('SELECT id, email, passe, clef_pgp FROM membres WHERE '.$champ_id.' = ? COLLATE U_NOCASE LIMIT 1;', trim($id));

		if (!$membre || trim($membre->email) == '')
		{
			return false;
		}

		// valide pour 1 heure minimum
		$expire = ceil((time() - strtotime('2017-01-01')) / 3600) + 1;

		$hash = hash_hmac('sha256', $membre->email . $membre->id . $membre->passe . $expire, SECRET_KEY, true);
		$hash = substr(Security::base64_encode_url_safe($hash), 0, 16);

		$id = base_convert($membre->id, 10, 36);
		$expire = base_convert($expire, 10, 36);

		$query = sprintf('%s.%s.%s', $id, $expire, $hash);

		$message = "Bonjour,\n\nVous avez oublié votre mot de passe ? Pas de panique !\n\n";
		$message.= "Il vous suffit de cliquer sur le lien ci-dessous pour modifier votre mot de passe.\n\n";
		$message.= ADMIN_URL . 'password.php?c=' . $query;
		$message.= "\n\nSi vous n'avez pas demandé à recevoir ce message, ignorez-le, votre mot de passe restera inchangé.";

		if ($membre->clef_pgp) {
			$content = Security::encryptWithPublicKey($membre->clef_pgp, $message);
		}

		Emails::queue(Emails::CONTEXT_SYSTEM, [$membre->email => null], null, 'Mot de passe perdu ?', $message);
		return true;
	}

	public function recoverPasswordCheck($code, &$membre = null)
	{
		if (substr_count($code, '.') !== 2)
		{
			return false;
		}

		list($id, $expire, $email_hash) = explode('.', $code);

		$config = Config::getInstance();
		$db = DB::getInstance();

		$id = base_convert($id, 36, 10);
		$expire = base_convert($expire, 36, 10);

		$expire_timestamp = ($expire * 3600) + strtotime('2017-01-01');

		if (time() / 3600 > $expire_timestamp)
		{
			return false;
		}

		$membre = $db->first('SELECT id, email, passe, clef_pgp FROM membres WHERE id = ? LIMIT 1;', (int)$id);

		if (!$membre || trim($membre->email) == '')
		{
			return false;
		}

		$hash = hash_hmac('sha256', $membre->email . $membre->id . $membre->passe . $expire, SECRET_KEY, true);
		$hash = substr(Security::base64_encode_url_safe($hash), 0, 16);

		if (!hash_equals($hash, $email_hash))
		{
			return false;
		}

		return true;
	}

	public function recoverPasswordChange($code, $password, $password_confirm)
	{
		if (!$this->recoverPasswordCheck($code, $membre))
		{
			throw new UserException('Le code permettant de changer le mot de passe a expiré. Merci de bien vouloir recommencer la procédure.');
		}

		$password = trim($password);
		$password_confirm = trim($password_confirm);

		if (!hash_equals($password, $password_confirm))
		{
			throw new UserException('Le mot de passe et sa vérification ne sont pas identiques.');
		}

		self::checkPasswordValidity($password);

		$password = self::hashPassword($password);

		$message = "Bonjour,\n\nLe mot de passe de votre compte a bien été modifié.\n\n";
		$message.= "Votre adresse email : ".$membre->email."\n";
		$message.= "La demande émanait de l'adresse IP : ".Utils::getIP()."\n\n";
		$message.= "Si vous n'avez pas demandé à changer votre mot de passe, merci de nous le signaler.";

		DB::getInstance()->update('membres', ['passe' => $password], 'id = :id', ['id' => (int)$membre->id]);

		return Emails::queue(Emails::CONTEXT_SYSTEM, [$membre->email => null], null, 'Mot de passe changé', $message);
	}

	public function editUser($data)
	{
		(new Membres)->edit($this->user->id, $data, false);
		$this->refresh();

		return true;
	}

	public function getUser()
	{
		$user = parent::getUser();

		// Force refresh of session when it's too old (FIXME: remove at version 1.2+)
		if (!property_exists($this->user, 'perm_users')) {
			$this->refresh();
			$user = $this->getUser();
		}

		return $user;
	}

	public function canAccess($category, $permission)
	{
		if (!$this->getUser())
		{
			return false;
		}

		$perm_name = 'perm_' . $category;
		$perm = $this->getUser()->$perm_name;

		return ($perm >= $permission);
	}

	public function requireAccess($category, $permission)
	{
		if (!$this->canAccess($category, $permission))
		{
			throw new UserException('Vous n\'avez pas le droit d\'accéder à cette page.');
		}
	}

	public function getNewOTPSecret()
	{
		$out = [];
		$out['secret'] = Security_OTP::getRandomSecret();
		$out['secret_display'] = implode(' ', str_split($out['secret'], 4));
		$out['url'] = Security_OTP::getOTPAuthURL(Config::getInstance()->get('nom_asso'), $out['secret']);

		$qrcode = new QRCode($out['url']);
		$out['qrcode'] = 'data:image/svg+xml;base64,' . base64_encode($qrcode->toSVG());

		return $out;
	}

	public function sendMessage($dest, $sujet, $message, $copie = false)
	{
		$user = $this->getUser();

		$content = "Ce message vous a été envoyé par :\n";
		$content.= sprintf("%s\n%s\n\n", $user->identite, $user->email);
		$content.= str_repeat('=', 70) . "\n\n";
		$content.= $message;

		$dest = $copie ? [$dest => null, $user->email => null] : [$dest => null];

		return Emails::queue(Emails::CONTEXT_PRIVATE, $dest, null, $sujet, $content);
	}

	public function editSecurity(Array $data = [])
	{
		$allowed_fields = ['passe', 'clef_pgp', 'secret_otp'];

		foreach ($data as $key=>$value)
		{
			if (!in_array($key, $allowed_fields))
			{
				throw new \RuntimeException(sprintf('Le champ %s n\'est pas autorisé dans cette méthode.', $key));
			}
		}

		if (isset($data['passe']) && trim($data['passe']) !== '')
		{
			$data['passe'] = trim($data['passe']);

			self::checkPasswordValidity($data['passe']);

			$data['passe'] = self::hashPassword($data['passe']);
		}
		else
		{
			unset($data['passe']);
		}

		if (isset($data['clef_pgp']) && trim($data['clef_pgp']) !== '')
		{
			$data['clef_pgp'] = trim($data['clef_pgp']);

			if (!$this->getPGPFingerprint($data['clef_pgp']))
			{
				throw new UserException('Clé PGP invalide : impossible d\'extraire l\'empreinte.');
			}
		}

		$db = DB::getInstance();
		$db->update('membres', $data, $db->where('id', (int)$this->user->id));
		$this->refresh();

		return true;
	}
}

<?php

namespace Garradin;

use Garradin\Membres\Session;

class Plugin
{
	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',
		'jpg' => 'image/jpeg',
		'jpeg' => '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)
	{
		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;');

		foreach ($plugins as &$row)
		{
			$row->disabled = !self::getPath($row->id, false);
		}

		return $plugins;

	 */
	static public function listMenu(Session $session)
	{
		$list = [];

		// First let plugins handle
		self::fireSignal('menu.item', compact('session'), $list);

		$db = DB::getInstance();
		$plugins = $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}',
			'{$user.droit_compta}' => '{$user.perm_accounting}',
			'{$user.droit_membres}' => '{$user.perm_users}',
			'{$user.droit_config}' => '{$user.perm_config}',
			'{$user.droit_wiki}' => '{$user.perm_documents}',
		];

		$user = $session->getUser();
		$permissions = [
			'{ACCESS_NONE}'  => $session::ACCESS_NONE,
			'{ACCESS_READ}'  => $session::ACCESS_READ,
			'{ACCESS_WRITE}' => $session::ACCESS_WRITE,
			'{ACCESS_ADMIN}' => $session::ACCESS_ADMIN,
		];

		foreach ($plugins as $id => $row)
		{
			if (!self::getPath($row->id, false))
			{
				// Ne pas lister les plugins dont le code a disparu
				unset($list[$id]);
				continue;
			}

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

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

			// FIXME: legacy
			if ($new_condition != $row->menu_condition) {
				$db->update('plugins', ['menu_condition' => $new_condition], 'id = :id', ['id' => $id]);
				$row->menu_condition = $new_condition;
			}

			$condition = strtr($row->menu_condition, $permissions);

			$condition = preg_replace_callback('/\{\$user\.(\w+)\}/', function ($m) use ($user, $db) {
				$prop = $m[1];
				if (!property_exists($user, $prop)) {
					return 'NULL';
				}

				if (substr($prop, 0, 5) == 'perm_') {
					return (int) $user->$prop;
				}

				return $db->quote($user->$prop);
			}, $condition);

			$query = 'SELECT 1 WHERE ' . $condition . ';';

			$res = $db->protectSelect(['membres' => []], $query);

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

		foreach ($plugins as $id => $row) {
			$list['plugin_' . $id] = sprintf('<a href="%s%s/">%s</a>', Utils::getLocalURL('!plugin/'), $id, $row->nom);
		}

		ksort($list);

		return $list;
	}

	/**
	 * Liste les plugins téléchargés mais non installés

		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 stoppé l'exécution
	 */
	static public function fireSignal($signal, $params = null, &$callback_return = null)
	{




		// Process SYSTEM_SIGNALS first
		foreach (SYSTEM_SIGNALS as $system_signal) {
			if (key($system_signal) != $signal) {
				continue;
			}

			if (!is_callable(current($system_signal))) {
				throw new \LogicException(sprintf('System signal: cannot call "%s" for signal "%s"', current($system_signal), key($system_signal)));
			}

			if (true === call_user_func_array(current($system_signal), [&$params, &$callback_return])) {
				return true;
			}
		}

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

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

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

<?php

namespace Garradin;

use Garradin\Entities\Accounting\Transaction;
use Garradin\Accounting\Accounts;
use Garradin\Accounting\Years;

class Recherche
{
	const TYPE_JSON = 'json';
	const TYPE_SQL = 'sql';
	const TYPE_SQL_UNPROTECTED = 'sql_unprotected';

	const TARGETS = [
		'membres',
		'compta',
	];

	protected function _checkFields($data)
	{
		$db = DB::getInstance();

		if (array_key_exists('intitule', $data) && trim($data['intitule']) === '')
		{
			throw new UserException('Le champ intitulé ne peut être vide.');
		}

		if (array_key_exists('id_membre', $data) && null !== $data['id_membre'])
		{
			$data['id_membre'] = (int)$data['id_membre'];
		}

		if (array_key_exists('id_membre', $data) && null !== $data['id_membre'] && !$db->test('membres', 'id = ?', $data['id_membre']))
		{
			throw new \InvalidArgumentException('Numéro d\'utilisateur inconnu.');
		}

		static $types = [self::TYPE_SQL, self::TYPE_JSON, self::TYPE_SQL_UNPROTECTED];

		if (array_key_exists('type', $data) && !in_array($data['type'], $types))
		{
			throw new \InvalidArgumentException('Type de recherche inconnu.');
		}

		if (array_key_exists('cible', $data) && !in_array($data['cible'], self::TARGETS, true))
		{
			throw new \InvalidArgumentException('Cible de recherche invalide.');
		}

		$query = null;

		if (array_key_exists('type', $data))
		{
			if (empty($data['contenu']))
			{
				throw new UserException('Le contenu ne peut être vide.');
			}

			if ($data['type'] == self::TYPE_SQL && !is_string($data['contenu']))
			{
				throw new \InvalidArgumentException('Recherche invalide pour le type SQL');
			}

			$query = $data['contenu'];

			if ($data['type']  == self::TYPE_JSON)
			{
				if (!is_object($query))
				{
					throw new \InvalidArgumentException('Recherche invalide pour le type JSON');
				}

				$query = json_encode($query);

				if (!json_decode($query))
				{
					throw new \InvalidArgumentException('JSON invalide pour le type JSON');
				}
			}
		}

		return $query;
	}

	public function edit($id, $data)
	{
		$allowed = ['intitule', 'id_membre', 'type', 'cible', 'contenu'];

		// Supprimer les champs qui ne sont pas ceux de la BDD
		$data = array_intersect_key($data, array_flip($allowed));

		$query = $this->_checkFields($data);

		if (isset($data['contenu']))
		{
			$data['contenu'] = $query;
		}

		return DB::getInstance()->update('recherches', $data, 'id = ' . (int)$id);
	}

	public function add($intitule, $id_membre, $type, $cible, $contenu)
	{
		$data = compact('intitule', 'id_membre', 'type', 'cible', 'contenu');
		$data['contenu'] = $this->_checkFields($data);

		$db = DB::getInstance();

		$db->insert('recherches', $data);

		return $db->lastInsertRowId();
	}

	public function remove($id)
	{
		return DB::getInstance()->delete('recherches', 'id = ?', (int) $id);
	}

	public function get($id)
	{
		$r = DB::getInstance()->first('SELECT * FROM recherches WHERE id = ?;', (int) $id);

		if ($r && $r->type == self::TYPE_JSON) {
			$r->query = (object) json_decode($r->contenu, true);
		}

		return $r;
	}

	public function getList($id_membre, $cible)
	{
		return DB::getInstance()->get('SELECT id, type, intitule, type, id_membre FROM recherches 
			WHERE (id_membre IS NULL OR id_membre = ?) AND cible = ?
			ORDER BY intitule;', (int)$id_membre, $cible);
	}

	/**
	 * Lancer une recherche enregistrée
	 */
	public function search($id, array $force_select = null, $no_limit = false)
	{
		$search = $this->get($id);

		if (!$search)
		{
			return false;
		}

		if ($search->type == self::TYPE_JSON)
		{
			$query = $search->query;
			$search->contenu = $this->buildQuery($search->cible, $query->query, $query->order, $query->desc, $no_limit ? 10000 : $query->limit);
		}

		$unprotected = $search->type == self::TYPE_SQL_UNPROTECTED;

		return $this->searchSQL($search->cible, $search->contenu, $force_select, $no_limit, $unprotected);
	}

	public function getResultHeader(string $target, array $result)
	{
		if (!count($result)) {
			return [];
		}

		$out = [];
		$columns = $this->getColumns($target);

		foreach (reset($result) as $key => $v) {
			if (substr($key, 0, 1) == '_') {
				continue;
			}

			$label = null;

			foreach ($columns as $ckey => $config) {
				if ($ckey == $key) {
					$label = $config->label;
					break;
				}
				elseif (isset($config->alias) && $config->alias == $key) {
					$key = $config->alias;
					$label = $config->label;
					break;
				}
			}

			if (!$label) {
				$label = $key;
			}

			$out[$key] = $label;
		}

		return $out;
	}

	public function getDefaultOrder(string $target): string
	{
		if ($target == 'compta') {
			return 't.date';
		}
		else {
			return Config::getInstance()->get('champ_identite');
		}
	}

	public function getDefaultDesc(string $target): bool
	{
		if ($target == 'compta') {
			return true;
		}
		else {
			return false;
		}
	}

	/**
	 * Renvoie la liste des colonnes d'une cible
	 */
	public function getColumns($target)
	{
		$columns = [];
		$db = DB::getInstance();

		if ($target == 'membres')
		{
			$champs = Config::getInstance()->get('champs_membres');

			$columns['id_category'] = (object) [
					'textMatch'=> false,
					'label'    => 'Catégorie',
					'type'     => 'enum',
					'null'     => false,
					'values'   => $db->getAssoc('SELECT id, name FROM users_categories ORDER BY name COLLATE U_NOCASE;'),
				];

			foreach ($champs->getList() as $champ => $config)
			{
				$column = (object) [
					'textMatch'=> $champs->isText($champ),
					'label'    => $config->title,
					'type'     => 'text',
					'null'     => true,
				];

				if ($config->type == 'checkbox')
				{
					$column->type = 'boolean';
				}
				elseif ($config->type == 'select')
				{
					$column->type = 'enum';
					$column->values = array_combine($config->options, $config->options);
				}
				elseif ($config->type == 'multiple')
				{
					$column->type = 'bitwise';
					$column->values = $config->options;
				}
				elseif ($config->type == 'date' || $config->type == 'datetime')
				{
					$column->type = $config->type;
				}
				elseif ($config->type == 'number' || $champ == 'numero')
				{
					$column->type = 'integer';
				}

				if ($config->type == 'tel') {
					$column->originalType = 'tel';
				}

				$columns[$champ] = $column;
			}
		}
		elseif ($target === 'compta') {
			$columns['t.id'] = (object) [
				'textMatch'=> false,
				'label'    => 'Numéro écriture',
				'type'     => 'integer',
				'null'     => false,
				'alias'    => 'transaction_id',
			];

			$columns['t.date'] = (object) [
				'textMatch'=> false,
				'label'    => 'Date',
				'type'     => 'date',
				'null'     => false,
				'alias'    => 'date',
			];

			$columns['t.label'] = (object) [
				'textMatch'=> true,
				'label'    => 'Libellé écriture',
				'type'     => 'text',
				'null'     => false,
				'alias'    => 'label',
			];

			$columns['t.reference'] = (object) [
				'textMatch'=> true,
				'label'    => 'Numéro pièce comptable',
				'type'     => 'text',
				'null'     => true,
				'alias'    => 'reference',
			];

			$columns['t.notes'] = (object) [
				'textMatch'=> true,
				'label'    => 'Remarques',
				'type'     => 'text',
				'null'     => true,
				'alias'    => 'notes',
			];

			$columns['l.label'] = (object) [
				'textMatch'=> true,
				'label'    => 'Libellé ligne',
				'type'     => 'text',
				'null'     => true,
				'alias'    => 'line_label',
			];

			$columns['l.debit'] = (object) [
				'textMatch'=> false,
				'label'    => 'Débit',
				'type'     => 'text',
				'null'     => false,
				'alias'    => 'debit',
				'originalType' => 'money',
			];

			$columns['l.credit'] = (object) [
				'textMatch'=> false,
				'label'    => 'Crédit',
				'type'     => 'text',
				'null'     => false,
				'alias'    => 'credit',
				'originalType' => 'money',
			];

			$columns['l.reference'] = (object) [
				'textMatch'=> true,
				'label'    => 'Référence ligne écriture',
				'type'     => 'text',
				'null'     => true,
				'alias'    => 'line_reference',
			];

			$columns['t.type'] = (object) [
				'textMatch'=> false,
				'label'    => 'Type d\'écriture',
				'type'     => 'enum',
				'null'     => false,
				'values'   => Transaction::TYPES_NAMES,
				'alias'    => 'type',
			];

			$columns['a.code'] = (object) [
				'textMatch'=> true,
				'label'    => 'Numéro de compte',
				'type'     => 'text',
				'null'     => false,
				'alias'    => 'code',
			];

			$columns['t.id_year'] = (object) [
				'textMatch'=> false,
				'label'    => 'Exercice',
				'type'     => 'enum',
				'null'     => false,
				'values'   => $db->getAssoc('SELECT id, label FROM acc_years ORDER BY end_date;'),
				'alias'    => 'id_year',
			];

			$columns['a2.code'] = (object) [
				'textMatch'=> true,
				'label'    => 'N° de compte projet',
				'type'     => 'text',
				'null'     => true,
				'alias'    => 'id_analytical',
			];
		}

		return $columns;
	}

	/**
	 * Construire une recherche SQL à partir d'un objet généré par QueryBuilder
	 * @param  string  $target Cible de la requête : membres, compta_journal, etc.
	 * @param  array   $groups Groupes de critères
	 * @param  string  $order  Ordre de tri
	 * @param  boolean $desc   Inverser le tri
	 * @param  integer $limit  Limite
	 * @return string Chaîne SQL
	 */
	public function buildQuery(string $target, array $groups, string $order, bool $desc = false, int $limit = 100)
	{
		if (!in_array($target, self::TARGETS, true))
		{
			throw new \InvalidArgumentException('Cible inconnue : ' . $target);
		}

		$config = Config::getInstance();

		if ($target == 'membres')
		{
			$champs = $config->get('champs_membres');
		}

		$db = DB::getInstance();
		$target_columns = $this->getColumns($target);

		if (!isset($target_columns[$order])) {
			throw new UserException('Colonne de tri inconnue : ' . $order);
		}

		$query_columns = [];

		$query_groups = [];

		static $no_transform_operators = ['IS NULL', 'IS NOT NULL', '= 0', '= 1', '&'];

		foreach ($groups as $group)
		{
			if (!isset($group['conditions'], $group['operator'])
				|| !is_array($group['conditions'])
				|| ($group['operator'] != 'AND' && $group['operator'] != 'OR'))
			{
				// Ignorer les groupes de conditions invalides
				continue;
			}

			$query_group_conditions = [];

			foreach ($group['conditions'] as $condition)
			{
				if (!isset($condition['column'], $condition['operator'])
					|| (isset($condition['values']) && !is_array($condition['values'])))
				{
					// Ignorer les conditions invalides
					continue;
				}

				if (!array_key_exists($condition['column'], $target_columns))
				{
					// Ignorer une condition qui se rapporte à une colonne
					// qui n'existe pas, cas possible si on reprend une recherche
					// après avoir modifié les fiches de membres
					continue;
				}

				$query_columns[] = $condition['column'];
				$column = $target_columns[$condition['column']];

				$query = sprintf('%s %s', $db->quoteIdentifier($condition['column']), $condition['operator']);

				$values = isset($condition['values']) ? $condition['values'] : [];

				if (!empty($column->originalType)) {
					if ($column->originalType == 'tel') {
						// Normaliser le numéro de téléphone
						$values = array_map(['Garradin\Utils', 'normalizePhoneNumber'], $values);
					}
					elseif ($column->originalType == 'money') {
						$values = array_map(['Garradin\Utils', 'moneyToInteger'], $values);
					}
				}

				// L'opérateur binaire est un peu spécial
				if ($condition['operator'] == '&')
				{
					$new_query = [];

					foreach ($values as $value)
					{
						$new_query[] = sprintf('%s (1 << %d)', $query, (int) $value);
					}

					$query = '(' . implode(' AND ', $new_query) . ')';
				}
				// Remplacement de liste
				elseif (strpos($query, '??') !== false)
				{
					$values = array_map([$db, 'quote'], $values);
					$query = str_replace('??', implode(', ', $values), $query);
				}
				// Remplacement de recherche LIKE
				elseif (preg_match('/%\?%|%\?|\?%/', $query, $match))
				{
					$value = str_replace(['%', '_'], ['\\%', '\\_'], reset($values));
					$value = str_replace('?', $value, $match[0]);
					$query = str_replace($match[0], sprintf('%s ESCAPE \'\\\'', $db->quote($value)), $query);
				}
				// Remplacement de paramètre
				elseif (strpos($query, '?') !== false)
				{
					$expected = substr_count($query, '?');
					$found = count($values);

					if ($expected != $found)
					{
						throw new \RuntimeException(sprintf('Operator %s expects at least %d parameters, only %d supplied', $condition['operator'], $expected, $found));
					}

					for ($i = 0; $i < $expected; $i++)
					{
						$pos = strpos($query, '?');
						$query = substr_replace($query, $db->quote(array_shift($values)), $pos, 1);
					}
				}

				$query_group_conditions[] = $query;
			}

			if (count($query_group_conditions))
			{
				$query_groups[] = implode(' ' . $group['operator'] . ' ', $query_group_conditions);
			}
		}

		if (!count($query_groups))
		{
			throw new UserException('Aucune clause trouvée dans la recherche : elle contenait peut-être des clauses qui correspondent à des champs qui ont été supprimés ?');
		}

		// Ajout du champ identité si pas présent
		if ($target == 'membres')
		{
			$query_columns = array_merge([$config->get('champ_identite')], $query_columns);
		}
		// Ajout de champs compta si pas présents
		elseif ($target == 'compta')
		{
			$query_columns = array_merge(['t.id', 't.date', 't.label', 'l.debit', 'l.credit', 'a.code'], $query_columns);
		}

		$query_columns[] = $order;

		if ($target_columns[$order]->textMatch)
		{
			$order = sprintf('%s COLLATE U_NOCASE', $db->quoteIdentifier($order));
		}
		else
		{
			$order = $db->quoteIdentifier($order);
		}

		$query_columns = array_unique($query_columns);
		$query_columns = array_map(function ($column) use ($target_columns, $db) {
			if (isset($target_columns[$column]->alias)) {
				return sprintf('%s AS %s', $db->quoteIdentifier($column), $db->quote($target_columns[$column]->alias));
			}
			return $db->quoteIdentifier($column);
		}, $query_columns);

		$query_columns = implode(', ', $query_columns);

		$query_groups = '(' . implode(') AND (', $query_groups) . ')';

		$desc = $desc ? 'DESC' : 'ASC';

		if ('compta' === $target) {
			$sql_query = sprintf('SELECT %s
				FROM acc_transactions AS t
				INNER JOIN acc_transactions_lines AS l ON l.id_transaction = t.id
				INNER JOIN acc_accounts AS a ON l.id_account = a.id
				LEFT JOIN acc_accounts AS a2 ON l.id_analytical = a2.id
				WHERE %s GROUP BY t.id ORDER BY %s %s LIMIT %d;',
				$query_columns, $query_groups, $order, $desc, (int) $limit);
			$sql_query = preg_replace('/"(a|a2|l|t)\./', '"$1"."', $sql_query);
		}
		else if ('membres' === $target) {
			$sql_query = sprintf('SELECT id AS _user_id, %s FROM %s WHERE %s ORDER BY %s %s LIMIT %d;',
				$query_columns, $target, $query_groups, $order, $desc, (int) $limit);
		}
		else {
			$sql_query = sprintf('SELECT id, %s FROM %s WHERE %s ORDER BY %s %s LIMIT %d;',
				$query_columns, $target, $query_groups, $order, $desc, (int) $limit);
		}

		return $sql_query;
	}

	static public function rawSQL(string $query, array $allowed_tables = null, bool $no_limit = false): array
	{
		if (!$no_limit && !preg_match('/LIMIT\s+\d+/i', $query))
		{
			$query = preg_replace('/;?\s*$/', '', $query);
			$query .= ' LIMIT 100';
		}

		$st = DB::getInstance()->protectSelect($allowed_tables, $query);
		$res = $st->execute();
		$out = [];

		while ($row = $res->fetchArray(\SQLITE3_ASSOC)) {
			$out[] = (object) $row;
		}

		return $out;
	}

	/**
	 * Lancer une recherche SQL
	 */
	public function searchSQL(string $target, $query, array $force_select = null, bool $no_limit = false, bool $unprotected = false)
	{
		if (!in_array($target, self::TARGETS, true))
		{
			throw new \InvalidArgumentException('Cible inconnue : ' . $target);
		}

		if (null !== $force_select)
		{
			$query = preg_replace('/^\s*SELECT\s+(.*)\s+FROM\s+/Uis', 'SELECT $1, ' . implode(', ', $force_select) . ' FROM ', $query);
		}

		if (!$no_limit && !preg_match('/LIMIT\s+\d+/i', $query))
		{
			$query = preg_replace('/;?\s*$/', '', $query);
			$query .= ' LIMIT 100';
		}

		try {
			$db = DB::getInstance();
			static $allowed = [
				'compta' => ['acc_transactions' => null, 'acc_transactions_lines' => null, 'acc_accounts' => null, 'acc_charts' => null, 'acc_years' => null, 'acc_transactions_users' => null],
				'membres' => ['membres' => null, 'users_categories' => null],
			];

			if ($unprotected) {
				$allowed_tables = null;
			}
			else {
				$allowed_tables = $allowed[$target];
			}

			$db->protectSelect($allowed_tables, $query);
			return $db->get($query);
		}
		catch (\Exception $e) {
			$message = 'Erreur dans la requête : ' . $e->getMessage();

			if (null !== $force_select)
			{
				$message .= "\nVérifiez que votre requête sélectionne bien les colonnes suivantes : " . implode(', ', $force_select);
			}

			throw new UserException($message);
		}
	}

	public function searchQuery(string $table, $query, $order, $desc = false, $limit = 100)
	{
		$sql_query = $this->buildQuery($table, $query, $order, $desc, $limit);
		return $this->searchSQL($table, $sql_query);
	}

	public function buildSimpleMemberQuery(string $query)
	{
		$operator = 'LIKE %?%';

		if (is_numeric(trim($query)))
		{
			$column = 'numero';
			$operator = '= ?';
		}
		elseif (strpos($query, '@') !== false)
		{
			$column = 'email';
		}
		else
		{
			$column = Config::getInstance()->get('champ_identite');
		}

		$query = [[
			'operator' => 'AND',
			'conditions' => [
				[
					'column'   => $column,
					'operator' => $operator,
					'values'   => [$query],
				],
			],
		]];

		return (object) [
			'query' => $query,
			'order' => $column,
			'desc' => false,
			'limit' => 50,
		];
	}

	public function buildSimpleAccountingQuery(string $text, ?int $id_year = null)
	{
		$query = [];

		$text = trim($text);

		if ($id_year) {
			$query[] = [
				'operator' => 'AND',
				'conditions' => [
					[
						'column'   => 't.id_year',
						'operator' => '= ?',
						'values'   => [$id_year],
					],
				],
			];
		}

		// Match number: find transactions per credit or debit
		if (preg_match('/^=\s*\d+([.,]\d+)?$/', $text))
		{
			$text = ltrim($text, "\n\t =");
			$query[] = [
				'operator' => 'OR',
				'conditions' => [
					[
						'column'   => 'l.debit',
						'operator' => '= ?',
						'values'   => [$text],
					],
					[
						'column'   => 'l.credit',
						'operator' => '= ?',
						'values'   => [$text],
					],
				],
			];
		}
		// Match account number
		elseif ($id_year && preg_match('/^[0-9]+[A-Z]*$/', $text)
			&& ($year = Years::get($id_year))
			&& ($id = (new Accounts($year->id_chart))->getIdFromCode($text))) {
			return sprintf('!acc/accounts/journal.php?id=%d&year=%d', $id, $id_year);
		}
		// Match date
		elseif (preg_match('!^\d{2}/\d{2}/\d{4}$!', $text) && ($d = Utils::get_datetime($text)))
		{
			$query[] = [
				'operator' => 'OR',
				'conditions' => [
					[
						'column'   => 't.date',
						'operator' => '= ?',
						'values'   => [$d->format('Y-m-d')],
					],
				],
			];
		}
		// Match transaction ID
		elseif (preg_match('/^#[0-9]+$/', $text)) {
			return sprintf('!acc/transactions/details.php?id=%d', (int)substr($text, 1));
		}
		// Or search in label or reference
		else
		{
			$operator = 'LIKE %?%';
			$query[] = [
				'operator' => 'OR',
				'conditions' => [
					[
						'column'   => 't.label',
						'operator' => $operator,
						'values'   => [$text],
					],
					[
						'column'   => 't.reference',
						'operator' => $operator,
						'values'   => [$text],
					],
					[
						'column'   => 'l.reference',
						'operator' => $operator,
						'values'   => [$text],
					],
				],
			];
		}

		return (object) [
			'query' => $query,
			'order' => 't.id',
			'desc' => true,
			'limit' => 50,
		];
	}

	public function schema(string $target)
	{
		$db = DB::getInstance();

		if ($target == 'membres') {
			$tables = [
				'membres'    => $db->firstColumn('SELECT sql FROM sqlite_master WHERE type = \'table\' AND name = \'membres\';'),
				'users_categories' => $db->firstColumn('SELECT sql FROM sqlite_master WHERE type = \'table\' AND name = \'users_categories\';'),
			];
		}
		elseif ($target == 'compta') {
			$tables = [
				'acc_transactions'       => $db->firstColumn('SELECT sql FROM sqlite_master WHERE type = \'table\' AND name = \'acc_transactions\';'),
				'acc_transactions_lines' => $db->firstColumn('SELECT sql FROM sqlite_master WHERE type = \'table\' AND name = \'acc_transactions_lines\';'),
			];
		}
		else {
			throw new \LogicException('Unknown target');
		}

		return $tables;
	}
}