-- Multi-tenant franchise system migration
-- Glado POS System

-- Create franchises table if not exists
CREATE TABLE IF NOT EXISTS franchises (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    owner_name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(20),
    address TEXT,
    settings JSON,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_franchise_active (is_active),
    INDEX idx_franchise_email (email)
);

-- Create branches table if not exists
CREATE TABLE IF NOT EXISTS branches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    franchise_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    address TEXT,
    phone VARCHAR(20),
    payment_methods JSON,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchise_id) REFERENCES franchises(id) ON DELETE CASCADE,
    INDEX idx_branch_franchise (franchise_id),
    INDEX idx_branch_active (is_active)
);

-- Add franchise_id to existing tables if not exists
ALTER TABLE users 
ADD COLUMN IF NOT EXISTS franchise_id INT,
ADD COLUMN IF NOT EXISTS branch_id INT,
ADD INDEX IF NOT EXISTS idx_user_franchise (franchise_id),
ADD INDEX IF NOT EXISTS idx_user_branch (branch_id);

ALTER TABLE customers 
ADD COLUMN IF NOT EXISTS franchise_id INT,
ADD INDEX IF NOT EXISTS idx_customer_franchise (franchise_id);

ALTER TABLE products 
ADD COLUMN IF NOT EXISTS franchise_id INT,
ADD INDEX IF NOT EXISTS idx_product_franchise (franchise_id);

ALTER TABLE sales 
ADD COLUMN IF NOT EXISTS franchise_id INT,
ADD COLUMN IF NOT EXISTS branch_id INT,
ADD INDEX IF NOT EXISTS idx_sale_franchise (franchise_id),
ADD INDEX IF NOT EXISTS idx_sale_branch (branch_id);

ALTER TABLE promotions 
ADD COLUMN IF NOT EXISTS franchise_id INT,
ADD INDEX IF NOT EXISTS idx_promotion_franchise (franchise_id);

ALTER TABLE suppliers 
ADD COLUMN IF NOT EXISTS franchise_id INT,
ADD INDEX IF NOT EXISTS idx_supplier_franchise (franchise_id);

ALTER TABLE purchases 
ADD COLUMN IF NOT EXISTS franchise_id INT,
ADD INDEX IF NOT EXISTS idx_purchase_franchise (franchise_id);

-- Create default franchise if none exists
INSERT IGNORE INTO franchises (id, name, owner_name, email, settings, is_active) 
VALUES (1, 'Glado Principal', 'Administrador', 'admin@glado.com', '{"points_per_amount": 10, "daily_points_limit": 50, "redemption_limit": 10, "timezone": "America/Guatemala"}', TRUE);

-- Create default branch if none exists
INSERT IGNORE INTO branches (id, franchise_id, name, address, payment_methods, is_active) 
VALUES (1, 1, 'Sucursal Principal', 'Dirección Principal', '{"cash": true, "transfer": true, "card": false}', TRUE);

-- Update existing users to belong to default franchise
UPDATE users SET franchise_id = 1, branch_id = 1 WHERE franchise_id IS NULL;

-- Update existing data to belong to default franchise
UPDATE customers SET franchise_id = 1 WHERE franchise_id IS NULL;
UPDATE products SET franchise_id = 1 WHERE franchise_id IS NULL;
UPDATE sales SET franchise_id = 1, branch_id = 1 WHERE franchise_id IS NULL;
UPDATE promotions SET franchise_id = 1 WHERE franchise_id IS NULL;
UPDATE suppliers SET franchise_id = 1 WHERE franchise_id IS NULL;
UPDATE purchases SET franchise_id = 1 WHERE franchise_id IS NULL;

-- Add foreign key constraints (only if they don't exist)
SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
     WHERE TABLE_NAME = 'users' AND CONSTRAINT_NAME = 'fk_user_franchise') = 0,
    'ALTER TABLE users ADD CONSTRAINT fk_user_franchise FOREIGN KEY (franchise_id) REFERENCES franchises(id)',
    'SELECT "FK already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
     WHERE TABLE_NAME = 'users' AND CONSTRAINT_NAME = 'fk_user_branch') = 0,
    'ALTER TABLE users ADD CONSTRAINT fk_user_branch FOREIGN KEY (branch_id) REFERENCES branches(id)',
    'SELECT "FK already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;