USE `projects_membershipdb`;

-- Recreate helper procedure (was dropped at end of membership_schema.sql)
DROP PROCEDURE IF EXISTS `add_column_if_not_exists`;
DELIMITER ;;
CREATE PROCEDURE `add_column_if_not_exists`(
    IN p_table VARCHAR(100),
    IN p_column VARCHAR(100),
    IN p_definition TEXT
)
BEGIN
    DECLARE col_count INT;
    SELECT COUNT(*) INTO col_count
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'projects_membershipdb'
      AND TABLE_NAME = p_table
      AND COLUMN_NAME = p_column;
    IF col_count = 0 THEN
        SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END;;
DELIMITER ;

-- Add template and sharing columns to dynamic_forms
CALL add_column_if_not_exists('dynamic_forms', 'is_template', 'TINYINT(1) DEFAULT 0 AFTER `requires_auth`');
CALL add_column_if_not_exists('dynamic_forms', 'template_name', 'VARCHAR(255) DEFAULT NULL AFTER `is_template`');
CALL add_column_if_not_exists('dynamic_forms', 'source_form_id', 'INT DEFAULT NULL AFTER `template_name`');
CALL add_column_if_not_exists('dynamic_forms', 'share_token', 'VARCHAR(64) DEFAULT NULL AFTER `source_form_id`');
CALL add_column_if_not_exists('dynamic_forms', 'share_token_expires', 'DATETIME DEFAULT NULL AFTER `share_token`');
CALL add_column_if_not_exists('dynamic_forms', 'allow_public_submissions', 'TINYINT(1) DEFAULT 0 AFTER `share_token_expires`');

-- Field templates table (reusable field configurations)
CREATE TABLE IF NOT EXISTS `field_templates` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `field_type` ENUM('text','textarea','email','number','select','checkbox','radio','date','file','password','url') NOT NULL DEFAULT 'text',
    `label` VARCHAR(255) NOT NULL,
    `options` TEXT DEFAULT NULL,
    `is_required` TINYINT(1) DEFAULT 0,
    `placeholder` VARCHAR(255) DEFAULT NULL,
    `validation_rules` VARCHAR(255) DEFAULT NULL,
    `created_by` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Form shares table (track shared links)
CREATE TABLE IF NOT EXISTS `form_shares` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `form_id` INT NOT NULL,
    `token` VARCHAR(64) NOT NULL UNIQUE,
    `shared_by` INT NOT NULL,
    `shared_with` INT DEFAULT NULL,
    `message` TEXT DEFAULT NULL,
    `expires_at` DATETIME DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`form_id`) REFERENCES `dynamic_forms`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`shared_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`shared_with`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Cleanup
DROP PROCEDURE IF EXISTS `add_column_if_not_exists`;