CREATE DATABASE IF NOT EXISTS landing_switcher CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE landing_switcher;

CREATE TABLE ecom_sites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    domain VARCHAR(255) NOT NULL UNIQUE,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE landing_pages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    slug VARCHAR(150) NOT NULL UNIQUE,
    landing_url VARCHAR(255) NOT NULL,
    status ENUM('active','draft','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE landing_ecom_assignments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    landing_id INT NOT NULL,
    ecom_id INT NOT NULL,
    priority INT NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    direct_product_url VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_landing_ecom (landing_id, ecom_id),
    CONSTRAINT fk_assignment_landing FOREIGN KEY (landing_id) REFERENCES landing_pages(id) ON DELETE CASCADE,
    CONSTRAINT fk_assignment_ecom FOREIGN KEY (ecom_id) REFERENCES ecom_sites(id) ON DELETE CASCADE
);

CREATE TABLE switch_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    landing_id INT NOT NULL,
    old_ecom_id INT NULL,
    new_ecom_id INT NOT NULL,
    changed_by INT NULL,
    reason VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_log_landing FOREIGN KEY (landing_id) REFERENCES landing_pages(id) ON DELETE CASCADE,
    CONSTRAINT fk_log_old_ecom FOREIGN KEY (old_ecom_id) REFERENCES ecom_sites(id) ON DELETE SET NULL,
    CONSTRAINT fk_log_new_ecom FOREIGN KEY (new_ecom_id) REFERENCES ecom_sites(id) ON DELETE CASCADE,
    CONSTRAINT fk_log_admin FOREIGN KEY (changed_by) REFERENCES admins(id) ON DELETE SET NULL
);
-- password: admin123

INSERT INTO ecom_sites (name, domain, status, notes) VALUES
('Store A', 'store-a.com', 'active', 'Primary store'),
('Store B', 'store-b.com', 'active', 'Backup store'),
('Store C', 'store-c.com', 'inactive', 'Temporarily down');

INSERT INTO landing_pages (title, slug, landing_url, status) VALUES
('Landing 1', 'landing-1', 'https://landing-domain.com/landing-1', 'active'),
('Landing 2', 'landing-2', 'https://landing-domain.com/landing-2', 'active'),
('Landing 3', 'landing-3', 'https://landing-domain.com/landing-3', 'active');

INSERT INTO landing_ecom_assignments (landing_id, ecom_id, priority, is_active, direct_product_url) VALUES
(1, 1, 1, 1, 'https://store-a.com/product-a'),
(1, 2, 2, 0, 'https://store-b.com/product-a'),
(2, 1, 1, 1, 'https://store-a.com/product-b'),
(2, 2, 2, 0, 'https://store-b.com/product-b'),
(3, 2, 1, 1, 'https://store-b.com/product-c');
