-- +goose Up
CREATE TABLE time_entries (
  start_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  end_at DATETIME,
  task_id INTEGER NOT NULL REFERENCES tasks(id),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME
);
-- +goose StatementBegin
CREATE TRIGGER time_entry_updated_at BEFORE UPDATE ON time_entries FOR EACH ROW
BEGIN
  UPDATE time_entries SET updated_at = CURRENT_TIMESTAMP WHERE rowid = NEW.rowid;
END;
-- +goose StatementEnd
-- +goose Down
DROP TRIGGER time_entry_updated_at;
DROP TABLE time_entries;

-- +goose Up
CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  status TEXT NOT NULL DEFAULT 'N' CHECK(status IN ('N', 'P', 'C', 'H', 'D')),
  estimate TEXT,
  start_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  due_at DATETIME,
  parent_id INTEGER REFERENCES tasks(id),
  project_id INTEGER REFERENCES projects(id),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- +goose StatementBegin
CREATE TRIGGER task_updated_at BEFORE UPDATE ON tasks FOR EACH ROW
BEGIN
  UPDATE tasks SET updated_at = CURRENT_TIMESTAMP WHERE rowid = NEW.rowid;
END;
-- +goose StatementEnd
-- +goose Down
DROP TRIGGER task_updated_at;
DROP TABLE tasks;

-- +goose Up
CREATE TABLE projects (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  status TEXT NOT NULL DEFAULT 'N' CHECK(status IN ('N', 'P', 'C', 'H', 'D')),
  start_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  due_at DATETIME,
  budget TEXT,
  recurrence TEXT,
  parent_id INTEGER REFERENCES projects(id),
  client_id INTEGER REFERENCES clients(id),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(name, client_id)
);
CREATE INDEX proj_name ON projects (name);
-- +goose StatementBegin
CREATE TRIGGER project_updated_at AFTER UPDATE ON projects FOR EACH ROW
BEGIN
  UPDATE projects SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;
-- +goose StatementEnd
-- +goose Down
DROP TRIGGER project_updated_at;

-- +goose Up
CREATE TABLE clients (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  email TEXT,
  phone TEXT,
  description TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- +goose StatementBegin
CREATE TRIGGER client_updated_at AFTER UPDATE ON clients FOR EACH ROW
BEGIN
  UPDATE clients SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;
-- +goose StatementEnd
-- +goose Down
DROP TRIGGER client_updated_at;
DROP TABLE clients;