-- Phase B multi-provider payments (Stripe + PayPal/Venmo first, Square later)
USE BarberShop;

CREATE TABLE IF NOT EXISTS payment_settings (
    tenant_id INT NOT NULL PRIMARY KEY,
    mode VARCHAR(10) NOT NULL DEFAULT 'test',
    enable_stripe TINYINT(1) NOT NULL DEFAULT 0,
    enable_paypal TINYINT(1) NOT NULL DEFAULT 0,
    enable_venmo TINYINT(1) NOT NULL DEFAULT 0,
    enable_square TINYINT(1) NOT NULL DEFAULT 0,
    enable_ach TINYINT(1) NOT NULL DEFAULT 0,
    enable_pay_at_shop TINYINT(1) NOT NULL DEFAULT 1,
    stripe_publishable_key VARCHAR(255) NULL,
    stripe_secret_key VARCHAR(255) NULL,
    stripe_webhook_secret VARCHAR(255) NULL,
    paypal_client_id VARCHAR(255) NULL,
    paypal_client_secret VARCHAR(255) NULL,
    paypal_webhook_id VARCHAR(255) NULL,
    square_application_id VARCHAR(255) NULL,
    square_access_token VARCHAR(255) NULL,
    square_location_id VARCHAR(255) NULL,
    default_currency CHAR(3) NOT NULL DEFAULT 'USD',
    booking_payments VARCHAR(20) NOT NULL DEFAULT 'off',
    retail_payments VARCHAR(20) NOT NULL DEFAULT 'optional',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    provider VARCHAR(30) NOT NULL,
    purpose VARCHAR(40) NOT NULL,
    reference_type VARCHAR(40) NOT NULL,
    reference_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    status VARCHAR(30) NOT NULL DEFAULT 'pending',
    external_id VARCHAR(255) NULL,
    checkout_url TEXT NULL,
    payer_email VARCHAR(150) NULL,
    meta_json LONGTEXT NULL,
    paid_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    INDEX idx_payments_tenant_ref (tenant_id, reference_type, reference_id),
    INDEX idx_payments_external (provider, external_id),
    INDEX idx_payments_status (tenant_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO payment_settings (tenant_id, mode, enable_pay_at_shop, retail_payments, booking_payments)
SELECT 1, 'test', 1, 'optional', 'off'
WHERE NOT EXISTS (SELECT 1 FROM payment_settings WHERE tenant_id = 1);
