-- AirStand database schema and seed data


CREATE TABLE categorias (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(120) NOT NULL,
    slug VARCHAR(120) NOT NULL UNIQUE,
    descricao VARCHAR(255) DEFAULT NULL,
    ordem INT UNSIGNED DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE avioes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    categoria_id INT UNSIGNED NOT NULL,
    marca VARCHAR(120) NOT NULL,
    modelo VARCHAR(120) NOT NULL,
    ano INT UNSIGNED DEFAULT NULL,
    preco DECIMAL(15,2) DEFAULT NULL,
    descricao TEXT,
    destaque TINYINT(1) DEFAULT 0,
    horas_voo VARCHAR(60) DEFAULT NULL,
    autonomia VARCHAR(60) DEFAULT NULL,
    velocidade VARCHAR(60) DEFAULT NULL,
    slug VARCHAR(160) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FULLTEXT KEY ft_avioes (marca, modelo, descricao),
    CONSTRAINT fk_avioes_categoria FOREIGN KEY (categoria_id) REFERENCES categorias(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE aviao_imagens (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    aviao_id INT UNSIGNED NOT NULL,
    ficheiro VARCHAR(255) NOT NULL,
    ordem INT UNSIGNED DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_imagens_aviao FOREIGN KEY (aviao_id) REFERENCES avioes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE caracteristicas (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    aviao_id INT UNSIGNED NOT NULL,
    nome VARCHAR(150) NOT NULL,
    valor VARCHAR(150) NOT NULL,
    ordem INT UNSIGNED DEFAULT 0,
    CONSTRAINT fk_caracteristicas_aviao FOREIGN KEY (aviao_id) REFERENCES avioes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE leads (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(150) NOT NULL,
    email VARCHAR(180) NOT NULL,
    assunto VARCHAR(180) NOT NULL,
    mensagem TEXT,
    aviao_slug VARCHAR(160) DEFAULT NULL,
    status ENUM('novo','em_contacto','fechado') DEFAULT 'novo',
    ip_address VARCHAR(64) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_leads_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(120) NOT NULL,
    slug VARCHAR(60) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE admins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(150) NOT NULL,
    email VARCHAR(180) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role_id INT UNSIGNED DEFAULT NULL,
    active TINYINT(1) DEFAULT 1,
    last_login_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_admin_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE permissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(120) NOT NULL,
    slug VARCHAR(80) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE role_permission (
    role_id INT UNSIGNED NOT NULL,
    permission_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    CONSTRAINT fk_role_perm_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    CONSTRAINT fk_role_perm_perm FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE admin_permission (
    admin_id INT UNSIGNED NOT NULL,
    permission_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (admin_id, permission_id),
    CONSTRAINT fk_admin_perm_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE,
    CONSTRAINT fk_admin_perm_perm FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    chave VARCHAR(120) NOT NULL UNIQUE,
    valor TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id INT UNSIGNED DEFAULT NULL,
    action VARCHAR(120) NOT NULL,
    ip_address VARCHAR(64) DEFAULT NULL,
    payload JSON DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_logs_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE i18n_strings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    locale VARCHAR(10) NOT NULL,
    chave VARCHAR(190) NOT NULL,
    texto TEXT NOT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_locale_key (locale, chave)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed data
INSERT INTO categorias (nome, slug, descricao, ordem) VALUES
('Jatos', 'jatos', 'Performance intercontinental e luxo.', 1),
('Monomotores', 'monomotores', 'Versatilidade operacional para pistas curtas.', 2),
('Bimotores', 'bimotores', 'Equilíbrio entre alcance e eficiência.', 3),
('Helicópteros', 'helicopteros', 'Mobilidade vertical premium.', 4);

INSERT INTO avioes (categoria_id, marca, modelo, ano, preco, descricao, destaque, horas_voo, autonomia, velocidade, slug, created_at, updated_at) VALUES
(1, 'Gulfstream', 'G700', 2024, 72000000.00, 'Jato executivo de ultralongo alcance com cabine personalizável e conectividade total.', 1, '120 h', '7500', '516', 'gulfstream-g700', NOW(), NOW()),
(4, 'Airbus', 'ACH145', 2023, 9500000.00, 'Helicóptero executivo com cabine silenciosa e configuração para 6 passageiros.', 1, '60 h', '351', '145', 'airbus-ach145', NOW(), NOW());

INSERT INTO caracteristicas (aviao_id, nome, valor, ordem) VALUES
((SELECT id FROM avioes WHERE slug = 'gulfstream-g700'), 'Passageiros', '19', 1),
((SELECT id FROM avioes WHERE slug = 'gulfstream-g700'), 'Altitude de cruzeiro', '51 000 ft', 2),
((SELECT id FROM avioes WHERE slug = 'airbus-ach145'), 'Passageiros', '6', 1),
((SELECT id FROM avioes WHERE slug = 'airbus-ach145'), 'Autonomia', '1 300 km', 2);

INSERT INTO roles (nome, slug) VALUES
('Superadmin', 'superadmin'),
('Gestor', 'gestor'),
('Editor', 'editor'),
('Leitor', 'leitor');

INSERT INTO permissions (nome, slug) VALUES
('Ver aeronaves', 'avioes.view'),
('Criar aeronaves', 'avioes.create'),
('Atualizar aeronaves', 'avioes.update'),
('Eliminar aeronaves', 'avioes.delete'),
('Gerir media', 'media.manage'),
('Ver leads', 'leads.view'),
('Atualizar leads', 'leads.update'),
('Gerir administradores', 'admins.manage'),
('Gerir definições', 'settings.manage'),
('Ver logs', 'logs.view');

-- Role permissions
INSERT INTO role_permission (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p WHERE r.slug = 'superadmin';

INSERT INTO role_permission (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.slug IN ('avioes.view','avioes.create','avioes.update','media.manage','leads.view','leads.update') WHERE r.slug = 'gestor';

INSERT INTO role_permission (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.slug IN ('avioes.view','avioes.update','media.manage','leads.view') WHERE r.slug = 'editor';

INSERT INTO role_permission (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.slug IN ('avioes.view','leads.view') WHERE r.slug = 'leitor';

-- Default admin user (email: admin@airstand.com / password: airstand123)
INSERT INTO admins (nome, email, password_hash, role_id, active, created_at)
SELECT 'AirStand Admin', 'admin@airstand.com', '$2y$12$a3TSfcuRni9UlygP2iSLZuu8gcb5hzNrzoTqJqPWlWajIdOpb7Ahi', id, 1, NOW()
FROM roles WHERE slug = 'superadmin' LIMIT 1;

-- Sample settings
INSERT INTO settings (chave, valor) VALUES
('site_name', 'AirStand'),
('contact_email', 'hello@airstand.com'),
('exchange_rate', '1.10'),
('currency', 'EUR'),
('sales_phone', '+351210000000');

