-- ============================================================
-- Database: aihan_covalima
-- Sistema Rekomendasaun Ai-han Tradisional iha Munisipiu Covalima
-- Algoritmu: Content-Based Filtering
-- ============================================================

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

-- ============================================================
-- TABEL ADMIN (login website backend)
-- ============================================================
CREATE TABLE admin (
    id_admin INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    nome_completo VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Password default: admin123 (ona hash ho bcrypt, VERIFIKA ona no valida)
INSERT INTO admin (username, password, nome_completo) VALUES
('admin', '$2y$10$/mJ5.gPCH0sEXyC/b6lGjuRkogxtx51gzwiRbv3fZiUSta9nxIN9G', 'Administradór Sistema');

-- ============================================================
-- TABEL USERS (Android app - login & register)
-- ============================================================
CREATE TABLE users (
    id_user INT AUTO_INCREMENT PRIMARY KEY,
    naran VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- TABEL KATEGORIA (harii husi Teknika Te'in)
-- ============================================================
CREATE TABLE kategoria (
    id_kategoria INT AUTO_INCREMENT PRIMARY KEY,
    naran_kategoria VARCHAR(50) NOT NULL,
    deskrisaun TEXT
);

INSERT INTO kategoria (naran_kategoria, deskrisaun) VALUES
('Da\'an', 'Aihan sira ne\'ebe prepara liu husi teknika da\'an (rebus) too tasak.'),
('Tunu', 'Aihan sira ne\'ebe prepara liu husi teknika tunu (bakar/panggang) too tasak.'),
('Kukus', 'Aihan sira ne\'ebe prepara liu husi teknika kukus too tasak.'),
('Sona', 'Aihan sira ne\'ebe prepara liu husi teknika sona (tumis/goreng) too tasak.'),
('Fermentasaun', 'Aihan sira ne\'ebe prepara liu husi prosesu fermentasaun.');

-- ============================================================
-- TABEL INGREDIENTES
-- ============================================================
CREATE TABLE ingredientes (
    id_ingredientes INT AUTO_INCREMENT PRIMARY KEY,
    naran_ingredientes VARCHAR(50) NOT NULL,
    deskrisaun TEXT,
    foto VARCHAR(255) DEFAULT NULL COMMENT 'Naran ficheiru foto ingredientes, rai iha assets/uploads/'
);

INSERT INTO ingredientes (naran_ingredientes, deskrisaun, foto) VALUES
('Naan', 'Naan manu lokal, uza dala barak iha aihan tradisionál Covalima.', 'manutimor.jpg'),
('Ikan', 'Ikan tasi ka ikan wai, uza dala barak iha aihan tradisionál Covalima.', 'ikan.jpg'),
('Koto', 'Koto fuik, ai-han tradisionál husi rai Covalima.', 'kotofuik.jpg'),
('Lakeru', 'Lakeru fuan tasak, uza hanesan baze ba dosi tradisionál.', 'lakerutasak.jpg'),
('Aifarina', 'Aifarina no hudi tasak, baze karbohidratu ba aihan tradisionál (sr: Kadaka).', 'aifarina.jpg'),
('Susu', 'Susu/keong mas, protein husi natar.', 'keongmas.jpg'),
('Boek', 'Boek, uza liu ba prosesu fermentasaun.', 'aihan_bubun.jpg'),
('Akar', 'Akar, ai-han tradisionál husi nuu no fore mungu.', 'aihan_akar.jpg');

-- ============================================================
-- TABEL AIHAN (dadus prinsipál - 9 ai-han tradisionál Covalima)
-- ============================================================
CREATE TABLE aihan (
    id_aihan INT AUTO_INCREMENT PRIMARY KEY,
    naran_aihan VARCHAR(100) NOT NULL,
    id_kategoria INT,
    id_ingredientes INT,
    sabor ENUM('Midar','Manas') NOT NULL,
    textura ENUM('Mamar','Toos') NOT NULL,
    deskripsaun TEXT,
    foto VARCHAR(255) DEFAULT 'default.jpg',
    foto_resep VARCHAR(255) DEFAULT NULL COMMENT 'Foto resep/prosesu (hatudu wainhira click ingredientes iha app)',
    origem VARCHAR(100) DEFAULT NULL COMMENT 'Suku/Postu orijen ai-han (hanesan iha Tabela 3.4 proposta)',
    -- ---- Fitur binariu (0/1) tuir Tabela 3.4 "Transformasaun Dataset" iha proposta ----
    fitur_ikan  TINYINT(1) NOT NULL DEFAULT 0,
    fitur_nan   TINYINT(1) NOT NULL DEFAULT 0,
    fitur_modo  TINYINT(1) NOT NULL DEFAULT 0,
    fitur_dosi  TINYINT(1) NOT NULL DEFAULT 0,
    fitur_manas TINYINT(1) NOT NULL DEFAULT 0,
    fitur_midar TINYINT(1) NOT NULL DEFAULT 0,
    fitur_moruk TINYINT(1) NOT NULL DEFAULT 0,
    fitur_sin   TINYINT(1) NOT NULL DEFAULT 0,
    fitur_mamar TINYINT(1) NOT NULL DEFAULT 0,
    fitur_tos   TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_kategoria) REFERENCES kategoria(id_kategoria),
    FOREIGN KEY (id_ingredientes) REFERENCES ingredientes(id_ingredientes)
);

INSERT INTO aihan (naran_aihan, id_kategoria, id_ingredientes, sabor, textura, deskripsaun, foto, foto_resep, origem,
                    fitur_ikan, fitur_nan, fitur_modo, fitur_dosi, fitur_manas, fitur_midar, fitur_moruk, fitur_sin, fitur_mamar, fitur_tos) VALUES
('Kaldu Naan', 1, 1, 'Midar', 'Mamar', 'Naan manu lokal, fos mean, salsa tahan, lis mutin da\'an too tasak.', 'kaldu_naan.jpg', 'resep_kaldu.jpg', 'Bau-lela',
    0, 1, 1, 0, 0, 1, 0, 0, 1, 0),
('Tukir', 2, 2, 'Manas', 'Mamar', 'Ikan, tali-tahan, derok, lis mean/mutin mina nuu, salsa tahan, duut morin no aimanas, depois tunu too tasak.', 'tukir.jpg', 'resep_tukir.jpg', 'Suai-loro',
    1, 0, 0, 0, 1, 0, 0, 1, 1, 0),
('Koto Tisi', 1, 3, 'Midar', 'Mamar', 'Koto fuik, da\'an too dala 7 too tasak.', 'koto_tisi.jpg', 'resep_kototisi.jpg', 'Fatumea',
    0, 0, 1, 0, 0, 1, 0, 0, 1, 0),
('Dosi Lakeru', 3, 4, 'Midar', 'Mamar', 'Lakeru fuan tasak, kukus, ai-farina parut, kukus too tasak.', 'dosi_lakeru.jpg', 'resep_dosilakeru.jpg', 'Salele',
    0, 0, 0, 1, 0, 1, 0, 0, 1, 0),
('Kadaka', 2, 5, 'Midar', 'Mamar', 'Aifarina, hudi tasak, depois tunu.', 'kadaka.jpg', 'resep_kadaka.jpg', 'Foholulik',
    0, 0, 0, 1, 0, 1, 0, 0, 1, 0),
('Susu Keong Mas', 4, 6, 'Manas', 'Mamar', 'Susu, lis mean/mutin, tomate, aimanas sona too tasak.', 'susu_keong_mas.jpg', 'resep_keongmas.jpg', 'Suai-Loro',
    0, 0, 1, 0, 1, 0, 0, 1, 1, 0),
('Saboko', 2, 2, 'Manas', 'Mamar', 'Ikan, tomate, lis mean, mutin, derok ai-lia tunu too tasak.', 'saboko.jpg', 'resep_saboko.jpg', 'Suai-Loro',
    1, 0, 0, 0, 1, 0, 0, 1, 1, 0),
('Bubun', 5, 7, 'Manas', 'Mamar', 'Boek, aimanas, masin, derok, lis mutin, habai iha loro-matan.', 'bubun.jpg', 'resep_bubun.jpg', 'Suai-Loro',
    1, 0, 0, 0, 1, 0, 0, 1, 1, 0),
('Akar', 2, 8, 'Midar', 'Toos', 'Akar, nuu rahun, fore mungu, masin midar depois tunu/pangang.', 'akar.jpg', 'resep_akar.jpg', 'Matai',
    0, 0, 0, 1, 0, 1, 0, 0, 0, 1);

-- ============================================================
-- TABEL FITUR_BOBOT (bobot ba kada fitur - Tabela 3.6 husi proposta, KORRIJIDU)
-- Uza husi algoritmu Cosine Similarity ho vetór berbobot (Kapítulu 3.9-3.10)
-- Bobot = Skor Valor Importánsia ÷ Total Skor (21), redondu ho "largest remainder
-- method" ba 3 decimál atu soma sai eksatu 1,000 (versaun 2 decimál uluk soma=0,94)
-- ============================================================
CREATE TABLE fitur_bobot (
    id_fitur_bobot INT AUTO_INCREMENT PRIMARY KEY,
    kolona VARCHAR(30) NOT NULL UNIQUE COMMENT 'Naran kolona fitur iha tabela aihan (fitur_xxx)',
    naran_fitur VARCHAR(30) NOT NULL,
    skor_importansia DECIMAL(4,1) DEFAULT NULL COMMENT 'Skor valor importánsia (baze ba kalkulasaun bobot)',
    bobot DECIMAL(5,3) NOT NULL,
    razaun VARCHAR(150) DEFAULT NULL
);

INSERT INTO fitur_bobot (kolona, naran_fitur, skor_importansia, bobot, razaun) VALUES
('fitur_ikan',  'IKAN',  6.0, 0.286, 'Ingredientes prinsipál ikan/seafood'),
('fitur_nan',   'NAN',   3.0, 0.143, 'Ingredientes prinsipál nan manu lokal'),
('fitur_modo',  'MODO',  3.0, 0.143, 'Ingredientes prinsipál modo'),
('fitur_dosi',  'DOSI',  2.0, 0.095, 'Ingredientes prinsipál lakeru fuan, aifarina no hudi'),
('fitur_manas', 'MANAS', 2.0, 0.095, 'Preferénsia sabor manas'),
('fitur_midar', 'MIDAR', 2.0, 0.095, 'Preferénsia sabor midar'),
('fitur_moruk', 'MORUK', 1.0, 0.048, 'Preferénsia sabor moruk'),
('fitur_sin',   'SIN',   1.0, 0.048, 'Preferénsia sabor sin'),
('fitur_mamar', 'MAMAR', 0.6, 0.028, 'Preferénsia textura mamar'),
('fitur_tos',   'TOS',   0.4, 0.019, 'Preferénsia textura tos');
-- Total Skor = 21  |  Total Bobot = 1,000 (korrijidu, uluk 0,94)

-- ============================================================
-- TABEL RATING (bintang husi user - uza nu'udar bobot preferénsia)
-- ============================================================
CREATE TABLE rating (
    id_rating INT AUTO_INCREMENT PRIMARY KEY,
    id_aihan INT NOT NULL,
    id_user VARCHAR(100) NOT NULL COMMENT 'Android device ID',
    valor_bintang TINYINT NOT NULL CHECK (valor_bintang BETWEEN 1 AND 5),
    data_rating TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_aihan) REFERENCES aihan(id_aihan) ON DELETE CASCADE
);

-- ============================================================
-- TABEL FAVORITO (opsionál - rai ai-han favoritu husi user)
-- ============================================================
CREATE TABLE favorito (
    id_favorito INT AUTO_INCREMENT PRIMARY KEY,
    id_aihan INT NOT NULL,
    id_user VARCHAR(100) NOT NULL,
    data TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_aihan) REFERENCES aihan(id_aihan) ON DELETE CASCADE,
    UNIQUE KEY unique_favorito (id_aihan, id_user)
);

-- ============================================================
-- TABEL REKOMENDASAUN_LOG (rai istória kada pedidu rekomendasaun
-- bazeia ba preferénsia utilizadór, tuir pedidu dosente orientadora)
-- ============================================================
CREATE TABLE rekomendasaun_log (
    id_log INT AUTO_INCREMENT PRIMARY KEY,
    id_device VARCHAR(100) DEFAULT NULL COMMENT 'Android device ID (opsional)',
    fitur_ikan TINYINT(1) DEFAULT 0,
    fitur_nan TINYINT(1) DEFAULT 0,
    fitur_modo TINYINT(1) DEFAULT 0,
    fitur_dosi TINYINT(1) DEFAULT 0,
    fitur_manas TINYINT(1) DEFAULT 0,
    fitur_midar TINYINT(1) DEFAULT 0,
    fitur_moruk TINYINT(1) DEFAULT 0,
    fitur_sin TINYINT(1) DEFAULT 0,
    fitur_mamar TINYINT(1) DEFAULT 0,
    fitur_tos TINYINT(1) DEFAULT 0,
    id_aihan_hasil INT DEFAULT NULL COMMENT 'Ai-han ho similarity aas liu (rank 1)',
    similaridade DECIMAL(5,3) DEFAULT NULL,
    data_pedidu TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_aihan_hasil) REFERENCES aihan(id_aihan) ON DELETE SET NULL
);