# on a debian 9 fresh VM
ssh -A intarnet@intarnet.fr

# "fork" from CHD' git repos
git config --global -e
git clone root@chd.sx:/var/git/chd_gestion
cd ~/chd_gestion/
git checkout a6104f4
git checkout -b cake36
editor app-from-scratch.sh # Adding notes progressively

# env setup
sudo apt --yes install php-cli apache2 libapache2-mod-php php-mbstring php-intl php-xml php-sqlite3 phpmyadmin mariadb-server unzip
sudo a2disconf phpmyadmin
sudo apache2 restart
sudo mkdir /var/www/intarnet.fr
sudo chown intarnet: /var/www/intarnet.fr

# composer install
wget -q https://getcomposer.org/composer.phar -O ~/bin/composer.phar
chmod +x ~/bin/composer.phar
echo 'export PATH="$PATH:~/bin"' >> ~/.bashrc
source ~/.bashrc
composer.phar --version
# Composer version 1.7-dev (006c921abde5a9fa5ec682014265e3203a0987b1) 2018-07-12 20:08:52

# cake install
cd ~/chd_gestion/
mkdir fai_gestion
yes | composer.phar create-project --prefer-dist cakephp/app fai_gestion
ln -s ~intarnet/chd_gestion/fai_gestion/bin/cake ~/bin/
cd ~/chd_gestion/fai_gestion
cake version
# 3.6.7

# cake first start (embed webserver)
cake server -p 8080
# from desktop linux :
# ssh -L 8080:localhost:8080 intarnet@intarnet.fr
# firefox http://localhost:8080/
# -> everything green except database connection
^C

# prepare for apache2
ln -s ~intarnet/chd_gestion/fai_gestion /var/www/intarnet.fr/gestion
sudo chgrp -R www-data tmp/ logs/
sudo chmod -R ug=rwX,o=rX tmp/ logs/
# (cake bake runs with intarnet user, web served pages with www-data)
# do NOT allow www-data write elsewhere
sudo mv logs /var/log/fai_gestion
ln -s /var/log/fai_gestion logs

# apache2 vhost excerpt

# Alias "/gestion" "/var/www/intarnet.fr/gestion/webroot"
# <Directory /var/www/intarnet.fr/gestion>
#         Options None
#         AllowOverride None
#         Require all denied
# </Directory>
# <Directory /var/www/intarnet.fr/gestion/webroot>
#         Options SymLinksIfOwnerMatch
#         AllowOverride None
#
#         AuthType Basic
#         AuthName "Partie privee"
#         AuthUserFile /etc/apache2/passwords/intarnet.fr/www_priv.passwd
#         Require valid-user
#
#         SetEnv APP_DEFAULT_LOCALE fr_FR
#         SetEnv DEBUG true
#
#         RequestHeader unset Proxy
#         RewriteEngine On
#         RewriteCond %{REQUEST_FILENAME} !-f
#         RewriteRule ^ index.php [L]
# </Directory>

sudo a2enmod env headers rewrite
sudo service apache2 restart

# from desktop linux :
# firefox https://intarnet.fr/gestion/
# -> everything green except database connection

cd ~/chd_gestion/
cat fai_gestion/.gitignore
git add app-from-scratch.sh fai_gestion
git commit -m "Cake 3.6.7 fresh install"
# 4a3ec0ca3f7d0ca8776a6ee7f2a2615234395eb8

# Cake startup configuration
sed --in-place -e 's#^/config/app.php#/config/app_credentials.php#' fai_gestion/.gitignore
git add app-from-scratch.sh fai_gestion/.gitignore fai_gestion/config/app.php
git commit -m "Config : add app.php to git without credentials"
# 8d39eb3d5ad5baee721a2c6518a8429fb0cbd1bf

cat > config/app_credentials.php <<"EOT"
<?php
return [
    'Security' => [
        'salt' => env('SECURITY_SALT', 'hexhexhexhexhexhexhexhexhexhexhexhexhexhexhexhexhexhexhexhexhexh'),
    ],
    'Datasources' => [
        'default' => [
            'className' => 'Cake\Database\Connection',
            'driver' => 'Cake\Database\Driver\Mysql',
            'persistent' => false,
            'host' => 'localhost',
            'username' => 'my_app',
            'password' => 'secret',
            'database' => 'my_app',
            'timezone' => 'UTC',
            'cacheMetadata' => true,
            'log' => false,
            'quoteIdentifiers' => false,
        ],
    ],
];
EOT

cd ~/chd_gestion/fai_gestion/
editor config/bootstrap.php config/app.php
editor config/app_credentials.php # choose real DB credentials now
git add config/bootstrap.php config/app.php ../app-from-scratch.sh
git commit -m "Config : database, salt, inflections, datetime format"
# 7bf701764ff5661d053244b263cfe0be423faef5


# database init
sudo mariadb --defaults-extra-file=/etc/mysql/debian.cnf <<"EOT"
CREATE DATABASE IF NOT EXISTS fai_gestion;
CREATE DATABASE IF NOT EXISTS fai_gestion_exports;
CREATE USER IF NOT EXISTS 'fai_gestion'@'localhost' IDENTIFIED BY 'XXXXXXXXXXXXXXXX';
GRANT ALL PRIVILEGES ON  fai_gestion.* TO 'fai_gestion'@'localhost';
FLUSH PRIVILEGES;
EOT

# database schema modifications
# base on branch master (chd_gestion) 5709f164402a76dfd0e6ef098a45c73f976b48cd
# Fixup list for ~/chd_gestion/schema/schema-mysql.sql
# - sed -e 's/DEFINER=`gestion`@`localhost`/DEFINER=`fai_gestion`@`localhost`/g'
# - change v_relais SQL SECURITY INVOKER to SECURITY DEFINER=`fai_gestion`@`localhost`
# - move all views (v_*) in fai_gestion_exports because cake bake do bad things with them
# - DROP TABLE tmp_mac_seen;
# - DROP VIEW v_tmp_diff_seen_mac;
# - ALTER TABLE adherents DROP num_adt_ttn;
# - ALTER TABLE villes DROP tel_contact;
# - ALTER TABLE equipement_modeles CHANGE modele title VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
# - ALTER TABLE equipement_modeles ADD INDEX(title);
# - DROP TRIGGER equipements_insert;
# - DROP TRIGGER equipements_update;

cat ~/chd_gestion/schema/schema-mysql.sql | sudo mariadb fai_gestion
cat <<"EOT"
CREATE TABLE lien_filaire (
 id int(11) NOT NULL AUTO_INCREMENT,
 equipement_interface_id_1 int(11) NOT NULL,
 port_1 char(15) NOT NULL,
 equipement_interface_id_2 int(11) NOT NULL,
 port_2 char(15) NOT NULL,
 PRIMARY KEY (id),
 UNIQUE KEY equipement_interface_id_1 (equipement_interface_id_1,port_1,equipement_interface_id_2,port_2),
 KEY equipement_interface_id_2 (equipement_interface_id_2),
 CONSTRAINT lien_filaire_ibfk_1 FOREIGN KEY (equipement_interface_id_1) REFERENCES equipement_interfaces (id) ON UPDATE CASCADE,
 CONSTRAINT lien_filaire_ibfk_2 FOREIGN KEY (equipement_interface_id_2) REFERENCES equipement_interfaces (id) ON UPDATE CASCADE
);

CREATE TABLE lien_radio (
 id int(11) NOT NULL AUTO_INCREMENT,
 title varchar(64) NOT NULL,
 PRIMARY KEY (id),
 UNIQUE KEY title (title)
);

CREATE TABLE lien_tunnel (
 id int(11) NOT NULL AUTO_INCREMENT,
 endpoint_ip_1 varchar(39) NOT NULL,
 endpoint_port_1 int(5) NOT NULL,
 endpoint_ip_2 varchar(39) NOT NULL,
 endpoint_port_2 int(5) NOT NULL,
 equipement_interface_id_1 int(11) NOT NULL,
 equipement_interface_id_2 int(11) NOT NULL,
 tuntype varchar(15) NOT NULL DEFAULT 'tun',
 daemon_options varchar(255) DEFAULT NULL,
 PRIMARY KEY (id),
 UNIQUE KEY equipement_interface_id_1 (equipement_interface_id_1,equipement_interface_id_2),
 UNIQUE KEY endpoint_ip_1 (endpoint_ip_1,endpoint_port_1,endpoint_ip_2,endpoint_port_2),
 KEY equipement_interface_id_2 (equipement_interface_id_2),
 CONSTRAINT lien_tunnel_ibfk_1 FOREIGN KEY (equipement_interface_id_1) REFERENCES equipement_interfaces (id) ON UPDATE CASCADE,
 CONSTRAINT lien_tunnel_ibfk_2 FOREIGN KEY (equipement_interface_id_2) REFERENCES equipement_interfaces (id) ON UPDATE CASCADE
);
EOT
cat ~/chd_gestion/schema/views-mysql.sql | sudo mariadb fai_gestion_exports
cat ~/chd_gestion/schema/enum-tables-data-mysql.sql | sudo mariadb fai_gestion
(
	echo 'INSERT INTO ippubliques (ip4, ip6, secteur_id) VALUES'
	for i in $(seq 1 254)
	do
		[ $i -ne 254 ] && sep=',' || sep=';'
		printf "('185.131.43.%i', '2a03:a0a1:1:%x::', '1')$sep\n" $i $i
	done
) | sudo mariadb fai_gestion

# Convert equipements.uplink tree to (equipement_interfaces, lien_cable, lien_radio, lien_tunnel)
# - ALTER TABLE equipements DROP uplink_id;
# - ALTER TABLE equipements CHANGE ipmgmt_id ip_management VARCHAR(39) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
# - import PROCEDURE refresh_network_elt(IN root INT)
# - TODO : work on fai_gestion_exports permissions
# - TODO : dump everything cleanly then git

# bake initial setup
cd ~/chd_gestion/fai_gestion/
composer.phar require --dev cakephp/bake:~1.0
yes | cake bake plugin CustomTheme
v=vendor/cakephp/bake/src/Template/Bake
p=plugins/CustomTheme/src/Template/Bake
mkdir -p $p/{Controller,Element/Controller,Model,Template}
cp -a $v/Controller/controller.twig $p/Controller/
cp -a $v/Element/form.twig $p/Element/
cp -a $v/Element/Controller/{add,edit,index}.twig $p/Element/Controller
cp -a $v/Model/{entity,table}.twig $p/Model/
cp -a $v/Template/{index,view}.twig $p/Template/
editor config/bootstrap.php config/bootstrap_cli.php
# load the plugin only in cli mode
git checkout -- config/bootstrap.php
git add ../app-from-scratch.sh composer.json composer.lock config/bootstrap_cli.php plugins/
git commit -m "Bake : setup and create empty CustomTheme"
# 31a40cfc446c9586f89a4aa69ef76d76d469e5a4


# Baking a cake (generate the CRUD)
cake bake all --theme=CustomTheme --everything
editor config/routes.php
# -$routes->connect('/', ['controller' => 'Pages', 'action' => 'display', 'home']);
# +//$routes->connect('/', ['controller' => 'Pages', 'action' => 'display', 'home']);
# +$routes->redirect('/', ['controller' => 'Adherents'], ['full' => false]);
# -$routes->connect('/', ['controller' => 'Pages', 'action' => 'display', 'home']);
git add ../app-from-scratch.sh config/routes.php
git add src/Model/Entity/empty src/Model/Table/empty tests/Fixture/empty
git commit -m "first cake bake, auto-generated files not included for now"
# 18507a4c76abad7cf5e6e9572137d0dc5a977fcc

# Adding debug tool, tweaking date formatting
editor config/app.php config/bootstrap.php
git add ../app-from-scratch.sh config/app.php config/bootstrap.php
git commit -m "Adding debug tool, tweaking date formatting"
# eeb6aee61bc55a95a4d62d354f180d167c6a25fb

# bake : Commit imported&derived files from bake to keep the diffs
cp -a vendor/cakephp/bake/src/Shell/BakeShell.php plugins/CustomTheme/src/Shell/BakeShell.php
editor plugins/CustomTheme/src/Shell/BakeShell.php
git add plugins/CustomTheme/src/Shell/BakeShell.php
git commit -m "bake : Commit imported&derived files from bake to keep the diffs"
# 1eeb6c1ee3b538e2a3f5b940c32725ec068f21ba

# bake: Add config, generate less actions
# https://book.cakephp.org/3.0/fr/bake/development.html#creating-a-bake-theme
editor config/bake_extra.php
git add config/bake_extra.php

editor config/bootstrap_cli.php plugins/CustomTheme/config/bootstrap.php
git add config/bootstrap_cli.php plugins/CustomTheme/config/bootstrap.php

editor plugins/CustomTheme/src/Template/Bake/Element/Controller/_empty.twig
git add plugins/CustomTheme/src/Template/Bake/Element/Controller/_empty.twig

editor plugins/CustomTheme/src/Shell/BakeShell.php
git add plugins/CustomTheme/src/Shell/BakeShell.php

editor plugins/CustomTheme/src/View/Helper/BakeExtraHelper.php
git add plugins/CustomTheme/src/View/Helper/BakeExtraHelper.php

git add ../app-from-scratch.sh
git commit -m "bake: Add configurability, generate less actions"
# 0dd134b71539f38d84db3b023ba42d2650df278a


# Add Copyright & GPL3+ notice to Templates
editor $p/Controller/controller.twig $p/Model/entity.twig $p/Model/table.twig $p/Template/index.twig $p/Template/view.twig
git add $p/Controller/controller.twig $p/Model/entity.twig $p/Model/table.twig $p/Template/index.twig $p/Template/view.twig
git commit -m "Add Copyright & GPL3+ notice to Templates"
# 9a73d523ebd6035fac8deaa347ce41e4eda13232

# Date picker in DD/MM/YYYY order
editor src/View/AppView.php
git add ../app-from-scratch.sh src/View/AppView.php
git commit -m "Date picker in DD/MM/YYYY order"
# 7de45a8e4b595856de2f0af78b13e4d2efc5e716

# bake all : skip some models and actions, via configuration
editor plugins/CustomTheme/src/Shell/BakeShell.php
git add ../app-from-scratch.sh plugins/CustomTheme/src/Shell/BakeShell.php
git commit -m "bake all : skip some models and actions, via configuration"
# 76ab63096a25f5bf7b9d076cdc9ab3fc5218d216

# bake: add Search filter support

# https://github.com/FriendsOfCake/search
composer.phar require friendsofcake/search
cake plugin load Search
git add src/Application.php config/bootstrap.php composer.json composer.lock

editor plugins/CustomTheme/src/View/Helper/BakeExtraHelper.php $p/Model/table.twig
git add plugins/CustomTheme/src/View/Helper/BakeExtraHelper.php $p/Model/table.twig

editor plugins/CustomTheme/config/bootstrap.php
git add plugins/CustomTheme/config/bootstrap.php

editor $p/Model/table.twig $p/Element/Controller/index.twig $p/Template/index.twig
git add $p/Model/table.twig $p/Element/Controller/index.twig $p/Template/index.twig

editor src/Template/Layout/default.ctp webroot/css/local.css
git add src/Template/Layout/default.ctp webroot/css/local.css

git add ../app-from-scratch.sh
git commit -m "bake add Search filter support"
# b59164b0b7451905adcca244e6395281eb0633ca

# index: no 'id' column by default
editor plugins/CustomTheme/src/View/Helper/BakeExtraHelper.php plugins/CustomTheme/src/Template/Bake/Template/index.twig
git add plugins/CustomTheme/src/View/Helper/BakeExtraHelper.php plugins/CustomTheme/src/Template/Bake/Template/index.twig
git add ../app-from-scratch.sh
git commit -m "index: no 'id' column by default"
# 5e90ca7ab1cddf94e500397549b85c54aed570da

# make side bar skinny, add shortcuts to main controllers
editor $p/Template/index.twig $p/Template/view.twig $p/Element/form.twig src/Template/Layout/default.ctp
git add $p/Template/index.twig $p/Template/view.twig $p/Element/form.twig src/Template/Layout/default.ctp
git add ../app-from-scratch.sh
git commit -m "make side bar skinny, add shortcuts to main controllers"
# 5cb131935f0e4a8fb4082ed989a17c214d1e4beb

# configure all filters, no align-right on 'view'
editor config/bake_extra.php webroot/css/local.css
git add config/bake_extra.php webroot/css/local.css ../app-from-scratch.sh
git commit -m "configure all filters, no align-right on 'view'"

# rename ippubliques -> ip_publiques, .gitignore autogenerated code for now
# MariaDB side done via phpMyAdmin
editor src/Template/Layout/default.ctp .gitignore
git add src/Template/Layout/default.ctp .gitignore
cake bake all --everything --force --quiet
git add ../app-from-scratch.sh
git commit -m "rename ippubliques -> ip_publiques, .gitignore autogenerated code for now"
# 51f0aacb4d4f2514433139552ac6ab8ab5506653

# no 200 item limit on drop-down lists, index : better codegen
editor plugins/CustomTheme/src/Template/Bake/Element/Controller/*
git add plugins/CustomTheme/src/Template/Bake/Element/Controller/*
git add ../app-from-scratch.sh
git commit -m "no 200 item limit on drop-down lists, index : better codegen"
# 61cad7c5f1680e3c91da16f2d5deb38ceb73fffb

# bake: implement virtual entity titles
editor plugins/CustomTheme/config/bootstrap.php plugins/CustomTheme/src/Template/Bake/Model/entity.twig
editor config/bake_extra.php
git add plugins/CustomTheme/config/bootstrap.php plugins/CustomTheme/src/Template/Bake/Model/entity.twig
git add config/bake_extra.php
git add ../app-from-scratch.sh
git commit -m "bake: implement virtual entity titles"
# 748b7bf7c5c4df2d546ae375d9c13b5657e431df

# bake: fix Search filter support (bad stringifyList())
editor plugins/CustomTheme/src/View/Helper/BakeExtraHelper.php
editor config/bake_extra.php
git add plugins/CustomTheme/src/View/Helper/BakeExtraHelper.php
git add config/bake_extra.php
git add ../app-from-scratch.sh
git commit -m "bake: fix Search filter support (bad stringifyList())"
# b49f4d5ccd5a61e901c448ad94495750bb2d0269

# bake: sort tables by default (with config) + allow ORDER BY hooks
editor plugins/CustomTheme/src/Template/Bake/Model/table.twig
git add ../app-from-scratch.sh plugins/CustomTheme/src/Template/Bake/Model/table.twig
git commit -m "bake: sort tables by default (with config) + allow ORDER BY hooks"