-- News/tips articles shown at /blog.
CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `slug` varchar(120) NOT NULL,
  `title` varchar(200) NOT NULL,
  `excerpt` varchar(400) NOT NULL DEFAULT '',
  `body` mediumtext NOT NULL,
  `published` tinyint(1) NOT NULL DEFAULT 0,
  `published_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `posts_slug_unique` (`slug`),
  KEY `posts_published_index` (`published`, `published_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- The enquiry changes (phone, source, notes, pipeline statuses) are applied by
-- `npm run db:migrate`, which checks each column first so it is safe to re-run.
-- To apply them by hand in phpMyAdmin instead, run these once:
--   ALTER TABLE enquiries MODIFY email varchar(255) NULL;
--   ALTER TABLE enquiries ADD COLUMN phone varchar(40) NULL AFTER email;
--   ALTER TABLE enquiries ADD COLUMN source enum('form','chat') NOT NULL DEFAULT 'form' AFTER message;
--   ALTER TABLE enquiries ADD COLUMN notes text NULL AFTER status;
--   ALTER TABLE enquiries MODIFY status enum('new','read','contacted','quoted','won','lost') NOT NULL DEFAULT 'new';
--   UPDATE enquiries SET status = 'contacted' WHERE status = 'read';
--   ALTER TABLE enquiries MODIFY status enum('new','contacted','quoted','won','lost') NOT NULL DEFAULT 'new';