-- database.sql
-- Import this into phpMyAdmin, then update inc/db.php with your database details.

CREATE TABLE IF NOT EXISTS cms_pages (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  page_type ENUM('article','page','privacy') NOT NULL DEFAULT 'article',
  topic VARCHAR(120) DEFAULT NULL,
  excerpt TEXT DEFAULT NULL,
  body LONGTEXT NOT NULL,
  featured_image VARCHAR(500) DEFAULT NULL,
  featured_image_alt VARCHAR(255) DEFAULT NULL,
  featured_image_caption TEXT DEFAULT NULL,
  show_featured_image TINYINT(1) NOT NULL DEFAULT 1,
  image_is_portrait TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('draft','published','private','archived') NOT NULL DEFAULT 'draft',
  published_at DATETIME DEFAULT NULL,
  article_date DATE DEFAULT NULL,
  article_time TIME DEFAULT NULL,
  is_featured TINYINT(1) NOT NULL DEFAULT 0,
  show_in_menu TINYINT(1) NOT NULL DEFAULT 0,
  menu_label VARCHAR(120) DEFAULT NULL,
  display_order INT NOT NULL DEFAULT 0,
  meta_title VARCHAR(255) DEFAULT NULL,
  meta_description TEXT DEFAULT NULL,
  keywords TEXT DEFAULT NULL,
  views INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cms_pages_slug (slug),
  KEY idx_status_type (status, page_type),
  KEY idx_topic (topic),
  KEY idx_published_at (published_at),
  KEY idx_featured (is_featured),
  KEY idx_menu (show_in_menu, display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_glossary_terms (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  term_key VARCHAR(120) NOT NULL,
  title VARCHAR(255) NOT NULL,
  short_definition TEXT DEFAULT NULL,
  body TEXT NOT NULL,
  status ENUM('draft','published','archived') NOT NULL DEFAULT 'published',
  display_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_term_key (term_key),
  KEY idx_status_order (status, display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO cms_pages
(title, slug, page_type, topic, excerpt, body, featured_image, featured_image_alt, featured_image_caption, show_featured_image, image_is_portrait, status, published_at, article_date, article_time, is_featured, show_in_menu, menu_label, display_order, meta_title, meta_description, keywords)
VALUES
(
  'A Return to Truth',
  'a-return-to-truth',
  'article',
  'Reflection',
  'A personal reflection on survival, responsibility, autonomy and the website beginning again.',
  '<p class="opening-line">It is 2:30am, and I am trying to understand where I am with my thoughts.</p>

<p>I feel as though I am standing at a crossroads. Part of me feels like giving up, walking away from everything, and finding a completely different life.</p>

<div class="warning-line">
  The reason I left my wife’s home was not as simple as people may have made it sound.
  After my father passed away, my mother was alone and grieving, and I felt a strong duty
  to support her emotionally. Looking back, I can see that I was caught between two duties:
  the duty I felt towards my mother, and the duty I had towards my wife and children.
</div>

<h2>Family systems and control</h2>

<p>In some <button class="glossary-ref js-glossary-ref" type="button" data-note="family-system">family systems</button>, marriage, divorce, property, money and reputation can become tied together.</p>

<div class="warning-line">
  Divorce and new arranged marriages can sometimes be used as tools of control. Beneath the language
  of family honour, concern or tradition, there may be a struggle over property, money, assets,
  inheritance and family alliances.
</div>

<div class="warning-line">
  In some family systems, when one brother from one side marries a sister from the other side,
  the marriages can become tied together in unhealthy ways. If one relationship breaks down,
  there can be an expectation that the other marriage should also end.
</div>

<h2>Shame, rumours and reputation</h2>

<p>Some forms of <button class="glossary-ref js-glossary-ref" type="button" data-note="shame-tactics">shame</button> are not really about truth. They are about pressure.</p>

<div class="warning-line">
  Those who try to shame me should remember that the story is far more complicated than the rumours suggest.
  Shame can sometimes be used to bring a person back under control.
</div>

<div class="warning-line">
  My wife asked me to return to our family home because she knows the wider truth of our life together.
  We have lived together for more than twenty years, and she knows that much of the gossip and character
  assassination surrounding me does not reflect the full reality.
</div>

<h2>Facing my own behaviour</h2>

<div class="warning-line">
  Facing the truth also means facing myself. I cannot only speak about what was done to me;
  I also have to look at the choices I made, the ways I coped, and the behaviour that caused
  pain or confusion.
</div>

<section class="facts-box">
  <h2>Seven Facts About the Story</h2>
  <ol class="facts-list">
    <li>I did not leave my wife’s home simply because I wanted to walk away. After my father died, my mother was grieving, and I felt a duty to support her.</li>
    <li>I became divided between two responsibilities: caring for my mother and remaining fully present for my wife and children.</li>
    <li>My wife has known me for more than twenty years. She understands the difference between my genuine mistakes and the rumours built around me.</li>
    <li>The gossip and character assassination do not tell the full story. Much of it is exaggerated, distorted or missing important context.</li>
    <li>Some of the shame tactics seem less about truth and more about control over marriage, children, property, loyalty and family alliances.</li>
    <li>I am not pretending I have done nothing wrong. I have to face my own behaviour, my coping mechanisms, and the ways I have caused pain or confusion.</li>
    <li>I now want to rebuild with truth, autonomy, emotional maturity and protection for my children.</li>
  </ol>
</section>

<div class="life-belongs">
  <p>For the first time in a long time, I am beginning to understand something very simple, but very powerful:</p>
  <h2>My life belongs to me.</h2>
  <ul>
    <li>Not to the tribe.</li>
    <li>Not to the family system.</li>
    <li>Not to shame.</li>
    <li>Not to fear.</li>
    <li>Not to the past.</li>
    <li>Not to anyone else’s expectations.</li>
  </ul>
  <h2>My life belongs to me.</h2>
</div>

<div class="final-line">
  <h2>Just wait for the website to return.</h2>
  <p>Something softer, stronger, and more honest is coming.</p>
</div>
',
  'assets/images/placeholder-image.svg',
  'A soft sunrise placeholder image',
  'A quiet image connected to the reflection. Replace this with your uploaded image.',
  1,
  0,
  'published',
  '2026-03-24 02:30:00',
  '2026-03-24',
  '02:30:00',
  1,
  1,
  'A Return to Truth',
  10,
  'A Return to Truth | Thaire Thoughts',
  'A personal reflection on truth, family systems, autonomy and rebuilding.',
  'truth, autonomy, reflection, family systems'
),
(
  'The Earlier Website Was a Cry for Help',
  'earlier-website-cry-for-help',
  'article',
  'Website',
  'A look back at the older version of the site as a container for distress, meaning and unfinished becoming.',
  '<p>The earlier version of the website was not simply a website. It was a container for urgency, distress and the need to be understood.</p>',
  NULL,
  NULL,
  NULL,
  0,
  0,
  'published',
  '2026-03-18 23:45:00',
  '2026-03-18',
  '23:45:00',
  1,
  0,
  NULL,
  20,
  NULL,
  NULL,
  NULL
),
(
  'Privacy Policy',
  'privacy-policy',
  'privacy',
  'Privacy',
  'How this website handles basic visitor information.',
  '<h2>Information collected</h2>
<p>This website may collect basic visitor information such as page views, browser type, approximate location, referrer and timestamps if analytics are enabled.</p>

<h2>How information is used</h2>
<p>Information may be used to understand website traffic, improve pages, protect the site and maintain a better reader experience.</p>

<h2>Contact</h2>
<p>If a contact form is added later, submitted details should only be used to respond to the message.</p>
',
  NULL,
  NULL,
  NULL,
  0,
  0,
  'published',
  '2026-01-01 00:00:00',
  NULL,
  NULL,
  0,
  0,
  NULL,
  999,
  'Privacy Policy | Thaire Thoughts',
  'Privacy policy for Thaire Thoughts.',
  NULL
);

INSERT INTO cms_glossary_terms
(term_key, title, short_definition, body, status, display_order)
VALUES
('family-system', 'Family System', 'The wider emotional and relational structure around a person.', 'A family system is the wider emotional and relational structure around a person: loyalty, roles, expectations, alliances, silences and inherited patterns.\n\nOn this site, the phrase is used to describe how pressure can come not only from one individual, but from a whole network of family expectations.', 'published', 10),
('shame-tactics', 'Shame Tactics', 'Attempts to control someone through embarrassment or reputation damage.', 'Shame tactics are attempts to control someone through embarrassment, reputation damage, gossip or moral pressure.\n\nThey can make a person feel that returning to obedience is safer than standing in their own truth.', 'published', 20),
('tribal-system', 'Tribal System', 'A wider family or community structure where loyalty may override autonomy.', 'A tribal system refers to a wider family or community structure where loyalty, honour and obedience may be valued over individual autonomy.\n\nThe term is used reflectively here to describe pressure, not to attack any culture or community as a whole.', 'published', 30),
('autonomy', 'Autonomy', 'The right to make adult choices and take responsibility.', 'Autonomy means the right to make adult choices, take responsibility, and live from one’s own conscience rather than fear or pressure.', 'published', 40),
('character-assassination', 'Character Assassination', 'Repeated attempts to damage a person’s reputation.', 'Character assassination is the repeated attempt to damage a person’s reputation through exaggeration, distortion or selective storytelling.', 'published', 50);



-- backend_upgrade.sql
-- Import this AFTER your existing database.sql, or import schema_full.sql for everything together.

CREATE TABLE IF NOT EXISTS cms_admin_users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(80) NOT NULL,
  email VARCHAR(255) DEFAULT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','editor') NOT NULL DEFAULT 'admin',
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  last_login_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cms_admin_username (username),
  KEY idx_cms_admin_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_quotes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  page_id INT UNSIGNED DEFAULT NULL,
  quote_text TEXT NOT NULL,
  quote_label VARCHAR(120) DEFAULT NULL,
  status ENUM('draft','published','archived') NOT NULL DEFAULT 'published',
  is_featured TINYINT(1) NOT NULL DEFAULT 1,
  display_order INT NOT NULL DEFAULT 0,
  published_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cms_quotes_page (page_id),
  KEY idx_cms_quotes_status (status),
  KEY idx_cms_quotes_featured (is_featured),
  KEY idx_cms_quotes_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_uploads (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  public_path VARCHAR(500) NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  file_size INT UNSIGNED NOT NULL DEFAULT 0,
  uploaded_by INT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cms_uploads_created (created_at),
  KEY idx_cms_uploads_user (uploaded_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional seed quote. The homepage also falls back to cms_pages if cms_quotes is empty.
INSERT INTO cms_quotes (page_id, quote_text, quote_label, status, is_featured, display_order, published_at)
SELECT id, excerpt, topic, 'published', 1, 10, published_at
FROM cms_pages
WHERE status = 'published'
  AND page_type = 'article'
  AND excerpt IS NOT NULL
  AND excerpt <> ''
  AND NOT EXISTS (SELECT 1 FROM cms_quotes LIMIT 1)
ORDER BY COALESCE(published_at, created_at) DESC
LIMIT 5;


-- FINAL SAFETY: featured image toggle.
-- If cms_pages already exists without this column, run the standalone upgrade file instead.
-- New installs include show_featured_image in the CREATE TABLE statement.