-- Gift Donation posts

CREATE TABLE gift_and_donation_posts (
    id CHAR(36) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    authorEmail VARCHAR(150) NOT NULL,
    galleries JSON NULL,

    isActive BOOLEAN DEFAULT TRUE,
    isDeleted BOOLEAN DEFAULT FALSE,

    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_donation_author_email (authorEmail),
    INDEX idx_donation_active_deleted (isActive, isDeleted),
    INDEX idx_donation_created_at (createdAt)
);
-- donations
CREATE TABLE donations (
    id CHAR(36) PRIMARY KEY,

    giftAndDonationPostId CHAR(36) NOT NULL,
    authorEmail VARCHAR(150) NOT NULL,

    amount DECIMAL(12,2) NOT NULL,

    transactionRefId VARCHAR(255) NULL,

    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_donation_post_id (giftAndDonationPostId),
    INDEX idx_donation_author_email (authorEmail),
    INDEX idx_donation_transaction_ref (transactionRefId)
);

-- donations
CREATE TABLE transactions (
    id CHAR(36) PRIMARY KEY,

    transactionRefId VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    transactionCode VARCHAR(100) NOT NULL,
    transactionAmount DECIMAL(12,2) NOT NULL,

    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_transaction_ref_id (transactionRefId),
    INDEX idx_transaction_code (transactionCode)
);