-- Migration to Multi-Tenancy

-- 1. Create stores table
CREATE TABLE IF NOT EXISTS stores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    owner_id INT DEFAULT NULL, -- Will update this later
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active TINYINT(1) DEFAULT 1
);

-- 2. Insert Default Store (Existing Data)
INSERT INTO stores (name) VALUES ('Toko Utama');

-- 3. Add store_id to USERS
ALTER TABLE users ADD COLUMN store_id INT DEFAULT 1;
ALTER TABLE users ADD CONSTRAINT fk_users_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE;

-- 4. Add store_id to PRODUCTS
ALTER TABLE products ADD COLUMN store_id INT DEFAULT 1;
ALTER TABLE products ADD CONSTRAINT fk_products_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE;

-- 5. Add store_id to CATEGORIES
ALTER TABLE categories ADD COLUMN store_id INT DEFAULT 1;
ALTER TABLE categories ADD CONSTRAINT fk_categories_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE;

-- 6. Add store_id to CUSTOMERS
ALTER TABLE customers ADD COLUMN store_id INT DEFAULT 1;
ALTER TABLE customers ADD CONSTRAINT fk_customers_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE;

-- 7. Add store_id to TRANSACTIONS
ALTER TABLE transactions ADD COLUMN store_id INT DEFAULT 1;
ALTER TABLE transactions ADD CONSTRAINT fk_transactions_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE;

-- 8. Add store_id to OUTLETS
ALTER TABLE outlets ADD COLUMN store_id INT DEFAULT 1;
ALTER TABLE outlets ADD CONSTRAINT fk_outlets_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE;

-- 9. Add store_id to SETTINGS
-- Settings table is key-value, unique key needs to be unique per store now.
-- Drop old unique index first
ALTER TABLE settings DROP INDEX setting_key;
ALTER TABLE settings ADD COLUMN store_id INT DEFAULT 1;
ALTER TABLE settings ADD CONSTRAINT fk_settings_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE;
-- New unique constraint: key + store_id
ALTER TABLE settings ADD UNIQUE KEY unique_setting_per_store (setting_key, store_id);

-- 10. Add store_id to DISCOUNTS
ALTER TABLE discounts ADD COLUMN store_id INT DEFAULT 1;
ALTER TABLE discounts ADD CONSTRAINT fk_discounts_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE;

-- 11. Add store_id to STOCK HISTORY
ALTER TABLE stock_history ADD COLUMN store_id INT DEFAULT 1;
ALTER TABLE stock_history ADD CONSTRAINT fk_stock_history_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE;

-- Update Default Store Owner
UPDATE stores SET owner_id = (SELECT id FROM users WHERE role = 'owner' LIMIT 1) WHERE id = 1;