-- Activate strict mode

-- Start with users and categories, as it’s referenced in other tables.
-- Otherwise, the reference is set to the renamed non strict table.

-- Users

ALTER TABLE "users" RENAME TO "users_non_strict";

CREATE TABLE IF NOT EXISTS "users"(
  "id" INTEGER PRIMARY KEY,
  "email" TEXT NOT NULL,
  "name" TEXT NOT NULL,
  "password" TEXT NOT NULL,
  "login_token" TEXT NULL,
  "created_at" TEXT NULL DEFAULT (datetime('now')),
  "updated_at" TEXT NULL,
  "deleted_at" TEXT NULL,
  CONSTRAINT "uniq_user_email" UNIQUE ("email"),
  CONSTRAINT "uniq_user_name" UNIQUE ("name")
) STRICT;

INSERT INTO users (id, created_at, email, name, password, login_token)
  SELECT id, created_at, email, name, password, login_token
  FROM users_non_strict;

DROP TABLE users_non_strict;

-- Categories

ALTER TABLE "categories" RENAME TO "categories_non_strict";

CREATE TABLE IF NOT EXISTS "categories"(
  "id" INTEGER PRIMARY KEY,
  "name" TEXT NOT NULL,
  "color" TEXT NOT NULL,
  "created_at" TEXT NULL DEFAULT (datetime('now')),
  "updated_at" TEXT NULL,
  "deleted_at" TEXT NULL
) STRICT;

INSERT INTO categories (id, name, color, created_at, updated_at, deleted_at)
  SELECT id, name, color, created_at, updated_at, deleted_at
  FROM categories_non_strict;

DROP TABLE categories_non_strict;

-- Payments

ALTER TABLE "payments" RENAME TO "payments_non_strict";

CREATE TABLE IF NOT EXISTS "payments"(
  "id" INTEGER PRIMARY KEY,
  "user_id" INTEGER NOT NULL REFERENCES "users",
  "name" TEXT NOT NULL,
  "cost" INTEGER NOT NULL,
  "date" TEXT NOT NULL,
  "frequency" TEXT NOT NULL,
  "category_id" INTEGER NOT NULL REFERENCES "categories",
  "created_at" TEXT NULL DEFAULT (datetime('now')),
  "updated_at" TEXT NULL,
  "deleted_at" TEXT NULL
) STRICT;

DROP INDEX IF EXISTS payment_date;
CREATE INDEX payment_date ON payments(date);

INSERT INTO payments (id, user_id, name, cost, date, frequency, category_id, created_at, updated_at, deleted_at)
  SELECT id, user_id, name, cost, date, frequency, category_id, created_at, updated_at, deleted_at
  FROM payments_non_strict;

DROP TABLE payments_non_strict;

-- Jobs

ALTER TABLE "jobs" RENAME TO "jobs_non_strict";

CREATE TABLE IF NOT EXISTS "jobs"(
  "name" TEXT PRIMARY KEY,
  "last_execution" TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;

INSERT INTO jobs (name, last_execution)
  SELECT name, last_execution
  FROM jobs_non_strict;

DROP TABLE jobs_non_strict;

-- Incomes

ALTER TABLE "incomes" RENAME TO "incomes_non_strict";

CREATE TABLE IF NOT EXISTS "incomes"(
  "id" INTEGER PRIMARY KEY,
  "user_id" INTEGER NOT NULL REFERENCES "users",
  "date" TEXT NOT NULL,
  "amount" INTEGER NOT NULL,
  "created_at" TEXT NULL DEFAULT (datetime('now')),
  "updated_at" TEXT NULL,
  "deleted_at" TEXT NULL
) STRICT;

INSERT INTO incomes (id, user_id, date, amount, created_at, updated_at, deleted_at)
  SELECT id, user_id, date, amount, created_at, updated_at, deleted_at
  FROM incomes_non_strict;

DROP TABLE incomes_non_strict;