USE jeans_hub;

CREATE TABLE IF NOT EXISTS gift_cards (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchaser_user_id INT UNSIGNED NULL,
    purchase_order_id BIGINT UNSIGNED NULL,
    card_type ENUM('digital','physical') NOT NULL,
    code_hash CHAR(64) NULL,
    code_last4 CHAR(4) NULL,
    initial_balance_paise BIGINT UNSIGNED NOT NULL,
    balance_paise BIGINT UNSIGNED NOT NULL DEFAULT 0,
    currency CHAR(3) NOT NULL DEFAULT 'INR',
    recipient_name VARCHAR(100) NOT NULL,
    recipient_email VARCHAR(254) NULL,
    sender_name VARCHAR(100) NULL,
    personal_message VARCHAR(500) NULL,
    shipping_name VARCHAR(100) NULL,
    shipping_phone VARCHAR(20) NULL,
    shipping_address_line1 VARCHAR(200) NULL,
    shipping_address_line2 VARCHAR(200) NULL,
    shipping_city VARCHAR(100) NULL,
    shipping_state VARCHAR(100) NULL,
    shipping_postal_code CHAR(6) NULL,
    shipping_country VARCHAR(100) DEFAULT 'India',
    status ENUM(
        'pending_payment','active','redeemed',
        'expired','cancelled','disabled'
    ) NOT NULL DEFAULT 'pending_payment',
    delivery_status ENUM(
        'not_required','pending','processing',
        'shipped','delivered','failed'
    ) NOT NULL DEFAULT 'not_required',
    expires_at DATETIME NULL,
    activated_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY gift_cards_code_hash_unique (code_hash),
    KEY gift_cards_purchaser_idx (purchaser_user_id),
    KEY gift_cards_order_idx (purchase_order_id),
    KEY gift_cards_recipient_email_idx (recipient_email),
    KEY gift_cards_status_idx (status, created_at),

    CONSTRAINT gift_cards_purchaser_fk
        FOREIGN KEY (purchaser_user_id)
        REFERENCES users(id) ON DELETE SET NULL,

    CONSTRAINT gift_cards_purchase_order_fk
        FOREIGN KEY (purchase_order_id)
        REFERENCES orders(id) ON DELETE SET NULL,

    CONSTRAINT gift_cards_initial_balance_check
        CHECK (initial_balance_paise > 0),

    CONSTRAINT gift_cards_balance_check
        CHECK (balance_paise <= initial_balance_paise)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS gift_card_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    gift_card_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NULL,
    actor_user_id INT UNSIGNED NULL,
    transaction_type ENUM(
        'purchase','redemption','refund','adjustment','expiry'
    ) NOT NULL,
    amount_paise BIGINT UNSIGNED NOT NULL,
    balance_before_paise BIGINT UNSIGNED NOT NULL,
    balance_after_paise BIGINT UNSIGNED NOT NULL,
    reference_key VARCHAR(100) NULL,
    notes VARCHAR(500) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY gift_card_reference_unique (reference_key),
    KEY gift_card_transactions_card_idx (gift_card_id, created_at),
    KEY gift_card_transactions_order_idx (order_id),

    CONSTRAINT gift_card_transactions_card_fk
        FOREIGN KEY (gift_card_id)
        REFERENCES gift_cards(id) ON DELETE RESTRICT,

    CONSTRAINT gift_card_transactions_order_fk
        FOREIGN KEY (order_id)
        REFERENCES orders(id) ON DELETE SET NULL,

    CONSTRAINT gift_card_transactions_actor_fk
        FOREIGN KEY (actor_user_id)
        REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;