-- =====================================================================
-- LarCare — stored procedures (all writes)
-- Source: docs/02-DATABASE-SCHEMA.md §5
-- Idempotent (DROP IF EXISTS + CREATE) — safe to re-run on every deploy.
-- =====================================================================
DELIMITER $$

-- ---------------------------------------------------------------
-- Contact form submission (public write)
-- Returns the new uuid so the API can echo it back to the client.
-- ---------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp_contact_submission_create $$
CREATE PROCEDURE sp_contact_submission_create (
  IN  p_first_name  VARCHAR(120),
  IN  p_last_name   VARCHAR(120),
  IN  p_email       VARCHAR(320),
  IN  p_phone       VARCHAR(40),
  IN  p_message     TEXT,
  IN  p_attachment  VARCHAR(500),
  IN  p_source_page VARCHAR(255),
  IN  p_ip_hash     CHAR(64),
  IN  p_user_agent  VARCHAR(500),
  OUT p_uuid        CHAR(36)
)
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
  DECLARE v_recent INT DEFAULT 0;

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  IF p_first_name IS NULL OR TRIM(p_first_name) = '' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'first_name is required';
  END IF;
  IF p_email IS NULL OR p_email NOT LIKE '%_@_%.__%' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'a valid email is required';
  END IF;
  IF p_message IS NULL OR CHAR_LENGTH(TRIM(p_message)) < 2 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'message is required';
  END IF;

  IF p_ip_hash IS NOT NULL THEN
    SELECT COUNT(*) INTO v_recent
      FROM contact_submissions
     WHERE ip_hash = p_ip_hash
       AND created_at > (UTC_TIMESTAMP() - INTERVAL 1 HOUR);
    IF v_recent >= 5 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'rate limit exceeded';
    END IF;
  END IF;

  SET p_uuid = UUID();

  START TRANSACTION;

  INSERT INTO contact_submissions
    (uuid, first_name, last_name, email, phone, message,
     attachment_path, source_page, ip_hash, user_agent)
  VALUES
    (p_uuid, TRIM(p_first_name), TRIM(p_last_name), LOWER(TRIM(p_email)),
     NULLIF(TRIM(COALESCE(p_phone,'')),''), p_message,
     p_attachment, p_source_page, p_ip_hash, p_user_agent);

  INSERT INTO audit_log (entity, entity_uuid, action, actor, detail)
  VALUES ('contact_submission', p_uuid, 'create', 'public',
          JSON_OBJECT('source_page', p_source_page));

  COMMIT;
END $$

-- ---------------------------------------------------------------
-- SMS consent (public write)
-- ---------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp_sms_consent_create $$
CREATE PROCEDURE sp_sms_consent_create (
  IN  p_first_name    VARCHAR(120),
  IN  p_last_name     VARCHAR(120),
  IN  p_email         VARCHAR(320),
  IN  p_phone         VARCHAR(40),
  IN  p_consent_given TINYINT(1),
  IN  p_consent_text  TEXT,
  IN  p_ip_hash       CHAR(64),
  OUT p_uuid          CHAR(36)
)
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  IF p_consent_given <> 1 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'consent must be affirmatively given';
  END IF;
  IF p_phone IS NULL OR CHAR_LENGTH(REGEXP_REPLACE(p_phone,'[^0-9]','')) < 10 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'a valid phone number is required';
  END IF;
  IF p_consent_text IS NULL OR TRIM(p_consent_text) = '' THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'consent_text must record the wording shown to the user';
  END IF;

  SET p_uuid = UUID();

  START TRANSACTION;

  INSERT INTO sms_consents
    (uuid, first_name, last_name, email, phone,
     consent_given, consent_text, ip_hash)
  VALUES
    (p_uuid, TRIM(p_first_name), TRIM(p_last_name), LOWER(TRIM(p_email)),
     p_phone, 1, p_consent_text, p_ip_hash);

  INSERT INTO audit_log (entity, entity_uuid, action, actor)
  VALUES ('sms_consent', p_uuid, 'create', 'public');

  COMMIT;
END $$

-- ---------------------------------------------------------------
-- SMS consent revocation (STOP reply / admin action)
-- ---------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp_sms_consent_revoke $$
CREATE PROCEDURE sp_sms_consent_revoke (
  IN p_phone VARCHAR(40),
  IN p_actor VARCHAR(160)
)
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
  UPDATE sms_consents
     SET revoked_at = UTC_TIMESTAMP()
   WHERE phone = p_phone
     AND revoked_at IS NULL;

  INSERT INTO audit_log (entity, action, actor, detail)
  VALUES ('sms_consent', 'revoke', COALESCE(p_actor,'system'),
          JSON_OBJECT('phone', p_phone, 'rows', ROW_COUNT()));
END $$

-- ---------------------------------------------------------------
-- Care service upsert (admin write)
-- Slug is the natural key: same slug updates, new slug inserts.
-- Highlights are replaced wholesale — simpler and safer than diffing.
-- ---------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp_care_service_upsert $$
CREATE PROCEDURE sp_care_service_upsert (
  IN  p_slug              VARCHAR(160),
  IN  p_title             VARCHAR(200),
  IN  p_short_description VARCHAR(500),
  IN  p_hero_image        VARCHAR(500),
  IN  p_body_html         MEDIUMTEXT,
  IN  p_sort_order        SMALLINT UNSIGNED,
  IN  p_featured          TINYINT(1),
  IN  p_status            VARCHAR(20),
  IN  p_meta_title        VARCHAR(200),
  IN  p_meta_description  VARCHAR(320),
  IN  p_highlights        JSON,
  IN  p_actor             VARCHAR(160),
  OUT p_uuid              CHAR(36)
)
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
  DECLARE v_id  BIGINT UNSIGNED DEFAULT NULL;
  DECLARE v_i   INT DEFAULT 0;
  DECLARE v_len INT DEFAULT 0;
  DECLARE v_action VARCHAR(20) DEFAULT 'update';

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  IF p_slug IS NULL OR TRIM(p_slug) = '' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'slug is required';
  END IF;
  IF p_status NOT IN ('draft','published','archived') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'invalid status';
  END IF;

  START TRANSACTION;

  SELECT id, uuid INTO v_id, p_uuid
    FROM care_services
   WHERE slug = p_slug
   FOR UPDATE;

  IF v_id IS NULL THEN
    SET v_action = 'create';
    SET p_uuid = UUID();
    INSERT INTO care_services
      (uuid, slug, title, short_description, hero_image, body_html,
       sort_order, featured_on_home, status, meta_title, meta_description)
    VALUES
      (p_uuid, p_slug, p_title, p_short_description, p_hero_image, p_body_html,
       COALESCE(p_sort_order,0), COALESCE(p_featured,0), p_status,
       p_meta_title, p_meta_description);
    SET v_id = LAST_INSERT_ID();
  ELSE
    UPDATE care_services
       SET title             = p_title,
           short_description = p_short_description,
           hero_image        = p_hero_image,
           body_html         = p_body_html,
           sort_order        = COALESCE(p_sort_order, sort_order),
           featured_on_home  = COALESCE(p_featured, featured_on_home),
           status            = p_status,
           meta_title        = p_meta_title,
           meta_description  = p_meta_description,
           deleted_at        = NULL
     WHERE id = v_id;
  END IF;

  DELETE FROM care_service_highlights WHERE service_id = v_id;

  IF p_highlights IS NOT NULL THEN
    SET v_len = JSON_LENGTH(p_highlights);
    WHILE v_i < v_len DO
      INSERT INTO care_service_highlights (service_id, label, sort_order)
      VALUES (v_id,
              JSON_UNQUOTE(JSON_EXTRACT(p_highlights, CONCAT('$[', v_i, ']'))),
              v_i);
      SET v_i = v_i + 1;
    END WHILE;
  END IF;

  INSERT INTO audit_log (entity, entity_uuid, action, actor, detail)
  VALUES ('care_service', p_uuid, v_action, COALESCE(p_actor,'system'),
          JSON_OBJECT('slug', p_slug, 'status', p_status));

  COMMIT;
END $$

-- ---------------------------------------------------------------
-- Blog post upsert (admin write)
-- ---------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp_blog_post_upsert $$
CREATE PROCEDURE sp_blog_post_upsert (
  IN  p_slug         VARCHAR(200),
  IN  p_title        VARCHAR(255),
  IN  p_excerpt      VARCHAR(600),
  IN  p_cover_image  VARCHAR(500),
  IN  p_body_html    MEDIUMTEXT,
  IN  p_author       VARCHAR(160),
  IN  p_published_at DATE,
  IN  p_status       VARCHAR(20),
  IN  p_meta_title   VARCHAR(200),
  IN  p_meta_description VARCHAR(320),
  IN  p_tags         JSON,
  IN  p_actor        VARCHAR(160),
  OUT p_uuid         CHAR(36)
)
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
  DECLARE v_id     BIGINT UNSIGNED DEFAULT NULL;
  DECLARE v_tag_id BIGINT UNSIGNED;
  DECLARE v_i      INT DEFAULT 0;
  DECLARE v_len    INT DEFAULT 0;
  DECLARE v_name   VARCHAR(120);
  DECLARE v_tslug  VARCHAR(80);
  DECLARE v_action VARCHAR(20) DEFAULT 'update';

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  IF p_slug IS NULL OR TRIM(p_slug) = '' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'slug is required';
  END IF;
  IF p_status NOT IN ('draft','published','archived') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'invalid status';
  END IF;
  IF p_status = 'published' AND p_published_at IS NULL THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'published_at is required to publish';
  END IF;

  START TRANSACTION;

  SELECT id, uuid INTO v_id, p_uuid
    FROM blog_posts WHERE slug = p_slug FOR UPDATE;

  IF v_id IS NULL THEN
    SET v_action = 'create';
    SET p_uuid = UUID();
    INSERT INTO blog_posts
      (uuid, slug, title, excerpt, cover_image, body_html, author,
       published_at, status, meta_title, meta_description)
    VALUES
      (p_uuid, p_slug, p_title, p_excerpt, p_cover_image, p_body_html,
       COALESCE(p_author,'LarCare Services'), p_published_at, p_status,
       p_meta_title, p_meta_description);
    SET v_id = LAST_INSERT_ID();
  ELSE
    UPDATE blog_posts
       SET title = p_title, excerpt = p_excerpt, cover_image = p_cover_image,
           body_html = p_body_html, author = COALESCE(p_author, author),
           published_at = p_published_at, status = p_status,
           meta_title = p_meta_title, meta_description = p_meta_description,
           deleted_at = NULL
     WHERE id = v_id;
  END IF;

  DELETE FROM blog_post_tags WHERE post_id = v_id;

  IF p_tags IS NOT NULL THEN
    SET v_len = JSON_LENGTH(p_tags);
    WHILE v_i < v_len DO
      SET v_name  = JSON_UNQUOTE(JSON_EXTRACT(p_tags, CONCAT('$[', v_i, ']')));
      SET v_tslug = LOWER(REGEXP_REPLACE(TRIM(v_name), '[^a-zA-Z0-9]+', '-'));

      INSERT INTO tags (slug, name) VALUES (v_tslug, v_name)
        ON DUPLICATE KEY UPDATE name = VALUES(name);
      SELECT id INTO v_tag_id FROM tags WHERE slug = v_tslug;

      INSERT IGNORE INTO blog_post_tags (post_id, tag_id) VALUES (v_id, v_tag_id);
      SET v_i = v_i + 1;
    END WHILE;
  END IF;

  INSERT INTO audit_log (entity, entity_uuid, action, actor, detail)
  VALUES ('blog_post', p_uuid, v_action, COALESCE(p_actor,'system'),
          JSON_OBJECT('slug', p_slug, 'status', p_status));

  COMMIT;
END $$

-- ---------------------------------------------------------------
-- Product upsert (admin write)
-- ---------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp_product_upsert $$
CREATE PROCEDURE sp_product_upsert (
  IN  p_slug         VARCHAR(160),
  IN  p_name         VARCHAR(200),
  IN  p_description  VARCHAR(1000),
  IN  p_image        VARCHAR(500),
  IN  p_price        DECIMAL(10,2),
  IN  p_currency     CHAR(3),
  IN  p_external_url VARCHAR(1000),
  IN  p_sort_order   SMALLINT UNSIGNED,
  IN  p_status       VARCHAR(20),
  IN  p_actor        VARCHAR(160),
  OUT p_uuid         CHAR(36)
)
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
  DECLARE v_id BIGINT UNSIGNED DEFAULT NULL;
  DECLARE v_action VARCHAR(20) DEFAULT 'update';

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN ROLLBACK; RESIGNAL; END;

  IF p_price < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'price cannot be negative';
  END IF;

  START TRANSACTION;

  SELECT id, uuid INTO v_id, p_uuid
    FROM products WHERE slug = p_slug FOR UPDATE;

  IF v_id IS NULL THEN
    SET v_action = 'create';
    SET p_uuid = UUID();
    INSERT INTO products
      (uuid, slug, name, description, image, price, currency,
       external_url, sort_order, status)
    VALUES
      (p_uuid, p_slug, p_name, p_description, p_image, COALESCE(p_price,0),
       COALESCE(p_currency,'USD'), p_external_url, COALESCE(p_sort_order,0),
       p_status);
  ELSE
    UPDATE products
       SET name = p_name, description = p_description, image = p_image,
           price = COALESCE(p_price, price),
           currency = COALESCE(p_currency, currency),
           external_url = p_external_url,
           sort_order = COALESCE(p_sort_order, sort_order),
           status = p_status, deleted_at = NULL
     WHERE id = v_id;
  END IF;

  INSERT INTO audit_log (entity, entity_uuid, action, actor)
  VALUES ('product', p_uuid, v_action, COALESCE(p_actor,'system'));

  COMMIT;
END $$

-- ---------------------------------------------------------------
-- Generic soft delete (admin write)
-- Whitelists the table name so this cannot become an injection vector.
-- ---------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp_content_soft_delete $$
CREATE PROCEDURE sp_content_soft_delete (
  IN p_entity VARCHAR(40),
  IN p_uuid   CHAR(36),
  IN p_actor  VARCHAR(160)
)
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
  IF p_entity = 'care_service' THEN
    UPDATE care_services SET deleted_at = UTC_TIMESTAMP() WHERE uuid = p_uuid;
  ELSEIF p_entity = 'blog_post' THEN
    UPDATE blog_posts SET deleted_at = UTC_TIMESTAMP() WHERE uuid = p_uuid;
  ELSEIF p_entity = 'product' THEN
    UPDATE products SET deleted_at = UTC_TIMESTAMP() WHERE uuid = p_uuid;
  ELSEIF p_entity = 'testimonial' THEN
    UPDATE testimonials SET deleted_at = UTC_TIMESTAMP() WHERE uuid = p_uuid;
  ELSEIF p_entity = 'job_opening' THEN
    UPDATE job_openings SET deleted_at = UTC_TIMESTAMP() WHERE uuid = p_uuid;
  ELSE
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'unknown entity';
  END IF;

  INSERT INTO audit_log (entity, entity_uuid, action, actor)
  VALUES (p_entity, p_uuid, 'soft_delete', COALESCE(p_actor,'system'));
END $$

-- ---------------------------------------------------------------
-- Contact submission triage (admin write)
-- ---------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp_contact_submission_set_status $$
CREATE PROCEDURE sp_contact_submission_set_status (
  IN p_uuid   CHAR(36),
  IN p_status VARCHAR(20),
  IN p_actor  VARCHAR(160)
)
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
  IF p_status NOT IN ('new','in_progress','closed','spam') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'invalid status';
  END IF;

  UPDATE contact_submissions SET status = p_status WHERE uuid = p_uuid;

  INSERT INTO audit_log (entity, entity_uuid, action, actor, detail)
  VALUES ('contact_submission', p_uuid, 'set_status',
          COALESCE(p_actor,'system'), JSON_OBJECT('status', p_status));
END $$

-- ---------------------------------------------------------------
-- Site settings update (admin write) — singleton
-- ---------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp_site_settings_update $$
CREATE PROCEDURE sp_site_settings_update (
  IN p_company_name  VARCHAR(120),
  IN p_tagline       VARCHAR(255),
  IN p_phone         VARCHAR(32),
  IN p_email         VARCHAR(160),
  IN p_address       VARCHAR(255),
  IN p_states_served JSON,
  IN p_actor         VARCHAR(160)
)
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
  INSERT INTO site_settings
    (id, company_name, tagline, phone, email, address, states_served)
  VALUES
    (1, p_company_name, p_tagline, p_phone, p_email, p_address, p_states_served)
  ON DUPLICATE KEY UPDATE
    company_name  = VALUES(company_name),
    tagline       = VALUES(tagline),
    phone         = VALUES(phone),
    email         = VALUES(email),
    address       = VALUES(address),
    states_served = VALUES(states_served);

  INSERT INTO audit_log (entity, action, actor)
  VALUES ('site_settings', 'update', COALESCE(p_actor,'system'));
END $$

DELIMITER ;