-- Table des adresses de livraison
CREATE TABLE IF NOT EXISTS addresses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    name VARCHAR(100) NOT NULL,
    street VARCHAR(255) NOT NULL,
    postal_code VARCHAR(10) NOT NULL,
    city VARCHAR(100) NOT NULL,
    country VARCHAR(100) DEFAULT 'Belgique',
    phone VARCHAR(20),
    is_default BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Index pour améliorer les performances
CREATE INDEX IF NOT EXISTS idx_addresses_user ON addresses(user_id);
CREATE INDEX IF NOT EXISTS idx_addresses_postal ON addresses(postal_code);

-- Trigger pour gérer une seule adresse par défaut par utilisateur
CREATE TRIGGER IF NOT EXISTS ensure_single_default_address
BEFORE INSERT ON addresses
FOR EACH ROW
WHEN NEW.is_default = 1
BEGIN
    UPDATE addresses SET is_default = 0 WHERE user_id = NEW.user_id;
END;

CREATE TRIGGER IF NOT EXISTS ensure_single_default_address_update
BEFORE UPDATE ON addresses
FOR EACH ROW
WHEN NEW.is_default = 1 AND OLD.is_default = 0
BEGIN
    UPDATE addresses SET is_default = 0 WHERE user_id = NEW.user_id AND id != NEW.id;
END;