-- +goose Up
CREATE TABLE IF NOT EXISTS mailboxes (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  label text NOT NULL,
  email text NOT NULL,
  display_name text NOT NULL DEFAULT '',
  imap_host text NOT NULL,
  imap_port integer NOT NULL,
  imap_username text NOT NULL,
  imap_password_ciphertext text NOT NULL,
  imap_use_tls boolean NOT NULL DEFAULT true,
  smtp_host text NOT NULL,
  smtp_port integer NOT NULL,
  smtp_username text NOT NULL,
  smtp_password_ciphertext text NOT NULL,
  smtp_use_tls boolean NOT NULL DEFAULT true,
  sync_status text NOT NULL DEFAULT 'idle',
  sync_error text NOT NULL DEFAULT '',
  last_synced_at timestamptz,
  created_at timestamptz NOT NULL,
  updated_at timestamptz NOT NULL
);

CREATE INDEX IF NOT EXISTS mailboxes_workspace_updated_idx ON mailboxes (workspace_slug, updated_at DESC);

CREATE TABLE IF NOT EXISTS mail_messages (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  mailbox_id text NOT NULL REFERENCES mailboxes(id) ON DELETE CASCADE,
  remote_uid bigint NOT NULL,
  message_id text NOT NULL DEFAULT '',
  folder text NOT NULL DEFAULT 'INBOX',
  from_address jsonb NOT NULL DEFAULT '{}'::jsonb,
  to_recipients jsonb NOT NULL DEFAULT '[]'::jsonb,
  cc_recipients jsonb NOT NULL DEFAULT '[]'::jsonb,
  subject text NOT NULL DEFAULT '',
  snippet text NOT NULL DEFAULT '',
  text_body text NOT NULL DEFAULT '',
  html_body text NOT NULL DEFAULT '',
  received_at timestamptz NOT NULL,
  is_read boolean NOT NULL DEFAULT false,
  linked_task_id text,
  created_at timestamptz NOT NULL,
  updated_at timestamptz NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS mail_messages_mailbox_folder_uid_idx ON mail_messages (mailbox_id, folder, remote_uid);
CREATE INDEX IF NOT EXISTS mail_messages_workspace_received_idx ON mail_messages (workspace_slug, received_at DESC);

CREATE TABLE IF NOT EXISTS outgoing_mails (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  mailbox_id text NOT NULL REFERENCES mailboxes(id) ON DELETE CASCADE,
  to_recipients jsonb NOT NULL DEFAULT '[]'::jsonb,
  cc_recipients jsonb NOT NULL DEFAULT '[]'::jsonb,
  bcc_recipients jsonb NOT NULL DEFAULT '[]'::jsonb,
  subject text NOT NULL DEFAULT '',
  text_body text NOT NULL DEFAULT '',
  html_body text NOT NULL DEFAULT '',
  status text NOT NULL,
  scheduled_for timestamptz,
  sent_at timestamptz,
  error_message text NOT NULL DEFAULT '',
  created_at timestamptz NOT NULL,
  updated_at timestamptz NOT NULL
);

CREATE INDEX IF NOT EXISTS outgoing_mails_workspace_created_idx ON outgoing_mails (workspace_slug, created_at DESC);
CREATE INDEX IF NOT EXISTS outgoing_mails_status_schedule_idx ON outgoing_mails (status, scheduled_for ASC);

-- +goose Down
DROP TABLE IF EXISTS outgoing_mails;
DROP TABLE IF EXISTS mail_messages;
DROP TABLE IF EXISTS mailboxes;
