-- Suppression des écrits qui n'existent pas
DELETE e FROM ecrits e LEFT JOIN ecrits_html h ON h.id = e.id WHERE h.id IS NULL ;
DELETE h FROM ecrits_html h LEFT JOIN ecrits e ON h.id = e.id WHERE e.id IS NULL ;

-- FIXME: passer à innodb
ALTER TABLE ecrits_html ADD FOREIGN KEY (id) REFERENCES ecrits(id);

-- Même chose sur les forums
DELETE f FROM forums f LEFT JOIN forums_textes t ON t.id = f.id WHERE t.id IS NULL ;
DELETE t FROM forums_textes t LEFT JOIN forums f ON t.id = f.id WHERE f.id IS NULL ;

-- FIXME: passer à innodb
ALTER TABLE forums_textes ADD FOREIGN KEY (id) REFERENCES forums(id);

-- Utilisation d'un ID numérique comme identifiant de journal
-- + noms de domaine pour journaux

DROP INDEX id ON journaux;

ALTER TABLE `journaux` CHANGE `id` `url_id` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;
ALTER TABLE `journaux` ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

UPDATE abonnements AS a
  JOIN journaux AS j ON j.url_id = a.journal
  SET a.journal = j.id;

ALTER TABLE `abonnements` CHANGE `journal` `journal` INT UNSIGNED NOT NULL;
ALTER TABLE abonnements ADD FOREIGN KEY (journal) REFERENCES journaux(id);

UPDATE abonnements_queue AS a
  JOIN journaux AS j ON j.url_id = a.journal
  SET a.journal = j.id;

ALTER TABLE `abonnements_queue` CHANGE `journal` `journal` INT UNSIGNED NOT NULL;
ALTER TABLE abonnements_queue ADD FOREIGN KEY (journal) REFERENCES journaux(id);

UPDATE acces_prives AS a
  JOIN journaux AS j ON j.url_id = a.journal
  SET a.journal = j.id;

ALTER TABLE `acces_prives` CHANGE `journal` `journal` INT UNSIGNED NOT NULL;
ALTER TABLE acces_prives ADD FOREIGN KEY (journal) REFERENCES journaux(id);

UPDATE ecrits AS a
  JOIN journaux AS j ON j.url_id = a.journal
  SET a.journal = j.id;

ALTER TABLE `ecrits` CHANGE `journal` `journal` INT UNSIGNED NOT NULL;
ALTER TABLE ecrits ADD FOREIGN KEY (journal) REFERENCES journaux(id);

# Changement format date
ALTER TABLE `ecrits` CHANGE `date` `date` VARCHAR( 255 ) NULL DEFAULT NULL;
UPDATE ecrits SET date = FROM_UNIXTIME(date);
ALTER TABLE `ecrits` CHANGE `date` `date` DATETIME NULL DEFAULT NULL ;

# En fait cette colonne ne sert pas
ALTER TABLE `journaux` DROP `dernier`;

# Et celle-ci on remplacera ça par une queue plus tard
ALTER TABLE journaux DROP print;

UPDATE forums AS a
  JOIN journaux AS j ON j.url_id = a.journal
  SET a.journal = j.id;

ALTER TABLE `forums` CHANGE `journal` `journal` INT UNSIGNED NOT NULL;
ALTER TABLE forums ADD FOREIGN KEY (journal) REFERENCES journaux(id);

CREATE TABLE files (
  journal INTEGER UNSIGNED NOT NULL REFERENCES journaux (id),
  type TINYINT UNSIGNED NOT NULL DEFAULT 0,
  hash VARCHAR(255) NOT NULL,
  mimetype VARCHAR(255) NULL,
  size INT UNSIGNED NOT NULL DEFAULT 0,
  filename VARCHAR(255) NOT NULL,
  title VARCHAR(255) NULL,
  article INT UNSIGNED NULL REFERENCES ecrits (id)
);

CREATE INDEX files_article ON files (article);
CREATE INDEX files_type ON files (type);
CREATE INDEX files_hash ON files (hash);


REPAIR TABLE abonnements;
OPTIMIZE TABLE abonnements;

REPAIR TABLE abonnements_queue;
OPTIMIZE TABLE abonnements_queue;

REPAIR TABLE acces_prives;
OPTIMIZE TABLE acces_prives;

REPAIR TABLE ecrits;
OPTIMIZE TABLE ecrits;

REPAIR TABLE ecrits_html;
OPTIMIZE TABLE ecrits_html;

REPAIR TABLE forums;
OPTIMIZE TABLE forums;

REPAIR TABLE forums_textes;
OPTIMIZE TABLE forums_textes;

REPAIR TABLE journaux;
OPTIMIZE TABLE journaux;

REPAIR TABLE membres;
OPTIMIZE TABLE membres;

REPAIR TABLE user_secret_keys;
OPTIMIZE TABLE user_secret_keys;
