-- ============================================================
-- Receipt Tracker — Projects feature migration
-- Target: agguae_receipts on MariaDB 10.11
-- Run ONCE in phpMyAdmin (Import / SQL tab). Safe to re-run.
-- ============================================================

-- 1) Projects table -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS projects (
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    name               VARCHAR(128) NOT NULL,
    created_by_user_id INT NULL,
    is_active          TINYINT(1) NOT NULL DEFAULT 1,
    created_at         TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_name (name),
    KEY idx_active (is_active),
    CONSTRAINT fk_project_creator
        FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2) receipts.project_id column + index --------------------------------------
--    (MariaDB supports IF NOT EXISTS on ADD COLUMN / ADD INDEX.)
ALTER TABLE receipts
    ADD COLUMN IF NOT EXISTS project_id INT NULL AFTER user_id,
    ADD INDEX  IF NOT EXISTS idx_project (project_id);

-- 3) receipts -> projects foreign key (conditional so re-runs don't fail) -----
SET @fk_exists := (
    SELECT COUNT(*)
    FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
      AND TABLE_NAME        = 'receipts'
      AND CONSTRAINT_NAME   = 'fk_receipt_project'
      AND CONSTRAINT_TYPE   = 'FOREIGN KEY'
);

SET @ddl := IF(
    @fk_exists = 0,
    'ALTER TABLE receipts
        ADD CONSTRAINT fk_receipt_project
        FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL',
    'DO 0'  -- already present: no-op
);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4) Verification -------------------------------------------------------------
SELECT 'projects table' AS check_item,
       COUNT(*)         AS row_count
FROM projects;

SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME   = 'receipts'
  AND COLUMN_NAME  = 'project_id';

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
  AND TABLE_NAME        = 'receipts'
  AND CONSTRAINT_NAME   = 'fk_receipt_project';